Postgres 14 hits end of life on November 12

On Thursday, November 12, the PostgreSQL project ships the last release of version 14. After that, 14 gets no more security fixes and no more bug fixes. The versioning policy gives every major version five years, and 14's five years are up.

Your database won't stop. It will keep running, and every vulnerability found from that day on stays open.

I ran a real upgrade from 14.24 to 18.6 to see what breaks. Several things did, and pg_upgrade --check caught only two of them.

What stops on the 12th

Version 14 shipped on September 30, 2021. Its final release is scheduled for November 12, 2026. Both the May and August update announcements carried the same warning.

The release team also says the November 12 update will include a large number of fixes. That makes the last 14 release worth installing.

The security load this year explains why it matters. The August 13 update fixed 28 vulnerabilities and more than 110 bugs across the supported branches.

One of them, CVE-2026-6471, lets logical decoding load an arbitrary file. It affects every version back to 9.4, 14 included.

After November 12, the next flaw like that stays unpatched on 14.

Managed services buy time, for a fee

AWS's RDS calendar lists February 28, 2027 as the end of standard support for major version 14. Extended Support pricing starts on March 1, 2027, and RDS enrolls the database in Extended Support automatically. The charge is per vCPU-hour on top of the instance price.

AWS prices Extended Support per vCPU per hour by Region, and the rate doubles in year three, which for version 14 starts on March 1, 2029. Reserved Instance discounts don't apply, standbys and read replicas are billed too, and you need to be on the latest minor version to get it. I couldn't find a published rate for version 14 itself, so run your instance through the AWS Pricing Calculator before you budget.

Self-hosted clusters get no such cushion. The date is the date.

Pick a target: 18

Each major version gets five years. The versioning policy lists these final-release dates:

Jumping to 15 or 16 means doing this again in a year or two. I'd go to 18. It has the longest runway, and pg_upgrade in 18 keeps your planner statistics.

PostgreSQL 19 is a release candidate, and the release team plans general availability for November 19, unless an issue forces a delay. That's a week after 14 stops.

I'd let a new major version collect its first minor releases before putting production on it. That's a judgment call, not a rule.

How to move

pg_upgrade has three modes that matter. Copy duplicates every data file and leaves the old cluster untouched. --link hard-links the files instead.

--swap, available in 18's pg_upgrade, moves the old data directories into the new cluster, and the docs say it can beat the other modes on clusters with many relations.

Logical replication gets you close to zero downtime, but there's a catch for 14 sources. pg_upgrade only migrates logical slots when the old cluster is 17 or newer, and subscription state from older clusters is silently ignored. If you run logical replication today, plan the re-sync.

What the three modes cost on a real cluster

Test setup: PostgreSQL 14.24 and 18.6 (conda-forge builds), Linux x86_64, one vCPU, 3 GB RAM, UTF-8. The database held a 5M-row orders table, a 3M-row events table with JSONB payloads, 500k customers and 2,000 small tenant tables. That's 2,003 tables and 1,334 MB.

Everything finished in under 13 seconds. Link and swap skip the file copy, so their lead grows with data size, but I only measured 1.3 GB.

My 2,003 tables didn't separate link from swap. Treat these numbers as a floor for a small, warm-cache database and rehearse on your own data.

What broke in my test

Seven things behave differently on 18 than they did on 14. Each one below comes from a real 14.24 to 18.6 run.

1. Checksums are on by default now

The 18 release notes say initdb enables data checksums by default, and pg_upgrade needs both clusters to match. A default 14 cluster has them off. --check catches it:

old cluster does not use data checksums but the new one does Failure, exiting 

There are two fixes, and I tested both. Create the new cluster with initdb --no-data-checksums, and --check prints *Clusters are compatible*.

The other fix is to enable checksums on the old cluster first. With the old cluster shut down cleanly, pg_checksums --enable from the 14 binaries scanned 173,865 blocks in 2.1 seconds on my 1.3 GB database. The upgrade into a default 18 cluster then finished, and SHOW data_checksums returned on.

The second fix is the better end state, but it needs the old cluster offline while every block is rewritten. That time grows with cluster size.

2. Expression indexes that rely on search_path

This one passes --check and fails the real run. Since 17, CREATE INDEX, REINDEX, ANALYZE, VACUUM and the materialized view commands run with a safe search_path. The 17 release notes say functions used by expression indexes and materialized views must set their own search path if they reference non-default schemas.

Here's the setup that works on 14:

CREATE SCHEMA app; SET search_path = app, public; CREATE TABLE rates (code text PRIMARY KEY, factor numeric); CREATE FUNCTION to_usd(amount numeric, cur text) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT amount * factor FROM rates WHERE code = cur $$; CREATE TABLE orders (id int, amount numeric, cur text); CREATE INDEX orders_usd ON orders (to_usd(amount, cur)); 

pg_upgrade --check printed *Clusters are compatible*. The real run stopped at "Restoring database schemas in the new cluster". The log named the index:

pg_restore: from TOC entry 2400; 1259 16412 INDEX orders_usd postgres pg_restore: error: could not execute query: ERROR: relation "rates" does not exist CONTEXT: SQL function "to_usd" during inlining 

The fix is one line, run on the old cluster:

ALTER FUNCTION app.to_usd(numeric, text) SET search_path = app; 

After that the upgrade completed, and REINDEX and REFRESH MATERIALIZED VIEW both ran clean on 18. The pg14-eol-preflight.sql script further down finds every index and materialized view that depends on such a function before you upgrade.

3. Removed settings stop the server from starting

pg_upgrade doesn't copy postgresql.conf. If you carry your old file over, 18 may refuse to boot. I tested each of the four:

The 16 and 17 release notes list all four. Build the new config from 18's defaults and add your changes by hand.

4. adminpack is gone

The extension was removed in 17. With it installed in the old cluster, --check fails at "Checking for presence of required libraries" and writes could not load library "$libdir/adminpack" to loadable_libraries.txt. Drop the extension on 14 first.

5. Backup scripts and a numeric literal

The 15 release notes removed exclusive backup mode. On 18, SELECT pg_start_backup('x') fails with function pg_start_backup(unknown) does not exist. The replacements are pg_backup_start() and pg_backup_stop(), and pg_backup_start('x') worked on my cluster.

The same release stopped accepting numeric literals with trailing characters. SELECT 123abc returns 123 on 14, where abc becomes a column alias. On 18 it fails with trailing junk after numeric literal.

6. New databases inherit an old permission

Since 15, new databases don't let ordinary roles create tables in the public schema. The release notes say upgrading preserves the existing permissions. Here's what that means in practice.

After pg_upgrade, template1 kept the old grant, and a database I created afterward let a non-owner role create tables:

upgraded cluster, template1: {postgres=UC/postgres,=UC/postgres} fresh 18 cluster, template1: {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner} 

On the fresh 18 cluster the same role got permission denied for schema public. On the upgraded cluster it got CREATE TABLE. If you want the tighter default, revoke CREATE on public from PUBLIC in template1 and in each existing database, after you've confirmed your applications don't rely on it.

7. MD5 passwords now warn

Setting an MD5 password on 18 prints this:

WARNING: setting an MD5-encrypted password DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL. 

My MD5 role came through the upgrade intact. The release notes say support will be removed in a future major version, and that md5_password_warnings = off silences the warning. I'd move those roles to SCRAM now instead of hiding it.

Documented changes I didn't test

The release notes also list these. They're worth a grep through your code and monitoring:

• A role with CREATEROLE can no longer modify arbitrary non-superuser roles (16).

• pg_stat_bgwriter lost buffers_backend and buffers_backend_fsync, and pg_stat_statements renamed blk_read_time and blk_write_time to shared_blk_read_time and shared_blk_write_time (17).

• plpython2u and plpythonu were removed (15).

• VACUUM and ANALYZE now process inheritance children unless you pass ONLY. Unlogged partitioned tables are disallowed. AFTER triggers run as the role active when the event was queued (18).

After the upgrade

Version 18 carries planner statistics across. On my test, pg_stats held 6,012 rows for the public schema before the upgrade and 6,012 after.

The cumulative counters don't carry over. Right after the upgrade, n_live_tup for orders was 0 and last_analyze was empty. Run the two commands pg_upgrade prints:

vacuumdb -U postgres --all --analyze-in-stages --missing-stats-only vacuumdb -U postgres --all --analyze-only 

The first took 0.1 seconds on my cluster, because nothing was missing. The second took 1.6 seconds, and afterward n_live_tup read about 5,000,072 for orders.

A runbook

Both scripts are printed in full below the runbook. pg14-eol-preflight.sql is the set of read-only checks from above. pg-upgrade-14-to-18.sh wraps pg_upgrade and matches the checksum setting for you.

1. Run pg14-eol-preflight.sql in every database on the 14 cluster. Checks 1 to 4 should come back empty.

2. Fix what it finds. Pin function search paths, drop adminpack, remove the dead settings, and list the roles with MD5 hashes.

3. Restore last night's backup somewhere else and rehearse the whole upgrade on it. --check passed on the search path case, and only the real run showed the failure.

4. Install 18 beside 14. Stop 14 cleanly with pg_ctl stop -m fast.

5. Run the wrapper in check-only mode, then for real:
   

export OLD_BIN=/path/to/14/bin NEW_BIN=/path/to/18/bin export OLD_DATA=/path/to/14/data NEW_DATA=/path/to/18/data export INITDB_ARGS="-E UTF8 --locale=C.UTF-8" # must match the old cluster ./pg-upgrade-14-to-18.sh # check only, the default RUN=1 MODE=link ./pg-upgrade-14-to-18.sh # real upgrade; MODE is copy, link or swap 

I ran it in check-only mode and in link and swap mode on the test cluster. It reads the old cluster's checksum setting from pg_controldata and creates the new cluster to match. Set INITDB_ARGS so the encoding and locale match your old cluster, or pg_upgrade will refuse.

1. Start 18 with a config built from its defaults. Run the two vacuumdb commands, then your smoke tests.

2. Keep the old cluster until you're confident. In link and swap mode the old cluster becomes unsafe to start once the new one runs, so take a backup first.

The preflight check: pg14-eol-preflight.sql

Run it as a superuser in every database on the 14 cluster. Every check is read-only.

Checks 1 to 4 should return no rows before you upgrade. Check 5 lists roles to move to SCRAM, check 6 tells you which initdb flag to use, and check 7 should return 0.

-- pg14-eol-preflight.sql -- Read-only checks to run in EVERY database of a PostgreSQL 14 cluster -- before pg_upgrade to 17 or 18. Run as a superuser: -- psql -d yourdb -f pg14-eol-preflight.sql -- Checks 1-4 should return zero rows before you upgrade. Check 5 lists roles to -- move to SCRAM, check 6 shows the checksum setting, and check 7 should return 0. \echo '== 1. Indexes that call SQL/PL/pgSQL functions with no search_path set ==' -- PostgreSQL 17 runs maintenance operations (CREATE INDEX, REINDEX, ANALYZE, -- VACUUM, REFRESH MATERIALIZED VIEW) with a safe search_path. pg_upgrade --check -- does not catch these; the schema restore does. SELECT DISTINCT i.indexrelid::regclass AS index_name, p.oid::regprocedure AS function_name FROM pg_index i JOIN pg_depend d ON d.classid = 'pg_class'::regclass AND d.objid = i.indexrelid AND d.refclassid = 'pg_proc'::regclass JOIN pg_proc p ON p.oid = d.refobjid JOIN pg_language l ON l.oid = p.prolang WHERE l.lanname IN ('sql', 'plpgsql') AND NOT EXISTS (SELECT 1 FROM unnest(p.proconfig) cfg WHERE cfg LIKE 'search_path=%'); \echo '== 2. Materialized views that call SQL/PL/pgSQL functions with no search_path set ==' SELECT DISTINCT c.oid::regclass AS matview_name, p.oid::regprocedure AS function_name FROM pg_class c JOIN pg_rewrite r ON r.ev_class = c.oid JOIN pg_depend d ON d.classid = 'pg_rewrite'::regclass AND d.objid = r.oid AND d.refclassid = 'pg_proc'::regclass JOIN pg_proc p ON p.oid = d.refobjid JOIN pg_language l ON l.oid = p.prolang WHERE c.relkind = 'm' AND l.lanname IN ('sql', 'plpgsql') AND NOT EXISTS (SELECT 1 FROM unnest(p.proconfig) cfg WHERE cfg LIKE 'search_path=%'); \echo '== 3. Extensions and languages that no longer exist in 17/18 or 15+ ==' SELECT extname AS removed_extension FROM pg_extension WHERE extname = 'adminpack'; SELECT lanname AS removed_language FROM pg_language WHERE lanname IN ('plpythonu', 'plpython2u'); \echo '== 4. Settings that were removed in 15, 16 or 17 and are set explicitly ==' SELECT name, setting, source FROM pg_settings WHERE name IN ('vacuum_defer_cleanup_age', 'promote_trigger_file', 'old_snapshot_threshold', 'db_user_namespace') AND source <> 'default'; \echo '== 5. Roles still using MD5 password hashes (deprecated in 18) ==' SELECT rolname AS md5_role FROM pg_authid WHERE rolpassword LIKE 'md5%'; \echo '== 6. Data checksums on this cluster (decides the initdb flag for 18) ==' SHOW data_checksums; \echo '== 7. Prepared transactions (pg_upgrade refuses to run with any) ==' SELECT count(*) AS prepared_transactions FROM pg_prepared_xacts; 

It only sees direct dependencies. A function that calls another function without a search path can still slip through, so review those by hand.

The upgrade wrapper: pg-upgrade-14-to-18.sh

Save it, run chmod +x pg-upgrade-14-to-18.sh, and set the environment variables from the runbook. It does nothing destructive until you pass RUN=1.

#!/usr/bin/env bash # pg-upgrade-14-to-18.sh # Thin wrapper around pg_upgrade for a PostgreSQL 14 -> 18 jump. # It matches the checksum setting for you, runs pg_upgrade --check first, # and only does the real upgrade when RUN=1. # # Required environment: # OLD_BIN bin directory of PostgreSQL 14 (pg_upgrade, pg_controldata) # NEW_BIN bin directory of PostgreSQL 18 # OLD_DATA data directory of the old cluster (cleanly shut down) # NEW_DATA data directory for the new cluster (must not exist yet) # Optional: # MODE copy (default), link or swap # RUN set to 1 to run the real upgrade after a clean --check # INITDB_ARGS extra initdb flags; encoding and locale must match the old # cluster, e.g. "-E UTF8 --locale=en_US.UTF-8" # PGSUPERUSER bootstrap superuser of the old cluster (default: postgres) set -euo pipefail : "${OLD_BIN:?set OLD_BIN}" "${NEW_BIN:?set NEW_BIN}" : "${OLD_DATA:?set OLD_DATA}" "${NEW_DATA:?set NEW_DATA}" MODE="${MODE:-copy}" SUPERUSER="${PGSUPERUSER:-postgres}" INITDB_ARGS="${INITDB_ARGS:-}" case "$MODE" in copy) FLAG="" ;; link) FLAG="--link" ;; swap) FLAG="--swap" ;; *) echo "MODE must be copy, link or swap" >&2; exit 2 ;; esac [ -e "$NEW_DATA" ] && { echo "NEW_DATA already exists: $NEW_DATA" >&2; exit 2; } state=$("$OLD_BIN/pg_controldata" "$OLD_DATA" | sed -n 's/^Database cluster state: *//p') if [ "$state" != "shut down" ]; then echo "Old cluster state is '$state'. Stop it cleanly first (pg_ctl stop -m fast)." >&2 exit 1 fi old_sums=$("$OLD_BIN/pg_controldata" "$OLD_DATA" | sed -n 's/^Data page checksum version: *//p') if [ "$old_sums" = "0" ]; then SUMS_FLAG="--no-data-checksums" echo "Old cluster has data checksums OFF -> initdb 18 with --no-data-checksums" else SUMS_FLAG="" echo "Old cluster has data checksums ON -> initdb 18 with its default (on)" fi # shellcheck disable=SC2086 "$NEW_BIN/initdb" -D "$NEW_DATA" -U "$SUPERUSER" $SUMS_FLAG $INITDB_ARGS > /dev/null echo "Created new cluster at $NEW_DATA" cd "$(dirname "$NEW_DATA")" echo "Running pg_upgrade --check ..." if ! "$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" -d "$OLD_DATA" -D "$NEW_DATA" \ -U "$SUPERUSER" --check; then echo "pg_upgrade --check failed. Details: $NEW_DATA/pg_upgrade_output.d" >&2 echo "Fix the items above, delete $NEW_DATA and rerun." >&2 exit 1 fi if [ "${RUN:-0}" != "1" ]; then echo echo "Check passed. --check does not catch everything (see pg14-eol-preflight.sql)." echo "Rehearse on a restored copy of production, then rerun with RUN=1." exit 0 fi echo "Running the real upgrade in $MODE mode ..." start=$(date +%s) # shellcheck disable=SC2086 if ! "$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" -d "$OLD_DATA" -D "$NEW_DATA" \ -U "$SUPERUSER" $FLAG; then echo "pg_upgrade failed. Read the logs in $NEW_DATA/pg_upgrade_output.d before deleting anything." >&2 echo "In copy mode the old cluster is untouched. In link or swap mode, read the docs' revert section first." >&2 exit 1 fi echo "pg_upgrade finished in $(( $(date +%s) - start )) s" cat <<MSG Next steps: 1. Start the new cluster and point your config at it. 2. vacuumdb -U $SUPERUSER --all --analyze-in-stages --missing-stats-only 3. vacuumdb -U $SUPERUSER --all --analyze-only 4. If the old cluster was not used in link or swap mode, ./delete_old_cluster.sh removes it. MSG 

If you can't make the date

Install the final 14 minor release the day it ships, since the release team expects it to be a large batch of fixes. Keep the instance off public networks and tighten who can connect. Put a firm upgrade date on the calendar, because an unpatched database collects risk every week it runs.

On RDS you have until February 28, 2027 before Extended Support billing starts. Other managed services keep their own calendars, so check yours.

FAQ

Will Postgres 14 stop working on November 12? No. It keeps running. The project stops shipping fixes.

Can I upgrade straight from 14 to 18? Yes. pg_upgrade supports it, and I did it with 14.24 and 18.6. Read the seven items above first.

Should I wait for 19? General availability is planned for November 19, after 14 stops. I'd put production on 18 now and revisit 19 once it has had a few minor releases.

Test notes

Tested with PostgreSQL 14.24 and 18.6 from conda-forge on Linux x86_64 with one vCPU and 3 GB RAM, fsync off, one run per trap and four runs per mode. Times come from a small, warm-cache database and won't match production. I didn't test replication setups, extensions such as PostGIS, distro wrappers like pg_upgradecluster, or any managed service.

Published via ZyVOP — Write once in Markdown, auto-backup to GitHub, and syndicate to Dev.to, Medium & Hashnode in 1 click.