Upgrading PostgreSQL with pg_upgrade on FreeBSD

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.

  1. $ bsdinstall jail /var/jail
    The installer comes up with several dialogs. During the installation
  2. Install PostgreSQL in the jail:
    $ pkg -c /var/jail install postgresql96-server
  3. Update PostgreSQL on the host system:
    $ portmaster -o databases/postgresql10-client postgresql96-client
    Select the NLS, OPTIMIZED_CFLAGS, and SSL options
    $ portmaster -o databases/postgresql10-server postgresql96-server
    $ portmaster -o databases/postgresql10-contrib postgresql96-contrib
  4. Initialize the new database:
    $ service postgresql initdb
  5. Manually merge any non-default settings from /var/db/postgres/data96/postgresql.conf to /var/db/postgres/data10/postgresql.conf
  6. Also, merge any changed settings from /var/db/postgres/data96/pg_hba.conf to /var/db/postgres/data10/pg_hba.conf
  7. For the upgrade, temporarily trust any local connections. Add the following line to both the old and new pg_hba.conf:
    local all all trust
  8. Change to the system account as which the database is running:
    $ su - postgres
  9. Stop the database server:
    # kill `head -1 /var/db/postgres/data96/postmaster.pid`
  10. Optionally, backup the database directory, or create a rescue copy:
    # cp -aiv /var/db/postgres/data96 /var/db/postgres/data96.save
  11. 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"
  12. Now update the database:
    # 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 -v
    If everything went ok, pg_upgrade places two shell scripts analyze_new_cluster.sh and delete_old_cluster.sh in the current directory
  13. Edit /var/db/postgres/data10/pg_hba.conf and remove the line which trusts all local connections
  14. If run one or more standby databases, stop them now. Then follow step 10 of the official documentation, i.e. rsync from the master to the standby server(s)
  15. As root, start the master database server:
    # exit
    $ service postgresql start
  16. Update the optimizer statistics of the new database (assuming that your database superuser is called postgres):
    $ PGUSER=postgres ./analyze_new_cluster.sh
  17. Remove the jail and source tarballs, and uninstall unused packages:
    $ chflags -R noschg /var/jail
    $ rm -r /var/jail /usr/ports/distfiles/*
    $ pkg autoremove
  18. 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/data96