- 16 Nov, 2024
- 0 Comments
- 2 Mins Read
PostgreSQL Backup and Restoration
PostgreSQL Backup Using pg_basebackup
Creating a backup of your PostgreSQL database is critical for data safety. To take a backup using pg_basebackup, follow these simple steps:
Command:
pg_basebackup -D /home/postgres/backup -F tar -z -P -U postgres -X fetch
This command creates a compressed and tarred backup, which is essential for restoring your PostgreSQL database later.
Step-by-Step Restoration
1. Stop the PostgreSQL Server
Before beginning the restoration process, stop the PostgreSQL server to ensure no active processes interfere with the restoration.
Command:
systemctl stop postgresql
2. Prepare the Data Directory
Next, remove or rename the current PostgreSQL data directory to avoid conflicts. After that, create a new empty data directory for the restoration.
Command:
mv /home/postgres/data /home/postgres/data_old
Create a new, empty data directory:
mkdir -p /home/postgres/data chown postgres:postgres /home/postgres/data chmod 700 /home/postgres/data
3. Extract the Backup
Now, extract the backup files into the new data directory. If your backup is stored in a tarball, use the following command:
tar -xvf /home/postgres/backup/base.tar.gz -C /home/postgres/data
If you have multiple tar files (e.g., pg_wal.tar.gz), extract them all.
4. Ensure Correct Permissions
After extracting the backup files, set the proper ownership and permissions for PostgreSQL to operate correctly.
Command:
chown -R postgres:postgres /home/postgres/data
5. Restore WAL Files (if needed)
If the backup included WAL (Write Ahead Log) files, extract them into the pg_wal directory:
Commands:
mkdir -p /home/postgres/data/pg_wal tar -xvf /home/postgres/backup/pg_wal.tar.gz -C /home/postgres/data/pg_wal chown -R postgres:postgres /home/postgres/data/pg_wal
6. Remove postmaster.pid (if present)
The postmaster.pid file may exist from the previous PostgreSQL instance, which could block the restoration. Remove this file before proceeding.
Command:
rm -f /home/postgres/data/postmaster.pid
7. Start PostgreSQL Server
After completing the restoration, start the PostgreSQL server to bring the database back online.
Command:
systemctl start postgresql
8. Verify Restoration
To ensure the restoration was successful, connect to the PostgreSQL server and run a simple query:
Command:
psql -U postgres -d postgres -c "SELECT 'Restoration successful!' AS status;"
Conclusion:
By following these steps, you can easily backup and restore your PostgreSQL database with pg_basebackup. Remember to stop the PostgreSQL server before restoring, and make sure all files have the correct ownership and permissions to avoid issues.