Version Upgrade Planning and Validation

infoMigration

Planning PostgreSQL major version upgrade requires understanding compatibility, performance changes, and risk mitigation.

Prompt: “We need to upgrade from PostgreSQL 12 to PostgreSQL 15. What should I check for compatibility issues, and how do I validate performance won't regress after the upgrade?”

Agent Playbook

When an agent encounters this scenario, Schema provides these diagnostic steps automatically.

When planning a PostgreSQL major version upgrade, start by identifying breaking changes and deprecated features in your current database, then validate extension compatibility before you invest time in testing. Only after those blockers are cleared should you establish performance baselines and build your validation test suite. Always have a tested rollback plan before you touch production.

1Audit for deprecated features and breaking changes

First thing I do is query pg_catalog to find deprecated features that will break in PostgreSQL 15. Check for removed data types (like abstime, reltime), WITH OIDS tables, deprecated SQL constructs, and hash indexes on system catalogs. Run pg_upgrade --check on a snapshot first—it'll catch most issues. The `deprecated-features-in-use` insight is critical here; a single incompatibility can block your entire upgrade until you refactor application code.

deprecated features in use version upgrade compatibility check version upgrade considerations

2Verify extension compatibility and upgrade paths

Check every extension in pg_extension against the PostgreSQL 15 compatibility matrix. Extensions like PostGIS, TimescaleDB, pgvector often require specific versions for PG15 compatibility. Test extension upgrades in a dev environment first—some require ALTER EXTENSION ... UPDATE, others need a drop/recreate. This is where `extension-version-compatibility` becomes a hard blocker if you're running custom or unmaintained extensions.

extension version compatibility version upgrade considerations

3Map configuration parameter changes

Review your current postgresql.conf against the release notes for PG 13, 14, and 15—several GUCs were renamed or removed. Key changes: wal_keep_segments became wal_keep_size (units changed!), vacuum_cleanup_index_scale_factor was removed, and many defaults tightened. The `configuration-parameter-changes-needed` insight will guide which params need updates. Missing this means your tuned settings won't apply post-upgrade.

configuration parameter changes needed version upgrade considerations

4Capture current performance baseline

Before touching anything, capture at least a week of `queries_duration_max`, pg_stat_statements data, buffer hit ratios, and I/O patterns. Note the `stats_reset` timestamp so you know your baseline window. Also record `uptime` and `postgresql.database.size` to validate the upgrade didn't corrupt data. Without this baseline, you can't objectively measure whether performance regressed—just gut feelings and angry users.

performance regression risk queries_duration_maxstats_resetuptimepostgresql.database.size

5Build test plan for query plan changes

PostgreSQL 15 has planner improvements that can change query plans—sometimes for worse on your specific schema. Run EXPLAIN ANALYZE on your top 50 queries (by total time) in both PG12 and PG15, comparing plans and actual timings. Watch for nested loop vs hash join switches, or new parallel query paths that might thrash your I/O. The `testing-and-validation-methodology` insight should guide your test coverage. Replay production workload with pgbench or pg_replay if possible.

testing and validation methodology performance regression risk queries_duration_max

6Prepare and test rollback procedure

Major version upgrades are one-way via pg_upgrade—you can't downgrade the data directory. Take a full pg_dump before starting, verify restore time fits your RTO, and keep the old PG12 binaries around. If using replication, plan to promote a lagging PG12 replica if the upgrade fails. The `rollback-plan-needed` insight is non-negotiable; I've seen teams stuck on a broken PG15 for days because they had no rollback path. Test the rollback, don't just document it.

rollback plan needed version upgrade considerations postgresql.database.sizeuptime