Upgrade PostgreSQL to 18 with pg_upgrade: Step-by-Step Runbook

To upgrade a self-managed PostgreSQL cluster to version 18, install the 18 binaries, create an empty 18 cluster with initdb using the same encoding, locale and checksum setting as the old cluster, run pg_upgrade --check until it passes, stop the old server, run pg_upgrade in copy, link, clone or swap mode, start the new server and finish with vacuumdb --all --analyze-in-stages --missing-stats-only. On Azure Database for PostgreSQL flexible server you run the built-in upgrade validation checks and then the in-place upgrade from the portal or with az postgres flexible-server upgrade --version 18. Both paths use pg_upgrade under the hood, so the same preparation applies.

Who this is for and what you will have at the end

This runbook is for DBAs and Linux administrators upgrading PostgreSQL 13, 14, 15, 16 or 17 to PostgreSQL 18 on their own servers, and for teams running Azure Database for PostgreSQL flexible server who need to plan the in-place upgrade. It assumes you can stop the database for a maintenance window. If you need near-zero downtime instead, use logical replication from the old version to a new 18 server; the PostgreSQL logical replication guide covers that setup.

At the end you will have:

• A pre-upgrade inventory that catches the PostgreSQL 18 changes that break upgrades.
• A rehearsed pg_upgrade --check and a chosen transfer mode with a known rollback path.
• A running PostgreSQL 18 cluster with statistics in place and standbys handled.
• The equivalent procedure for Azure Database for PostgreSQL flexible server.

Choose the upgrade path

pg_upgrade supports upgrades from 9.2 and later, and it isn't needed for minor upgrades such as 17.4 to 17.6.

PostgreSQL 18 changes that affect the upgrade

Read these before you touch a production cluster. Several of them change what initdb and pg_upgrade do.

• Data checksums are on by default. initdb now enables checksums unless you pass --no-data-checksums, and pg_upgrade requires matching checksum settings. Most clusters created on older versions don't have checksums, so this is the most common new failure.
• Optimizer statistics are preserved. pg_upgrade now transfers most planner statistics. Extended statistics are not preserved, and you can turn the transfer off with --no-statistics.
• New --swap mode and parallel checks. --jobs now also parallelizes the database checks.
• char signedness is recorded per cluster. pg_upgrade keeps the existing setting, and when upgrading from 17 or earlier it adopts the signedness of the platform pg_upgrade was built on. If you plan to move between x86 and ARM, upgrade on the original platform first, then migrate.
• MD5 passwords are deprecated. CREATE ROLE and ALTER ROLE emit warnings when setting MD5 passwords. Plan a move to SCRAM.
• VACUUM and ANALYZE now process inheritance children of a parent table by default. Use the new ONLY option for the old behavior.
• Full-text search uses the cluster's default collation provider. For clusters that default to ICU or the builtin provider, the release notes recommend reindexing full-text search and pg_trgm indexes after a pg_upgrade.
• Primary and foreign keys must use deterministic collations or the same nondeterministic collation. The schema restore inside pg_upgrade fails otherwise.

Prerequisites

• A current, restorable backup of the old cluster. If you use pgBackRest or a similar tool, confirm the last backup and WAL archive before the window.
• PostgreSQL 18 server binaries installed alongside the old version.
• PostgreSQL 18 builds of every extension that has a shared library in the old cluster (for example PostGIS or pg_partman packages for 18).
• Shell access as the operating system user that owns the data directory, usually postgres.
• Enough free disk space for the chosen mode, and a working directory that only the postgres user can read and write, because pg_upgrade creates its temporary Unix sockets in the current directory by default.

Step 1: Record the old cluster's settings

Connect to the old cluster and capture the values the new cluster must match.

psql -c "SELECT version();"
psql -c "SHOW data_checksums;"
psql -c "SHOW server_encoding;"
psql -c "SELECT datname, datcollate, datctype, datlocprovider FROM pg_database;"
psql -c "SELECT extname, extversion FROM pg_extension;"

Also copy postgresql.conf, any included files, postgresql.auto.conf and pg_hba.conf somewhere safe. pg_upgrade doesn't carry configuration across, and you will reapply these settings to the new cluster.

Step 2: Install PostgreSQL 18

On Ubuntu with the PostgreSQL Global Development Group (PGDG) repository:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
sudo apt install postgresql-18

Install the matching extension packages for version 18 at the same time. Then confirm where the new binaries live:

pg_config --bindir --version

Run that with the full path of each version's pg_config if both are installed, and set four variables for the rest of the runbook:

export OLD_BIN=/path/to/17/bin
export NEW_BIN=/path/to/18/bin
export OLD_DATA=/path/to/17/data
export NEW_DATA=/path/to/18/data

Step 3: Initialize the new cluster with matching settings

Create the 18 cluster as the same operating system user and with the same bootstrap superuser, encoding and locale as the old one. If Step 1 showed data_checksums as off, add --no-data-checksums.

sudo -iu postgres
"$NEW_BIN/initdb" -D "$NEW_DATA" \
  --username=postgres \
  --encoding=UTF8 \
  --locale=en_US.UTF-8 \
  --no-data-checksums

Don't start this cluster and don't run CREATE EXTENSION in it. pg_upgrade restores the schema, including extension definitions, from the old cluster.

On Debian and Ubuntu, the postgresql-common tooling offers pg_upgradecluster, which wraps these steps and copies the configuration files for you, for example pg_upgradecluster -v 18 -m upgrade --jobs 4 17 main (or -m link, -m clone). Its default method is dump, so pass -m explicitly. The upgraded cluster keeps the old cluster's name, so if an empty 18/main cluster already exists from the package installation, drop it first with pg_dropcluster --stop 18 main.

Step 4: Run pg_upgrade --check

Run the check with the new version's pg_upgrade and the same mode you will use for the real run, so you get mode-specific checks. It works while the old server is still running.

cd ~
"$NEW_BIN/pg_upgrade" \
  --old-bindir="$OLD_BIN" \
  --new-bindir="$NEW_BIN" \
  --old-datadir="$OLD_DATA" \
  --new-datadir="$NEW_DATA" \
  --link \
  --jobs=4 \
  --check

pg_upgrade's default port for both clusters is 50432. When you check against a running old server, pass --old-port with the port it is listening on (for example 5432); during a live check the two ports must differ. Fix everything the check reports and rerun until it passes. Typical items are missing extension libraries for 18, tables using unsupported reg* column types such as regproc, and the checksum mismatch described above.

Plan for authentication: pg_upgrade connects to both clusters many times, so use peer authentication for the local socket or a ~/.pgpass file rather than typing passwords.

Step 5: Stop applications and the old server

Stop application traffic, then shut down cleanly. If you have streaming replicas that you plan to upgrade with rsync, keep them running while you stop the primary so they receive all changes, then stop them.

"$OLD_BIN/pg_ctl" -D "$OLD_DATA" stop

If the server is managed by systemd, stop it through the service unit instead so the service manager doesn't restart it.

Step 6: Run pg_upgrade

Run the same command without --check:

"$NEW_BIN/pg_upgrade" \
  --old-bindir="$OLD_BIN" \
  --new-bindir="$NEW_BIN" \
  --old-datadir="$OLD_DATA" \
  --new-datadir="$NEW_DATA" \
  --link \
  --jobs=4

Start with --jobs equal to the number of CPU cores. Working files and logs go to pg_upgrade_output.d inside the new data directory and are removed on success; add --retain if you want to keep them. If the schema restore fails, revert to the old cluster as described in the rollback section, fix the cause in the old cluster and try again.

Step 7: Apply configuration and start PostgreSQL 18

Reapply your pg_hba.conf and the settings from the old postgresql.conf and postgresql.auto.conf, reviewing any that were renamed or removed in the versions you skipped. Then start the new server:

"$NEW_BIN/pg_ctl" -D "$NEW_DATA" -l ~/pg18-start.log start

Step 8: Post-upgrade processing

pg_upgrade prints the names of any scripts you need to run, for example to update extensions. Run each one:

psql --username=postgres --file=script.sql postgres

Tables referenced by rebuild scripts are unsafe to use until the scripts finish. Then fill in statistics that weren't transferred, followed by a full analyze:

vacuumdb --all --analyze-in-stages --missing-stats-only --jobs=4
vacuumdb --all --analyze-only --jobs=4

--missing-stats-only is new in 18, requires a superuser, and only works with --analyze-only or --analyze-in-stages. If you use extended statistics, the second command rebuilds them. If your cluster defaults to ICU or the builtin collation provider, reindex full-text search and pg_trgm indexes now.

Step 9: Upgrade or rebuild standbys

The rsync method only applies to link mode. Install the 18 binaries and extension libraries on each standby, make sure the new standby data directory is empty, save its configuration files, then run rsync on the primary from the directory above the old and new data directories:

rsync --archive --delete --hard-links --size-only --no-inc-recursive \
  /opt/PostgreSQL/17 /opt/PostgreSQL/18 standby01.contoso.com:/opt/PostgreSQL

Verify beforehand that pg_controldata shows the same "Latest checkpoint location" on the old primary and standbys, and that wal_level isn't set to minimal on the new primary. Physical replication slots are not migrated, so recreate them. For any other mode, rebuild the standbys from a fresh base backup of the upgraded primary. In a Patroni cluster, follow Patroni's own major-upgrade procedure; the PostgreSQL high availability with Patroni guide describes the cluster layout this applies to.

Step 10: Remove the old cluster

After the new cluster has run successfully and a fresh backup of it exists, delete the old cluster using the script pg_upgrade mentions at the end of its run, then remove the old binaries.

Azure Database for PostgreSQL flexible server

Flexible server supports PostgreSQL 18 and performs in-place major version upgrades with pg_upgrade, keeping the server name and connection strings. You can skip versions, for example 14 straight to 18.

Before the upgrade:

• Restore a point-in-time copy of production to a new server and rehearse the upgrade there. The upgrade is irreversible.
• Delete read replicas, including cascading replicas, and re-create them afterwards.
• Leave 10 to 20 percent of storage free.
• Drop extensions that block the upgrade. session_variable, anon and age block every path; pg_repack, hypopg and pg_partman must be dropped and re-created by design. For a target of 18, azure_ai, azure_storage, azure_local_ai, pg_diskann, pg_failover_slots and pgrouting are also blocked.
• Drop event triggers and recreate them afterwards.
• If you upgrade from PostgreSQL 11, switch to SCRAM authentication and reset role passwords first.
• Enable server logs (logfiles.download_enable) so you can download the pg_upgrade logs.

Run the validation checks to get the authoritative list of blockers for your exact path. In the portal: Overview > Upgrade, choose the target version, set Action to Validate only, and select Start. With Azure CLI 2.89.0 or later:

az postgres flexible-server upgrade \
  --resource-group rg-data \
  --name pg-contoso-prod \
  --version 18 \
  --validate-only

Run the upgrade with Action set to Validate and upgrade in the same pane, or:

az postgres flexible-server upgrade \
  --resource-group rg-data \
  --name pg-contoso-prod \
  --version 18

If high availability is enabled, the service disables it, upgrades the primary, and re-enables it, which needs capacity for a new standby and network rules that allow ports 5432 and 6432 within the virtual network. Afterwards, run ANALYZE in each database as Microsoft recommends.

Verification

SELECT version();
SHOW data_checksums;
SELECT extname, extversion FROM pg_extension ORDER BY extname;
SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname
LIMIT 20;

Compare extension versions against Step 1, run your application smoke tests, and check that backups and WAL archiving work against the new cluster, because the backup tool usually needs a new stanza or configuration for the new version.

Rollback

Troubleshooting

The check reports a checksum mismatch. The new cluster was created with PostgreSQL 18's default of checksums on. Delete the new data directory, run initdb again with --no-data-checksums, and repeat the check.

The check reports missing libraries. An extension's shared library for version 18 isn't installed. Install the 18 build of the extension package; don't create the extension in the new cluster.

The schema restore fails on a collation error. PostgreSQL 18 requires primary and foreign keys to use deterministic collations or the same nondeterministic collation. Fix the schema in the old cluster, then rerun.

Connection or socket path errors. pg_upgrade uses the current directory for its socket by default. If the path is too long, pass --socketdir with a directory that other users can't read or write.

Azure upgrade blocked. Download the validation report or the pg_upgrade logs from Server logs, drop the reported extensions, event triggers or logical replication slots, and rerun validation. Views that depend on pg_stat_activity also block the upgrade.

Queries are slow right after the upgrade. Extended statistics weren't carried over. Run the vacuumdb commands from Step 8.

Checklist

• Backup verified and WAL archive current.
• Old cluster settings recorded: checksums, encoding, locale, extensions, configuration files.
• PostgreSQL 18 binaries and extension packages installed.
• New cluster initialized with matching settings, --no-data-checksums if needed.
• pg_upgrade --check passes with the chosen mode.
• Rollback path for that mode written into the change plan.
• Upgrade run, configuration reapplied, server started.
• Generated scripts run, vacuumdb --missing-stats-only and --analyze-only complete.
• Standbys upgraded with rsync (link mode) or rebuilt.
• Backups reconfigured and tested on the new version; old cluster removed.

References

• pg_upgrade (PostgreSQL 18)
• PostgreSQL 18 release notes
• initdb (PostgreSQL 18)
• pg_config (PostgreSQL 18)
• Preset options (PostgreSQL 18)
• PostgreSQL downloads for Ubuntu
• pg_upgradecluster manual page
• pg_dropcluster manual page
• Major version upgrades in Azure Database for PostgreSQL
• Perform a major version upgrade in Azure Database for PostgreSQL
• Upgrade validation checks in Azure Database for PostgreSQL