A Cloud Architect Company
Amazon Aurora PostgreSQL
Amazon Web Services

How upgrade Amazon Aurora PostgreSQL latest version with zero to minimal downtime using AWS DMS

In our previous blog we could see how to upgrade RDS MySQL  version to latest version with zero downtime using AWS DMS.

In this blog we are going to discuss about upgrade the Amazon Aurora PostgreSQL version from 10.21 to latest version(for now 14.5) using DMS.

We just follow the same but little different procedure from the previous blog to complete this requirement.

Create Parameter Groups

Open your AWS console and search for RDS and click it.

On the left side of the RDS page you can see parameter groups. Enter into the section and Click Create parameter group button in the top right corner of the page.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Create parameter group

  • First we have to create parameter group for aurora PostgreSQL version 10 for old version of database.
  • Choose the Parameter group family to aurora-postgresql10.
  • Enter Group name to cluster-aurora-postgresql10 or whatever you want to provide as your convenience.
  • Then provide a description about the parameter group then click Create.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Create parameter group

Now select the parameter group you just created, and click Parameter group actions -> Edit.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit parameter group

Search for the following parameters and change the values of them with the below values like the screenshots below.

  • rds.logical_replication        = 1
  • wal_sender_timeout           = 0
  • rds.log_retention_period   = 7200
  • max_replication_slots        = 20
  • session_replication_role    = replica

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit Logical Replication

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit Wal Sender Timeout

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit Log Retention Period

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit Max Replication Slots

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit Session Replication Role

Once completed the all changes in the parameters click the Preview changes button like the above picture to view the changes.

You can see the changes in the parameters, click Save changes.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Preview Changes

Create another parameter group for Aurora PostgreSQL version 14 for latest version.

  • Choose the Parameter group family to aurora-postgresql14.
  • Enter Group name to cluster-aurora-postgresql14 or whatever you want to provide as your convenience.
  • Then provide a description about the parameter group then click Create.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Create Parameter Group Latest version

Select the Parameter group for latest PostgreSQL version and click Parameter group actions -> Edit.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit Parameter Group Latest version

Change the following parameters’ values and click Save changes.

  • rds.logical_replication        = 1
  • wal_sender_timeout           = 0
  • rds.log_retention_period   = 7200
  • max_replication_slots        = 20

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Preview CHnages Parameter Group Latest version

Create Aurora PostgreSQL DB Clusters

Aurora PostgreSQL DB Cluster with version 10.21

On the left navigation section under the Amazon RDS Click the Databases section followed by Create database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Create Source Database

Select the standard create option. For Engine choose Amazon Aurora engine.

For Edition choose Amazon Aurora PostgreSQL-Compatible Edition.

And for the Available versions, choose an old version which is available the time when you working. For now we select PostgreSQL 10.21.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Choose Engine Source Database

For Templates choose the Free tier section. It will reduce the cost for creating a database. Here we are going to create a database for testing purposes only. So free tier is enough for now.

Under Settings, for DB instance identifier, provide a name like source-cluster for your database instance.

For master username enter postgres and Master password provide a password for your database username and type the password again for confirmation.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Settings Source Database

Under the Instance configuration, for DB Instance class select Burstable classes and choose the least size for this demo.

For Availability & durability choose Don’t create an Aurora Replica.

  • If you choose the other one it will create a new reader instance in a different availability zone for scalable and high available. It will cost higher than the first option.
  • In production scenarios we should follow the Aurora Replica creation method for high availability. But for now just run with single instance.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Configurations Source Database

  • For the Connectivity section, first select Don’t connect to an EC2 compute resource option. We can configure it later.
  • Next the network-type choose IPv4, and VPC, choose any VPC as you want to use. For myself I choose the default one.
  • For the DB subnet group select default.
  • For Public access, choose yes. Then only we can connect with our database from outside of the VPC.
  • Then for the Security group select the Create new option and provide a Name for it.
  • And choose any of the availability zone that you want.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Connectivity Source Database

Scroll down and expand the Additional configuration section.

For Initial database name, enter a name for database.

Under the DB parameter group choose the parameter group which you create from the beginning of this tutorial for Aurora PostgreSQL version 10.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Select Parameter Group Source Database

Leave the all other things as default, then click Create database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Default Source Database

Now the source database cluster is creating with a database instance.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Creating Source Database Cluster

Aurora PostgreSQL DB Cluster with latest version

Again we are going to create a PostgreSQL database cluster with the latest version. So click Create database.

For Engine type, choose Amazon Aurora.

For Edition choose Amazon Aurora PostgreSQL-Compatible Edition.

And for the Available versions, choose an latest version which is available the time when you working. For now we select PostgreSQL 14.5.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Choose Engine Target Database

Choose Dev/Test for Templates section.

Under Settings, fill all the things like the below screenshot.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Settings Target Database

For instance class choose Burstable classes for this demo purpose and for the Multi-AZ deplyment, choose Don’t create an Aurora Replica.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS System Config Target Database

Choose a VPC which you select for the old version database cluster for VPC. Follow the same for the DB Subnet group.

Click Yes for Public access.

For VPC security group, click Choose existing and choose the security group which is created by the previous database.

And finally for the Availability zone, choose the same availability zone which is you created your old version database cluster.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Connectivity Target Database

Under the Additional configuration, Enter a database name for Initial database name and for DB cluster parameter group, choose the parameter group you created earlier for latest PostgreSQL version.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Parameter Group Target Database

Leave all others as default and click Create database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Create Target Database

Now the second DB cluster with a DB instance also creating. Let them to complete the creation.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Creating Source and Target Databases

Modify the Security group Inbound Rule

In the mean time lets modify the Inbound rule in the security group of the our database.

Go to the VPC section in AWS console and select Security groups in the left navigation section.

Choose the security group that you created with the database and select Inbound rules and click Edit inbound rules.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit DB Security Group

Change the Source to Anywhere and add ‘0.0.0.0/0′ like the picture below. Then click Save rules.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Edit DB Security Group Inbound Rule

Create Table in Source Database

Navigate to the RDS Databases section and you can see all the DB clusters and the DB Instances are created successfully.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Source DB status Available

Select the Source DB Cluster, under the Configuration section, You can see the Engine version. So you can confirm this Db cluster is the older version of the PostgreSQL.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Source DB Configuration

Select the Source DB Cluster, under the Connectivity & security section, you can find the endpoints of your DB Cluster.

Copy the endpoint for the Writer instance and note it in somewhere else.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Source DB Endpoints

Connect to your EC2 instance via SSH connection and login as a root user.

This Instance is should be in the same VPC as this DB cluster.

Then run the following command with your source DB Cluster Endpoint.

psql --host=<source_db_endpoint> --port=5432 --username=<db_username> --password --dbname=<db_name>

It prompt for password for the db user.

Enter the following commands to create a table called ‘COMPANY‘ and insert a row of data into the table.

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);

Run ‘\d‘ command to list the tables inside the database.

Run the below command to see the contents of the table.

select * from COMPANY;

Run command ‘exit;‘ to exit from the database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Login to Source PostgreSQL DB Create Table

Now select the target DB cluster, and under the Configuration  section, you can find the database version. For this scenario latest version of PostgreSQL is 14.5.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Target DB Configuration

Under the Connectivity & security section, copy the Endpoint for the type of Writer Instance.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Target DB Endpoints

Login into the target database with the following command.

For target_db_endpoint, enter the endpoint name of the target cluster.

psql --host=<target_db_endpoint> --port=5432 --username=<db_username> --password --dbname=<db_name>

Run ‘\d‘ command to see the list of tables. You cannot able to find any table in target database. Because right now we doesn’t creates any tables here.

So run ‘exit‘ command to logout from the target database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Login to Target PostgreSQL DB

Database Migration Service

Now we are going to migrate the Source Database to Target database using DMS.

We now follow the same procedure like the previous blog.

Create Replication Instance

Click the below link and follow the process to create a Replication Instance for Database Migration Process.

Link for creating replication instance

For creation of replication instance time will took around 10 to 15 minutes. Wait until the creation is completed.

Create Source and Target Endpoints

Once the replication instance created click the Endpoints in the left navigation section and then click Create endpoint.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Create Endpoints

For endpoint type choose Source endpoint and check right for Select RDS DB instance.

For RDS Instance, choose the source DB cluster with the old PostgreSQL version.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Choose Source Endpoint

  • Under the Endpoint configuration, for Endpoint identifier and Source engine are automatically filled with its values.
  • For Access to endpoint database, choose Provide access information manually.
  • Enter only the Password for the database. All other things are automatically filled.
  • Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Source Endpoint ConfigurationScroll down to the bottom and expand the Test Endpoint connection.
  • choose the same VPC that you using in this demo and choose the Replication instance that you created in the previous step.
  • Click the Run test button to check the connection with the database.
  • If the Status is showing successful  like the below picture, Click the Create endpoint  button.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Source Endpoint Test Connection

Endpoint for the source database is now Active.

Now click Create endpoint  to create target endpoint for the target database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Source Endpoint Created

For endpoint type choose Target endpoint and check right for Select RDS DB instance.

For RDS Instance, choose the target DB cluster with the latest PostgreSQL version.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Choose Target Endpoint

  • Under the Endpoint configuration, for Endpoint identifier and target engine are automatically filled with its values.
  • For Access to endpoint database, choose Provide access information manually.
  • Enter only the Password for the database. All other things are automatically filled.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Target Endpoint Configuration

  • Scroll down to the bottom and expand the Test Endpoint connection.
  • choose VPC and Replication instance.
  • Click Run test and if the connection was successful , Click the Create endpoint button.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Target Endpoint Test Connection

All the two endpoints are created successfully and in Active status.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Created Source and Target Endpoints

Create Database Migration Task

Now its time to create Database migration task.

Navigate to the Database migration tasks section and click Create database migration task.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Creating Database Migration Task

  • For Task identifier, provide a name for the task.
  • Choose the replication instance which we created for the Replication instance.
  • For Source database endpoint, choose the endpoint you have created for source and for the Target database endpoint, choose the endpoint you’ve created for target.
  • For the Migration type, choose Migrate existing data and replicate ongoing changes.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Database Migration Task Configuration

Scroll down and under the Task settings, for the Editing mode choose Wizard.

Change the followings and keep the default values to the remaining options:

  1. Target table operation mode – Truncate
  2. Turn on validation – Enable
  3. Turn on Cloudwatch Logs – Enable

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Database Migration Task Settings

  • Scroll down under the Table mappings choose Wizard for Editing mode.
  • Expand Selection rules and Click Add new selection rule.
  • For Schema select Enter a schema.
  • Leave all other things as default. So all tables inside the source database will be migrated to target database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Database Migration Table Mappings

Scroll down to bottom and click Create task.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Database Migration Create Task

The migration task getting created. Wait until it shows Running.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Database Migration Task Creating

Once the creation is completed, you can able to see the Status of the task with Load compile, replication ongoing.

Click the Table statistics, you can see the Load state is like Table completed.

So the Database migration process is successfully done.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Database Migration Task Table Statistics

For check the migration, login to the target database and run the following command to see the contents is migrate to this database from source.

\d
select * from COMPANY;

You can see the table is created and the contents also stored in the target database.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Check Migration in Target DB

Check the Replication

In Source Database

First login to source database and run the following command to add some new rows into the company table.

INSERT INTO COMPANY VALUES (2, 'Arun', 28, 'New York', 20000.00), (3, 'Abishek', 36, 'Chicago', 42000.00), (4, 'Sarath', 26, 'Los Angeles', 25000.00), (5, 'Xavier', 42, 'San Francisco', 39000.00);

Run the below command to see the add rows inside the table.

select * from COMPANY;
exit;

As you can see the table contains the total of 5 rows. Now check whether it will replicated into the target database or not.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Adding Rows Inside Source Database

In Target Database

Login into the target database and run below command to see the replication.

select * from COMPANY;
exit;

You can see the now the table in the target database also has 5 rows.

So all the contents that will add or remove in the source database will also replicate into the target database also.

Upgrade Aurora PostgreSQL latest version with 0 Downtime using DMS Check Replication in Target Database

Final Step

To this point whenever changes or updates are made from the old version source database are parallelly replicated to the secondary database which is the latest PostgreSQL version. But we need to set up the latest PostgreSQL version database as the primary one. For this just point the upgraded database as the primary database inside the application code. Now the application will use the database which was upgraded to the latest version. Then delete the old Aurora PostgreSQL version database. 

We are successfully upgrading the RDS Aurora PostgreSQL database to the latest PostgreSQL version with nearly zero downtime. During this process there will no downtime in your application.

That’s all I want to show you. See you in the next article. Thank you..

Article written by:

Jerin is working as a Junior Cloud Automation Engineer at EasyDeploy Technologies Pvt. Ltd. He is writing terraform scripts that creates services on AWS cloud. He can able to handle CI-CD process using AWS CodePipeline.

Leave a Reply

Your email address will not be published. Required fields are marked *

back to top

Contact Us to save your AWS bill by 40%

X