PostgreSQL Backup and Recovery Quiz — PG_Dump vs. PG_BackRest

Master PostgreSQL backup and recovery — a critical skill for every PostgreSQL DBA. This quiz compares the two most widely used backup tools: pg_dump for logical backups and pgBackRest for physical, incremental, and WAL-based backups. Understanding when and how to use each tool is essential in production database environments.

PostgreSQL Backup & Recovery — Key Concepts Every DBA Must Know

PG_Dump — Logical Backups

pg_dump exports a single database into a SQL script or archive file. It is platform-independent, making it ideal for migrations and schema transfers. Key flags to know:

• -F c — custom format (compressed, supports parallel restore)
• -F d — directory format (best for large databases)
• pg_dumpall — dumps all databases including roles and tablespaces
• pg_restore — restores from custom or directory format dumps

Limitation: pg_dump takes a logical snapshot — it does not support Point-in-Time Recovery (PITR) and can be slow for very large databases.

PG_BackRest — Physical Backups at Scale

pgBackRest is a robust backup solution designed for large PostgreSQL deployments. It supports:

• Full backups — complete copy of the data directory
• Differential backups — changes since the last full backup
• Incremental backups — changes since the last backup of any type
• Delta restore — restores only changed blocks, dramatically faster recovery
• Parallel processing — uses multiple CPU cores for faster backup/restore
• WAL archiving — enables PITR to any point in time

PG_Dump vs PG_BackRest — When to Use Which

Common DBA Interview & Exam Mistakes

• Assuming pg_dump supports PITR — it does not
• Forgetting that pgBackRest requires WAL archiving to be enabled for PITR
• Not testing restores regularly — backups are only valid when restore works
• Skipping retention policy configuration — disk fills up silently
• Confusing pg_dump (single DB) with pg_dumpall (all DBs + globals)