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:

  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. 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
  12. Edit /var/db/postgres/data10/pg_hba.conf and remove the line which trusts all local connections
  13. As root, start the database server:
    # exit
    $ service postgresql start
  14. Update the optimizer statistics of the new database (assuming that your database superuser is called postgres):
    $ PGUSER=postgres ./analyze_new_cluster.sh
  15. 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