Zero-Downtime PostgreSQL Major-Version Upgrade at Scale

A multi-terabyte PostgreSQL 12 database was reaching end of life, and the business ran around the clock, so the usual answer of “schedule a maintenance window” was off the table. We upgraded it to PostgreSQL 16 while users kept reading and writing the whole time. The write pause during the final switch was measured in seconds, and the old database stayed hot the entire cutover so we could fail back instantly if anything looked wrong. This is how the migration was designed, rehearsed, and executed.

Impact

The upgrade happened while users kept reading and writing. The final switch cost a write pause measured in seconds and lost no rows, with the old database kept hot for instant failback.

The safety came from never modifying the source until the very last step, so failback stayed a genuine option the entire time. These numbers are specific to this workload and were measured on our own traffic, so treat them as a shape to expect rather than a guarantee.

The problem

The classic upgrade paths all needed a window we did not have. pg_upgrade with hard links is fast, but it still stops the database, and on a multi-terabyte instance you cannot risk a long tail if something goes wrong mid-upgrade. A dump and restore was measured in hours, which was a non-starter. Even RDS in-place major upgrades take the instance offline for the duration and give you no clean way to abort once they begin.

The workload was write-heavy and latency-sensitive, so we could not just pause the application either. What we needed was a way to build the new version alongside the old one, keep it continuously in sync with live traffic, and switch over in a single short, reversible step.

Constraints

• No maintenance window. The only acceptable interruption was a brief write pause during the final switch, on the order of seconds.
• Reversible at every step. Until we were certain, the old PostgreSQL 12 primary had to stay untouched and ready to take traffic back.
• Multi-terabyte, so the initial copy is not free. Seeding the target could not lock the source or saturate its I/O during business hours.
• Correctness of the awkward bits. Sequences, extensions, large objects, and tables without a primary key all needed explicit handling, because logical replication does not carry all of them for you.

Architecture

The core idea is a source and a target running side by side, with logical replication streaming changes from the old database to the new one. The application never talks to Postgres directly; it goes through PgBouncer, which is what lets us flip traffic in one place at cutover time.

Logical replication works at the level of rows, not disk blocks, which is exactly why it can span major versions. A publication on the source declares which tables to stream, and a subscription on the target consumes that stream through a replication slot that tracks how far the target has consumed. Those three objects are the whole contract.

Physically, nothing about the deployment is exotic. The application nodes pool connections through PgBouncer, PgBouncer points at the source on port 5432, and a second, dormant route to the target waits for the switch. Both databases publish replication lag and error metrics so we can watch the gap close in real time.

Implementation

The upgrade moved through a fixed set of phases, and treating it as a small state machine kept everyone honest about which step we were on and what “done” meant for each one.

First we turned on logical replication on the source. On RDS that means setting rds.logical_replication to 1 in the parameter group and rebooting once, well ahead of the migration. Tables that get updates or deletes need a replica identity so those changes can be matched on the target; a primary key covers most, and anything without one gets REPLICA IDENTITY FULL.

1
-- Stream every table in the app schema.
2
CREATE PUBLICATION app_pub FOR ALL TABLES;
3

4
-- Tables without a primary key need a full replica identity
5
-- so UPDATE and DELETE can be replicated.
6
ALTER TABLE audit_events REPLICA IDENTITY FULL;

To seed the target without hammering the source during the day, we restored the schema and a recent snapshot into the new PostgreSQL 16 instance first, then created the subscription with copy_data = false so it only carried changes from that point forward. On a smaller database you can let the subscription do the initial copy itself, but at multiple terabytes the snapshot route keeps the source calm.

1
-- Schema and a consistent snapshot are already restored here.
2
-- Subscribe for ongoing changes only; the bulk data is already present.
3
CREATE SUBSCRIPTION app_sub
4
  CONNECTION 'host=source.internal dbname=app user=repl'
5
  PUBLICATION app_pub
6
  WITH (copy_data = false, create_slot = true, slot_name = 'app_sub_slot');

From there it was a waiting game while the target caught up. We watched the lag from both ends until the gap held near zero under normal write load.

1
-- On the source: how far behind is the subscriber?
2
SELECT slot_name, active,
3
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS behind
4
FROM pg_replication_slots;
5

6
-- On the target: is the subscription streaming and healthy?
7
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
8
FROM pg_stat_subscription;

Before touching traffic, we validated parity with dual reads. A read-only checker ran the same queries against both databases and compared row counts and checksums on the busiest tables. We wanted proof that the target was a faithful copy, not just a hopeful one.

The cutover itself was a short, rehearsed sequence. The point of writing it as an ordered exchange between the operator, PgBouncer, and the two databases was to make the timing and the ordering unambiguous.

We drove it from a runbook with a hard time budget and an explicit rollback branch. If replication did not reach zero lag inside the budget, or the post-cutover smoke tests failed, we resumed writes on the untouched source and walked away to try another day.

The two commands at the heart of the switch are pausing the pool and resetting sequences, since sequence values do not come across on their own.

# Emit setval() calls from the source, apply them on the target.
psql "$SOURCE" -Atc "SELECT format('SELECT setval(%L, %s);', seqrelid::regclass, last_value)
                     FROM pg_sequences_lastvals()" \
  | psql "$TARGET"

Results

The measured write pause during the switch was a handful of seconds, dominated by draining in-flight transactions rather than any copy. Read traffic was never interrupted, since reads could keep hitting the source until the pool repointed. No rows were lost, which the dual-read parity checks confirmed both before and after cutover.

Because the source stayed primary until the very last step and was never modified, rollback stayed a genuine option right up to the point we chose to decommission it, which we did only after a full business day of clean operation on PostgreSQL 16. These numbers are specific to this workload and were measured on our own traffic; treat them as a shape to expect, not a guarantee.

Lessons

The single most valuable thing we did was rehearse the cutover against a copy until the runbook was boring. The first rehearsal surfaced the sequence problem, the second surfaced a table with no primary key, and by the third the whole thing was muscle memory.

Watching replication lag as a first-class metric mattered more than any single command. The go or no-go decision at cutover was a number on a dashboard, not a gut feel. And keeping the old primary untouched turned rollback from a scary, multi-hour restore into a one-line repoint, which is what made the whole plan safe enough to run against production in the first place.

Frequently Asked Questions

References

• PostgreSQL: Logical Replication
• PostgreSQL: CREATE PUBLICATION
• PostgreSQL: CREATE SUBSCRIPTION
• PostgreSQL: Replication Slots and pg_replication_slots
• pglogical (2ndQuadrant / EDB)
• Amazon RDS for PostgreSQL: Logical Replication
• PgBouncer: PAUSE and RESUME