SQLAlchemy + Postgres Cheatsheet 18 — Backup, PITR, and Restore

Cheatsheet for backups. Test your restores quarterly.

pg_dump (logical)

# Single database
pg_dump -h prod -U app -d mydb -Fc -f mydb.dump

# Plain SQL
pg_dump -h prod -d mydb > mydb.sql

# All databases (run as superuser)
pg_dumpall -h prod > all.sql

Format flags:

• -Fc (custom): compressed, restore selectively.
• -Fp (plain): readable SQL.
• -Fd (directory): parallel.

Parallel dump

pg_dump -h prod -d mydb -Fd -j 8 -f /backups/mydb_dir

8 workers; faster for huge DBs.

Restore

# From -Fc dump
pg_restore -h target -U app -d mydb -j 4 mydb.dump

# From plain SQL
psql -h target -U app -d mydb < mydb.sql

Schema-only / data-only

pg_dump -s mydb > schema.sql            # schema only
pg_dump -a mydb > data.sql              # data only
pg_dump -t users mydb > users.sql       # specific table

pgBackRest (recommended for production)

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-archive=14

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

Full backup:

pgbackrest --stanza=mydb backup

Incremental:

pgbackrest --stanza=mydb backup --type=incr

Continuous WAL archiving (set in postgresql.conf):

archive_mode = on
archive_command = 'pgbackrest --stanza=mydb archive-push %p'

Point-in-Time Recovery (PITR)

pgbackrest --stanza=mydb \
    --type=time \
    --target="2026-05-13 14:30:00" \
    restore

Restores to a specific moment. Requires WAL archive + base backup.

Barman (alternative)

Similar capabilities; different UX. Pick based on team preference.

Cloud-native

For managed: enable PITR; document RTO/RPO.

Logical replication for migration

For minimal-downtime version upgrades:

-- Publisher
CREATE PUBLICATION mypub FOR ALL TABLES;

-- Subscriber (new version)
CREATE SUBSCRIPTION mysub
CONNECTION 'host=oldprimary dbname=mydb'
PUBLICATION mypub;

Catches up; cut over with brief downtime.

Verify backup

# Restore to test instance
pg_restore -h test -U app -d test_mydb mydb.dump

# Run smoke tests against test_mydb

Test quarterly. Untested backups are not backups.

RTO and RPO targets

• RTO (Recovery Time Objective): how long to recover. For most apps: < 4h.
• RPO (Recovery Point Objective): how much data loss is acceptable. For most: < 5 min (continuous WAL).

Match backup strategy to these targets.

Disaster scenarios

1. Accidental DROP TABLE — PITR to before the drop.
2. Storage corruption — restore from backup; replay WAL.
3. Region failure — promote DR replica in another region.
4. Logical corruption (bad migration) — PITR.

Plan for each.

What to back up

• Database (pg_dump or physical backup).
• WAL archive (for PITR).
• Roles / permissions (pg_dumpall -r).
• Tablespaces config.
• Configuration files (postgresql.conf, pg_hba.conf).
• Application secrets / keys (separate; encrypted).

Encryption

pg_dump -h prod -d mydb -Fc | gpg --encrypt --recipient [email protected] > mydb.dump.gpg

Or use pgBackRest’s encryption + S3 SSE.

Retention

Daily backups: 7 days.
Weekly: 4 weeks.
Monthly: 12 months.
Yearly: 7 years (compliance-dependent).

Or your own.

Common mistakes

• No PITR — limited recovery options.
• No restore drill — discover bugs during real incident.
• Backups on same host/region as primary.
• Plaintext backups in S3 without encryption.
• Forgetting roles/permissions — restore succeeds but app can’t connect.

Read this next

• Cheatsheet 19 — Operational Patterns

If you want my pgBackRest config + restore drill script, 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 .