Cheatsheet for Postgres backups. SQLAlchemy doesn’t ship backup tools; this is operational.

pg_dump (logical)

pg_dump -h db -U app -d myapp \
    --no-owner --no-acl \
    -Fc -f myapp.dump        # custom format (compressed, parallel restore)

Flags:

  • -Fc — custom format (recommended).
  • -Fd — directory format (parallel dump + restore).
  • -Fp — plain SQL (legacy; good for tiny dumps).
  • -Ft — tar format.
  • -j 8 — parallel dump (directory format only).
  • --no-owner --no-acl — strip role assignments (good for migrations).
  • --exclude-table-data='audit_log' — skip table data.
  • --schema-only, --data-only.

pg_restore

pg_restore -h db -U app -d myapp \
    --no-owner --no-acl --clean --if-exists \
    -j 8 myapp.dump

--clean --if-exists drops existing objects before recreating.

pg_basebackup (physical, replication-ready)

pg_basebackup -h primary -U replicator \
    -D /backup/$(date +%Y%m%d) \
    -Ft -X stream -z -P
  • -Ft — tar format.
  • -X stream — include WAL via streaming (consistent backup).
  • -z — gzip.
  • -P — progress.

WAL archiving (for PITR)

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'

Continuous WAL → object storage. Combined with base backup = PITR.

Restore to point-in-time

# Restore base backup
tar -xf base.tar.gz -C /var/lib/postgresql/data

# recovery.conf (PG < 12) or recovery_target_* in postgresql.conf (PG 12+)
echo "recovery_target_time = '2026-05-13 12:00:00'" >> postgresql.conf
echo "restore_command = 'aws s3 cp s3://my-wal-archive/%f %p'" >> postgresql.conf
touch recovery.signal

# Start; replays WAL up to target
systemctl start postgresql

# pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-type=s3
repo1-s3-bucket=my-pgbackrest

[myapp]
pg1-path=/var/lib/postgresql/data

pgbackrest --stanza=myapp backup --type=full
pgbackrest --stanza=myapp backup --type=incr
pgbackrest --stanza=myapp restore --target-time='2026-05-13 12:00:00'

Full/incr/diff backups; parallel; S3 native; PITR.

Barman (alternative)

barman backup myapp
barman recover --target-time '2026-05-13 12:00:00' myapp latest /var/lib/postgresql/data

Managed DB

  • RDS / Aurora: automated snapshots + transaction logs (PITR).
  • Cloud SQL: similar.
  • Neon: branching DB; instant restore from any point.
  • Supabase: managed PG with daily backups.

Test restore quarterly.

RPO and RTO

  • RPO (Recovery Point Objective) = max acceptable data loss.
  • RTO (Recovery Time Objective) = max acceptable downtime.

For most apps: RPO ~minutes, RTO ~hours.

Continuous WAL archiving → RPO ~ minutes. Cross-region replica → RTO ~ minutes.

Selective dump (per tenant / schema)

# Schema-only
pg_dump -h db -d myapp -n tenant_42 -Fc -f tenant_42.dump

# Specific tables
pg_dump -h db -d myapp -t users -t posts -Fc -f subset.dump

Logical backup of partitioned tables

pg_dump --table='events' --table='events_*' -Fc -f events.dump

Or: dump each partition separately for granular restore.

Backup verification

# Restore to a test DB
createdb myapp_test
pg_restore -d myapp_test myapp.dump

# Run sanity checks
psql myapp_test -c "SELECT count(*) FROM users"

Automate: nightly restore-and-verify.

Encryption

pg_dump ... -Fc | gpg --encrypt -r [email protected] > myapp.dump.gpg

Or use pgBackRest with repo1-cipher-type=aes-256-cbc.

Backup retention

Daily for 14 days.
Weekly for 12 weeks.
Monthly for 12 months.
Annual for 7 years (compliance).

Adjust per regulatory requirements.

Backup monitoring

# pgBackRest
pgbackrest --stanza=myapp info

Alert on:

  • Backups older than N hours.
  • Backup failures.
  • Disk space at backup destination.

Common mistakes

  • Backup with no restore test — finds out at incident time.
  • WAL archive on the same host as DB — useless after disk failure.
  • No off-region backup — region outage = total loss.
  • Forgetting role / extension dumps with --no-owner --no-acl.
  • pg_dump on huge DB — hours; use pgBackRest.

Read this next

If you want my pgBackRest + restore-test runbook, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .