What you will learn
Focus on extensions, application SQL, roles, and the toolchain for a major-version upgrade. A minor patch update is not a pg_upgrade migration.
Before you begin
Prepare an isolated clone of the old cluster, a separately initialized target-major cluster, both binary directories, and compatible target extension libraries. Keep production endpoints unreachable from the rehearsal. The example directory names are illustrative; replace them with the exact old/new installations.
1. Capture a testable baseline
SELECT version();
SELECT extname, extversion FROM pg_extension;
SELECT datname FROM pg_database WHERE datallowconn;
Record critical SQL, result invariants, timings, roles, schema ownership, and sequence behavior. Include the real application role rather than running all tests as a superuser.
2. Run the compatibility check
/opt/postgresql/new/bin/pg_upgrade --check \
--old-bindir=/opt/postgresql/old/bin --new-bindir=/opt/postgresql/new/bin \
--old-datadir=/srv/postgresql/old --new-datadir=/srv/postgresql/new
Resolve every reported blocker, including extension libraries and incompatible data types. A successful check does not validate application behavior.
3. Upgrade the isolated copy
Stop both rehearsal clusters according to the tool’s requirements, rerun the reviewed command without --check, and retain its output. Use the default copy mode for this first rehearsal so you do not introduce hard-link rollback constraints. Follow generated post-upgrade instructions and rebuild statistics before comparing plans.
4. Exercise the application
Test extension-dependent SQL, migrations, object creation, grants, and background jobs. Fix compatibility issues in version-controlled application changes and repeat the entire rehearsal from a fresh clone.
5. Measure the production window
Separate clone time, compatibility checks, actual conversion, statistics collection, and application validation. Use those measurements to define a maintenance budget; do not extrapolate a small development database’s elapsed time directly to production.
Example output
The following is an illustrative, normalized lab result, not output captured from a live customer system. Your versions, addresses, timings, and row counts will differ.
Illustrative rehearsal record:
pg_upgrade --check: passed
Upgrade exit status: 0
Required extensions: verified
Application role tests: passed
Critical SQL comparisons: passed
Restore rehearsal: passed
Record failures and measured durations next to these gates during your own rehearsal. This compact record is not evidence that the example commands have been executed on your database.
Verify the result
The upgraded clone passes functional tests, permission checks, and a restore rehearsal. Confirm no background job points to production. Preserve upgrade logs and list unresolved performance differences explicitly before approving a production plan.
Troubleshooting
Missing shared libraries point to target extension installation. Permission failures can be ownership or default-grant differences. Regressed plans should be investigated with current statistics and representative parameters, not by copying arbitrary old planner settings.
Recovery and next steps
Discard a failed rehearsal only after preserving logs; restart from the original backup/clone. In production, post-upgrade writes create a new recovery boundary. Do not assume the old cluster can simply be restarted, especially if link mode was used.