Change the data directory of the PostgreSQL

In ubuntu 18.04 PostgreSQL is running on the default directory /usr/lib/postgresql/ and the data directory is ‘/etc/postgresql/10/main’

To get the data directory you can run

sudo -u postgres psql -c "show data_directory;"

To change the data directory, first, stop the PostgreSQL service

sudo service postgresql stop

I mounted a ZFS volume through iSCSI protocol in my home directory

/home/vignesh/pdata

That will be my target directory so whenever I want to create a backup I can take the ZFS snapshot

After mounting the FS, I changed to the owner to postgres

vignesh@vignesh:~$ sudo chown postgres pdata

Then modify the data_directory configuration in the postgresql.conf configuration file

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

Then Initiate the new data directory

vignesh@vignesh:~$ sudo /usr/lib/postgresql/10/bin/initdb -D pdata/

Then start the service and check the data directory again, the directory will be changed to the new folder.


Also published on Medium.

This site uses Akismet to reduce spam. Learn how your comment data is processed.