Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 4 · Lesson 26 · 6 min read

PostgreSQL Backup and Restore: pg_dump and PITR

On this page
  1. Logical vs physical backups
  2. Step 1: Take a logical backup with pg_dump
  3. Step 2: Restore it with pg_restore
  4. Step 3: Set up base backups and WAL archiving
  5. Step 4: Point-in-time recovery (PITR) to 14:34
  6. Step 5: Test your backups, with RPO and RTO
  7. Check yourself

Logical vs physical backups

Someone runs DELETE FROM orders without a WHERE clause at 14:35. By the end of this lesson you'll know how to get those orders back, and how to make sure your backups would really work when that day comes.

There are two kinds of backup, and production systems usually need both:

Logical: `pg_dump`Physical: `pg_basebackup` + WAL archive
What it copiesSQL definitions and dataThe data files themselves
Restore one tableYesNo, whole cluster only
Across major versionsYes, that's how many upgrades workNo, same major version only
Recover to any momentNo, only to when the dump ranYes, with point-in-time recovery
Speed on very large databasesSlow to restore, since indexes are rebuiltFast

Remember one more thing: replication (Day 27) isn't a backup. A replica applies your accidental DELETE a fraction of a second later.

Step 1: Take a logical backup with pg_dump

pg_dump backs up one database while it's in use, as a consistent snapshot. Use the custom format (-Fc), which is compressed and lets you restore selectively:

Shell
pg_dump -d appdb -Fc -f appdb.dump

For big databases, the directory format (-Fd) can dump several tables in parallel:

Shell
pg_dump -d appdb -Fd -j 4 -f appdb_dir

A plain SQL dump (-Fp) is human-readable and restores with psql, but it can't be restored selectively or in parallel. Keep it for small databases and for reading.

pg_dump doesn't include roles or tablespaces, because those belong to the whole cluster, not one database. Back them up separately:

Shell
pg_dumpall --globals-only -f globals.sql

Step 2: Restore it with pg_restore

Restore into a new, empty database. Use -j to run in parallel, which helps most with index builds:

Code
createdb appdb_restored
pg_restore -d appdb_restored -j 4 appdb.dump

The custom format pays off when you only need part of the backup. List what's inside, then restore a single table's data:

Shell
pg_restore -l appdb.dump | head
pg_restore -d appdb_restored -t orders --data-only appdb.dump

That's often the fastest way to recover from "someone deleted the orders": restore yesterday's dump into a scratch database and copy back the missing rows. The catch is that you lose everything written since the dump ran. To do better, you need the next step.

Step 3: Set up base backups and WAL archiving

Every change is written to the WAL before it reaches the table files (Day 2). If you keep every WAL segment, you can take a copy of the data files from last night and replay the WAL forward to any moment since.

First, tell PostgreSQL to archive each finished WAL segment. This needs a restart:

Config
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'

%p is the segment's path and %f its file name. The test ! -f guard refuses to overwrite a segment that already exists. In production, archive to separate storage such as S3 using a dedicated tool. pgBackRest and WAL-G handle compression, encryption, retention and parallelism for you.

Then take a base backup, a consistent copy of the whole data directory, taken while the server runs:

Shell
pg_basebackup -h db-primary -U replicator -D /backups/base_2026_10_08 -Fp -Xs -P -c fast

-Xs streams the WAL generated during the backup, so the copy is consistent by itself. PostgreSQL 17 added incremental base backups (pg_basebackup --incremental, combined later with pg_combinebackup), which copy only the blocks that changed since the last backup.

Step 4: Point-in-time recovery (PITR) to 14:34

This is point-in-time recovery (PITR). The orders were deleted at 14:35, so you recover to 14:34:59, on a separate server so production keeps running while you check the result:

  1. Copy the latest base backup taken before 14:35 into an empty data directory.
  2. Tell PostgreSQL where to find archived WAL and where to stop:
Config
# postgresql.conf on the recovery server
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-10-08 14:34:59+00'
recovery_target_action = 'pause'
  1. Create an empty file called recovery.signal in the data directory, then start the server:
Shell
touch /var/lib/postgresql/18/recovery/recovery.signal
pg_ctl -D /var/lib/postgresql/18/recovery start

PostgreSQL replays the WAL up to 14:34:59 and pauses in read-only mode. Check that the orders are there. If you need a different point, change the target and restart. When you're happy, run SELECT pg_wal_replay_resume(); to finish recovery and open the server for writes, then copy the rows back to production or switch over to this server.

Note the file name. recovery.signal means "recover to the target, then become a normal server". standby.signal means "keep following WAL forever as a replica" (Day 27), which isn't what you want here.

Before a risky change such as a large migration, you can save yourself guessing a timestamp by creating a named restore point:

Code
SELECT pg_create_restore_point('before_migration_2026_10');

Later, recovery_target_name = 'before_migration_2026_10' recovers to exactly that point.

Step 5: Test your backups, with RPO and RTO

A backup you've never restored isn't a backup yet. Backup jobs fail quietly: a disk fills, a credential expires, and nobody notices until the day it matters. Automate the checks:

  • Alert on archiving failures at once. pg_stat_archiver shows failed_count and the last failure time. A gap in the WAL archive means you can't recover past it.
  • Restore last night's dump into a scratch database on a schedule, and check row counts on key tables.
  • Run a full PITR drill every quarter, on separate infrastructure, and time it.
  • Alert on backup age. Page someone if the newest good backup is older than you can afford to lose.

Two numbers decide how far you need to go. RPO (recovery point objective) is how much recent data you can afford to lose. RTO (recovery time objective) is how long you can afford to be down. Nightly dumps give an RPO of up to 24 hours. WAL archiving brings it down to minutes or less. A replica (Day 27) cuts RTO for hardware failures but does nothing for mistakes, which is why serious setups combine all three.

Check yourself

Restore last night's dump into a scratch database and copy invoices back with pg_restore -t invoices. Every invoice change made between the dump and 09:12 is lost, because a dump only captures the moment it ran.

There's a gap in the WAL archive, so recovery can't go past it. Monitor pg_stat_archiver and alert on archiving failures so you find gaps long before you need them.

The replica copies every change, including the mistakes, almost at once. Backups with PITR are what let you go back to the moment before a bad DELETE or DROP.

RPO, recovery point objective, is how much data loss is acceptable, such as five minutes. RTO, recovery time objective, is how long recovery may take. Nightly dumps give an RPO of up to a day; WAL archiving with PITR can bring it to seconds. Tested restore times tell you your real RTO.

Backups fail silently: missing WAL segments, a wrong archive path, corrupted files or a restore procedure nobody remembers. Only a regular, timed restore into a separate environment proves the backup works and measures how long recovery really takes.