Tutorial

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

Published on July 13, 2018

Not using Ubuntu 18.04?Choose a different version or distribution.

Ubuntu 18.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 also 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 18.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 18.04 guide.
PostgreSQL installed on your server. If you haven’t already set this up, the How To Install and Use PostgreSQL on Ubuntu 18.04 guide can help you.

In this example, we’re moving the data to a block storage device mounted at /mnt/volume_nyc1_01. If you are using Block Storage on DigitalOcean, this guide can help you mount 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/10/main
(1 row)

This output confirms that PostgreSQL is configured to use the default data directory, /var/lib/postgresql/10/main, so that’s the directory we need to move. Once you’ve confirmed the directory on your system, type \q and press ENTER to close the PostgreSQL prompt.

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 final line of the output should tell you that PostgreSQL has been stopped:

Output. . .
Jul 12 15:22:44 ubuntu-512mb-nyc1-01 systemd[1]: Stopped PostgreSQL RDBMS.

Now that the PostgreSQL server is shut down, we’ll 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 so you can follow the progress. We’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, we can avoid permissions problems for future upgrades.

Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. If you do include a trailing slash, rsync will dump the contents of the directory into the mount point instead of copying over the directory itself.

The version directory, 10, isn’t strictly necessary since we’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, we’ll rename the current folder with a .bak extension and keep it until we’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/10/main /var/lib/postgresql/10/main.bak

Now we’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 is set to /var/lib/postgresql/10/main in the /etc/postgresql/10/main/postgresql.conf file. Edit this file to reflect the new data directory:

sudo nano /etc/postgresql/10/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 look like this:

/etc/postgresql/10/main/postgresql.conf

. . .
data_directory = '/mnt/volume_nyc1_01/postgresql/10/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, you will see the following line at the end of this command’s output:

Output. . .
Jul 12 15:45:01  ubuntu-512mb-nyc1-01[1]: Started PostgreSQL RDBMS.
. . .

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/10/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/10/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 take a look at 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.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us

About the authors

Melissa Anderson

author

Mark Drake

author

Manager, Developer Education

Technical Writer @ DigitalOcean

Still looking for an answer?

Ask a question Search for more help

Was this helpful?

8 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

sergiig • October 20, 2020

You may need to change your PostgreSQL’s systemd configuration file (/usr/lib/systemd/system/postgresql@.service) to make sure mount point is ready when Postgres is starting.

Add mount point to RequiresMountsFor parameter:

[Unit]
...
RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I /mnt/volume_nyc1_01
...

everthoman • October 2, 2020

Hi,

I’ve been looking for a long time for a good guide on moving postgresql database folder, and successfully applied yours on a test system. Now I want to do it on my production system and run into the classic problem where psql complains accordingly:

psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?

I moved the database folder to /media/data/postgresql and modified the /etc/postgresql/10/main/postgresql.conf file accordingly:

data_directory = ‘/media/data/postgresql’ # use data in another directory

/media/data folder looks like this:

drwxr-xr-x 6 root root 4096 okt 2 08:45 . drwxr-xr-x 5 root root 4096 feb 19 2020 … drwxr-xr-x 9 evehom evehom 36864 okt 1 16:30 evehom drwx------ 2 root users 16384 nov 6 2015 lost+found drwxr-xr-x 3 postgres postgres 4096 okt 2 08:10 postgresql drwx------ 5 evehom users 4096 jul 10 2017 .Trash-1000

Postgresql is running: sudo systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Fri 2020-10-02 09:27:35 CEST; 9min ago Process: 14397 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 14397 (code=exited, status=0/SUCCESS)

okt 02 09:27:35 mjolnir systemd[1]: Starting PostgreSQL RDBMS… okt 02 09:27:35 mjolnir systemd[1]: Started PostgreSQL RDBMS.

What is wrong? Grateful for pointers. This is on Ubuntu 18.04 with postgresql 10.

Thanks/Evert

ianpegg9 • January 24, 2020

Thank you! I found this extremely useful, but I had to do a lot outside of this tutorial to get this to work:

I was looking to move my data to a mountable drive, which needed the following steps first to work.

Create mount directory

mkdir /media/usb

Configure fstab (located at etc/fstab on Debian) so that the volume is mounted with the ownership and permissions required by Postgres. Add the following line to fstab:

# <file system> <mount point>   <type>  <options>       <dump>  <pass>
UUID=<uuid> /media/usb <filesystem> auto,gid=postgres,uid=postgres,umask=0027 0 2

Use lsblk to find the location in /dev where you can find your device. Then use sudo blkid to find your device’s UUID and filesystem (type). gid and uid set the group user ownerships for the mounted filesystem respectively. umask sets the the permissions to be compatible with the Postgres requirements: u=rxw,g=rw,o=-.

Now, you can run sudo mount /dev/<device> and then start with the instructions above.

fhot707 • February 18, 2022

This comment has been deleted

jakethekoenig • February 23, 2021

Hi, I followed this tutorial but unfortunately it’s not working for me. When I start the server and check its status I get a green ‘active (exited)’ but when I run

pg_lsclusters

I get a red:

Ver Cluster Port Status Owner     Data directory                                            Log file
11  main    5432 down   <unknown> /media/pi/3436fd79-3d79-45da-9bf1-67133736856a/posty/main /var/log/postgresql/postgresql-11-main.log

When I check the log file I see

pg_ctl: could not access directory "/media/pi/3436fd79-3d79-45da-9bf1-67133736856a/posty/main": Permissioon denied

But this is very confusing to me because when I run ls -l on my original database files and those on my external hard drive they appear to exactly match:

pi@raspberrypi:~ $ sudo ls -l /media/pi/3436fd79-3d79-45da-9bf1-67133736856a/posty/main/
total 80
drwx------ 8 postgres postgres 4096 Feb 23 07:20 base
drwx------ 2 postgres postgres 4096 Feb 23 11:34 global
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_commit_ts
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_dynshmem
drwx------ 4 postgres postgres 4096 Feb 23 11:36 pg_logical
drwx------ 4 postgres postgres 4096 Feb 18 10:13 pg_multixact
drwx------ 2 postgres postgres 4096 Feb 23 11:34 pg_notify
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_replslot
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_serial
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_snapshots
drwx------ 2 postgres postgres 4096 Feb 23 11:36 pg_stat
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_subtrans
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_tblspc
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_twophase
-rw------- 1 postgres postgres    3 Feb 18 10:13 PG_VERSION
drwx------ 3 postgres postgres 4096 Feb 18 10:29 pg_wal
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_xact
-rw------- 1 postgres postgres   88 Feb 18 10:13 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Feb 23 11:34 postmaster.opts

and:

pi@raspberrypi:~ $ sudo ls -l /var/lib/postgresql/11/main/
total 80
drwx------ 8 postgres postgres 4096 Feb 23 07:20 base
drwx------ 2 postgres postgres 4096 Feb 23 11:34 global
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_commit_ts
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_dynshmem
drwx------ 4 postgres postgres 4096 Feb 23 11:36 pg_logical
drwx------ 4 postgres postgres 4096 Feb 18 10:13 pg_multixact
drwx------ 2 postgres postgres 4096 Feb 23 11:34 pg_notify
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_replslot
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_serial
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_snapshots
drwx------ 2 postgres postgres 4096 Feb 23 11:36 pg_stat
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_subtrans
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_tblspc
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_twophase
-rw------- 1 postgres postgres    3 Feb 18 10:13 PG_VERSION
drwx------ 3 postgres postgres 4096 Feb 18 10:29 pg_wal
drwx------ 2 postgres postgres 4096 Feb 18 10:13 pg_xact
-rw------- 1 postgres postgres   88 Feb 18 10:13 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Feb 23 11:34 postmaster.opts

I’m not really sure what to try next because I’m not sure what the problem is. Here’s the edited line from my .conf:

data_directory =  '/media/pi/3436fd79-3d79-45da-9bf1-67133736856a/posty/main'

Silas de Rooy • December 7, 2020

I am always very satisfied with the clear articles on digitalocean. Like this article too. I do run into a problem that writing to the data on the new mounted volume is very slow (not tested in receiving since it takes to long to fill loads data). Do you have a tips? I am using digital ocean block storage volume.

trebusz • November 1, 2020

The title suggests article is about “moving” postgres to new place. But the content seems to be about copying to new directory. It does not say what to do with old files (which I wish to get rid off). It seems to me I can’t just delete them. PG mixes config and database files in data/ directory and I suspect after finishing the tutorial, postgres is still using postgresql.conf from old location, right? Anyway, how do I know which files are not needed and can be safely deleted?

olig • March 11, 2020

Hello, thanks for this really clear and simple tuto. However in step 3 when I try to open the postgresql command promt, il get following message:

psql: could not connect to server: No such file or directory

Any idea how I can solve that problem?

This work is licensed under a Creative Commons Attribution-NonCommercial- ShareAlike 4.0 International License.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Tutorial

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

Not using Ubuntu 18.04?Choose a different version or distribution.

Introduction

Prerequisites

Step 1 — Moving the PostgreSQL Data Directory

Step 2 — Pointing to the New Data Location

Step 3 — Restarting PostgreSQL

Conclusion:

Still looking for an answer?

Try DigitalOcean for free

Popular Topics

Join the Tech Talk

Get our biweekly newsletter

Hollie's Hub for Good

Become a contributor

Featured on Community

DigitalOcean Products

Welcome to the developer cloud