Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 4 · Lesson 23 · 5 min read

PostgreSQL Backups vs SQL Server Backups

On this page
  1. SQL Server backups mapped to PostgreSQL
  2. Logical backups: pg_dump
  3. Physical backups and point-in-time recovery
  4. What doesn't carry over
  5. Testing restores
  6. Check yourself

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 ServerPostgreSQLNotes
Full backupBase backup (pg_basebackup)Physical copy of the whole cluster, taken online
Differential backupIncremental base backup (PostgreSQL 17+)pg_basebackup --incremental, merged with pg_combinebackup
Transaction log backupsContinuous WAL archivingEvery WAL segment is copied off as soon as it's complete
RESTORE ... STOPATPoint-in-time recovery (recovery_target_time)Restore a base backup and replay WAL up to the target
Script/export one database or tablepg_dump and pg_restoreLogical, portable, restore just what you need
Backup software, maintenance planspgBackRest, WAL-G, Barman, or your cloud's backupsHandle 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:

Shell
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 skips

Restore into a new database, in parallel, or pick out one table:

Shell
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 inside

Don'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):

Config
# 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:

Shell
pg_basebackup -h db-primary -U replicator -D /backups/base_2026_10_04 -Fp -Xs -P

To recover to the moment before a mistake, such as 14:32 on 30 July:

  1. Restore the latest base backup from before that time into an empty data directory, on a separate server if possible.
  2. Set the recovery settings:
Config
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-07-30 14:31:59+00'
recovery_target_action = 'pause'
  1. Create an empty recovery.signal file 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 -Z options 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_archiver shows failed_count and 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.