Postgres 9.3 to 9.4 upgrade

gistfile1.md

Upgrading from postgres 9.3 to 9.4

Outline

Subject to change after runs in staging

1. Install new postgres version in separate directory structure
2. Ensure pg_upgrade is installed for 9.3 and 9.4
3. Copy 9.3 conf files to 9.4 directories.
4. Dump database using pg_dump (just in case)
5. Shutdown application (app, worker, label, all). Ensure maintenance is up.
6. Shutdown old version (IS THIS RIGHT? OR DO BOTH HAVE TO RUN ON DIFFERENT PORTS?)
7. Use pg_upgrade to migrate data from 9.3 to 9.4
8. Verify data/operability
9. Change init scripts to run 9.4 binaries
10. Change user's PATH to include 9.4 binaries
11. Bring up new version (IS THIS RIGHT? OR HAVE THEY HAD TO REMAIN RUNNING?)
12. Test connection and queries from app server with console
13. Bring up app servers (app, worker, label, all)
14. Verify app works.
15. Bring down maintenance page.

Abortion Outline

Subject to change after runs in staging

1. Ensure init scripts run 9.3 binaries
2. Ensure user's PATH to include 9.3 binaries
3. Bring up old version (IS THIS RIGHT? OR HAVE THEY HAD TO REMAIN RUNNING?)
4. Test connection and queries from app server with console
5. Bring up app servers (app, worker, label, all) and verify app works
6. Bring down maintenance page.

Much later (after running solid for a time)

1. Remove 9.3 packages
2. Remove 9.3 data files

Todo

• Discussion: Database backups are managed by Rackspace using Holland. Would this still work after this upgrade? Yes, but we need to make sure the daemon will see the correct binaries, this requires us to modify their path and/or to redirect symlinks in /usr/bin to new binaries.
• Answer question: Do we need to install packages and/or reinstall pg gem on app servers? Does not appear so in staging.
• Answer question: Do both servers have to be running on different ports during this process? Both servers should be shut down during this process. pg_upgrade will run postgres binary for each version and will abort if either server is running against their data dirs.
• Answer question: How do we install 9.4? Are there packages available (and will they not overwrite 9.3)? Or do we need to compile on server? I have compiled and installed in staging and it was not a difficult process and allowed me to control everything to ensure we were not overwriting anything related to 9.3 (so we could abort)
• Answer question: What do we need to do to the hot slave? Can we do the same thing there, or will it need to be wiped?
• Verify assumption: Postgres 9.4 can work with our prod 9.3 configuration files. Nothing in stagings pg_hba.conf or postgres.conf files was version specific, and 9.4 is running in staging with the 9.3 config files. There were some optional new config options in the 9.4 config files that were commented out.
• Answer question: Can 9.3 data files be deleted safely? Yes, pg_upgrade should be done with the hard links option, to make it very fast, and not increase the disk size. Need to make sure we can reverse the process, though...
• Carry out upgrade on staging, automate what I can and note what I cannot. Note time to upgrade data on staging.
• Extrapolate time to upgrade staging to time to upgrade production. < 20 minutes if we can use the hard linking option of pg_upgrade
• After upgrading data with pg_upgrade and its hard-link option, can we still use the old postgres version if we need to abort for some reason?
• Carry out abortion on staging and ensure works, no hiccups.

Links

• Postgres release notes (all versions)
• Man page for pg_upgrade (9.4)
• Postgres docs on upgrading to 9.4 (via slow psql)
• Migration from 9.3 to 9.4 in Puppet Env
• Migration Issues #1

Notes

TBW after carrying out in staging.