Databases

Upgrade PostgreSQL 14 to 18 on Ubuntu, Debian and Rocky Linux

PostgreSQL 14 stops receiving fixes on November 12, 2026, and 14.24 is its last release. Upgrading PostgreSQL 14 to 18 sounds like a dump-and-restore weekend, but pg_upgrade rewrites the system catalogs in place and keeps every table file, so our 3.6 GB test cluster upgraded in 430 seconds in dump mode and 18 seconds in link mode. The slow path is worth knowing; you’ll use the fast one.

Original content from computingforgeeks.com - post 186027

This guide covers both routes: pg_upgradecluster on Ubuntu and Debian, where the wrapper picks the newest installed version and runs pg_upgrade for you, and a manual pg_upgrade on Rocky Linux and RHEL, where you drive it. It covers the extension update script and the statistics rebuild. Every command was run on Ubuntu 24.04, Debian 13 and Rocky Linux 9 in November 2026 against a cluster seeded with 15 million rows and three extensions, so the timings and errors below are measured.

What changes between PostgreSQL 14 and 18

pg_upgrade jumps multiple major versions in one run, so a 14 cluster can go straight to 18 without stopping at 15, 16 and 17. That single-step upgrade is documented behavior of pg_upgrade, and it held on all three distributions in our lab. What the tool won’t do is update your extensions, migrate removed server settings or fix application code that calls renamed functions, and those gaps are where the real work of a major upgrade lives.

The deadline matters when you are picking the target. PostgreSQL 18 is the current stable release and the one this guide upgrades to:

VersionCurrent releaseEnd of support
PostgreSQL 1414.24November 12, 2026
PostgreSQL 1717.11November 8, 2029
PostgreSQL 1818.6November 14, 2030

PostgreSQL 19 is still at release candidate stage as of November 2026, so 18 is the target here and 17 remains the conservative choice if your tooling lags a major version. If you are heading to 19 anyway, the PostgreSQL 19 install guide for Ubuntu, Debian, Rocky and AlmaLinux tracks that release separately. Whichever major you land on, put its packages on the fixed November build first: that release closes 52 CVEs across every supported branch, and our 18.7 security rundown shows how to patch before you upgrade PostgreSQL 14 to 18.

A few changes between 14 and 18 can break application code or scripts after the upgrade, and pg_upgrade doesn’t fix any of them. They bite application code, not the upgrade itself:

  • public schema no longer grants CREATE to PUBLIC on new databases (15), but databases carried over by pg_upgrade keep the old grant, which the CVE-2026-6460 privilege audit checks for
  • pg_start_backup and pg_stop_backup became pg_backup_start and pg_backup_stop (15)
  • plpython2u was removed (15)
  • lc_collate and lc_ctype server variables were removed (16)
  • old_snapshot_threshold and the adminpack extension were removed (17)

The backup function rename is the one that bites in practice. Monitoring scripts and older wrappers that call pg_start_backup fail with a function-not-found error on 18, and that error shows up the first time a backup runs after the upgrade, not during pg_upgrade itself. Grep your code and your cron jobs for the old names before you touch the cluster, because finding them during your first backup window is the expensive way.

Back up before you touch anything

pg_upgrade moves your data in place, so the only undo is a backup you take before it runs. Two backups are worth having before a major upgrade:

  • A logical pg_dumpall of every database, which doubles as proof that 14 can still read its own data
  • A filesystem copy of the data directory, which restores in minutes if you need the old cluster back exactly as it was

The logical dump is cheaper, and you can take it while 14 keeps serving traffic. On the test cluster it took 31 seconds and produced a 1.4 GB file:

sudo -u postgres sh -c 'pg_dumpall > /var/lib/postgresql/pg14-all.sql'

Store a checksum beside the file and compare it again before you ever rely on the dump:

md5sum /var/lib/postgresql/pg14-all.sql

On the test cluster the checksum read:

1d7283d46c989a3b4dbdee45fda146f7  /var/lib/postgresql/pg14-all.sql

The filesystem copy covers the other failure mode, which is wanting the old cluster back byte for byte. Stop the cluster, tar up the data directory and the config tree, then start 14 again and upgrade. On the same cluster that tar took 413 seconds and produced a 1,738,519,695 byte archive, and restoring it is an untar back into place:

sudo tar -C / -czf /var/lib/postgresql/pg14-cluster.tgz var/lib/postgresql/14/main etc/postgresql/14

That one archive carries both the data directory and the config tree under /etc, so the restore rebuilds the whole cluster. If the disk holds both copies, take both. If it only holds one, take the tar, because it brings a half-upgraded 14 back byte for byte, and keep the dump too when you can, because it reloads onto any version and it is the artifact every upgrade path in this guide ended up using.

Upgrade the cluster on Ubuntu and Debian

Ubuntu 24.04’s own repositories stop at PostgreSQL 16, so 18 comes from the PGDG repository. The setup script is two commands and keys itself to your distribution’s codename:

sudo apt install postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

The script announces the codename it will use and writes the repo file:

This script will enable the PostgreSQL APT repository on apt.postgresql.org on
your system. The distribution codename used will be noble-pgdg.
...
Writing /etc/apt/sources.list.d/pgdg.sources ...
You can now start installing packages from apt.postgresql.org.

Install the server next, and pull the version-matched package for every extension you run, because pg_upgradecluster runs a package pre-check and refuses to start while one is missing:

sudo apt install postgresql-18

If you run pgvector, for example, you need postgresql-18-pgvector installed beside your existing postgresql-14-pgvector, and the same pattern applies to every PGDG extension package. PostGIS, pgrouting and the rest of the PGDG list work the same way, and the pre-check reads your installed extension list, so it catches anything, not just pgvector. The error text when one is missing is exact, and we reproduce it in the troubleshooting section.

Before upgrading, confirm what you are starting from: the running cluster, its version and its checksum setting, because the checksum setting decides what happens later:

pg_lsclusters
psql --version
sudo /usr/lib/postgresql/14/bin/pg_controldata /var/lib/postgresql/14/main | grep -i checksum

The source cluster on the test VM, before anything changed. The last line decides several later steps, so note it now.

pg_lsclusters and pg_controldata output showing PostgreSQL 14.24 online on Ubuntu 24.04 with data page checksum version 0

The checksum line is the one to remember. This cluster reports “Data page checksum version: 0”, meaning checksums are off, and that mismatch with PostgreSQL 18’s new default drives the most common upgrade failure on the RHEL side. Write it down before you start, as we did. The version is worth noting too, 14.24, the final 14 release.

Now run the upgrade. Pass the old version and cluster name and nothing else; the wrapper detects the newest installed version on its own:

sudo pg_upgradecluster 14 main

The full dump-mode run on the test VM printed all of this. It’s long, but the stages explain the timing, so read it top to bottom once:

pg_upgradecluster 14 main in dump mode on Ubuntu 24.04, with staged vacuumdb statistics lines, the Success block and a 430 second elapsed time

Dump mode is the default, and the output shows why it is slow: the wrapper dumps every database out of 14 and loads it into 18, which took 430 seconds here. The timing scales with your data size, not with the version jump. It also runs the statistics passes for you at the end, so the cluster comes up with usable planner statistics. When it finishes, 14 sits stopped on port 5433 and 18 owns 5432.

Check that the data actually moved before you drop anything:

sudo -u postgres psql -d appdb -tAc 'SELECT count(*) FROM events'
sudo -u postgres psql -d appdb -c '\dx' | grep -E 'pg_trgm|pgcrypto|vector'
systemctl status postgresql@18-main --no-pager | head -5

Every check came back green on the test VM. The row count is the one to match against your own data:

Verification of the upgraded cluster on Ubuntu 24.04: pg_lsclusters with 18 main online on port 5432, psql 18.6, a 15 million row count, extension versions and the postgresql@18-main service running

The row count matches the seed, the extensions are all present on 18, and psql now reports 18.6. Anything short here means the upgrade needs a second look before you continue. This is the moment to point your application at the new cluster and let it run, while 14 stays stopped and untouched on port 5433. Leave 14 in place until the last section.

Watch the arguments, because there is a real trap here. The third positional argument is a data directory, not the new version: typing pg_upgradecluster 14 main 18 main makes the wrapper run the whole dump-mode upgrade into a data directory literally named 18 inside your current directory. We captured that exact run during testing, and it completes, which is what makes it dangerous. The upgraded 18 cluster comes up on port 5432 with its data living in a relative path, and when you later drop the leftover cluster the cleanup warns that the data directory does not exist. Two arguments, version and cluster name, are always the correct form. Debian 13 works identically to Ubuntu; a smaller seed cluster there upgraded in 29 seconds in dump mode. If you are landing on 17 instead of 18, the pg_upgradecluster steps in our PostgreSQL 17 install guide are the same shape with one number changed.

Dump mode is safe and slow because it copies every row through the new catalogs. Link mode hardlinks the old table files into the new cluster instead, so the data never gets copied at all:

sudo pg_upgradecluster --method=link 14 main

Link mode on the test VM looked like this. The elapsed time sits at the bottom of the capture.

pg_upgradecluster --method=link 14 main on Ubuntu 24.04, with the --no-data-checksums initdb, pg_upgrade --link stages, the update_extensions.sql notice and an 18 second elapsed time

Look at the initdb line in that output: the wrapper passes –no-data-checksums on its own, because pg_upgrade requires both clusters to agree on checksums and a 14 cluster usually has them off. That is the mismatch that trips manual upgrades on the RHEL side, and the wrapper simply handles it for you. The same run also prints a notice about update_extensions.sql, which the extension section covers.

The difference in cost isn’t subtle on the same 3.6 GB cluster:

ModeUpgrade timeDisk after (18 + 14)
dump430 seconds3.6 GB + 3.6 GB
link18 seconds2.6 GB + 1.1 GB

Link mode changes the rollback story, and you need to hear it before you choose. The two clusters share file content, so once 18 has started, 14 can’t be safely started again; pg_upgrade renames the old pg_control out of the way and the output says so explicitly. From that point your rollback is the backup from the last section, not the old cluster. Dump mode keeps 14 intact and stopped on port 5433, and pg_ctlcluster 14 main start brings it back until you drop it.

Link mode leaves helper scripts in /var/log/postgresql/pg_upgradecluster-14-18-main./, and the extension update script is the one you will actually use. Dump mode needs none of them, because its restore updates extensions on the way through. The directory is owned by postgres with mode 700, which matters when you try to read it in a minute.

Upgrade on Rocky Linux and RHEL

RHEL-family systems have no pg_upgradecluster wrapper, so you drive pg_upgrade directly and make the decisions the wrapper would have made for you. Everything below ran on Rocky Linux 9 with SELinux enforcing, and it applies the same way on RHEL and AlmaLinux. Package names are the only thing that changes between RHEL, Alma and Rocky. Fedora users have their own path entirely: the Fedora install guide covers postgresql-setup –upgrade, which wraps this same pg_upgrade in Fedora packaging.

Start with the PGDG repository and disable the AppStream postgresql module so it doesn’t shadow the PGDG packages:

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %rhel)-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql

Then install the 18 server and contrib, plus the version-matched build of any extension you run. Extensions ship as version-suffixed packages here, so pgvector installs as pgvector_18 beside an existing pgvector_14:

sudo dnf install postgresql18-server postgresql18-contrib

Now initdb the new cluster the default way first, even though it will fail the check a minute from now, because the checksum default is worth seeing once. PostgreSQL 18 turns data checksums on at initdb, and your 14 cluster almost certainly has them off. The check itself needs a current directory postgres can write to, so the blocks below cd into /tmp first:

sudo -u postgres /usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/data
sudo systemctl stop postgresql-14
cd /tmp
sudo -u postgres /usr/pgsql-18/bin/pg_upgrade -b /usr/pgsql-14/bin -B /usr/pgsql-18/bin -d /var/lib/pgsql/14/data -D /var/lib/pgsql/18/data --check

The failing check on the test VM stopped right at the checksum comparison. Nothing else happened:

pg_upgrade --check on Rocky Linux 9 failing after a default PostgreSQL 18 initdb, with the data checksum mismatch error

Nothing was written to either cluster; the check ran and exited. The data directories stay exactly as they were. Fix the mismatch by deleting the fresh data directory and initializing again with checksums off, then the same check passes:

sudo rm -rf /var/lib/pgsql/18/data
sudo -u postgres /usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/data --no-data-checksums
cd /tmp
sudo -u postgres /usr/pgsql-18/bin/pg_upgrade -b /usr/pgsql-14/bin -B /usr/pgsql-18/bin -d /var/lib/pgsql/14/data -D /var/lib/pgsql/18/data --check

Run it again after the re-initialization and the check comes back clean. The final line is the one you want:

pg_upgrade --check passing on Rocky Linux 9 after re-running initdb with --no-data-checksums, ending with Clusters are compatible

Clusters are compatible is the green light. One more trap stands between you and the real run: pg_upgrade writes its output scripts into the current directory, and starting it as postgres from your own home directory fails because postgres can’t write there. Run it from a directory postgres owns instead:

sudo -u postgres sh -c 'cd /var/lib/pgsql && /usr/pgsql-18/bin/pg_upgrade -b /usr/pgsql-14/bin -B /usr/pgsql-18/bin -d /var/lib/pgsql/14/data -D /var/lib/pgsql/18/data --link'

The real run on the test VM went exactly as the check promised. The tail of the output tells you what comes next:

pg_upgrade --link run on Rocky Linux 9 from a postgres-writable working directory, ending with Upgrade Complete and the two vacuumdb suggestions

Four seconds on the 3.6 GB cluster. The output names the two vacuumdb commands it wants you to run afterwards, points at update_extensions.sql, and leaves delete_old_cluster.sh in /var/lib/pgsql for when you are done with 14. Start the new cluster and enable it at boot:

sudo systemctl enable --now postgresql-18

Up and running, with the disk picture beside it. The two du lines are link mode’s signature on any box.

PostgreSQL 18 service running on Rocky Linux 9 with du output showing 2.7 GB in the new data directory and 1.1 GB of hardlinked files left in the old one

The service comes up, and du shows what link mode left behind: 2.7 GB in 18/data and 1.1 GB of hardlinked files still listed under 14/data. SELinux needs nothing from you; the PGDG units carry the right contexts, and ausearch -m avc -ts recent came back empty after the entire upgrade on our enforcing-mode host.

Update the extensions after the upgrade

pg_upgrade doesn’t update extension objects; it leaves every extension at the version it had on 14. Dump mode updates them during the restore, but link mode writes an update_extensions.sql script and prints a notice pointing at it. Find the output directory and read the script:

sudo cat /var/log/postgresql/pg_upgradecluster-14-18-main.*/update_extensions.sql

The script held one extension on the test cluster:

\connect appdb
ALTER EXTENSION "pgcrypto" UPDATE;

Each extension that can be updated gets one ALTER EXTENSION line, prefixed with a \connect to the database it lives in. You can’t point psql at the file path directly, because the output directory is owned by postgres with mode 700 and your shell can’t enter it. Pipe the file in instead:

sudo cat /var/log/postgresql/pg_upgradecluster-14-18-main.*/update_extensions.sql | sudo -u postgres psql -X -f -

Running it against the upgraded cluster produced two lines, a connection notice and the ALTER itself. Both are expected:

You are now connected to database "appdb" as user "postgres".
ALTER EXTENSION

Confirm the extension moved with a quick query against the catalog:

sudo -u postgres psql -d appdb -tAc "SELECT extname||' '||extversion FROM pg_extension WHERE extname='pgcrypto'"

Expect the version 18 build to answer here, as it did on the test cluster.

pgcrypto 1.4

pgcrypto moved from 1.3 to 1.4, and its new version arrived with the postgresql-18 package because it ships in contrib. Extensions from PGDG need their version-matched package installed first, which is the pre-check from the Ubuntu section doing its job, because without postgresql-18-pgvector the upgrade refuses to start at all rather than leaving you with a vector extension that can’t load on 18. The version you see in \dx after the script runs is the one 18 has recorded, updated in place.

Rebuild the optimizer statistics

PostgreSQL 18 transfers most planner statistics across the upgrade by itself. Three kinds don’t survive the jump:

  • Statistics created with CREATE STATISTICS
  • Statistics that extensions added to their own objects
  • The cumulative counters the planner uses for adaptive decisions

An empty statistics cache makes the first queries pick bad plans, so pg_upgrade’s own output tells you to run two vacuumdb passes. On RHEL-family systems the binary sits at /usr/pgsql-18/bin/vacuumdb rather than the /usr/lib path below. The first pass gives every relation that has no statistics at all the minimal set, which makes the planner safe again in seconds:

sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only

On the test cluster the first pass covered the two user databases and the two system ones:

vacuumdb: processing database "appdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "benchdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)

The second pass builds full statistics the way a regular ANALYZE would:

sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-only

Both passes together on the upgraded test cluster, exactly as the manual path runs them:

vacuumdb --all --analyze-in-stages --missing-stats-only followed by vacuumdb --all --analyze-only on the upgraded PostgreSQL 18 cluster

pg_upgradecluster already runs both passes in dump mode, so this section matters most after a manual pg_upgrade on the RHEL side, where the tool only prints the suggestion. Re-run the plain analyze after any big data load later, because statistics age like everything else. If you want to go deeper on how the planner uses all this, our PostgreSQL books list covers it in detail.

Remove the old cluster

Keep 14 until your application has run against 18 for a day and one backup cycle has succeeded on the new version. Then remove it and take the disk space back:

sudo pg_dropcluster 14 main

pg_lsclusters should list only 18 afterwards:

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

On Rocky the delete script pg_upgrade left behind does the same job: run sudo /var/lib/pgsql/delete_old_cluster.sh once you are committed to 18. The space comes back differently in the two modes. In link mode the 1.1 GB still listed under the old data directory is mostly hardlinks into the new one, so deleting it frees only the files 18 rewrote, while dump mode’s duplicate 3.6 GB comes back in full. Removing the postgresql-14 packages with apt or dnf is safe once the cluster is dropped, and it stops your monitoring from alerting on a service that will never start again.

The next step for a single-node database is usually replication rather than another major bump, and the PostgreSQL streaming replication guide for Rocky and AlmaLinux starts there. With 18 running and 14 gone, you are set until 2030.

Fix common PostgreSQL 14 to 18 upgrade errors

Every failure we hit during testing stopped before anything was written, which is the upside of pg_upgrade, because it checks first and acts second. These are the four you will actually meet. All four are environment problems, not data problems.

old cluster does not use data checksums but the new one does

PostgreSQL 18 enables data checksums at initdb by default and pg_upgrade refuses to bridge the difference. The failing check looks like this:

pg_upgrade --check failing with the message old cluster does not use data checksums but the new one does, after a default PostgreSQL 18 initdb

The fix is to initialize the new cluster with –no-data-checksums, exactly as the Rocky section does. The refusal is the tool protecting you from a mismatch it cannot resolve, not a bug. pg_upgradecluster handles this automatically for link and clone runs. If you genuinely want checksums on the upgraded cluster, enable them afterwards with pg_checksums while the cluster is stopped; that costs one scan of every data file but changes nothing else about the upgrade.

postgresql-14-pgvector is installed, but postgresql-18-pgvector is missing

pg_upgradecluster runs a package pre-check before it touches anything and refuses to continue while an installed extension has no version-matched package for the target. The text names the fix:

postgresql-14-pgvector is installed, but postgresql-18-pgvector is missing
1 package(s) from version 14 missing for version 18
Install the missing packages and try pg_upgradecluster again. To ignore the issue, use --force-packages

Install the missing package and rerun. The package name always follows the postgresql-– pattern. The –force-packages flag exists for extensions you are about to drop anyway; forcing while the extension is still in use leaves the new cluster unable to load it, which is a worse state than the refusal you just got.

You must have read and write access in the current directory

pg_upgrade writes its output scripts into the working directory, and running it as postgres from your own home directory dies immediately:

You must have read and write access in the current directory.
Failure, exiting

Start pg_upgrade from a directory postgres owns. The Rocky section wraps it in sh -c with a cd, which gets you the right user and the right directory in one command, and any directory owned by postgres works as well as /var/lib/pgsql. The scripts it writes, delete_old_cluster.sh among them, land in that same directory.

repomd.xml GPG signature verification error: Bad GPG signature

On current Rocky 9 the freshly installed PGDG repo fails its metadata check during the first dnf transaction, because the repo ships with repo_gpgcheck=1 and the signature path fails there. Turn the metadata check off and rebuild the cache:

sudo sed -i 's/repo_gpgcheck = 1/repo_gpgcheck = 0/' /etc/yum.repos.d/pgdg-redhat-all.repo
sudo dnf -qy makecache

The packages themselves stay GPG-verified by rpm, so this drops one layer of checking on repo metadata only, not on what you actually install. After the cache rebuild, the postgresql18-server install goes through normally and you can pick the guide back up at the initdb step. Nothing else about the transaction changes.

Almost every other pg_upgrade failure is a variant of the first kind: the tool checks cluster versions, checksums, extension packages, prepared transactions and free disk before it moves a byte, so a failed run usually leaves 14 untouched and running. Fix what the check names and rerun. A failed check costs minutes; skipping the backup costs the cluster. We upgraded the same cluster three ways in the lab, and the dump was the only artifact every path used. Keep it from the first section until 18 has survived a full backup cycle, because that file is the only undo that works in every failure mode this page covers.

Keep reading

Install PostgreSQL 18 on Debian 13 / 12 Databases Install PostgreSQL 18 on Debian 13 / 12 Install DBeaver on Ubuntu 26.04 / 24.04 and Debian 13 Databases Install DBeaver on Ubuntu 26.04 / 24.04 and Debian 13 Install pgAdmin 4 on Ubuntu 26.04 / 24.04 Databases Install pgAdmin 4 on Ubuntu 26.04 / 24.04 Install and Use VictoriaMetrics time-series database on Ubuntu Databases Install and Use VictoriaMetrics time-series database on Ubuntu TICK Stack vs InfluxDB 3: Do Kapacitor and Chronograf Still Work? Databases TICK Stack vs InfluxDB 3: Do Kapacitor and Chronograf Still Work? How To Run MySQL Database in Docker Container Containers How To Run MySQL Database in Docker Container

Leave a Comment

Press ESC to close