How to move a PostgreSQL 12 data directory

The following are an easy to follow steps to change the data directory in PostgreSQL 12.

FIND THE LOCATION OF POSTGRES CONFIG FILE

In your DB console execute the following query:

SHOW config_file

The result will show you the path to your postgresql.conf file, something like this:

/var/lib/pgsql/12/data/postgresql.conf

FIND THE LOCATION OF POSTGRES DATA DIRECTORY

SHOW data_directory

The result is:

/var/lib/pgsql/12/data

CHANGE THE PATH FROM OLD TO NEW DATA DIRECTORY

In my case, I want to change the data directory location from the current /var/lib/pgsql/12/data, to a new directory /home/postgres/data

I have turned off the PostgreSQL first, by running:

sudo systemctl stop postgresql-12

Once the PostgreSQL server is down, I have moved data folder from /var/lib/pgsql/12/ to /home/postgres/.

Once done, log in to your Linux server and open the PostgreSQL config file (postgresql.conf) on the suggested path (in step one). Then, find the ‘data_directory’ directive and change it to a new data folder, like this:

 

You may also want to edit your systemd service. To do so, go to /lib/systemd/system and find postgresql-12.service file (this can possibly be called differently on your system). Once found, open and edit the ‘Environment=PGDATA’ directive:

Once completed, save the file, refresh systemctl and start the PostgreSQL:

systemctl daemon-reload
sudo systemctl start postgresql-12

Enjoy!

Facebook Comments