Migrate PostgreSQL on AWS from RDS to Aurora

Migrate PostgreSQL on AWS from RDS to Aurora
Written by Debarghya DasOctober 29, 2021
11 min read
Amazon Web Service (AWS)
2 VIEWS 0 LIKES 0 DISLIKES SHARE
0 LIKES 0 DISLIKES 2 VIEWS SHARE
Debarghya Das

Data Entry

In this article, we know how to migrate PostgreSQl on AWS from RDS to Aurora..

We started to design and build a system that imports and administrates business data in the main business region, then ships the data as a suite of microservice API endpoints and batch file feeds to the global markets.

The database choice was PostgreSQL and the whole infrastructure is on AWS. Since Aurora PostgreSQL didn’t come into being until right before the project started, the database solution was designed to use RDS PostgreSQL with the same- and cross-region replicas.

In the main market’s AWS region, the master instance serves to write traffic coming from importing upstream data and admin applications, one replica serves in-region API read traffic, while another replica serves all regions’ batch read traffic. In each overseas market, one replica was created to serve that market’s API read traffic.

blogpost

How and How Not

An architectural level change to address the database replica limit is unavoidable. With the fourth overseas market’s rollout date approaching, the fifth, sixth and the seventh overseas market also had their rollout plan laid out.

However, we must tread very carefully. The system has been running on production for nearly 2.5 years, now serving eight client systems from seven countries. The daily traffic could amount to 20 million accesses, any downtime has impact on real users, any read downtime could result in business loss.

We need a solution to:

  1. satisfy the global requirements.
  2. minimize the impact to the live system.

For the first requirement, AWS Aurora Global Database is a low-hanging fruit solution. It supports cross-region read-only replicas in up to five AWS regions (secondary regions as named in AWS docs, as opposed to the primary region where the master database instance resides,) with less than one second cross-region replication latency. As for the number of replicas, it will allow us 15 replicas in the primary region and 16 in each secondary region . Plus the scalability and availability improvement (think about cross-region failover), migrating to Aurora Global Database is a no-brainer.

Geographically it is easy to group the seven (and more to come in the future) overseas markets into less than five AWS regions. Once we have an Aurora database cluster, adding the first secondary AWS region will automatically turn it into an Aurora Global Database.

blogpost

The only problem is, our PostgreSQL database was still on version 9.6.11. Although the lowest Aurora compatible PostgreSQL is 9.6.8. Aurora Global Database is only compatible with PostgreSQL 10.11, 10.12, and 11.7 up.

For some more buffer time before the next major version upgrade, we chose the newest Aurora compatible PostgreSQL version in our target AWS regions, 11.8, as the upgrade target version.

With AWS CLI, the following command checks available engine versions of Aurora PostgreSQL in a particular AWS region:

 $aws rds describe-db-engine-versions --engine aurora-postgresql --query '*[].[EngineVersion]' --output text --region <your-aws-region> 

Also with AWS CLI, the following commands check available upgrade target versions from a given PostgreSQL version in a given AWS region :

First, export the AWS region and that region’s RDS endpoint as the terminal tool’s environment variables:

$export REGION=<you-aws-region>
$export ENDPOINT=https://rds.<your-aws-region>.amazonaws.com

Then, to check available upgrade target versions for RDS PostgreSQL from a given version, use this:

 $aws rds describe-db-engine-versions \
--engine postgres \
--region $REGION \
--endpoint $ENDPOINT \
--output text \
--query '*[].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].{EngineVersion:EngineVersion}' \
--engine-version <your-start-version>

To check available upgrade target versions for Aurora PostgreSQL from a given version, use this:

$aws rds describe-db-engine-versions \
--engine aurora-postgresql \
--engine-version <your-start-version> \
--query 'DBEngineVersions[].ValidUpgradeTarget[?IsMajorVersionUpgrade == `true`]'

This is how we found out that it is impossible to upgrade 9.6.11 directly to 11.8, no matter in RDS or Aurora. We have to first upgrade to 10.x, then 11.8.This is how we found out that it is impossible to upgrade 9.6.11 directly to 11.8, no matter in RDS or Aurora. We have to first upgrade to 10.x, then 11.8.

Minimize User Impact

To achieve the least user impact, especially to avoid potential business loss, we set the goal of zero read downtime, and minimizing write downtime as much as possible.

According to AWS doc, there are two ways to migrate from RDS to Aurora:

  1. Migrate from an RDS PostgreSQL DB snapshot, or
  2. Create an Aurora read replica from an RDS instance, then promote it to be an independent Aurora cluster

Both options can achieve zero read downtime, since the existing RDS DB can keep serving the read traffic until the Aurora cluster is ready, then we just switch the applications’ DB connections to the new Aurora cluster.

As for the write downtime, at first glance, the second option looks more appealing.

Option one requires us to stop all DB write operations (importers, admin applications) as soon as we start to create the RDS snapshot, otherwise we would lose all the changes after this point in the new Aurora cluster.

In contrast, option two allows us to keep accepting writes even after the migration starts. The Aurora read replica will replicate all changes from the RDS master after it is created. We only need to stop write operations at this point, wait for it to catch up with the master, and promote it to be an independent Aurora cluster. Then we can switch all read and write applications to the new Aurora DB, then resume the write operations.

However, the AWS doc failed to mention one key fact, which we had to find out only in our first staging rehearsal. In our use case, this rendered option two essentially impossible:

If the RDS master instance has cross-region replicas, no Aurora read replica can be created.

blogpost

In the end, we had to go with option one, despite the longer write downtime.

Tune It

After migrating on the staging environment, we noticed severe write performance degradation in the new Aurora DB. Compared with using RDS, importing the same amount of data from upstream now takes ten times longer.

Two operations resolved the issue:

  1. Run ANALYZE to refresh the pg_statistic table (which was recommended in AWS doc for PostgreSQL major version upgrade, both RDS and Aurora)
  2. Tune the Aurora parameter group according to our data’s characteristics (mainly some Autovacuum related parameters needed to be adjusted)


Conclusion

Our migration procedure turned out like this:

  1. Stop all DB writing applications/batch jobs
  2. Create the RDS Snapshot
  3. Create Aurora cluster from the RDS snapshot
  4. Create Aurora instances
  5. Upgrade the Aurora cluster from 9.6.11 to 10.11
  6. Upgrade the Aurora cluster from 10.11 to 11.8
  7. Test new cluster
  8. Add regional clusters
  9. Connect test applications to the new DB clusters
  10. Application test
  11. Switch production applications to the new DB clusters
  12. Resume DB writing applications/batch jobs


AWS
Aurora
Rds
Cloud
Database
2 VIEWS 0 LIKES 0 DISLIKES SHARE
0 LIKES 0 DISLIKES 2 VIEWS SHARE
Was this blog helpful?
You must be Logged in to comment
Code Block
Techiio

Techiio is on the journey to build an ocean of technical knowledge, scouring the emerging stars in process and proffering them to the corporate world.

Follow us on:

facebooklinkdeintwitter

Subscribe to get latest updates

You can unsubscribe anytime from getting updates from us
Copyright techiio.com @2020 Kolkata, India
made with by Abhishek & Priyanka Jalan
Copyright techiio.com @2020
made with by Abhishek & Priyanka Jalan