Previously in Part 6, we dug into indexes, MVCC, and why VACUUM is your new best friend. Now we get to the question every responsible DBA asks of a new database before trusting it with anything real: how do I back this thing up — and how do I know I can restore it?
If that was the first thing on your mind when you started this series, good. That instinct is exactly what makes you a DBA and not just someone who runs queries. The good news: PostgreSQL takes backup and recovery just as seriously as SQL Server does. The tools have different names and a slightly different shape, but every concept you rely on — full backups, log backups, point-in-time restore — has a direct home here.
The mapping table
| SQL Server | PostgreSQL | Notes |
|---|---|---|
| Full backup (.bak) | pg_basebackup (physical) or pg_dump (logical) |
Two tools, two philosophies — more on this below. |
| Transaction log backup | WAL archiving (archive_command / pg_receivewal) |
The write-ahead log (WAL) is PostgreSQL's transaction log. |
Point-in-time restore (STOPAT) |
PITR: base backup + WAL replay to a target time | Same idea as a full + log restore chain. |
| Differential backup | No direct equivalent | Incremental backups arrived in recent versions via pg_basebackup --incremental. |
| BACPAC / Generate Scripts | pg_dump (plain SQL format) |
A dump can be a readable .sql file — schema, data, or both. |
Two philosophies: logical and physical
The one mental shift to make is that PostgreSQL splits backups into two distinct approaches, where SQL Server mostly gives you one.
Logical: pg_dump and pg_restore
A logical backup is a set of SQL statements (or a compressed archive of them)
that recreates your database: CREATE TABLE, COPY the data,
CREATE INDEX, and so on. Think of it as Generate Scripts done properly — it is
portable across PostgreSQL versions, across operating systems, even across architectures.
The workhorse pair looks like this:
# Back up one database in the custom archive format
pg_dump -Fc -h localhost -U postgres -d shopdb -f shopdb.dump
# Restore it, using 4 parallel workers
pg_restore -j 4 -h localhost -U postgres -d shopdb_restored shopdb.dump
The -Fc flag selects the custom format: compressed, and it lets
pg_restore pick and choose — a single table, schema only, data only — and restore
with parallel workers via -j. For small-to-medium databases, and for migrations
(moving from an old major version to PostgreSQL 18, say), logical dumps are perfect.
Physical: pg_basebackup and WAL archiving
A physical backup is a byte-level copy of the whole cluster's data directory,
taken safely while the server runs. This is the real production recipe, and it maps directly
onto what you already do: pg_basebackup is your full backup, and continuously
archiving WAL segments is your transaction log backup schedule. Set
archive_command (or run pg_receivewal to stream them), and every
change is captured:
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backups/wal/%f'
# Take the base backup
pg_basebackup -D /backups/base -Fp -Xs -P
Point-in-time recovery is then exactly the full-plus-log restore chain you know: restore the
base backup, point the server at the WAL archive with a restore_command, and set
recovery_target_time = '2026-08-06 09:29:00' — the moment just before someone ran
that DELETE without a WHERE clause. PostgreSQL replays WAL up to that
instant and stops. Same religion as STOPAT, different hymn book.
A practical everyday setup
For a small site or an internal app, you do not need the full physical machinery. A nightly compressed dump, copied off the server, covers you honestly. One crontab line:
# Every night at 02:00 — note cron requires % to be escaped as \%
0 2 * * * pg_dump -Fc -d shopdb -f /backups/shopdb-$(date +\%F).dump
Pair it with a job that syncs /backups to another machine or object storage, and
keep a couple of weeks of history. When the database grows or the business starts asking about
recovery point objectives measured in minutes, graduate to base backups plus WAL archiving —
or a tool like pgBackRest or Barman that manages that recipe for you.
Tip: A backup you have never restored is a hope, not a backup. Schedule a
recovery drill: pg_restore last night's dump into a scratch database and run a
few row-count checks. It is the same religion as RESTORE VERIFYONLY, but
stronger — you are proving the whole path, not just the file's checksums.
If you are on a managed service — Amazon RDS, Azure Database for PostgreSQL Flexible Server — the platform runs base backups and WAL archiving for you and hands you a restore-to-any-minute slider. Lovely. Use it. But now you know exactly what is underneath that slider, which means you can sanity-check the retention settings and know what questions to ask when it matters.
Gotcha: roles and other globals live at the cluster level, as we
saw in Part 2 —
so a pg_dump of one database does not include them. Restore
that dump onto a fresh server and every GRANT fails because the logins are not
there. This is the classic migration surprise. Capture them with
pg_dumpall --globals-only and keep that file next to your dumps.
What's next
Your safety net is in place: dumps for portability, base backups plus WAL for production, and a restore drill to keep everyone honest. In Part 8 — the finale — we connect .NET and EF Core to PostgreSQL, and put together the actual migration plan for moving a real SQL Server application across.