Upgrading PostgreSQL with pg_upgrade on FreeBSD

I was running PostgreSQL ~~9.6~~ ~~13.4~~ 17.6 on FreeBSD and wanted to update that database to version 10 ~~14.0~~ 18 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 primary to all secondary servers, and rsync installed on both sides.

On the primary database server, install the previous version of PostgreSQL in a temporary jail:
1. ```
$ 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.
2. Mount the device filesystem inside the jail:
```
$ mount -t devfs none /var/jail/dev
```
3. Install PostgreSQL in the jail:
```
$ pkg -c /var/jail install postgresql17-server
```

On both the primary and all secondaries, update PostgreSQL on the host system:

$ portmaster -o databases/postgresql18-client postgresql17-client

Select the NLS, OPTIMIZED_CFLAGS, SSL, and ZSTD options.

```
$ portmaster -o databases/postgresql18-server postgresql17-server
```
Select the LZ4, NLS, OPTIMIZED_CFLAGS, SSL, TZDATA, XML, and ZSTD options.

$ portmaster -o databases/postgresql18-contrib postgresql17-contrib

Select the OPENSSL, XML, and ZSTD options.

Cleanup:

$ pkg autoremove
$ pkg clean
$ rm -rv /usr/ports/distfiles/* /var/cache/pkg/*

On the primary server, initialize the new database:

```
$ service postgresql initdb
```
Manually merge any non-default settings from /var/db/postgres/data17/postgresql.conf to /var/db/postgres/data18/postgresql.conf
Also, merge any changed settings from /var/db/postgres/data17/pg_hba.conf to /var/db/postgres/data18/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 former version of PostgreSQL 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

On both the primary and all secondaries, change to the system account as which the database is running:
```
$ su - postgres
```
Starting with the primary, stop all database servers:
```
# kill `head -1 /var/db/postgres/data17/postmaster.pid`
```
Repeat this on all secondaries as well.
Optionally, backup the database directory, or create a local rescue copy:
```
# cp -aiv /var/db/postgres/data17 /var/db/postgres/data17.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"
```
On all secondary servers, backup at least the config file postgresql.auto.conf and standby.signal:
```
# cp -aiv data17/postgresql.auto.conf .
# cp -aiv data17/standby.signal .
```
On the primary server only, update the database:
1. As PostgreSQL 18 enabled data page checksums by default, we have to disable them while upgrading the database in linked mode only when updating from a version before 18.0:
```
# pg_checksums -d -P -v data18
```
2. Now upgrade the database:
```
# pg_upgrade -b /var/jail/usr/local/bin -B /usr/local/bin -d /var/db/postgres/data17 -D /var/db/postgres/data18 -j 2 -k -v
```
  If everything went ok, pg_upgrade places ~~two~~ one shell script analyze_new_cluster.sh and delete_old_cluster.sh in the current directory
3. If you disabled checksums, enable them again:
```
# pg_checksums -e -P -v data18
```
4. Edit /var/db/postgres/data18/pg_hba.conf and comment out the line which trusts all local connections
Exit user postgres:
```
# exit
```
If you run one or more secondary database servers, either create a fresh standby server from scratch, e.g. by adapting my howto PostgreSQL with replication slots on FreeBSD. Or follow step 11 of the official documentation, i.e. rsync just the delta between the old and new database directory on the primary to all secondaries. On the primary server only:
1. As root, start the primary database server:
```
$ service postgresql start
```
2. Re-create replications slots for all secondary servers, e.g.:
```
$ psql -c "SELECT pg_create_physical_replication_slot('server2')" postgres postgres
```
3. Stop the primary server again:
```
$ service postgresql stop
```
4. Now rsync the delta between the old and new database directory on the primary to all secondaries:
```
$ rsync --archive --delete --hard-links --size-only --no-inc-recursive --verbose /var/db/postgres/data17 /var/db/postgres/data18 server2:/var/db/postgres
```
  Repeat this for all secondaries.
5. On all secondaries, check data18/postgresql.conf for valid listen_addresses. You may have to correct them on the secondaries, as postgresql.conf was copied from the primary server.
6. On all secondaries, move postgresql.auto.conf and standby.signal, which we saved earlier, to the new database directory:
```
# mv -v postgresql.auto.conf standby.signal data18/
```
As root, start the primary database server, then all secondaries:
```
$ service postgresql start
```
On the primary server, 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
```

On the primary server, remove the jail and BSD tarballs:

$ umount /var/jail/dev
$ chflags -R noschg /var/jail
$ rm -r /var/jail /usr/freebsd-dist

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