Backups with pg_dump and Restore
A backup is useful only if it can be restored and meets recovery requirements. pg_dump creates a logical backup of a database, and pg_restore can restore custom-format dumps. Physical backups and continuous archiving are used for different recovery objectives, including point-in-time recovery. Choose a backup strategy based on data size, recovery point objective (RPO), recovery time objective (RTO), and operational constraints.
Store backups securely, protect them from accidental deletion or ransomware, and test restoration regularly. A successful backup command is not proof that the backup is complete or recoverable.
Create and restore a custom-format dump
pg_dump -Fc -d app_db -f app_db.dump
createdb app_db_restore
pg_restore -d app_db_restore app_db.dump💡 The custom format supports selective and parallel restore options. Supply connection details and credentials securely in real deployments, and test the restored database before relying on the backup.
Key Takeaways
- Choose backups based on RPO and RTO.
- Logical and physical backups serve different recovery strategies.
- Regularly test restores, not just backup creation.
Knowledge Check
What proves that a backup is operationally useful?