--- title: "Postgres 14 hits end of life on November 12" author: "Bhavya Arora" published: "2026-11-11T05:32:54.963Z" updated: "2026-11-11T05:32:54.963Z" url: "https://zyvop.com/postgres-14-hits-end-of-life-on-november-12-r5hhk" canonical: "https://zyvop.com/postgres-14-hits-end-of-life-on-november-12-r5hhk" tags: ["database-upgrade","pg_upgrade","postgres-18","postgresql","end-of-life"] --- # Postgres 14 hits end of life on November 12 PostgreSQL 14 gets its last release on November 12. I upgraded 14.24 to 18.6 and hit seven failures. pg_upgrade --check caught two. ![Cover image]() 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: | Version | Final release | | --- | --- | | 15 | November 11, 2027 | | 16 | November 9, 2028 | | 17 | November 8, 2029 | | 18 | November 14, 2030 | 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 ```mermaid flowchart TD A[Postgres 14 cluster] --> B{Can you take a short outage?} B -- Yes --> C[pg_upgrade] B -- No --> D[Logical replication to a new 18 cluster] C --> E{Large data or many tables?} E -- Yes --> F[--link or --swap] E -- No --> G[Copy mode keeps the old cluster intact] ``` `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. | Mode | Four runs | Range | | --- | --- | --- | | copy | 9.5, 9.8, 12.3, 11.2 s | 9.5 to 12.3 s | | link | 7.6, 7.9, 7.9, 7.7 s | 7.6 to 7.9 s | | swap | 6.7, 8.1, 8.7, 8.8 s | 6.7 to 8.8 s | 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: ```text 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: ```sql 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: ```text 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: ```sql 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: | Setting | Removed in | Error on 18 | | --- | --- | --- | | `vacuum_defer_cleanup_age` | 16 | `FATAL: unrecognized configuration parameter` | | `promote_trigger_file` | 16 | same | | `old_snapshot_threshold` | 17 | same | | `db_user_namespace` | 17 | same | 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: ```text 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: ```text 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: ```bash 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: ```bash 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. ```sql -- 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`. ```bash #!/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 <