Upgrade PostgreSQL 13.5 to 14.1 on FreeBSD

Stop your postgresql database daemon on FreeBSD machine:

service postgresql stop

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

pkg create postgresql13-server postgresql13-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-13.5.pkg -C /tmp/pg-upgrade
tar xf postgresql13-contrib-13.5.pkg -C /tmp/pg-upgrade /* only needed if you have postgresql13-contrib package installed */

Remove old installed binaries

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

Install new version of PostgreSQL 14.1


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

Create new empty database

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

Migrate the data from old database to the new empty database

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/data14/ -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

Source of the manual is comming from FreeBSD Ports tree.

Share with Me via Nextcloud