PostgreSQL pg_dump vs point-in-time recovery: choose a recovery goal
Compare PostgreSQL pg_dump and point-in-time recovery. Define database scope, WAL history, data-loss targets and restore checks.
In this guide
An export and a recovery timeline solve different problems
pg_dump creates a logical backup of a single PostgreSQL database. It does not by itself export cluster-wide objects such as roles and tablespaces. Choose the output and restore tools around the intended migration or recovery task.
Continuous archiving and point-in-time recovery combine a base backup with the required write-ahead log history. They address recovery along a timeline rather than treating one logical export as every possible restore point.
Write the business recovery target first
Imagine a fictional order service that can tolerate a short maintenance window but very little lost order history. A nightly file may exist while still leaving a gap the business cannot accept. Conversely, a team moving one database to a new environment may want a logical export without needing an entire cluster recovery exercise.
Record the maximum acceptable data gap, the expected restoration time and what must be included. These targets are requirements, not outcomes. A backup schedule cannot prove them until the team rehearses the relevant restore and measures the result.
Map the method to the required scope
| Need | Method to assess | Question to verify |
|---|---|---|
| Move one database logically | pg_dump and a compatible restore route | Are required objects, extensions and data included? |
| Retain cluster-wide objects | Separate global-object handling | How are roles and ownership recreated? |
| Recover to a time after a base backup | Base backup and required WAL history | Is the complete recovery chain available? |
| Recover a managed database | Provider-supported recovery service | Which restore points and destinations are actually offered? |
The table does not provide a ready-to-run backup command. Version compatibility, service permissions, extensions and storage design need review for the actual environment.
An ordinary filesystem copy is a separate technical question
PostgreSQL’s backup overview separates SQL dumps, filesystem-level backup and continuous archiving. Do not substitute an arbitrary copy of a running data directory for a documented, consistent backup procedure.
Name the owner of each required part: base backup, log archive, configuration, global objects and external application files. Record encryption and recovery access through the organisation’s approved process. Keep passwords out of runbooks and check that the restore operator can obtain the authorised access when needed.
Prove the restore in an isolated destination
Use an approved non-production destination and the documented method. Verify a representative set of records, relationships, ownership and application queries after restoration. Include a known event around the intended restore point so the team can explain what was retained and what was intentionally excluded.
Record the actual elapsed work, required access, missing dependencies and observed data gap. Retain that result with the runbook and repeat the relevant exercise after material changes. The PostgreSQL profile and managed versus self-hosted database guide help frame responsibility. This article is a planning guide, not a report of a production restore.
Frequently asked questions
Does pg_dump back up an entire PostgreSQL cluster?
pg_dump covers a single database. Global objects such as roles and tablespaces need appropriate separate handling, for example through the documented pg_dumpall route.
Does one pg_dump file provide arbitrary point-in-time recovery?
No. PostgreSQL’s continuous-archiving approach requires a base backup and the necessary WAL history for the target recovery point.
Does a successful backup job prove the database can be restored?
No. Rehearse the supported restore in an isolated destination, verify representative data and application behaviour, and record the observed recovery time and data gap.
Put the guide into practice
Explore database tools, then compare your shortlist.
Explore the category →