How do you back up and recover PostgreSQL?

Question

Accepted Answer

PostgreSQL offers several **backup** methods — logical backups (`pg_dump`) and physical backups (base backup + WAL for **point-in-time recovery**). A solid backup-and-recovery strategy is essential for protecting against data loss, and crucially, **backups must be tested**.

## Logical backups — pg_dump

```bash
# pg_dump — dumps a database as SQL (or a custom archive) — portable, flexible
pg_dump -U user -d mydb -F c -f backup.dump      # custom format (compressed, flexible restore)
pg_dump -U user -d mydb > backup.sql              # plain SQL
pg_dumpall > all.sql                              # ALL databases + roles

# restore
pg_restore -U user -d mydb backup.dump            # from custom format
psql -U user -d mydb -f backup.sql                # from plain SQL
```

`pg_dump` creates a logical backup (SQL or archive) of a database — portable (restorable to different versions/servers), good for smaller databases, migrations, and selective backups. But it's a point-in-time snapshot (no continuous recovery).

## Physical backups + Point-in-Time Recovery (PITR)

```text
For larger/critical databases: a BASE BACKUP (a copy of the data files) + continuous
archiving of the WAL (write-ahead log) → enables POINT-IN-TIME RECOVERY:
  ✓ Restore the base backup, then REPLAY WAL up to ANY point in time
  ✓ Recover to JUST BEFORE a disaster (e.g. an accidental DROP TABLE at 2:47pm)
→ Tools: pg_basebackup, WAL archiving, and tools like pgBackRest / Barman automate this.
```

**PITR** is powerful: by archiving WAL continuously, you can recover to any moment — not just the last backup — which is essential for minimizing data loss from disasters.

## Backup strategy essentials

```text
✓ Regular, AUTOMATED backups (scheduled, not manual/forgotten)
✓ Store backups OFF the database server (and offsite / cloud) — survive hardware failure
✓ Retention policy (keep daily/weekly/monthly as needed)
✓ Combine: periodic base backups + continuous WAL for PITR (critical data)
✓ Backups from a REPLICA to avoid loading the primary
```

## The most important rule: TEST your backups

```text
⚠️ A backup you've never restored is NOT a reliable backup.
UNTESTED backups fail when you need them most (corrupt, incomplete, wrong process).
→ Regularly TEST restores to verify they actually work and you know the procedure.
```

## Why it matters

Backup and recovery is **critical** knowledge for operating PostgreSQL responsibly — protecting against data loss (from hardware failure, corruption, accidental deletion, disasters) is one of the most important responsibilities for any production database, so understanding backup strategies is essential senior-level knowledge.

Understanding the methods is necessary: **`pg_dump`** (logical backups — portable SQL/archive dumps, good for smaller databases, migrations, and selective/cross-version backups, but point-in-time snapshots only) and **physical backups with Point-in-Time Recovery (PITR)** (base backup + continuous WAL archiving, enabling recovery to *any* moment — which is powerful for minimizing data loss, like recovering to just before an accidental `DROP TABLE`, essential for critical data).

Knowing the **strategy essentials** (regular automated backups, storing them off-server and offsite, retention policies, combining base backups with WAL for PITR on critical data, backing up from replicas) is important operational knowledge.

Most critically, understanding that **backups must be tested** — an untested backup is unreliable and often fails exactly when needed (corruption, incomplete data, unfamiliar restore process) — is a vital, often-neglected principle: regularly testing restores is what makes a backup strategy actually trustworthy.

Since data loss can be catastrophic (and recovery is only possible with working backups), and since a proper strategy (appropriate backup methods, PITR for minimal loss, off-site storage, and crucially *tested* restores) is what protects an organization's data, understanding PostgreSQL backup and recovery is critical senior-level knowledge for responsible database operations — a high-stakes responsibility where the difference between a tested strategy and an untested one can be the difference between recovering from a disaster and losing data permanently, making it an essential, frequently-relevant topic for anyone operating production databases.