# Migrating Postgresql data directory



## jordanch (Jan 11, 2019)

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.

Get the current data directory. As user _postgres_, start a `psql` prompt, and do  `show data_directory;`
Stop the running postgres service. `service postgresql stop`
Copy the database files using rsync in archive mode - this is to ensure permissions, etc. `rsync -a /old/location /new/location`
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 
	
	



```
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"
```

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.

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`
Start postgres again. `service postgresql start`. Everything should be back to normal, with the database files in the new location.


----------

