Transformation: self-managed PostgreSQL or MySQL on EC2 to Amazon RDS / Aurora with minimal downtime

#Pick a method

Native replication is usually the most faithful for same-engine moves; DMS is the most flexible.

#1. Prepare the target

• Same major version as the source (upgrade separately, before or after).
• Custom parameter group with your tuned settings (memory settings are often formulas on RDS — don't copy absolute values blindly).
• Extensions: confirm each PostgreSQL extension you use is supported on RDS/Aurora.
• Users and roles: RDS has no superuser; the master user has rds_superuser. Scripts that need SUPERUSER must change.

aws rds create-db-instance --db-instance-identifier orders-rds --engine postgres --engine-version 16.4 \
  --db-instance-class db.r7g.xlarge --allocated-storage 500 --storage-type gp3 \
  --master-username dbadmin --manage-master-user-password \
  --db-subnet-group-name prod-db --vpc-security-group-ids sg-db --multi-az \
  --db-parameter-group-name orders-pg16 --backup-retention-period 14 --deletion-protection

#2. PostgreSQL logical replication (same major version family)

On the source (wal_level = logical, restart required):

CREATE PUBLICATION orders_pub FOR ALL TABLES;

Load schema on the target, then subscribe:

pg_dump -h ec2-db -U postgres -d orders --schema-only | psql -h orders-rds.xxxx.rds.amazonaws.com -U dbadmin -d orders

CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=10.0.1.20 dbname=orders user=replicator password=...'
  PUBLICATION orders_pub;

Logical replication does not copy sequences or DDL. Sync sequence values at cutover and freeze schema changes during the migration. Every table needs a primary key (or replica identity).

#3. MySQL binlog replication into RDS

Take a consistent dump with binlog coordinates (or GTIDs), load it, then:

CALL mysql.rds_set_external_source_with_auto_position('10.0.1.30', 3306, 'repl', 'password', 0);
CALL mysql.rds_start_replication;
SHOW REPLICA STATUS\G

The exact procedure names depend on the RDS MySQL version (older versions use rds_set_external_master); check the RDS documentation for yours.

#4. AWS DMS alternative

Create source/target endpoints, a replication instance (or DMS Serverless) and a task with full load and CDC. Enable validation to compare rows. DMS creates tables but not secondary indexes, foreign keys or defaults the way you want — pre-create the schema yourself and use TargetTablePrepMode=DO_NOTHING.

#5. Cutover

1. Rehearse the whole thing at least once, timing each step.
2. Stop application writes; wait for lag to reach zero.
3. Sync sequences (PostgreSQL): SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));
4. Point the app at the RDS endpoint (ideally via a Route 53 CNAME you control).
5. Smoke test, then open writes.

Tip

Keep the EC2 database intact and, if possible, set up reverse replication from RDS so rollback doesn't lose new writes.

#Checklist

• Version, extensions and privileges checked for RDS compatibility
• Parameter group reviewed
• Replication or DMS running with validation
• Rehearsal timed; sequences handled
• Backups, Performance Insights and alarms on the new instance