PostgreSQL Upgrade from Version 12 to Version 18

Overview

This guide explains how to upgrade PostgreSQL from Version 12 to Version 18 on Ubuntu 24.04 LTS. We'll cover the complete upgrade process, including preparation, migration, verification, and post-upgrade cleanup.

Prerequisites

• Server running PostgreSQL 12
• Ubuntu 24.04 LTS (Noble)
• Shell access with sudo privileges
• A verified logical backup and preferably a VM/storage snapshot
• Enough free disk space for a second copy of the PostgreSQL cluster
• A maintenance window with application writes stopped
• PostgreSQL 18-compatible packages for all external extensions

Step 1: Verify PostgreSQL 12 and Ubuntu 24.04

Confirm the operating system, cluster name, production port, server version, and data directory before making any changes.

cat /etc/os-release

pg_lsclusters

sudo -u postgres psql -X -Atc "SHOW server_version;"

sudo -u postgres psql -X -Atc "SHOW port;"

sudo -u postgres psql -X -Atc "SHOW data_directory;"

Step 2: Back Up and Inventory PostgreSQL 12

Check Disk Space

df -hT

sudo du -sh /var/lib/postgresql/12/main

Create and Verify a Logical Backup

sudo mkdir -p /root/pg12-to-pg18-backup
sudo chmod 700 /root/pg12-to-pg18-backup
sudo -u postgres /usr/lib/postgresql/12/bin/pg_dumpall --verbose | gzip -1 | sudo tee /root/pg12-to-pg18-backup/postgresql12-pg_dumpall.sql.gz > /dev/null
sudo gzip -t /root/pg12-to-pg18-backup/postgresql12-pg_dumpall.sql.gz

Also take a VM or storage snapshot if available. A successfulgzip -t exits without an error.

Check Extensions and Preloaded Libraries

sudo -u postgres psql -X -Atc "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY datname;" |
while read -r DB; do
  echo "===== DATABASE: $DB ====="
  sudo -u postgres psql -X -d "$DB" -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
done

sudo -u postgres psql -X -c "SHOW shared_preload_libraries;"

dpkg -l | grep -E '^ii[[:space:]]+postgresql-12'

Step 3: Add the Official PostgreSQL PGDG Repository

Ubuntu 24.04 Noble is supported by the official PostgreSQL APT repository. Use the signed .sources configuration instead of the deprecated apt-key method.

sudo apt update

sudo apt install -y curl ca-certificates postgresql-common

sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF
Types: deb deb-src
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: noble-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF
sudo apt update

apt-cache policy postgresql-18

Step 4: Install PostgreSQL 18 Binaries

sudo apt install -y postgresql-18 postgresql-client-18

/usr/lib/postgresql/18/bin/postgres --version

pg_lsclusters

Installation normally creates a new empty 18/maincluster on another port. PostgreSQL 12 should still be the active production cluster at this point.

Step 5: Remove Only the New Empty PostgreSQL 18 Cluster

pg_lsclusters

sudo pg_dropcluster --stop 18 main

pg_lsclusters

Check Required Upgrade Packages

sudo pg_upgradecluster --check --method=upgrade -v 18 12 main

The pg_upgradecluster --check option checks whether the PostgreSQL 18 packages required by the old cluster are installed. If it reports a missing package, stop and install it before proceeding.

Step 6: Enter Maintenance Mode and Stop PostgreSQL 12

Stop application writes, workers, and scheduled jobs using your normal deployment procedure, then check for remaining sessions.

sudo -u postgres psql -X -c "SELECT pid, usename, datname, application_name, client_addr, state FROM pg_stat_activity WHERE pid <> pg_backend_pid() ORDER BY datname, usename;"

sudo -u postgres psql -X -c "CHECKPOINT;"

sudo pg_ctlcluster 12 main stop

pg_lsclusters

Step 7: Upgrade PostgreSQL 12 Directly to PostgreSQL 18

Use pg_upgrade copy mode. The old PostgreSQL 12 data files remain independent because this command does not use --link.

sudo pg_upgradecluster -v 18 --method=upgrade --jobs=4 --no-start 12 main

Do not interrupt this process. Upgrade duration depends on database size, storage performance, tablespaces, and available CPU resources.

Step 8: Verify the Upgraded Cluster Layout

pg_lsclusters

PostgreSQL 18 should now own the original production port. The old PostgreSQL 12 cluster remains stopped on an unused port for rollback. The exact unused port can differ from server to server.

Step 9: Start and Verify PostgreSQL 18

sudo pg_ctlcluster 18 main start

pg_lsclusters

pg_isready -h 127.0.0.1 -p 5432

sudo -u postgres psql -X -c "SELECT version();"

sudo -u postgres psql -X -c "\l+"

Check Collation Versions

sudo -u postgres psql -X -c "SELECT datname, datcollversion AS recorded_version, pg_database_collation_actual_version(oid) AS actual_version FROM pg_database WHERE datallowconn ORDER BY datname;"

Check PostgreSQL 18 Logs

sudo tail -100 /var/log/postgresql/postgresql-18-main.log

sudo grep -iE 'PANIC|FATAL|ERROR|could not load|incompatible|collation' /var/log/postgresql/postgresql-18-main.log | tail -100

Step 10: Restore Application Traffic and Keep PostgreSQL 12 for Rollback

Test application connections and critical read/write paths before restoring full production traffic. Keep the old PostgreSQL 12 cluster until PostgreSQL 18 has been fully validated and your rollback window has ended.

sudo -u postgres psql -X -c "SELECT datname, numbackends, xact_commit, xact_rollback, deadlocks FROM pg_stat_database ORDER BY datname;"

sudo pg_dropcluster --stop 12 main

Post-Upgrade Best Practices

PostgreSQL 18 pg_upgrade can preserve optimizer statistics, so a full analyze is not mandatory solely because of the major upgrade. A staged analyze is still useful as a conservative maintenance step, especially after index rebuilds or other post-upgrade changes.

sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --jobs=4

sudo tail -f /var/log/postgresql/postgresql-18-main.log

Frequently Asked Questions