A Postgres backup that is restored and checked every time

Test a backup by hand

Set DATABASE_URL to the database's connection string. Run these where PostgreSQL's own programs are installed, of the server's version or newer; they need nothing from us and send nothing anywhere.

# 1. back up
pg_dump --format=custom --no-owner --no-privileges --file=backup.dump "$DATABASE_URL"

# 2. restore into a scratch database on a local PostgreSQL of the same major version
createdb restore_test
pg_restore --no-owner --no-privileges --exit-on-error --dbname=restore_test backup.dump

# 3. count every table on both sides, and compare
COUNT="SELECT format('SELECT %L, count(*) FROM %I.%I;', schemaname || '.' || relname, schemaname, relname) FROM pg_stat_user_tables ORDER BY 1"
psql "$DATABASE_URL" -XAtc "$COUNT" | psql "$DATABASE_URL" -XAt > live.txt
psql -d restore_test -XAtc "$COUNT" | psql -d restore_test -XAt > restored.txt
diff live.txt restored.txt && echo "every table came back with the same number of rows"

--exit-on-error makes the restore stop at its first error instead of carrying on past it. A role that a row-level security policy names must exist first (createuser --no-login <role>), and so must every extension the backup creates (pgvector and PostGIS are packages of their own). The counts can differ by the rows written while the backup ran. This compares rows only; restoreproof also compares every column, constraint, index, function, trigger and policy.

A recorded run

On a synthetic database shaped like a small Supabase project (auth and storage schemas, policies calling auth.uid(), a partitioned table), recorded 9 Oct 2026. Not anyone's data.

restoreproof 0.1.0 · restore test PASSED · 2026-10-09 20:37 UTC

  source    PostgreSQL 17.9 (supabase), 9.1 MB, read through RESTOREPROOF_DATABASE_URL
  backup    91.5 KB in 0.1 s (pg_dump 17.9, custom format)
  restored  in 0.1 s into PostgreSQL 17.9, a throwaway cluster on 127.0.0.1 with no other connections
  checked   5,925 rows in 9 tables against the live database: all match
            92 schema objects: 11 tables, 37 columns, 17 constraints, 14 indexes, 1 view, 1
            materialized view, 2 functions, 1 trigger, 3 policies, 3 sequences, 1 enum, 1 domain

  table                                   live → restored
  auth.users                               400 → 400
  public.events_2026_09                  1,500 → 1,500
  public.events_2026_10                  1,500 → 1,500
  public.notes                           2,000 → 2,000
  public.profiles                          400 → 400
  public.settings                            2 → 2
  storage.buckets                            1 → 1
  storage.objects                          120 → 120
  supabase_migrations.schema_migrations      2 → 2

  notes
    - the rows of supabase_functions.hooks are left out of the backup by design, as platform data
      Supabase writes for itself (its own restore guide leaves out the Storage vector tables); their
      definitions are checked
    - 1 Supabase platform schema(s) left out of the backup, as `supabase db dump` does: graphql

An email when a weekly restore test fails or does not run is not built.
It would email the people you name when a test fails, when the backup shrinks, or when no test has
run for eight days, from outside GitHub's scheduler. Today GitHub emails one person when a scheduled
run fails, and nobody when it never runs. agentcheck's free hourly watch covers MCP servers only. If
you would pay for this one, say so with one click:
https://agentwares-agentcheck.vercel.app/restoreproof?ref=restoreproof#watch

An email when a weekly restore test fails or does not run is not built.

Sources

• Supabase docs: Database backups (read 9 Oct 2026)
• Supabase docs: Automated backups using GitHub Actions (read 9 Oct 2026)
• Neon pricing (read 9 Oct 2026)
• Neon docs: Automate pg_dump backups to S3 with GitHub Actions (read 9 Oct 2026)
• GitHub docs: Downloading workflow artifacts (read 9 Oct 2026)
• GitHub docs: Notifications for workflow runs (read 9 Oct 2026)