Recipe - Major PostgreSQL upgrades in-place

A short recipe extracted from our award winning administration Cookbook regarding in-place major upgrades of PostgreSQL.

PostgreSQL provides an Additional Supplied Program, called pg_upgrade, that allows you to migrate between major releases, such as from 9.1 to 9.2; or you can upgrade straight to the latest server version.

These upgrades are performed in-place, meaning that we upgrade your database without moving to a new system. That does sound good, but pg_upgrade has a few things that you may wish to consider as potential negatives, which are as follows:

• The database server must be shut down while the upgrade takes place. Your system must be large enough to hold two copies of the database server: old and new copies. If it's not, then you have to use the link option of pg_upgrade, or use the Major upgrades online recipe from the Cookbook.

• If you use the link option on pg_upgrade, then there is no pg_downgrade utility. The only option in that case is a restore from backup, and that means extended unavailability while you restore. To lower the time of unavailability, you can leave a standby with the old version (not upgraded) and use that for failback purposes.

• If you copy the database, that is you don't use the link option, then the upgrade time will be proportional to the size of the database.

• The pg_upgrade utility does not validate all your additional add-in modules, so you will need to set up a test server and confirm that these work, ahead of performing the main upgrade.

The pg_upgrade utility supports versions from PostgreSQL 8.4 onwards and allows you to go straight from your current release to the latest release in one hop.

Find out the size of your database (using the How much disk space does a database use? recipe in Chapter 2, Exploring the Database). If the database is large or you have an important requirement for availability, you should consider doing the major upgrade using replication tools as well. Then, check out the next recipe.

1. Read the release notes for the new server version to which you are migrating, including all of the later releases. Pay attention to the incompatibilities section carefully; PostgreSQL does change from release to release. Assume this will take some hours.
2. Set up a test server with the old software release on it. Restore one of your backups in it. Upgrade that system to the new release to verify that there are no conflicts from software dependencies. Test your application. Make sure you identify and test each add-in PostgreSQL module you were using to confirm that it still works at the new release level.
3. Back up your production server. Prepare for the worst; hope for the best!
4. Most importantly, work out who you will call if things go badly, and exactly how to restore from that backup you just took.
5. Install new versions of all the required software on the production server, and create a new database server.
6. Don't disable security during the upgrade. Your security team will do backflips if they hear about this. Keep your job!
7. Now, go and do that backup. Don't skip this step; it isn't optional. Check whether the backup is actually readable, accessible, and complete.
8. Shut down the database servers.
9. Run pg_upgrade -v and then run any required post-upgrade scripts. Make sure you check whether any were required.
10. Start up the new database server and immediately run a server-wide ANALYZE operation using vacuumdb --analyze-in-stages
11. Run through your tests to check whether it worked or you need to start performing the contingency plan.
12. If all is OK, re-enable wide access to the database server. Restart the applications.
13. Don't delete your old server directory if you used the link method. The old data directory still contains the data for the new database server. Confusing! So, don't get caught by this.

The pg_upgrade utility works by creating a new set of database catalog tables, and then creating the old objects again in the new tables using the same identifiers as before. The pg_upgrade utility works easily because the data block format hasn't changed between some releases. Since we can't (always) see the future, make sure you read the release notes.