Why does a Postgres major version upgrade need special handling?

A Postgres server binary can only read a data directory laid out for its own major version. Each major release is free to change the system catalogs, the internal tables that describe relations, types, functions and other objects. Columns get added, removed or redefined, and the catalog contents the server expects at startup no longer match what is on disk. The data directory records its major version in a PG_VERSION file, and a newer server will refuse to start on an older directory rather than misread it.

Minor releases are different. They contain bug and security fixes and do not change the on-disk format. A minor upgrade is a binary swap and a restart, and your downtime is roughly the length of that restart.

A major upgrade has to get your data into a cluster with the new catalog format. There are three common ways to do it:

  • Dump and restore. Use pg_dump against the old cluster and load the output into a new one. Every row is rewritten and every index rebuilt, so the time grows with the size of the database. It is simple and reliable, but often too slow for large databases.
  • pg_upgrade. Rebuild only the catalogs in a new cluster and reuse the existing user data files. This is fast, but it takes the database offline.
  • Logical replication. Stream row changes from the old cluster into a new one running the target version, then cut over. Downtime is short, but setup has more moving parts.

The rest of this article compares the last two.

How does each upgrade method work, in short?

Both methods end with your data in a cluster running the new major version. They differ in what they move and when.

  • pg_upgrade works on stopped clusters on the same host. It recreates the schema in a new, empty cluster, which produces catalogs in the new format, and then copies or hard-links the existing table and index files into it. User data is never decoded or rewritten. The work is proportional mostly to the number of schema objects, and in copy mode also to the data size.
  • Logical replication works on a running source. The old primary decodes committed row changes from its write-ahead log and sends them, through a replication slot, to a subscription on a separately built new-version cluster. The subscriber first copies each table in full, then applies ongoing changes until it is caught up. You then stop writes on the old primary, let the last changes drain, and point the application at the new cluster.

In short: pg_upgrade converts the cluster while it is offline, and logical replication builds a copy while the old cluster stays online. The sections below cover each in detail.

How does pg_upgrade work, and how long is the downtime?

pg_upgrade skips dumping and reloading your rows. It runs pg_dump --schema-only against the old cluster, restores that schema into a freshly initdb'd cluster on the new version, and then moves the existing data files into place. This works because the on-disk format of table and index data stays compatible across major versions. Only the system catalogs change. Dump/restore, by contrast, rewrites every row and rebuilds every index.

Always run it with --check first. This dry run compares the two clusters and reports problems such as missing extensions or incompatible data types without changing anything. The real run then uses the same arguments (-b/-B for the old and new bin directories, -d/-D for the old and new data directories), with or without --link.

Copy mode (default) Link mode (--link)
Data files Copied to the new data directory Hard-linked, which requires the same filesystem
Disk needed About 2× the data size Little extra
Old cluster after upgrade Still usable Unsafe once the new cluster starts

The outage includes:

  • Stopping both clusters.
  • Having new-version builds of every extension installed beforehand.
  • Running ANALYZE afterward. Unless your target version's release notes say otherwise, assume planner statistics are not carried over. vacuumdb --all --analyze-in-stages gets rough statistics in place quickly.
  • Rebuilding or resyncing physical standbys.

Schema restore time grows with catalog size. Thousands of tables, partitions, or large objects lengthen the outage even in link mode.

Illustrative estimate (assumed numbers, not a benchmark). Assume 2 TB of data, 500 MB/s sustained copy throughput, and a 15-minute schema restore.

  • Copy mode: data size ÷ throughput + schema time = 2,000,000 MB ÷ 500 MB/s ≈ 67 min, plus 15 min, for about 82 min total.
  • Link mode: about 15 min, plus a few seconds for linking.

Add ANALYZE time to either figure.

#!/usr/bin/env bash
set -euo pipefail

OLD_BIN=/usr/lib/postgresql/15/bin
NEW_BIN=/usr/lib/postgresql/17/bin
OLD_DATA=/var/lib/postgresql/15/main
NEW_DATA=/var/lib/postgresql/17/main

cd /var/lib/postgresql

"$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" -d "$OLD_DATA" -D "$NEW_DATA" --link --check

"$OLD_BIN/pg_ctl" -D "$OLD_DATA" stop -m fast

"$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" -d "$OLD_DATA" -D "$NEW_DATA" --link --jobs 4

"$NEW_BIN/pg_ctl" -D "$NEW_DATA" -l "$NEW_DATA/startup.log" start

"$NEW_BIN/vacuumdb" --all --analyze-in-stages --jobs 4

A pg_upgrade run that starts with a --check dry run, performs a link-mode upgrade with both clusters stopped, and then rebuilds planner statistics with vacuumdb --analyze-in-stages.

How does a logical replication upgrade work?

Logical replication avoids rewriting the old cluster in place. Instead, it streams row changes into a separately built new-version cluster while the application keeps writing to the old primary.

 app ──writes──► OLD PRIMARY (old version, wal_level=logical)
                   │  publication: all tables
                   │  replication slot (holds WAL until confirmed)
                   ▼
                 walsender ──row changes──► NEW CLUSTER (new version)
                                              subscription + apply worker
 app ┄┄(after cutover)┄┄────────────────────►

The sequence:

  1. Provision the new cluster on the target major version with its own storage. Nothing is shared with the old one.
  2. Load the schema separately. DDL is not replicated, so dump the schema only (pg_dump --schema-only) and restore it on the new cluster before subscribing. Any schema change made on the source after this point must be applied to both sides by hand.
  3. Create a publication on the old primary covering the tables to move. This requires wal_level = logical, and changing that setting needs a restart.
  4. Create a subscription on the new cluster. This creates a replication slot on the source and starts the initial table copy.
  5. Wait for initial sync. Each table is copied with a full read and then caught up from the slot. Watch per-table state until every table is ready.
  6. Monitor lag. Use pg_stat_subscription on the subscriber and pg_replication_slots on the source.

The source pays for all of this. The initial copy is a sequential scan of every table. On large tables, that I/O competes with production traffic.

The slot is the bigger risk. It keeps WAL until the subscriber confirms receipt. If the subscriber stalls or falls far behind, the WAL keeps accumulating and can fill the old primary's disk. Alert on retained WAL size, and drop the slot if you abandon the migration.

#!/usr/bin/env bash
set -euo pipefail
OLD="host=old-primary.internal dbname=appdb user=postgres"
NEW="host=new-cluster.internal dbname=appdb user=postgres"

pg_dump --schema-only "$OLD" | psql "$NEW" -v ON_ERROR_STOP=1

psql "$OLD" -c "CREATE PUBLICATION upgrade_pub FOR ALL TABLES;"

psql "$NEW" -c "CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-primary.internal dbname=appdb user=replicator'
  PUBLICATION upgrade_pub;"

psql "$NEW" -c "SELECT srrelid::regclass AS tbl, srsubstate
  FROM pg_subscription_rel WHERE srsubstate <> 'r';"

psql "$NEW" -c "SELECT subname, received_lsn, latest_end_lsn,
  last_msg_receipt_time FROM pg_stat_subscription;"

psql "$OLD" -c "SELECT slot_name, active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
    AS retained_wal
  FROM pg_replication_slots WHERE slot_type = 'logical';"

Sets up schema, publication, and subscription for a logical replication upgrade, then checks initial sync state, subscriber progress, and how much WAL the replication slot is retaining on the old primary.

What does logical replication not carry over?

Logical replication decodes row changes from published tables and nothing else. Anything outside that stream stays on the old cluster unless you move it yourself, and the subscriber does not report the gap.

Not replicated Why it causes silent divergence What to do
DDL (ALTER TABLE, new indexes) Schema drifts, and replication can stop when a column the subscriber lacks starts receiving data Freeze schema changes during the migration, or apply each change to the subscriber first
Sequence values The subscriber's sequences stay at their initial values, so the first insert after cutover can collide with existing keys Read last_value from pg_sequences at cutover and setval each sequence ahead of it
Large objects (pg_largeobject) They are not table rows of a published table, so the OIDs that rows reference point to nothing Copy them separately, or move them to bytea columns beforehand
Tables not in the publication New tables are not added automatically unless the publication covers all tables Compare table lists on both sides before cutover

Replica identity

To apply an UPDATE or DELETE, the subscriber has to find the target row. It does this through the table's replica identity, which defaults to the primary key. If a table has no primary key and no other identity is configured, the publisher rejects updates and deletes on that table. If you set REPLICA IDENTITY FULL, the whole old row is logged and matched, which is slow on large tables without a usable index.

To find tables at risk, join pg_class to pg_index filtered on indisprimary. Keep ordinary tables with no match, then check relreplident to confirm none of them fall back to d (default) without a key.

-- 1. Run on the PUBLISHER: ordinary/partitioned tables with no primary key.
-- relreplident: d = default (uses PK; none here, so UPDATE/DELETE fail once published),
-- n = nothing, f = full (works, but slow without a usable index), i = a chosen unique index.
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.relreplident AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND NOT EXISTS (
        SELECT 1 FROM pg_index i
        WHERE i.indrelid = c.oid AND i.indisprimary)
ORDER BY 1, 2;

-- 2. Run on the PUBLISHER after application writes have stopped:
-- current sequence positions. last_value is NULL if the sequence was never
-- used or the role lacks privileges on it.
SELECT schemaname, sequencename, last_value, increment_by
FROM pg_sequences
ORDER BY 1, 2;

-- 3. Generate setval statements to run on the SUBSCRIBER.
-- The +1000 margin is an arbitrary safety gap and assumes ascending sequences.
-- Example: psql -At -d appdb -f gen_setval.sql > setval.sql
SELECT format('SELECT setval(%L, %s, true);',
              quote_ident(schemaname) || '.' || quote_ident(sequencename),
              last_value + 1000)
FROM pg_sequences
WHERE last_value IS NOT NULL
  AND increment_by > 0
ORDER BY schemaname, sequencename;

Catalog queries that list tables lacking a primary key along with their replica identity setting, read current sequence values, and generate setval statements to apply on the subscriber at cutover.

How do you cut over and roll back safely?

The cutover sequence is short because the slow work, the initial table copy and the ongoing apply, finished long before you touch traffic.

  1. Fence writes on the old primary. Revoke CONNECT from application roles, set default_transaction_read_only = on and terminate existing sessions, or pause the pooler. Anything that commits on the old primary after the switch never reaches the subscriber. It is silently lost, or it conflicts later when a row with the same key arrives on the new side.
  2. Wait for catch-up. Compare pg_current_wal_lsn() on the publisher with the slot's confirmed_flush_lsn in pg_replication_slots. Proceed only when the slot has reached the fenced LSN.
  3. Advance sequences. Logical replication does not carry sequence state, so run setval on the new cluster for every sequence. Add headroom above the old values.
  4. Repoint traffic through the pooler, DNS, or service discovery. A pooler switch is the fastest. DNS is bounded by TTLs and client-side caching.
  5. Verify. Check row counts on hot tables and do a test write.

The write-blocked window covers only the final replication lag plus the repoint, not the size of the data.

Link-mode pg_upgrade Logical replication
Old cluster after cutover Unsafe to start once the new cluster has started, because the data files are shared Intact and still runnable
Fallback path Copy mode, or a pre-upgrade backup or snapshot Reverse replication from new to old
Writes kept after a fallback Lost back to the snapshot point Kept, if the reverse subscription is running

For the reverse path, create a publication on the new cluster and a subscription on the old one with copy_data = false. Do this before you open writes, so the old cluster stays current and remains a usable fallback.

-- [OLD] 1. Fence writes: block new app logins, then end live sessions.
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC, app_rw;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
 WHERE datname = 'appdb' AND usename = 'app_rw';

-- [OLD] 2. Catch-up: repeat until lag_bytes = 0 (no app writes can arrive now).
SELECT slot_name, confirmed_flush_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
  FROM pg_replication_slots WHERE slot_name = 'upgrade_sub';

-- [OLD] 3a. Generate setval calls with 1000 of headroom.
-- Save the output (psql -At > setval.sql), then run it on NEW.
SELECT format('SELECT setval(%L, %s);',
              quote_ident(schemaname) || '.' || quote_ident(sequencename),
              last_value + 1000)
  FROM pg_sequences WHERE last_value IS NOT NULL;

-- [NEW] 3b. Run setval.sql here. Then stop forward replication.
-- This also drops the slot on OLD, so changes cannot loop back.
DROP SUBSCRIPTION upgrade_sub;

-- [NEW] Prepare the rollback path before any application write lands.
CREATE PUBLICATION rollback_pub FOR ALL TABLES;

-- [OLD] Subscribe to NEW without recopying data. OLD already matches NEW.
-- The password comes from ~/.pgpass, not from this string.
CREATE SUBSCRIPTION rollback_sub
  CONNECTION 'host=pg-new.internal dbname=appdb user=replicator'
  PUBLICATION rollback_pub
  WITH (copy_data = false);

-- [NEW] 4-5. Repoint the pooler, GRANT CONNECT to app_rw here, then verify:
SELECT count(*) FROM orders;  -- compare with the same count on OLD

A logical-replication cutover script that fences writes on the old primary and waits for the slot to drain. It then copies sequence values with headroom, retires the forward subscription and sets up reverse replication from new to old with copy_data = false, so a rollback keeps post-cutover writes.

When should you use pg_upgrade, and when should you avoid it in favor of logical replication?

Default to pg_upgrade. In link mode, its runtime depends mostly on the number of schema objects, not the volume of data, so you can rehearse it on a restored snapshot and get a reliable timing. If that window fits your downtime budget, it is the cheapest correct option. One person runs one well-understood procedure, and the cluster is finished when it is done.

Choose logical replication only when the rehearsed window exceeds your budget, or when you need a tested rollback that keeps the old cluster current. That capability has a cost. You run two clusters for weeks and pay for both. Someone has to watch lag. Some failures are silent: a table left out of the publication, sequences that drift, a subscription that stalls overnight. Each of these needs a check that pg_upgrade would never require.

Factor Favors pg_upgrade Favors logical replication
Downtime budget Minutes of planned outage acceptable Seconds, or no fixed window
Write volume Any Moderate; very high rates make lag hard to close
Schema Tables without primary keys, large objects, frequent DDL Stable schema, keys on every table
Rollback Restore from backup is acceptable Must fail back with no data loss
Team experience Limited replication experience Has operated replication before
Hosting Filesystem access to both binaries Provider allows publications and subscriptions

Avoid pg_upgrade when the rehearsed window does not fit, or when your host gives no filesystem access. Many managed services offer only their own in-place upgrade, so check what your provider actually allows before you plan. Avoid logical replication when the schema has heavy DDL churn, many keyless tables, or many large objects. Also avoid it when no one on the team will own lag monitoring for the full migration period.

Who should use each method?

pg_upgrade fits teams that:

  • run self-managed Postgres with shell access to the host and can install both the old and new server binaries side by side;
  • can schedule a maintenance window of minutes, measured in a rehearsal, during which the database is offline;
  • have a small operations team, or no one with replication experience, and want a single procedure with a clear end;
  • have schemas that are awkward for logical replication: keyless tables, large objects, or frequent DDL.

Logical replication fits teams that:

  • run services where a multi-minute write outage is not acceptable, and where the rehearsed pg_upgrade window is too long, often because of a very large catalog or because copy mode is required;
  • need a fallback that keeps writes made after cutover, which only reverse replication provides;
  • want to move to new hardware, a new storage layout, or a new host at the same time as the version change, since the new cluster is built independently;
  • have an engineer who can own lag and slot monitoring for the whole migration, and a schema with a primary key or suitable replica identity on every table.

Teams on a managed service should start from what the provider supports. If filesystem access is not available, pg_upgrade is not an option you run yourself, and the choice is between the provider's upgrade mechanism and logical replication, if the provider allows it.

What are the alternatives to pg_upgrade and logical replication?

  • Dump and restore with pg_dump and pg_restore. Dumping in directory format (-Fd) allows parallel dump and restore with -j. Roles and tablespaces are not included in a per-database dump, so export them with pg_dumpall --globals-only. This is the most portable method: it works across hosts, architectures and more than one major version, and it produces a cleanly rebuilt cluster with fresh indexes. The cost is downtime proportional to data size, because writes must stop before the dump starts if you want a consistent copy that includes them. It is a reasonable choice for small and medium databases where a few hours of downtime are acceptable.
  • Your managed service's built-in major upgrade. Hosted Postgres offerings usually provide an upgrade operation of their own. Read the provider's documentation for how it works, how long the database is unavailable, and what happens to replicas and extensions. You give up control over the procedure in exchange for not running it yourself.
  • Converting a physical standby into a logical subscriber. Starting with PostgreSQL 17, the pg_createsubscriber tool turns a physical standby into a logical replication subscriber. This avoids the initial table copy over a logical connection, which is the slowest and most source-intensive part of a logical replication setup. It requires that the source already be on a version that includes the tool, so check your versions before planning around it.
  • Physical streaming replication is not an alternative. A physical standby must run the same major version as its primary, so you cannot fail over to a newer-version standby to upgrade. Standbys have to be rebuilt or resynced after a pg_upgrade of the primary.
  • Trigger-based or external change-data-capture tools. Before built-in logical replication existed, upgrades with low downtime relied on trigger-based replication. Such tools still exist, but they add triggers and write overhead to every replicated table. Built-in logical replication covers the same use case for most clusters.

How can the right upgrade method save engineering time?

Most engineering time in a major upgrade goes to preparation, monitoring and recovery from surprises, not to the commands themselves. The method and preparation affect each of these.

  • pg_upgrade keeps total effort low. The work is a rehearsal, a reviewed script, and a short window. Nothing runs for days afterward, and there is no second cluster to monitor or to pay for. If the rehearsed window fits your budget, choosing logical replication instead adds weeks of dual-cluster operation, monitoring, and cutover planning for no benefit.
  • Rehearsal replaces guesswork. A timed run on a restored production copy tells you the real window and exposes blockers such as missing extensions or keyless tables. Each blocker found during a rehearsal is fixed once, at a planned time. The same blocker found during the real window costs an aborted upgrade, a rollback, and a second window to schedule.
  • Scripts are reusable. The pg_upgrade script and the catalog queries in this article, covering keyless tables and sequence setval generation, are not tied to one database. Once tested, they cover every cluster in your fleet with only path and host changes. Without them, each upgrade repeats the same analysis.
  • Logical replication saves time when downtime is expensive. It costs more engineering time than pg_upgrade, but it can still be the cheaper option overall. A long outage needs coordination with other teams, off-hours work, and communication to users. A cutover of seconds needs none of that. A reverse subscription also avoids the slow manual reconciliation of writes made after cutover if you have to fall back.

Illustrative estimate (assumed numbers, not a measurement). Assume a team upgrades ten similar clusters. Assume the first upgrade, including writing and rehearsing the scripts, takes 5 engineer-days. Assume each later upgrade takes 1 engineer-day because it reuses the tested scripts and runbook, compared with 3 engineer-days if each one is planned from scratch. Reuse then costs 5 + 9 × 1 = 14 engineer-days, against 5 + 9 × 3 = 32 engineer-days without it. Your numbers will differ; the point is that the scripted procedure pays off across a fleet.

What should you check before any Postgres major version upgrade?

Whichever method you choose, the same preparation decides whether the upgrade window is uneventful.

  • Rehearse on a restored copy of production. Restore a recent backup to a separate host and run the full upgrade there, timing each step. Synthetic test databases miss the things that block upgrades: an old extension, a table without a primary key, an unusual collation. Finding these during a rehearsal costs an afternoon. Finding them during the real window means an aborted upgrade and a second maintenance window to schedule.
  • Confirm extensions are available and compatible. Every extension installed on the old cluster must have a build for the new major version on the target host. Some extensions also need their own upgrade step after the server upgrade. Check each extension's documentation rather than assuming the latest package supports your target version.
  • Run the application test suite against the new version. Point CI at a database on the new version. This catches removed functions, changed casting rules, and driver incompatibilities before users hit them.
  • Read the release notes for every major version you skip. Look for behavior changes, deprecated settings, and planner changes. If you go from version N to N+3, you inherit three sets of incompatibilities.
  • Take a verified backup. Restore it somewhere and query it. A backup that has never been restored is not proven.

Neither method removes the need for application-level testing. Planner changes can turn a fast query into a slow one, and that regression may only show up with production data volumes and concurrency. Before cutover, capture your slowest and most frequent queries and compare their plans on the rehearsal copy. After cutover, watch query latency closely.

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 postgres major version upgrade, 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 route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

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.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • 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.