pg_upgrade Workflows, Link/Clone Modes, Statistics, Analyze, and Rollback Planning

Learning outcomes

ServiceHub chooses pg_upgrade to move a large local cluster from PostgreSQL 17 to 18 without the time and temporary disk footprint of a full logical reload. The actual conversion can be fast, but the surrounding workflow—new cluster initialization, checksum compatibility, extension libraries, mode choice, statistics, rebuild scripts and rollback point—determines whether the change is safe.

1. Prepare isolated directories and record tool ownership

export OLD_BIN=/path/to/postgresql-17/binexport NEW_BIN=/path/to/postgresql-18/binexport OLD_DATA="$PWD/ch23_pg17"export NEW_DATA="$PWD/ch23_pg18"export OLD_PORT=55471export NEW_PORT=55472"$OLD_BIN/postgres" --version"$NEW_BIN/postgres" --version"$NEW_BIN/pg_upgrade" --version

$env:OLD_BIN = 'C:\Program Files\PostgreSQL\17\bin'$env:NEW_BIN = 'C:\Program Files\PostgreSQL\18\bin'$env:OLD_DATA = "$PWD\ch23_pg17"$env:NEW_DATA = "$PWD\ch23_pg18"$env:OLD_PORT = '55471'$env:NEW_PORT = '55472'& "$env:OLD_BIN\postgres.exe" --version& "$env:NEW_BIN\postgres.exe" --version& "$env:NEW_BIN\pg_upgrade.exe" --version

Always run the pg_upgrade binary from the new PostgreSQL installation. Keep versioned binaries side-by-side until validation and rollback windows have closed.

2. Initialize the old cluster and create representative data

rm -rf "$OLD_DATA" "$NEW_DATA""$OLD_BIN/initdb" -D "$OLD_DATA""$OLD_BIN/pg_ctl" -D "$OLD_DATA" -o "-p $OLD_PORT" -l ch23-old.log start"$OLD_BIN/createdb" -p "$OLD_PORT" servicehub_ch23"$OLD_BIN/psql" -X -p "$OLD_PORT" -d servicehub_ch23 <<'SQL'CREATE TABLE work_order (  work_order_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  status text NOT NULL,  amount numeric(12,2) NOT NULL);INSERT INTO work_order(status,amount)SELECT CASE WHEN g%2=0 THEN 'open' ELSE 'closed' END,       g/10.0FROM generate_series(1,50000) AS g;CREATE INDEX work_order_status_idx ON work_order(status);ANALYZE work_order;SQL

For a real cluster, add every extension/library, tablespace, full-text dictionary, collation and replication dependency to the inventory. This minimal lab focuses on the core upgrade mechanism.

3. Check checksum compatibility before initializing the new cluster

PostgreSQL 18 enables data checksums by default for newly initialized clusters. PostgreSQL 17 did not. pg_upgrade requires the old and new clusters' checksum settings to match.

"$OLD_BIN/pg_controldata" "$OLD_DATA" |  grep -i "Data page checksum version" || true

If the old lab says checksum version 0, initialize the PostgreSQL 18 target with --no-data-checksums. If the old cluster has checksums enabled, initialize the target compatibly instead of disabling them.

# For an old cluster WITHOUT checksums:"$NEW_BIN/initdb" --no-data-checksums -D "$NEW_DATA"# For an old cluster WITH checksums, omit --no-data-checksums# and verify pg_controldata after initdb.

4. Install new-major extension binaries before pg_upgrade

Before the real upgrade, install every native/contrib extension shared object for PostgreSQL 18. pg_upgrade will move catalog/schema state; you should not manually create duplicate extension definitions in the new cluster.

SELECT e.extname, e.extversion,       a.default_version AS packaged_defaultFROM pg_extension AS eLEFT JOIN pg_available_extensions AS a ON a.name=e.extnameORDER BY e.extname;

The lab's core objects need no optional extension. A production change must prove that every native library referenced by the old cluster has a compatible target-major package before the downtime window.

5. Run mode-specific pg_upgrade --check

"$OLD_BIN/pg_ctl" -D "$OLD_DATA" stop -m fast"$NEW_BIN/pg_upgrade"   --check   --old-bindir="$OLD_BIN"   --new-bindir="$NEW_BIN"   --old-datadir="$OLD_DATA"   --new-datadir="$NEW_DATA"   --old-port="$OLD_PORT"   --new-port="$NEW_PORT" 

& "$env:OLD_BIN\pg_ctl.exe" -D $env:OLD_DATA stop -m fast& "$env:NEW_BIN\pg_upgrade.exe" `  "--check" `  "--old-bindir=$env:OLD_BIN" `  "--new-bindir=$env:NEW_BIN" `  "--old-datadir=$env:OLD_DATA" `  "--new-datadir=$env:NEW_DATA" `  "--old-port=$env:OLD_PORT" `  "--new-port=$env:NEW_PORT"

On Windows, run pg_upgrade from the new installation under an account with the required administrative/file permissions, and keep quoted paths when PostgreSQL is installed under Program Files. The check still validates database compatibility; it does not substitute for a tested backup and rollback plan.

--check performs compatibility checks without upgrading. It can also be run while the old server is still running if old/new ports differ. If you intend to use --link, --clone, --copy-file-range, or --swap, repeat --check with that mode so filesystem-specific constraints are validated.

6. Choose the file-transfer mode by rollback semantics

Clone availability is platform/filesystem-specific. Swap can be fastest for clusters with many relations, but once pg_upgrade reports the old cluster is unsafe, restoring the old cluster requires a backup—not optimism.

7. Perform the disposable upgrade in copy mode

"$NEW_BIN/pg_upgrade"   --old-bindir="$OLD_BIN"   --new-bindir="$NEW_BIN"   --old-datadir="$OLD_DATA"   --new-datadir="$NEW_DATA"   --old-port="$OLD_PORT"   --new-port="$NEW_PORT"   --jobs=2"$NEW_BIN/pg_ctl" -D "$NEW_DATA" -o "-p $NEW_PORT" -l ch23-new.log start

Expected final output includes compatibility success plus post-upgrade instructions/scripts. Do not invent the exact timing; object count, storage and mode dominate it.

8. PostgreSQL 18 preserves most optimizer statistics—but not all statistics

Unless --no-statistics is used, PostgreSQL 18 pg_upgrade transfers most ordinary optimizer statistics. It does not transfer everything: extended statistics created with CREATE STATISTICS, extension-defined statistics, and cumulative statistics still need regeneration/recollection.

"$NEW_BIN/vacuumdb" -p "$NEW_PORT"   --all --analyze-in-stages --missing-stats-only --jobs=2"$NEW_BIN/vacuumdb" -p "$NEW_PORT"   --all --analyze-only --jobs=2

Also run any rebuild/fixup scripts that pg_upgrade emits before using affected tables. PostgreSQL explicitly warns that tables named in rebuild scripts can produce wrong results or poor performance until those scripts finish.

9. Verify data, plans, extensions, collations and generated scripts

SELECT version();SELECT count(*) AS rows,       sum(amount) AS amount_sum,       min(work_order_id) AS min_id,       max(work_order_id) AS max_idFROM work_order;SELECT extname, extversionFROM pg_extensionORDER BY extname;SELECT c.oid::regcollation,       c.collversion,       pg_collation_actual_version(c.oid) AS actual_versionFROM pg_collation AS cWHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)ORDER BY 1;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT count(*) FROM work_order WHERE status='open';

Correct row counts do not prove application correctness; a fast plan does not prove all workload classes are fast. Keep the old cluster intact in copy/clone mode until the Chapter 23 validation gate is satisfied.

10. Wrong rollback promise: link mode after starting the new cluster

11. Cleanup only after acceptance

"$NEW_BIN/pg_ctl" -D "$NEW_DATA" stop -m fast# Only because this is disposable and validation is complete:rm -rf "$OLD_DATA" "$NEW_DATA"rm -f ch23-old.log ch23-new.log

In production, do not run the old-cluster deletion script until backups/restores, application checks, replication, scheduled jobs, performance canaries and rollback policy all say the old physical cluster is no longer needed.

Authoritative references

Upgrade, compatibility, locking, and migration behavior is version-sensitive. These PostgreSQL primary sources define the mechanisms used here; always read the exact source/target release notes during a real change.

• PostgreSQL 18 — pg_upgrade
• PostgreSQL 18 — Release 18 / pg_upgrade changes
• PostgreSQL 18 — Upgrading a PostgreSQL Cluster
• PostgreSQL 18 — vacuumdb
• PostgreSQL 18 — CREATE EXTENSION