backup-restore

Run docker compose exec -T db pg_dump -Fc -U $POSTGRES_USER $POSTGRES_DB and redirect output to a dated .dump file. The custom format (-Fc) enables selective and parallel restores with pg_restore later.

The 3-2-1 rule means keeping 3 copies of data on 2 different media with 1 copy offsite. A backup stored on the same VPS dies with that VPS, so push dumps to S3, B2, or rsync.net using a tool like rclone.

Run a restore drill: spin up a throwaway postgres Docker container, copy the dump in, run pg_restore, then verify row counts and the most recent timestamp against production. Schedule this quarterly and record the restore duration as your real RTO.

RPO is how much data you can afford to lose, which drives backup frequency and WAL archiving. RTO is how long you can be down while restoring, which determines whether you use pg_restore or faster replica promotion.

Backups fail silently through cron misconfiguration, full disks, or credential expiry. Alert on job failure, on absence of a new backup within 25 hours, and on size anomalies like 0-byte or sharply shrinking dumps.

Use physical backups with pgBackRest or replica snapshots when logical dumps take hours on large databases. For low RPO requirements, add WAL archiving to enable point-in-time recovery regardless of dump method.