pg_upgrade: Major PostgreSQL Version Upgrades with Minimal Downtime

Running PostgreSQL 13 in production while PostgreSQL 17 ships with query planner improvements, logical replication enhancements, and security fixes you desperately need? Major version upgrades in PostgreSQL are not as daunting as they once were — but they still require a disciplined approach to avoid data loss and extended downtime. The pg_upgrade utility ships with PostgreSQL itself and can migrate your cluster in minutes rather than hours, provided you understand its modes, limitations, and the gotchas that catch teams off guard. This guide walks through every stage of a production-safe major version upgrade, from pre-flight checks to rollback plans.

Why Upgrade PostgreSQL Major Versions?

PostgreSQL releases a new major version every year, and each major version is supported for five years. Once a version reaches end-of-life, it stops receiving security patches, bug fixes, and performance improvements. Beyond security, every major version ships meaningful capabilities:

• PostgreSQL 14 introduced pipeline mode for libpq, logical replication improvements, and range of multiranges.
• PostgreSQL 15 added MERGE support, improved logical replication conflict handling, and the pg_walinspect extension.
• PostgreSQL 16 brought parallel pg_dump, more flexible logical replication from standbys, and pg_stat_io for detailed I/O instrumentation.
• PostgreSQL 17 shipped a rewritten memory management system for VACUUM, incremental backup support, and further logical replication improvements.

Minor version upgrades (e.g., 16.1 to 16.4) only require replacing the binaries and restarting. Major version upgrades (e.g., 15 to 17) require migrating on-disk data structures because the system catalog format changes between major versions. This is where pg_upgrade earns its place in every DBA's toolkit.

Pre-Upgrade Checklist

Skipping pre-upgrade validation is the most common cause of failed or corrupted upgrades. Work through this checklist before touching a production system.

1. Take a Full Backup

Before anything else, take a verified backup using pg_basebackup, your WAL archiver, or a snapshot at the storage layer. Do not proceed without a backup you have tested restoring from.

pg_basebackup -h localhost -U replicator -D /var/lib/pgsql/backup-pre-upgrade \
  --wal-method=stream --progress --verbose

2. Install the New PostgreSQL Binaries

Install the target major version alongside the existing version. Both versions must coexist on the same host during the upgrade. On RPM-based systems:

# Example: upgrading to PostgreSQL 17 on RHEL/Rocky Linux
sudo dnf install -y postgresql17-server postgresql17-contrib

# Initialize a new empty cluster for the target version
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

The new cluster's data directory must be initialized but the service must not be started.

3. Check Extension Compatibility

Extensions compiled against the old major version are not compatible with the new one. List all installed extensions and verify packages exist for the target version.

-- Run against your existing cluster
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

# Verify extension packages are available for PostgreSQL 17
sudo dnf list available 'postgresql17-*'
# Install required extensions before running pg_upgrade
sudo dnf install -y postgresql17-contrib

4. Review Configuration Files

pg_upgrade does not migrate postgresql.conf or pg_hba.conf automatically. Parameters added, renamed, or removed between major versions will cause the new cluster to fail to start if stale configuration is blindly copied.

# Diff old and new sample configuration files
diff /var/lib/pgsql/15/data/postgresql.conf \
     /usr/pgsql-17/share/postgresql.conf.sample

Copy your pg_hba.conf and postgresql.conf to the new data directory after initialization, then validate by running the new binary with --config-file before the upgrade:

sudo -u postgres /usr/pgsql-17/bin/postgres \
  --config-file=/var/lib/pgsql/17/data/postgresql.conf \
  -C max_connections

5. Check Disk Space

In the default copy mode, pg_upgrade duplicates data files. Ensure you have at least as much free disk space as the size of your current data directory. Use du -sh /var/lib/pgsql/15/data to measure and df -h to check availability.

The pg_upgrade Process

Running pg_upgrade --check (Dry Run)

Always start with the --check flag. It validates both clusters for compatibility — checking extension versions, encoding, locale settings, and data directory contents — without modifying anything.

sudo -u postgres /usr/pgsql-17/bin/pg_upgrade \
  --old-datadir=/var/lib/pgsql/15/data \
  --new-datadir=/var/lib/pgsql/17/data \
  --old-bindir=/usr/pgsql-15/bin \
  --new-bindir=/usr/pgsql-17/bin \
  --check

Examine the output carefully. Common failures at this stage include encoding mismatches, missing extensions in the new cluster, and pg_hba.conf authentication problems that prevent pg_upgrade from connecting.

Full pg_upgrade Syntax Reference

sudo -u postgres /usr/pgsql-17/bin/pg_upgrade \
  --old-datadir=/var/lib/pgsql/15/data \
  --new-datadir=/var/lib/pgsql/17/data \
  --old-bindir=/usr/pgsql-15/bin \
  --new-bindir=/usr/pgsql-17/bin \
  --old-port=5432 \
  --new-port=5433 \
  --jobs=4

Key flags:

• --old-datadir / --new-datadir: Paths to the old and new cluster data directories.
• --old-bindir / --new-bindir: Paths to the PostgreSQL binary directories for each version.
• --jobs: Number of parallel processes for copying or linking files (set to the number of available CPU cores).
• --old-port / --new-port: Used when both clusters need to be running simultaneously during the process.

Upgrade Strategies: Copy, Link, and Clone

Default Copy Mode

Without additional flags, pg_upgrade copies all data files from the old cluster to the new one. This is the safest mode because the old cluster remains intact and fully usable for rollback. The downside is time and disk space proportional to database size.

--link Mode (Hardlinks)

sudo -u postgres /usr/pgsql-17/bin/pg_upgrade \
  --old-datadir=/var/lib/pgsql/15/data \
  --new-datadir=/var/lib/pgsql/17/data \
  --old-bindir=/usr/pgsql-15/bin \
  --new-bindir=/usr/pgsql-17/bin \
  --link \
  --jobs=4

In link mode, pg_upgrade creates hardlinks from the new data directory to the old data files rather than copying them. For a 500 GB database, this takes seconds instead of minutes. The critical tradeoff: once you start writing to the new cluster, the old cluster's data files are modified too (because they share the same inode), making rollback impossible without a prior backup.

--clone Mode (Copy-on-Write Reflinks)

sudo -u postgres /usr/pgsql-17/bin/pg_upgrade \
  --old-datadir=/var/lib/pgsql/15/data \
  --new-datadir=/var/lib/pgsql/17/data \
  --old-bindir=/usr/pgsql-15/bin \
  --new-bindir=/usr/pgsql-17/bin \
  --clone \
  --jobs=4

Clone mode uses filesystem-level copy-on-write reflinks (available on Linux with btrfs, or XFS with the reflink=1 mount option). It is nearly as fast as link mode but preserves the old files through copy-on-write semantics — the old cluster remains valid for rollback until writes diverge the two copies. This is the recommended mode when your filesystem supports it.

Upgrade Paths Comparison

Zero-Downtime Upgrade via Logical Replication

For systems that cannot tolerate more than a few seconds of write unavailability, logical replication offers a path to upgrade without an extended maintenance window.

The approach:

1. Stand up a new PostgreSQL 17 cluster (empty, or pre-loaded with the schema).
2. Create a publication on the old cluster covering all tables to be migrated.
3. Create a subscription on the new cluster pointing to the old cluster.
4. Wait for initial table sync to complete and replication lag to reach near-zero.
5. During a brief maintenance window, stop writes to the old cluster, verify lag is zero, update your application's connection string (or DNS/load balancer), and resume writes on the new cluster.

-- On the OLD cluster (PostgreSQL 15)
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- On the NEW cluster (PostgreSQL 17)
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-db port=5432 dbname=myapp user=replicator password=secret'
  PUBLICATION upgrade_pub;

-- Monitor replication lag
SELECT subname, received_lsn, latest_end_lsn,
       (latest_end_lsn - received_lsn) AS lag_bytes
FROM pg_stat_subscription;

Post-Upgrade Steps

Review pg_upgrade_internal.log

pg_upgrade writes detailed logs to the directory where it was invoked. Always review these before declaring success:

cat pg_upgrade_internal.log
cat pg_upgrade_server.log

Look for any WARNING or ERROR lines. The logs also record the exact SQL scripts generated and executed during catalog migration.

Start the New Cluster and Run ANALYZE

The new cluster has no query planner statistics — all table statistics from the old cluster are discarded because the statistics format changes between major versions. Running queries before rebuilding statistics risks catastrophically bad query plans.

# Start the new cluster
sudo systemctl start postgresql-17

# Rebuild statistics progressively (less I/O impact than a full ANALYZE)
sudo -u postgres vacuumdb --all --analyze-in-stages --jobs=4

# Or run a full ANALYZE if the cluster is not yet serving production traffic
sudo -u postgres vacuumdb --all --analyze --jobs=4

Run the Generated SQL Scripts

pg_upgrade generates two scripts in the working directory that must be run after the new cluster is confirmed healthy:

# Update pg_catalog entries for extensions
sudo -u postgres psql -f update_extensions.sql

# Delete old cluster data files (only after confirming the new cluster is stable)
# This script is generated only when NOT using --link mode
./delete_old_cluster.sh

Update Configuration and Validate

Review postgresql.conf on the new cluster for parameters that may have changed defaults or been removed between versions. Check pg_hba.conf if authentication methods changed (e.g., md5 deprecated in favor of scram-sha-256 in PostgreSQL 14+).

-- Verify extensions loaded correctly
SELECT name, installed_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

-- Check for invalid objects
SELECT schemaname, viewname FROM pg_views
WHERE definition ILIKE '%DEPRECATED%';

-- Confirm replication slots were preserved (if applicable)
SELECT slot_name, plugin, slot_type, active FROM pg_replication_slots;

Rollback Plan

The rollback strategy depends entirely on the upgrade mode used.

Copy mode rollback: Simply stop the new cluster, re-point your connection string or load balancer to the old cluster (which is still intact), and restart it. No data loss for writes that happened during the upgrade window since those writes went to the old cluster before it was stopped.

Clone mode rollback: Similar to copy mode — the old cluster's data directory is valid until the new cluster begins writing. After the new cluster starts, the copy-on-write snapshots diverge and the old cluster can only be used if it has not received writes since the cutover.

Link mode rollback: Requires restoring from the backup taken before the upgrade. There is no file-level rollback once the new cluster starts writing.

Logical replication rollback: The old cluster remains active and fully current (or near-current) throughout the sync phase. Rolling back is simply a matter of updating the connection string back to the old cluster and dropping the subscription on the new cluster. This is the most flexible rollback option.

Key Takeaways

Upgrade PostgreSQL Without the Toil

Major version upgrades are a routine part of running PostgreSQL in production, but the pre-flight checks, mode selection, post-upgrade validation, and rollback planning add up to significant operational overhead — especially when you are managing dozens of clusters across environments. JusDB handles major version upgrades for your PostgreSQL clusters with automated compatibility checks, zero-downtime logical replication migration paths, and point-in-time recovery so your rollback option is always available.

If you are planning a major PostgreSQL version upgrade and want a second pair of eyes on your runbook — or want to hand off the operational complexity entirely — talk to the JusDB team about managed PostgreSQL services.