How To Change PostgreSQL Data Directory Location on Ubuntu 16.04

Let’s suppose that  the  database grows frequently and need more space, the best solution is to relocate the PostgreSQL data directory to a new location.

In this article, we will show you  how to change or move the PostgreSQL Database data directory to a new location on Ubuntu 16.04.

Prerequisites

To complete this tutorial, you will need:

• An Ubuntu 16.04 server with a user with sudo privileges.
• A PostgreSQL server. If you haven’t already set one up, the How To Install PostgreSQL 9.6 on Ubuntu 16.04 article can help you.
• A new volume where you will move the PostgreSQL Database data.

In this tutorial, we will move the PostgreSQL Database data to a storage volume mounted at /opt/pgdb.

Step 1. Moving the PostgreSQL Data Directory

– Check the current PostgreSQL Data Directory
Before start moving the PostgreSQL Data Directory, we have to check the current PostgreSQL Data Directory location.
– Connect to the PostgreSQL Server and run the following command:

# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (9.6.1)
Type "help" for help.

postgres=# SHOW data_directory;
        data_directory         
-------------------------------
 /var/lib/postgresql/9.6/main
(1 row)

postgres=# 

Notice that PostgreSQL is configured to use the default data directory, /var/lib/postgresql/9.6/main

– Stop The PostgreSQL Service

# sudo systemctl stop postgresql

– Copy the PostgreSQL Database Data Directory

# sudo rsync -av /var/lib/postgresql /opt/pgdb

– once the PostgreSQL Database Data Directory is copied we will rename the old database data Directory.

# sudo mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main.backup

Step 2. Pointing to the New PostgreSQL Data Directory Location

– Open the /etc/postgresql/9.6/main/postgresql.conf file and modify the data_directory setting with the new path location

# sudo vi /etc/postgresql/9.6/main/postgresql.conf
data_directory = '/opt/pgdb/postgresql/9.6/main'         # use data in another directory
                                        # (change requires restart)
.
.

– Start the PostgreSQL Service

# sudo systemctl start postgresql

Step 3. Checking

– Connect to PostgreSQL and verify the PostgreSQL Database Data Directory using the following commands

# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (9.6.1)
Type "help" for help.

postgres=# SHOW data_directory;
        data_directory         
-------------------------------
 /opt/pgdb/postgresql/9.6/main
(1 row)

postgres=# 

– Delete the the old PostgreSQL Database Data Directory

# sudo rm -rf /var/lib/postgresql/9.6/main.backup

 
We hope this tutorial was enough Helpful. If you need more information, or have any questions, just comment below and we will be glad to assist you!