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.

Phase 5: Post-Upgrade Tasks

5.1 Start the New Cluster

sudo pg_ctlcluster 17 <new_cluster_name> start

5.2 Run analyze_new_cluster.sh

pg_upgrade generates a helper script to rebuild optimizer statistics, which are not transferred during the upgrade. Run this before routing any traffic to the new cluster as without fresh statistics, the query planner will make poor decisions.

sudo su - postgres
 PGPORT=<new_port> /usr/bin/time -f "Analyze time: %E" ./analyze_new_cluster.sh

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 step is specific to pg_upgrade’s behavior through PG17. Starting with PostgreSQL 18 optimizer statistics (table and column-level stats from pg_stats) transfer automatically during the upgrade; analyze_new_cluster.sh 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.