Zero-Downtime PostgreSQL Migration: The Checklist From a 2M-Record Move

In 2023 I moved two million patient records with no downtime. The part people expect to be hard, copying the rows, was the easy part. It ran for weeks in the background and nobody noticed.

The hard parts were knowing when the copy was actually correct, and being able to go back after the new system had already accepted writes.

TL;DR Inventory every writer before you choose a mechanism. Use logical replication when the shape stays the same and dual writes through an outbox when it does not. Backfill in resumable chunks, verify with per-chunk checksums rather than row counts, shift reads before writes, and keep reverse replication running until you pass the point of no return. If you cannot demonstrate the rollback, you do not have one.

First, Decide If You Actually Need It

Zero downtime costs roughly twice the engineering of a maintenance window. Sometimes that is obviously worth it, and sometimes a team spends two months building dual-write infrastructure for a system whose users are all asleep between 2am and 5am on a Sunday.

Ask what a window would really cost, in money and in trust. If the honest answer is "not much," take the window, and spend the saved effort on verification instead. Everything below still applies, minus the traffic shifting.

Also be precise about the words. Most systems that claim zero downtime mean "no lost writes, and a few seconds where a retryable error is possible." That is a different and much cheaper target than continuous availability, and it is usually the one the business actually wants.

Inventory Before Mechanism

Every failed migration I have been called into skipped this step. Before choosing a tool, write down:

• Every process that writes to the source, including the ones nobody owns: cron jobs, an ETL export, a support tool, an ops engineer with psql access.
• Every reader, and whether it tolerates stale data. Reporting usually does. A booking screen does not.
• Every trigger, rule and stored procedure on the affected tables. Triggers that fire on the source will not fire on a replicated target, and business logic hiding in a trigger is the classic source of silent drift.
• Every foreign key that crosses the boundary you are about to draw.
• The identity columns. Logical replication does not replicate sequence values, so on cutover you have to advance every sequence on the target yourself or the first insert collides.

This list is the migration plan. The rest is mechanics.

Dual Writes or Change Data Capture

The decision rule is simple. If the target has the same shape as the source, use PostgreSQL logical replication and write no code. If you are also remodelling the data, dual write, and write the outbox row inside the same transaction as the business change so there is no window where one exists without the other. Never dual write with two independent connections and hope. There is no atomicity across two databases, and the crash between the two writes is not hypothetical.

If you use logical replication, put an alert on replication slot lag on day one. A subscriber that dies quietly over a weekend keeps its slot, the publisher retains WAL for it, and the source database runs out of disk. That single failure mode has caused more incidents than everything else in this article combined.

The Backfill

• Chunk by primary key range, never by OFFSET. Offsets get slower every page and can skip rows when the underlying data shifts.
• Keep a watermark table recording the last key committed. The job must be killable at any moment and resumable from where it stopped, because it will be killed.
• Make every write idempotent with INSERT ... ON CONFLICT, so a replayed chunk is harmless.
• Throttle deliberately and watch replication lag on the source's own replicas while it runs. A backfill that saturates I/O is an outage with a friendly name.
• Create indexes on the target after the bulk load, with CREATE INDEX CONCURRENTLY. Then check pg_index.indisvalid, because a concurrent build that fails leaves an invalid index behind that queries will silently ignore.
• If the target is a different server, check the collation version. Text indexes built under a different glibc collation can order differently, which turns into wrong results in range scans rather than an error.

Verification: Counts Are Not Enough

Matching row counts prove that you moved the right number of rows, not the right rows. Three layers, in increasing cost:

Per-chunk checksums. Hash the ordered, concatenated columns of each key range on both sides and compare the hashes. Pin DateStyle, extra_float_digits and the collation in the session on both sides first, or you will spend a day chasing differences that are only formatting.

Invariant checks. Sums of money columns, counts per tenant, referential integrity across the new boundary, and the domain rules that actually matter. In a healthcare system, "every record still has exactly one owning patient" catches classes of bug that a checksum passes over because both sides are equally wrong.

Shadow reads. Send live read traffic to both systems, return the old answer, and log every difference. This is the only technique that tests the new system against real query patterns rather than the ones you thought of. Log the differences, alert on the rate, and do not surface the new answer to anyone until the rate is flat at zero for longer than your longest business cycle.

The Cutover

Then, in this order: shift reads gradually (1 percent, 10, 50, 100, each step reversible in seconds through a flag, not a deploy), let each step bake, and only then shift writes. Reads are safe to move because a wrong answer is recoverable. Writes are not.

Make the switch a configuration change. If flipping traffic requires a deploy, your rollback takes as long as your build pipeline, and that is the moment you will discover the build pipeline is slow.

Set lock_timeout on every DDL statement in the cutover script. PostgreSQL queues lock requests, so one blocked ALTER TABLE waiting for a long-running read will park every query that arrives behind it, and the site goes down without a single error in the migration log. If you use PgBouncer in transaction mode, PAUSE and RESUME give you a clean sub-second connection cutover instead.

Freeze schema changes for the whole window. One helpful colleague shipping a migration mid-cutover has ended more of these than bad SQL.

Rollback Is a Feature, Not a Hope

Define the point of no return explicitly: the moment the new system accepts a write the old one has never seen. Before it, rollback is a flag flip. After it, rollback means replaying the new system's writes backwards, which is a project.

So keep reverse replication running from the new system to the old one for a defined bake period after the write cutover, and keep the old system deployable. The team will want to delete it the week after go-live. Do not let them until the bake period is over and someone has actually looked at the reverse stream.

And rehearse the rollback in staging with production-shaped data. An untested rollback is a paragraph in a document, not a capability.

Schema Changes While the Migration Runs

Everything above assumes the shape holds still. When it cannot, use expand and contract, and never do the two halves in the same release:

• Add columns nullable. A default has been safe since PostgreSQL 11 for non-volatile values, but SET NOT NULL still takes a full scan under an exclusive lock. Add a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT outside peak hours, then set the column not null cheaply.
• Add foreign keys NOT VALID first, validate second.
• Never rename in place. Add the new column, write both, backfill, switch reads, and drop the old one a release later.
• Drop columns last, only after every deployed version has stopped naming them.

The Short Version

Inventory the writers. Choose replication for the same shape and an outbox for a new one. Alert on slot lag. Backfill in resumable chunks. Verify with checksums, invariants and shadow reads. Shift reads before writes, through a flag. Keep the rollback alive past the point of no return, and rehearse it.

The copying is never the risk. The risk is finding out on Monday that the two systems quietly disagreed since Thursday.