Backup!

Backup! Backup! Backup!

# pg_dumpall -U postgres > /root/pg17_backup.sql

And stop PostgreSQL

# service postgresql stop

Checksums

Enable data checksums if they are not already enabled. Data checksums were not enabled by default until PostgreSQL 18

# su - postgres -c "pg_checksums -e -D /var/db/postgres/data17"

Upgrade

pg_upgrade needs the PostgreSQL binaries from the currently installed and new versions. Download and extract the current version into a temporary folder

# mkdir -p /tmp/pg17
# pkg fetch -o /tmp/pg17 postgresql17-server
# tar -xf /tmp/pg17/All/Hashed/postgresql17-server-*.pkg -C /tmp/pg17/

Install the new version

# pkg install postgresql18-server

Initialize the data directory

# service postgresql initdb

Edit data17/pg_hba.conf and data18/pg_hba.conf to allow connections without a password by adding the following line:

local all all trust

Then upgrade

# su - postgres -c "pg_upgrade \
   -b /tmp/pg17/usr/local/bin \
   -B /usr/local/bin \
   -d /var/db/postgres/data17 \
   -D /var/db/postgres/data18 \
   -k"

Compare data17/postgresql.conf and data18/postgresql.conf for changes, and review the permissions on data18/pg_hba.conf before starting the service

# service postgresql start

Source: OpenBSD PostgreSQL README

get in touch

• Email

  hi@adrianobarbosa.xyz