PostgreSQL 14 to 17 Upgrade Runbook SpecimenSample deliverable
Pre-Flight & Cutover Result
4.8 TB cluster upgraded from PostgreSQL 14 to 17 with hard-links and 90-second staged ANALYZE
Pre-flight auditing caught two legacy md5 service roles, a missing PUBLIC schema CREATE grant for the migration runner, and an outdated PostGIS build. On cutover night, pg_upgrade --link and vacuumdb --analyze-in-stages completed inside a 7-minute maintenance window.
pg_upgrade --link time5m 18s for 4.8 TB
Staged ANALYZE (16 jobs)1m 34s
1. OS glibc collation version mismatch & extension pre-flight SQL
-- Detect OS glibc / ICU collation version drift before upgrading
SELECT collname, collprovider, collversion AS recorded_version,
pg_collation_actual_version(oid) AS actual_os_version
FROM pg_collation
WHERE collversion IS NOT NULL
AND collversion <> pg_collation_actual_version(oid);
-- Audit installed extensions against available target versions
SELECT e.extname, e.extversion AS installed_ver, a.default_version AS target_ver
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname
ORDER BY e.extname;2. Hard-link pre-flight check & post-cutover staged statistics rebuild
# Pre-flight compatibility check (non-destructive, runs against live or stopped source) /usr/lib/postgresql/17/bin/pg_upgrade \ --old-bindir=/usr/lib/postgresql/14/bin \ --new-bindir=/usr/lib/postgresql/17/bin \ --old-datadir=/var/lib/postgresql/14/main \ --new-datadir=/var/lib/postgresql/17/main \ --link --jobs=8 --check # Immediately after cutover: regenerate planner histograms in 3 fast passes /usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages --jobs=16
