When you are upgrading across major PostgreSQL versions, there are a few ways to go. Dump and restore is the simplest to reason about, but downtime scales directly with database size, so for anything multi-terabyte, it is off the table. Logical replication gets you near-zero downtime, but it only works from PostgreSQL 10 onward; if your source cluster is on less than version 10, that path does not exist in a native way. That leaves pg_upgrade, the community-maintained tool for in-place major version upgrades. With the –link flag, it creates hard links instead of copying data files, so the upgrade step itself stays fast, no matter how big the database is.

This post is based on an upgrade moving from 9.6 to 17 on Ubuntu using pg_upgrade. I will walk through each phase, flag the things that catch people off guard, and share the validation checks we run after the upgrade completes.

Phase 1: Install PostgreSQL 17 and Prepare the New Cluster

1.1 Install PostgreSQL 17 Binaries

sudo apt update
 sudo apt-cache show postgresql-17
 sudo apt install postgresql-17 postgresql-client-17 postgresql-contrib-17

 # Verify
 /usr/lib/postgresql/17/bin/psql --version

1.2 Create the New Data Directory

sudo mkdir -p /pgdata/17/<new_cluster_name>
 sudo chown -R postgres:postgres /pgdata/17/<new_cluster_name>/
 sudo chmod -R 700 /pgdata/17/<new_cluster_name>

1.3 Initialize the New Cluster

Initialize with the same locale as your existing cluster. Here we are using C.UTF-8.

sudo pg_createcluster 17 <new_cluster_name> \
   --datadir=/pgdata/17/<new_cluster_name> \
   --port=<new_port> \
   --locale=C.UTF-8 \
   --start

Phase 2: Configuration for PostgreSQL 17

Copy the relevant settings from your 9.6 postgresql.conf into the new cluster’s config, but do not blindly copy the whole file. Across the versions between 9.6 and 17, PostgreSQL removed or renamed a significant number of parameters; carrying any of them over will prevent the new cluster from starting. The table below lists every parameter that must be removed or substituted before running pg_upgrade.

Key settings to review and carry forward:

•       listen_addresses, max_connections
•       shared_buffers, work_mem, temp_buffers
•       wal_level, max_wal_size, min_wal_size
•       archive_mode, archive_command
•       max_wal_senders, max_replication_slots
•       statement_timeout, lock_timeout
•       Timezone settings

Parameters Removed or Renamed Between 9.6 and 17

Critical (PostgreSQL 12+): recovery.conf no longer exists as of PG 12. The server will refuse to start if this file is present. All recovery settings must be moved into postgresql.conf. Standby mode is now indicated by a standby.signal file in the data directory.

Notable Default Value Changes

• password_encryption: Default changed from md5 to scram-sha-256 in PG 14. Update pg_hba.conf auth methods accordingly and rotate passwords if clients support SCRAM.
• log_checkpoints: Default changed to “on” in PG 15. Increases log volume on quiet servers; set explicitly to avoid surprises.

Phase 3: Pre-Upgrade Steps

3.1 Remove Incompatible Extensions

Some extensions cannot survive a major version upgrade in-place and must be dropped before pg_upgrade runs. In the current setup, we have repmgr. If you leave it in place, the upgrade check will fail.

# Stop repmgrd first, then drop the extension
 sudo systemctl stop repmgrd
 sudo -u postgres psql -p <old_port> -d repmgr \
   -c "DROP EXTENSION repmgr CASCADE;"

After the upgrade, repmgr can be reinstalled and reconfigured against the new cluster.

3.2 Stop Both Clusters

pg_upgrade requires both clusters to be fully stopped, not just the old one. If the new cluster is still running from the initialization step, stop it now. Make sure all application traffic pointing at the old cluster is halted as well.

sudo pg_ctlcluster 9.6 <old_cluster_name> stop
sudo pg_ctlcluster 17 <new_cluster_name> stop

3.3 Copy pg_hba.conf

sudo cp /etc/postgresql/9.6/<old_cluster_name>/pg_hba.conf \
         /etc/postgresql/17/<new_cluster_name>/pg_hba.conf

Phase 4: Running pg_upgrade

4.1 Dry Run (–check)

Always run the check mode first. It validates compatibility without touching any data. Do not skip this step; it is fast, and it catches configuration issues before your maintenance window begins.

sudo su - postgres
 cd /tmp/

 /usr/bin/time -f "Check time: %E" \
   /usr/lib/postgresql/17/bin/pg_upgrade \
   --old-datadir=/pgdata/9.6/<old_cluster_name> \
   --new-datadir=/pgdata/17/<new_cluster_name> \
   --old-bindir=/usr/lib/postgresql/9.6/bin \
   --new-bindir=/usr/lib/postgresql/17/bin \
   --check

If the output ends with “Clusters are compatible”, you are clear to proceed.

4.2 Run the Upgrade

The –link flag uses hard links instead of copying files, which makes the upgrade significantly faster for large clusters. The –jobs flag parallelizes the catalog conversion work across multiple CPU cores.

/usr/bin/time -f "Upgrade time: %E" \
   /usr/lib/postgresql/17/bin/pg_upgrade \
   --old-datadir=/pgdata/9.6/<old_cluster_name> \
   --new-datadir=/pgdata/17/<new_cluster_name> \
   --old-bindir=/usr/lib/postgresql/9.6/bin \
   --new-bindir=/usr/lib/postgresql/17/bin \
   --jobs=8 \
   --link

Note: Because hard links point to the same underlying data blocks, the old cluster’s data directory is considered tainted after the upgrade completes. Do not start the old cluster again. If you need a rollback path, take a filesystem snapshot before running the upgrade. Also, Set –jobs based on the number of available CPU cores, but leave some headroom if the system is also serving live traffic during the cutover window.

4.3 Debian/Ubuntu Alternative: Using pg_upgradecluster

If you’re using PostgreSQL packages from Debian-based distributions (such as Debian or Ubuntu), you can simplify the upgrade process by using the pg_upgradecluster utility provided by the distribution.

To perform an in-place upgrade using hard links:

pg_upgradecluster -m link <old_version> <cluster_name>

The -m link option performs a hard-link upgrade similar to pg_upgrade --link, significantly reducing both upgrade time and disk space requirements.

If your filesystem supports reflinks (such as Btrfs or reflink-enabled XFS on Linux), you can instead use:

pg_upgradecluster -m clone <old_version> <cluster_name>

The -m clone option creates copy-on-write clones instead of hard links. This provides performance comparable to a hard-link upgrade while preserving the original PostgreSQL data directory, making rollback easier if needed.

Note:pg_upgradecluster is available only on Debian-based distributions. The -m clone option requires filesystem support for reflinks. The standard pg_upgrade workflow described in this guide remains the recommended approach for environments using PostgreSQL community packages or other Linux distributions.

Phase 5: Post-Upgrade Tasks

5.1 Start the New Cluster

sudo pg_ctlcluster 17 <new_cluster_name> start

5.2 Regenerate optimizer statistics

In PostgreSQL 14 and later, it no longer generates an analyze_new_cluster.sh script. pg_upgrade now prints the command directly and expects you to run it yourself. Run this before routing any traffic to the new cluster; without fresh statistics, the query planner will make poor decisions.

sudo su - postgres
PGPORT=<new_port> /usr/bin/time -f "Analyze time: %E" vacuumdb --all --analyze-in-stages

If a specific table fails due to a timeout, run it manually:

sudo -u postgres psql -p <new_port> -d <your_database> -c "
 SET statement_timeout = 0;
 ANALYZE VERBOSE <schema>.<table_name>;"

Note 1: This applies to PG14 through PG17. Starting with PostgreSQL 18, optimizer statistics transfer automatically during the upgrade, so this step is no longer needed for that purpose. 

Note 2: A full reindex isn’t required for every upgrade. Hash indexes specifically must be rebuilt after upgrading from any version before 10; pg_upgrade will generate a script flagging any it finds. Using concurrently to rebuild index in that case is the safer side. 

Phase 6: Post-Upgrade Validation

Before routing any application traffic to the new cluster, run through these integrity checks. These are the queries I use after every major version upgrade.

Check the version

SELECT version();

Check for invalid indexes

This query lists any indexes left in an invalid state that failed or was interrupted, or a partial rebuild during the upgrade itself. An invalid index is silently skipped by the planner, so queries still run but without the performance benefit you expect. This should return zero rows. Any invalid index needs to be rebuilt before going live.

SELECT s.schemaname, s.relname AS tablename, s.indexrelname AS indexname
 FROM pg_stat_user_indexes s
 JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE i.indisvalid = false;

Check extension versions

pg_upgrade carries over your installed extensions, but it doesn’t upgrade them to match what’s bundled with the new PostgreSQL binaries. This query compares what’s installed against what’s available and flags any mismatch. Look for anything marked NEEDS UPDATE and upgrade those extensions (ALTER EXTENSION … UPDATE) before proceeding.

SELECT name, default_version, installed_version,
   CASE WHEN default_version != installed_version
     THEN '*** NEEDS UPDATE ***'
     ELSE 'OK'
   END AS status
 FROM pg_available_extensions
 WHERE installed_version IS NOT NULL
 ORDER BY name;

Check the logs

tail -f /var/log/postgresql/postgresql-17-<new_cluster_name>.log

Phase 7: Cleanup

Once the new cluster has been stable for a defined retention period, remove the old cluster to reclaim disk space.

./delete_old_cluster.sh

Or manually:

sudo pg_dropcluster 9.6 <old_cluster_name> --stop
sudo rm -rf /pgdata/9.6/<old_cluster_name>/

Wrapping Up

The core pg_upgrade step with –link is fast for most clusters; you are looking at single-digit minutes of actual downtime regardless of database size. The work is really in the preparation: cleaning up incompatible extensions, getting the configuration right, testing application compatibility and making sure you have proper backup before you begin.

The analyze and reindex phases are what take time post-upgrade. Run them before routing application traffic; especially the analyze, a query planner with stale statistics will cause real problems. Once those are done and your validation queries come back clean, you are good to go.