Efficient PostgreSQL Management: Navigating Data Directory Relocation for Database Administrators in Linux

Overview

As a seasoned Database Administrator, ensuring the optimal performance and scalability of your PostgreSQL database is paramount. In the course of managing data growth, there comes a pivotal juncture—relocating the Data Directory. This guide, tailored for Database Administrators navigating Linux environments, unfolds a strategic roadmap to seamlessly migrate your PostgreSQL Data Directory. From assessing the current state to implementing a secure transition, each step is meticulously outlined. Join us on this journey, empowering you to harmonize your database infrastructure for continued efficiency and resilience

Environment Details:

• OS: Ubuntu 20.04 LTS

• Access: root/postgres

• PostgreSQL Version: 14

Getting Started

Step 1: Check Current Data Directory Location:

# Access PostgreSQL prompt
sudo -u postgres psql

# Check current data directory
SHOW data_directory;

data_directory
—---------------------
/var/lib/postgresql/14/main/data
(1 row)

Current data directory:

/var/lib/postgresql/14/main/data

Step 2: Shutdown PostgreSQL Service:

sudo systemctl stop postgresql

New data_directory location:

/postgres/data

Step 3: Copy/Move Data to New Location:

# Set permissions for the new directory
sudo chown postgres:postgres /postgres/data
sudo chmod -R 750 /postgres/data

# Copy data to the new location
cd /var/lib/postgresql/14/main/data
sudo cp -r * /postgres/data
cd /postgres/data
ls -lrt

Step 4: Update Data Directory Location in Config File:

# Backup the original configuration file
sudo cp /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf_copy

# Modify the configuration file
sudo vi /etc/postgresql/14/main/postgresql.conf

Update data_directory to:

data_directory = '/postgres/data/'

Step 5: Restart PostgreSQL:

sudo systemctl start postgresql
sudo systemctl status postgresql
tail -10f /var/log/postgresql/postgresql-14-main.log

Step 6: Verify New Data Directory Location:

# Access PostgreSQL prompt
sudo -u postgres psql

# Verify new data directory
SHOW data_directory;

New data directory:

data_directory
—---------------------
/postgres/data
(1 row)

Conclusion: By following these steps, you've effectively relocated the PostgreSQL Data Directory. Verify the changes to ensure seamless database operation.