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 the RDS MySQL version to the latest version with zero downtime using AWS DMS.

In this blog, we are going to discuss upgrading the Amazon Aurora PostgreSQL version from 10.21 to the 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 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 a parameter group for aurora PostgreSQL version 10 for an old version of the database.
  • Choose the Parameter group family to aurora-postgresql10.
  • Enter the Group name to cluster-aurora-postgresql10 or whatever you want to provide at your convenience.
  • Then provide a description of 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 their 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 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 the latest version.

  • Choose the Parameter group family to aurora-postgresql14.
  • Enter the Group name to cluster-aurora-postgresql14 or whatever you want to provide at your convenience.
  • Then provide a description of 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 the 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 the Engine choose the Amazon Aurora engine.

For Edition choose Amazon Aurora PostgreSQL-Compatible Edition.

And for the Available versions, choose an old version that is available at 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 availability. 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 a single instance.

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

  • For the Connectivity section, first, select the Don’t connect to an EC2 compute resource option. We can configure it later.
  • Next, the network-type chooses IPv4, and VPC, and choose any VPC 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 zones 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 the Initial database name, enter a name for the database.

Under the DB parameter group choose the parameter group which you create at 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 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 created with a database instance.

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

Aurora PostgreSQL DB Cluster with the 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 the latest version which is available at 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 in 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 deployment, 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 the 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 in which 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 the DB cluster parameter group, choose the parameter group you created earlier for the 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 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 meantime, let’s modify the Inbound rule in the security group of our database.

Go to the VPC section in the 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 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 somewhere else.

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

Connect to your EC2 instance via SSH connection and log in 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 prompts for a 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 the ‘\d‘ command to list the tables inside the database.

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

select * from COMPANY;

Run the 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 the ‘\d‘ command to see the list of tables. You cannot able to find any table in the target database. Because right now we don’t creates any tables here.

So run the ‘exit‘ command to log out 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 the Target database using DMS.

We now follow the same procedure as 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 the creation of the replication instance, time will take around 10 to 15 minutes. Wait until the creation is completed.

Create Source and Target Endpoints

Once the replication instance is 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 to 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 the 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 to 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 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 it’s time to create a 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 the Source database endpoint, choose the endpoint you have created for the source and for the Target database endpoint, choose the endpoint you’ve created for the 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 following 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 and 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 the target database.

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

Scroll down to the bottom and click Create task.

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

The migration task gets 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 the 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

To check, the migration, log in to the target database and run the following command to see if the contents are migrated to this database from the source.

\d
select * from COMPANY;

You can see the table is created and the contents are 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 the source database and run the following command to add some new rows to 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 a total of 5 rows. Now check whether it will be replicated in 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 the below command to see the replication.

select * from COMPANY;
exit;

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

So all the contents that will add or remove from the source database will also replicate in 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 is 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 to 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 Rathnam is a proficient DevOps engineer who is dedicated to streamlining software development and deployment processes. He has extensive knowledge of cloud infrastructure, containerization, and CI/CD pipelines, which enables him to effectively connect development and operations. Jerin specializes in creating numerous Terraform modules for multi-cloud infrastructure and possesses immense expertise in configuring and managing cloud infrastructure. His profound understanding of containerization, along with his experience in orchestration tools like Docker and Kubernetes, further supports his skills as a valuable DevOps engineer.

Leave a Reply

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

back to top
advanced-floating-content-close-btn

Contact Us to save your AWS bill by 40%

X