Debian PostgreSQL Backup and Recovery Tips

Logical Backup with pg_dump

pg_dump is a flexible tool for logical backups, creating SQL scripts or custom-format archives that can be restored on other PostgreSQL instances.

• Basic Syntax: Use -U for the username, -d for the database name, -f to specify the output file, and -F to set the format (e.g., t for tar, c for custom).
  Example: pg_dump -U postgres -h localhost -p 5432 -F t mydb > mydb_backup.tar.
• Compression: Pipe the output to gzip to reduce file size (e.g., pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz). This is recommended for large databases.
• Backup All Databases: Use pg_dumpall (run as postgres user) to back up all databases, including roles and tablespaces.
  Example: sudo -u postgres pg_dumpall -f /path/to/all_databases.sql.

Physical Backup with pg_basebackup

For large databases or whole-cluster recovery, pg_basebackup creates a physical copy of the data directory, including WAL (Write-Ahead Logging) files.

• Prerequisites: Ensure the PostgreSQL user has read access to the data directory and write access to the backup location.
• Basic Syntax: Use -D to specify the backup directory, -F for the format (e.g., t for tar), -z to compress, and -P to show progress.
  Example: pg_basebackup -D /pg_basebackup -F t -z -P -h localhost -p 5432 -U postgres.

Automation with Cron

Schedule regular backups using cron to avoid manual intervention.

• Example Task: Add a line to your crontab (crontab -e) to run a daily pg_dump at 1 AM:
  0 1 * * * /usr/bin/pg_dump -U postgres -h localhost -p 5432 -F t mydb | gzip > /path/to/backups/mydb_$(date +\%Y\%m\%d).tar.gz.

Restore from Logical Backups

• SQL Format: Use psql to execute the SQL script.
  Example: psql -U postgres -d mydb -f /path/to/mydb_backup.sql.
• Custom/Tar Format: Use pg_restore for compressed or custom-format backups.
  Examples:
  • Compressed tar: pg_restore -U postgres -d mydb /path/to/mydb_backup.tar.gz
  • Tar format: pg_restore -U postgres -d mydb /path/to/mydb_backup.tar.

Restore from Physical Backups

• Stop PostgreSQL: Halt the service to prevent data corruption during recovery.
  Example: sudo systemctl stop postgresql.
• Copy Backup Files: Replace the existing data directory with the backup files.
  Example: cp -r /pg_basebackup/* /var/lib/postgresql/data/.
• Start PostgreSQL: Restart the service to apply the restored data.
  Example: sudo systemctl start postgresql.

Point-in-Time Recovery (PITR)

PITR allows restoring the database to a specific time or transaction, ideal for recovering from accidental deletions or corruption.

• Enable WAL Archiving: Modify postgresql.conf to turn on archiving and set the WAL level.
  Example settings:

  wal_level = replica
  archive_mode = on
  archive_command = 'test -d /usr/local/pgsql/arch/$(date +%Y%m%d) || mkdir -p /usr/local/pgsql/arch/$(date +%Y%m%d); cp %p /usr/local/pgsql/arch/$(date +%Y%m%d)/%f'
  ```.  
  

• Create Base Backup: Use pg_basebackup to take a full backup before enabling PITR.
  Example: pg_basebackup -D /backup/full_backup -F t -z -P -h localhost -p 5432 -U postgres.
• Recover to a Specific Time:
  1. Stop PostgreSQL and clear the data directory.
  2. Copy the base backup to the data directory.
  3. Create postgresql.auto.conf in the data directory with restore and recovery parameters:

     restore_command = 'cp /usr/local/pgsql/arch/$(date +%Y%m%d)/%f %p'
     recovery_target_time = '2025-09-25 14:30:00'
     recovery_target_timeline = 'latest'
     ```.  
     

  4. Start PostgreSQL to begin recovery. The server will apply WAL logs until reaching the target time.

Backup Validation and Testing

Regularly test backups to ensure they are valid and can be restored successfully.

• Logical Backups: Use pg_restore with the --list option to view the contents of a custom-format backup without restoring it.
  Example: pg_restore --list /path/to/mydb_backup.tar.
• Physical Backups: Restore the backup to a test environment (e.g., a local VM) and verify data integrity by running queries.
• Automated Checks: Use tools like pg_verifybackup (available in PostgreSQL 14+) to validate the integrity of physical backups.

Additional Tips

• Permissions: Always run backup/restore commands as the postgres user (or a user with sufficient privileges) to avoid permission issues.
• Storage: Store backups in multiple locations (e.g., external drives, cloud storage) to protect against hardware failure.
• Retention Policy: Define a retention policy to delete old backups and free up storage (e.g., keep daily backups for 7 days, weekly backups for 4 weeks).
• Version Compatibility: Ensure backups are compatible with the PostgreSQL version of the target server (backups from newer versions may not restore on older ones).