Upgrading AWS RDS PostgreSQL With Extensions

We recently received an end of support notification for the PostgreSQL database version 9.4 that we were using.  As with most things, when it's working you leave it alone, only to realize years later how long it's been.  The first emotion I get in situations like this is dread, while asking myself, 'how big a headache is this going to be'?  And then you remember you're running a managed service!  This is going to be easy; there's probably a magic button that just rolls this forward.   Or is there?

The answer is: 'kinda'.  Back in April of 2019, AWS announced multi-major version upgrades for RDS Postgres.  And it supports several 9.x version upgrades directly to Postgres 11.  Hooray, we're saved!  Or ... maybe not.  First, it's important to read the fine print about major version upgrades.  The first line grabs your attention, "Major version upgrades can contain database changes that are not backward-compatible with previous versions of the database".  Higher up on this same page, it also says, "A PostgreSQL upgrade doesn't upgrade any PostgreSQL extensions".  We use PostGIS, and this page specifically addresses that plugin by saying 'you cannot skip major versions for some source to target combinations'.  After some serious googling, it’s clear that other folks ran into this problem. That’s the good news.

And what if you have a multi-AZ deployment? AWS says, “If your DB instance is in a Multi-AZ deployment, both the primary writer DB instance and standby DB instances are upgraded.” Or what about a read replica? AWS says, “A read replica can’t undergo a major version upgrade but the read replica’s source instance can. If a read replica’s source instance undergoes a major version upgrade, all read replicas for that source instance remain with the previous engine version. In this case, the read replicas can no longer replicate changes performed on the source instance. We recommend that you either promote your read replicas, or delete and recreate them after the source instance has upgraded to a different major version.” AWS then refers you to more information on managing read replica’s.

So, I’m going to focus on the source instance in this blog.  It appears there are basically two ways to upgrade your primary database without using third party tools: (1) following the AWS RDS pg_upgrade path, or (2) stand up the new PostgreSQL engine you want (e.g. 11.4) and use pg_dump | pg_dumpall to migrate your data from the old engine.  It looks like the second option is possible with AWS RDS; the quickest approach being to export data to S3 and import data from S3.  It appears that AWS Database Migration Service will also help you migrate from one version of RDS Postgres to another version, or to a different database engine entirely.  There are other great blogs  like this one by Amet Umerov that provide an upgrade path using Bucardo. I’m sure there are other approaches, too.

For this task, I chose the first option, which was to follow the upgrade path.  You can approach this chore in one of several ways.  AWS (and I) recommend against upgrading your production instance without testing the upgrade path first: AWS says, "Make sure that you thoroughly test any upgrade to verify that your applications work correctly before applying the upgrade to your production DB instances".  The route I took was to:

• Take a snapshot of my production instance
• Restore that snapshot to a new RDS instance
• Upgrade the snapshot RDS instance to 11.x, one major version at a time
• Test the application/database to ensure everything works
• Synchronize data between the instances
• Point the production application to the newer RDS PostgreSQL instance

For this blog, I’m going to step through how I upgraded the primary Postgres instance and then stop there. I’m not going to cover ways to synchronize  data, test the application or examine breaking changes between versions, gracefully switch over using DNS and/or configuration, etc. With this narrow focus in mind, let’s get started.

Follow the Upgrade Path

You'll want to read the PostgreSQL change log to see what backward compatibility issues exist between major versions. But even if you don't see any glaring issues, you're going to want to test your application/database code to see what, if anything breaks as you upgrade.  One way (perhaps the best way) to do this is to take a current snapshot of your existing database, restore this to an RDS instance, and use it as a guinea pig to test the upgrade path.  You can do this in the AWS Console simply by taking a snapshot, then clicking on the snapshot, and choosing "Restore Snapshot" from the Actions dropdown list on the top right of the snapshot page.

You will need to upgrade from your current version to the next major version of Postgres.  You can follow the AWS RDS upgrade path provided in their source / target table.  For example, this is the upgrade path we followed:

In addition, you will also want to look at this page to see which versions of the extensions are supported for each major version of Postgres. As you upgrade your Postgres database engine, you'll need to upgrade your extensions as well. For example, going from the 9.4 to the 9.5 engine, you'll look at the table for PostgreSQL Version 9.5.x Extensions Supported on Amazon RDS.  Because you'll likely want to go from 9.4.x to 9.5.18, look for the version of your extensions that appear under 9.5.18.  Those are the versions you'll want to be running at the end of your migration from 9.4.x to 9.5.18, before you then try to upgrade to 9.6.14.  

Upgrading from 9.4.20 to 9.5.18

So, let's walk through a full upgrade.  To upgrade the PostgreSQL engine, we'll use the AWS Console.  If we connect to the database via psql, we can see our current Postgres engine with the SELECT version(); command.  If, like us, you're using PostGIS, you can get its version with the SELECT postgis_full_version(); command.

For this example, we'll go from PostgreSQL 9.4.20 to 9.5.18. After you've restored your database from a snapshot, you should see it in the list of available databases:

Modify the Instance to Upgrade the Engine

Select your test instance and click the 'Modify' button at the top.  After doing so, you'll see the 'DB engine version' drop down at the top of the modify page:

Select 9.5.18-R1 to upgrade to that version.  Once you click Continue, you'll arrive at the next screen:

You'll want to 'Apply immediately' since this is your test instance.  There's no time like the present! Then, accept this upgrade by clicking the 'Modify DB Instance'. Back on the RDS Databases page, you'll see that your database is now being upgraded:

You can see the list of steps that the upgrade process takes by following the RDS Events Log:

If there are any issues during the upgrade, you'll see the error messages appear in the event log.  Once the database becomes 'Available' again in the Databases list, you can verify that the database was successfully upgraded by connecting to it, and running the SELECT version(); or through the AWS Console RDS Databases list by clicking on the database, and going to the 'Configuration' tab, under 'Engine Version'.  At this point, you should see 9.5.18.

Upgrade the Extensions Manually

After the engine upgrade completes successfully, you'll want to upgrade your extensions.  To run these queries, you can connect to an instance using psql or pgAdmin, depending on your security configuration.  For our example, we'll be upgrading the PostGIS, PostGIS Topology, and PostGIS Tiger Geocoder extensions. If at this point we run the SELECT postgis_full_version(); command, we see the following output:

POSTGIS="2.1.8 r13780" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.1" LIBJSON="UNKNOWN" (core procs from "2.1.5 r13152" need upgrade) TOPOLOGY (topology procs from "2.1.5 r13152" need upgrade) RASTER (raster procs from "2.1.5 r13152" need upgrade)

Notice the upgrade messages in the output above. To upgrade these extensions, you use the ALTER EXTENSION [extension name] UPDATE command. We will run the following commands, one at a time:

ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_tiger_geocoder UPDATE;

These commands should take you up to the latest version for the Postgres 9.5.18 engine.  After running these statements, you should see something similar to the following output:

ALTER EXTENSION

Query returned successfully in 127 msec.

If you receive an error, check below in the 'Troubleshooting' section to see if it matches anything we've seen. If successful though, you can then run SELECT postgis_full_version(); again, and you should see the below output:

POSTGIS="2.2.5 r15298" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.0.3, released 2016/07/01" LIBXML="2.9.1" LIBJSON="0.12" TOPOLOGY RASTER

Make sure at this point that the version of your extensions are up-to-date with the corresponding versions on the AWS RDS Postgres supported extensions page before trying to upgrade to the next major version.  PostGIS is upgraded to 2.2.5, and there are no upgrade messages in our output, so we should be good to go.

That's it.  We've successfully upgraded from 9.4 to 9.5.  You can repeat these steps to upgrade to 9.6.14, and so forth. You'll start by modifying the Postgres Engine just as before, and selecting the 9.6.14-R1 option. We followed this path to 11.4, and our application code continued to work as designed.  Hopefully, you have the same good luck!

Troubleshooting

Errors and Warnings about the postgis.backend

If you see the following error when trying to upgrade PostGIS, you'll want to disconnect from the instance and reboot the RDS instance via the Console.

ERROR:  attempt to redefine parameter "postgis.backend" SQL state: XX000

When the instance becomes available again, reconnect using psql or pgAdmin and re-run the upgrade commands:

ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_tiger_geocoder UPDATE;

If that doesn't fix the problem, you may have other issues.  I found out during my googling that some other extensions can also cause this issue, and you may need to update those before updating PostGIS.

On a couple occasions, I also received the following set of warnings.  However, the upgrades seem to complete successfully. I tested the application afterward, and everything worked for me so these warnings seem benign:  

WARNING:  'postgis.backend' is already set and cannot be changed until you reconnect
WARNING:  'postgis.gdal_datapath' is already set and cannot be changed until you reconnect
WARNING:  'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect
WARNING:  'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect

ALTER EXTENSION

Query returned successfully in 479 msec.

Manually upgrading to specific versions

If you need to upgrade an extension to a specific version, you can append TO "x.x.x" to the end of the statement. You should not need to upgrade to specific versions, as the regular UPDATE statement should bring the extension up to the latest version for the engine you selected. However, if you see an error that says there is no upgrade path, you can manually walk the versions forward using the TO clause.  For example, to upgrade PostGIS to version 2.2.5, you'd use the following statement:

ALTER EXTENSION postgis UPDATE TO "2.2.5";