---
title: "PostgreSQL Backup and Recovery"
description: "Logical and physical backup strategies with pg_dump, pg_basebackup, WAL archiving for PITR, pgBackRest, automated cron schedules, and restore testing procedures."
url: https://agent-zone.ai/knowledge/databases/postgres-backup-and-recovery/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["postgresql","backup","recovery","pitr","wal","pgbackrest","pg_dump"]
skills: ["postgres-backup","postgres-recovery","disaster-recovery"]
tools: ["postgresql","pg_dump","pg_dumpall","pg_basebackup","pgbackrest","cron"]
levels: ["intermediate"]
word_count: 524
formats:
  json: https://agent-zone.ai/knowledge/databases/postgres-backup-and-recovery/index.json
  html: https://agent-zone.ai/knowledge/databases/postgres-backup-and-recovery/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=PostgreSQL+Backup+and+Recovery
---


# PostgreSQL Backup and Recovery

A backup you have never tested restoring is not a backup. This covers the main backup tools, when to use each, point-in-time recovery, and automation.

## Logical Backups: pg_dump and pg_dumpall

pg_dump exports a single database as SQL or a compressed binary format. It takes a consistent snapshot without blocking writes.

```bash
# Custom format (compressed, supports parallel restore)
pg_dump -U postgres -Fc -d myapp -f myapp.dump

# Directory format (parallel dump)
pg_dump -U postgres -Fd -j 4 -d myapp -f myapp_dir/
```

pg_dumpall exports every database plus cluster-wide objects. In practice, dump roles separately and per-database for flexibility:

```bash
pg_dumpall -U postgres --roles-only > roles.sql
pg_dump -U postgres -Fc -d myapp -f myapp.dump
```

Restoring:

```bash
# From custom format with parallel workers
createdb -U postgres myapp_restored
pg_restore -U postgres -d myapp_restored -j 4 myapp.dump
```

Logical backups are ideal for databases under ~100 GB and for cross-version migrations. They are too slow for multi-terabyte databases.

## Physical Backups: pg_basebackup

pg_basebackup copies the entire data directory at the filesystem level. Much faster for large databases and required for streaming replication setup.

```bash
pg_basebackup -U replicator -h primary-host -D /backup/base \
  --checkpoint=fast --wal-method=stream -P
```

Prerequisites on the primary: `wal_level = replica`, `max_wal_senders = 5`, a replication role, and a `pg_hba.conf` entry for `replication`.

## Point-in-Time Recovery (PITR)

PITR restores to any specific moment by combining a base backup with continuous WAL archiving.

Configure the primary to archive WAL:

```
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
```

To restore to a point in time: stop PostgreSQL, replace the data directory with the base backup, create `recovery.signal`, and configure recovery:

```
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-02-22 14:30:00 UTC'
recovery_target_action = 'promote'
```

Start PostgreSQL. It replays WAL up to the target time, then promotes to read-write.

## pgBackRest

pgBackRest is the standard tool for production backup management -- incremental backups, parallel compression, rotation, and remote storage.

Configure `/etc/pgbackrest/pgbackrest.conf`:

```ini
[mydb]
pg1-path=/var/lib/postgresql/16/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
compress-type=zst
```

Set PostgreSQL to archive through pgBackRest:

```
archive_command = 'pgbackrest --stanza=mydb archive-push %p'
```

Initialize and take backups:

```bash
pgbackrest --stanza=mydb stanza-create
pgbackrest --stanza=mydb --type=full backup    # full
pgbackrest --stanza=mydb --type=diff backup    # differential
pgbackrest --stanza=mydb --type=incr backup    # incremental
```

Restore (including PITR):

```bash
pgbackrest --stanza=mydb --delta restore
pgbackrest --stanza=mydb --delta --type=time --target="2026-02-22 14:30:00" restore
```

## Automated Backup Schedule

Full weekly, differential daily, WAL archiving continuous:

```cron
0 2 * * 0 pgbackrest --stanza=mydb --type=full backup >> /var/log/pgbackrest.log 2>&1
0 2 * * 1-6 pgbackrest --stanza=mydb --type=diff backup >> /var/log/pgbackrest.log 2>&1
```

For pg_dump setups with 7-day retention:

```cron
0 3 * * * pg_dump -U postgres -Fc -d myapp -f /backup/myapp_$(date +\%Y\%m\%d).dump && find /backup -name "myapp_*.dump" -mtime +7 -delete
```

## Testing Your Restores

Restore into a temporary instance at least monthly:

```bash
docker run -d --name pg-test -v /backup:/backup -e POSTGRES_PASSWORD=test postgres:16
docker exec pg-test pg_restore -U postgres -d postgres --create /backup/myapp.dump
docker exec pg-test psql -U postgres -d myapp \
  -c "SELECT COUNT(*) FROM users; SELECT MAX(created_at) FROM orders;"
docker rm -f pg-test
```

Script this on a schedule. If row counts or timestamps are wrong, alert immediately.

## Choosing Your Strategy

| Database Size | Tool | PITR Capable |
|---|---|---|
| Under 10 GB | pg_dump | No |
| 10-500 GB | pgBackRest | Yes |
| 500 GB+ | pgBackRest with S3 | Yes |
| Cross-version migration | pg_dump | No |

For production, use pgBackRest with continuous WAL archiving. Logical backups supplement for portability.