Upgrading PostgreSQL from 16 to 18 on Ubuntu involves adding the official PGDG repository, installing PostgreSQL 18 binaries alongside your existing 16, stopping both services, using the pg_upgrade utility to migrate data (often after adjusting pg_hba.conf), and then restarting services and testing.

A complete backup before starting is crucial, and you'll eventually remove the old version's data directories to save space.

Here's a step-by-step guide:

Backup Your Database (crucial!)

Before anything else, perform a full backup using pg_dumpall to ensure you can recover if issues arise.

sudo -u postgres pg_dumpall > full_backup_postgres16.sql

Add the Official PostgreSQL Repository

This ensures you get the latest official packages for PostgreSQL 18.

sudo apt update
sudo apt install curl ca-certificates gnupg
curl www.postgresql.org | sudo gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/pgdg.gpg > /dev/null
echo "deb apt.postgresql.org $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update

Install PostgreSQL 18

Install the server, client, and contrib packages for version 18.

sudo apt install postgresql-18 postgresql-client-18 postgresql-contrib-18

Stop Services

Stop both your old (Postgres 16) and the newly installed (Postgres 18) services to prevent conflicts.

sudo systemctl stop postgresql@16-main
sudo systemctl stop postgresql@18-main

Configure Authentication for pg_upgrade

Edit pg_hba.conf for both versions to allow pg_upgrade to connect as the postgres user.

• For PostgreSQL 16: /var/lib/postgresql/16/main/pg_hba.conf
• For PostgreSQL 18: /var/lib/postgresql/18/main/pg_hba.conf

Add a line like this to both (adjust paths as needed):

host    all             postgres        127.0.0.1/32    trust

Restart Postgres 16: sudo systemctl restart postgresql@16-main

Run pg_upgrade

This command upgrades the old data directory to the new version.

sudo pg_upgrade --check \
    --old-bindir=/usr/lib/postgresql/16/bin \
    --new-bindir=/usr/lib/postgresql/18/bin \
    --old-datadir=/var/lib/postgresql/16/main \
    --new-datadir=/var/lib/postgresql/18/main

Run the check first, then remove --check for the actual upgrade.

Restart Services & Verify

Start the new PG18 service and stop PG16.

sudo systemctl start postgresql@18-main
sudo systemctl stop postgresql@16-main

Check logs and connect to your database to ensure everything works as expected (e.g., psql -U postgres).

Clean Up

Once fully satisfied, you can remove the old data directory and stop/disable the old service to free up space.

sudo systemctl stop postgresql@16-main
sudo systemctl disable postgresql@16-main
sudo rm -rf /var/lib/postgresql/16/main

source