Zero-Downtime Multi-TB Migrations: Cutover, Validation and Rollback (Part 2)

“The plan looks great. So what actually happens at 2am on Sunday?”

That was the CTO again, once we’d walked through Part 1: logical replication, the database split into units, five waves, the last one fixed on Sunday 6. Fair question. A plan is only as good as the night you run it.

So this part is about that night: how we proved the data matched, how a cutover fits into five minutes, how to keep a way back you can actually use, and why we rehearsed until it was dull. Functional testing of the apps is still out of scope. What customers see during the pause, and when a payment gets rolled back, is very much in.

Prove the data matches

We agreed what “matching” meant before we started, because 3am is a bad time for that argument. We checked in three layers, cheapest first:

1. Row counts per table. They catch the embarrassing stuff, like a table that never started replicating.
2. Checksums by primary key range. A mismatch points at one chunk instead of 3 TB. Frozen ranges you can compare any time. Hot tables need a fixed snapshot, because a live comparison reports lag as corruption. And please don’t hash 3 TB on the primary in the middle of the business day.
3. Business checks. Yesterday’s payment total, merchant balances, the highest order ID. Whatever finance will check on Monday, check it first.

Collation deserves its own warning. Compare by key, not by sorted text, because a newer glibc or ICU can put the same rows in a different order, and that’s just a false alarm. A unique text index that won’t build, or that treats two values differently, is a real failure, and we treated it as one.

We validated continuously during catch up, unit by unit. And since this was an upgrade as well, we ran ANALYZE after each copy and compared the plans of the busiest queries on both sides. Not load testing, just making sure 16 hadn’t picked a very different route.

Rehearse at full size

You can’t copy 12 TB five times inside a twelve week lease, so we didn’t try. We did two full size rehearsals against real replication, a second subscription off production into a scratch instance, with real lag and the real roles, and used smaller copies to drill the SQL. A 50 GB sample would never have shown us the WAL pressure, the apply lag or the CTE regression.

We ran each one like the real night and timed every step. And we proved the way back: a PostgreSQL 11 database subscribed to 16, applying a real peak hour. If you skip one rehearsal, don’t let it be that one.

Here’s some of what the rehearsals turned up:

• a dozen tables with no primary key, one with 400 million rows
• a sequence in another schema that the reset script skipped
• WAL filling the source disk during the biggest initial copy
• lag hitting forty minutes in the month end batch, because authorisations and the ledger share one apply worker
• a runbook step that only worked from one engineer’s laptop
• a settlement report going from 2 seconds to 3 minutes after 16 folded its CTE into the main query. One word fixed it: AS MATERIALIZED
• an old batch client that couldn’t log in with SCRAM. A driver upgrade fixed it

Every one of those would have been a bad night in production. In rehearsal, they were just tickets.

Cut over one wave at a time

Every cutover follows the same idea. Everything that’s moving has been replicating old to new since day one, one publication per unit. A cutover just flips one unit: its tables stop being written on the old side and copied across, and start being written on the new side and copied back. At any moment each table has exactly one writing side, and both databases hold all the live data.

The runbook was short, and every step had a named owner (shown by role in the card below).

An hour before, we ran the go or no go. Every answer had to be yes:

• Initial copy finished for every table in the unit. We checked pg_subscription_rel for state r, meaning ready, because a subscription can look healthy while one big table is still copying.
• Lag in seconds, the last validation green, and fresh statistics on the new side.
• Grants scripted, not applied. An app that connects early would write into tables a live subscription is still filling.
• Reverse path staged. A publication on the new side, and a subscription on the old side created with connect = false, so no slot exists yet. A slot made days early would collect changes still arriving from the old side and send them straight back.
• The way back proved in rehearsal, and the rollback owner on the call.

One no, and we used a spare Sunday. That’s what they’re for.

Then the clock started. These are wave 1’s times from its final rehearsal:

The first time we rehearsed wave 1 it took eleven minutes. On the night it was under three, mostly because steps 2 to 5 were scripted and nobody was typing SQL under pressure.

Run two databases at once

For a few weeks you’re running two databases, with replication flowing both ways, and it feels odd. These were the rules that kept it safe:

• Lag is visible. Reads on the side that isn’t writing are a second or two old, so the reads that care stay with the writer.
• No transaction spans units, and no insert depends on a parent written seconds ago on the other side.
• Triggers fire on the writer only. Replicated changes skip ordinary triggers, so the audit row travels as its own change. Set the trigger to ENABLE ALWAYS and you’ll get every audit row twice.
• DDL stays frozen, apart from the next monthly partitions, which have to exist on both sides already and be published in the right direction.
• Don’t fail over casually. On 11, slots live only on the primary (failover slots arrived in 17), so a failover breaks every subscription and the big tables copy again. We wrote the resync procedure first and agreed nobody would fail over for convenience during a 1.4 TB copy.
• Drop each wave’s dead slot the same night, and keep watching slots, disk and the oldest transaction right to the end.

Keep a way back

How you roll back depends on where you are in the runbook when something goes wrong. We planned for three cases:

The first two are mostly about keeping your nerve. We used the first one once, in a rehearsal, when the lag check stalled at 1:40 and nobody wanted to guess why. The apps were back on the old side a couple of minutes later.

The third case took the most work. Going from 16 back to 11 means replicating down five major versions, and PostgreSQL doesn’t promise that works, so we tested it: an 11 database subscribed to 16 through a full peak hour in rehearsal. It held. If it hadn’t, we’d have told the CTO plainly that once the new side takes its first write there’s no clean way back, and then either lived with that or built a separate feed of changes that 11 could replay. And don’t forget to grant writes back on the old side. We nearly did, in rehearsal, and the apps came home to a database that wouldn’t let them write.

If you ever have to undo more than one wave, go in reverse order, one unit at a time, or a payment can end up pointing at a category the old side hasn’t got yet.

We kept the old database running, patched and monitored for two weeks after the last wave, or until the first schema change on the new side, whichever came first. That was our point of no return, and it’s why Sunday 6 had to be the last cutover. Finance asked twice whether we really needed to keep paying for it. We did.

What bites people

If I had to name the ones I see most often:

• Planning the copy and forgetting the catch up. The catch up decides whether you can ever cut over.
• Big tables with no primary key, found in week ten instead of week one.
• Sequences not reset, so the first insert on the new side collides.
• Forgotten consumers still pointing at the old database. Revoked grants are what catch them.
• Treating the upgrade as free, until one important query picks a terrible plan.
• A forgotten slot. A disabled subscription keeps its slot, the slot keeps WAL, and 11 lets it fill the disk.
• Splitting a transaction across two waves. Fine until someone checks the ledger.
• No proven way back, because “it went fine in test.”

None of these are exotic. They’re just what gets skipped under deadline pressure, which is always.

Several of them you can catch on day one, before a single subscription exists. I’ve put the read-only check we wish we’d run first into a script: pg-logical-replication-precheck.sql. Point it at the source with psql -X -f. It lists tables without a primary key, sequences and their current values, partitions, large objects, triggers that would fire twice, extensions, MD5 passwords, risky unique text indexes, and any replication slot already quietly holding WAL. It doesn’t write or lock anything, and it won’t find the cron job on the old VM. That still takes a month of connection logs.

Wrapping up

So how did it go? Mostly to plan. One wave slipped a week after a validation false alarm (the check had compared a live chunk while the subscriber was a second behind), and that’s exactly what the spare Sunday was for. All five cutovers finished inside four minutes, and the old database was switched off before the lease ran out.

Looking back, nothing we used was new. Logical replication has been in PostgreSQL since version 10, moving in waves is old project management, and the runbook was a checklist on one page. What got us through was doing the dull parts properly: a month of connection logging, rehearsing until the times stopped changing, and keeping a spare Sunday that nobody wanted to give up. The upgrade came along inside the same project instead of becoming a second one. At this size, the plan matters far more than the tool.

References

1. Logical Replication, PostgreSQL 16 documentation
2. CREATE SUBSCRIPTION, PostgreSQL 16 documentation, including the copy_data option used when starting reverse replication
3. Logical Replication Restrictions, PostgreSQL 16 documentation
4. Monitoring Logical Replication, PostgreSQL 16 documentation
5. Sequence Manipulation Functions, PostgreSQL 16 documentation
6. pg_replication_slots, PostgreSQL 16 documentation, for confirmed_flush_lsn and restart_lsn
7. pg_subscription_rel, PostgreSQL 16 documentation, for per table sync state
8. PostgreSQL 17 Release Notes, including failover of logical replication slots