How do I upgrade the version of my PostgreSQL database

Owned by Tenyo Grozev (Unlicensed)
Last updated: Mar 22, 2021
2 min read

PostgreSQL version 9.6 is nearing end-of-life at the end of 2021, so if you are running that version in Spinup you should plan an upgrade to a newer version, e.g. 10.x, or, preferably, 12.x.

There are two ways you can do the upgrade:

  1. Upgrade the PostgreSQL instance in place from the Spinup UI

  2. Create a new PostgreSQL instance and do a dump and restore

Upgrade in place

Upgrading your current PostgreSQL instance to a new major version is faster, however, it will cause your database to become temporarily unavailable (unless you have HA enabled). Also, once you upgrade, there is no way to revert back to the old version in case there are problems. It may be a good idea to perform this upgrade on a test database first to get a sense of the length of the outage (usually less than 30 min for a small database but could be more depending on size), and if there are any issues introduced by the new version.

AWS has a good matrix of which specific versions can be upgraded to what major/minor release: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion

You can initiate the upgrade by going to your database instance in the Spinup UI and clicking the “Upgrade version” button:

Select the version you want to upgrade to from the drop-down list:

Note that this list is not vetted by Spinup, so you should check the compatibility matrix above first. If you pick an incompatible new version, you will see an error when you click “Upgrade”.

You can try upgrading to the latest available version (e.g. 12.x) or you could also pick in intermediate version, e.g. upgrade from 9.6.x to 10.15, and then later upgrade from 10.15 to 12.x.

After clicking Upgrade, you will be sent back to the database view and shortly the status should change to “upgrading”:

Wait until the upgrade is complete and the database status is green and “available”.

Verify that the database is working as expected.

 

Dump and restore

TODO