Upgrade PostgreSQL 14.5 to 15.x on FreeBSD

New version of postgresql 15 database have arrived. I have updated the manual to newest possible upgraded version on 14 october 2022.

Stop your postgresql database daemon on FreeBSD machine:

service postgresql stop

Create package containing your old version ( 14.5 ) binaries ( depends are you using postgresql14-contrib package, but command will not fail if it is missing )

pkg create postgresql14-server postgresql14-contrib

Uncompress them somewhere in the filesystem - in our example it is into /tmp/pg-upgrade directory.

mkdir /tmp/pg-upgrade
tar xf postgresql13-server-14.5.pkg -C /tmp/pg-upgrade
tar xf postgresql13-contrib-14.5.pkg -C /tmp/pg-upgrade /* only needed if you have postgresql14-contrib package installed */

Remove old installed binaries

pkg delete -f databases/postgresql14-server databases/postgresql14-contrib databases/postgresql14-client

Install new version of PostgreSQL 15.x

portmaster databases/postgresql15-server databases/postgresql15-client
portmaster databases/postgresql15-contrib /* only if need it */

Create new empty database with

su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data15 -U postgres"

Migrate the data from old database to the new empty database. Assuming that previos database is leaving into /var/db/postgres/data14/ directory.

su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data14/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres"

Start your database

service postgresql start

Once you start the new server, consider running:

/usr/local/bin/vacuumdb -U postgres --all --analyze-in-stages

Running this script will delete the old cluster's data files:

/var/db/postgres/delete_old_cluster.sh

Some decrease of needed disk space is easy visible:

1,5G    data14
1,4G    data15

Source of the manual is comming from FreeBSD Ports tree.