I was running PostgreSQL 9.6 on FreeBSD and wanted to update that database to version 10 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 run one or more standby database servers, e.g. a streamining replication, then you'll ssh access from the master to the standbys, and rsync installed on both sides.
$ bsdinstall jail /var/jailThe installer comes up with several dialogs. During the installation
$ mount -t devfs none /var/jail/dev
$ pkg -c /var/jail install postgresql96-server
$ portmaster -o databases/postgresql10-client postgresql96-clientSelect the NLS, OPTIMIZED_CFLAGS, and SSL options
$ portmaster -o databases/postgresql10-server postgresql96-serverSelect the INTDATE, NLS, OPTIMIZED_CFLAGS, SSL, TZDATA, and XML options
$ portmaster -o databases/postgresql10-contrib postgresql96-contrib
$ service postgresql initdb
local all all trust
# 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)
[/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
$ su - postgres
# kill `head -1 /var/db/postgres/data96/postmaster.pid`
# cp -aiv /var/db/postgres/data96 /var/db/postgres/data96.save
# export LB_LIBRARY_PATH="/var/jail/usr/local/lib"
# pg_upgrade -b /var/jail/usr/local/bin -B /usr/local/bin -d /var/db/postgres/data96 -D /var/db/postgres/data10 -j 2 -k -vIf everything went ok, pg_upgrade places two shell scripts analyze_new_cluster.sh and delete_old_cluster.sh in the current directory
# exit $ service postgresql start
$ PGUSER=postgres ./analyze_new_cluster.sh
$ umount /var/jail/dev $ chflags -R noschg /var/jail $ rm -r /var/jail /usr/ports/distfiles/* $ pkg autoremove
$ rm -r analyze_new_cluster.sh delete_old_cluster.sh /var/db/postgres/data96