Postgres backup verification: pre-deploy snapshots with automated restore checks

Data loss is the one failure mode you can’t recover from with a hotfix and a deploy. Everything else (a 500, a slow query, a broken UI) is temporary. A missing row is permanent. So when we built Bubblegram’s deployment pipeline, we made backup verification a hard gate: the deploy doesn’t proceed until we’ve proven the backup restores cleanly.

Here’s how that works in practice.

What’s the backup strategy?

We run Postgres 17 in Docker on a single VPS. Logical backups with pg_dump are the right tool at this scale: fast, portable, and they work across Postgres versions without WAL streaming or replica infrastructure.

Three triggers, one script:

Trigger	When	Retention
Daily cron	03:00 UTC every day	30 days
Pre-deploy	Before every deploy, automatically	7 days
Manual	On demand via ./scripts/db-backup.sh manual	Until deleted

The daily backup is a safety net for catastrophic loss or corruption that isn’t caught immediately. The pre-deploy snapshot is the one we actually rely on: if a migration goes wrong, you can roll back to the exact state the database was in two minutes before the deploy ran. The manual trigger exists for “we’re about to do something scary” situations.

All three land in the same place: a private bucket on Cloudflare R2, under a prefix that identifies the trigger type.

Why R2?

We already use R2 for other assets, so there’s no new vendor or billing to manage. R2 doesn’t charge egress fees, which matters when you’re pulling backups down locally to test them. And it’s S3-compatible, so awscli works out of the box.

The backup script is straightforward:

#!/usr/bin/env bash
set -euo pipefail

TRIGGER="${1:-manual}"
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
GIT_SHA=$(git rev-parse --short HEAD 2>/dev/null || echo "unknown")
FILENAME="${TRIGGER}/${TIMESTAMP}-${GIT_SHA}.sql.gz"

docker compose exec -T postgres pg_dump -U bubblegram bubblegram \
  | gzip \
  | aws s3 cp - "s3://${R2_BUCKET}/${FILENAME}" \
      --endpoint-url "$R2_ENDPOINT" \
      --region auto

echo "Backup uploaded: ${FILENAME}"

No temp files. The dump streams directly into gzip, which streams directly to R2. set -euo pipefail means any step failing exits with a non-zero code, which the deploy pipeline treats as a blocking error.

The filename encodes the git SHA, so rolling back to a specific deploy is easy: find the backup that matches the SHA that deployed before the bad one.

R2 lifecycle rules handle retention per prefix: 30 days for daily/, 7 days for pre-deploy/. Manual backups have no lifecycle rule and persist until explicitly deleted.

How does it fit into deploys?

The backup runs as part of the Ansible deploy playbook, before any containers are rebuilt:

- name: Pre-deploy database backup
  command: ./scripts/db-backup.sh pre-deploy
  args:
    chdir: "{{ app_dir }}"
  register: backup_result

- name: Run backup restore simulation
  command: ./scripts/verify-backup.sh "{{ backup_result.stdout_lines[-1] | regex_search('pre-deploy/[^ ]+') }}"
  args:
    chdir: "{{ app_dir }}"

- name: Rebuild and restart containers
  command: docker compose up -d --build

If either the backup or the verification fails, the playbook stops. The deploy does not run. You will never deploy without a verified, restorable snapshot of your data.

What does the restore simulation actually do?

Taking a backup is easy. Knowing it’s restorable is the hard part. Backup files can be silently corrupted. pg_dump can fail mid-stream and the script won’t always catch it. The gzip can be truncated. You don’t find out any of this happened until the moment you need to restore, which is exactly when you have the least time to discover the backup is broken.

So every pre-deploy backup triggers a restore simulation immediately after it’s uploaded.

The script:

1. Downloads the backup from R2
2. Spins up an ephemeral Postgres 17 container on a random port
3. Restores the dump into it
4. Runs a validation suite
5. Tears the container down

#!/usr/bin/env bash
set -euo pipefail

BACKUP_KEY="$1"
CONTAINER="pg-verify-$$"
TIMEOUT=60

cleanup() {
  docker rm -f "$CONTAINER" 2>/dev/null || true
}
trap cleanup EXIT

# Download
aws s3 cp "s3://${R2_BUCKET}/${BACKUP_KEY}" /tmp/verify.sql.gz \
  --endpoint-url "$R2_ENDPOINT" --region auto

# Spin up ephemeral Postgres (password is throwaway — this container is destroyed after the check)
docker run --rm -d \
  --name "$CONTAINER" \
  -e POSTGRES_USER=bubblegram \
  -e POSTGRES_PASSWORD=verify_only \
  -e POSTGRES_DB=bubblegram \
  -v "$(pwd)/scripts:/scripts:ro" \
  postgres:17-alpine

# Wait for Postgres to be ready (with timeout)
elapsed=0
until docker exec "$CONTAINER" pg_isready -U bubblegram -q; do
  sleep 1
  elapsed=$((elapsed + 1))
  if [ "$elapsed" -ge "$TIMEOUT" ]; then
    echo "Timed out waiting for Postgres" >&2
    exit 1
  fi
done

# Restore
gunzip -c /tmp/verify.sql.gz \
  | docker exec -i "$CONTAINER" psql -U bubblegram -d bubblegram -q

# Validate
docker exec "$CONTAINER" psql -U bubblegram -d bubblegram -f /scripts/validate-backup.sql

echo "Restore simulation passed."

The trap cleanup EXIT ensures the container is always removed, even if the script fails partway through.

What do we actually check?

This is where most backup strategies get lazy. You can’t check every row. But “row count looks right” isn’t enough either. That passes even if half the schema is missing.

We check three things, in order of what would actually hurt us if it was wrong.

Schema completeness. Every table that should exist does exist. A restore that silently drops a table would pass a row count check and fail the moment the API tries to query it.

-- Returns rows for any expected table that is missing from the restore. Any result = fail.
SELECT expected.tbl
FROM (VALUES
  ('user'),
  ('projects'),
  ('messages'),
  ('sessions'),
  ('api_keys')
) AS expected(tbl)
LEFT JOIN information_schema.tables t
  ON t.table_name = expected.tbl AND t.table_schema = 'public'
WHERE t.table_name IS NULL;

Row count sanity. Not zero. We check that all core tables have at least one row on a live system. This won’t catch data loss of a few rows, but it catches a fully empty restore, which is the failure mode that actually happens when a dump is truncated.

SELECT
  'user'     AS tbl, count(*) AS rows FROM "user"     UNION ALL
  SELECT 'projects',  count(*) FROM projects   UNION ALL
  SELECT 'messages',  count(*) FROM messages   UNION ALL
  SELECT 'sessions',  count(*) FROM sessions   UNION ALL
  SELECT 'api_keys',  count(*) FROM api_keys;

The validation script errors if any count is 0 on a system that should have data. New installs with empty tables skip this check.

Data freshness. If the newest message in the backup is more than 48 hours old on a system with regular traffic, something went wrong with the backup pipeline long before this run. It may be dumping a stale replica or an old snapshot.

SELECT
  max(created_at) AS newest_message,
  now() - max(created_at) AS age
FROM messages
HAVING count(*) > 0;
-- No rows returned on empty table (skip). Populated table: alert if age > 48h.

All three checks run as a single SQL file mounted into the verification container. If any of them return a result that indicates a problem, the script exits non-zero and the deploy is blocked.

What don’t we check?

Every row. We accept that we can’t validate the content of individual records in an automated way without essentially rebuilding the application. The checks above are designed to catch the failure modes that actually happen: corrupted dumps, truncated files, schema drift, empty restores, stale data. They don’t catch “a specific user’s record was garbled.” Neither would any automated check short of a full diff against a second source of truth.

The goal is not perfect certainty. The goal is to catch the failures that would make a restore useless, before we need to use it.

Why not just trust the backup?

Because “the backup ran” and “the backup restores” are two different claims. We’ve seen both fail independently:

• pg_dump exits 0 but wrote a truncated file because a connection dropped mid-stream
• A gzip that passes gunzip -t but fails to restore because the stream was interrupted after the header was written
• A backup that restores cleanly but is missing a table because the dump ran during a migration that dropped and recreated it

None of these are caught by checking whether the backup script ran. All of them are caught by actually restoring and querying the result.

How do you restore to production?

When we need to actually restore, the process mirrors the simulation:

# 1. Stop the API
docker compose stop api

# 2. Download the backup you want (filename includes the git SHA that deployed before the problem)
aws s3 cp "s3://${R2_BUCKET}/pre-deploy/20260624-143201-a3f9b12.sql.gz" /tmp/restore.sql.gz \
  --endpoint-url "$R2_ENDPOINT" --region auto

# 3. Drop and recreate the database
docker compose exec -T postgres psql -U bubblegram -d postgres -c "
  DROP DATABASE bubblegram;
  CREATE DATABASE bubblegram OWNER bubblegram;
"

# 4. Restore
gunzip -c /tmp/restore.sql.gz \
  | docker compose exec -T postgres psql -U bubblegram -d bubblegram

# 5. Restart
docker compose start api

The actual guarantee

Every deploy leaves a breadcrumb: a verified, restorable snapshot of the database as it was before the deploy ran. If a migration corrupts data, you have a path back that was proven restorable 60 seconds before the migration ran. If the backup itself is broken, you find out during the deploy, not during an outage at 2am.

---

If you’re curious about the message delivery system this backup pipeline protects, see how we built the message queue around Telegram’s rate limits.