A backup you haven’t restored is a hypothesis, not a backup. That sentence is a cliché precisely because it keeps being true: teams running self-hosted Postgres routinely discover their backup strategy was broken — a silently failing cron job, a dump that’s been writing zero-byte files for months, a WAL archive with a gap nobody noticed — at the exact moment they need it, which is the worst possible time to learn anything. The fix isn’t more backups, it’s a strategy with the right layers and a drill that actually proves recovery works before an incident forces the question.

This covers a realistic self-hosted setup: Postgres in Docker Compose, no managed backup service, and a need for both quick logical restores and true point-in-time recovery.

Logical vs Physical Backups

These solve different problems and a mature setup needs both, not one or the other.

Logical backups (pg_dump/pg_dumpall) export data as SQL or a custom-format archive. They’re portable across Postgres versions and platforms, easy to inspect, and good for “give me last night’s data in a new environment.” They’re slow to restore for large databases (rebuilding indexes and re-inserting rows takes real time) and they only capture a single point in time — anything between dumps is gone if that’s your only backup.

# Logical dump in custom format — supports parallel restore and selective table restore
docker exec finance-db pg_dump -U finance -Fc finance > /backups/finance-$(date +%Y%m%d).dump

# Restore into a fresh database
docker exec -i finance-db pg_restore -U finance -d finance_restored < /backups/finance-20260903.dump

Physical backups (a base backup plus continuous WAL archiving) copy the actual on-disk files and the write-ahead log stream. They restore much faster for large databases, and combined with WAL archiving they enable point-in-time recovery — replaying transactions up to any specific timestamp, not just to the last dump. The tradeoff is that a physical backup is tied to the same major Postgres version and generally the same architecture as the source.

Logical (pg_dump)Physical + WAL archiving
Restore granularitySnapshot at dump time onlyAny point in time within retention
Restore speed (large DB)Slow — full re-insert + reindexFast — file copy + WAL replay
Cross-version portabilityYesNo, same major version required
Setup complexityTrivialModerate — needs an archiving tool
Good forQuick dev restores, migrations, spot checksProduction disaster recovery

The practical answer for anything that matters is: keep nightly logical dumps for convenience and quick sanity checks, and run continuous WAL archiving for real disaster recovery with point-in-time restore.

WAL Archiving with WAL-G

WAL-G (and pgBackRest, a comparable and equally solid option) automates the two halves of physical backup: periodic base backups and continuous shipping of WAL segments to object storage as they’re generated.

# docker-compose.yml — Postgres configured to archive WAL via WAL-G
services:
  finance-db:
    image: postgres:17
    environment:
      POSTGRES_DB: finance
      POSTGRES_USER: finance
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      WALG_S3_PREFIX: s3://furybee-pg-backups/finance-db
      AWS_ACCESS_KEY_ID: ${BACKUP_S3_ACCESS_KEY}
      AWS_SECRET_ACCESS_KEY: ${BACKUP_S3_SECRET_KEY}
      AWS_ENDPOINT: https://s3.eu-west-1.amazonaws.com
      WALG_COMPRESSION_METHOD: zstd
    volumes:
      - finance-db-data:/var/lib/postgresql/data
      - ./postgres/archive-command.sh:/usr/local/bin/archive-command.sh:ro
    command:
      - postgres
      - -c
      - archive_mode=on
      - -c
      - archive_command=/usr/local/bin/archive-command.sh %p %f
      - -c
      - wal_level=replica
      - -c
      - max_wal_senders=3

volumes:
  finance-db-data:
#!/usr/bin/env bash
# postgres/archive-command.sh — ships each completed WAL segment to S3 via WAL-G
set -euo pipefail
WAL_PATH="$1"
WAL_FILE="$2"
exec wal-g wal-push "$WAL_PATH"

# --- Scheduled base backup — run nightly via cron or a sidecar timer container ---
docker exec finance-db wal-g backup-push /var/lib/postgresql/data

# List existing base backups
docker exec finance-db wal-g backup-list
# name                          last_modified          wal_segment_backup_start
# base_00000001000000000000004D 2026-09-02T02:00:11Z   00000001000000000000004D
# base_000000010000000000000052 2026-09-03T02:00:08Z   000000010000000000000052

archive_command runs once per completed WAL segment (16MB by default), so under continuous write load this is a steady trickle of small uploads, not one giant nightly transfer — that’s what makes point-in-time recovery possible down to roughly a transaction, not just to the last base backup.

Point-in-Time Recovery

The payoff of WAL archiving is being able to restore to any moment, which matters most for the scenario logical dumps can’t handle at all: someone ran a bad migration or a DELETE without a WHERE clause at 14:32, and you want the database exactly as it was at 14:31.

# 1. Fetch the most recent base backup that predates the target time
docker exec finance-db wal-g backup-fetch /var/lib/postgresql/data_restore LATEST

# 2. Create a recovery signal file and target time — Postgres 17 uses recovery.signal
#    plus postgresql.conf settings rather than the old recovery.conf
cat >> /var/lib/postgresql/data_restore/postgresql.conf <<EOF
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_time = '2026-09-03 14:31:00+00'
recovery_target_action = 'promote'
EOF
touch /var/lib/postgresql/data_restore/recovery.signal

# 3. Start Postgres against the restored data directory — it replays WAL
#    up to the target time, then promotes to a normal read/write instance
docker run -d --name finance-db-recovery \
  -v /var/lib/postgresql/data_restore:/var/lib/postgresql/data \
  postgres:17
-- Verify the restore landed where expected
SELECT now(), pg_last_wal_replay_lsn();
SELECT * FROM audit_log ORDER BY created_at DESC LIMIT 5;
-- Confirm the bad delete/migration is not present

recovery_target_action = 'promote' means Postgres automatically exits recovery mode and becomes writable once it hits the target time — the alternative, pause, stops just before promoting so you can inspect the state and move the target time incrementally if the first guess wasn’t quite right.

Off-Site Storage and Encryption

Backups stored only on the same host as the database they protect defend against exactly one failure mode — accidental data corruption — and fail completely against the ones that matter most: disk failure, a compromised host, or a provider-level incident. Object storage in a different region or provider from the database itself is the baseline, not an enhancement.

# WAL-G reads standard AWS env vars, so any S3-compatible endpoint works —
# a different cloud provider than the one hosting the VPS is a reasonable choice
AWS_ENDPOINT=https://s3.eu-west-1.amazonaws.com
# or, for pure separation from the VPS's own cloud provider:
AWS_ENDPOINT=https://<account>.r2.cloudflarestorage.com

# Encryption matters both in transit (S3 endpoints default to TLS) and at rest.
# WAL-G supports envelope encryption via libsodium or OpenPGP directly, so
# backups are encrypted before they ever leave the host — add to the
# environment block and every file is encrypted before upload:
WALG_LIBSODIUM_KEY: ${BACKUP_ENCRYPTION_KEY}

Store that key somewhere other than next to the backups it protects — a password manager or a separate secrets store — since an attacker who gets both the encrypted backup and the key sitting in the same bucket gains nothing from the encryption at all.

Retention

Keeping every WAL segment and every base backup forever is both expensive and unnecessary — retention policy should match how far back you plausibly need to recover, not “as long as storage allows.”

# Keep the last 7 base backups and everything needed to reach them via WAL,
# delete anything older — WAL-G handles the dependency chain correctly
docker exec finance-db wal-g delete retain 7 --confirm

# Alternative: time-based retention
docker exec finance-db wal-g delete before FIND_FULL $(date -d '30 days ago' +%Y-%m-%dT%H:%M:%SZ) --confirm

A reasonable default for a small production database: keep 30 days of point-in-time recovery capability, plus a monthly base backup retained for a year for compliance or long-tail “we need last March’s data” requests.

The Restore Drill

None of the above matters if nobody has verified it actually works. The drill should run on a schedule, against real backup artifacts, into an isolated environment, and check something meaningful — not just “the restore command exited zero.”

#!/usr/bin/env bash
# scripts/restore-drill.sh — run monthly via cron, alerts on failure
set -euo pipefail

DRILL_CONTAINER="pg-restore-drill"
EXPECTED_MIN_ROWS=1000

echo "Fetching latest base backup..."
docker run --rm -v drill-data:/var/lib/postgresql/data \
  -e AWS_ACCESS_KEY_ID -e AWS_SECRET_ACCESS_KEY -e WALG_S3_PREFIX \
  postgres:17-walg wal-g backup-fetch /var/lib/postgresql/data LATEST

echo "Starting isolated Postgres instance from restored data..."
docker run -d --name "$DRILL_CONTAINER" \
  -v drill-data:/var/lib/postgresql/data \
  --network none \
  postgres:17

sleep 10

echo "Running sanity checks..."
ROW_COUNT=$(docker exec "$DRILL_CONTAINER" psql -U finance -d finance -tAc \
  "SELECT count(*) FROM transactions;")

if [ "$ROW_COUNT" -lt "$EXPECTED_MIN_ROWS" ]; then
  echo "FAIL: restored row count ($ROW_COUNT) below expected minimum ($EXPECTED_MIN_ROWS)"
  docker rm -f "$DRILL_CONTAINER"
  exit 1
fi

LATEST_TX=$(docker exec "$DRILL_CONTAINER" psql -U finance -d finance -tAc \
  "SELECT max(created_at) FROM transactions;")
echo "OK: restored $ROW_COUNT rows, latest transaction at $LATEST_TX"

docker rm -f "$DRILL_CONTAINER"
docker volume rm drill-data

--network none is deliberate: the drill container should never be reachable from the outside or able to reach anything else — it exists purely to prove the restore path works, not to serve traffic. Wiring this script into a monthly cron job with alerting on non-zero exit is what turns “we have backups” into “we know our backups work,” and it’s the single highest-value thing in this whole strategy — a backup nobody has restored recently is unverified by definition, no matter how confident the retention dashboard looks.

Key Takeaways

  • Logical dumps (pg_dump) are for convenience and quick restores; WAL archiving is for real disaster recovery with point-in-time granularity — run both.
  • WAL-G or pgBackRest automate base backups plus continuous WAL shipping; either is a solid choice, pick based on team familiarity.
  • Point-in-time recovery via recovery_target_time is the tool for “someone ran a bad migration a few minutes ago” — logical dumps can’t do this at all.
  • Store backups off-site, in a different provider or region than the database itself, and encrypt them before they leave the host.
  • Set a retention policy sized to actual recovery needs, not indefinite storage — WAL-G’s delete retain/delete before handle the dependency chain correctly.
  • Schedule a restore drill that checks real data (row counts, latest timestamps), not just a successful exit code, and alert when it fails.
  • An untested backup strategy is a hypothesis; only a restore drill that actually runs, on a schedule, converts it into a fact you can rely on during an incident.