What is pg_upgrade and why do major versions need it?
pg_upgrade is the tool that ships with PostgreSQL for moving a cluster from one major version to another without dumping and reloading every row. It exists because of how PostgreSQL treats its on-disk format.
Minor releases within a major version keep the data directory format unchanged. Upgrading means stopping the server, installing the new binaries and starting it against the same data directory. Release notes sometimes ask for extra steps, such as reindexing a specific index type, but the data directory itself is never rewritten.
Major versions are different. The system catalogs, the internal tables that describe your tables, types, functions and indexes, change their structure between major releases. A new server cannot read an old catalog, so it will refuse to start on an old data directory. That leaves three options: dump and restore, logical replication into a new cluster, or pg_upgrade.
pg_upgrade works because the format of user data files is generally kept compatible across major versions. It rebuilds the catalogs in a fresh cluster from a schema-only dump. It then reuses the existing heap and index files by copying, hard-linking or cloning them, instead of reloading them through SQL.
This article is for backend and platform engineers who run self-managed PostgreSQL. The upgrade command itself is short. Most of the engineering time goes elsewhere:
- rehearsing against a production-sized copy
- making sure every extension is available for the new version
- handling planner statistics so query plans don't degrade after cutover
Who should use pg_upgrade?
pg_upgrade fits teams that run PostgreSQL themselves and have shell access to the database host. It needs both the old and new server binaries installed on the same machine, and it reads and writes the data directories directly. If your provider does not give you filesystem access, you cannot run it yourself.
It is a good fit when:
- The database is too large for a comfortable dump and restore. pg_upgrade in link or clone mode does not reload rows or rebuild indexes, so its runtime depends mostly on the number of objects and files, not on data volume.
- A short planned outage is acceptable. pg_upgrade needs both clusters stopped while it runs. If the business can accept a maintenance window of minutes, it is usually the simplest path.
- You are staying on the same host type. The new cluster reuses the old data files, so the CPU architecture and the cluster-level settings that affect the on-disk format must stay compatible. Encoding, locale and data-checksum settings must match between the old and new clusters.
- Every extension you use has a build for the new major version. pg_upgrade cannot proceed if an extension's shared library is missing for the new version.
It is a poor fit when you need near-zero downtime, when you want to change hardware architecture, encoding or locale as part of the move, or when you want to reorganize the data during the upgrade, for example by dropping bloat or moving tables into a different layout. In those cases, dump/restore or logical replication is the better tool.
How does pg_upgrade work under the hood?
pg_upgrade works because the on-disk format of table and index files rarely changes between major versions. The system catalogs change. So the tool rebuilds the catalog in the new format and reuses the user data files as they are, instead of dumping and reloading every row.
The sequence:
- Install the new binaries alongside the old ones. pg_upgrade needs both sets, because it starts the old server to read its schema and the new server to restore it.
- Initialize an empty new cluster with the new version's
initdb. Encoding, locale and data-checksum settings must match the old cluster, or pg_upgrade refuses to proceed. - Stop both clusters. pg_upgrade starts and stops each server itself, on a private port, so no client writes can arrive during the run.
- Dump and restore the schema only. It runs a schema-only dump of the old cluster and restores it into the new one. It preserves the file identifiers of each relation so the new catalog points at files with the expected names.
- Transfer the data files into the new data directory by copying, hard-linking or cloning them.
Where everything sits:
| Component | Location | Flag |
|---|---|---|
| Old binaries | old bindir | -b |
| New binaries | new bindir | -B |
| Old data directory | old datadir | -d |
| New data directory | new datadir | -D |
| User data files | both datadirs in link mode, as hard links to the same inodes | — |
Link mode has two consequences. Both data directories must be on the same filesystem. And once the new cluster starts, the old cluster can no longer be safely used.
+-------------------------+
| pg_upgrade |
| starts each server on a |
| private port, in turn |
+------------+------------+
|
+----------------------+-----------------------+
v v
+-----------------------+ +-----------------------+
| old bindir (-b) | | new bindir (-B) |
| old postgres, pg_dump | | new postgres, initdb |
+-----------+-----------+ +-----------+-----------+
| reads schema restores into |
v v
+-----------------------+ 1. schema-only dump +-----------------------+
| old datadir (-d) | -------------------> | new datadir (-D) |
| old-format catalog | 2. restore, keep | new-format catalog |
| base/<db>/<relfile> | file identifiers | base/<db>/<relfile> |
+-----------+-----------+ +-----------+-----------+
| link mode: hard links (same filesystem) |
+----------------------+-----------------------+
|
v
+-------------------------------+
| user data files: tables and |
| indexes, format unchanged |
| link: same inodes in both |
| copy/clone: separate files |
+-------------------------------+
pg_upgrade runs the old and new binaries against their own stopped data directories, moves only the schema and catalog via a schema-only dump and restore that keeps relation file identifiers, and in link mode leaves both data directories pointing at the same user data file inodes.
Copy, link or clone: which pg_upgrade transfer mode should you choose?
The choice decides three things: how long the cluster is down, how much free disk you need, and what rollback looks like afterwards. In every mode, the schema dump and restore takes the same time. That part scales with the number of database objects, not with data size. The modes differ only in how they move the relation files.
| Mode | Speed | Extra disk | Runtime scales with | Rollback after new cluster starts |
|---|---|---|---|---|
| Copy (default) | Slowest | Roughly a second full copy of the data | Total bytes of data | Old cluster is untouched and can be started again |
Link (--link) |
Fast | Negligible | Number of files, not their size | Old cluster cannot be started safely, so restore from a snapshot or backup |
Clone (--clone) |
Fast, close to link | Starts near zero, grows as blocks change | Number of files, not their size | Old cluster stays usable, because copy-on-write keeps its blocks separate |
The core trade-off is between copy and link.
Copy mode keeps a clean fallback. The old data directory is never modified, so abandoning the upgrade means starting the old binaries again. The cost is disk space and time. You need about double the space, and on a large database the copy alone can run past your maintenance window.
Link mode makes the transfer close to constant time, but it removes that fallback. Both clusters share the same inodes, so the first write from the new server changes files the old cluster depends on. Take a filesystem snapshot or a verified backup before you run it.
Clone mode gives you link-mode speed and still keeps the old cluster usable. It only works on filesystems that support reflinks, such as Btrfs and XFS created with reflink enabled. Both directories must also be on the same filesystem. If your storage supports it, clone mode is usually the right default.
How do you run a pg_upgrade major version upgrade step by step?
Start by installing the new major version's server binaries alongside the old ones, plus the new-version build of every extension the old cluster uses. Extensions are the most likely blocker. pg_upgrade needs each extension's shared library to exist for the new version, and a missing package will stop the run.
Initialize the new cluster with initdb using the same encoding, locale and collation settings as the old cluster. Mismatches cause --check to fail, so read them from the old cluster rather than relying on defaults.
Run pg_upgrade --check early and rerun it after every change. Point it at both bin directories (--old-bindir, --new-bindir) and both data directories (--old-datadir, --new-datadir). It catches catalog-level incompatibilities, such as missing libraries, unsupported data types and locale mismatches. It does not catch behavioral changes in SQL functions, planner behavior or removed configuration parameters, so you still need application testing.
For the real run, stop both clusters and run pg_upgrade with --link. Once you start the new cluster, the old one is no longer safe to start.
Before opening traffic, copy over your settings from postgresql.conf, pg_hba.conf and any included files, because the new cluster has defaults. Then run any scripts pg_upgrade generates, and run ALTER EXTENSION ... UPDATE in each database for each extension.
Finally, run vacuumdb --all --analyze-in-stages. Do not count on planner statistics surviving the upgrade. Until ANALYZE finishes, the planner works from defaults and can choose bad plans, especially on large or skewed tables. The staged run gives you rough statistics quickly, then refines them in later passes.
#!/usr/bin/env bash
set -euo pipefail
OLD_BIN=/usr/lib/postgresql/16/bin; OLD_DATA=/var/lib/postgresql/16/main
NEW_BIN=/usr/lib/postgresql/17/bin; NEW_DATA=/var/lib/postgresql/17/main
read -r ENC COLL CTYPE < <("$OLD_BIN/psql" -At -F ' ' -d postgres -c \
"SELECT pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database WHERE datname = 'template1'")
"$NEW_BIN/initdb" -D "$NEW_DATA" --encoding="$ENC" --lc-collate="$COLL" --lc-ctype="$CTYPE"
"$NEW_BIN/pg_upgrade" --check \
--old-bindir="$OLD_BIN" --new-bindir="$NEW_BIN" \
--old-datadir="$OLD_DATA" --new-datadir="$NEW_DATA"
"$OLD_BIN/pg_ctl" -D "$OLD_DATA" stop -m fast
"$NEW_BIN/pg_upgrade" --link \
--old-bindir="$OLD_BIN" --new-bindir="$NEW_BIN" \
--old-datadir="$OLD_DATA" --new-datadir="$NEW_DATA"
"$NEW_BIN/pg_ctl" -D "$NEW_DATA" -l /tmp/pg17-start.log start
for db in $("$NEW_BIN/psql" -At -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn"); do
"$NEW_BIN/psql" -At -d "$db" -c "SELECT format('ALTER EXTENSION %I UPDATE;', e.extname)
FROM pg_extension e JOIN pg_available_extensions a ON a.name = e.extname
WHERE a.default_version IS DISTINCT FROM e.extversion" | "$NEW_BIN/psql" -d "$db"
done
"$NEW_BIN/vacuumdb" --all --analyze-in-stages
A rehearsal-ready shell script that matches the old cluster's encoding and locale in initdb, runs pg_upgrade --check and then --link, updates extensions in every database, and rebuilds planner statistics with vacuumdb --analyze-in-stages.
How do you rehearse the upgrade and handle replicas and rollback?
Restore a recent backup or volume snapshot to a staging host that matches production in PostgreSQL version, extensions and storage class, then run the full upgrade with a timer on each step: --check, the upgrade itself, staged ANALYZE, and application startup. Point the application at the result and run smoke tests, then replay real workload. This is where you find settings that the new version renamed or removed, defaults that changed, and queries whose plans regressed. These problems are cheap to fix in staging. In a live window they are expensive, because every minute spent debugging is a minute of downtime with several engineers on the call.
Replicas
Streaming replicas cannot follow a primary across a major version. Rebuild each one with a fresh base backup, or use the rsync procedure in the pg_upgrade documentation, which works with link mode and must run before the new primary starts. Until a rebuilt replica has caught up, the primary has no failover protection. Plan the window with that gap in mind.
Rollback
- Copy and clone: The old cluster is untouched. Stop the new cluster and start the old one.
- Link: If the new cluster has never been started, you can still revert by following the documented steps. Once it has started, the old cluster is no longer usable, so rollback means restoring from backup.
Illustrative estimate (assumed inputs, not measured): a 4-minute link-mode upgrade, 8 minutes for the first ANALYZE stage, 10 minutes of smoke tests and a 15-minute buffer give a window of about 40 minutes.
#!/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 # already created with initdb, same locale/encoding
LOG=/var/tmp/upgrade-rehearsal-timings.log
timed() {
local name=$1; shift
local start=$SECONDS
"$@"
echo "$name: $((SECONDS - start))s" | tee -a "$LOG"
}
"$OLD_BIN/pg_ctl" -D "$OLD_DATA" -w stop || true
timed check "$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" \
-d "$OLD_DATA" -D "$NEW_DATA" --link --check
timed upgrade "$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" \
-d "$OLD_DATA" -D "$NEW_DATA" --link --jobs 4
timed start "$NEW_BIN/pg_ctl" -D "$NEW_DATA" -w -l /var/tmp/pg17-start.log start
timed analyze "$NEW_BIN/vacuumdb" --all --analyze-in-stages --jobs 4
timed smoke "$NEW_BIN/psql" -d appdb -c 'SELECT version();'
echo "Timings written to $LOG"
A staging rehearsal script that runs pg_upgrade in link mode with --check, startup, staged ANALYZE and a smoke test, logging the duration of each step so the production window can be sized from measured numbers.
When is logical replication worth it for a postgres upgrade?
When pg_upgrade's downtime window is still too long, logical replication can shrink the cutover to the time it takes to stop writes and repoint clients. The cost is more moving parts and a longer preparation phase.
The procedure
- Build a new cluster on the target major version, configured like production.
- Copy the schema with
pg_dump --schema-onlyand restore it on the new cluster. Logical replication moves rows, not definitions. - On the old primary, set
wal_level = logical(this requires a restart) and create a publication. On the new cluster, create a subscription pointing at it. - Wait for every table to finish its initial copy, which you can check in
pg_subscription_rel. Then wait for replication lag to reach zero, measured by comparing the slot'sconfirmed_flush_lsnwith the publisher's current WAL position. - To cut over, stop writes on the old primary and confirm that lag is zero again. Copy sequence values across with
setval, then repoint clients.
What does not replicate
DDL, sequence values and large objects are not replicated. Freeze schema changes until cutover, and plan a separate path for large objects.
UPDATE and DELETE replicate only on tables that have a primary key or an explicit replica identity. REPLICA IDENTITY FULL works as a fallback, but it writes the entire old row to WAL. On the subscriber, each change may also require a scan to find the matching row. On wide or heavily updated tables, add a key instead.
Rollback
Before you open writes on the new cluster, create a reverse subscription from the old cluster with copy_data = false. The old cluster then keeps receiving new writes and stays a viable fallback. The simpler option is to keep the old primary frozen but running. That lets you roll back quickly, but you lose any writes made after cutover.
#!/usr/bin/env bash
set -euo pipefail
OLD="host=old-db.internal dbname=appdb user=repl_admin"
NEW="host=new-db.internal dbname=appdb user=repl_admin"
pg_dump --schema-only -d "$OLD" | psql -v ON_ERROR_STOP=1 -q -d "$NEW"
psql -d "$OLD" -c "CREATE PUBLICATION upgrade_pub FOR ALL TABLES;"
psql -d "$NEW" -c "CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=old-db.internal dbname=appdb user=repl_admin'
PUBLICATION upgrade_pub;"
until [ "$(psql -d "$NEW" -Atc \
"SELECT count(*) FROM pg_subscription_rel WHERE srsubstate <> 'r';")" = "0" ]; do
sleep 10
done
psql -d "$OLD" -Atc "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
FROM pg_replication_slots WHERE slot_name = 'upgrade_sub';"
psql -d "$OLD" -Atc "SELECT format('SELECT setval(%L, %s, true);',
quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value)
FROM pg_sequences WHERE last_value IS NOT NULL;" \
| psql -v ON_ERROR_STOP=1 -q -d "$NEW"
A minimal shell walkthrough of a logical-replication upgrade: copy the schema, publish and subscribe, wait for the initial sync, check slot lag, and copy sequence values at cutover.
What are the alternatives to pg_upgrade?
There are two alternatives that work for any self-managed PostgreSQL cluster.
Dump and restore. Use pg_dumpall for roles and other global objects and pg_dump for each database, then restore into a cluster initialized with the new version. Use the new version's pg_dump, which can read from older servers. This is the most portable option: it works across hosts, CPU architectures, encodings and locales, and the restore produces freshly built tables and indexes without bloat. The cost is time. Every row is reloaded and every index is rebuilt, so the outage grows with data size. Running pg_restore with --jobs on a directory-format dump reduces that time, but the cost still grows with data volume.
Logical replication. As described in the previous section, the new cluster subscribes to the old one and stays in sync until cutover. It gives the shortest outage and also allows a change of host, architecture or storage at the same time. In exchange, you must handle DDL, sequences, large objects and replica identity yourself, and you need a full second cluster for the duration.
One option does not work: physical streaming replication. A standby must run the same major version as its primary, so you cannot stream from an old-version primary to a new-version standby and promote it.
If you use a managed PostgreSQL service, it usually provides its own major-version upgrade mechanism. Check its documentation for which of these methods it uses under the hood and what downtime it implies.
Which upgrade method should you use, and when should you avoid each?
All four methods produce the same end state, a cluster on the new major version. They differ in where the cost lands: outage time, disk, or engineering effort.
| Method | Downtime | Complexity | Extra disk | Rollback path | Engineering cost |
|---|---|---|---|---|---|
| pg_upgrade copy | Grows with data size | Low | About a second copy of the data | Old cluster intact; restart it | Low |
| pg_upgrade link | Minutes, mostly fixed | Low | Minimal | Snapshot or backup restore only | Low |
| dump/restore | Grows with size and index builds | Low | Dump file plus new cluster | Old cluster intact | Low, but long windows |
| Logical replication | Seconds to minutes at cutover | High | Full second cluster | Reverse replication or frozen old primary | High |
Avoid logical replication when a link-mode outage window is acceptable. It does not carry DDL or sequence values, and tables without a primary key need a replica identity. You pay that tax only to shave a few minutes.
Avoid link mode when no snapshot or backup exists. Once the new cluster starts, the old data directory is no longer safe to run, so the only way back is a restore.
Avoid copy mode when disk space or the window is tight. Copy time scales with bytes on disk, and you need room for both copies at once.
Avoid dump/restore for large databases. It reloads every row and rebuilds every index.
For most teams, link mode on a snapshotted volume is the default. Logical replication is worth its extra complexity only when even a short outage window is too long.
How can pg_upgrade save engineering time?
Most of the time pg_upgrade saves is in building, babysitting and debugging the upgrade, not in raw transfer speed.
- No replication pipeline to build. Logical replication means managing publications, subscriptions, slots, replica identities, a schema freeze and sequence copying, then tearing it all down after cutover. pg_upgrade replaces that with one command that runs against two stopped clusters.
- Problems surface before the window.
pg_upgrade --checkcan be rerun as often as needed without changing either cluster. Missing extension libraries, locale mismatches and unsupported data types show up as a list during preparation, not as failures during the outage. - Short, predictable windows. In link or clone mode, runtime depends on the number of objects and files rather than on data size, so a rehearsal on a production-sized copy gives a timing you can plan around. Shorter windows also mean fewer engineers on call for fewer hours, and less overtime or weekend scheduling.
- A reusable runbook. The scripts above are mostly parameterized by bin and data directory paths. Once a team has rehearsed one major upgrade, the same scripts, timing log and checklist carry over to the next one with small changes, instead of each upgrade becoming a new project.
- Parallelism with one flag.
--jobsin pg_upgrade andvacuumdbspreads work across databases and tables without extra tooling.
Illustrative estimate (assumed inputs, not measured): assume a logical-replication upgrade needs two engineer-days to set up and validate the pipeline, plus one day of monitoring during sync. Assume a pg_upgrade link-mode upgrade needs half a day to write and rehearse the scripts, reusing them later. Under those assumptions, the first upgrade saves about two and a half engineer-days, and later upgrades save more because the scripts already exist. Measure your own setup and rehearsal effort before relying on figures like these.
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 pg_upgrade major version, 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
.envby 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.