Postgres Upgrade

How to perform a PostgreSQL major upgrade on a FreeBSD jail

I personally run my PostgreSQL server inside of a jail on my FreeBSD box which makes the upgrade and/or migration process a lot easier. This will guide you through the process of upgrading PostgreSQL and anything specific gotchas that we come across.

We can not install two different versions of PostgreSQL on FreeBSD but need access to the old binary during the upgrade. We will need to create a temporary jail and mount the needed paths within the production jail for PostgreSQL.

Guide

Make a temporary jail environment:
```
bsdinstall jail /usr/jails/pg_upgrade
```

Install the older PostgreSQL into the jail

pkg -c /usr/jails/pg_upgrade install postgresql15-server

Mount Temporary Jail inside your current PostgreSQL Jail

sudo mount_nullfs /usr/jails/pg_upgrade /"Location of current jail"/mnt

Stop the postgres process on the production jail
```
service postgresql stop
```
Take a ZFS snapshot (optional, but it lets you safely use -k with pg_upgrade instead of copying each file)
Remove the older PostgreSQL version:
```
pkg remove postgresql
```
Install the newer PostgreSQL version on the production jail:
```
pkg install postgresql16-server postgresql16-contrib
```
Rename the data directory if it does not already have the version number at the end as in "data15":
```
mv /var/db/postgres/data /var/db/postgres/data15
```

Run pg_upgrade as postgres user:

sudo -u postgres pg_upgrade -b /mnt/usr/local/bin/ -B /usr/local/bin -d //mnt/var/db/postgres/old_data/ -D /var/db/postgres/new_data -j 16 -k

Start the new postgresql server:
```
service postgresql start
```
Run any scripts pg_upgrade tells you to run as the pgsql user (check ~pgsql for the script contents):
```
sudo -u postgres analyze_new_cluster.sh
```
Delete the old data directory:
```
sudo -u postgres delete_old_cluster.sh
```

Delete the temporary jail:

chflags -R noschg /usr/jails/pg_upgrade
rm -rf /usr/jails/pg_upgrade