Zero-Downtime Multi-TB Migrations: Strategy and Grouping (Part 1)

“We have a migration. It’s a big one. And we’ve already tried twice.”

That’s how the call opened. A payments company, 12 TB on PostgreSQL 11 in their own data centre, and twelve weeks until the hardware lease ran out. They needed to land on a managed cloud service and upgrade to PostgreSQL 16 on the way, five major versions in one jump. The CTO wanted zero downtime. Their test restore was still running four days later, and the only window anyone would sign off was two hours early on a Sunday.

I’ve seen some version of this story plenty of times, and the teams that get through it never do it by finding a cleverer tool. They stop treating it as one huge migration, break it into smaller ones, and let the upgrade ride along. There are about fifteen steps between that phone call and switching off the old database. Here’s the whole map. I’ll go deep on the ones that make or break the project, and leave the rest at a line each. This post is the planning. Part 2 is how we ran it. Functional testing of the apps isn’t covered in either.

Agree what “zero downtime” means

“Zero downtime” was the first thing we pushed back on. Nobody really gets zero, so we sat down with the business owner and agreed a number we could actually plan against.

What we landed on: reads keep working the whole time, and writes pause for no more than five minutes per cutover. That’s something you can design for, rehearse and measure. “Zero” on its own is a wish.

The window came out of the same conversation. Payments traffic bottoms out between 2 and 4 on a Sunday morning, so that’s the only time the business would sign off a cutover. Five minutes was what they could absorb, short enough for retries and queued payouts to catch up before any customer noticed. The rest of the two hours is for checks before and watching after.

Why logical replication

At 50 GB you can dump and restore over lunch. At 12 TB, the maths just stops working:

Even the best case needed eleven of those two hour windows back to back, so dump and restore was out. The usual on-prem trick, building a replica and promoting it, went next. A managed service won’t act as a physical replica of your own server, and physical replication can’t cross major versions anyway.

That left logical replication. It sends row changes rather than disk blocks, so it works across major versions, and every row lands on 16 as it arrives. The upgrade rides along with the move instead of becoming a second project. Two words you’ll see a lot from here: a publication is the list of tables the source offers, and a subscription is the target pulling their changes, applied by a single worker.

It comes with homework, and the parts that bite hardest on PostgreSQL 11 are these:

• Every table needs a primary key, or updates and deletes won’t replicate.
• DDL and sequences don’t replicate. We froze schema changes and set sequences by hand at cutover. The exception was monthly partitions: 11 publishes each one separately, so someone had to create next month’s on both sides ahead of time.
• The source holds WAL until the target catches up, and 11 has no cap. We watched slots and free disk every day.
• No dual writes from the app. Nothing keeps two databases in step, and that’s a hill I’m happy to stand on.

Whatever the tool, a migration this size always takes the same shape:

Most of the effort goes into phase 1. Almost all the risk lives in phases 2 and 3.

Take inventory

Before we planned a single wave, we built an inventory, one entry per table. It’s dull work, and every decision after it leans on it. Here’s the entry for one of the busiest tables, boiled down:

That last tag is the one that nearly got us. The third writer was a reconciliation script on an old VM. It ran at 3am on the first of the month, connected by IP address and wrote straight into the payments tables. Nobody in the room knew it existed. We only found it because we left connection logging on for a full month, so it caught a first of the month before any payments wave. A week would have missed it.

Split into units

This is the bit that turned the project around. Instead of one 12 TB move, we planned several, each with its own cutover. A migration unit is a set of tables that switch over together, along with the services that write them. Each wave repoints those services at the new database, so it’s an app change as well as a database one.

What makes it safe is simple. From day one, everything that’s moving copies from old to new, so both databases always hold all the live data, and a cutover only changes which side writes to one unit’s tables.

We drew the lines with three questions:

1. Which tables are written in the same transaction? They go in the same unit and the same stream. That’s the only hard rule. Break it and the new side can show a payment without its ledger entry. It’s why authorisations, the ledger and the audit log’s current month shared one subscription, and one worker.
2. Which service writes them? We grouped by writer, so each cutover was simply “move this service’s writes.”
3. What breaks if the data is a second old? Reads across units work on either side, just slightly behind. Inserts are trickier: a payment against a merchant category created seconds ago on the other side fails its foreign key. A new merchant doesn’t take a payment for hours, so a second of lag there is harmless. A new category can be used on the very next payment, so we froze those until their writer moved.

Inside each unit, two more questions decided how each table travelled: how big is it, and how often does it change?

The big win was in the append only box. The audit log was 5 TB, and its old partitions never change, so we copied 4.5 TB of them ahead of time and replicated only the current month. The cold box saved another 1.3 TB: with compliance’s blessing, the old exports went to object storage and never moved at all.

Plan the waves backwards

The most useful planning habit I know: don’t start from the first cutover and see where you end up. Start from the date you can’t move and work backwards.

1. Find the hard date. The lease end, the end of support, the contract renewal.
2. Subtract the fallback. The old database stays alive and current for a while after the last cutover. Two weeks is typical.
3. That’s your last cutover.
4. Place the waves backwards, a week apart, cheapest first, with a free spare right before the riskiest one.
5. What’s left is preparation. If that’s under about four weeks, push back on the date now, not in week six.

Two rules kept our calendar honest. A spare after the hard date isn’t a spare, it’s a lease extension. And a freeze lasts until the writer moves, so if a wave slips, the freeze slips with it.

Here’s how it came out for the payments team:

The lease was the hard date. Two weeks of fallback put the last cutover on Sunday 6, which left about five weeks to prepare. Payments, the riskiest wave at 3.5 TB, got the free spare in front of it. Reference data went last, so new currencies and categories were frozen from Sunday 5 until it moved.

What’s next

By this point we had a downtime number everyone had agreed to, logical replication carrying the upgrade, and the database sorted into waves. Part 2 is about running them: proving the data matches, the five minute cutover, an honest way back, and a lot of rehearsal.

References

1. Logical Replication, PostgreSQL 16 documentation
2. Logical Replication Restrictions, PostgreSQL 16 documentation, covering DDL, sequences and large objects
3. Log-Shipping Standby Servers, PostgreSQL 16 documentation, including the note on major versions
4. Versioning Policy, PostgreSQL
5. PostgreSQL 12 Release Notes, including CTE inlining and removal of WITH OIDS
6. PostgreSQL 14 Release Notes, including the SCRAM password default
7. PostgreSQL 15 Release Notes, including public schema permissions
8. PostgreSQL 13 Release Notes, including publish_via_partition_root and max_slot_wal_keep_size
9. PostgreSQL 16 Release Notes, including parallel apply and the origin option