PostgreSQL 14 End of Life: Plan an Upgrade to PostgreSQL 18

Capture a basic upgrade inventory

select version();

select extname, extversion
from pg_extension
order by extname;

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
where datallowconn
order by pg_database_size(datname) desc;

select rolname, rolcanlogin, rolsuper, rolreplication
from pg_roles
order by rolname;

Also inventory operating-system packages, collations, tablespaces, replication slots, publications, subscriptions, backup jobs, and client drivers.

Choose the migration mechanism

pg_upgrade reuses or links the existing data files and is usually the fastest path for a large self-managed cluster. It requires old and new server binaries plus compatible extension libraries on the same machine.

Dump-and-restore rewrites the logical database into a fresh cluster. It is slower and needs more temporary storage, but it can clean up physical layout and is easier to move across machines or operating systems. Logical replication can reduce cutover time at the cost of a more involved migration.

Resolve extension and collation risks first

List every extension and confirm that a PostgreSQL 18 build exists for the destination platform. An extension installed with CREATE EXTENSION may still depend on operating-system packages that pg_dump cannot carry.

Check locale and collation versions on the destination. Changes in libc or ICU can require REINDEX so indexes use the same ordering rules as the new environment.

Rehearse with production-shaped data

A schema-only test misses the time required to copy or link files, rebuild extension objects, analyze tables, and warm important caches. Use a recent backup or storage snapshot and record every command, duration, warning, and manual correction.

Run pg_upgrade --check before the rehearsal and again before cutover. For dump-and-restore, test the exact pg_dump and pg_restore flags, parallelism, object ownership, and role creation order.

Run the compatibility check before pg_upgrade

/path/to/postgresql-18/bin/pg_upgrade \
  --check \
  --old-bindir=/path/to/postgresql-14/bin \
  --new-bindir=/path/to/postgresql-18/bin \
  --old-datadir=/path/to/pg14-data \
  --new-datadir=/path/to/pg18-data

Use paths and service controls appropriate to your installation. Read the generated log files rather than treating a zero exit code as the whole validation.

Define cutover and rollback together

Specify when writes stop, how final changes are captured, how clients move to the new port or endpoint, and who decides to roll back. A rollback plan must account for writes accepted by PostgreSQL 18 after cutover; the old PostgreSQL 14 cluster will not contain them automatically.

Keep a verified backup independent of the old data directory. If pg_upgrade uses --link, starting the new cluster changes linked files and removes the old cluster as a clean rollback target.

Validate behavior, not only row counts

Compare database sizes, important table counts, constraints, sequences, extension versions, permissions, scheduled jobs, replication, and application smoke tests. Run ANALYZE and compare plans for latency-sensitive queries.

Review the PostgreSQL backup guide before the rehearsal. A major upgrade is not the moment to discover that the restore procedure was never tested.