PostgreSQL interview question · Most asked
How do backups and point-in-time recovery work in PostgreSQL?
Summary
Take physical base backups (pg_basebackup, or tools such as pgBackRest, Barman, WAL-G) and archive WAL continuously. To recover to a point in time, restore the base backup and replay archived WAL up to a target time, transaction or named restore point. pg_dump makes logical backups of single databases, useful for migrations and smaller data sets.
Longer Answer
pg_dump -Fcis consistent (it uses a snapshot) and portable across versions, but restores of large databases are slow and it does not capture everything cluster-wide (roles: usepg_dumpall --globals-only).- Physical backups + WAL archiving give PITR with RPO measured in seconds, and fast restores.
- pgBackRest adds parallelism, compression, encryption, incremental backups and retention policies. PostgreSQL 17 added built-in incremental base backups.
Test restores regularly and measure the time; monitor that WAL archiving keeps succeeding (pg_stat_archiver).
Key Points
- Base backups + WAL archive = PITR.
- pg_dump for logical, portable backups.
- pgBackRest/Barman/WAL-G.
- Test restores; monitor archiving.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?