Backup and Restore

Two kinds of backup

Logical backups

pg_dump -Fc -d pandadev -f pandadev.dump        # custom format: compressed, flexible
pg_dumpall --globals-only > globals.sql         # roles and tablespaces (not in pg_dump!)

Restore:

createdb pandadev_restore
pg_restore -d pandadev_restore --no-owner -j 4 pandadev.dump
pg_restore -d pandadev -t users pandadev.dump    # just one table

pg_dump takes a consistent snapshot without blocking the app, so it is safe to run during the day. Remember --globals-only: without it, a restore onto a new server is missing every role.

Point-in-time recovery (PITR)

Dumps restore to the moment the dump was taken; you lose everything after. PITR combines a base backup with the archived WAL to restore to any second, for example just before someone ran DELETE FROM orders without a WHERE.

With pgBackRest (recommended):

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=company-pg-backups
repo1-s3-region=eu-central-1
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc

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

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main --type=time \
  "--target=2026-10-20 14:03:00+00" restore

Managed services (RDS, Render) provide PITR as a setting. Check how many days your plan keeps.

RPO and RTO

• RPO (recovery point objective): how much data you can afford to lose. Nightly dumps mean up to 24 hours; WAL archiving means seconds.
• RTO (recovery time objective): how long you can be down. Measure it by doing a real restore.

Agree these numbers with the business owner. They decide the backup design, not the other way round.

Test every backup

Automate a weekly job that restores the latest backup to a scratch server, runs a few sanity queries (row counts, the latest created_at) and reports the result. Untested backups fail exactly when you need them.

Check yourself

• What does pg_dump not include, and how do you back it up?
• What does PITR let you do that dumps cannot?
• What are RPO and RTO?