Backups That You Don't Restore Aren't Backups: A Weekly Restore Drill

This article was originally published on Jo4 Blog.

Most teams have backups. Half of those teams have never restored one. The other half discovered the hard way that their dumps were corrupt, incomplete, or pointed at a database schema that no longer exists.

Backups you don't actively restore aren't backups — they're warm fuzzy feelings. Here's how we built a backup pipeline whose first-class citizen is the weekly restore, not the nightly dump.

The Pipeline at 30,000 Feet

Three GitHub Actions workflows:

1. Nightly backup — pg_dump from the production DB, gzip, upload to DigitalOcean Spaces. Runs at 03:00 UTC daily.
2. Restore test — pulls the latest backup, restores it into a Docker postgres:16 container in the runner, asserts table count and row counts. Runs Sunday 04:00 UTC.
3. Manual restore — parameterized one-click pipeline for the actual recovery flow. Same code path the restore test exercises, minus the assertions, plus the parameter for "which backup."

The clever bit isn't (1). The clever bit is (2) hard-failing if (1) silently broke.

The Backup Workflow

name: DigitalOcean-460-Backup

on:
  schedule:
    - cron: '0 3 * * *'   # 03:00 UTC daily
  workflow_dispatch:

concurrency:
  group: jo4-db-backup
  cancel-in-progress: false   # Don't cancel a running backup

env:
  DO_REGION: nyc3
  PROJECT_NAME: jo4
  SCHEMA_NAME: alertstage
  BACKUP_PREFIX: db-daily-backup

Two settings worth highlighting:

• concurrency.cancel-in-progress: false — if workflow_dispatch fires while the cron is running, queue the new run, don't cancel the old one. Cancelling a half-uploaded dump leaves a corrupt object in the bucket and a confusing log trail.
• Pin the pg_dump major version. PostgreSQL is forgiving about minor versions, but a pg_dump from a different major to the server is a bug factory:

- name: Install PostgreSQL client and verify version
  run: |
    sudo apt-get install -y -qq postgresql-client
    PG_DUMP_MAJOR=$(pg_dump --version | awk '{print $3}' | cut -d. -f1)
    if [ "$PG_DUMP_MAJOR" != "16" ]; then
      echo "❌ pg_dump must be 16 to match server, got: $(pg_dump --version)"
      exit 1
    fi

The Firewall Dance

Our managed Postgres is locked behind a trusted-source IP allowlist. The runner IP rotates every job. So every backup adds the runner's IP, runs the dump, and removes the IP — even if the dump fails:

- name: Open DB trusted-source for runner IP
  id: db_firewall
  run: |
    RUNNER_IP=$(curl -s https://api.ipify.org)
    DB_ID="${{ steps.database.outputs.db_id }}"
    doctl databases firewalls add "$DB_ID" --rule "ip_addr:${RUNNER_IP}"
    echo "runner_ip=$RUNNER_IP" >> $GITHUB_OUTPUT
    echo "db_id=$DB_ID"          >> $GITHUB_OUTPUT

- name: Close DB trusted-source (revoke runner IP)
  if: always()   # critical — runs even if the dump step failed
  run: |
    UUIDS=$(doctl databases firewalls list "$DB_ID" \
      | awk -v ip="$RUNNER_IP" 'NR>1 && $3=="ip_addr" && $4==ip {print $1}')
    echo "$UUIDS" | while read -r uuid; do
      [ -z "$uuid" ] && continue
      doctl databases firewalls remove "$DB_ID" "$uuid" || true
    done

if: always() is non-negotiable here. If the dump fails and we don't clean up, we leak ephemeral runner IPs into the trusted-sources list. After a few months you're looking at hundreds of stale entries.

The Dump Itself

- name: Run pg_dump
  run: |
    TS=$(date -u +%Y%m%d-%H%M%S)
    DUMP_FILE="/tmp/jo4db-${TS}.sql"

    PGPASSWORD="${{ steps.database.outputs.db_password }}" \
    PGSSLMODE=require \
    pg_dump \
      -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_DATABASE}" \
      --schema=${SCHEMA_NAME} \
      --no-owner \
      --no-privileges \
      --quote-all-identifiers \
      -f "${DUMP_FILE}"

    gzip -9 "${DUMP_FILE}"

    SIZE_BYTES=$(stat -c%s "${DUMP_FILE}.gz")
    if [ "$SIZE_BYTES" -lt 1024 ]; then
      echo "❌ Dump suspiciously small ($SIZE_BYTES bytes) — aborting upload"
      exit 1
    fi

--no-owner --no-privileges — the dump is restorable on any Postgres 16+ instance, not just the one with our exact role names. Important for the restore test, which uses a fresh container.

--quote-all-identifiers — defends against case-sensitivity surprises if the restore target uses different default search paths.

The 1KB sanity floor — even an empty schema produces more than 1KB of dump (CREATE TABLE statements, comments, etc.). If the dump is smaller, something went wrong, abort before you upload garbage that overwrites yesterday's good backup.

The Lifecycle Rule (Idempotently)

We expire backups after 30 days via a bucket lifecycle rule. The catch: s3cmd setlifecycle replaces the entire bucket policy. We don't want to clobber other rules:

- name: Ensure 30-day lifecycle rule on backup prefix
  run: |
    BUCKET="${PROJECT_NAME}-assets"
    RULE_ID="expire-daily-backups"

    EXISTING=$(s3cmd getlifecycle "s3://${BUCKET}" 2>&1 || true)

    if echo "$EXISTING" | grep -qF "${RULE_ID}"; then
      echo "✅ Lifecycle rule already in place — skipping"
    elif echo "$EXISTING" | grep -q "<Rule>"; then
      echo "❌ Bucket has other rules but not '${RULE_ID}'."
      echo "   Refusing to overwrite. Merge manually."
      exit 1
    else
      echo "<LifecycleConfiguration><Rule><ID>${RULE_ID}</ID>..." > /tmp/lifecycle.xml
      s3cmd setlifecycle /tmp/lifecycle.xml "s3://${BUCKET}"
    fi

Three branches: ours is there (skip), foreign rules but not ours (abort, don't overwrite), nothing at all (apply ours). This is the only way to safely re-run lifecycle setup automatically.

The Restore Test — The Whole Point

Here's where most teams stop. Don't.

name: DigitalOcean-470-Restore-Test

on:
  schedule:
    - cron: '0 4 * * 0'   # Sunday 04:00 UTC

jobs:
  restore-test:
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: testadmin
          POSTGRES_PASSWORD: testpw_local_only
          POSTGRES_DB: jo4test
        ports: ['5432:5432']

    steps:
      - name: Sandbox banner & hard guard
        run: |
          # Hard guard #1 — target host MUST be localhost
          if [ "${TEST_PG_HOST}" != "localhost" ] \
             && [ "${TEST_PG_HOST}" != "127.0.0.1" ]; then
            echo "❌ Restore target is not localhost: ${TEST_PG_HOST}"
            exit 1
          fi

          # Hard guard #2 — must not look like a managed service
          if echo "${TEST_PG_HOST}" \
             | grep -qE "(ondigitalocean|amazonaws|gcp|azure)"; then
            echo "❌ Target appears to be cloud-managed: ${TEST_PG_HOST}"
            exit 1
          fi

The hard guards are paranoia, on purpose. The restore-test workflow shares code paths with the manual restore workflow. One env var typo — TEST_PG_HOST=jo4-db-do-user-...ondigitalocean.com — and you've just restored last week's backup over today's production database. The two grep -qE checks make that mistake un-shippable.

The rest of the restore test:

• Download the most recent backup from Spaces.
• Hard-fail if the most recent backup is older than 2 days. This is the silent-failure detector — if the nightly cron stops working, the next Sunday's restore test fails loudly within a week.
• Pipe the gzipped SQL into psql against the localhost container.
• Assert: minimum table count (catches half-applied schemas), positive row counts on key tables (catches empty dumps that passed the 1KB sanity floor).
• Record end-to-end restore duration. This is your empirical RTO, not a number you guessed.

What This Buys You

• A nightly backup that fails noisily if the dump is anomalously small or the upload doesn't complete.
• A 30-day rolling window maintained by the bucket itself, not a cleanup script you'll forget about.
• An automated weekly proof that backups are restorable, restored against a real Postgres of the same major version, with assertions on what came out.
• An empirical RTO — every Sunday measures it, every quarter you can compare measurements against your commitment.
• A manual restore workflow that's the same code path the test exercises — your worst day is not the day you debug the restore script.

Lessons Learned

• Untested backups have a 50% failure rate. Industry data, our internal data, your data — same number. Test.
• Failing-loud beats failing-silent every time. A workflow that hard-fails on stale backups, on small dumps, on cross-major pg_dump versions, on non-localhost restore targets — that workflow tells you when something's broken before you need it to work.
• Pin the pg_dump major version. Cross-major dumps will seem to work and will be missing things you depended on.
• Always use if: always() for cleanup steps that touch shared state (firewall rules, lock files, lifecycle policies). The cleanup needs to run on failure, otherwise failure leaks state.
• Concurrency cancellation is the wrong default for backups. Queue them, don't cancel them. Cancelled pg_dump mid-upload is the kind of bug that masquerades as a corrupt restore six weeks later.

---

When did you last restore a backup? What broke when you tried? Drop it in the comments — the post-mortems are always educational.

Building jo4.io — a URL shortener with a documented RPO, RTO, and a weekly proof we can hit them.

Comments

[Mads Hansen]

This is the right operational habit. I would make the restore assertions describe the recovery contract, not just the presence of data. Table and row counts can pass while sequences, extensions, functions, RLS policies, grants, or a critical cross-table invariant are broken.

A small post-restore suite of semantic canaries helps: run a few real read paths under the same roles the application uses, verify sequence next-values do not collide, inspect invalid constraints/indexes, and prove the expected extensions and policies exist. Because this dump is intentionally schema-scoped and omits ownership/privileges, I would also document which recovery pieces live outside it and test those separately.

One more useful distinction: this drill proves logical-dump recovery and measures its RTO. It does not prove point-in-time recovery or the achievable RPO between nightly dumps. Keeping those as separate, named recovery contracts makes the green check much more meaningful.