In addition to the standard pg_dumpall/restore procedure when moving from one major PostgreSQL version to another, we also need to take care of 2 changes that happened in the FreeBSD package between 9.3 and 9.6. Namely, the datastore location and postgres username.
Please note that I'm upgrading the pkg-version of postgres 9.3 with all stock options (pgsql user, data store at /usr/local/pgsql/data).
First, stop all apps and services that are using the postgres DB to avoid any changes between the backup and service shutdown
Next, let's back up all our databases:
$ pg_dumpall -U pgsql | gzip > backup.sql.gz
Shut down the service:
# service postgresql stop
Remove 9.3 and install 9.6. I'm uninstalling the client since that also removes the server (and installing the server also installs the client):
# pkg remove postgresql93-client
# pkg install postgresql96-server
Next, we need to run vipw to remove the old pgsql user as well as activate the postgres user the 9.6 pkg just created:
# vipw
# delete this line: pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
Initialize the new datastore:
$ sudo -u postgres initdb -D /var/db/postgres/data96
Start the service:
# service postgresql start
… and restore your backup:
$ gzcat backup.sql.gz | psql -U postgres postgres
Start your DB apps and verify everything is working properly. After that it should be safe to delete the backup and old datastore:
# rm backup.sql.gz /usr/local/pgsql/data
Finally make sure all your admin/backup scripts are using the new postgres user.