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_restore | Native logical replication | AWS DMS (full load + CDC) | |
|---|---|---|---|
| Write downtime | Full dump + restore time | Minutes at cutover | Minutes at cutover |
| Moves schema | Yes, everything | No; you copy it once with pg_dump --schema-only | Tables and primary keys only |
| Sequences | Yes | No; you sync them at cutover | No |
| Large objects | Yes | No | No (OID LOBs) |
| Source requirements | Read access | wal_level = logical, a replication role, network path from RDS to the source | Logical decoding enabled on the source, primary keys on every table |
| Main risk | Window overruns | Schema changes during the sync; source disk filling with WAL | Silent 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:
- DDL is not replicated. Freeze schema changes for the migration, or apply every migration to RDS first (additive changes on the subscriber first is what the docs suggest), then the source.
- Sequences are not replicated. The rows arrive with their IDs, but the sequences on RDS stay at their start values. You must set them at cutover or the first insert fails with a duplicate key. PostgreSQL 19, in release candidate at the time of writing (checked November 2026), adds
ALTER SUBSCRIPTION ... REFRESH SEQUENCES, but it needs the publisher on 19, so it will not help a migration away from an older server. - Large objects are not replicated, and there is no workaround other than storing that data in normal tables. Check with
SELECT count(*) FROM pg_largeobject_metadata;. - Only tables replicate. Views come with the schema; materialized views need a
REFRESHon RDS before cutover. - TRUNCATE across foreign keys fails on the subscriber if some of the related tables are not in the subscription. With
FOR ALL TABLESthis is not an issue.
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:
- Tables without a primary key: DMS ignores UPDATE and DELETE for them. The task keeps running; the data simply drifts.
NUMERICcolumns without precision and scale are migrated asNUMERIC(28,6)by default. AWS's own example shows 0.611111104488373 arriving as 0.611111. Check every unconstrained numeric column, especially money.- DMS creates only tables and primary keys, not secondary indexes, foreign keys or column defaults (AWS best practices). Pre-create the schema yourself with
pg_dump --schema-only. - Partitioned tables are created as plain tables unless you pre-create the parent and partitions, and partition
ADD,DROPandTRUNCATEare not replicated. - OID large objects, materialized views and deferred constraints are not supported, and
ALTER COLUMN ... SET DEFAULTorSET NOT NULLchanges are not captured.
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.
- 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; enablerds.logical_replicationon RDS now if you want the rollback path (it is a static parameter and needs a reboot). - 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'; - Wait for zero lag. Note
pg_current_wal_lsn()on the source and wait until the slot'sconfirmed_flush_lsnhas passed it. - Sync sequences. Generate
setvalcalls 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 - Validate (next section). Decide go or no-go against criteria you wrote down before you started.
- 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. - Switch connection strings. Update the secret or parameter your services read, restart them, and run your smoke tests against RDS.
- 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
- Extensions. RDS supports a fixed list. Run
SELECT extname, extversion FROM pg_extension;on the source and compare withSHOW rds.extensions;on the target and the RDS extension versions list. PostGIS, pgvector, pg_cron and pg_partman are there; TimescaleDB is not in the list for current major versions. Find this out before the schema restore fails on aCREATE EXTENSION. - No superuser. On RDS you get
rds_superuser, not a real superuser. Server settings live in parameter groups, and anything that relied on superuser-only functions or untrusted languages needs a rewrite. - Collations. RDS relies on glibc for collations and ships an independent default collation library to keep sort order stable across OS updates (AWS docs). If your source runs a different glibc, text may sort differently for some characters. Create the target database with the same encoding and locale (compare
\lon both) and test queries that depend on text ordering. - Time zones. Compare
SHOW timezone;on both sides. On RDS you change it in the parameter group. A mismatch silently shifts any value written asnow()::timestampor rendered into atimestamp without time zonecolumn. - Long transactions. Logical decoding spills big transactions to disk, and a long-running transaction holds the slot back. Do not run a backfill during the migration.
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.