Use SQL dumps for portability and single database restores, file system level or base backups for fast whole cluster restores, and continuous archiving when you need to recover to a chosen point in time. Many teams combine a base backup with archiving and keep periodic dumps as a second path.
The three approaches
The PostgreSQL manual names three: SQL dump, file system level backup, and continuous archiving with point in time recovery.
SQL dump (logical). A dump is a snapshot of one database as of when it started, and does not block most other work. It does not include roles or tablespaces, which are cluster wide, so the manual points to dumping those separately. Text dumps restore with psql, custom and directory formats with pg_restore. Roles that own objects must exist before restoring.
File system level backup (physical). Copies the files PostgreSQL uses. The manual says the server must be shut down to get a usable backup by default, or you can use a consistent snapshot, which the server treats like a crash and recovers from by replaying write ahead log. It works only for the whole cluster, with no partial restores.
Continuous archiving. A base backup plus archived write ahead log lets you recover to a point in time. It needs more setup and monitoring.
Comparison
| Question | SQL dump | File system or base backup | Continuous archiving |
|---|---|---|---|
| Restore one database | Yes | No | No, whole cluster |
| Cross version restore | Generally yes | No, tied to version | No |
| Speed on large data | Slower | Usually faster | Fast base plus replay time |
| Recovery point | Time of dump | Time of backup | Chosen point |
| Includes roles | Separate step | Yes | Yes |
The manual states dump output generally restores into newer PostgreSQL versions, while file level backups and archiving are tied to a specific server version.
How to choose
- Write your RPO and RTO for the database.
- If RPO is under your backup interval, you need archiving.
- If RTO is tight and the data is large, you need physical restores, since replaying a long dump takes time.
- If you must restore a single database, or move between versions, keep dumps too.
- Test each path by restoring into a clean environment and timing it.
Common mistakes
- Taking a file copy of a running server without a snapshot or the proper start and stop backup steps. The result may be unusable.
- Forgetting roles and tablespaces when relying on per database dumps.
- Snapshotting data and write ahead log volumes separately. The manual says snapshots must be simultaneous across file systems.
- Treating a replica as the backup. It copies deletes and corruption too.
Safeguard can scan the application dependencies and infrastructure definitions around your database. You can start with a free scan.
Quick check
Is pg_dump enough for a production database?
It can be, if your RPO allows restoring from the last dump and restore time is acceptable. Many teams add archiving for a tighter recovery point.
Can I restore a base backup on a newer major version?
Not directly. File level backups are tied to the server version, so use a dump or an upgrade tool for version changes.
Sources
- PostgreSQL docs: Backup and Restore
- PostgreSQL docs: SQL Dump
- PostgreSQL docs: File System Level Backup
Last reviewed: 19 October 2026.
