PostgreSQL Major Version Upgrade Zero Downtime: pg_upgrade vs Logical

Why major version upgrades need special handling

Minor releases of PostgreSQL share an on-disk format, so a minor update is a binary swap and a restart. Major versions are different. The system catalogs, which are the internal tables describing your schemas, types, functions and statistics, can change layout between major versions, and the server records its major version in the data directory. A new-version server will refuse to start on an old-version data directory. Installing new binaries and restarting is not enough.

This also rules out the usual way of standing up a parallel copy. Streaming physical replication ships WAL, which describes changes at the level of data pages. The replica must interpret those pages exactly as the primary wrote them, so the primary and replica have to run the same major version. You cannot attach a new-version standby and promote it.

That leaves three practical approaches:

• Dump and restore. Export with pg_dump and load the dump into a new cluster. This is the simplest and most portable option. Writes must stop for the whole export and import to get a consistent result, so downtime grows with data size.
• pg_upgrade. This tool rebuilds the catalogs for the new version and reuses the existing data files instead of rewriting them. It is fast, but it requires stopping the old cluster, so it is a short-downtime tool, not a zero-downtime one.
• Logical replication. This method decodes row-level changes and applies them to a new-version cluster that runs alongside the old one. It is the only one of the three that can bring downtime close to zero, at the cost of more moving parts.

How pg_upgrade works and where the downtime goes

pg_upgrade takes a different approach. It does not dump and reload your rows. It creates fresh system catalogs in a newly initialized cluster by restoring a schema-only dump, then moves the existing user data files into it. The on-disk format of table and index files is compatible across major versions, so pg_upgrade only rebuilds the metadata layer. In copy mode, every data file is duplicated, so runtime grows with database size. In link mode (--link), files are hard-linked instead. Runtime then depends mostly on the number of objects (tables, indexes, sequences, functions), not on terabytes. A database with tens of thousands of partitions can take longer than a much larger one with a simple schema.

The workflow:

1. Run pg_upgrade --check with the old and new binary and data directories (for example -b /path/old/bin -B /path/new/bin -d /path/old/data -D /path/new/data). This reports incompatibilities while the old cluster is still serving traffic.
2. Stop the old cluster. Downtime starts here.
3. Run pg_upgrade for real, usually with --link. After you start the new cluster in link mode, the old cluster is no longer safe to start, because both clusters share the same files.
4. Start the new cluster.
5. Run vacuumdb --all --analyze-in-stages. Depending on the version, planner statistics may not carry over. This command produces rough statistics quickly and refines them in later passes, which lets you admit traffic sooner.

Physical standbys cannot follow the upgrade. You have two options:

• Rebuild them from a new base backup.
• Use the documented rsync procedure. You run it after pg_upgrade but before you start the new primary, with both clusters stopped and exact directory arguments.

The rsync procedure is fragile. If you get the ordering or a path wrong, you can end up with a corrupted standby that still starts without complaint.

Copy#!/usr/bin/env bash
# Run as the postgres OS user from a writable directory (pg_upgrade writes logs there).
set -euo pipefail

OLD_BIN=/path/old/bin
NEW_BIN=/path/new/bin
OLD_DATA=/path/old/data
NEW_DATA=/path/new/data   # same filesystem as OLD_DATA, required for --link

# 0. Initialize the new cluster beforehand. Match encoding, locale and
#    data checksum settings of the old cluster or --check will fail.
"$NEW_BIN/initdb" -D "$NEW_DATA"

# 1. Dry run while the old cluster still serves traffic.
"$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" \
  -d "$OLD_DATA" -D "$NEW_DATA" --link --check

# 2. Downtime starts: stop the old cluster cleanly.
"$OLD_BIN/pg_ctl" -D "$OLD_DATA" stop -m fast

# 3. Real upgrade. Rebuilds catalogs and hard-links data files, so runtime
#    tracks object count, not data volume.
"$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" \
  -d "$OLD_DATA" -D "$NEW_DATA" --link

# Standbys: rebuild them, or run the documented rsync procedure NOW,
# before the new primary is started for the first time.

# 4. Start the new cluster. From here the old cluster must never be started,
#    because both clusters share the same data files.
"$NEW_BIN/pg_ctl" -D "$NEW_DATA" -l /path/new/logfile start

# 5. Build planner statistics: coarse first, then refined passes.
"$NEW_BIN/vacuumdb" --all --analyze-in-stages

A placeholder-path shell sequence for a link-mode pg_upgrade: check, stop, upgrade, start, then staged ANALYZE, with the window for handling standbys marked.

How a logical replication upgrade works

Logical replication avoids that file-level fragility because it never copies data files. The old cluster decodes its WAL into row-level changes and sends them to a new cluster that has already been initialized on the target version. The two clusters exchange changes, not disk pages, so they can run different major versions.

Prerequisites on the source

• wal_level = logical. Changing it requires a restart, so do it during a quiet period well before migration day.
• A primary key, or an explicit REPLICA IDENTITY, on every replicated table. Without one, the subscriber cannot locate the target row for UPDATE and DELETE, and the publisher rejects those operations on that table.
• Enough max_replication_slots, max_wal_senders and, on the subscriber, logical replication workers to cover the initial sync.

The sequence

1. Run CREATE PUBLICATION upgrade_pub FOR ALL TABLES; on the old cluster.
2. Load the schema on the new cluster with pg_dump --schema-only.
3. Run CREATE SUBSCRIPTION ... PUBLICATION upgrade_pub; on the new cluster. This creates a replication slot on the source and starts copying each table.
4. Wait until every row in pg_subscription_rel shows srsubstate = 'r', which means the table is ready.
5. Track lag on the source by applying pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) to pg_replication_slots. On the target, check pg_stat_subscription.

The application keeps writing to the old cluster through the connection pooler the whole time. The pooler is the single switch you flip at cutover.

What does not replicate

DDL, sequence values and large objects are not replicated. A schema change on the source can break the apply worker, so freeze DDL for the whole migration. You will also need to copy sequence values manually before cutover.

Copy-- On the OLD cluster (source). Requires wal_level = logical.
SHOW wal_level;
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- Find tables that cannot replicate UPDATE/DELETE (no primary key).
SELECT c.oid::regclass AS table_name
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)
  AND c.relreplident IN ('d', 'n');  -- 'f' = FULL, 'i' = index: both OK

-- On the NEW cluster, after loading pg_dump --schema-only output.
-- This creates a slot on the source and starts the initial table copy.
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-db.internal port=5432 dbname=appdb user=replicator password=change_me'
  PUBLICATION upgrade_pub;

-- NEW cluster: every table must reach 'r' (ready) before you plan cutover.
SELECT srrelid::regclass, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';

-- NEW cluster: apply worker progress.
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

-- OLD cluster: bytes of WAL the subscriber has not yet confirmed.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

Creates the publication and subscription, finds tables with no primary key or usable replica identity, and monitors initial sync state and slot lag on both clusters.

A step-by-step cutover plan

Before cutover

Run validation while replication is still live, so a failed check costs nothing. Compare row counts and per-table checksums on critical tables (for example, an aggregate hash over primary-key-ordered rows). Run the application test suite against the new cluster. Confirm every extension exists there at a compatible version. Run EXPLAIN on your hottest queries, because planner changes between major versions can flip a plan.

The cutover

1. Confirm lag is near zero. On the publisher, compare pg_current_wal_lsn() with the slot's confirmed_flush_lsn in pg_replication_slots.
2. Stop writes on the old cluster. Pause the pooler, or set the database to read-only and terminate existing sessions. Changing the default only affects new connections.
3. Confirm the subscriber has applied everything. Wait until the confirmed flush position reaches the publisher's final WAL position. Only then is nothing left in flight.
4. Sync sequences. Call setval on each sequence with the source value plus a safety margin.
5. Switch connections through a pooler target change, a DNS update or an application config change. Pooler changes are fastest. With DNS, you have to wait out TTLs and client-side caching.
6. Accept writes on the new cluster. Disable the subscription so a stray connection to the old cluster cannot replay changes into the new one.

After cutover

Re-run the row counts and smoke tests. Run ANALYZE, because planner statistics do not carry over.

Why it is never truly zero

Writes pause for seconds to minutes. Clients reconnect, and in-flight transactions on the old cluster abort and must retry. Cold caches and fresh statistics make the first minutes slower than steady state.

Copy-- [OLD] Step 1: logical replication lag in bytes; wait until it is near zero
SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- [OLD] Step 2: block new writes, then end existing client sessions.
-- The ALTER only affects new connections, so the terminate is required.
-- Filtering on 'client backend' leaves the walsender running.
ALTER DATABASE appdb SET default_transaction_read_only = on;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'appdb'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

-- [OLD] Step 3: record the final WAL position, then re-run until
-- confirmed_flush_lsn >= that value (lag_bytes from step 1 should reach 0 or less)
SELECT pg_current_wal_lsn() AS final_lsn;

-- [OLD] Step 4: generate setval statements with a safety margin of 1000.
-- Logical replication does not copy sequence values.
-- Run the generated output on the NEW cluster.
SELECT format('SELECT setval(%L, %s);',
              format('%I.%I', schemaname, sequencename),
              last_value + 1000)
FROM pg_sequences
WHERE last_value IS NOT NULL;

-- Step 5 happens outside SQL: repoint the pooler, DNS record or app config.

-- [NEW] Step 6: stop applying changes from the old cluster before taking writes.
ALTER SUBSCRIPTION upgrade_sub DISABLE;

-- [NEW] After cutover: rebuild planner statistics, which are not replicated
ANALYZE;

An ordered SQL runbook for the logical replication cutover, with each step marked for the old or new cluster: lag check, write freeze, final LSN confirmation, sequence sync, disabling the subscription and the post-cutover ANALYZE.

Rollback strategies and failure modes

Rolling back pg_upgrade

In link mode, the new cluster shares data files with the old one through hard links. Once the new cluster has started and written anything, the old cluster's files are no longer consistent, and starting it risks corruption. Rollback therefore needs something independent of those files: a verified base backup with archived WAL, or a physical standby that you detach before the upgrade and never point at the shared data directory. Copy mode leaves the old cluster intact, but it costs disk space and time.

Rolling back logical replication

Before cutover, create a publication on the new cluster and a subscription on the old one with initial copy disabled. Writes made after the switch then flow back to the old cluster, so you can return to it without losing data. Test this path in advance. An untested reverse path is not a reliable rollback.

Failure modes that stay quiet

• Slots. A lagging or abandoned slot pins WAL until the source disk fills. Monitor retained WAL and set max_slot_wal_keep_size.
• REPLICA IDENTITY FULL. Without a primary key or unique index, each update or delete can trigger a sequential scan on the subscriber. On large tables, apply falls far behind.
• Initial sync. The table copy adds heavy I/O load. It also holds old snapshots, which blocks vacuum and causes bloat on busy tables.
• DDL. Schema changes are not replicated. Freeze migrations or apply them to both clusters.
• Sequences. Unless your version explicitly synchronizes sequences, set them past the current maximum values at cutover. Otherwise the first inserts fail with duplicate-key errors.
• Large objects. These are not replicated. Copy them separately.

pg_upgrade vs logical replication: when to use and when to avoid each

Choose pg_upgrade when a planned write outage of minutes is acceptable and you want the fewest moving parts. With link mode, upgrade time depends mostly on catalog size, not data volume, so a short window often covers large databases.

Choose logical replication when writes can only pause for seconds and the team can run two clusters and watch lag, slots and disk for days.

Avoid logical replication when many tables lack primary keys, because updates and deletes need replica identity, or when DDL changes often, because DDL is not replicated and the schemas drift. Avoid pg_upgrade when no maintenance window exists at all.

For small databases, plain dump and restore is the simplest fallback. It also rewrites the data cleanly, which removes bloat as a side effect.

Impact on cost and engineering time

Illustrative estimate (assumed numbers, not a benchmark)

Assume a 2 TB cluster with 40 schemas and 3,000 tables, upgraded with pg_upgrade in link mode so data files are not copied.

• pg_upgrade downtime ≈ shutdown + (tables × per-table catalog time) + time to minimum useful statistics = 1 min + (3,000 × 0.1 s = 5 min) + 10 min ≈ 16 min
• Logical replication cutover ≈ write pause until lag reaches zero + sequence sync + connection switch = 1 min + 1 min + 2 min ≈ 4 min

In link mode, data volume barely affects pg_upgrade. Object count drives catalog time, and the statistics step matters because the planner performs poorly until ANALYZE has run. Check whether your target version carries statistics across. Logical replication cutover time depends on how quickly you can stop writes and repoint clients, so pooler configuration and DNS TTLs affect it more than database size does.

To reduce engineering time:

• Rehearse on a copy restored from backup, and time each step against the formula above.
• Script the runbook so the real run repeats the rehearsal exactly.
• Run pg_upgrade --check weeks in advance to surface incompatible extensions early.
• Choose pg_upgrade when the business can accept a short outage. Paying for a doubled cluster and weeks of monitoring to avoid a 15-minute window is often a poor trade.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql major version upgrade zero downtime, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.