postresql: move data directory to another location

0
(0)

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 director

In this example, we’re moving the data to a block storage device mounted at  /mnt/diskd

No matter what underlying storage you use, the following steps can help you move the data directory to a new location.

  1. Verify existing data directory. Run psql.
$ sudo -u postgres psql

Once you’ve entered the monitor, select the data directory:

postgres=# SHOW data_directory;

Output:

     data_directory       
------------------------------
/var/lib/postgresql/9.6/main
(1 row)

This output confirms that PostgreSQL is configured to use the default data directory, /var/lib/postgresql/9.6/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:

sudo systemctl stop postgresql

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

sudo systemctl status postgresql

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.6 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/diskd

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:

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

Now we’re ready to turn our attention to configuration.

2. Pointing new data location

PostgreSQL has several ways to override configuration values. By default, the data_directory is set to /var/lib/postgresql/9.6/main in the /etc/postgresql/9.6/main/postgresql.conf file. Edit this file to reflect the new data directory:

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

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/diskd/postgresql/9.6/main'

We’re ready to start PostgreSQL.

sudo systemctl start postgresql
sudo systemctl status postgresql

To make sure that the new data directory is indeed in use, start the PostgreSQL monitor.

sudo -u postgres psql

postres=# SHOW data_directory;

Output:

          data_directory
-----------------------------------------
/mnt/diskd/postgresql/9.6/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:

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

All done.

Similar Posts:

413

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top