PostgreSQL Upgrade Checklist: Backups, Extensions & Replication

A safe PostgreSQL upgrade starts with a tested recovery plan, a version-by-version compatibility check, and a rehearsal on a copy of production data. This checklist covers backups, extensions, replication, upgrade methods, validation, and rollback so you can choose a process that fits your outage and recovery requirements.

Plan the upgrade and choose a method

First identify the current and target PostgreSQL major versions, the operating system, the installed packages, and the application’s supported versions. PostgreSQL major releases can change data formats and require a major-upgrade procedure; minor releases within a major version generally use the normal package update process and do not require a dump-and-restore or pg_upgrade.

For a major upgrade, the usual choices are:

• Dump and restore: Use tools such as pg_dump and pg_restore to create a logical copy in a new cluster. It is straightforward and can change architecture or encoding choices, but large databases may take substantial time.
• pg_upgrade: This utility upgrades an existing data directory to a new major version, usually faster than a full logical restore. It requires compatible installation and configuration, and its checks must pass before the upgrade.
• Replication-based migration: Logical replication or another supported migration design can keep a new cluster current while the old system serves writes. It can reduce planned downtime but requires careful handling of objects and changes that replication does not copy.

Do not assume one method is universally safest. Test the selected method with representative data, extensions, permissions, and application traffic. Check the PostgreSQL documentation for both source and target releases, because supported upgrade paths and options vary.

Backups and recovery readiness

A backup is useful only if it can be restored. Before the change, make a fresh backup using a method appropriate to your recovery objectives, and perform a restore test on a separate system. Keep the original cluster and its files intact until the upgraded service has passed acceptance checks.

• Take and verify a physical base backup, or make logical dumps as appropriate; for critical systems, consider both because they serve different recovery needs.
• Record how to restore the backup, including configuration files, tablespaces, certificates, service settings, and required extensions.
• Confirm that archived write-ahead logs (WAL), if used for point-in-time recovery, are being retained and can be retrieved. A base backup without the required WAL may not support the intended recovery point.
• Measure backup and restore duration. These timings help establish whether the planned maintenance window is realistic.
• Preserve a pre-upgrade snapshot or storage copy only if its consistency and restore procedure are understood; a casual filesystem copy of a running data directory is not necessarily a valid backup.

For example, a team that can restore a nightly dump but needs recovery to within a few minutes should not assume that dump alone meets its recovery point objective. It may need a tested physical-backup and WAL-archiving plan.

Inventory extensions and database objects

Extensions are a frequent source of upgrade failures. List installed extensions in every database, along with their current versions, and confirm that compatible packages or binaries exist for the target PostgreSQL release. An extension may need to be updated separately with ALTER EXTENSION ... UPDATE; upgrading the server does not guarantee that every extension has been upgraded.

• Check extension availability and supported upgrade paths with the extension’s documentation and package source.
• Include procedural languages, foreign-data wrappers, custom types, operator classes, and vendor-provided modules in the inventory.
• Check for unsupported or obsolete features, custom shared libraries, and configuration parameters removed or changed in the target release.
• Record roles, grants, ownership, tablespaces, publications, subscriptions, and scheduled jobs. A data-only copy may not include all of these cluster-level or operational objects.

Run pg_upgrade --check when using pg_upgrade, and resolve reported incompatibilities before the maintenance window. A successful check is valuable, but it does not replace testing the application against the upgraded cluster.

Replication and cutover planning

Replication needs an explicit plan: what is replicated, how lag is measured, how writes are stopped, and how the new primary is promoted. Physical streaming replication is generally tied to compatible PostgreSQL major-version clusters; it is not a simple way to stream a running old-major cluster directly into a new-major cluster. For cross-major migration, logical replication may be an option, but it does not automatically copy every database object or all forms of database state.

Before relying on replication, test initial synchronization and ongoing changes. Check that required tables have suitable replica identities, account for sequences and large objects where relevant, and handle DDL changes deliberately. Logical replication does not replicate schema changes automatically in the general case, and sequence values may require separate synchronization.

1. Bring the target cluster up to date and check replication lag.
2. Pause application writes or put the application into a controlled maintenance mode.
3. Allow outstanding changes to reach the target, then verify the final synchronization point.
4. Promote or redirect traffic using the documented procedure, and verify reads and writes against the new cluster.

Keep the old system available but fenced from writes after cutover. Allowing both old and new primaries to accept writes can create divergent data that is difficult to reconcile.

Run the upgrade and validate it

Rehearse the complete sequence, including downtime communications, service stops, upgrade commands, configuration changes, and application checks. On upgrade day, confirm that backups are complete, the target has sufficient disk space, the intended binaries and data directories are being used, and the team knows who can approve a rollback.

After startup, review the server log and check that databases, roles, and extensions are present. Then test the application rather than relying only on a successful database start:

• Run representative reads, writes, transactions, and background jobs.
• Check connection counts, error rates, replication status, disk usage, and query performance.
• Refresh planner statistics as recommended for the chosen upgrade method; do not assume old statistics are adequate for the new cluster.
• Verify backups and monitoring against the new data directory and server version.

Compare important query plans and latency with the baseline. A major-version upgrade can change planner decisions even when the SQL and data are unchanged. Resolve performance regressions before declaring the migration complete.

Rollback: define the boundary before cutover

Rollback is easiest before the new cluster accepts writes. If validation fails at that point, stop the new service and return traffic to the unchanged old cluster, following the rehearsed procedure. Once users have written new data to the upgraded system, simply switching back can lose those writes or leave the systems inconsistent.

Therefore, define a rollback deadline and a write-handling policy in advance. Options include keeping a controlled maintenance window until checks pass, retaining a way to transfer post-cutover changes, or accepting a documented recovery point. Do not treat a backup as an instant reverse upgrade: restoring it takes time, and a newer-format data directory cannot generally be opened by an older PostgreSQL major version. Never point an old server at the upgraded data directory as a rollback shortcut.

Frequently Asked Questions

Do I need a backup before a PostgreSQL upgrade?
Yes. Keep a verified, restorable pre-upgrade backup and test the restore procedure. For production systems, make sure the backup and any required WAL cover the recovery point you need.

Does a major PostgreSQL upgrade upgrade extensions automatically?
Not necessarily. Check that each extension supports the target release, install compatible components, and follow its documented update procedure. Test extensions in a rehearsal environment.

Can physical replication upgrade PostgreSQL to a new major version?
Usually not as a direct cross-major stream from the old cluster. Consider a documented logical-replication migration or another supported method, and account for schema, sequence, and object differences.

Can I roll back after the new server has accepted writes?
Not safely by simply starting the old cluster. New writes must be preserved or reconciled, and the older server cannot generally read the upgraded data directory. Define a write-aware rollback plan before cutover.

Is pg_upgrade always faster than dump and restore?
It is often faster for large databases because it avoids logically copying every row, but actual time depends on storage, database size, options, and required post-upgrade work. Rehearse both if downtime is critical.

Related

• Persona 5 the phantom x vs persona 3 reload gameplay story and monetization compared?
• Spotting Self-Harm Encouragement in AI Chatbots
• OS X Yosemite vs Mavericks Icons Compared