Moving data dir for PostgreSQL on FreeBSD

16 October 2020

One of the beauties of FreeBSD is of course its support for ZFS. For systems with ZFS volumes and UFS root, it makes sense to keep database data on a ZFS volume. The steps to change the data directory used by PostgreSQL are below.

Find the data directory

psql -U postgres

Then show data_directory; to find out what your data directory is

  • Stop the service with sudo service postgresql stop
  • Copy the data across, using rsync to preserve permissions
sudo rsync -a /var/db/postgres/data11 /titanium/db/postgres
  • Edit rc.conf to include the new directory
sudo vim /etc/rc.conf

Note that titanium is used as an example volume below.

  • Update the home directory for the postgres user so it is the directory one above (i.e. containing) the data directory
sudo vipw /etc/passwd
postgres:*:770:770::0:0:PostgreSQL Daemon:/titanium/db/postgres:/bin/sh
  • Start the service with sudo service postgresql stop

Versions used: FreeBSD 12.1-RELEASE-p4, PostgreSQL 11.7