postgresql migration/upgrade with a seamless...
TRANSCRIPT
![Page 1: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/1.jpg)
PostgreSQL Migration/Upgrade with a
Seamless Downtime
- by Avinash Vallarapu OpenSCG
![Page 2: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/2.jpg)
➢ Major Concerns in an Infrastructure
➢ PostgreSQL Upgrade from 8.2.13 to 9.5.5 (Case Study)
➢ Results
➢ Learnings
➢ Questions ?
Agenda
![Page 3: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/3.jpg)
Major concerns in an Infrastructure• Legacy Software
• Downtime during Software Upgrades
• Scaling Performance by adding more Servers
• Business Logic inside the Database.
![Page 4: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/4.jpg)
PostgreSQL Upgrade from 8.2.13 to 9.5.5(Case Study)
![Page 5: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/5.jpg)
Purpose : PostgreSQL Upgrade from 8.2.13 to 9.5.5Challenges :
• 72 Database Servers in Replication using Slony 1.2 (1 Master, 68 Read and 3 Writable Slony Replicas)
• 150 App Servers• 4 Applications (Transactional, Back-Office, Reporting, Audits)• 1200 Stored Procs• DOWNTIME• pg_upgrade does not work between 8.x and 9.x.• Slony cannot replicate between 8.2 and 9.x
Case Study
![Page 6: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/6.jpg)
![Page 7: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/7.jpg)
Why should we Upgrade?• Older OS and DB Software Versions lacks several new features.
• Increase in Transactions, needing more Servers.
• No possibility of Performance Improvements.
• Not too many extensions/tools for PostgreSQL 8.x
• Maintenance of Database Objects is a hurdle.
• No Support for Older Versions(below PostgreSQL 9.2)
![Page 8: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/8.jpg)
➢ Plan your Hardware Specifications
➢ Application to DB Connectivity.
➢ High Availability.
➢ Performance Testing.
➢ Backup Strategy.
➢ Plan your postgresql.conf parameters.
➢ Install all Softwares and Extensions in advance.
Pre-Upgrade Procedure
![Page 9: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/9.jpg)
![Page 10: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/10.jpg)
![Page 11: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/11.jpg)
How did we Upgrade?
![Page 12: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/12.jpg)
Step 1 : Build the Infrastructure & Install Software
![Page 13: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/13.jpg)
• Configured 2 Dedicated Machines for Master and Failover Slave.
• Configured 71 Slave Virtual Machines on Cloud.
• Pushed BigSQL Sandbox using Ansible to all the Servers.
Build the Infrastructure & Install Software
![Page 14: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/14.jpg)
What did the BigSQL Sandbox include?BigSQL Sandbox includes a variety of Packaged/Compiled Softwares & Extensions.Following are the Softwares and Extensions we have installed using BigSQL.• PostgreSQL 9.5.5• PlProfiler• PgBadger• pg_stat_statements• pgBouncer• pg_repack• pg_buffercache• Pldebugger
![Page 15: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/15.jpg)
Step 2 : Replication between 8.2 and 9.5
![Page 16: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/16.jpg)
Setup Replication between an 8.2 Instance and a New 9.5 PostgreSQL Instance.
Does Streaming Replication Work ? → No
Can Slony work between 8.2 & 9.5? → No
Does Londiste Work → No
What about Bucardo → Yes
![Page 18: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/18.jpg)
Step 3 : Streaming Replication between New 9.5 Instance and Slaves/Cascaded Slaves
![Page 19: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/19.jpg)
Streaming Replication between New 9.5 Instance and Slaves/Cascaded Slaves
• Installed Rep Manager on all the Servers.
• Set Up Streaming Replication between Master and all the Slaves and Cascaded Slaves.
![Page 20: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/20.jpg)
Step 4 : Re-direct all the Read traffic with a seamless downtime
![Page 21: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/21.jpg)
Re-direct all the Read traffic with a seamless downtime
• Make modifications to the already existing HA Proxy configurations with the New Slave Server IP’s.
• Reload the HA proxy with the New Server IP’s.
![Page 22: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/22.jpg)
Step 5 : Build the 3 Writable Slaves
![Page 23: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/23.jpg)
• Set up Streaming Replication between the New 9.5 Master and the 3 future Writable Slaves.
• Stop Bucardo Replication between 8.2 and 9.5.
• Add the 3 Writable Slaves as 3 New Targets to the Bucardo Replication
• Start Bucardo Replication.
Build the 3 Writable Slaves
![Page 24: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/24.jpg)
![Page 25: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/25.jpg)
Step 6 : Re-direct writes through PgBouncer to existing 8.2.13 Master
![Page 26: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/26.jpg)
• Configure PgBouncer on a New Server.
• Re-direct all the Application Writes through PgBouncer to the Old 8.2.13 Master.
• This enables us to use a PgBouncer restart to re-direct the Writes to the New 9.5.5 Master.
Re-direct writes through PgBouncer to existing 8.2.13 Master
![Page 27: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/27.jpg)
Step 7 : Re-direct writes to the New 9.5.5 Master.
![Page 28: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/28.jpg)
• Stop bucardo Replication between the 8.2.13 and all the 4 → 9.5.5 Instances.
• Setup Bucardo Replication between the New Master and the 3 Writables Slaves.
• Restart PgBouncer with the New Master IP Address for Writes.
Re-direct writes to the New 9.5.5 Master
![Page 29: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/29.jpg)
![Page 30: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/30.jpg)
Results
![Page 31: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/31.jpg)
Results
• No need to push all the DDL Changes or Code changes to all the Instances manually.
• Improvement in Reads & Writes Performance.• Max Replication Lag reduced to 0.5 seconds due to Streaming
Replication.• Improved performance of Stored Procs through PlProfiler.• Servers able to handle over 3x times the load as earlier.• Plan to reduce the number of Servers.• Plan to decommission the 3 Writable Slaves and re-direct the load to
Master.• Automatic-Failover• Partitioning
![Page 32: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/32.jpg)
Learnings
![Page 33: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/33.jpg)
Learnings
• Replication lag using Bucardo 5.4.1→ 40 seconds to 120 seconds
• Replication lag using Slony 2.2→ 8 seconds to 20 seconds.
• Setting max_standby_streaming_delay & max_standby_archive_delay to -1 may cause a great lag in Streaming Replication.
![Page 34: PostgreSQL Migration/Upgrade with a Seamless Downtime2017.pgconf.in/wp-content/uploads/2016/05/Online_Migration_Upgrade_With... · PostgreSQL Migration/Upgrade with a Seamless Downtime](https://reader030.vdocuments.us/reader030/viewer/2022040307/5ed2d445924c8a40787fb4a6/html5/thumbnails/34.jpg)
Questions ?