Zero-downtime PostgreSQL migration to Amazon RDS

· 10 min read · postgres · migration

Moving a live Postgres database to RDS is a replication problem followed by a five-minute cutover. This is the method we use, the limitations that bite, and the queries we run before we let traffic in.

To migrate PostgreSQL to Amazon RDS with near-zero downtime, use native logical replication: copy the schema, create a publication on the source and a subscription on RDS, let it catch up, then freeze writes for a few minutes, sync sequences, and point the application at RDS. If you can afford a maintenance window of a few hours, pg_dump and pg_restore are simpler and safer. AWS DMS sits in between and has limitations you need to read before you trust it with production data.

"Zero downtime" in practice means a write freeze measured in minutes, with reads still served. True zero needs dual writes in the application, which is rarely worth it for a one-off move.

Choose the method: pg_dump, logical replication or DMS

Pick by how long you can stop writes. If the dump and restore fit inside a window your customers accept, use pg_dump. If not, use logical replication. Use DMS when you cannot change the source's configuration yourself or want AWS to run the replication for you, and only after checking its limitations against your schema.

pg_dump / pg_restoreNative logical replicationAWS DMS (full load + CDC)
Write downtimeFull dump + restore timeMinutes at cutoverMinutes at cutover
Moves schemaYes, everythingNo; you copy it once with pg_dump --schema-onlyTables and primary keys only
SequencesYesNo; you sync them at cutoverNo
Large objectsYesNoNo (OID LOBs)
Source requirementsRead accesswal_level = logical, a replication role, network path from RDS to the sourceLogical decoding enabled on the source, primary keys on every table
Main riskWindow overrunsSchema changes during the sync; source disk filling with WALSilent data differences (see below)

Coming from Heroku? Classic Heroku Postgres does not let you replicate out with standard tools, so the options there are different; our Heroku to AWS migration guide covers that case.

Option 1: pg_dump and pg_restore

This is the boring, reliable method: stop writes, dump, restore, switch. Downtime equals the time it takes, so rehearse it on a copy of production and time it before you promise anyone a window.

# Run from an EC2 instance in the same region as the RDS instance
pg_dumpall -h source.internal -U postgres --roles-only > roles.sql
# Edit roles.sql: remove SUPERUSER, REPLICATION and other attributes RDS rejects

pg_dump -h source.internal -U postgres -d appdb \
  -Fd -j 8 -f /data/appdb.dump --no-owner --no-privileges

psql -h appdb.xxxx.eu-central-1.rds.amazonaws.com -U admin -d postgres -f roles.sql
pg_restore -h appdb.xxxx.eu-central-1.rds.amazonaws.com -U admin -d appdb \
  -j 8 --no-owner --no-privileges /data/appdb.dump

psql -h appdb.xxxx.eu-central-1.rds.amazonaws.com -U admin -d appdb -c 'ANALYZE;'

The directory format (-Fd) is what lets pg_dump and pg_restore run in parallel with -j. Re-apply grants afterwards with a script you keep in version control, since there is no real superuser on RDS. Run ANALYZE before you open traffic, or the first queries will plan badly.

Option 2: native logical replication, step by step

Logical replication copies each table's existing rows, then streams every insert, update and delete from the source to RDS until you cut over. RDS for PostgreSQL supports the standard PUBLICATION and SUBSCRIPTION syntax (AWS docs), and the target can run a newer major version than the source, which makes this a good moment to upgrade.

1. Prepare the source

# postgresql.conf on the source (wal_level needs a restart)
wal_level = logical
max_replication_slots = 10      # one per subscription plus table-sync workers
max_wal_senders = 10
max_slot_wal_keep_size = 100GB  # PostgreSQL 13+: cap WAL kept for a stalled slot

# pg_hba.conf: logical replication matches the database name,
# NOT the "replication" keyword (that one is for physical replication only)
hostssl  appdb  migrator  10.20.0.0/16  scram-sha-256

The pg_hba.conf docs are explicit that the replication keyword does not match logical replication connections; that line trips up a lot of first attempts. Note the direction too: the RDS subscriber connects to the source, so RDS needs a network route to it (VPN, Direct Connect, or a public endpoint locked to RDS's egress with TLS).

CREATE ROLE migrator WITH LOGIN REPLICATION PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migrator;

-- Tables that will break: no primary key and default replica identity.
-- UPDATE and DELETE on these fail on the source once they are published.
SELECT c.oid::regclass AS table_without_pk
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND c.relreplident = 'd'
  AND NOT EXISTS (SELECT 1 FROM pg_index i
                  WHERE i.indrelid = c.oid AND i.indisprimary);

That last query matters. The PostgreSQL docs state that a published table with no primary key and default replica identity cannot support UPDATE or DELETE: the statement errors on the source, which means your production app starts failing the moment you create the publication. Add a primary key, or set REPLICA IDENTITY USING INDEX on a unique index, or as a last resort REPLICA IDENTITY FULL (slow on the subscriber for big tables).

2. Copy the schema and start the subscription

pg_dump -h source.internal -U postgres -d appdb \
  --schema-only --no-owner --no-privileges > schema.sql
psql -h appdb.xxxx.eu-central-1.rds.amazonaws.com -U admin -d appdb -f schema.sql
-- On the source (FOR ALL TABLES requires a superuser)
CREATE PUBLICATION migrate_to_rds FOR ALL TABLES;

-- On RDS
CREATE SUBSCRIPTION migrate_to_rds
  CONNECTION 'host=10.20.1.15 port=5432 dbname=appdb user=migrator password=... sslmode=require'
  PUBLICATION migrate_to_rds;

-- On RDS: every table should reach state 'r' (ready)
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel ORDER BY 2;

-- On the source: how far behind is the subscriber?
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots WHERE slot_type = 'logical';

Watch the slot. A replication slot keeps WAL on the source until the subscriber confirms it, so a stalled subscription fills the source's disk. Alert on that lag figure and on free disk space for the whole migration.

3. Know what logical replication does not copy

From the PostgreSQL restrictions page:

Option 3: AWS DMS, and the limitations to read first

AWS DMS runs the full load and change data capture for you, using logical decoding on the source. It is useful when you cannot create a subscription that reaches the source, but for PostgreSQL to PostgreSQL its documented limitations are long. The ones that matter most:

AWS also offers DMS homogeneous data migrations, a serverless option that uses native PostgreSQL tools and moves secondary objects too. Read its limits as well: views arrive as tables, there is no built-in data validation, and new tables created on the source during ongoing replication are not picked up for PostgreSQL. In our experience, if you can make the source reachable from RDS, native logical replication is easier to reason about and to debug.

The cutover runbook

The cutover is a rehearsed, timed sequence: stop writes, wait for zero lag, sync sequences, validate, switch the connection string, reopen. Rehearse it end to end at least once against a staging copy, and keep the runbook next to your incident runbook so roles are clear.

  1. Day before: lower the TTL on any DNS name the app uses for the database; confirm all tables are in state r; run the validation queries below once to get a baseline; enable rds.logical_replication on RDS now if you want the rollback path (it is a static parameter and needs a reboot).
  2. Freeze writes. Scale workers and cron jobs to zero, put the app in maintenance or read-only mode, then make the app role read-only on the source and drop its sessions:
    ALTER ROLE app_user SET default_transaction_read_only = on;
    SELECT pg_terminate_backend(pid) FROM pg_stat_activity
    WHERE usename = 'app_user';
  3. Wait for zero lag. Note pg_current_wal_lsn() on the source and wait until the slot's confirmed_flush_lsn has passed it.
  4. Sync sequences. Generate setval calls on the source and run them on RDS:
    psql -h source.internal -d appdb -At -c "
      SELECT format('SELECT setval(%L, %s, true);',
                    format('%I.%I', schemaname, sequencename), last_value)
      FROM pg_sequences WHERE last_value IS NOT NULL" > sequences.sql
    psql -h appdb.xxxx.eu-central-1.rds.amazonaws.com -d appdb -f sequences.sql
  5. Validate (next section). Decide go or no-go against criteria you wrote down before you started.
  6. Stop the subscription on RDS with ALTER SUBSCRIPTION migrate_to_rds DISABLE;. Drop it once you no longer need it; dropping it also removes the slot on the source.
  7. Switch connection strings. Update the secret or parameter your services read, restart them, and run your smoke tests against RDS.
  8. Reopen writes and watch error rates, latency and connection counts for the first hour.

Rollback: decide before you cut over

Until the first write lands on RDS, rollback is easy: point the app back at the source and undo the read-only setting. After that, every write on RDS is lost if you go back, unless you replicate in reverse.

For a real safety net, set up reverse replication before reopening writes: create a publication on RDS (which needs rds.logical_replication = 1), then on the old source CREATE SUBSCRIPTION ... WITH (copy_data = false) so it only receives new changes. If something goes wrong in the first days, you freeze RDS, sync sequences back, and switch again. Keep the old server untouched for an agreed period, and treat it like any other backup in your disaster recovery plan until it is retired.

Validation queries to run before go-live

Compare exact row counts for every table, checksums for the tables that hold money or customer state, and sequence values against the highest IDs. Run the same queries on both sides with the same session settings.

-- Exact row counts per table (psql: run on both sides, diff the output)
SELECT format('SELECT %L AS tbl, count(*) FROM %I.%I',
              schemaname || '.' || relname, schemaname, relname)
FROM pg_stat_user_tables ORDER BY 1 \gexec

-- Content checksum for a critical table. Fix the session time zone first,
-- because timestamptz is rendered in the session's zone.
SET TIME ZONE 'UTC';
SELECT count(*), md5(string_agg(o::text, '|' ORDER BY o.id))
FROM orders o;

-- Sequences must be ahead of the data
SELECT max(id) AS max_id, (SELECT last_value FROM orders_id_seq) AS seq
FROM orders;

For very large tables, checksum by ID ranges (for example per million rows) so a mismatch tells you where to look.

Common pitfalls

For the wider plan around the database (networking, DNS, the order to move services in), see our cloud migration checklist.

If you would rather not run your first production cutover alone, our cloud migration service plans and rehearses the move with your team, runs the cutover with you, and keeps the rollback path open until you are confident on RDS.

Frequently asked questions

How do I migrate PostgreSQL to RDS without downtime?

Use native logical replication. Copy the schema with pg_dump --schema-only, create a publication on the source and a subscription on RDS, and let it catch up. At cutover, freeze writes for a few minutes, wait for zero lag, sync sequences with setval, validate, and switch the connection string to RDS.

Does PostgreSQL logical replication copy sequences?

No. Rows arrive with their IDs, but sequences on the subscriber keep their start values, so the first insert after cutover can fail with a duplicate key. Generate setval statements from pg_sequences on the source and run them on RDS during the write freeze. PostgreSQL 19 adds sequence sync, but only with a publisher on 19.

Is AWS DMS good for PostgreSQL to RDS migrations?

It works, but read its limitations first. DMS ignores UPDATE and DELETE on tables without a primary key, converts unconstrained NUMERIC columns to NUMERIC(28,6) by default, and creates only tables and primary keys. If RDS can reach your source, native logical replication is usually simpler to run and to debug.

How long does a pg_dump and pg_restore migration take?

It depends on data size, indexes, network and instance size, so measure it. Rehearse on a copy of production from an EC2 instance in the same region as RDS, using the directory format with parallel jobs (-Fd -j). The rehearsal time plus a margin is your maintenance window.

Tell us what you need