PostgreSQL upgrade from 11.x to 15.x

I had a opportunity to upgrade one of our postgreSQL DB engine from 11.x to 15.x which was hosted in RDS.

In this blog , I am going to share my real-time experience .

Initial Analysis Initially we need to understand , how the database data is used and who are the consumers of the data. Based on your analysis , we can plan the upgrade strategy , correct time to upgrade the database.

Our database was critical and it was hosted on AWS RDS. Recently AWS RDS introduced new strategy called Blue/Green deployment and we decided to use that.

With this initial analysis , we should understand the release notes of all new major versions 12.x ,13.x ,14.x ,15.x as we have to upgrade from 11.x to 15.x

What is Blue/Green deployment?

From AWS documentation , https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html

“A blue/green deployment creates a staging environment that copies the production environment. In a Blue/Green deployment, the blue environment is the current production environment , the green environment is the staging environment . The staging environment stays in sync with the current production environment using logical replication .”

When we initiate blue/Green deployment from RDS console , Read-Replica DB will be created and your original DB will be tagged as “Blue DB” , the read-replica will be tagged as Dbname-greenDB and there is no modification in the blue-DB configuration properties like endpoint . Data will be replicated to green DB from blue DB. The blue DB will be serving your live traffic.

Now we can upgrade the greenDB postgreSQL engine to 15.x . You can make note of the GreenDB endpoints and connect your test environment and test your application. If all the functionalities of your application are working , then you can do “Switch over”.

How to Initiate Blue/Green deployment ? We can initiate the blue/green deployment from the AWS RDS console . Without any downtime , the blue/green deployment can be initiated from AWS RDS console .Select database and click actions , you will get “create new Blue/Green deployment " option.

Prices involved with the Blue/Green deployment There is no cost increase for the Blue/Green deployment. You will be billed for the GreenDB RDS instance . RDS instance are billed per second.

Blue/Green deployment Limitations Blue/Green deployment limitation based on the restriction of PostgreSQL logical replication ref doc - https://www.postgresql.org/docs/current/logical-replication-restrictions.html

Few limitations are , 1. Data definition language(DDL) statements are not replicated . 2. Before creating blue green deployment , make sure all the tables has primary key. To understand all the limitations , refer https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html#blue-green-deployments-limitations-postgres

How to Test Blue/Deployment or DB upgrade First , I initiated blue/deployment for our test environment and I got greenDB for our test environment .

I upgraded the PostgreSQL engine version . While upgrading , the RDS will run the pre-compatibility check and it any of our Database objects or queries not compatible , then it will not upgrade . To check the error go to RDS console , monitoring tab and download the logs.

If the upgrade is done successfully , then configure our application to make connection with green DB . Green DB is READ_ONLY Database. So I did the below verification. 

• First, ensure that the PostgreSQL JDBC driver is compatible with the new version of the PostgreSQL engine by verifying that the application layer connects to the database without any exceptions or errors.
• Tested the read only queries by getting data from the application API.

Once the verification is good , then click on switch over and green DB become blue and blue become old blue DB.

What will happen during switch over ? you need to expect downtime for 5-15 mins during switch over .The downtime can be configured in the console . For Example, if the switch over is happening more than 15 mins and you don’t want downtime for more than 15 mins , you can configure that .

During switch over,The blue DB all the connections will be dropped and blue DB to green DB data sync will happen.

And Blue DB name and endpoint will be modified to blue-old1.The green DB name modified to blue DB name.

For example , our DB name is Test ,

Before switchover -  Test (blueDb) ,  Test-Green (greendb) 
After switch over -   Test (blueDb) --->  Test-old1 (old blue),    Test (new blue) 

The new blue will be serving your traffic from application .

The below figure illustrates the view after switchover for a database which has one Readreplica