Migrating Postgresql data directory

[jordanch]

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
   1. look for a line that looks something like

      postgresql_data="/var/db/postgres/data10"

      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

      postgresql_data="/new/location/db/postgres/data10"

   2. there's also another line that looks like

      eval postgresql_data=${postgresql_data:-"~${postgresql_user}/data10

      By default this line is not commented out. Either comment this out (since the previous step takes care of assigning the postgresql_data variable, or update this line to reflect the correct data dir.
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 parent of the old data directory (in the previous step). So if the actual data directory is /var/db/postgres/data10, the user's home directory is /var/db/postgres. Change this to reflect the new parent 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.

 

[martinrame]

Maybe easier, after point 3 edit /etc/rc.conf and add this:

postgresql_data="/new/location/db/postgres/data10"