--- title: "Zero-Downtime PostgreSQL Major-Version Upgrade at Scale" description: "How we moved a multi-terabyte PostgreSQL 12 database to 16 with no maintenance window, using logical replication, dual-read validation, and a rehearsed, timed cutover with a real rollback trigger." author: "Mohammad Abu Mattar" canonical: https://mkabumattar.com/case-studies/post/zero-downtime-postgres-upgrade --- # 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 The migration had to satisfy four hard constraints, and every design decision came back to them. - **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. _Replication and cutover topology_ 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. _Logical replication objects (class view)_ 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. _Deployment topology_ ## 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. _Upgrade phases (state machine)_ 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`. ```sql title="On the source (PostgreSQL 12)" -- Stream every table in the app schema. CREATE PUBLICATION app_pub FOR ALL TABLES; -- Tables without a primary key need a full replica identity -- so UPDATE and DELETE can be replicated. 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. ```sql title="On the target (PostgreSQL 16)" -- Schema and a consistent snapshot are already restored here. -- Subscribe for ongoing changes only; the bulk data is already present. CREATE SUBSCRIPTION app_sub CONNECTION 'host=source.internal dbname=app user=repl' PUBLICATION app_pub 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. ```sql title="Watching the gap close" -- On the source: how far behind is the subscriber? SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS behind FROM pg_replication_slots; -- On the target: is the subscription streaming and healthy? SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time 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. Logical replication does not carry everything. Sequences are not replicated, DDL is not replicated, and large objects in `pg_largeobject` are not replicated. Freeze schema changes for the migration window, plan to reset sequences at cutover, and handle large objects separately. `pglogical` can sync sequences for you if you would rather not script it. 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. _Cutover sequence_ 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. _Cutover runbook (activity)_ 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. 1. Pause new writes at PgBouncer with `PAUSE`, which lets in-flight transactions finish and holds new ones. 2. Confirm on the source that the replication slot has drained to zero lag. 3. Reset every sequence on the target from the source's current values, then run `ANALYZE` so the planner has fresh statistics. 4. Repoint PgBouncer at the target and `RESUME`, so held connections wake up talking to PostgreSQL 16. 5. Run smoke tests. If they pass, announce done. If not, repoint back to the source and resume there. ```bash title="Reset sequences on the target from the source" # 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 > **Why not just use pg_upgrade or an in-place RDS major upgrade?** Both stop the database for the duration and give you no clean abort once they start. On a multi-terabyte, 24/7 workload that downtime and that lack of a rollback were unacceptable. Logical replication lets you build the new version alongside the old one and switch over in a short, reversible step instead. > **What is the difference between logical and physical replication here?** Physical (streaming) replication copies disk blocks and requires both sides to run the same major version, so it cannot help you upgrade. Logical replication ships row-level changes decoded from the WAL, which is version-independent, so a PostgreSQL 12 primary can feed a PostgreSQL 16 subscriber. > **Why do sequences need special handling?** Logical replication streams table data but not sequence values, so the target's sequences would still sit at wherever the initial copy left them. If you skip the reset, the first inserts after cutover can collide with existing primary keys. We reset every sequence from the source's live values as part of the cutover, just before resuming writes. > **How did you seed a multi-terabyte target without hurting the source?** We restored a recent snapshot and the schema into the target first, then created the subscription with copy_data set to false so it only carried changes from that point forward. That avoids a giant online copy that would compete with production traffic. On a small database you can let the subscription copy the data itself. > **What was the actual rollback plan?** Until the final switch, the source stayed primary and unmodified. If replication did not reach zero lag inside the time budget, or the post-cutover smoke tests failed, we repointed PgBouncer back at the source and resumed writes there. Because the source never diverged, that was instant and lossless. > **Does this work on Amazon RDS?** Yes. Set rds.logical_replication to 1 in the parameter group and reboot once ahead of time so the source starts producing logical WAL. From there the publication, subscription, and slot work the same as on self-managed PostgreSQL. The target can be a fresh RDS instance on the new major version. > **What about large objects and extensions?** Neither rides along automatically. Extensions must be installed on the target before you subscribe, and large objects stored in pg_largeobject are not replicated by native logical replication, so migrate them separately during the window. If your schema leans heavily on either, factor that into rehearsals. ## References - [PostgreSQL: Logical Replication](https://www.postgresql.org/docs/current/logical-replication.html) - [PostgreSQL: CREATE PUBLICATION](https://www.postgresql.org/docs/current/sql-createpublication.html) - [PostgreSQL: CREATE SUBSCRIPTION](https://www.postgresql.org/docs/current/sql-createsubscription.html) - [PostgreSQL: Replication Slots and pg_replication_slots](https://www.postgresql.org/docs/current/view-pg-replication-slots.html) - [pglogical (2ndQuadrant / EDB)](https://github.com/2ndQuadrant/pglogical) - [Amazon RDS for PostgreSQL: Logical Replication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication) - [PgBouncer: PAUSE and RESUME](https://www.pgbouncer.org/usage.html)