Upgrading your PostgreSQL to 9.5


Upgrading your PostgreSQL deployment to version 9.5 is now possible through the Compose console. Working out how to perform this upgrade safely and reliably has been an interesting process because from version 9.4 to 9.5 is a PostgreSQL major upgrade.

"Wait", you say, "that's not a major upgrade."

With PostgreSQL it is...

"A major release is numbered by increasing either the first or second part of the version number, e.g. 9.1 to 9.2."

The important thing about PostgreSQL major updates is that they usually change how the data is stored internally. That's why whenever a PostgreSQL database starts up, it checks what version of PostgreSQL created the data directory. If it isn't the same major version, it'll refuse to run. Upgrading is traditionally done by dumping the contents of the databases, updating the database software and then restoring the dump's contents to the freshly updated database. It's a bit hands on and time consuming so we looked for an alternative.

We'll be looking at how we came up with our approach to this in another article coming soon. Suffice to say we looked at engineering an upgrade system with an eye on resilience and redundancy which performed quickly.

Compose's PostgreSQL Upgrade

Our major version upgrade process begins with a backup. We start there as it's a known point in the life of your data. You may wish to put your applications into maintenance mode and create an on-demand backup to ensure that you have the most recent data.
Whichever backup you go with, it will be be restored to a new PostgreSQL deployment where we may, or may not, run the pg_upgrade tool.

We call this process Deployment from backup and it supports the ability to change database version while it runs. At the end of the process, you'll have a freshly provisioned database in a fraction of the time it would take to dump and restore it. Backups are made automatically on Compose so there's always a recent backup, but you can always make one on demand to be completely up to date. Once you have a backup, you can restore it to a new deployment by clicking on the restore icon.

That will take you to the Deployment from backup dialog. At the top are details about the backup you have selected to restore; which deployment it is from and when it was created.

The rest of the page is about the deployment to be created to house this restored backup. You can enter a new deployment name (or accept the delightfully generated default). You can then say generally where you'd like the deployment created if you have a Compose Enterprise account, otherwise it defaults to "Compose Hosted". If "Compose Hosted" is selected, a range of data center locations is then available to create the deployment in.

Then we get to the Upgrade section. By default, this process will not upgrade your database and will select the matching major version. If you click Create Deployment at this point you will effectively clone your database.

If, on the other hand, you select a different version, like say 9.5, when you click Create Deployment, something extra happens. Your data is restored into a new deployment but rather than start up a database instance, pg_upgrade is run to upgrade the stored data to the selected version.

You'll now have your original PostgreSQL deployment and an upgraded PostgreSQL deployment running concurrently. Validate the upgraded PostgreSQL deployment and switch your applications to use that, then decommission the original PostgreSQL when you are ready.

If you're unhappy with the upgraded database, you still have your original database to fall back to. We're not expecting anyone to have problems with the Deployment from backup process, but we like to build things which give you lots of room to recover with if anything does go astray.

Dj Walker-Morgan
Dj Walker-Morgan was Compose's resident Content Curator, and has been both a developer and writer since Apples came in II flavors and Commodores had Pets. Love this article? Head over to Dj Walker-Morgan’s author page to keep reading.

Conquer the Data Layer

Spend your time developing apps, not managing databases.