Migrate an on-premises PostgreSQL database to Aurora PostgreSQL

Created by Baji Shaik (AWS) and Jitender Kumar (AWS)

Environment: PoC or pilot	Source: On-premises PostgreSQL database	Target: Aurora PostgreSQL-Compatible
R Type: Replatform	Workload: Open-source	Technologies: Migration; Databases
AWS services: Amazon Aurora; AWS DMS

Summary

Amazon Aurora PostgreSQL-Compatible Edition combines the performance and availability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. Aurora provides these benefits by scaling storage across three Availability Zones in the same AWS Region, and supports up to 15 read replica instances for scaling out read workloads and providing high availability within a single Region. By using an Aurora global database, you can replicate PostgreSQL databases in up to five Regions for remote read access and disaster recovery in the event of a Region failure. This pattern describes the steps for migrating an on-premises PostgreSQL source database to an Aurora PostgreSQL-Compatible database. The pattern includes two migration options: using AWS Data Migration Service (AWS DMS) or using native PostgreSQL tools (such as pg_dump, pg_restore, and psql) or third-party tools. 

The steps described in this pattern also apply to target PostgreSQL databases on Amazon Relational Database Service (Amazon RDS) and Amazon Elastic Compute Cloud (Amazon EC2) instances.

Prerequisites and limitations

Prerequisites

• An active AWS account

• A PostgreSQL source database in an on-premises data center

• An Aurora PostgreSQL-Compatible DB instance or an Amazon RDS for PostgreSQL DB instance

Limitations

• Database size limits are 64 TB for Amazon RDS for PostgreSQL and 128 TB for Aurora PostgreSQL-Compatible.

• If you’re using the AWS DMS migration option, review AWS DMS limitations on using a PostgreSQL database as a source.

Product versions

• For PostgreSQL major and minor version support in Amazon RDS, see Amazon RDS for PostgreSQL updates in the Amazon RDS documentation.

• For PostgreSQL support in Aurora, see Amazon Aurora PostgreSQL updates in the Aurora documentation.

• If you’re using the AWS DMS migration option, see supported PostgreSQL versions in the AWS DMS documentation.

Architecture

Source technology stack

• On-premises PostgreSQL database

Target technology stack

• Aurora PostgreSQL-Compatible DB instance

Source architecture

Target architecture

Data migration architecture

Using AWS DMS

Using native PostgreSQL tools

Tools

• AWS Database Migration Service (AWS DMS) helps you migrate data stores into the AWS Cloud or between combinations of cloud and on-premises configurations. This service supports different sources and target databases. For information about how to validate the PostgreSQL source and target database versions and editions supported for use with AWS DMS, see Using a PostgreSQL database as an AWS DMS source. We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support.

• Native PostgreSQL tools include  pg_dump, pg_restore, and psql.

Epics

Analyze the migration

Task	Description	Skills required
Validate the source and target database versions.	If you are using AWS DMS, make sure that you’re using a supported version of PostgreSQL.	DBA
Identify the storage type and capacity requirements.	Calculate the storage allocated for the source database instance. Gather the historical growth metrics for the source database instance. Anticipate the future growth forecast for the target database instance. Allocate storage by calculating the total number of read and write IOPS on the source database. A General Purpose SSD (gp2) volume provides 3 IOPS for each 1 GB of storage.	DBA, Systems administrator
Choose the proper instance type, capacity, storage features, and network features.	Determine the compute requirements of the target database instance. Review known performance issues that might need additional attention. Consider the following factors to determine the appropriate instance type: CPU utilization of the source database instance IOPS (read and write operations) for the source database instance Memory footprint on the source database instance For more information, see Aurora DB instance classes in the Aurora documentation.	DBA, Systems administrator
Identify the network access security requirements for the source and target databases.	Determine the appropriate security groups that would enable the application to talk to the database.	DBA, Systems administrator
Identify the application migration strategy.	Determine the migration cutover strategy based on the complexity of your application.  Determine the recovery time objective (RTO) and recovery point objective (RPO) for the application, and plan for the cutover accordingly.	DBA, App owner, Systems administrator

Configure the infrastructure

Task	Description	Skills required
Create a VPC.	Create a new virtual private cloud (VPC) for the target database instance.	Systems administrator
Create security groups.	Create a security group within the VPC (as determined in the previous epic) to allow inbound connections to the database instance.	Systems administrator
Configure and start the Aurora DB cluster.	Create the target database instance with the new VPC and security group and start the instance.	Systems administrator

Migrate data ‒ option 1 (using AWS DMS)

Task	Description	Skills required
Complete pre-migration steps.	Clean up the data in the source database. Create a replication instance. Create source and target endpoints. Identify the number of available tables and objects to be migrated.	DBA
Complete migration steps.	Drop foreign key constraints and triggers on the target database. Drop secondary indexes on the target database. Use a full-load task to migrate data from the source to the target database. Enable foreign keys. If you’re using flash-cut migration and your application requires minimal downtime, enable change data capture (CDC) to replicate ongoing changes Enable triggers. Update sequences. Validate the source and target data.	DBA
Validate data.	To ensure that your data was migrated accurately from the source to the target, follow the data validation steps in the AWS DMS documentation.	DBA

Migrate data ‒ option 2 (using pg_dump and pg_restore)

Task	Description	Skills required
Prepare the source database.	Create a directory to store pg_dump backup if it doesn’t already exist. Create a migration user that has permissions to run pg_dump on database objects. Connect to the EC2 instance and run pg_dump backup. For more information, see the pg_dump documentation and the walkthrough in the AWS DMS documentation.	DBA
Prepare the target database.	Create a migration user that has permissions to use pg_restore on database objects. Import the database dump by using pg_restore. For more information, see the pg_restore documentation and the walkthrough in the AWS DMS documentation.	DBA
Validate data.	Compare the database object counts between the source and the target databases. Resolve any discrepancies found between object counts.	DBA

Migrate the application

Task	Description	Skills required
Follow the application migration strategy.	Implement the application migration strategy that you created in the first epic.	DBA, App owner, Systems administrator

Cut over to the target database

Task	Description	Skills required
Switch the application clients over to the new infrastructure.	Stop all application services and client connections that point to the on-premises PostgreSQL database. Run the AWS DMS tasks. Set up a rollback task (reverse CDC from Aurora PostgreSQL-Compatible to the on-premises PostgreSQL database) if needed. Validate data. Start the application services on the new target by configuring Amazon Route 53 to the new Aurora PostgreSQL-Compatible DB instance. Add Amazon CloudWatch and Performance Insights monitoring on your new Aurora PostgreSQL-Compatible DB instance.	DBA, App owner, Systems administrator
If you need to roll back the migration.	Stop all application services that point to the Aurora PostgreSQL-Compatible database. Roll back the changes to the source on-premises PostgreSQL database by using the AWS DMS task you created in the previous story. Stop the AWS DMS tasks running from the on-premises PostgreSQL database to the Aurora PostgreSQL-Compatible database. Configure the application so that it points back to the source on-premises PostgreSQL database. Confirm that all rollback deployment is complete.	DBA, App owner

Close the project

Task	Description	Skills required
Shut down resources.	Shut down the temporary AWS resources.	DBA, Systems administrator
Validate documents.	Review and validate the project documents.	DBA, App owner, Systems administrator
Gather metrics.	Gather metrics around time to migrate, percent of manual versus tool cost savings, and so on.	DBA, App owner, Systems administrator
Close the project.	Close the project and provide any feedback.	DBA, App owner, Systems administrator

Related resources

References

• AWS Data Migration Service

• VPCs and Amazon Aurora

• Amazon Aurora pricing

• Using a PostgreSQL database as an AWS DMS source

• How to create an AWS DMS replication instance

• How to create source and target endpoints using AWS DMS

Additional resources

• Getting Started with AWS DMS

• Data migration step-by-step walkthroughs

• Amazon Aurora resources