Migration of Postgres Database from one Mount point to another

Always ensure data integrity before proceeding with the migration. 

Take the data backup, and for the safe side, please ensure to take a snapshot of the VM.

# Backup command

Postgres Default path:

To check the current running directory of the Database using the command below

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

Example:

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

data_directory

------------------------

/var/lib/pgsql/14/data

(1 row)

Migration of Data from one File System to another:

We can able to migrate the data using the rsync method,

# rsync -av /var/lib/pgsql/14/data /new_Filesystem_path

Changing Permissions:

Once the Data is copied with attributes by rsync, we need to change the owner of the new directory

# chown -R postgres:postgres /new_Filesystem_path

Pointing the New filesystem for Postgres:

According to the Postgres Version, the Path of the conf file has been located. For Instance, we are using version 14.

# vi /var/lib/pgsql/14/data/postgresql.conf

Change the New Filesystem path in the line below, located in the FILE LOCATIONS Heading, and remove the # to enable that line and save it.

. . .

data_directory = '/new_Filesystem_path'

. . .

After the PostgreSQL.conf file has been changed and saved. We need to restart the PostgreSQL service.

# systemctl restart postgresql-14

Verification of Postgres data Status:

After saving, we can check the path using by below command.

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

For Permanent Change:

The above changes were only temporary. If the system restarted, the configuration would revert to the default. So, to do it permanently, we need to change the systemd file.

# vi /usr/lib/systemd/system/postgresql-14.service

And change the line to New file system. For your reference, find below

. . .

# Location of database directory

Environment=PGDATA=/new_Filesystem_path/data

. . .

Restarting the Services:

Then need to reload the daemon for the source path change and  restart the PostgreSQL service also

# systemctl daemon-reload

# systemctl restart postgresql-14

Once Everything is done, if required, we can restart the system to check whether postgresql service will run after booting.

Removing the old Data Directory:

Once Everything is up and running, we can remove the old data directory if required to release the storage.

# rm -rf var/lib/pgsql/14/data