An online marketplace company managed a 6-terabyte database using an old version of Amazon RDS PostgreSQL version 9.3. With the gaining popularity of their platform, they needed a way to scale the performance and size of their database near term to 15-terabytes. They also had several performance issues including a daily batch script that was taking almost 2 hours to complete that was starting to exceed the maintenance window, and simple tasks like adding a new column to their largest table would time out. The main challenge for the migration project was that downtime could not exceed 6 hours and test efforts by their DBA team could never meet the requirement.
In this blog post, we will walk through two things:
- The various options for upgrading their PostgreSQL 9.3 server to 9.6
- The process of migrating them to Amazon Aurora from RDS
Please read on to see how we were able to complete the entire migration process in under 3 hours and take advantage of the performance of Amazon Aurora for PostgreSQL.
Why migrate from Amazon RDS PostgreSQL to Amazon Aurora?
Our client after reading the Overview of Amazon Aurora documentation topic, was primarily interested in two key capabilities:
- High Performance at Scale where Amazon claims 3X higher throughput than PostgreSQL on similar hardware.
- High Availability with multi-AZ deployments, self-healing storage, and automatic backups.
The problem that our client has was that all of their attempts to migrate to Amazon Aurora from their existing Amazon RDS PostgreSQL 9.3 database either failed or greatly exceed their maintenance window. As an e-commerce provider, excessive downtime means a revenue loss and loss of confidence. So, we had to find a better way to fit into allowed downtime window.
Upgrading PostgreSQL from 9.3 to 9.6.3 to prepare for Amazon Aurora
The first challenge was to discover that there is no a straight path from 9.3 to the latest PostgreSQL engine with AWS. As of May 2018, Amazon Aurora only supports automation of the upgrade process using version 9.6.1 or 9.6.3. If you tried to run an upgrade process process on Amazon PostgreSQL RDS databases less than 9.6.3, the upgrade tools throw a cryptic error message:
Cannot upgrade from postgres 9.6.x to aurora-postgresql 9.6.3. Specify a current active database version, the latest active minor version for postgres 9.6 is 9.6.x.
So, we had to get the customer database to at least 9.6.3 before any further progress could be made. Amazon acknowledged this problem in their article Using PostgreSQL Version 10.x and Later as a Source for AWS DMS. In this case, the solution involves using the Amazon Database Migration Service. Fortunately for our customer, they didn’t have to worry about this problem.
Automating the triple-hop upgrade to 9.6.3
With PostgreSQL, there is no real way to simply migrate from 9.3 to 9.6.3. You have to first upgrade to the latest version for the next major release. This meant having to upgrade the database using a triple-hop between the following versions:
- PostgreSQL 9.4.12
- PostgreSQL 9.5.7
- PostgreSQL 9.6.3
Although Amazon provides a way to upgrade using the AWS Console, we had to automate the process to avoid delays in order to beat the 6 hour downtime window. We used the AWS CLI modify-db-instance process as shown below to upgrade the database to 9.6.3.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DB_INSTANCE=[change_to_db_instance_identifier] aws rds modify-db-instance \ --db-instance-identifier $DB_INSTANCE \ --allow-major-version-upgrade \ --no-auto-minor-version-upgrade \ --engine-version 9.4.12 \ --apply-immediately aws rds modify-db-instance \ --db-instance-identifier $DB_INSTANCE \ --allow-major-version-upgrade \ --no-auto-minor-version-upgrade \ --engine-version 9.5.7 \ --apply-immediately aws rds modify-db-instance \ --db-instance-identifier "$DB_INSTANCE" \ --allow-major-version-upgrade \ --no-auto-minor-version-upgrade \ --engine-version 9.6.3 \ --apply-immediately |
After each modify-db-instance command, it’s necessary to wait some time (about 10 to 15 minutes) while the instance is upgrading. During this time, its state can be monitored as follows:
1 2 3 4 | DB_INSTANCE=[change_to_db_instance_identifier] aws --output table --query "DBInstances[0].{id: DBInstanceIdentifier, version: EngineVersion, status: DBInstanceStatus}" rds describe-db-instances \ --db-instance-identifier $DB_INSTANCE |
The end result was that we completed the triple-hop upgrade in 45 minutes. That left us with 5 hours and 15 minutes to play with to do the final migration step to Amazon Aurora.
PostgreSQL RDS Database Migration to Amazon Aurora PostgreSQL
There are several options for migrating data from an RDS PostgreSQL instance to an Amazon Aurora PostgreSQL cluster:
- Migrate data directly from an Amazon RDS PostgreSQL DB snapshot to an Amazon Aurora PostgreSQL Database cluster.
- Migrate from an RDS PostgreSQL DB instance by creating an Amazon Aurora PostgreSQL Read Replica of a PostgreSQL DB instance. When the replica lag between the PostgreSQL DB instance and the Amazon Aurora PostgreSQL Read Replica is 0, you can stop replication to make the Aurora Read Replica a standalone Amazon Aurora PostgreSQL Database cluster for reading and writing.
Our customer ended up trying the first approach and had to kill the process because copy operation of the 6 TB DB snapshot exceeded their 6-hour window. We had to try this out for ourselves before embarking using the trickier second option. We started with a 1 TB subset of their production database and discovered that it took 13 hours to complete the transfer within the same availability zone. Needless to say, 13 hours times 6 TB would take 65 hours to migrate.
Migration to Aurora PostgreSQL using Amazon Aurora Read Replicas
Amazon RDS uses the PostgreSQL DB engines’ streaming replication functionality to create a special type of Database cluster called an Aurora Read Replica for a source PostgreSQL DB instance. Updates made to the source PostgreSQL DB instance are asynchronously replicated to the Aurora Read Replica.
AWS recommends using this functionality to migrate from a PostgreSQL DB instance to an Aurora PostgreSQL Database cluster by creating an Aurora Read Replica of the source PostgreSQL DB instance. When the replica lag between the PostgreSQL DB instance and the Aurora Read Replica is zero, the Aurora Read Replica can be promoted to become a standalone Aurora PostgreSQL Database cluster which can accept write loads.
When a user creates an Aurora Read Replica of a PostgreSQL DB instance, Amazon RDS creates a DB snapshot of the source PostgreSQL DB instance (private to Amazon RDS, and incurring no charges). Amazon RDS then migrates the data from the DB snapshot to the Aurora Read Replica. After we migrated the data from the DB snapshot to the new Aurora PostgreSQL Database cluster, Amazon RDS starts replication between the PostgreSQL DB instance and the Aurora PostgreSQL Database cluster.
We essentially followed the detailed steps provided by Amazon in their documentation page titled Migrating Data from a PostgreSQL DB Instance to an Aurora PostgreSQL DB Cluster by Using an Aurora Read Replica to complete this process.
The results of this approach were worth celebrating success. By using the approach of creating a read replica and then promoting it, this final step took only 2 hours! What this meant to our customer was that the entire process to upgrade their production database to Amazon Aurora would only take 2 hours and 45 minutes!
More room for celebration with 12 times better performance
After verifying the process a couple of more times, the customer was able to follow the process we wrote up for them in under three hours. We were a little skeptical of Amazon’s claim that PostgreSQL on Aurora would give you 3 X more throughput, so we ran the batch job that was taking 2 hours to run to put Aurora to the test. The script ran in only 10 minutes! We had to double and triple check the results, and sure enough Amazon Aurora for PostgreSQL got our stamp of approval for this project!
We have to always caveat performance claims by saying that these results were based on our customer’s very specific scenario and that the results you might find will vary.
Take the deep dive into how we went through the process
We created a technical white paper that goes into the details on how we were able to complete the migration project in less than three hours. To understand the trade-offs we made along the way, check out our white paper titled Amazon Aurora Improves PostgreSQL Database Performance by 12 X.
To take advantage of our expertise in cloud database management to streamline your upgrade or migration projects, please contact us today to get started.
