PostgreSQL Backups vs SQL Server Backups
On this page
SQL Server backups mapped to PostgreSQL
A typical SQL Server backup plan: a full backup every Sunday, differentials every night, transaction log backups every 15 minutes, all copied off the server. You can restore to any point in time, and you test restores regularly.
PostgreSQL can give you the same guarantees. The pieces have different names, and they come as two separate toolsets:
| SQL Server | PostgreSQL | Notes |
|---|---|---|
| Full backup | Base backup (pg_basebackup) | Physical copy of the whole cluster, taken online |
| Differential backup | Incremental base backup (PostgreSQL 17+) | pg_basebackup --incremental, merged with pg_combinebackup |
| Transaction log backups | Continuous WAL archiving | Every WAL segment is copied off as soon as it's complete |
| RESTORE ... STOPAT | Point-in-time recovery (recovery_target_time) | Restore a base backup and replay WAL up to the target |
| Script/export one database or table | pg_dump and pg_restore | Logical, portable, restore just what you need |
| Backup software, maintenance plans | pgBackRest, WAL-G, Barman, or your cloud's backups | Handle scheduling, retention, compression and encryption |
The biggest difference is scope. A SQL Server full backup covers one database. A PostgreSQL base backup covers the whole cluster, every database in it, and restores it as a whole. To get a single database or table back, you use the logical tools.
Most teams don't script this by hand. They use pgBackRest or WAL-G, or the automated backups of a managed service such as Amazon RDS or Azure Database for PostgreSQL, which do all of this, including point-in-time restore, for you. Understand the pieces anyway, because you'll configure and test them.
Logical backups: pg_dump
pg_dump exports one database while it's in use, as a consistent snapshot. It's the closest thing to "script the database", plus the data:
pg_dump -d salesdb -Fc -f salesdb.dump # custom format: compressed, selective restore
pg_dump -d salesdb -Fd -j 4 -f salesdb_dir # directory format: parallel dump
pg_dumpall --globals-only -f globals.sql # roles and tablespaces, which pg_dump skipsRestore into a new database, in parallel, or pick out one table:
createdb salesdb_restored
pg_restore -d salesdb_restored -j 4 salesdb.dump
pg_restore -d salesdb_restored -t orders --data-only salesdb.dump
pg_restore --list salesdb.dump # see what's insideDon't forget --globals-only. A restored database whose application roles don't exist will fail on its GRANT statements, and nobody can log in.
Logical dumps are also how you move between major versions and how you restore a single table after a mistake. They aren't enough on their own for big, busy databases, though. A dump only captures the moment it ran, and restoring a large one takes a long time because every index is rebuilt.
Physical backups and point-in-time recovery
This is the equivalent of full plus log backups. First, archive every WAL segment (restart required):
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'The test ! -f part refuses to overwrite an existing file. In production, archive_command usually calls pgBackRest or WAL-G, which send WAL to object storage.
Then take base backups on a schedule:
pg_basebackup -h db-primary -U replicator -D /backups/base_2026_10_04 -Fp -Xs -PTo recover to the moment before a mistake, such as 14:32 on 30 July:
- Restore the latest base backup from before that time into an empty data directory, on a separate server if possible.
- Set the recovery settings:
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-07-30 14:31:59+00'
recovery_target_action = 'pause'- Create an empty
recovery.signalfile in the data directory and start the server.
PostgreSQL replays WAL to 14:31:59 and pauses so you can check the data. SELECT pg_wal_replay_resume(); then finishes recovery and opens the server for writes. This is RESTORE LOG ... STOPAT, with a pause button.
What doesn't carry over
- No per-database physical restore. You can't restore one database from a base backup. Restore the cluster somewhere else and pg_dump what you need from it.
- No native backup compression setting for WAL. Compression comes from your tools, and from
-Zoptions on pg_dump and pg_basebackup. - No GUI restore wizard. DataGrip doesn't wrap pg_dump or pg_restore, so use its built-in Terminal window or your shell.
Testing restores
The habits are the same: monitor every job, and prove you can restore.
- Alert on archiving failures at once.
pg_stat_archivershowsfailed_countand the last failure. A gap in the WAL archive means you can't recover past it. - Restore last night's backup into a scratch server on a schedule, then check row counts and run a few application queries.
- Run a full point-in-time recovery drill every quarter, and time it. That time is your real recovery time objective.
Check yourself
Restore the latest base backup, optionally combined with incrementals, onto a separate server, replay archived WAL to 10:15 with recovery_target_time, then pg_dump the database you need and restore it where it belongs.
Roles are cluster-wide objects, so pg_dump of one database doesn't include them, and grants and ownership in the dump refer to roles that don't exist yet. Run pg_dumpall --globals-only alongside every backup, and restore it on the new server before restoring the database dumps.
You can't do point-in-time recovery past the start of the gap using backups taken before it. Take a new base backup now, and alert on archiver failures so gaps are found immediately.
PostgreSQL 17 added incremental base backups with pg_basebackup --incremental, combined with pg_combinebackup at restore time. Before that, tools like pgBackRest and Barman provided incremental and differential backups. Continuous WAL archiving covers the role of log backups.
At least as far back as your oldest base backup you might restore from, plus the window you want for point-in-time recovery. WAL from before your oldest usable base backup can't be replayed, so retention policies must keep base backups and WAL consistent with each other.