Change PostgreSQL Data Directory

Change-PostgreSQL-Data-Directory.md

1. Locate Current Data Directory
Find the current data directory (default: /var/lib/pgsql/13/data):

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

2. Stop PostgreSQL Service

sudo systemctl stop postgresql-13

3. Move Data to the New Location

• a. Create and Set Permissions for the New Directory

sudo mkdir -p /home/user/postgresql_data
sudo chown -R postgres:postgres /home/user/postgresql_data
sudo chmod 700 /home/user/postgresql_data

• b. Transfer Data

sudo rsync -av --progress /var/lib/pgsql/13/data/ /home/user/postgresql_data/

4. Update postgresql.conf
Edit postgresql.conf to point to the new data directory:

sudo vi /home/user/postgresql_data/postgresql.conf

Update:

data_directory = '/home/user/postgresql_data'

5. Update SELinux Contexts (if enabled)
Set proper SELinux contexts for the new directory:

sudo semanage fcontext -a -t postgresql_db_t "/home/user/postgresql_data(/.*)?"
sudo restorecon -Rv /home/user/postgresql_data

6. Update Systemd Service File
Edit the PostgreSQL service file:

sudo vi /usr/lib/systemd/system/postgresql-13.service

Update:

Environment=PGDATA=/home/user/postgresql_data/

Reload systemd:

sudo systemctl daemon-reload

7. Restart PostgreSQL
Restart the service and check its status:

sudo systemctl start postgresql-13
sudo systemctl status postgresql-13

8. Verify Data and Connectivity
Confirm the database is using the new directory and verify data integrity:

sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c "\l"

9. Rename or Remove 🔥 Old Data Directory (Optional)
If everything works fine, rename or remove the old directory:

# Rename
sudo mv /var/lib/pgsql/13/data /var/lib/pgsql/13/data-old-data
# or Remove
sudo rm -rf /var/lib/pgsql/13/data/