Overview
This guide describes how to migrate a PostgreSQL database from one mount point to another using rsync.
Important: • Always ensure data integrity before proceeding with the migration. • Take a full data backup before starting. • Take a VM snapshot as an additional safety measure. |
Step 1: Verify Current Data Directory
Before migrating, confirm the current PostgreSQL data directory:
| sudo -u postgres psql -c "SHOW data_directory;" |
Example output:
data_directory ------------------------ /var/lib/pgsql/14/data (1 row) |
Step 2: Migrate Data to the New Filesystem
Use rsync to copy the data with all attributes preserved:
| rsync -av /var/lib/pgsql/14/data /new_Filesystem_path |
Step 3: Set Permissions
After the data is copied, update the ownership of the new directory:
| chown -R postgres:postgres /new_Filesystem_path |
Step 4: Update PostgreSQL Configuration
Open the PostgreSQL.conf file. The path depends on your PostgreSQL version (e.g., version 14):
| vi /var/lib/pgsql/14/data/postgresql.conf |
Under the FILE LOCATIONS section, locate the data_directory line. Remove the leading # to uncomment it, and update the path:
. . . data_directory = '/new_Filesystem_path' . . . |
Save and exit the file.
Step 5: Restart and Verify
Restart the PostgreSQL service:
| systemctl restart postgresql-14 |
Verify the new data directory is active:
| sudo -u postgres psql -c "SHOW data_directory;" |
Step 6: Make the Change Permanent
| Note: The postgresql.conf change alone is temporary and will revert to default after a system restart. To make the change permanent, update the systemd service file. |
Edit the systemd service file:
| vi /usr/lib/systemd/system/postgresql-14.service |
Locate the PGDATA line and update it to the new filesystem path:
. . . # Location of database directory Environment=PGDATA=/new_Filesystem_path/data . . . |
Reload the systemd daemon and restart PostgreSQL:
systemctl daemon-reload systemctl restart postgresql-14 |
Once complete, optionally reboot the system to confirm that the PostgreSQL service starts correctly after boot.
Step 7 (Optional): Remove the Old Data Directory
Once everything is verified and running correctly, you can remove the old data directory to free up storage:
| rm -rf /var/lib/pgsql/14/data |
| Warning: Only run this command after confirming the new directory is fully operational and all data has been successfully migrated. |
Comments
0 comments
Please sign in to leave a comment.