How to upgrade major version of PostgreSQL in AWS RDS.

In this article, I will share how upgraded the PostgreSQL from 11.9 to 12.4 in AWS RDS.

Make sure that you thoroughly test after upgrade in the testing environment to verify that your applications work correctly before applying the upgrade to your production DB instances. Major version upgrades can contain database changes that are not backwards-compatible with existing applications.

Checklist to follow before Major version upgrade

1. Take a Backup of the latest DB
2. If you are using a custom parameter group upgrade it to the latest version
3. Make sure that you have read release notes of the version you are going to upgrade

Start Upgrading

Steps followed are done in a testing environment, as I have created a new database using Restore to point in time from the existing testing DB, so developers can use the application without downtime as the app is pointed to the testing DB.

1. Sign in to the AWS Console and open the RDS console at https://console.aws.amazon.com/rds/
2. Select the DB identifier which is to be upgraded in AWS RDS console and click Modify.
3. AWS will suggest the recommended upgrade PostgreSQL version for the current version, You can choose it from DB engine version.
4. Update the DB instance identifier name
5. Choose the DB parameter group from Additional configuration section, by default it will be as default.postgres12, if you have custom parameter group choose it.
6. Other details are auto-filled from the existing DB configuration.
7. Click continue, you will see the Summary of modifications verify the modification you have made.
8. Select Apply immediately from Scheduling of modifications and click Modify DB instance.

From here AWS will take care of the upgrade, based on DB size it will take some time to complete the upgrade. Once the upgrade is completed the status will be changed to Available in the RDS console. But the upgraded database is not ready to use!!!

After the Upgrade, if you connect the database to the app, you can see that some queries will take a long time and your app might become very very slow as compared to the previous performance, some API might fail due to query running for a long time! But we can fix these by doing the After upgrade steps.

Steps to do after Upgrade

• Run REINDEX, The REINDEX command rebuilds one or more indices, replacing the previous version of the index.

Recreate all indices in the database -> REINDEX DATABASE database_name

• Once REINDEX is completed, Run ANALYZE.

ANALYZE gathers statistics for the query planner to create the most efficient query execution paths. This command will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing.

ANALYZE -> This will be run on available tables in the current schema that the user has access to.

• Once the ANALYZE is completed, Upgrade your extensions. As PostgreSQL engine upgrade doesn’t upgrade most of the PostgreSQL extensions. Use the below command to upgrade your extensions.

ALTER EXTENSION extension_name UPDATE TO ‘new_version’

Issues

My application is hosted in Beanstalk (without a load balancer) was using SSL for connecting to PostgreSQL DB instance, so it raised the following issue

SQLSTATE[08006] [7] SSL SYSCALL error: Connection reset by peer

After some hours of debugging, I found that issue raised is due to ssl_min_protocol_version which is added in PostgreSQL 12 parameter group, by default it is set to TLSv1.2, But the Apache HTTP Server used TLSv1 for connecting to PostgreSQL DB instance, so the connection was reset by peer due to mismatch on TLS protocol, To fix this I created a custom parameter group and updated ssl_min_protocol_version to TLSv1 After this is change, the connection issue was resolved!

Now we have upgraded to PostgreSQL 12 successfully.