PostgreSQL Backup and Restoration

• Pradip
• 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.