How to migrate a Postgres database with minimal downtime

Moving a Postgres database between providers has two techniques. Dump and restore: stop writes, copy everything, point the app at the new database. Logical replication: stream changes to the new database while the old one keeps serving, then cut over in seconds.

The choice is one calculation — how long a full restore of your data takes versus how much downtime you can accept. Everything else in this post is preparation, because the ways these migrations fail are almost all preventable and almost all discovered at the worst moment.

First: measure, don't estimate

Do a full dump and restore into a scratch database on the destination, with your real data, and time it. Not a subset. The number you get is the downtime for the simple approach, and it's usually larger than people guess because index rebuilds dominate at larger sizes.

# Time the whole thing end to end, with real data
time pg_dump --format=custom --no-owner --no-acl \
  "$OLD_DATABASE_URL" -f prod.dump

time pg_restore --no-owner --no-acl --jobs=4 \
  --dbname="$NEW_DATABASE_URL" prod.dump

# --jobs parallelises index and constraint creation, which is where
# most of the restore time goes on any non-trivial database

If the total fits your acceptable window with margin, take the simple path. If it doesn't, use logical replication. Don't try to optimise a dump-and-restore into being fast enough — that path ends with a migration that's 90% done when the window closes.

The preflight checks that prevent a failed window

1. Major version parity. Restore into the same major version. Combining a provider migration with a version upgrade is two risky changes at once, and if something breaks you won't know which one did it.
2. Extensions. List them on the source and confirm every one exists on the destination, at a compatible version. A restore that fails on a missing extension halfway through is the classic wasted window.
3. Roles and ownership. Dump with --no-owner and --no-acl, then create the roles you need on the destination deliberately. Managed providers don't give you superuser and a dump full of ownership statements will fail.
4. Sequences. After any restore, verify sequence values. A sequence left behind the max id of its table produces duplicate key errors on the first insert, minutes after cutover, and it's a confusing thing to debug under pressure.
5. Connection limits. Check the destination's limit and whether a pooler is in front. A migration is a fine time to discover you needed one; a Monday morning is not.

-- Run on the source, then verify each one exists on the destination
SELECT extname, extversion FROM pg_extension ORDER BY 1;

-- After restore: sequences behind their table are a live bug
SELECT schemaname, sequencename, last_value
FROM pg_sequences
WHERE schemaname = 'public'
ORDER BY 1, 2;

Path A: dump and restore

For databases where the measured restore fits your window. The sequence:

1. Put the application into maintenance mode, or stop the writers. Reads can continue against the old database if you're careful about what that implies.
2. Take the dump. Custom format, no owner, no ACLs.
3. Restore with parallel jobs into the destination.
4. Verify: row counts on your largest tables, sequence values, extension presence, and one real query from the application.
5. Switch the connection string and restart the app.
6. Keep the old database running and readable for at least a week.

Path B: logical replication

For databases where a restore takes hours and you need the window measured in seconds. The destination subscribes to the source, copies the initial data, then streams changes continuously. When replication lag is near zero, you pause writes for a few seconds and switch.

-- On the source: publish the tables you're moving
CREATE PUBLICATION migration FOR ALL TABLES;

-- On the destination, after creating the schema (dump with --schema-only)
CREATE SUBSCRIPTION migration
  CONNECTION 'host=old-db.example.com dbname=app user=replicator sslmode=require'
  PUBLICATION migration;

-- Watch the lag until it is consistently near zero
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

Three things about logical replication that catch people out. It does not replicate schema changes, so freeze migrations for the duration. It does not replicate sequence values, so you must advance them on the destination at cutover. And every table needs a replica identity — a primary key, in practice — or updates and deletes won't replicate, silently.

The cutover itself

1. Stop writes at the application — a maintenance flag is cleaner than killing connections.
2. Wait for replication lag to reach zero. Confirm it, don't assume it.
3. Advance every sequence on the destination past its table's maximum id.
4. Point the application at the new database and restart.
5. Run a real write and a real read through the application, immediately.
6. Drop the subscription on the destination, so it stops trying to replicate from a database you're about to retire.

Afterwards

Run ANALYZE on the new database. A freshly restored database has no statistics, and the planner will make poor choices until it does — which presents as 'the new provider is slow' and is nothing of the sort. Then take a backup on the new provider and restore it once, so you know that path works before you need it.

Keep the old database running for a week. Not snapshotted — running, reachable, and able to serve. The cost of a week of an instance you don't need is trivially less than the cost of discovering on day three that something still points at it.