Upgrading PostgreSQL with pg_upgrade on FreeBSD

I was running PostgreSQL ~~9.6~~ 13.4 on FreeBSD and wanted to update that database to version 10 14.0 with minimal downtime. If you use pg_upgrade with the -k commandline option, database files are hard-linked to the new data directory, which is a time- and space-saving method. For this you need both the old and new binaries installed on the machine. Unfortunately, as every version of PostgreSQL is installed under the /usr/local hierarchy on FreeBSD, you can't have two different versions installed at the same time - unless we setup a temporary jail.
If you run one or more standby database servers, e.g. a streaming replication, then you'll need ssh access from the master to the standbys, and rsync installed on both sides.

```
$ bsdinstall jail /var/jail
```
The installer comes up with several dialogs. During the installation
- select a FreeBSD mirror close to you
- deselect all packages in the Distribution Select dialog
- set a simple password for the root user as we will use this jail just for installing the binaries of the previous PostgresSQL version
- in the System Configuration dialog, just hit Ok
- don't add any users.
Mount the device filesystem inside the jail:
```
$ mount -t devfs none /var/jail/dev
```

Install PostgreSQL in the jail:

$ pkg -c /var/jail install postgresql13-server

Update PostgreSQL on the host system:

$ portmaster -o databases/postgresql14-client postgresql13-client

Select the NLS, OPTIMIZED_CFLAGS, and SSL options

$ portmaster -o databases/postgresql14-server postgresql13-server

Select the INTDATE, LZ4, NLS, OPTIMIZED_CFLAGS, SSL, TZDATA, and XML options

$ portmaster -o databases/postgresql14-contrib postgresql13-contrib

Select the OPENSSL and XML options

Initialize the new database:
```
$ service postgresql initdb
```
Manually merge any non-default settings from /var/db/postgres/data13/postgresql.conf to /var/db/postgres/data14/postgresql.conf
Also, merge any changed settings from /var/db/postgres/data13/pg_hba.conf to /var/db/postgres/data14/pg_hba.conf
For the upgrade, temporarily trust any local connections. Uncomment the default entry in both the old and new pg_hba.conf:
```
local   all             all                                     trust
```

Determine all shared objects which the old Postgres version requires:

# ldd /var/jail/usr/local/bin/postgres
/var/jail/usr/local/bin/postgres:
        libthr.so.3 => /lib/libthr.so.3 (0x800d1c000)
        libintl.so.8 => /usr/local/lib/libintl.so.8 (0x800d47000)
        libssl.so.8 => /var/jail/usr/lib/libssl.so.8 (0x800d54000)
        libcrypto.so.8 => not found (0)
        libm.so.5 => /lib/libm.so.5 (0x800fc6000)
        libicui18n.so.64 => not found (0)
        libicuuc.so.64 => not found (0)
        libc.so.7 => /lib/libc.so.7 (0x800ff8000)
        libcrypto.so.8 => not found (0)

Add any shared object that is not found to /etc/libmap.conf and instruct the dynamic linker to pick up those libraries from /var/jail instead:

[/var/jail/]
libssl.so.8             /var/jail/usr/lib/libssl.so.8
libcrypto.so.8          /var/jail/lib/libcrypto.so.8
libicui18n.so.64        /var/jail/usr/local/lib/libicui18n.so.64
libicuuc.so.64          /var/jail/usr/local/lib/libicuuc.so.64
libicudata.so.64        /var/jail/usr/local/lib/libicudata.so.64

Repeat steps 8 and 9 until there are no unresolved shared objects anymore
Change to the system account as which the database is running:
```
$ su - postgres
```

Stop the database server:

# kill `head -1 /var/db/postgres/data13/postmaster.pid`

Optionally, backup the database directory, or create a rescue copy:
```
# cp -aiv /var/db/postgres/data13 /var/db/postgres/data13.save
```
~~Let the dynamic linker find any libraries that are installed in /var/jail/usr/local/lib:~~
```
# export LB_LIBRARY_PATH="/var/jail/usr/local/lib"
```
Now update the database:
```
# pg_upgrade -b /var/jail/usr/local/bin -B /usr/local/bin -d /var/db/postgres/data13 -D /var/db/postgres/data14 -j 2 -k -v
```
If everything went ok, pg_upgrade places two shell scripts analyze_new_cluster.sh and delete_old_cluster.sh in the current directory
Edit /var/db/postgres/data14/pg_hba.conf and comment the line out which trusts all local connections
If run one or more standby databases, stop them now. Then, either follow step 11 of the official documentation, i.e. rsync from the master to the standby server(s), or create a fresh standby server from scratch, e.g. by adapting my howto PostgreSQL with replication slots on FreeBSD
As root, start the master database server:
```
# exit
$ service postgresql start
```
Update the optimizer statistics of the new database (assuming that your database superuser is called postgres):
```
$ PGUSER=postgres vacuumdb --all --analyze
```
If you just updated to a PostgreSQL version before 14.0, pg_upgrade created a shell script instead:
```
$ PGUSER=postgres ./analyze_new_cluster.sh
```

Remove the jail and source tarballs, and uninstall unused packages:

$ umount /var/jail/dev
$ chflags -R noschg /var/jail
$ rm -r /var/jail /usr/ports/distfiles/*
$ pkg autoremove

If everything is running fine, remove the shell scripts and the old database directory:
```
$ rm -r analyze_new_cluster.sh delete_old_cluster.sh /var/db/postgres/data13
```