Moving a live database with minutes of downtime

Copy while the old server keeps working.

replicationlive

Illustrative example of a logical replication cutover.replay

The scenario#

A busy PostgreSQL server on a self-managed VM had to move to a managed database service. A dump-and-restore would have meant hours of downtime.

The business could accept a pause of a few minutes at a quiet time, but not an evening offline.

Constraints#

• Hundreds of gigabytes of data, changing all day.
• A maintenance window of a few minutes.
• No tolerance for lost writes.

How it was planned#

PostgreSQL’s built-in logical replication streams changes from the old server to the new one while the old one keeps serving traffic. Once the new server catches up, the only downtime is the switch itself.

-- on the old server
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- on the new server, after restoring the schema with pg_dump --schema-only
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=old-db dbname=app user=replicator'
  PUBLICATION app_pub;

-- watch it catch up
SELECT subname, latest_end_lsn, latest_end_time FROM pg_stat_subscription;

The cutover#

1. Rehearse the whole switch on a copy, twice, with a stopwatch.
2. At the agreed time, pause writes in the app.
3. Wait for replication lag to reach zero.
4. Set every sequence on the new server past the old server’s current values.
5. Point the app at the new server and resume writes.
6. Keep the old server read-only for a few days as a fallback.

Result#

In this illustrative run, writes pause for under a minute. Reads keep working throughout, and nothing is lost because the switch only happens at zero lag.

Lessons#

• Downtime comes from the switch, not the copy. Replicate first, switch later.
• Sequences, large objects and schema changes need their own plan.
• A rehearsed cutover is a boring cutover.