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

[email protected]:~$ 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

[email protected]:~$ 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.

Newsletter Updates

Enter your email address below to subscribe to our newsletter

Leave a ReplyCancel Reply

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