Configuration PostgreSQL

PostgreSQL Configuration

Customize data directory PostgreSQL for Ubuntu 16.04

• Check data_directory position

sudo -i -u postgres
psql
-------
postgres=# SHOW data_directory;

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

• Restart service PostgreSQL

sudo service postgresql stop
### OR ###
## /etc/init.d/postgresql stop

• Rsync to new data_directory

sudo rsync -av /var/lib/postgresql /home/deploy/database

• Backup old configuration PostgreSQL

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

• Edit configuration PostgreSQL for new location

sudo nano /etc/postgresql/9.6/main/postgresql.conf
------
. . .
data_directory = '/home/deploy/database/postgresql/9.6/main'
. . .

• Restart PostgreSQL Services

sudo service postgresql start
### OR ###
## /etc/init.d/postgresql start

• Validate new data_directory position

sudo -i -u postgres
psql
-------
postgres=# SHOW data_directory;

              data_directory               
-------------------------------------------
 /home/deploy/database/postgresql/9.6/main
(1 row)

Notes

• References: Digital Ocean - PostgreSQL