Why I Used Logical Replication Instead of pg_upgrade for a Near-Zero-Downtime Major Upgrade

pg_upgrade –link finishes a major version bump in seconds, but it still needs the database stopped. I had a service that could not stop.

The tool everyone reaches for on a PostgreSQL major upgrade is pg_upgrade, and for most upgrades it is the right answer. For one PostgreSQL 16 to 17 bump, on a system where the acceptable write outage was measured in seconds rather than minutes, it was not. So I built the new major version as a logical-replication subscriber, let it catch up live against production traffic, verified it, and then flipped. This is how that goes, what logical replication silently refuses to carry, and where I would tell you to stop overthinking it and just run pg_upgrade.

When pg_upgrade is still the right call

pg_upgrade --link hard-links the data files into the new cluster instead of copying them, so the upgrade itself is near-instant regardless of database size. But the old server has to be shut down for the duration, and you are not fully back until you have restarted and re-gathered statistics with vacuumdb --analyze-only, because pg_upgrade does not carry optimiser statistics across. For a service that can absorb a few minutes of downtime in a maintenance window, that is the correct, boring, well-trodden choice. Take it.

Reach for a logical-replication cutover only when one of these is true: your outage budget is smaller than the shutdown-plus-restart window; you want a tested, instant rollback path because the old cluster stays live and writable until you decommission it; or you are changing something else at the same time, such as the storage layer, the operating system, or the region. A migration is a change like any other, and it is worth treating it as one you measure — the same change-failure and lead-time discipline you apply to deployments applies to a database upgrade.

The shape of the cutover

The old cluster (16) is the publisher; the new cluster (17) is the subscriber. Schema does not replicate, so you copy it first, using the new version’s pg_dump binary against the old server so the dump is forward-compatible:

# run from the PostgreSQL 17 host, dumping the 16 publisher
pg_dump --schema-only --no-publications --no-subscriptions \
  --host=pg16-old --port=5432 --username=repl app_db > schema.sql
psql --host=pg17-new --port=5432 --username=repl -d app_db -f schema.sql

Then create the publication on the old cluster and the subscription on the new one. The subscription copies the initial table data and then streams live changes on top of it:

-- on pg16-old (publisher)
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- on pg17-new (subscriber)
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=pg16-old port=5432 dbname=app_db user=repl password=...'
  PUBLICATION app_pub
  WITH (copy_data = true, streaming = on);

-- watch initial sync reach 'r' (ready) for every table
SELECT srrelid::regclass AS table, srsubstate
FROM pg_subscription_rel;

What logical replication silently will not carry

This is where cutovers go wrong, because none of these throw an error — they just quietly leave the new cluster incorrect.

• Sequences. The values in serial and identity columns replicate as ordinary row data, but the sequence object’s own counter does not advance on the subscriber. Flip without fixing this and your first insert on the new cluster reuses an identifier that already exists. This is the single most common way a logical-replication cutover corrupts data.
• DDL. No schema change replicates. Any migration that runs during the catch-up window lands on the publisher only. Freeze schema changes for the window, or apply them to both sides by hand. Enforcing that freeze is easier if your migrations already go through a pre-commit and CI gate rather than ad-hoc psql sessions.
• Large objects. The pg_largeobject catalogue is not supported by logical replication at all. If you use the lo_ interface, those objects need a separate copy step.
• Replica identity. Every published table that receives updates or deletes needs a primary key or an explicit REPLICA IDENTITY; without one, those changes error on the publisher. TRUNCATE does replicate, but mind foreign-key order.
• Partitioned tables. By default changes publish as the individual leaf partitions, so the partition structure must match on both sides. Set publish_via_partition_root = true on the publication if you want changes published against the root instead.

Syncing sequences at the last moment

Do this once, at cutover, after writes have stopped on the old cluster — not before, or the counters drift again. Generate the setval statements from the live publisher and apply them on the new subscriber:

-- on pg16-old, emit setval() calls reflecting current counters
SELECT format(
  'SELECT setval(%L, %s, true);',
  schemaname || '.' || sequencename,
  last_value
)
FROM pg_sequences
WHERE last_value IS NOT NULL;
-- pipe the output straight into psql on pg17-new

If you would rather not hand-roll this, pglogical exists precisely because the in-core feature set leaves sequences and DDL to you: it adds helpers such as synchronize_sequence() and replicate_ddl_command(). For a one-way 16-to-17 hop the built-in logical replication is enough, and one fewer extension in the path is one fewer thing to reason about. Reach for pglogical when you genuinely need its sequence automation or bidirectional flows, not by default.

Verifying parity, then the choreography

Before you trust the new cluster, confirm every table reached srsubstate = 'r', then compare per-table row counts between the two clusters. Counts are not proof of byte-for-byte equality, but they catch gross failures, and a per-table checksum pass catches the rest if the data matters that much. The actual flip is an ordered, rehearsed sequence:

1. Stop application writes — a maintenance flag at the app layer, backed by ALTER DATABASE app_db SET default_transaction_read_only = on; and terminating writer sessions as belt-and-braces.
2. Wait for the stream to drain: compare pg_current_wal_lsn() on the publisher against latest_end_lsn in pg_stat_subscription on the subscriber until they meet.
3. Apply the sequence setval statements.
4. Run the row-count and checksum verification.
5. Repoint the application at the new cluster and lift the read-only flag. If your write path is already safe to retry with idempotency keys, the brief window where requests fail fast and retry costs you nothing.
6. DROP SUBSCRIPTION app_sub; on the new cluster, and keep the old one running, untouched, for a soak period as your rollback.

The reason to do all of this is not that logical replication is elegant — it is fiddly, and it hands you three or four ways to corrupt data if you skip a step. The reason is that it turns a major-version upgrade into a traffic flip you can rehearse, verify, and reverse, with the old cluster still standing behind you. If you do not need that, pg_upgrade --link is faster, simpler, and correct. Choose the fiddly path only when the downtime you are avoiding actually costs more than the risk you are taking on.