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.

Originally published at asvignesh.