PostgreSQL Major Upgrade Checklist: Test and Roll Back

Updated 9 November 2026 · Standard formula, shown below

Before a PostgreSQL major upgrade reaches production, test the target version with a realistic copy of your database, check every extension and application query, rehearse the upgrade procedure, and prove how you will recover if it fails. A rollback is not always a simple version downgrade: after writes occur on the upgraded cluster, returning to the old version may require restoring a backup or rebuilding from a replica.

What a major upgrade changes

A PostgreSQL major release changes the first part of the version number—for example, moving from PostgreSQL 15 to 16. Major upgrades can introduce new features and planner behavior, and may change system catalogs or on-disk formats. A cluster’s data directory is generally not directly interchangeable between major versions. By contrast, minor releases within a major version are designed to be installed without a dump-and-restore or major-version migration, though normal maintenance precautions still apply.

The main upgrade methods are pg_upgrade, logical dump and restore, and logical replication or another migration-based approach. Their downtime, complexity, and recovery options differ. Read the documentation for both the source and target versions, and for the exact method and operating system you will use; do not assume that a command or option from another release applies.

Build a representative test environment

Restore a recent production backup into an isolated staging environment, or use a production-like replica where appropriate. Match the target PostgreSQL version, operating system, architecture, configuration, extensions, connection settings, and relevant infrastructure as closely as practical. Protect copied data according to your organization’s privacy and access rules.

Record a baseline before changing anything. Capture database size, table and index sizes, connection counts, important query plans and timings, background-job behavior, and application error rates. Include peak-load patterns and scheduled work, not just a quick test that the server starts. A small sample database can miss large-table migration time, disk-space needs, and query plans that depend on production-scale data.

Inventory databases, roles, tablespaces, replication slots, foreign servers, scheduled jobs, and external integrations.
Save the current server configuration and note settings that are deprecated, removed, renamed, or changed in the target release.
Check available disk space for the upgrade method, temporary files, backups, and any period when old and new data coexist.
Run the rehearsal from documented commands or automation that can be repeated by the people responsible for the production change.

Verify extensions and other dependencies

List installed extensions and their versions in every database; extensions are not necessarily installed in only one place. Confirm that each required extension has a compatible build for the target PostgreSQL version and the target operating system. Check its installation or upgrade instructions, required shared libraries, control files, and any supported extension update path. A database upgrade tool may warn about extension libraries or incompatibilities, but it cannot make an unavailable or incompatible extension work.

Also review procedural languages, custom types and operators, foreign-data wrappers, authentication methods, backup and monitoring tools, drivers, and connection poolers. Verify that application clients can connect and that automation uses supported commands. After the server upgrade, an extension may need a separate SQL update, such as an appropriate ALTER EXTENSION ... UPDATE; use the extension’s documented procedure rather than guessing a target version.

Test schemas, data, and application queries

Schema compatibility includes more than whether tables exist. Exercise migrations and inspect dependencies involving functions, triggers, views, generated expressions, collations, constraints, permissions, and SQL that relies on system catalogs. Restore the backup using the planned procedure, then run application migrations and verify that objects, ownership, grants, sequences, and expected row counts are correct.

Test representative read and write paths through the application, including transactions, prepared statements, batch jobs, reports, and rarely used but important workflows. Compare results as well as speed: a query can still run but return different results because of application assumptions, changed behavior, collation differences, or data-handling bugs. Test queries with realistic parameters and data distributions.

Use EXPLAIN and, where safe, EXPLAIN (ANALYZE, BUFFERS) to compare plans and execution on staging. ANALYZE runs the query, so account for its effects when testing writes.
Refresh statistics after the upgrade as appropriate, then check for slow queries, unexpected sequential scans, lock waits, and increased temporary-file use.
Run application and integration tests against the target version, and check server logs for warnings, errors, and deprecated-feature notices.
Test the application’s real connection and failover behavior, not only a direct local command-line connection.

Rehearse the method and measure the outage

Choose the method based on database size, acceptable downtime, operational skills, and recovery requirements. pg_upgrade can reduce the time needed compared with a full logical reload, but its checks and prerequisites must pass. A dump and restore is straightforward conceptually and can validate a logical export, but may take longer. Logical replication can support a lower-downtime cutover, but requires careful handling of objects and changes that are not replicated in the same way as table data.

Rehearse the complete sequence, including pre-upgrade checks, backups, stopping writes, upgrade or synchronization, validation, application cutover, and post-upgrade maintenance. Measure each stage, including how long it takes to restore or recreate service—not just the command that changes the data directory. Set a realistic maintenance window with a buffer, and define explicit go/no-go checks such as successful application smoke tests, acceptable query latency, and verified replication or backup status.

Plan rollback before cutover

Take a verified backup and ensure you know how to restore it. A backup is not a rollback plan until the restore has been tested, including recovery of required configuration, roles, extension files, and any external dependencies. Record who can authorize a rollback, which signals trigger it, how the application will be redirected, and how long recovery is expected to take.

Keep the old cluster intact until the upgraded service has passed its acceptance checks. If production writes are allowed on the new cluster, the old cluster will not automatically receive those changes. Switching back can therefore lose data unless a deliberate reverse-replication or reconciliation plan exists. For many upgrades, the safe fallback after writes begin is restoring or rebuilding from a known-good backup and recovering changes using the organization’s established recovery process—not simply starting the old server against the new data.

Frequently Asked Questions

Can I downgrade PostgreSQL after a major upgrade?
Usually not by pointing the older PostgreSQL binary at the upgraded data directory. Plan to restore a compatible backup, rebuild the old-version cluster, or use a separately designed data-reconciliation strategy.

Does pg_upgrade test my application queries?
No. Its checks address upgrade prerequisites and compatibility conditions; application behavior, query results, performance, and integrations need separate testing.

Do PostgreSQL extensions upgrade automatically?
Do not assume so. Confirm target-version availability and follow each extension’s documented installation and SQL update steps.

How long should an upgrade maintenance window be?
There is no reliable universal duration. Rehearse with production-scale data and the chosen method, measure validation and recovery too, and schedule a buffer for unexpected issues.

Is a successful staging upgrade enough to proceed?
It is necessary but not sufficient. Confirm that staging resembles production, backups restore correctly, application owners approve results, monitoring is ready, and rollback actions are clear.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.