Debian PostgreSQL Data Recovery Strategies

1. Preparation Before Recovery

• Verify Backup Integrity: Ensure you have recent, valid backups (logical .dump/.sql or physical tarballs). Test backups periodically by restoring them to a test environment.
• Stop PostgreSQL Service: To prevent further data corruption, stop the service before recovery:

  sudo systemctl stop postgresql
  

• Identify Data Loss Scope: Check logs (/var/log/postgresql/) to determine the deletion time, affected tables, and whether WAL (Write-Ahead Logging) files cover the lost data.

2. Logical Backup & Recovery (Recommended for Most Cases)

Logical backups use SQL-formatted files, ideal for selective restoration (e.g., a single table).

• Backup with pg_dump:
  Use the custom format (-F c) for compressibility and flexibility. Include blobs (-b) and verbose output (-v):

  pg_dump -U postgres -F c -b -v -f "/path/to/backup.dump" database_name
  

• Restore with pg_restore:
  For .dump files, use pg_restore to recreate the database schema and data. Example for a specific database:

  sudo -u postgres pg_restore -U postgres -d target_database /path/to/backup.dump
  

  For compressed backups, decompress first (gunzip backup.dump.gz) before restoring.

3. Physical Backup & Recovery (For Large Databases)

Physical backups copy database files directly, faster for large datasets but less flexible.

• Backup with pg_basebackup:
  Create a full, compressed backup of the entire cluster. The -F t flag outputs a tarball, and -z compresses it:

  pg_basebackup -F t -z -P -D /path/to/physical_backup -h localhost -p 5432 -U postgres
  

• Restore with File Copy:
  Stop PostgreSQL, replace the data directory with the backup, and restart:

  sudo systemctl stop postgresql
  sudo rm -rf /var/lib/postgresql/*/data/*
  sudo cp -r /path/to/physical_backup/* /var/lib/postgresql/*/data/
  sudo chown -R postgres:postgres /var/lib/postgresql/*/data
  sudo systemctl start postgresql
  

  This method restores the database to the state at the time of the backup.

4. Point-in-Time Recovery (PITR) for Precise Recovery

PITR uses WAL files to recover to a specific timestamp or transaction, critical for accidental deletions or corruptions.

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

  wal_level = replica
  archive_mode = on
  archive_command = 'cp %p /path/to/wal_archive/%f'
  max_wal_senders = 2
  wal_keep_segments = 100
  

  Restart PostgreSQL to apply changes:

  sudo systemctl restart postgresql
  

• Perform PITR:
  1. Stop PostgreSQL and clear the existing data directory.
  2. Copy a base backup (from pg_basebackup) to the data directory.
  3. Create postgresql.auto.conf in the data directory with recovery settings:

     restore_command = 'cp /path/to/wal_archive/%f %p'
     recovery_target_time = 'YYYY-MM-DD HH:MI:SS'
     recovery_target_timeline = 'latest'
     

  4. Start PostgreSQL in recovery mode. It will apply WAL files until reaching the target time:

     sudo pg_ctl start -D /var/lib/postgresql/*/data -o "-p 5433"
     

  5. Verify recovery (check logs or connect to the database) and promote the server to normal operation if successful.

5. Advanced Tools for Severe Corruption

For corrupted WAL files or physical data damage, use specialized tools:

• pg_resetwal: Resets WAL logs and pg_control to recover from corruption (use with caution—this can cause data loss if misapplied).
• Third-Party Tools: Tools like pg_repack (for defragmentation/recovery) or Wondershare Recoverit (for physical file recovery) may help, but success is not guaranteed.

6. Post-Recovery Steps

• Validate Data: Connect to the database and run queries to ensure lost data is restored.
• Monitor Performance: Check logs for errors and adjust configuration (e.g., shared_buffers) if needed.
• Update Backup Strategy: If recovery exposed gaps (e.g., missing WAL files), enhance your backup schedule (e.g., daily incremental backups) and test restores more frequently.