Upgrade PostgreSQL with pg_upgrade

Upgrading a production PostgreSQL cluster is often the moment when seasoned DBAs feel their stomach drop. Between incompatible data directory layouts, binary version checks, and the dread of a multi-day dump-and-restore window, it's easy to postpone the inevitable. But there's a better way: pg_upgrade, the built-in tool that can migrate your entire cluster in minutes, not hours, with minimal downtime. In this lesson, you'll learn exactly how to upgrade PostgreSQL with pg_upgrade — from understanding the underlying mechanism to executing a safe, verified migration. By the end, you'll have the confidence to upgrade your own clusters without breaking a sweat.

The problem this lesson solves

Every major PostgreSQL release changes the on-disk format of the data directory. That means you cannot simply swap out the old binaries for new ones and expect your data to be readable. The traditional solution — pg_dump and pg_restore — is reliable but painfully slow. For large databases, the dump can take hours, the restore even longer, and the entire process requires a lengthy maintenance window where your application is down or read-only.

The pain points are real:

• Long downtime: Dumping and restoring a multi-terabyte database can take a full day or more.
• Risk of failure: A hiccup mid-restore can leave your database in an inconsistent state.
• Resource strain: Both dump and restore are CPU and I/O intensive, competing with your production workload.
• Post-verification nightmare: You have to manually compare row counts and schema definitions to feel confident.

pg_upgrade solves all of this by physically copying the data files from the old cluster to the new one, rather than recreating the data from SQL statements. This makes the upgrade dramatically faster and less risky — often completing in minutes for clusters that would take hours to dump and restore.

Core concept / mental model

Think of a PostgreSQL data directory as a warehouse of files. Each table, index, and sequence is a set of files on disk, organized in a specific subdirectory structure. Different PostgreSQL versions arrange these files differently — they might use different data types for page headers, different layout for pg_control, or new catalog columns.

pg_upgrade works like a moving crew that packs and relabels boxes before transporting them. It doesn't recreate the contents; it understands the structural differences between the old and new warehouse, transforms the necessary metadata, and then physically moves the files to the new location. Only the parts that must change — like the system catalogs and pg_control — are rewritten.

Key definitions

• Old cluster: The existing PostgreSQL instance and its data directory (e.g., version 14).
• New cluster: A freshly initialized instance of the target version (e.g., version 16) with an empty data directory.
• Bin directory: The location of the PostgreSQL executables, typically /usr/lib/postgresql/<version>/bin or /usr/pgsql-<version>/bin.
• Data directory: The folder containing all database files, often /var/lib/postgresql/<version>/main or /var/lib/pgsql/<version>/data.
• Compatibility check: pg_upgrade performs a suite of checks to ensure the old cluster can be migrated safely.

Why not just copy the data directory?

You might wonder: why not simply stop the old server and copy its data directory to the new installation? Because the file formats are incompatible. The new PostgreSQL binaries would try to read the old files and fail — likely with confusing errors about "invalid page header" or "could not read block".

pg_upgrade bridges that gap by performing a two-phase migration: 1. Analyze and transform: It connects to both clusters, examines the old catalog, and creates a translated version of the new cluster's system catalogs. 2. File copy: It copies the user data files (tables, indexes) byte-for-byte, only adjusting the file paths and permissions.

The result is a new cluster that contains all your data, but in the native format of the target version.

How it works step by step

To successfully upgrade with pg_upgrade, you must follow a precise sequence:

1. Plan and prepare the environment

• Choose your target version: Check the PostgreSQL versioning policy. Each major version is supported for 5 years, so plan to be on a version that's still supported.
• Check OS compatibility: Ensure your operating system supports the target version. For example, PostgreSQL 16 requires a 64-bit platform.
• Allocate disk space: pg_upgrade needs space for both clusters during the migration, plus temporary space for logs and scripts.

2. Stop the old cluster cleanly

• Use pg_ctl or your service manager to stop the old server gracefully.
• Verify that no connections are active: pg_isready should return "no response".

3. Initialize the new cluster

The new cluster must be created with the same locale and encoding as the old one. Use initdb with the appropriate flags:

initdb -D /data/newcluster -U postgres --encoding=UTF8 --locale=en_US.UTF-8

4. Run pg_upgrade

• Use the --old-bindir, --new-bindir, --old-datadir, and --new-datadir flags.
• Output a log file to track progress and errors.

5. Start and validate the new cluster

• Start the new server, then run analyze_new_cluster.sh to update planner statistics.
• Run your own sanity checks: SELECT count(*) on key tables, test a few queries.
• If satisfied, run delete_old_cluster.sh to remove the old data directory.

6. Update connection settings

• Change your application's connection string to point to the new port (if it changed).
• Update any monitoring or backup scripts.

Hands-on walkthrough

Now let's put the theory into practice. We'll upgrade a PostgreSQL 14 cluster to PostgreSQL 16 on a Linux system.

Prerequisites

• Both PostgreSQL 14 and 16 binaries installed (e.g., via apt or from the official repos).
• Your old cluster is running (or stopped) and you have postgres OS user permissions.
• Enough disk space for the new cluster copy.

Step 1: Install PostgreSQL 16

On Ubuntu/Debian:

sudo apt update
sudo apt install postgresql-16

On RHEL/CentOS with the PGDG repo:

sudo dnf install postgresql16-server

Step 2: Stop the old cluster

sudo systemctl stop postgresql@14-main

Step 3: Initialize the new cluster

Create a new data directory and initialize it with matching locale settings:

sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main \
  --encoding=UTF8 --locale=en_US.UTF-8

Pro tip: Check your old cluster's locale with SHOW lc_collate; and SHOW lc_ctype; before running initdb. Mismatches are a common cause of pg_upgrade failures.

Step 4: Run pg_upgrade

Now the main event:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-options='-c config_file=/etc/postgresql/14/main/postgresql.conf' \
  --new-options='-c config_file=/etc/postgresql/16/main/postgresql.conf' \
  --link --logfile=/tmp/pg_upgrade.log

Let's break down the flags:

• --link: Use hard links to copy files instantly instead of actually copying data. This is a game-changer for speed, but you must keep the old cluster until the upgrade is verified.
• --logfile: Redirect all diagnostics to a file for easier debugging.

Step 5: Start the new cluster and verify

sudo systemctl start postgresql@16-main
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -c "SELECT version();"

Run the generated analysis script to refresh statistics:

sudo -u postgres ./analyze_new_cluster.sh

Now perform your own smoke tests:

psql -p 5433 -d mydb -c "SELECT count(*) FROM users;"
psql -p 5433 -d mydb -c "\dt+"  # list tables with sizes

Step 6: Finalize

If everything looks good, remove the old cluster:

sudo -u postgres ./delete_old_cluster.sh

Update your application to use port 5433 (or whichever port the new cluster listens on).

Full example script

For repeatability, here's a complete bash script you can adapt:

#!/bin/bash
set -euo pipefail

OLD_VERSION=14
NEW_VERSION=16
OLD_BIN=/usr/lib/postgresql/14/bin
NEW_BIN=/usr/lib/postgresql/16/bin
OLD_DATA=/var/lib/postgresql/14/main
NEW_DATA=/var/lib/postgresql/16/main
LOG=/tmp/pg_upgrade.log

# Stop old cluster
sudo systemctl stop postgresql@${OLD_VERSION}-main

# Init new cluster (assuming old locale = en_US.UTF-8)
sudo -u postgres $NEW_BIN/initdb -D $NEW_DATA --encoding=UTF8 --locale=en_US.UTF-8

# Run pg_upgrade
sudo -u postgres $NEW_BIN/pg_upgrade \
  --old-bindir=$OLD_BIN \
  --new-bindir=$NEW_BIN \
  --old-datadir=$OLD_DATA \
  --new-datadir=$NEW_DATA \
  --link --logfile=$LOG

# Start new cluster
sudo systemctl start postgresql@${NEW_VERSION}-main

# Analyze
sudo -u postgres ./analyze_new_cluster.sh

# Optional: remove old cluster
# sudo -u postgres ./delete_old_cluster.sh
echo "Upgrade complete. Verify with psql -p ${NEW_VERSION}23..."

Expected output: The script will produce a series of progress messages and end with a success note. The pg_upgrade.log will show "Upgrade Complete" at the end.

Compare options / when to choose what

pg_upgrade is not the only way to upgrade. Let's compare it with the alternatives:

When to choose pg_upgrade

• Your database is larger than a few GB.
• You can afford a short downtime window (minutes).
• You want a simple, testable procedure.

When to consider alternatives

• Logical replication (using tools like pglogical or pgl_delta) if you need near-zero downtime and can handle schema changes.
• Dump/restore if you're also reorganizing your schema or need to move to a different platform (e.g., from Windows to Linux).

Variations of pg_upgrade

• --link mode (hard links): Fastest but requires keeping the old cluster until verification.
• --copy mode (default): Copies files, uses more disk space but allows deleting the old cluster immediately.
• --check mode: Runs all validation checks without performing the upgrade. Great for testing.

Troubleshooting & edge cases

The most common pitfalls—and how to fix them.

Error: "could not load library ..." or "incompatible library" / "extension is not allowed"

If you use extensions like postgis, pg_trgm, or plpgsql, the new cluster must have matching versions of those extensions. Solution: Install the same extension packages for the new PostgreSQL version and include --old-options and --new-options to point to the right postgresql.conf (which often loads shared libraries).

# On Debian/Ubuntu
sudo apt install postgresql-16-postgis-3

Then rerun pg_upgrade. You may need to manually adjust shared_preload_libraries in the new cluster's config.

Error: "could not determine the old data checksum version" or "old and new cluster datadirs are different"

This is often due to using initdb with different checksum settings or different data directory ownership. Ensure you run initdb as the same OS user (postgres) and with the same data-checksums setting as the old cluster. Check your old config with:

cat /etc/postgresql/14/main/postgresql.conf | grep checksum

If checksums were enabled, re-initialize the new cluster with --data-checksums.

Error: "could not access file ..." / permission issues

Ensure both data directories are owned by the postgres user and that the directories have correct permissions (700). Use chown -R postgres:postgres if needed.

Error: "the new cluster cannot be started" or "old cluster is still running"

Make sure the old server is fully stopped. Use pg_ctl status or check with pg_isready. If using systemd, disable the old service temporarily to prevent automatic start.

Diagnosing with --check

Before the real run, always execute pg_upgrade --check with the same flags to validate prerequisites:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --check

This will flag missing extensions, locale mismatches, or incompatible libraries before you risk anything.

Edge case: Upgrading across more than one major version

pg_upgrade supports upgrades from the two previous major versions (e.g., from 15 or 16 to 17). If you're on version 13 and want to go to 16, you must do two upgrades: 13 → 14 → 16, or use dump/restore.

Edge case: Custom tablespaces

If your database uses custom tablespaces, ensure those directories exist and are writable by the postgres user. pg_upgrade will copy files to the new tablespace paths.

What you learned & what's next

You now understand the core mechanism behind pg_upgrade: it avoids slow SQL-level dumps by physically copying data files after a metadata transformation. You can plan and execute a safe upgrade by checking prerequisites, initializing the new cluster, running the upgrade, and verifying the result. You also learned how to choose between pg_upgrade, dump/restore, and logical replication based on downtime and complexity requirements.

You've met the learning objectives: you can explain the core idea behind pg_upgrade and you've completed a practical exercise to upgrade a cluster.

Next in the track: Now that you can upgrade seamlessly, the next lesson likely covers PostgreSQL backup and recovery strategies — essential for protecting your upgraded cluster. Look forward to learning about pg_basebackup, WAL archiving, and point-in-time recovery.

Final pro tip: Always test your upgrade on a staging clone first. Cloning production data is a great way to practice pg_upgrade without risk, and it builds your confidence for the real thing.