Debian PostgreSQL Backup and Recovery Steps

This guide covers logical (pg_dump/pg_restore) and physical (pg_basebackup) backup methods, along with Point-in-Time Recovery (PITR) for Debian-based PostgreSQL systems.

Logical Backup (pg_dump)

Logical backups export database objects (tables, schemas, data) as SQL or custom-format files, suitable for granular recovery (e.g., individual tables).

Backup Steps

1. Install PostgreSQL Client Tools (if not installed):

   sudo apt update && sudo apt install postgresql-client
   

2. Run pg_dump Command:
   Use sudo -u postgres to execute as the PostgreSQL superuser. Key options:

   • -U: Database user (default: postgres).
   • -d: Target database name.
   • -F: Output format (t=custom binary, c=plain SQL, p=plain text).
   • -f: Output file path.

   Examples:

   • Full database backup (custom format, compressed):

     sudo -u postgres pg_dump -U postgres -F t -f /var/backups/mydb_backup.tar mydb
     gzip /var/backups/mydb_backup.tar  # Optional compression
     

   • Plain SQL backup (restorable via psql):

     sudo -u postgres pg_dump -U postgres -f /var/backups/mydb.sql mydb
     

Restore Steps

1. Stop PostgreSQL (recommended for consistency):

   sudo systemctl stop postgresql
   

2. Restore Using pg_restore (Custom Format):

   sudo -u postgres pg_restore -U postgres -d mydb /var/backups/mydb_backup.tar
   

3. Restore Using psql (Plain SQL):

   sudo -u postgres psql -U postgres -d mydb -f /var/backups/mydb.sql
   

4. Start PostgreSQL:

   sudo systemctl start postgresql
   

Physical Backup (pg_basebackup)

Physical backups copy the entire PostgreSQL data directory (including WAL files), ideal for large databases or full-cluster recovery.

Backup Steps

1. Create Backup Directory:

   sudo mkdir -p /mnt/pg_backup
   sudo chown postgres:postgres /mnt/pg_backup
   

2. Run pg_basebackup:

   sudo -u postgres pg_basebackup -D /mnt/pg_backup -F t -z -P -h localhost -p 5432 -U postgres
   

   • -D: Backup destination.
   • -F t: Tar format (compresses data).
   • -z: Compress output.
   • -P: Show progress.

Point-in-Time Recovery (PITR)

PITR recovers the database to a specific timestamp or transaction ID using WAL (Write-Ahead Logging) archiving.

Prerequisites

1. Enable WAL Archiving:
   Edit postgresql.conf (typically at /etc/postgresql/<version>/main/postgresql.conf):

   wal_level = replica
   archive_mode = on
   archive_command = 'cp %p /mnt/pg_arch/%f'
   max_wal_senders = 2
   wal_keep_segments = 32
   

   Restart PostgreSQL:

   sudo systemctl restart postgresql
   

2. Perform Full Backup:
   Use pg_basebackup (see above) to create a base backup before enabling PITR.

Recovery Steps

1. Stop PostgreSQL:

   sudo systemctl stop postgresql
   

2. Clear Existing Data Directory:

   sudo rm -rf /var/lib/postgresql/<version>/main/*
   

3. Restore Base Backup:

   sudo cp -r /mnt/pg_backup/* /var/lib/postgresql/<version>/main/
   

4. Configure PITR Parameters:
   Create/modify postgresql.auto.conf in the data directory:

   restore_command = 'cp /mnt/pg_arch/%f %p'
   recovery_target_time = '2025-11-07 10:00:00'  # Target timestamp
   recovery_target_timeline = 'latest'
   

5. Start PostgreSQL in Recovery Mode:

   sudo systemctl start postgresql
   

   PostgreSQL will apply WAL logs until reaching recovery_target_time, then exit recovery mode automatically.

6. Verify Recovery:

   sudo -u postgres psql -c "\l"  # List databases
   sudo -u postgres psql -d mydb -c "\dt"  # List tables
   

Key Notes

• Automation: Schedule logical backups via cron (e.g., daily at 2 AM):

  0 2 * * * sudo -u postgres pg_dump -U postgres -F t -f /var/backups/$(date +\%F)_mydb.tar mydb
  

• Testing: Regularly test backups by restoring them to a staging environment.
• Security: Store backups in secure locations (e.g., encrypted volumes) and restrict access to authorized users.
• Large Databases: Prefer pg_basebackup for physical backups (faster for large datasets).

For detailed options, refer to the PostgreSQL documentation.