How to change PostgreSQL database data directory

01 September,2020 by Jack Vamvas

Question: The Postgres data_directory  is showing a value  via the SHOW data_directory command. 

postgres=# SHOW data_directory;
data_directory
------------------------
/var/lib/postgres/data
(1 row)

 

How can I change the database directory to another directory?

 

Answer:

The steps below are a standard approach. The steps will vary according to specific requirements, and may require a more customised approach

Step 1 - Identify current data directory path 

SHOW data_directory;

Step 2- Stop Postgresql services

systemctl status  <service_name>

systemctl stop <service_name>

Step 3 - create a blank directory on the target path 

e.g    mkdir /mydirectory/data

--change ownership to the postgres user

chown  postgres:postgres /mypostgres/data

Step 4 - Use initdb to create  creates a new PostgreSQL database cluster

./initdb -D /mydirectory/data

The -D option specifies the directory where the database cluster should be stored

initdb creates a new database cluster . The initdb command has a number of other switches to customise the setup. Check the documentation for extra details 

 

Some extra steps that may be required will for  other configurables that you may have set up. Typical steps include:

1) Service start up configurables. 

/usr/lib/systemd/system/postgres.service

2)There may be a password edit required , before trust authentication is modified

ALTER USER davide WITH PASSWORD 'hu8jmn3'

3) enabling trust authentication in the pg_hba.conf file for local connection

 

 

 

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to change PostgreSQL database data directory


dba-ninja.com