Zero-Downtime PostgreSQL Major Upgrade | Logical Replication

The change request usually asks for a Friday night. Stop the application at 11 p.m. Run pg_dump, restore onto the new version, rebuild indexes, run smoke tests, and bring it back before the branches open on Sunday morning. Then someone rehearses the restore on a copy of production. Say it takes eight or nine hours on a large database. That is most of the night, and the upgrade slips another quarter.

If you run PostgreSQL for a bank, a ministry, or any regulated business in the Kingdom, you have probably seen this play out. Meanwhile PostgreSQL 13 is already out of community support. 14 follows in November 2026. And the audit finding about unsupported software comes back every review.

You can move from 14 to 16, 17 or 18 with downtime measured in seconds to a few minutes. Build the new cluster next to the old one, keep it in sync with logical replication, and switch connections when you are ready. Below is how that works, where it bites, and what a real rollback plan looks like. Real meaning you can run it, as opposed to a line on a slide.

Why dump/restore and in-place upgrades hurt

There are three standard ways to cross a major version.

pg_dump / pg_restore. Simple and very safe. The problem is downtime, and it grows with the database. Writes stop for the whole dump, the restore, the index builds and the ANALYZE. On a multi-terabyte core system, that is a long night at best.

pg_upgrade. Much faster, especially with --link, because it rewrites the system catalogs and reuses the data files. Downtime is often minutes. But it is still an in-place cutover. The old cluster stops. Once you start the new one in link mode and accept writes, there is no simple way back to the old version. Every extension also has to exist in compatible versions for both binaries. And you still have to run ANALYZE afterwards. Planner statistics don't carry over (PostgreSQL 18 partly changes this), and without them the first hour of production can be slow.

Logical replication. The new cluster is a separate, running database on the new version. The old one keeps serving production while the new one catches up. You pick the cutover moment, and the old cluster stays there as your fallback. This is the option this post covers.

None of the three is wrong. pg_upgrade --link on a rehearsed system is a perfectly good answer if you can get a 10 to 30 minute window. Logical replication is worth the extra complexity when the window is close to zero, or when the business wants a way back that doesn't mean restoring from backup.

How the logical replication path works

The outline:

Build the target. Install the new major version on new servers (or new pods) with the same extensions, configuration and roles. If you run Patroni, this is a new Patroni cluster with its own leader and replicas.
Copy the schema. Logical replication does not replicate DDL. Run pg_dump --schema-only on the source and apply it to the target. Keep the script. You will need it again.
Publish and subscribe. On the source: CREATE PUBLICATION upgrade_pub FOR ALL TABLES; On the target: CREATE SUBSCRIPTION upgrade_sub CONNECTION '...' PUBLICATION upgrade_pub; The subscription takes an initial copy of each table, then streams changes from WAL.
Let it catch up. Watch pg_stat_subscription on the target and pg_replication_slots on the source until lag is near zero and stays there.
Cut over. Stop writes on the source for a short time. Confirm the target has applied everything, fix sequences, move connections, and start writing to the new version.
Keep the way back open (details below). Decommission the old cluster once you trust the new one.

On a busy database the initial copy can take hours. That's fine, because production keeps running the whole time. Only step 5 needs a write freeze.

Where it bites

The outline fits on one screen. The work is in the details below. Each one has broken a real cutover somewhere.

Every table needs a replica identity

To replicate UPDATE and DELETE, PostgreSQL has to find the row on the subscriber. That takes a primary key, a unique index set with REPLICA IDENTITY USING INDEX, or REPLICA IDENTITY FULL as a last resort. If a table has none of these, updates and deletes on it fail on the source as soon as it joins a publication. FULL works, but the subscriber then scans for every changed row. On large tables that can be very slow.

Find these tables before you start:

SELECT c.oid::regclass
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (SELECT 1 FROM pg_index i
WHERE i.indrelid = c.oid AND i.indisprimary);

Old ERP and core banking schemas usually have more of them than anyone expects.

Sequences are not replicated

In the versions most of you run today, logical replication copies table rows but not sequence values. After cutover, the first insert into a table with a serial or identity column tries to reuse a value that already exists. It fails with a duplicate key error.

The fix is simple, but it has to be in the runbook. During the write freeze, read each sequence's current value on the source and run setval() on the target with some margin. Script it, test it in rehearsal, and check the result before you open the doors.

DDL during the migration breaks things

Schema changes are not replicated. Add a column on the source while the subscription runs, and apply on the target breaks as soon as a row with that column arrives. Agree a schema freeze with the development teams for the whole migration. If a change can't wait, apply it to the target first, then the source.

Large objects and some objects are left behind

Large objects (pg_largeobject) are not replicated. Neither are materialized view contents, or anything outside ordinary tables. Unlogged tables are not replicated either. If your application stores documents with the lo_* functions, plan a separate copy for them during the freeze.

Long transactions and a growing slot

The source keeps WAL for the replication slot until the subscriber confirms it. If the subscriber falls behind, or a long-running transaction on the source holds the slot back, WAL piles up in pg_wal. On a busy system that can fill the disk and take production down. That is exactly what the upgrade was supposed to avoid.

Before and during the migration:

Find long-running and idle-in-transaction sessions in pg_stat_activity. Agree with the application owners how to handle them.
Set max_slot_wal_keep_size on the source so a stuck slot can't fill the disk. The slot gets invalidated instead. Annoying, but recoverable.
Alert on pg_wal size and on slot lag, not only on replication being "up."

Extensions and catalog differences

Every extension you use needs a version that installs on the new major release. Check PostGIS, pg_partman, pgaudit and any in-house C extensions early. Read the release notes for every version you skip, too. Going from 14 to 17 means reading the incompatibility sections for 15, 16 and 17, not just 17. Removed functions, the changed default privileges on the public schema (changed in 15), and planner differences turn up in testing. They don't turn up in the migration plan.

Patroni does not do major upgrades for you

Patroni keeps a cluster highly available within one major version. Crossing a major version is not a switchover it can perform. With logical replication you build a second Patroni cluster on the new version, replicate into its leader, and move the application across. The upside is that HA stays intact on both sides the whole time. The old cluster keeps its replicas and failover. The new one is fully HA before it takes a single production write.

One detail to plan for. Until recent versions, logical replication slots lived only on the primary and were lost on failover, which broke the subscription. PostgreSQL 17 adds failover slots that can be synchronised to standbys. On an older source, decide now what you will do if the old primary fails during the migration window. Usually that means re-creating the subscription and copying again.

The cutover, minute by minute

Once lag is close to zero, a typical cutover goes like this:

Freeze writes on the source. Pause the application, revoke write access from the application roles, or put PgBouncer into PAUSE. Pausing at the pooler is often cleanest, because clients wait instead of failing.
Confirm the target has caught up. Compare pg_current_wal_lsn() on the source with the subscription's latest_end_lsn in pg_stat_subscription on the target.
Sync sequences with your script.
Run the checks you rehearsed. Row counts on key tables, and a few business totals that must match.
Move connections. Point PgBouncer, the DNS name, or the virtual IP at the new cluster. If applications connect straight to host names, this step takes longest. Fix that before migration day.
Resume. Writes now go to the new version.

With a pooler in front and a rehearsed script, the freeze usually lasts seconds to a few minutes. Most of that is checking. Don't cut it short.

A rollback plan that actually works

"We'll restore from backup" is not a rollback plan for a core system. You lose every transaction since the backup.

Logical replication gives you something better. Before you resume writes on the new cluster, create a reverse publication there and a subscription on the old cluster, with copy_data = false because the data is already in place. Now the old cluster receives every change made on the new version. Say something serious shows up in the first few days, like a query plan that collapses or an extension that behaves differently. You freeze writes again, sync sequences in the other direction, and move connections back. No data lost.

Logical replication supports replicating from a newer major version to an older one. Test that direction in rehearsal anyway. Then decide in advance how long you keep the reverse path and the old cluster, and who has the authority to call a rollback.

Rehearse it on real data

Every problem above shows up in rehearsal, and rehearsal is where you want to find it. Run the full procedure at least once against a recent copy of production, in an environment that looks like production:

Time the initial copy, and watch WAL growth on the source while it runs.
Run your real application, or at least the critical batch jobs and reports, against the new version. Compare plans for the slow queries.
Run the cutover script end to end, including sequences and connection switching. Then run the rollback script.
Write down the timings. They become your change request.

In regulated environments, the rehearsal report is also what your change advisory board and auditors will ask for.

Where Worlber fits

Worlber is an authorised CYBERTEC reseller in Saudi Arabia, with a team in Riyadh. CYBERTEC has spent many years running PostgreSQL in production for enterprises. PGEE, its enterprise PostgreSQL distribution, is built around PostgreSQL with HA on Patroni. If you are planning a major upgrade, or moving to PGEE at the same time, we can help. We check the schema for replica identity gaps, plan the extension path, design the cutover and rollback, and rehearse it with your team.

Tell us which version you run, how big the database is, and what maintenance window you can get: Plan your PostgreSQL upgrade with Worlber.