Migrating Postgresql data directory


New Member

Reaction score: 2
Messages: 18

This is my first attempt at a public service post.

Sometimes, one discovers that the database directories are better off in a different location than the current/default, e.g. when one decides to use a new zfs partition for the db files. The process of moving the data files needs a bit of care, and is slightly involved - different for different OS's. For Ubuntu there is a nice guide at Digital Ocean, but this isn't entirely applicable to FreeBSD - there are subtle but important differences.

So for migrating the postgres data files in FreeBSD, the below is a helpful guide. Steps 4 and 5 are FreeBSD specific.
  1. Get the current data directory. As user postgres, start a psql prompt, and do show data_directory;
  2. Stop the running postgres service. service postgresql stop
  3. Copy the database files using rsync in archive mode - this is to ensure permissions, etc. rsync -a /old/location /new/location
  4. Update the postgres rc script to use the new location. Open (using sudo) the file /usr/local/etc/rc.d/postgresql and look for a line that looks something like
    Generally this line should be commented out (default) - if it isn't, comment it out because a new one is needed. Copy this line (and uncomment it, if applicable) and update the directory location. So the /usr/local/etc/rc.d/postgresql file should have a new line that looks line
  5. Lastly, open the /etc/passwd file using vipw. Notice that for each user, a home directory is specified. For the daemon user postgres, the home dir should be the same as the old data directory (in the previous step). Change this to reflect the new data directory. Save and exit. If you skip this step, you'll get an error that looks like su: no directory
  6. Start postgres again. service postgresql start. Everything should be back to normal, with the database files in the new location.