Applies toPostgreSQL 11–17 sources; Amazon Aurora PostgreSQL-Compatible (versions matching current Aurora support); AWS DMS
What changes with Aurora
| Area | Self-managed | Aurora |
|---|---|---|
| Storage | Your disks, your IOPS sizing | Distributed storage, grows automatically; I/O-Optimized or Standard pricing modes |
| Replicas | Streaming replicas you manage | Up to 15 Aurora Replicas on shared storage, low lag |
| Failover | Patroni / manual | Managed; usually tens of seconds |
| Superuser | Yes | rds_superuser role, not full superuser |
| Extensions | Anything you compile | Supported list per version |
1. Pre-checks
SELECT extname, extversion FROM pg_extension;
SELECT name, setting FROM pg_settings WHERE source NOT IN ('default','override');
- Compare extensions against Aurora's supported list for your target version.
- Settings that matter go into a DB cluster parameter group (cluster-level) or DB parameter group (instance-level).
- Tables without primary keys (needed for logical replication / DMS full UPDATE/DELETE support).
2. Choose the method
| Method | Downtime | Notes |
|---|---|---|
pg_dump / pg_restore | Full duration | Simple, good for < a few hundred GB |
| Native logical replication (publication → subscription) | Minutes | Source PG 10+, wal_level=logical; sequences and DDL not replicated |
| AWS DMS full load + CDC | Minutes | Handles heterogeneous options and monitoring; check LOB and data type settings |
3. Native logical replication path
On the source:
ALTER SYSTEM SET wal_level = logical; -- restart required
CREATE PUBLICATION mig_pub FOR ALL TABLES;
Copy the schema first (logical replication doesn't create tables):
pg_dump -h src -U postgres -d sales --schema-only -f schema.sql
psql -h aurora-cluster.cluster-xxxx.eu-west-1.rds.amazonaws.com -U admin -d sales -f schema.sql
On Aurora (the source must be reachable from the VPC: VPN/Direct Connect for on-prem):
CREATE SUBSCRIPTION mig_sub
CONNECTION 'host=src.example.com port=5432 dbname=sales user=repl password=... sslmode=require'
PUBLICATION mig_pub;
Monitor:
-- target
SELECT subname, received_lsn, latest_end_lsn, latest_end_time FROM pg_stat_subscription;
-- source
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;
4. Cut-over
- Stop application writes on source.
- Wait until lag is 0.
- Sync sequences:
-- generate on source, run output on target
SELECT format('SELECT setval(%L, %s, true);', schemaname||'.'||sequencename, last_value)
FROM pg_sequences WHERE last_value IS NOT NULL;
DROP SUBSCRIPTION mig_sub;on Aurora (drops the slot on the source too, if reachable).- Point applications at the cluster endpoint (writer) and the reader endpoint for read-only traffic.
ANALYZE;on the target.
5. After migration
- Use the cluster endpoint, never an instance endpoint, in app config, so failovers are transparent.
- Set up Performance Insights / Database Insights and CloudWatch alarms (CPU, freeable memory,
DBLoad, deadlocks). - Test a failover (
aws rds failover-db-cluster --db-cluster-identifier ...) and check app reconnect behaviour. - Clean up the replication slot on the source if it still exists, or WAL will fill its disk.
Warning
Logical replication doesn't copy DDL. Freeze schema changes from the start of replication until cut-over, or apply each DDL change on both sides manually.
Checklist
- Extensions and parameters mapped
- PK coverage checked; method chosen
- Replication lag monitored; sequences synced at cut-over
- Apps on cluster/reader endpoints; failover tested
- Source slot removed