Tutorial
How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 16.04
Not using Ubuntu 16.04?Choose a different version or distribution.
Introduction
Databases grow over time, sometimes outgrowing the space on their original file system. You can also run into I/O contention when they’re located on the same partition as the rest of the operating system. RAID, network block storage, and other devices can offer redundancy and 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 16.04 server with a non-root user with
sudoprivileges. You can learn more about how to set up a user with these privileges in our Initial Server Setup with Ubuntu 16.04 guide. -
A PostgreSQL server. If you haven’t already set one up, the How To Install and Use PostgreSQL on Ubuntu 16.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.
No matter what underlying storage you use, the following steps can help you move the data directory to a new location.
Step 1 — Moving the PostgreSQL Data Directory
To prepare for moving PostgreSQL’s data directory, let’s verify the current location by starting an interactive PostgreSQL session. In the line below, psql is the command to enter the interactive monitor, and -u postgres tells sudo to execute psql as the system’s postgres user:
Once you’ve entered the monitor, select the data directory:
Output data_directory
------------------------------
/var/lib/postgresql/9.5/main
(1 row)
This output confirms that PostgreSQL is configured to use the default data directory, /var/lib/postgresql/9.5/main, so that’s the directory we need to move. Once you’ve confirmed the directory on your system, type \q to quit.
To ensure the integrity of the data, we’ll shut down PostgreSQL before we actually make changes to the data directory:
systemctl doesn’t display the outcome of all service management commands. To verify you’ve succeeded, use the following command:
You can confirm it’s shut down if the final line of the output tells you the server is stopped:
Output. . .
Jul 22 16:22:44 ubuntu-512mb-nyc1-01 systemd[1]: Stopped PostgreSQL RDBMS.
Now that the 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.
Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing PostgreSQL directory:
We’re going to start the rsync from the postgresql directory in order to mimic the original directory structure in our 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. The version directory, 9.5 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.
Once the copy is complete, we’ll rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful. By re-naming it, we’ll avoid confusion that could arise from files in both the new and the old location:
Now we’re ready to turn our attention to configuration.
Step 2 — Pointing to the New Data Location
PostgreSQL has several ways to override configuration values. By default, the data_directory is set to /var/lib/postgresql/9.5/main in the /etc/postgresql/9.5/main/postgresql.conf file. Edit this file to reflect the new data directory:
Find the line that begins with data_directory and change the path which follows to reflect the new location.
In our case, the updated file looks like the output below:
. . .
data_directory = '/mnt/volume-nyc1-01/postgresql/9.5/main'
. . .
Step 3 — Restarting PostgreSQL
We’re ready to start PostgreSQL.
To make sure that the new data directory is indeed in use, start the PostgreSQL monitor.
Look at the value for the data directory again:
Output data_directory
-----------------------------------------
/mnt/volume-nyc1-01/postgresql/9.5/main
(1 row)
Now that you’ve restarted PostgreSQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve verified the integrity of any existing data, you can remove the backup data directory:
Restart PostgreSQL one final time to be sure that it works as expected:
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.
About the author(s)
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!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
Great write-up. Thanks for posting. Worked great with 9.3 on Ubuntu 14.04 (just needed to swap version numbers and use /etc/init.d/postgresql stop/start/status)
This worked well. The only thing I’m having trouble with is it not finding the data_directory on reboot. If I reboot the droplet and try to run psql, it gives me this error:
psql: could not connect to server: No such file or directory
If I manually restart the posgresql service, it will find the directory and work again.
Any ideas how to get the postgresql service to wait for the block storage to mount?
Thanks!
I don’t know the exact answer, but if you’re running Ubuntu 16.04, systemd controls the order in which services are started. For 14.04, it’s Upstart.
You might try asking in the Q&A section to get more eyes on your question.
You need to check the permissions to /postgresql/9.5/main directory: it should be 700
Thanks for the amazing tutorial. There is a small issue though.
First you’re creating a backup like this;
But then try to remove it like this;
Might want to fix that.
Thank you! I’ve made that correction.
thanks for the tutorial. it works well except that bash and psql history for postgres user doesn’t work afterwards. do i miss something?
This comment has been deleted
creating a soft link solved my problem:
Sweet… Worked fine.
Love these tutorials!
You should not blow away the /var/lib/postgresql/9.x/main because the daemon scripts will still point to it with “-D” argument. The -D directory needs to exist so that the postmaster will leave its pid.
The easier way for most consequently is just to mv /var/lib/postgresql/9.x/main and make a symlink in place of /var/lib/postgresql/9.x/main.
Thanks for the tutorial, unfortunately after successfully restarting the server I can no longer connect to it. I get the error - psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket
Running Ubuntu 16.04.2 and PostgreSQL9.6 Any hints?
Ps. can connect no problem when I rsync the data back to the original directory. Cheers
Did some more digging. It was a drive mount issue - drive was a shared NTFS. Reformatted to EXT3 and actually checked fstab options and now can connect to Posgresql no problem. Thanks again.
Hi I got the same error on ubuntu14.04 and postgresql 9.5 which is ‘could not connect to server: No such file or directory’.
I finally figured out that it’s that my changed data directory which is an external SSD drive does not give access to user postgres the access to see the drive even though I’ve already changed the ownership of the new data directory(which is inside the SSD drive) to postgres.
I got help by this thread: https://dba.stackexchange.com/questions/124617/postgres-user-cannot-access-location-of-new-data-directory
I had the same problem and solved by adding user postgres to directory owner group. Hope this helps too.
Nice, clean, and straight forward. Good job Melissa!
I have two databases on postgreSQL. I am running out of disk space and want to move one of my databases to a different directory. How can I do this?
To quote https://www.postgresql.org:
Thank you so much. This tutorial was extremely helpful!
Thank you for this procedure! One contribution with regards to the modification of the postgresql.conf file: one can actually use the standard tool to change the configuration, ie:
pg_conftool set data_directory /mnt/volume-nyc1-01/postgresql/9.5/mainI followed this article and everything seems to have worked. Now I resized the CPU & RAM of the droplet and after a restart all the tables which I have created after I changed the postgresql location are gone.
The data_directory points to the volume but the size of the data_directory cannot hold the data. Bizarrly the main.bak is far larger than it should be. Is it possible that my database is now somehow locked inside main.bak?
Any ideas how to recover the tables?
This was the case. Following this article it turned out that PostgreSQL continued to write into the main.bak file instead of the data_location on block storage. I could recover the data from the *.bak file into the data_location but there is there something wrong in the order of steps as described in this article. This needs to be investigated and tested more thoroughly.
Thank you for the great tutorial. I found an issue that might be unique to some cases, it was for mine.
I had a subdirectory in the in /var/lib/postgresql/9.6 directory ( say abc directory) that was linked from /var/lib/postgresql/9.6/main/pg_tblspc/ folder as XXXX . The rsync copies all the files but then you have to have change the link by using
cd /mnt/volume-nyc1-01/postgresql/9.6/main/pg_tblspc sudo -u postgres /mnt/volume-nyc1-01/postgresql/9.6/abc XXXX
Now you can delete both main folder and abc folder from /var/lib/postgresql/9.6 directory
If i do this, will postgre still be able to read the data from the older data_directory? or what should i do to have postgre to read from a directory (that has no space left) and a new volume?
In case that something goes wrong, how do i use the backup?
Thanks
I also got the error 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”?
make sure about permissions. even on folder before postgresql
Exact same problem. I was able to revert back and try again with the same problem. Did you figure how to to get around this issue?
In Postgres 10, one needs to also switch to user
postgresand runinitdb -D [new_directory]use
sudo rsync -rtv --modify-window=1 /source /destfor external fat32 drivesFor CentOS 7 its same the only differences are…
Config is: /var/lib/pgsql/11/data/postgresql.conf Data directory is: /var/lib/pgsql/11/data
Hello, this instruction worked well for me. I can create a database afterwards and fill it with data. But after 4 hours my databaseserver crashed and the postgresql log file shows that it serverprocess can’t create a new WAL segment file. The first segment file 000000010000000000000001 exists when i change the data directory, so it works first. But when the server shutdown i can see in the log file: can’t open pg_wal/000000010000000000000002, file not found.
Did somebody know, why the serverprocess can’t create this file automatically? Thanks.