How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 22.04

Introduction

Databases grow over time, sometimes outgrowing the space on their original file system. When they’re located on the same partition as the rest of the operating system, this can potentially lead to I/O contention.

RAID, network block storage, and other devices can offer redundancy and improve scalability, along with other desirable features. Whether you’re adding more space, evaluating ways to optimize performance, or looking to take advantage of other storage features, this tutorial will guide you through relocating PostgreSQL’s data directory.

Prerequisites

To complete this guide, you will need:

• An Ubuntu 22.04 server with a non-root user with sudo privileges. You can learn more about how to set up a user with these privileges in our Initial Server Setup with Ubuntu 22.04 guide.

• PostgreSQL installed on your server. If you haven’t already set this up, the How To Install and Use PostgreSQL on Ubuntu 22.04 guide can help you.

The examples throughout this tutorial will involve moving the data to a block storage device mounted at /mnt/volume_nyc1_01. If you’re using Block Storage on DigitalOcean, read our documentation on How to Create and Set Up Volumes for Use with Droplets to guide you on mounting your volume before continuing with this tutorial.

Regardless of what underlying storage you use, though, the following steps can help you move the data directory to a new location.

Step 1 — Moving the PostgreSQL Data Directory

Before we get started with moving PostgreSQL’s data directory, let’s verify the current location by starting an interactive PostgreSQL session. In the following command, psql is the command to enter the interactive monitor and -u postgres tells sudo to execute psql as the system’s postgres user:

sudo -u postgres psql

Once you have the PostgreSQL prompt opened up, use the following command to show the current data directory:

SHOW data_directory;

Output
       data_directory
-----------------------------
 /var/lib/postgresql/14/main
(1 row)

This output confirms that PostgreSQL is configured to use the default data directory, /var/lib/postgresql/14/main, so that’s the directory you need to move. Once you’ve confirmed the directory on your system, you can close the psql prompt by running the \q meta-command:

\q

To ensure the integrity of the data, stop PostgreSQL before you actually make changes to the data directory:

sudo systemctl stop postgresql

systemctl doesn’t display the outcome of all service management commands. To verify that you’ve successfully stopped the service, use the following command:

sudo systemctl status postgresql

The output should tell you that PostgreSQL is inactive (dead), meaning it has been stopped:

Output
○ postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor>
     Active: inactive (dead) since Thu 2022-06-30 18:46:35 UTC; 27s ago
    Process: 4588 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 4588 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Now that the PostgreSQL server is no longer running, copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while -v provides verbose output to help you follow the progress. You’re going to start the rsync from the postgresql directory in order to mimic the original directory structure in the new location. By creating that postgresql directory within the mount-point directory and retaining ownership by the PostgreSQL user, you can avoid permissions problems for future upgrades.

The version directory, 14, isn’t strictly necessary since you’ve defined the location explicitly in the postgresql.conf file, but following the project convention certainly won’t hurt, especially if there’s a need in the future to run multiple versions of PostgreSQL:

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

Once the copy is complete, rename the current folder with a .bak extension and keep it until you’ve confirmed that the move was successful. This will help to avoid confusion that could arise from having similarly-named directories in both the new and the old location:

sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main.bak

Now you’re ready to configure PostgreSQL to access the data directory in its new location.

Step 2 — Pointing to the New Data Location

By default, the data_directory configuration directive is set to /var/lib/postgresql/14/main in the /etc/postgresql/14/main/postgresql.conf file. Edit this file to reflect the new data directory:

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

Find the line that begins with data_directory and change the path which follows to reflect the new location. In the context of this tutorial, the updated directive will be written as:

. . .
data_directory = '/mnt/volume_nyc1_01/postgresql/14/main'
. . .

Save and close the file by pressing CTRL + X, Y, then ENTER. This is all you need to do to configure PostgreSQL to use the new data directory location. All that’s left at this point is to start the PostgreSQL service again and check that it is indeed pointing to the correct data directory.

Step 3 — Restarting PostgreSQL

After changing the data-directory directive in the postgresql.conf file, go ahead and start the PostgreSQL server using systemctl:

sudo systemctl start postgresql

To confirm that the PostgreSQL server started successfully, check its status by again using systemctl:

sudo systemctl status postgresql

If the service started correctly, the Active line will say active (exited) in the command’s output:

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor>
     Active: active (exited) since Thu 2022-06-30 18:50:18 UTC; 3s ago
    Process: 4852 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 4852 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Lastly, to make sure that the new data directory is indeed in use, open the PostgreSQL command prompt:

sudo -u postgres psql

Check the value for the data directory again:

SHOW data_directory;

Output
             data_directory
----------------------------------------
 /mnt/volume_nyc1_01/postgresql/14/main
(1 row)

This confirms that PostgreSQL is using the new data directory location. Following this, take a moment to ensure that you’re able to access your database as well as interact with the data within. Once you’ve verified the integrity of any existing data, you can remove the backup data directory:

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

With that, you have successfully moved your PostgreSQL data directory to a new location.

Conclusion:

If you’ve followed along, your database should be running with its data directory in the new location and you’ve completed an important step toward being able to scale your storage. You might also want to check out 5 Common Server Setups For Your Web Application for ideas on how to create a server infrastructure to help you scale and optimize web applications.

# sudo rsync -av /var/lib/postgresql/ /mnt/volume_sfo3_03/

. . .
data_directory = '/mnt/volume_sfo3_03/16/main'
. . .