Introduction

WARNING make sure you backup (and also test the backup!) before following the steps detailed in this tutorial.

We are going to move the current data to a new directory. If this is your first postgresql configuration you can skip step 1. This tutorial is based on Linux, but with some minor changes can be done on OSX.

Step 1: Moving the current data to a new directory

Fist change your user to the postgres user and execute psql command. You can use your user in case you configured postgresql to allow login with another user.

sudo su postgres
psql

On the sql command propt enter the following query:

SHOW data_directory;

This will output the current directory where all the data is currently stored/ In our case the printed directory was:

/var/lib/postgresql/9.5/main

Now in order to move the files stop postgresql:

sudo systemctl stop postgresql

To copy the files we will use rsync. This tool is used to make remote and local file synchronizarion. We prefer this tool than the cp command.

Remeber to change /mnt/new_volume to match your new directory.

IMPORTANT Be sure there is no trailing slash on the directory.

sudo rsync -av /var/lib/postgresql /mnt/new_volume

We use -a flag on rsync to preserve the permissions and other directory properties and -v is to show verbose output.

Step 2: How to change postgreSQL configuration to a new data path

All postgreSQL configuration are in the /etc/postgresql path. Open the file /etc/postgresql/9.6/main/postgresql.conf (check your current postgres version for the correct path). Once you opened the file, search for the string data_directory and change it to:

/mnt/new_volume/postgresql/9.6/main

YOu are ready to start postgresql again with:

sudo systemctl start postgresql

Step 3 (optional): Make sure your configuration is working

To verify the new configuration execute psql command and check the result of the SHOW data_directory:

sudo su postgres
psql

On the sql command propt enter the following query:

SHOW data_directory;

Appendix: How to check if postgresql is active or running

Execute systemctl with status and the name of the service to check if the service is running and active:

 $ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2017-12-24 12:59:58 EST; 9s ago
  Process: 8962 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 8962 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4915)
   CGroup: /system.slice/postgresql.service

Dec 24 12:59:58 homelab systemd[1]: Starting PostgreSQL RDBMS...
Dec 24 12:59:58 homelab systemd[1]: Started PostgreSQL RDBMS.