Install the new PostgreSQL major version alongside the old one (both binaries available on the host); do not start the new cluster yet
Run the compatibility check: pg_upgrade -b /usr/lib/postgresql/<old>/bin -B /usr/lib/postgresql/<new>/bin -d /var/lib/postgresql/<old>/data -D /var/lib/postgresql/<new>/data --check
Stop the old cluster, then run pg_upgrade without --check to perform the actual upgrade; use --link to hard-link data files instead of copying them (dramatically faster on large databases, but makes rollback to the old version impossible)
After upgrade, run the generated update_extensions.sql script to update system extensions and the statistics collection command (vacuumdb --all --analyze-in-stages) to rebuild query planner statistics
Update postgresql.conf and pg_hba.conf in the new data directory to match your production settings, then start the new cluster
For a near-zero-downtime alternative, set up logical replication from old to new version, replicate all data, then cut over by switching writes to the new cluster
Known gotchas
pg_upgrade with --link is very fast but irreversible — once the new cluster starts, the old data files are modified and cannot be used to roll back; always take a base backup before upgrading
Extensions must be available in the new PostgreSQL version before pg_upgrade runs; check pg_catalog.pg_extension on the old cluster and install matching packages for the new version
Logical replication for major-version upgrade does not replicate sequences, large objects, or DDL; manually sync sequence values and run pg_dump for non-table objects after cutover
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp