Migrating PostgreSQL to AWS RDS With Zero Downtime: A Step-by-Step Guide

Migrating a production PostgreSQL database to AWS RDS while keeping the application running requires a systematic approach. A maintenance window approach (take the app down, dump, restore, point at new DB, bring up) is simpler to execute but not always acceptable. This guide covers the zero-downtime path using logical replication.

Pre-Migration Assessment

Before you touch anything, answer these questions: What is the database size? What is the current replication lag tolerance (RPO during migration)? Are there any PostgreSQL features used that RDS doesn't support (extensions, superuser-required operations)? What's the current Postgres version, and what version will you target on RDS?

RDS supports PostgreSQL 13 through 16. Check the list of supported extensions — most common ones (pgcrypto, uuid-ossp, PostGIS) are supported, but some are not.

Phase 1: Provision the Target RDS Instance

Use QuickInfra to provision the RDS PostgreSQL instance with your target configuration. Place it in the same VPC as your application servers (or in the target VPC if you're migrating that too). Enable Multi-AZ. Set the parameter group to enable logical replication: set rds.logical_replication = 1 and wal_level = logical.

Phase 2: Initial Bulk Load

Use pg_dump --format=custom to export the source database. Transfer to an S3 bucket in your AWS account. Restore to the target RDS instance using pg_restore. For large databases (hundreds of GB), AWS Database Migration Service (DMS) handles the bulk load more efficiently.

Phase 3: Continuous Replication

Once the bulk load completes, set up logical replication from the source to the target. Create a replication slot on the source, create a subscription on the target pointing at the source. PostgreSQL replication will catch up the delta (changes made during bulk load) and stay in sync.

Monitor replication lag — it should reach near-zero within a few minutes of the subscription starting on a healthy network.

Phase 4: Application Cutover

When replication lag is consistently below 1 second, schedule the cutover. The process:

1. Enable maintenance mode or take the application read-only
2. Wait for replication lag to hit zero
3. Update the database connection string in your application configuration to point at the RDS endpoint
4. Disable read-only mode / remove maintenance mode
5. Verify application functionality

Total downtime: typically under 60 seconds.

Phase 5: Cleanup

After 24-48 hours of clean operation on RDS, decommission the source database. Drop the replication slot to avoid WAL accumulation on the now-decommissioned source.