pg_upgrade in practice · PostgreSQL

Everything below was performed on a cluster running PostgreSQL 17.11, upgraded to 18.6, with both sets of binaries installed on one host.

What must exist first

Both major versions’ binaries, on the same machine. pg_upgrade runs the old server and the new one. On Debian that means postgresql-17 and postgresql-18 installed together.

A new, initialised, empty cluster. initdb for the new version, with the same locale and encoding as the old, or the upgrade refuses.

Every extension installed for the new version, before you start.

A verified backup. Part XIII.

`--check` first, days in advance

Read-only / Safepg_upgrade --check, with the old cluster STILL RUNNING— EXECUTION VALIDATED, 17.11 to 18.6. --check does not require downtime.

$ pg_upgrade --old-datadir=/var/lib/postgresql/17data \
          --new-datadir=/var/lib/postgresql/18data \
          --old-bindir=/usr/lib/postgresql/17/bin \
          --new-bindir=/usr/lib/postgresql/18/bin --check

Checking for objects affected by Unicode update               ok
Checking for not-null constraint inconsistencies              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

*Clusters are compatible*

--check ran against a live old cluster. That is what makes it usable as a pre-flight days or weeks ahead, and it is the step that finds the problems you do not want to find during the window — missing extension libraries above all.

The real run refuses a running cluster

Service impact possibleattempting the upgrade without stopping the old cluster— Real error from 18.6.

$ pg_upgrade --old-datadir=... --new-datadir=... --old-bindir=... --new-bindir=...

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

Stop it cleanly — pg_ctl stop -m fast — and confirm.

The upgrade

Destructive17.11 to 18.6, copy mode— EXECUTION VALIDATED. 78 MB, 400,000 rows, two secondary indexes. Final portion of the output, verbatim.

$ pg_upgrade --old-datadir=... --new-datadir=... --old-bindir=... --new-bindir=...

Setting next transaction ID and epoch for new cluster         ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster                 ok
Copying user relation files                                   ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                ok

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
  /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
  /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
  ./delete_old_cluster.sh

ELAPSED: 2 seconds

Note Resetting WAL archives. The new cluster starts a fresh WAL sequence, which means your archive and every standby are now irrelevant to it. Standbys must be rebuilt, and archiving reconfigured — which lesson XVII-08 puts in the post-upgrade checklist.

What survived, measured

Read-only / Safeplanner statistics, before and after— EXECUTION VALIDATED. The same query run on 17.11 before and 18.6 after.

$ SELECT tablename, attname, n_distinct, null_frac, correlation FROM pg_stats WHERE tablename='orders';

--- BEFORE, on 17.11 ---
tablename | attname  | n_distinct | null_frac |  correlation
-----------+----------+------------+-----------+---------------
orders    | amount   |   -0.20237 |         0 | -0.0049658697
orders    | customer |       9980 |         0 |   0.008761019

--- AFTER, on 18.6 ---
orders    | amount   |   -0.20237 |         0 | -0.0049658697
orders    | customer |       9980 |         0 |   0.008761019

Identical to the last digit. The planner statistics crossed the major version boundary.

Read-only / Safecumulative statistics, after— EXECUTION VALIDATED on 18.6 immediately after the upgrade.

$ SELECT relname, last_analyze, last_autoanalyze, n_live_tup FROM pg_stat_user_tables WHERE relname='orders';

 relname | last_analyze | last_autoanalyze | n_live_tup
---------+--------------+------------------+------------
orders  |              |                  |          0

Nothing. pg_stat_user_tables starts from zero, which means autovacuum’s thresholds start from zero too.

This is a real PostgreSQL 18 change, confirmed from the binaries

Read-only / Safethe completion message in each version's pg_upgrade— EXECUTION VALIDATED. Read directly out of the two binaries rather than from release notes.

$ grep -a -o 'Optimizer statistics are not transferred[^"]*' /usr/lib/postgresql/17/bin/pg_upgrade
grep -a -o 'Some statistics are not transferred[^"]*'      /usr/lib/postgresql/18/bin/pg_upgrade

--- 17 ---
Optimizer statistics are not transferred by pg_upgrade.
--- 18 ---
Some statistics are not transferred by pg_upgrade.

17 says “Optimizer statistics are not transferred”. 18 says “Some statistics are not transferred” and offers --missing-stats-only.

The wording change is the feature. Before 18, a freshly upgraded cluster had no planner statistics at all, and the first hours after an upgrade were spent running badly-planned queries until ANALYZE finished. That was the single largest post-upgrade risk, and 18 removes most of it.

It does not remove all of it, which is what “some” means — and the vacuumdb --analyze-in-stages --missing-stats-only command in the completion message is there precisely to fill the gaps.

After the upgrade

Not optional, in this order:

# 1. Start the new cluster and confirm what it is
psql -c "SELECT version();"

# 2. Fill in the statistics that did not transfer
vacuumdb --all --analyze-in-stages --missing-stats-only
vacuumdb --all --analyze-only

# 3. Verify data
psql -d appdb -c "SELECT count(*) FROM orders;"

# 4. Rebuild standbys — the WAL sequence was reset
# 5. Reconfigure archiving to a new location
# 6. Check extensions
psql -c "SELECT extname, extversion FROM pg_extension;"
# 7. Update monitoring for changed views (lesson XVI-03)

delete_old_cluster.sh is the last step, not an early one

$ cat /tmp/delete_old_cluster.sh
#!/bin/sh

rm -rf '/var/lib/postgresql/17data'

In copy mode the old cluster is intact and is your rollback. Running that script removes it.

Keep it until you are confident — days, not minutes. The disk it occupies is cheap relative to the alternative.

In --link or --swap mode there is nothing to keep, which lesson XVII-06 covered and which is the real cost of those modes.

What pg_upgrade actually does, step by step

The output lines are the algorithm, and reading them makes the failure modes obvious.

It starts the old server to dump the schema. That is why the old binaries must be present and why the old cluster must be stopped first — pg_upgrade wants to control it.

It dumps the schema only, not the data. pg_dump --schema-only against the old cluster, restored into the new one. This is why the catalogue conversion is fast regardless of data size, and why an extension missing from the new cluster fails here rather than later.

It sets the new cluster’s transaction ID, epoch, OID and multixact counters to continue from the old cluster’s. That is what makes the copied data files meaningful: a tuple’s xmin only means something relative to the cluster’s transaction counter.

It copies or links the user data files. No page is read; they are moved as files. This is the step whose duration depends on the transfer mode.

It resets the WAL archives. The new cluster starts a new WAL sequence with no relationship to the old one, which is why standbys must be rebuilt rather than repointed.

Two consequences worth holding.

Failures are almost always in the schema restore, because that is the only step that interprets anything. Extension libraries missing, a data type removed in the new version, an incompatible catalogue object — all surface there, and all are exactly what --check looks for.

The new cluster is not a copy of the old one’s identity. It has a different system identifier, a fresh WAL history, and no relationship to the old cluster’s backups. A backup taken before the upgrade cannot be used to recover the new cluster forward — it recovers the old one. That is why the first post-upgrade action, after verifying, should be a new base backup: until it exists, the recovery position is “restore the old cluster and upgrade again”, and the upgrade window is not a good thing to have to repeat.

What to take from this

Both versions’ binaries, an empty new cluster with matching locale, extensions installed, and a verified backup.
--check runs against a live old cluster. Use it days ahead.
The real run refuses a running old cluster.
Measured: 2 seconds for 78 MB in copy mode. Mechanism, not a projection.
Planner statistics survived identically; cumulative statistics did not. 17 said “Optimizer statistics are not transferred”; 18 says “Some”.
Resetting WAL archives means standbys must be rebuilt and archiving reconfigured.
Run vacuumdb --analyze-in-stages --missing-stats-only afterwards.
Keep delete_old_cluster.sh unrun for days, and take a new base backup as soon as you have verified.

Cross-course references

Linux for Production Sysadmins — Part XI (Package management) covers having two majors installed at once, which pg_upgrade requires and some packagings make awkward.
Ansible for Production Sysadmins — Part XXIV (Assertions and guardrails) covers refusing to proceed when a precondition is unmet, which is what --check is doing.