Lab 26: Rehearse a 17 to 18 upgrade, and find the obstacle before the maintenance window does

Objective

pg_upgrade is fast — 1.6 seconds in this lab run, and minutes rather than hours on databases of any size. The risk in a major version upgrade is almost never the upgrade itself. It is everything you find out during the maintenance window that you could have found out a week earlier.

This lab finds one of those things. pg_upgrade --check refuses to run at all, for a reason that affects every 17-to-18 upgrade and appears in no upgrade checklist written before PostgreSQL 18: 17 creates clusters with data checksums off, 18 creates them on, and pg_upgrade will not bridge the two.

You will also establish precisely what PostgreSQL 18’s pg_upgrade carries across in the way of planner statistics, because the answer changed in 18 and the old advice is now partly wrong.

Architecture

flowchart LR
    S["source: PostgreSQL 17.11\n/var/lib/postgresql/17data\n400,000 rows, 43 MB\nchecksum version 0"] --> C{"pg_upgrade --check"}
    T["target: PostgreSQL 18.6\ninitdb with 18 defaults\nchecksum version 1"] --> C
    C -->|refuses| F["old cluster does not use data checksums\nbut the new one does"]
    F --> R["pg_checksums --enable on the source"]
    R --> C2["--check: Clusters are compatible"]
    C2 --> U["pg_upgrade: 1587 ms"]
    U --> V["verify: rows, roles, privileges, statistics"]

Requirements

• Both major versions installed. On Debian, postgresql-17 and postgresql-18 coexist; the binaries are under /usr/lib/postgresql/<version>/bin.
• Disk for two copies in the default copy mode.
• Superuser access, and the ability to stop the source cluster.

Scenario

You are planning to move a production database from PostgreSQL 17 to 18. The change request asks for a maintenance window, and you need to say how long, and to be confident nothing will stop halfway through.

Tasks

Task 1 — The source cluster

LAB="$HOME/rbpg-lab-26"
mkdir -p "$LAB"

docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17data -U postgres
docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/pg_ctl \
  -D /var/lib/postgresql/17data -o "-p 5432" -l /tmp/17.log start
sleep 4

docker exec -i -u postgres rbpg-upg psql -X -p 5432 <<'SQL'
CREATE DATABASE app;
\c app
CREATE TABLE orders(id int primary key, customer text, total numeric, placed date);
INSERT INTO orders SELECT g, 'cust-'||(g%5000), (g%700)*1.25, date '2025-01-01'+(g%400)
  FROM generate_series(1,400000) g;
CREATE INDEX orders_placed_idx ON orders(placed);
CREATE INDEX orders_customer_idx ON orders(customer);
CREATE ROLE app_ro;
GRANT SELECT ON orders TO app_ro;
VACUUM ANALYZE;
SQL

docker exec -u postgres rbpg-upg psql -X -p 5432 -c "SELECT version();" | tee "$LAB/source.txt"
docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/pg_controldata \
  -D /var/lib/postgresql/17data | grep -E "system identifier|checksum version|cluster state" \
  | tee -a "$LAB/source.txt"

$ version(), then pg_controldata on the 17 cluster

 PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

orders | size  
--------+-------
400000 | 43 MB

Database system identifier:           7678968806233899141
Database cluster state:               in production
Data page checksum version:           0

Data page checksum version: 0. That is not a misconfiguration — it is what initdb did by default on PostgreSQL 17, and it is true of essentially every cluster created before PostgreSQL 18.

Task 2 — The target, and the refusal

docker exec -u postgres rbpg-upg /usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18data -U postgres
docker exec -u postgres rbpg-upg /usr/lib/postgresql/18/bin/pg_controldata \
  -D /var/lib/postgresql/18data | grep "checksum version"

docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/pg_ctl -D /var/lib/postgresql/17data stop -m fast

docker exec -u postgres -w /var/lib/postgresql/upgtmp rbpg-upg \
  /usr/lib/postgresql/18/bin/pg_upgrade \
    -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17data -D /var/lib/postgresql/18data --check \
  | tee "$LAB/check-failure.txt"

$ initdb the 18 target with defaults, stop the source, run pg_upgrade --check

Data page checksum version:           1

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

old cluster does not use data checksums but the new one does
Failure, exiting

pg_upgrade checked the versions, compared the checksum settings, and stopped.

Task 3 — Two ways out, and the better one

Option A: turn checksums off on the target.

initdb --no-data-checksums -D /var/lib/postgresql/18data

Instant, and it gives up a feature PostgreSQL 18 turned on by default for good reasons — Lab 9’s territory, and the difference between corruption detected and corruption silently returned.

Option B: turn checksums on at the source.

docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/pg_checksums \
  --enable -D /var/lib/postgresql/17data -P
docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/pg_controldata \
  -D /var/lib/postgresql/17data | grep "checksum version"

$ pg_checksums --enable on the stopped source cluster

Files written:  1035
Blocks written: 8364
pg_checksums: updating control file
Checksums enabled in cluster

elapsed: 159 ms on a 43 MB database
Data page checksum version:           1

docker exec -u postgres -w /var/lib/postgresql/upgtmp rbpg-upg \
  /usr/lib/postgresql/18/bin/pg_upgrade \
    -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17data -D /var/lib/postgresql/18data --check

$ pg_upgrade --check, second attempt

Checking for contrib/isn with bigint-passing mismatch         ok
Checking for valid logical replication slots                  ok
Checking for subscription state                               ok
Checking data type usage                                      ok
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*

Read that list rather than skipping to the verdict. Each line is a failure somebody has had. “Checking for presence of required libraries” is the one that catches most real upgrades: an extension installed on the old cluster whose .so is not present for the new version stops the upgrade, and the fix is installing packages, not anything you can do quickly.

Task 4 — The upgrade

docker exec -u postgres -w /var/lib/postgresql/upgtmp rbpg-upg \
  /usr/lib/postgresql/18/bin/pg_upgrade \
    -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17data -D /var/lib/postgresql/18data | tee "$LAB/upgrade.txt"

$ pg_upgrade without --check

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

pg_upgrade exit status: 0
ELAPSED: 1587 ms for a 43 MB database

Task 5 — Verify

docker exec -u postgres rbpg-upg /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/18data -o "-p 5433" -l /tmp/18.log start
sleep 4

docker exec -u postgres rbpg-upg psql -X -p 5433 -c "SELECT version();"
docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c "SELECT count(*) FROM orders;"
docker exec -u postgres rbpg-upg psql -X -p 5433 -c "SELECT rolname FROM pg_roles WHERE rolname='app_ro';"
docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c "\dp orders"

$ start the new cluster on port 5433 and check what arrived

 orders 
--------
400000

rolname 
---------
app_ro
(1 row)

                                Access privileges
Schema |  Name  | Type  |     Access privileges      | Column privileges | Policies 
--------+--------+-------+----------------------------+-------------------+----------
public | orders | table | postgres=arwdDxtm/postgres+|                   | 
      |        |       | app_ro=r/postgres          |                   | 
(1 row)

Note the contrast with Lab 17: pg_upgrade does bring roles and privileges, because it upgrades the whole cluster rather than one database. The globals problem that made a logical restore fail does not arise here.

Task 6 — What about the statistics?

Long-standing advice is that pg_upgrade discards optimizer statistics and you must ANALYZE immediately. Check it:

docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c "
  SELECT relname, n_live_tup, last_analyze, last_autoanalyze
  FROM pg_stat_user_tables WHERE relname='orders';"
docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c "
  SELECT attname, n_distinct,
         array_length(most_common_vals::text::text[],1) AS mcv,
         array_length(histogram_bounds::text::text[],1) AS hist
  FROM pg_stats WHERE tablename='orders' ORDER BY attname;" | tee "$LAB/statistics.txt"
docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c \
  "EXPLAIN SELECT * FROM orders WHERE placed = date '2025-06-01';"

$ check pg_stat_user_tables, then pg_stats, then a plan

 relname | n_live_tup | last_analyze | last_autoanalyze 
---------+------------+--------------+------------------
orders  |          0 |              | 
(1 row)

attname  | n_distinct | mcv | hist 
----------+------------+-----+------
customer |       5002 |  10 |  101
id       |         -1 |     |  101
placed   |        400 |   4 |  101
total    |        700 |   6 |  101
(4 rows)

Bitmap Heap Scan on orders  (cost=12.15..2014.06 rows=997 width=23)

The advice is now out of date. Every column has its n_distinct, its most-common-values list and a full 101-bucket histogram, and the planner produced a real estimate of 997 rows immediately.

Run what pg_upgrade recommended and compare:

docker exec -u postgres rbpg-upg /usr/lib/postgresql/18/bin/vacuumdb \
  --all --analyze-in-stages --missing-stats-only -p 5433
docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c \
  "EXPLAIN SELECT * FROM orders WHERE placed = date '2025-06-01';"

$ vacuumdb --analyze-in-stages --missing-stats-only, then the same EXPLAIN

vacuumdb: processing database "app": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Bitmap Heap Scan on orders  (cost=12.15..2014.06 rows=997 width=23)

docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c "
  SELECT c.relname, c.reltuples::bigint AS reltuples_transferred,
         s.n_live_tup AS counter, s.n_dead_tup,
         (current_setting('autovacuum_vacuum_threshold')::int
          + current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)::bigint
           AS autovacuum_fires_at
  FROM pg_class c JOIN pg_stat_user_tables s ON s.relid=c.oid WHERE c.relname='orders';"

$ compare reltuples against the pg_stat_user_tables counter, and compute the autovacuum threshold

 relname | reltuples_transferred | counter | n_dead_tup | autovacuum_fires_at 
---------+-----------------------+---------+------------+---------------------
orders  |                400000 |       0 |          0 |               80050
(1 row)

reltuples is 400,000 and the activity counter is 0. Lab 10 established that autovacuum’s threshold is computed from reltuples, so it is correctly 80,050 — the zeroed counters do not mislead it.

What the zeroed counters do affect is your monitoring: every dashboard built on pg_stat_user_tables and pg_stat_database restarts from zero after an upgrade, and any alert on a rate will see a discontinuity.

Task 7 — The transfer modes

docker exec -u postgres rbpg-upg /usr/lib/postgresql/18/bin/pg_upgrade --help \
  | grep -E "^  -k|^  --clone|^  --copy|^  --swap|^  -j"
echo "--- PostgreSQL 17 for comparison ---"
docker exec -u postgres rbpg-upg /usr/lib/postgresql/17/bin/pg_upgrade --help \
  | grep -E "^  -k|^  --clone|^  --copy|^  --swap|^  -j"

$ grep the transfer-mode options from both versions' --help

  -j, --jobs=NUM                number of simultaneous processes or threads to use
-k, --link                    link instead of copying files to new cluster
--clone                       clone instead of copying files to new cluster
--copy                        copy files to new cluster (default)
--copy-file-range             copy files to new cluster with copy_file_range
--swap                        move data directories to new cluster

--- PostgreSQL 17 for comparison ---
-j, --jobs=NUM                number of simultaneous processes or threads to use
-k, --link                    link instead of copying files to new cluster
--clone                       clone instead of copying files to new cluster
--copy                        copy files to new cluster (default)
--copy-file-range             copy files to new cluster with copy_file_range

-j parallelises across tablespaces and databases; set it to the number of CPUs for a cluster with many databases.

Validation

test -s "$LAB/source.txt"        && echo "OK source"
test -s "$LAB/check-failure.txt" && echo "OK check-failure"
test -s "$LAB/upgrade.txt"       && echo "OK upgrade"
test -s "$LAB/statistics.txt"    && echo "OK statistics"

grep -q "does not use data checksums" "$LAB/check-failure.txt" && echo "OK checksum obstacle captured"
grep -q "Upgrade Complete"            "$LAB/upgrade.txt"       && echo "OK upgrade completed"

# The verification the change request needs:
docker exec -u postgres rbpg-upg psql -X -p 5433 -d app -c \
  "SELECT count(*) = 400000 AS all_rows_present FROM orders;"
docker exec -u postgres rbpg-upg psql -X -p 5433 -c \
  "SELECT count(*) = 1 AS role_present FROM pg_roles WHERE rolname='app_ro';"

Questions to answer without looking anything up:

1. pg_upgrade --check reports “old cluster does not use data checksums but the new one does”. What are your two options and which costs downtime?
2. Why must pg_checksums --enable be scheduled separately from the upgrade window?
3. pg_upgrade finished. Is it safe to run delete_old_cluster.sh?
4. On PostgreSQL 18, what do you lose by opening to traffic before running ANALYZE? On 17?
5. Which two transfer modes leave you with no rollback, and what must you have before using them?

Expected Outcome

You have rehearsed a complete major version upgrade, hit an obstacle that would have consumed a maintenance window, resolved it, and produced a timing.

The rehearsal procedure, which is the actual deliverable:

# A week before, on a restored copy:
pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
           -d $OLD -D $NEW --check
# Fix everything it names. Record how long each fix took.

# Time the real thing on the copy:
time pg_upgrade -b ... -B ... -d $OLD -D $NEW -j $(nproc)

# Verify as the application, not by counting rows:
psql -p 5433 -d app -c "SET ROLE app_ro; SELECT ...;"

# Only then plan the window, using the measured time plus the fixes.

And three things this lab established that a pre-18 checklist will not mention: the checksum default changed, pg_upgrade now transfers column statistics, and --swap exists.

Troubleshooting

pg_upgrade --check fails with old cluster does not use data checksums but the new one does. PostgreSQL 17 and earlier default to checksums off; 18 defaults to on. A stock 17 cluster will not upgrade into a stock 18 cluster. This is Task 2, and it is the obstacle that consumes a maintenance window when it is first met inside one.

Which way out to choose. Enabling checksums on the source with pg_checksums --enable on a stopped cluster is the better answer — measured at 159 ms on a 43 MB database, scaling with size. Creating the new cluster with --no-data-checksums is faster and permanently gives up the protection; make it a recorded decision, not a workaround.

pg_upgrade refuses to run at all. Both clusters must be shut down cleanly. It says so, and the refusal is protecting you.

pg_upgrade reports a missing extension library. A compatible build for the target major version is not installed. Enumerate extensions in every database before the window; this failure appears at check time, and for a logical restore only at restore time.

Row counts match and the application cannot use the database. Counts prove the data moved. Verify as the application’s role, from the application’s network path, with the real driver.

The statistics look like they need rebuilding and they do not. PostgreSQL 18’s pg_upgrade says Some statistics are not transferred, where 17 said Optimizer statistics are not transferred. Measured on 18.6 from 17.11, full column statistics transferred — n_distinct, MCV lists, 101-bucket histograms — and the plan estimate was identical before and after the recommended vacuumdb. Run the commands anyway and record the comparison; it is what makes the next window shorter.

delete_old_cluster.sh is offered at the end. Do not run it yet. In copy mode the old cluster is the rollback.

Cleanup

docker exec -u postgres rbpg-upg /usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/18data stop -m fast
docker exec rbpg-upg rm -rf /var/lib/postgresql/17data /var/lib/postgresql/18data /var/lib/postgresql/upgtmp

Production notes

• Run --check days in advance and keep running it until every line reads ok. It is read-only, takes seconds, and finds every blocking condition. A check first run inside the window is a window spent reading error messages.
• Choose the transfer mode by its rollback, not by its speed. Copy leaves the old cluster intact and usable; --link makes it unusable once the new cluster starts; --swap moves the directories. Link and swap buy speed with rollback.
• Rehearse on production-scale data and record the duration of each phase — check, upgrade, statistics, verification, total. The next window is planned from those numbers rather than from an estimate.
• Take a new physical backup immediately afterwards. Every backup you hold is for the old major version and cannot restore into the new cluster, so until the new one exists the recovery path is a restore of the old version followed by another upgrade.
• Capture row counts, object counts by relkind, extension versions in every database, and EXPLAIN output for significant queries before the window. None of them can be reconstructed afterwards, and they are what the verification compares against.

What You Learned

• The checksum default changed in 18, and a stock 17 cluster will not upgrade into a stock 18 cluster without a deliberate decision.
• pg_checksums --enable is the better resolution — 159 ms on 43 MB here — and --no-data-checksums permanently gives up a real protection.
• --check is read-only and finds every blocking condition, in seconds, days before the window.
• PostgreSQL 18 transfers column statistics. The earlier wording, Optimizer statistics are not transferred, was PostgreSQL 17’s; 18 says Some statistics are not transferred, and the measurement confirms it.
• The transfer mode is a rollback decision. Copy keeps the old cluster; link and swap do not.
• Row counts are not verification. Verify as the application, and compare plans against the ones captured before.