Introduction

There is not much to consider when performing minor updates in the current release.
Things get a little more complicated when larger jumps need to be made. There are many different methods out there, but according to my research, the following is the simplest.

Goals

An upgrade from major version to major version.

Last update:

• 30 November 2025: Added upgrade from 13 to 17 as an example

Requirements

• PostgreSQL is installed

Upgrade

After logging into the jail, e.g. with bastille console db, we switch to the home directory with cd ~ and create a package of the old PostgreSQL 13 version from the current PostgresSQL version with pkg create postgresql13-server. This package is then unpacked into a temporary folder, as we need to explicitly access the ‘old’ program pg_upgrade in version 13 again: mkdir /tmp/pg-upgrade && tar xf postgresql13-server-13.23_1.pkg -C /tmp/pg-upgrade

Backup

Now it is time to stop the old database with service postgresql stop and back up the database directory with cp -r /var/db/postgres/data13 /var/db/postgres/data13.bak.

Update

Now the old installed version can be removed with pkg delete -f postgresql13-server postgresql13-client postgresql13-contrib and replaced with the new version with pkg install -y postgresql17-server postgresql17-client postgresql17-contrib. This new version is then initialised directly: service postgresql initdb

Migration

Now comes the crucial part: The migration. Depending on the size, this may take some time. All databases from the folder /var/db/postgres/data13/ are copied to the new folder /var/db/postgres/data17/ and adapted to the new version: su -l postgres -c ‘pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data13/ -B /usr/local/bin/ -D /var/db/postgres/data17/ -U postgres’

Configuration

In addition to the databases, the current configurations must of course also be transferred. First, we back up the new ‘empty’ configuration file with cp /var/db/postgres/data17/pg_hba.conf /var/db/postgres/data17/ pg_hba.conf.bak and then simply copy the old pg_hba.conf file back into the new directory with cp /var/db/postgres/data13/pg_hba.conf /var/db/postgres/data17/pg_hba.conf.

Clean up

Now the database can be restarted with service postgresql start and its functionality checked. With /usr/local/bin/vacuumdb -U postgres --all --analyze-in-stages, the statistics for the new database are generated and then the old database can also be removed with rm -rf /var/db/postgres/data13. We also throw away a leftover with rm /var/db/postgres/delete_old_cluster.sh.

Console

cd ~
pkg create postgresql13-server
mkdir /tmp/pg-upgrade
tar xf postgresql13-server-13.23_1.pkg -C /tmp/pg-upgrade
service postgresql stop
cp -r /var/db/postgres/data13 /var/db/postgres/data13.bak
pkg delete -f postgresql13-server postgresql13-client postgresql13-contrib
pkg install -y postgresql17-server postgresql17-client postgresql17-contrib
service postgresql initdb
su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data13/ -B /usr/local/bin/ -D /var/db/postgres/data17/ -U postgres"
cp /var/db/postgres/data17/pg_hba.conf /var/db/postgres/data17/pg_hba.conf.bak
cp /var/db/postgres/data13/pg_hba.conf /var/db/postgres/data17/pg_hba.conf
service postgresql start
/usr/local/bin/vacuumdb -U postgres --all --analyze-in-stages
rm -rf /var/db/postgres/data13
rm /var/db/postgres/delete_old_cluster.sh

Voilá