PostgreSQL Major Version Upgrade Planner

Choosing how to run a PostgreSQL major version upgrade comes down to three numbers: how much data you have, how much downtime you can afford, and how much spare disk is on the box. Enter those and this planner recommends pg_upgrade in link or copy mode, a dump and restore, or a logical replication cutover, with a downtime estimate for each so you can see why. It then generates the step-by-step commands for the method you picked, the preflight SQL to run on the old cluster while it is still serving traffic, and the list of breaking changes you cross on the way from your current version to your target. Everything is computed in your browser — no server details are sent anywhere.

Versions and platform

Current versionTarget versionPlatformMethod

Size and window

Data size (GB)Free disk (GB)Downtime budget (min)Restore speed (GB/min)File copy speed (GB/min)

Environment

Physical standbys?Many large objects?Extensions in useData directory root

Recommendation

PostgreSQL 13  ->  17
Recommended method:  pg_upgrade --link
Estimated downtime:  4 min   (your budget: 30 min)

How the options compare for your data size:
  pg_upgrade --link      ~ 4 min   needs almost no extra disk, no rollback without a backup
  pg_upgrade (copy)      ~ 70 min   needs a second copy of the data, old cluster stays intact
  pg_dump / pg_restore   ~ 5.6 h   slowest, but rebuilds everything cleanly and can cross architectures
  logical replication    ~ 5 min   only the cutover is downtime; setup runs for hours or days beforehand

These are rough estimates from the throughput numbers you entered. The only number that
matters is the one from a rehearsal on a restored copy of production — run it twice.

Command plan

# ---------------------------------------------------------------
# Phase 1 — before the maintenance window (no downtime)
# ---------------------------------------------------------------
# Take a backup and verify you can restore it. This is your only rollback.
pg_basebackup -D /backup/pre-upgrade -Ft -z -P -X stream

# Clear out anything that blocks the upgrade while the server is still live.
psql -c "SELECT * FROM pg_prepared_xacts;"          -- must be empty
psql -c "SELECT slot_name, active FROM pg_replication_slots;"
vacuumdb --all --analyze-only --jobs 4

# ---------------------------------------------------------------
# pg_upgrade --link
# ---------------------------------------------------------------
# Install the 17 server packages AND every extension you use, built for 17.
apt-get install -y postgresql-17 postgresql-17-postgis-3   # adjust to your extensions

# Initialise the new cluster. Checksum settings must match the old cluster.
/usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/main --data-checksums
# Check the old cluster's setting first:  SHOW data_checksums;

# Dry run — this is safe to run while the old cluster is still serving traffic.
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/13/bin --new-bindir=/usr/lib/postgresql/17/bin \
  --old-datadir=/var/lib/postgresql/13/main --new-datadir=/var/lib/postgresql/17/main \
  --check

# --- downtime starts here ---
pg_ctlcluster 13 main stop
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/13/bin --new-bindir=/usr/lib/postgresql/17/bin \
  --old-datadir=/var/lib/postgresql/13/main --new-datadir=/var/lib/postgresql/17/main \
  --link

# Copy your tuned settings across, then start the new cluster.
diff /var/lib/postgresql/13/main/postgresql.conf /var/lib/postgresql/17/main/postgresql.conf
cp /var/lib/postgresql/13/main/pg_hba.conf /var/lib/postgresql/17/main/pg_hba.conf
pg_ctlcluster 17 main start

# pg_upgrade does not carry planner statistics across before PostgreSQL 18.
# Until this finishes, every plan is built on empty statistics — expect slow queries.
vacuumdb --all --analyze-in-stages --jobs 4

# Update every extension to the version shipped with 17.
psql -c "SELECT name, installed_version, default_version FROM pg_available_extensions
         WHERE installed_version IS NOT NULL AND installed_version <> default_version;"
# then for each row:  ALTER EXTENSION <name> UPDATE;

# Only once you are happy, reclaim the old cluster's space.
./delete_old_cluster.sh

# ---------------------------------------------------------------
# Phase 3 — after the window
# ---------------------------------------------------------------
# Confirm the version and that the data is there.
psql -c "SELECT version();"
psql -c "SELECT count(*) FROM pg_stat_user_tables WHERE n_live_tup > 0;"
# Rebuild B-tree indexes if you crossed 13 (deduplication) or changed collation.
# reindexdb --all --concurrently --jobs 4
# Rebuild each physical standby from the upgraded primary.
# pg_basebackup -h PRIMARY -D /var/lib/postgresql/17/main -Fp -Xs -P -R

Preflight SQL (run on the old cluster)

-- Run all of these on the OLD cluster before the maintenance window.

-- 1. Prepared transactions block pg_upgrade outright.
SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

-- 2. Inactive replication slots hold WAL and, before PG17, are not carried over.
SELECT slot_name, slot_type, active, restart_lsn FROM pg_replication_slots;

-- 3. Extensions: every one of these must exist, compiled for 17, before you start.
SELECT extname, extversion FROM pg_extension ORDER BY extname;
-- You listed: postgis, pg_stat_statements, pgcrypto
--   Check each one's support matrix for PostgreSQL 17. PostGIS in particular
--   wants the same major PostGIS version on both sides, then ALTER EXTENSION postgis UPDATE.

-- 4. Unlogged tables are truncated by pg_upgrade's rebuild in some paths, and their
--    contents never survive a crash anyway. Know which ones you have.
SELECT c.oid::regclass FROM pg_class c WHERE c.relpersistence = 'u';

-- 5. Roles whose password is still md5 (a problem from 14 onwards, deprecated in 18).
SELECT rolname FROM pg_authid
WHERE  rolcanlogin AND rolpassword IS NOT NULL AND rolpassword NOT LIKE 'SCRAM-SHA-256%';

-- 6. Total size, so you can sanity-check the disk space arithmetic.
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- 7. Objects that depend on things removed in newer versions. pg_upgrade --check
--    finds most of them, but this catches WITH OIDS tables early (pre-12 only).
SELECT c.oid::regclass FROM pg_class c WHERE c.relhasoids;   -- errors on 12+, which is itself the answer

Breaking changes you will cross

Breaking changes between PostgreSQL 13 and 17
You cross every version in between, even though pg_upgrade jumps straight there.

--- PostgreSQL 14 ---
  * The default for password_encryption became scram-sha-256. Existing md5 hashes keep working, but any user whose password is reset now gets a SCRAM verifier, and old client drivers that do not speak SCRAM will fail to connect.
  * Some pg_stat_activity and pg_locks columns were reorganised, and pg_stat_statements gained a toplevel column — monitoring queries that SELECT * or rely on column order need checking.

--- PostgreSQL 15 ---
  * The public schema is no longer writable by PUBLIC. After the upgrade, non-owner roles cannot CREATE in public unless you grant it explicitly: GRANT CREATE ON SCHEMA public TO app_role. This breaks a surprising number of migration tools on first run.
  * The exclusive backup mode was removed. pg_start_backup / pg_stop_backup became pg_backup_start / pg_backup_stop, and any home-grown backup script using the old functions stops working.

--- PostgreSQL 16 ---
  * promote_trigger_file was removed. Failover tooling that promotes a standby by touching a file must switch to pg_ctl promote or the pg_promote() function.
  * Several contrib modules and libpq options were tightened (for example require_auth was added, and the old ssl_ciphers defaults changed). Review connection strings if you pin TLS settings.

--- PostgreSQL 17 ---
  * The adminpack contrib module was removed. If pg_upgrade --check reports it, DROP EXTENSION adminpack on the old cluster first.
  * old_snapshot_threshold was removed. A postgresql.conf that still sets it will refuse to start.
  * Good news for later upgrades: from 17 onwards pg_upgrade preserves logical replication slots on the publisher, so a downstream subscriber no longer has to be re-seeded after every major upgrade.

This list covers the changes that most often break an upgrade, not every incompatibility.
Read the "Migration to Version X" section of each release note for the full set before you commit to a date.

Notes

With --link the old cluster and the new cluster share the same files. Once the new cluster has been started and written to, the old cluster is unusable — your rollback is a restore from backup, not a restart of the old binaries. Take (and verify) a backup first.

Physical standbys cannot be upgraded in place along with the primary. Either rebuild each standby with pg_basebackup after the primary is upgraded, or use rsync in the mode described in the pg_upgrade documentation. Budget the rebuild time separately from the downtime window — the primary is already serving traffic by then.

You are crossing the password_encryption default change. Check for clients on old drivers before the cutover: SELECT rolname, CASE WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'scram' ELSE 'md5' END FROM pg_authid WHERE rolcanlogin;

You are crossing the public-schema permission change. Applications that create tables at runtime (migration frameworks, test harnesses) will start failing with 'permission denied for schema public' until you grant CREATE explicitly.

Rehearse on a restored copy of production, not on a small sample. The rehearsal gives you the real duration, the real extension errors, and a script you have already executed once.

Write the rollback plan down before the window opens, and decide the abort deadline in advance: 'if the new cluster is not accepting connections by HH:MM, we restore the backup'. Deciding that under pressure never goes well.

After the upgrade, watch pg_stat_statements for plans that changed. A new major version can pick a different plan for a query that was fine for years — that, not the upgrade itself, is the usual source of the post-upgrade incident.

Chat2DB is handy for the verification pass: connect to the old and new clusters side by side, compare schemas and row counts, and run the checks above without hand-editing psql output. https://app.chat2db.ai or https://chat2db.ai/download.

Do more than postgresql major version upgrade planner — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Set your current and target major versions, the platform, your data size and free disk space, and how long an outage you can accept.
Leave the method on 'Recommend one for me' to see the comparison, or force a specific method to see its plan and downtime estimate.
Work through the preflight SQL on the old cluster first, read the breaking-changes list, then rehearse the generated command plan on a restored copy before the real window.

Frequently asked questions

Is pg_upgrade --link safe to use in production?

It is safe in the sense that it is a supported, widely used mode, and it is the only way to upgrade a multi-terabyte cluster in minutes: instead of copying the data files it creates hard links to them, so the runtime barely depends on database size. The catch is the rollback. Because both clusters point at the same files on disk, once the new cluster has started and written anything, the old cluster is no longer consistent — you cannot simply restart the old binaries. Your rollback becomes a restore from backup, so the rule is: never run --link without a verified backup and a tested restore, and never delete the old cluster until the new one has served real traffic. If you have the disk space and the window, plain copy mode keeps the old cluster bootable, which is worth a lot on a stressful night.

How do I upgrade PostgreSQL with almost no downtime?

Use logical replication. Build the new cluster on the target version, copy the schema across with pg_dump --schema-only, create a publication on the old cluster and a subscription on the new one, and let the initial copy plus ongoing replication run for as long as it takes — hours or days, with the application still live on the old cluster. The downtime is then only the cutover: stop writes, wait for the replication lag to hit zero, advance the sequences (logical replication does not replicate them), and repoint the application. That is typically a couple of minutes. The costs are that the old cluster needs wal_level = logical, tables without a primary key need REPLICA IDENTITY FULL, and DDL is not replicated, so the schema has to stay frozen during the migration window.

Do I have to run ANALYZE after a PostgreSQL major upgrade?

Yes, for any upgrade to a version before 18. pg_upgrade migrates the data and the catalogue but not the planner statistics, so the moment the new cluster starts, every table looks statistically empty and the planner picks bad plans — this is the single most common cause of a 'the upgrade broke performance' incident. Run vacuumdb --all --analyze-in-stages immediately after starting the new cluster; the staged mode produces rough statistics fast, then refines them, so the site recovers in the first stage rather than at the end. PostgreSQL 18's pg_upgrade carries most statistics over, which removes the cliff, but running analyze afterwards is still cheap insurance.