PostgreSQL 14 to 18 Upgrade: What Breaks and How to Do It Safely

PostgreSQL 14 reaches end of life on 12 November 2026, and for most teams the target is PostgreSQL 18. pg_upgrade can go from 14 to 18 in one step, so there is no reason to stop at 15, 16 or 17. But one step from 14 to 18 means you get the incompatibilities of four major releases at once. Below are the ones that actually stop an upgrade or break things after it, with the errors you will see, and what changes when the database is hundreds of gigabytes or several terabytes.

Why now, and why 18

According to the PostgreSQL versioning policy, 14 gets its final release on 12 November 2026. After that there are no more security or bug fixes. PostgreSQL 19 is due a week later, but most production systems will want a release that already has minor updates behind it. Our PostgreSQL 14 end-of-life overview covers the choice between 18 and 19. This article is about the technical side of getting from 14 to 18.

The pg_upgrade documentation supports upgrades from 9.2 and later, so 14 to 18 is a single, supported hop. Logical replication and dump/restore work as well. If you have not chosen a method yet, see our comparison of pg_upgrade and logical replication. Most of what follows applies to every method, because it is about the new server, not the way the data gets there.

What stops pg_upgrade: data checksums

This is the first thing most 14 to 18 upgrades will run into. Since PostgreSQL 18, initdb enables data checksums by default. Many 14 clusters were created without them, and pg_upgrade requires both clusters to match. pg_upgrade --check stops right away:

$ pg_upgrade --check -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/14/main -D /var/lib/postgresql/18/main
...
old cluster does not use data checksums but the new one does
Failure, exiting

The fix is to create the new cluster with the new --no-data-checksums option. On Debian or Ubuntu, pg_createcluster passes initdb options after --. Check the old cluster first:

# is the old cluster using checksums? (0 = no)
pg_controldata /var/lib/postgresql/14/main | grep checksum

# if not, create the new cluster to match
initdb -D /var/lib/postgresql/18/main --no-data-checksums ...

Checksums are worth having, because they detect storage corruption. Turn them on as a separate, planned step after the upgrade, not in the middle of it. On a large database that step needs its own downtime, see the section on large databases below.

What stops the server from starting: removed settings

If you carry your old postgresql.conf over, or your configuration management templates it, a setting that no longer exists stops the server from starting. One line left over from a typical 14 standby config is enough:

LOG:  unrecognized configuration parameter "promote_trigger_file" in file ".../postgresql.conf" line 890
FATAL:  configuration file ".../postgresql.conf" contains errors

Settings removed between 14 and 18 that still appear in real configs:

• stats_temp_directory: removed in 15.
• promote_trigger_file: removed in 16. Use pg_ctl promote or pg_promote(), and check your failover tooling.
• vacuum_defer_cleanup_age: removed in 16.
• force_parallel_mode: renamed to debug_parallel_query in 16.
• old_snapshot_threshold, db_user_namespace and trace_recovery_messages: removed in 17.

The safest approach is to start from the default 18 postgresql.conf and carry over only the settings you can explain.

What breaks in scripts: backups and monitoring

The database server is usually fine. The trouble is in the scripts around it.

• Backup functions. PostgreSQL 15 removed the exclusive backup mode. pg_start_backup() and pg_stop_backup() were renamed to pg_backup_start() and pg_backup_stop(), and pg_is_in_backup() and pg_backup_start_time() are gone. On 18, an old script fails with function pg_start_backup(unknown) does not exist. Home-grown backup scripts often call these functions. Tools like pgBackRest or Barman handle this themselves, as long as you run a version that supports 18.
• Checkpoint statistics. PostgreSQL 17 moved checkpoint columns from pg_stat_bgwriter to the new pg_stat_checkpointer view. A query for checkpoints_timed returns column "checkpoints_timed" does not exist; the new column is num_timed. buffers_backend and buffers_backend_fsync were removed as well.
• pg_stat_statements. In 17, blk_read_time and blk_write_time became shared_blk_read_time and shared_blk_write_time.
• WAL and I/O statistics. In 18, pg_stat_wal lost wal_write, wal_sync, wal_write_time and wal_sync_time, and pg_stat_io lost op_bytes.

A monitoring check that fails with a query error is easy to mistake for "no data", so the dashboard looks quiet. Upgrade your monitoring agent, run every custom query against a test 18 server, and fix them before the production upgrade.

What breaks after the upgrade: functions in indexes

This one is easy to miss because the upgrade succeeds and queries keep working. Since 17, maintenance operations such as ANALYZE, VACUUM, REINDEX, CREATE INDEX and REFRESH MATERIALIZED VIEW run functions with a safe search_path. A function used in an expression index or a materialized view that refers to tables in its own schema without a schema name, and relied on the database's search_path to find them, now fails.

A typical example: a PL/pgSQL function app.to_eur() reads a table rates in schema app, and an expression index uses it. After the upgrade, SELECT queries still work, but:

shop=# ANALYZE orders;
ERROR:  relation "rates" does not exist
LINE 1: amount * (SELECT rate FROM rates WHERE code = c)
                                   ^
CONTEXT:  PL/pgSQL function app.to_eur(text,numeric) line 1 at RETURN

REINDEX, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW and the recommended post-upgrade vacuumdb --all --analyze-only fail the same way. On a large table this hurts more over time: statistics go stale, plans get worse, and the index cannot be rebuilt. Find these functions on 14 before the upgrade and fix them ahead of time. This query works on both 14 and 18:

-- functions used by indexes and materialized views
-- that do not pin their own search_path (run on 14 before upgrading)
SELECT DISTINCT p.oid::regprocedure AS function, c.relname AS used_by
FROM pg_depend d
JOIN pg_proc p      ON p.oid = d.refobjid AND d.refclassid = 'pg_proc'::regclass
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l  ON l.oid = p.prolang
LEFT JOIN pg_rewrite r ON d.classid = 'pg_rewrite'::regclass AND r.oid = d.objid
JOIN pg_class c     ON c.oid = COALESCE(r.ev_class, d.objid)
WHERE c.relkind IN ('i', 'I', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND l.lanname NOT IN ('c', 'internal')
  AND NOT EXISTS (SELECT 1 FROM unnest(p.proconfig) AS cfg
                  WHERE cfg LIKE 'search_path=%');

-- fix: give the function the search_path it needs
ALTER FUNCTION app.to_eur(text, numeric) SET search_path = app;

Not every function in the list is a problem. Functions that only use built-ins or qualify every table name are fine. But each one is worth a look, and setting search_path on the function is harmless on 14 as well. ALTER FUNCTION ... SET search_path only changes the function definition and rewrites no data, so it is just as quick on a multi-terabyte table.

Security changes: public schema and MD5 passwords

Since 15, ordinary users can no longer create objects in the public schema of a new database. pg_upgrade keeps the old permissions, so an upgraded database behaves as before. This also applies to databases created after the upgrade, because they are copied from the upgraded template1. A freshly created 18 cluster has the new, strict default. This is why the same migration can work on the upgraded production server and fail on a new staging server. Our article on "permission denied for schema public" explains the fix and how to move to the new default deliberately.

PostgreSQL 18 deprecates MD5 passwords. They still work, but support will be removed in a future major release, and setting an MD5 password now produces a warning. Since 14 already defaults to scram-sha-256, MD5 hashes usually belong to old roles. Find them:

SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

Reset their passwords while password_encryption is scram-sha-256, check that the clients connect with SCRAM, and then change md5 to scram-sha-256 in pg_hba.conf.

Smaller changes worth checking

• Extensions. Every extension must be installed for 18 before you run pg_upgrade. adminpack was removed in 17, and plpython2u/plpythonu in 15.
• Logical replication slots. pg_upgrade carries logical slots and subscriptions over only when the old cluster is 17 or later. From 14 you need to recreate them.
• Roles. In 16, CREATEROLE lost much of its power over other roles, and changes now need ADMIN OPTION. Scripts that create and manage users without superuser may need new grants.
• VACUUM and ANALYZE on inheritance parents. In 18 they also process the children. Use ONLY for the old behaviour.
• COPY and CSV. In 18, COPY FROM no longer treats \. as the end of a CSV file. The release notes warn that older psql clients may have \copy problems with 18 servers, so upgrade clients too.
• Unlogged partitioned tables are no longer allowed in 18.
• Full-text search and pg_trgm. If the cluster uses ICU or the builtin collation provider, the 18 release notes recommend reindexing full-text search and pg_trgm indexes after pg_upgrade. On large tables, use REINDEX CONCURRENTLY as described in our REINDEX CONCURRENTLY guide.
• Defaults. hash_mem_multiplier is 2.0 since 15. In 18, effective_io_concurrency and maintenance_io_concurrency default to 16, and the new asynchronous I/O uses io_method = worker by default. If you rely on defaults, compare performance on a realistic workload.

Statistics after the upgrade

Good news, especially for large databases: since 18, pg_upgrade preserves optimizer statistics, even when the old cluster is 14. Previously a multi-terabyte database ran with poor plans until a long ANALYZE finished. Extended statistics (CREATE STATISTICS) are not carried over, so run ANALYZE on the tables that use them first. The tool prints two commands to run at the end. The documentation recommends --jobs to speed them up, and PGOPTIONS='-c vacuum_cost_delay=0' if you use a cost delay:

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

The first fills in statistics only where they are missing, so the database is ready to serve traffic much sooner than with a full ANALYZE in older upgrades. The second refreshes everything and also updates the counters that trigger autovacuum. ANALYZE does not block normal reads and writes, so on a large database you can run the second command after the application is back online.

Large databases: what changes

On a database of a few gigabytes, any method fits in a short window. On hundreds of gigabytes or several terabytes, the choice of mode and the preparation determine whether the downtime is minutes or hours.

• Copy mode (the default) copies every data file. It needs as much free disk space as the data itself, and the time grows with the data size. For a large database it is usually the wrong choice.
• Link, clone and swap do not copy data files, so the time hardly depends on the data size. What remains is dumping and restoring the schema, so a database with tens of thousands of tables, partitions and indexes takes longer than a large but simple one. The documentation says --swap may be the fastest when there are many relations. All three need the old and new data directories on the same file system.
• Parallelism. --jobs processes several databases and tablespaces in parallel. The documentation suggests starting with the number of CPU cores. With a single database in one tablespace it helps much less.
• Checks before the window. pg_upgrade --check can run while the old server is still running. Run it days ahead, with the same mode option you will use (--link, --clone or --swap), so the mode-specific checks run too.
• Standbys. Rebuilding a multi-terabyte standby from scratch takes hours, during which you have no failover. With link mode, the documentation describes upgrading standbys with rsync --hard-links from the primary, which copies only the files that were not linked.
• Enabling checksums later. pg_checksums --enable works only on a cleanly stopped cluster and rewrites blocks in place. The documentation warns that this can take a long time on a large cluster, and with replicas all nodes must be switched consistently or the standbys rebuilt. If that downtime is not acceptable, the alternative is logical replication into a new cluster created with checksums.
• Logical replication instead of pg_upgrade. If even minutes of downtime do not fit, logical replication avoids them, but the initial copy of large tables can take many hours or days, and the source keeps WAL for the replication slot all that time. Watch the disk on the old server.

The only reliable estimate of the maintenance window is a rehearsal on a full-size copy of production, on the same storage type. Time every step: --check, the upgrade itself, the start, the first vacuumdb and the standbys.

A safe upgrade plan

1. Update 14 to the latest minor release and take a verified backup.
2. Run the checks on 14: the checksum setting, functions without search_path, MD5 roles, extensions and whether 18 builds exist for them, and logical replication slots.
3. Clean the configuration: start from the 18 defaults and remove deleted settings.
4. Fix scripts and monitoring: backup functions, pg_stat_bgwriter, pg_stat_statements columns and your monitoring agent version.
5. Rehearse on a full-size copy of production: create the 18 cluster with matching checksums, run pg_upgrade --check, then the real upgrade in the mode you will use. Time every step, run the application tests and the post-upgrade vacuumdb, and compare your slowest queries.
6. Upgrade production in a maintenance window with the rollback plan written down, then rebuild standbys and run vacuumdb.
7. Afterwards, plan enabling checksums and moving off MD5 as separate tasks.

Managed PostgreSQL

On RDS, Cloud SQL or Azure, the provider runs the upgrade, but most of the issues above are in your schema, scripts and clients. They apply there too: functions in indexes, renamed monitoring columns, backup scripts, MD5 roles and changed defaults. Test the upgrade on a snapshot of production before you schedule it.