AWS RDS Upgrade — Multi major version upgrade (9.5.20 to 12.2)

As AWS RDS started supporting the multi major version upgrades for PostgreSQL DB Engine, it’s possible to upgrade across the different major versions in a single step. Previously, AWS only allowed to upgrade all the major versions one by one until the target version reached. For example, to upgrade from 9.5.21 to 12.2, manually we need to upgrade from 9.5.21 to 9.6, and then 9.6 to 10 and then 10 to 11 and then from 11 to 12.2. But with the multi major upgrade feature of RDS, upgrading from 9.5.21 to 12.2 version can be done by skipping all the major upgrade steps (ie) skipping to upgrade from 9 to 10 and then from 10 to 11 or till the target version is reached and it can be done seamlessly in a single step from 9.5.21 to 12.2 using manual upgrade of the DB engine. Big advantage of using this feature is to achieve the minimum downtime as opposed to going each major version one by one.

DB tools that was needed to connect and work with DB instances are

1. GUI PostgreSQL Client — Postico (MAC) or PgAdmin(Windows/MAC) or DBeaver(Windows/Linux)
2. Command Line Client — psql client (for the Ubuntu)

To connect the DB instance using any of the db client, host name, username, password and Database name are required (Checkout the connectivity and Configuration details of the DB instance to get these details)

1. Host name is the Endpoint of the DB instance
2. Username is the Master username of the DB instance
3. Password is the password provided while creating the instance
4. DB name is the initialize DB created in the instance

Basically, AWS RDS user guide provides all the necessary steps to carry out the multi major upgrades. Checkout AWS guide.

Also, the documentation talks about the associated target major versions for each DB engine version. For example, 9.5.20 can only be upgraded to 11.6 as a maximum major version and not to the 12.2 and 9.5.21 can be upgrade to 12.2 as its major version.

So, in this article, we will see the three methods to upgrade the PostgreSQL DB Engine of RDS 9.5.20 to 12.2

First Method is using the steps described in the AWS documentation. Since it didn’t successfully upgrade, leverage the snapshot upgrade as a second approach(unfortunately it also failed). Finally, a RDS with PostgreSQL DB engine of 12.2 is setup using the good old pg_restore utility directly in the RDS host. As a matter of fact, the first and second method uses pg_upgrade utility under the hook.

Method #1: Create a dry run DB instance and then upgrade

AWS suggests us to create a dry run instance and do the upgrading. If everything goes well, the production DB instance can be upgraded with confidence and minimize the downtime. To do that, Let’s assume the DB instance that needs upgrade is called myapp-prod-9.5.21.

1. Find out which major version is supported for the current version as mentioned in the AWS documentation.
2. Do the minor upgrade if needed. In our case, the upgrade is from 9.5.20 to 12.2 as I mentioned earlier. AWS 9.5.20 engine version does not have an option to go directly to 12.2. So a minor upgrade to 9.5.21 has to be done to reach the target version 12.2. To do that, Select the myapp-prod-9.5.21 DB instance and click on Modify button and choose the next minor version as needed (9.5.21) and then click Apply Immediately for the upgrade as mentioned in the following guide.
3. Create a snapshot (using Action button) for the myapp-prod-9.5.21 DB instance. Let’s name the Snapshot as myapp-snapshot-before-major-upgrade-snapshot. The version of the snapshot will be 9.5.21.
4. Have a parameter group ready (in our case, the default parameter group, which is used to define the setting of an engine, was sufficient and hence didn’t create any group).
5. To create a dry run DB instance (which is used to test the upgrading feature), restore the snapshot using myapp-snapshot-before-major-upgrade-snapshot. The restore basically creates a new DB instance with 9.5.21 engine version. Let’s assume the restoring DB instance name as myapp-dry-run-12.2. Now we got the duplicate testing DB instance same as the original version.
6. Now, Let’s do major upgrade in the myapp-dry-run-12.2 DB instance and then test the data integrity by connecting to the our application.

Preliminary Steps before the upgrade (Connect to myapp-dry-run-12.2 DB instance using Postico or PgAdmin or DBeaver)

1. Make sure the there is no prepared transactions (ie) Run the following query in the Postico and make sure the count returns zero.

SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

2. Make sure that there are no uses of the line data type by the running the following query in the Postico and make sure the count returns zero.

SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a
WHERE c.oid = a.attrelid
AND NOT a.attisdropped
AND a.atttypid = ‘pg_catalog.line’::pg_catalog.regtype
AND c.relnamespace = n.oid
AND n.nspname !~ ‘^pg_temp_’
AND n.nspname !~ ‘^pg_toast_temp_’
AND n.nspname NOT IN (‘pg_catalog’, ‘information_schema’);

3. Remove all uses of the reg* data types by running the following query in the Postico and make sure the count again returns zero.

SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a
WHERE c.oid = a.attrelid
AND NOT a.attisdropped
AND a.atttypid
IN (‘pg_catalog.regproc’::pg_catalog.regtype, ‘pg_catalog.regprocedure’::pg_catalog.regtype, ‘pg_catalog.regoper’::pg_catalog.regtype, ‘pg_catalog.regoperator’::pg_catalog.regtype, ‘pg_catalog.regconfig’::pg_catalog.regtype, ‘pg_catalog.regdictionary’::pg_catalog.regtype)
AND c.relnamespace = n.oid
AND n.nspname
NOT IN (‘pg_catalog’, ‘information_schema’);

4. Handle Read Replica if applicable (Skipping this part as we didn’t have any replicas)

5. Upgrade the certain extensions before the major version upgrade. As per the AWS documentation, certain extensions (like address_standardizer, postGIS etc..) need to be upgraded before the Engine upgrade itself.

To see the extensions available in the DB, run the query.

SELECT * FROM pg_extension

Run the following query to upgrade each extension.

ALTER EXTENSION PostgreSQL-extension UPDATE TO ‘new-version’

6. Drop the certain extensions before the major version upgrade and reinstall safely to a compatible version. Certain extensions are not supported in the multi major upgrade like pgRouting. For the extension versions that needs upgrade, checkout PostgreSQL Extensions and Modules Supported on Amazon RDS.

7. Now, do the major version in the dry-run-12.2 using the manual DB upgrade.

8. Wait and keep checking the state of DB the instance (that goes from upgrading to Available). Once the Available state is reached, make sure DB engine shows 12.2. If the upgrade fails, it just shows the old version and it might have created the log event. By checking the log event of the db instance or CloudWatch Logs, we may come to know the error that occurred during the upgrade.

10. If the dry-run major version upgrade is successful, connect to the application using myapp-dry-run-12.2 DB instance (in our case, we were planning to connect the staging application) and make sure to test thoroughly the myapp-dry-run-12.2 instance to verify that the applications work correctly before applying the upgrade to the production DB instances.

11. Once the application runs smoothly with new upgrades, then upgrade the production database with confidence using Manually Upgrading the Engine Version.

12. Once Production is good to go, delete the myapp-dry-run-12.2 DB instance

Unfortunately, myapp-dry-run-12.2 DB instance didn’t successfully get upgraded to 12.2 for us. The log event showed the following error message that “Database instance is in a state that cannot be upgraded: Postgres cluster is in a state where pg_upgrade can not be completed successfully.” Also, AWS log mentioned that backtrace of the error is written to the file called `pg_upgrade_dump_16389.log` but unfortunately AWS doesn’t provide the access to see the log file as mentioned on the log events. Googling also doesn’t help to get the access. After spending couple of hours trying to access the log file, decided to go with the second approach (to upgrade the snapshot and then restore db instance from the upgraded snapshot)

Method #2: Upgrading the snapshot

There is an option to upgrade the snapshot and then restore a new DB using the upgraded snapshot. Most likely if the method #1 fails, the method #2 will also fail for the same reason but in this approach, there is no logs to checkout the result.

Since both approaches failed, wanted to try using the good old manual pg_dump/ pg_restore utility directly in the DB instance

Method #3: pg_dump/pg_restore utilities

1. Create a new DB instance (myapp-prod-12.2) with the target major version(12.2). Make sure that the master username(my-app), password(zsertzue=) and initial db name (my-db)has been configured in this step.
2. Make sure that the local machine or EC2 instance got the psql client. I connected to myapp-prod-12.2 instance using the psql client of an Ubuntu EC2 instance to make sure the DB(my-db) that mentioned in the initial DB setup is available. If not, execute the command in psql client to create the DB where the data needs to be restored
   Create the DB using `CREATE DATABASE my-db;`
3. Take a note of the host name, master username, password and db name of both the old and new DB instance (myapp-prod-9.5.21, myapp-prod-12.2) for executing pg_dump and pg_restore utility.
4. Execute the pg_dump utility for the source db instance( one with 9.5.21) to create a dump file as a backup.

pg_dump -Fc — no-acl — no-owner -h old-db-9.5.21-host -U my-app my-db > myapp-production.dump

Provide the password that it prompts to enter

5. Execute the pg_restore utility for the target db instance (one with 12.2). It takes couple of hours to restore the data myapp-prod-12.2 (ours took 4 hours)

pg_restore — verbose — clean — no-acl — no-owner -h new-db-12.2-host -U myapp -d my-db myapp-production.dump

Provide the password that it prompts to enter

Testing the new upgraded DB instance

Once the pg_restore got completed, connect the new instance using the staging application and make sure the data is similar to the current old version production db. There are tools that can used to check the data integrity but those are out of this article’s scope. Once everything seems good, just reconfigure the DB setting of the production app to use the new upgrade DB instance.

Voilà! Now We have a brand new shining DB instance to make use of the new features. In our case, we wanted to upgrade to improve the DB query performance using GIST Indexing/K-nearest neighbor concept and SLQ-JSON path expression support.