PostgreSQL backups: logical dumps vs point-in-time recovery
Compare PostgreSQL logical dumps and WAL-based recovery by what they restore, what they depend on and what recovery tests must demonstrate.
A PostgreSQL logical dump recreates database objects and data from a consistent snapshot. Point-in-time recovery, or PITR, combines a suitable base backup with a continuous sequence of archived write-ahead log files, letting recovery stop at a chosen point within the recoverable range. They solve different recovery needs; one is not simply a newer name for the other.
This comparison uses PostgreSQL 18 documentation. It is a planning guide, not a restore procedure to execute on a live database. Managed services may expose different controls and retention limits.
Compare the recovery outcome first
| Question | Logical dump | Continuous archiving/PITR |
|---|---|---|
| What is the starting material? | A logical representation produced by a tool such as pg_dump | A suitable base backup and required archived WAL |
| What state can it recreate? | The consistent snapshot represented by the dump | A supported target after the base backup, with the required log sequence |
| What is the recovery scope? | pg_dump covers one database; supported formats offer restoration choices | The documented physical recovery method restores a cluster, not selected rows or one table |
| Is it a cross-version migration shortcut? | Dumps can generally be loaded into newer PostgreSQL versions | Physical backups/WAL are highly version-specific |
| What must be checked? | Contents, privileges, dependencies and actual restore result | Base backup, log continuity, target, configuration and actual recovery result |
Do not select a method solely because a dashboard labels it "backup enabled." First write down the incident you need to recover from.
What a logical dump does and leaves out
The SQL dump manual explains that pg_dump produces an internally consistent snapshot from when it began. It does not generally stop ordinary database work, although operations requiring conflicting exclusive locks are an exception.
A single-database dump does not include cluster-wide roles or tablespaces. PostgreSQL provides pg_dumpall for broader coverage, including globals, but its per-database snapshots are not synchronized with one another. Inventory the actual recovery dependencies rather than assuming one file includes everything.
Restore behaviour also matters. A text restore through psql can continue after errors by default and leave a partial result. The manual describes error-stop and transaction options, with tradeoffs. Merely seeing the process finish is not proof of a complete usable restore.
For a real plan, check the destination version, required extensions and roles, object ownership, permissions and application expectations. Do not use an untested command copied from a general article as your production recovery runbook.
PITR depends on more than keeping some log files
The continuous-archiving manual requires a continuous archived WAL sequence reaching back far enough for the chosen base backup. A gap can make the desired target unrecoverable.
A logical pg_dump file is not the physical base backup needed for WAL replay. Keeping a dump and a folder of arbitrary WAL files does not automatically create a PITR system.
Use the documented base-backup method or the managed provider's supported process. Do not interpret the underlying concept as permission to copy a running data directory casually and assume recovery will work.
The archive must also be protected. PostgreSQL notes that WAL contains effectively all the database's information. Backup storage needs appropriate access controls, and retention must preserve the complete materials required for the intended recovery range.
Monitoring is part of the design
If WAL archiving falls behind, recent data can remain outside the archive, and retained local WAL can consume disk space. The manual recommends monitoring archiving and reporting conditions that need operator attention.
Ask who receives an alert, what they investigate and how the team confirms recovery coverage afterward. "The scheduled job ran" is weaker evidence than knowing a valid base backup and required WAL are available and recoverable.
Configuration files are another boundary. WAL replay does not restore edits to files such as postgresql.conf or authentication configuration. Keep the necessary configuration recovery plan separate.
Plan a recovery exercise, not only a backup job
Our suggested exercise record includes:
- A hypothetical incident and target state, including the intended timestamp and timezone when relevant.
- The exact backup materials and dependencies used.
- A separate recovery environment that cannot overwrite production data.
- The observed recovery result, including errors and missing objects.
- Application-level checks on representative records and permissions.
- Measured time until the service is genuinely usable, not merely until files are copied.
- Gaps and the person responsible for correcting them.
Choose backup frequency and retention around acceptable data loss and recovery time for the actual service. This article supplies no universal schedule, storage estimate or guaranteed recovery duration.
Which method should you choose?
A team needing portable logical copies has a different problem from one needing recovery to a recent point before an accidental change. Some services use more than one method for different purposes.
Document the desired recovery outcome, choose supported tooling, and prove the result in a safe exercise. A backup strategy is credible when it can restore the service you need, not merely when it creates files.
By Omnistu. Reviewed against PostgreSQL 18 documentation on 16 November 2026. No backup or restore was executed; no vendor reliability or recovery guarantee is claimed.