The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Backup and Restore

Next ►
►

In this section, we will cover the essential concepts and practical steps for backing up and restoring databases in PostgreSQL. Ensuring that you can reliably back up and restore your data is crucial for maintaining data integrity and availability.

Key Concepts

  1. Backup Types:

    • SQL Dump: A logical backup that generates SQL statements to recreate the database.
    • File System Level Backup: A physical backup that copies the database files directly.
    • Continuous Archiving and Point-in-Time Recovery (PITR): Advanced backup strategy using Write-Ahead Logging (WAL).

  2. Restore Types:

    • Restoring from SQL Dump: Replaying the SQL statements to recreate the database.
    • Restoring from File System Backup: Copying the database files back to the data directory.
    • Point-in-Time Recovery: Restoring the database to a specific point in time using WAL files.

Practical Examples

  1. SQL Dump Backup

The pg_dump utility is used to create a logical backup of a PostgreSQL database.

pg_dump -U [username] -h [hostname] -p [port] -F c -b -v -f [backup_file] [database_name]
  • -U [username]: Specifies the database user.
  • -h [hostname]: Specifies the host.
  • -p [port]: Specifies the port.
  • -F c: Specifies the format (custom).
  • -b: Includes large objects.
  • -v: Verbose mode.
  • -f [backup_file]: Specifies the output file.
  • [database_name]: Specifies the database name.

Example:

pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f mydb_backup.dump mydb

  1. Restoring from SQL Dump

The pg_restore utility is used to restore a database from a custom-format dump file.

pg_restore -U [username] -h [hostname] -p [port] -d [database_name] -v [backup_file]
  • -U [username]: Specifies the database user.
  • -h [hostname]: Specifies the host.
  • -p [port]: Specifies the port.
  • -d [database_name]: Specifies the target database.
  • -v: Verbose mode.
  • [backup_file]: Specifies the backup file.

Example:

pg_restore -U postgres -h localhost -p 5432 -d mydb -v mydb_backup.dump

  1. File System Level Backup

To perform a file system level backup, you need to stop the PostgreSQL server and copy the data directory.

Stopping the PostgreSQL Server:

sudo systemctl stop postgresql

Copying the Data Directory:

sudo cp -r /var/lib/postgresql/[version]/main /path/to/backup/

Starting the PostgreSQL Server:

sudo systemctl start postgresql

  1. Continuous Archiving and Point-in-Time Recovery (PITR)

Setting Up Continuous Archiving:

  1. Edit postgresql.conf to enable WAL archiving:

    archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

  2. Restart PostgreSQL:

    sudo systemctl restart postgresql

Performing a Base Backup:

pg_basebackup -U [username] -h [hostname] -D /path/to/backup -F tar -z -P

Restoring from Base Backup and WAL Files:

  1. Stop the PostgreSQL server.

  2. Clear the data directory and restore the base backup:

    sudo rm -rf /var/lib/postgresql/[version]/main/*
sudo tar -xvf /path/to/backup/base.tar -C /var/lib/postgresql/[version]/main/

  3. Copy the archived WAL files to the pg_wal directory.

  4. Create a recovery.conf file in the data directory:

    restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = 'YYYY-MM-DD HH:MM:SS'

  5. Start the PostgreSQL server.

Practical Exercises

Exercise 1: Create and Restore a SQL Dump

  1. Create a Backup:

    • Use pg_dump to create a backup of a sample database.
    • Verify the backup file is created.

  2. Restore the Backup:

    • Use pg_restore to restore the database from the backup file.
    • Verify the restored database.

Solution:

# Create a backup
pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f sampledb_backup.dump sampledb

# Restore the backup
pg_restore -U postgres -h localhost -p 5432 -d sampledb -v sampledb_backup.dump

Exercise 2: Perform a File System Level Backup

  1. Stop the PostgreSQL Server:

    • Use the appropriate command to stop the server.

  2. Copy the Data Directory:

    • Copy the data directory to a backup location.

  3. Start the PostgreSQL Server:

    • Restart the server and verify it is running.

Solution:

# Stop the PostgreSQL server
sudo systemctl stop postgresql

# Copy the data directory
sudo cp -r /var/lib/postgresql/13/main /path/to/backup/

# Start the PostgreSQL server
sudo systemctl start postgresql

Common Mistakes and Tips

  • Mistake: Forgetting to stop the PostgreSQL server before performing a file system level backup.

    • Tip: Always ensure the server is stopped to avoid data corruption.

  • Mistake: Not verifying the backup file after creation.

    • Tip: Always check the backup file to ensure it is complete and not corrupted.

  • Mistake: Incorrect archive_command in continuous archiving.

    • Tip: Test the archive_command manually to ensure it works as expected.

Conclusion

In this section, we covered the essential techniques for backing up and restoring PostgreSQL databases. We explored different backup types, including SQL dumps, file system level backups, and continuous archiving for point-in-time recovery. Practical examples and exercises were provided to reinforce the concepts. Understanding these techniques is crucial for maintaining data integrity and ensuring business continuity.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved