The 9-Step Postgres Major-Upgrade Playbook for Teams Without DevOps

The 9-Step Postgres Major-Upgrade Playbook for Teams Without DevOps

Major PostgreSQL upgrades are rarely about the upgrade itself — they're about everything a lean team doesn't have time to double-check: extension compatibility, replication lag, and the one query that quietly depends on deprecated behavior. For a three-person engineering team with no dedicated DBA, the risk isn't technical complexity; it's missing a step under deadline pressure. This playbook is built for exactly that constraint.

Pre-Upgrade: Know What You're Running Into

Start by confirming your current and target major versions, and read the release notes for every version in between — not just the destination. Postgres major upgrades can change default behaviors, deprecate parameters, or alter planner defaults in ways that affect query performance even when nothing "breaks."

1. Inventory extensions. Check that every extension you rely on (PostGIS, pg_cron, pgvector, etc.) has a stable release for the target version.

2. Audit custom types and functions. Anything written in older procedural syntax deserves a manual review.

3. Check client driver compatibility. Older ORMs or connection poolers sometimes lag behind new protocol or catalog changes.

4. Baseline current performance. Capture slow-query logs, index usage stats, and table sizes before touching anything. Without a baseline, you can't tell whether a post-upgrade slowdown is real or imagined.

Staging: Where the Upgrade Actually Happens

Never run a major upgrade cold against production. Spin up a staging environment that mirrors production data volume as closely as possible — a scaled-down dataset can hide planner regressions that only appear at real table sizes.

5. Run `pg_upgrade` (or logical replication) in staging first. `pg_upgrade` is faster for same-architecture moves; logical replication is better when you need near-zero downtime or are changing infrastructure simultaneously.

6. Regenerate statistics immediately. Run `ANALYZE` across all tables post-upgrade. New planner versions make decisions based on fresh statistics, and stale stats are the single most common cause of "everything got slower" reports after an upgrade.

7. Re-run your baseline queries. Compare execution plans, not just timings. A query that runs at the same speed but took a different plan to get there is a warning sign worth investigating before it becomes a production incident. This is where ongoing query performance monitoring earns its keep — catching plan shifts early rather than after a support ticket.

Cutover and Rollback: The Parts You Can't Improvise

8. Schedule the cutover during low-traffic windows, with a hard time-boxed rollback trigger. Decide in advance how long you'll wait before reverting — "it's probably fine, let's give it another hour" is how small teams turn a 20-minute maintenance window into a half-day outage. Keep the old version's data directory or a recent snapshot untouched until the new version has run cleanly through a full business cycle.

9. Document the rollback path before you need it. For `pg_upgrade`, this usually means preserving the old cluster; for logical replication, it means keeping the original primary as a fallback target. Either way, write the rollback steps down and assign who executes them — deciding mid-incident is slower and riskier than deciding in advance.

Keeping It Boring Going Forward

The goal of this playbook isn't heroics — it's making major upgrades routine enough that a three-person team can run one without burning a weekend. Tracking index health and