How to move a PostgresSQL Data Directory?

Over time, there are changes that your database has grown quite a bit that it may be time to think about moving this database to a new directory. This post is written considering that the database is hosted in a linux ubuntu 16.0.4 environment. Here's how you do it.

Moving the data directory

As a first step, you will need to identify the data directory of your database. Follow this blogpost to do so. 

In order to ensure the integrity of the data, you will need to shut down your postgres services. Use the following command to do so. 

sudo systemctl stop postgresql

In order to confirm the stopped status of postgres, use the following command.

sudo systemctl status postgresql

Next, we will use the file transfer utility rsync to help us transfer over the data directory. Using the -a flag preserves the permissions and other directory properties while -v provides verbose output so you can follow the progress.

Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing PostgreSQL directory.

Use the following command to start the transfer.

sudo rsync -av /var/lib/postgresql /mnt/<new_volume_name>

Once the copy is complete, we’ll rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful. By re-naming it, we’ll avoid confusion that could arise from files in both the new and the old location.

sudo mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.5/main.bak

Pointing the database to a new location

PostgreSQL has several ways to override configuration values. By default, the data_directory is set to /var/lib/postgresql/9.5/main in the /etc/postgresql/9.5/main/postgresql.conf file. Edit this file to reflect the new data directory. Find the line that begins with data_directory and change the path which follows to reflect the new location. 

Restarting PostgreSQL

Use the following command to restart postgresql.

sudo systemctl restart postgresql
sudo systemctl status postgresql

Once you've restarted the database, you can make sure that the data directory has changed by following this blog. 

After confirming that everything is running, you can delete the old backup file using the following command. 

sudo rm -Rf /var/lib/postgresql/9.6/main.bak

Source: https://www.digitalocean.com/community/tutorials/how-to-move-a-postgresql-data-directory-to-a-new-location-on-ubuntu-16-04

That’s all for this blog post! I hope that you learned something new! ☺️