PostgreSQL Major Upgrade Runbook

$29

Tested, rollback-safe runbook for a production Postgres major-version upgrade (pg_upgrade and logical-replication paths) with pre-flight checks, post-upgrade SQL, and rollback per stage.

📁 7 files🏷 v1.0.0 (updated 2026-08-02)

SQLMarkdownJSONShellPostgreSQL

📄 Product Preview

Try the interactive reader and demo tools below, or get the full product with all content unlocked.

📖 Interactive Reader (Free Preview) 📦 Download Free Sample

📁 File Structure 7 files

postgres-major-upgrade-runbook/ ├── PRE-FLIGHT-CHECKLIST.md ├── README.md ├── ROLLBACK.md ├── UPGRADE-RUNBOOK.md └── scripts/ ├── post_upgrade.sql └── preflight.sh

📖 Documentation Preview README excerpt

PostgreSQL Major Version Upgrade Runbook

A tested, rollback-safe runbook for taking a production PostgreSQL database across

a major version boundary (e.g. 13→16) — the kind of task an agent gets handed

("upgrade our Postgres") and that goes badly without a plan. Covers both the

pg_upgrade (in-place) and logical-replication (near-zero-downtime) paths, with a

pre-flight checklist and a rollback for each.

Two paths — pick by your downtime budget

Path	Downtime	Complexity	Use when
`pg_upgrade --link`	minutes	low	You can take a short maintenance window
Logical replication	seconds (cutover only)	higher	You need near-zero downtime
Managed (RDS/Cloud SQL)	provider-managed	low	You're on a managed service

What's inside

UPGRADE-RUNBOOK.md — the full step-by-step for both paths, with exact commands.
PRE-FLIGHT-CHECKLIST.md — everything to verify before you touch prod

(extensions, deprecated features, disk, replication slots, pg_upgrade --check).

ROLLBACK.md — how to abort safely at each stage.
scripts/preflight.sh — runs the read-only pre-flight checks and prints a report.
scripts/post_upgrade.sql — the post-upgrade tasks people forget (ANALYZE,

reindex where needed, extension updates, statistics).

The golden rules

1. Back up first, and test the restore — before anything.

2. Run pg_upgrade --check — it catches most blockers before downtime starts.

3. ANALYZE immediately after — the new cluster has no statistics; queries

will be slow and may pick bad plans until you do.

4. Have the rollback ready and rehearsed in staging before prod.

Requirements

PostgreSQL 12+ source; access to run pg_upgrade / manage replication; a staging

copy to rehearse on. preflight.sh needs psql.

License

MIT.

📄 Code Sample .sql preview

scripts/post_upgrade.sql-- Post-upgrade tasks. Run immediately after starting the new cluster. -- 1. Rebuild planner statistics (CRITICAL — the new cluster has none). ANALYZE VERBOSE; -- 2. Update extensions to the version bundled with the new server. -- (Repeat per extension listed by preflight.sh.) -- ALTER EXTENSION pg_stat_statements UPDATE; -- ALTER EXTENSION postgis UPDATE; -- 3. Reindex anything that depends on collation (glibc collation changes across -- OS/major upgrades can silently corrupt text indexes). If your collation -- provider/version changed, reindex text/varchar indexes: -- REINDEX DATABASE CONCURRENTLY current_database(); -- 4. Refresh materialized views if any depend on changed behavior. -- 5. Re-check sequence values if you used logical replication. SELECT 'post-upgrade tasks: review ANALYZE output, then handle extensions + reindex' AS note;

Buy Now — $29 Back to Products