How to Change the PostgreSQL Data Directory on Ubuntu

Databases tend to grow beyond their original filesystem over time. If they share the same partition as the operating system, it could potentially lead to I/O conflicts.

Devices like network block storage RAID provide redundancy and enhance scalability. Regardless of your goals—whether increasing space or optimizing performance—this guide will assist you in moving the PostgreSQL data directory.

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS.

Prerequisites

Copy link

To follow this guide, you will need:

• An Ubuntu 22.04 VPS server with a non-privileged user account and sudo privileges.

• PostgreSQL installed on your server.

In this guide, we will move the data to a block storage device mounted at /mnt/volume_01. The method described here is universal and will help relocate the data directory to another location in any basic storage.

Step 1. Move the PostgreSQL Data Directory

Copy link

First, check the current location of the data directory by starting an interactive PostgreSQL session. Here, psql is the command to enter the interactive monitor, and -u postgres instructs sudo to run psql as the system user postgres:

sudo -u postgres psql

In the PostgreSQL command line, enter this command to show the current directory:

SHOW data_directory;

By default, the directory is /var/lib/postgresql/xx/main, where xx is your PostgreSQL version.

Exit the PostgreSQL prompt by typing \q and pressing ENTER.

Before making any changes to the directory, stop PostgreSQL to avoid compromising data integrity:

sudo systemctl stop postgresql

You won’t be able to check the service status directly through systemctl after stopping it. To ensure the service has been stopped, run the following command:

sudo systemctl status postgresql

The last line of the output will confirm that PostgreSQL has indeed stopped.

To copy the directory to the new location, use the rsync command. You can add flags: -a preserves the permissions and other attributes of the directory, while -v ensures detailed output so you can track progress. To replicate the original directory structure in the new location, run rsync from the postgresql directory. Creating this postgresql directory at the mount point and preserving ownership by the PostgreSQL user will prevent permission issues during future updates.

Strictly speaking, the versioned directory (e.g., 16) is unnecessary since the location is explicitly defined in the postgresql.conf file. However, it is recommended to follow the project's conventions, especially if you later need to run multiple versions of PostgreSQL:

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

Once the copy is complete, rename the original folder with a .bak extension, and don't delete it until the move is complete. This ensures that nothing gets mixed up due to directories with the same name:

sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/10/main.bak

Now, we can configure PostgreSQL to access the data directory in the new location.

Step 2. Point to the New Location of the Directory

Copy link

By default, the value for data_directory is set to /var/lib/postgresql/16/main in the file /etc/postgresql/16/main/postgresql.conf. You need to edit this file to point to the new directory:

sudo nano /etc/postgresql/16/main/postgresql.conf

Now, locate the line starting with data_directory and change the path to point to the new location. The updated directive will look something like this:

# /etc/postgresql/16/main/postgresql.conf
...
data_directory = '/mnt/volume_01/postgresql/10/main'
...

Save the file and close it by pressing CTRL+X, then Y, and finally ENTER. No further configuration is needed for PostgreSQL in the new directory. The only thing left to do at this point is restart the PostgreSQL service and verify that it correctly points to the new data directory.

Step 3. Restart PostgreSQL

Copy link

After changing the data_directory directive in the postgresql.conf file, start the PostgreSQL server using systemctl:

sudo systemctl start postgresql

Check the server status:

sudo systemctl status postgresql

If the service started correctly, you will see a line like this at the end of the output.

Finally, to ensure that the new directory is being used, open the PostgreSQL command line:

sudo -u postgres psql

Check the value of the data directory again:

SHOW data_directory;

The output will confirm that PostgreSQL is using the new data directory location. After verifying that everything is working properly, ensure you have access to your database and can interact with its data without issues. Once you are confident that the existing data is intact, you can delete the backup directory:

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

Thus, you have successfully moved the PostgreSQL data directory to a new location.

Conclusion

Copy link

If you followed the instructions correctly, your database directory is now in a new location, and you're closer to being able to scale your storage. Congrats!