PostgreSQL Backup, Restore, and Disaster Recovery

Disaster recovery is crucial for maintaining database reliability and business continuity. PostgreSQL offers multiple backup and restore methods to protect your data from accidental loss, hardware failures, or software issues.

1. Types of Backups

• Logical Backups: Extract data and schema using tools like pg_dump and pg_dumpall. Useful for smaller databases and cross-version migration.
• Physical Backups: Block-level copy of database files using pg_basebackup. Suitable for large databases and faster restoration.
• Continuous Archiving / WAL Archiving: Captures Write-Ahead Logs (WAL) for point-in-time recovery.

2. Logical Backup with pg_dump

-- Backup a single database
            pg_dump -U username -F c -b -v -f "mydb.backup" mydb

            -- Backup all databases
            pg_dumpall -U username > all_databases.sql

3. Restore Logical Backup

-- Restore single database
            pg_restore -U username -d mydb -v "mydb.backup"

            -- Restore all databases
            psql -U username -f all_databases.sql postgres

4. Physical Backup with pg_basebackup

-- Create a base backup of PostgreSQL data directory
            pg_basebackup -h primary_host -D /var/lib/postgresql/12/main -U replicator -v -P --wal-method=stream

Physical backups include all data files, WAL logs, and configurations. They are faster for large databases and support point-in-time recovery.

5. Point-in-Time Recovery (PITR)

PITR allows restoring the database to a specific moment in time using base backups and WAL archives:

-- 1. Restore base backup
            tar -xvf base_backup.tar -C /var/lib/postgresql/12/main

            -- 2. Configure recovery.conf or standby.signal
            restore_command = 'cp /path/to/wal_archive/%f %p'
            recovery_target_time = '2025-09-23 15:30:00'

            -- 3. Start PostgreSQL to replay WAL logs up to target time

6. Replication and Disaster Recovery

• Synchronous replication ensures no data loss in primary failure.
• Asynchronous replication allows faster performance but may risk minor data loss.
• Standby servers can be promoted for quick recovery in case of primary failure.

7. Backup Scheduling and Automation

• Use cron jobs to schedule regular pg_dump or pg_basebackup.
• Automate WAL archiving for continuous backup.
• Test backup and restore procedures periodically to ensure reliability.

8. Best Practices

• Keep multiple copies of backups in different locations.
• Encrypt backups to secure sensitive data.
• Monitor backup and restore operations for errors.
• Document recovery procedures and maintain a disaster recovery plan.
• Combine backups with replication for high availability and minimal downtime.

Conclusion

Proper backup, restore, and disaster recovery strategies are essential for maintaining PostgreSQL reliability. By combining logical and physical backups, WAL archiving, point-in-time recovery, and standby servers, you can minimize data loss, reduce downtime, and ensure business continuity even in the face of failures.