Our nightly backup refuses to run for three reasons before it ever reaches pg_dump, and each refusal names the fix

Munchable's database is on Supabase's Free plan, which provides no managed backups. Not "backups you have to pay to restore", not "seven days of point in time". None. The entire backup story is a 185-line GitHub Actions workflow that runs pg_dump and copies the result to Cloudflare R2, nightly, at 03:42 UTC.

A scheduled job that nobody watches is a different kind of program from one a person runs. Nobody is standing by to read the log, and a failure is only noticed because GitHub emails repo admins when a scheduled workflow fails. So the job is written to be loud, and to fail early with a message that says what to change. Three of its refusals happen before pg_dump is invoked at all.

The product itself holds almost nothing about people, which is the architecture described on munchable.app/privacy: conditions, scan history and verdicts stay on the device. That makes the server-side half small, and it is exactly why losing it would be unrecoverable rather than inconvenient. There is no second copy anywhere.

Refusal one: the client is older than the server

pg_dump's major version must be at least the server's. GitHub's ubuntu-latest runner ships a Postgres client, and its PATH entry wins. So the job installs the one it wants and then does two things most workflows skip:

PG_BIN="/usr/lib/postgresql/${PG_MAJOR}/bin"
test -x "${PG_BIN}/pg_dump" || { echo "::error::postgresql-client-${PG_MAJOR} did not install ${PG_BIN}/pg_dump"; exit 1; }
echo "${PG_BIN}" >> "$GITHUB_PATH"
"${PG_BIN}/pg_dump" --version

It asserts the binary it just installed actually exists, pins PATH for every later step, and prints the version. Without the GITHUB_PATH line, the runner's own older client is what runs, the dump aborts with a version mismatch, and the message blames the server. The PG_MAJOR value lives in the job's env with a comment saying to bump it here when the managed server moves, because pg_dump can always dump an older server and the failure text names the version it wants.

Refusals two and three: the connection string is the wrong one of three

This is the part I would not have known to write without getting it wrong. A managed Postgres offers several hostnames and ports, and they are not interchangeable:

• the direct host is IPv6-only on this plan, and GitHub's runners are IPv4-only, so the connection simply cannot be made
• the transaction pooler on 6543 is what the application uses, and pg_dump cannot run against it
• the session pooler on 5432 is the one that works

Each of the first two produces a failure that tells you nothing. The IPv6 one surfaces as a bare "Network is unreachable". So the job reads the host and port out of the secret first, with sed, touching nothing else in it:

DB_HOSTPORT="$(printf '%s' "$SUPABASE_DB_URL" | sed -E 's#^[a-zA-Z+]+://##; s#^.*@##; s#[/?].*$##')"

Only the host and port come out, never the password, and the host is already visible in libpq's own error messages, so nothing new is exposed in the log. Then two checks, each of which spends its error message on the remedy rather than the symptom:

case "$DB_HOST" in
  db.*.supabase.co)
    echo "::error::... That host is IPv6-only on the Free plan and GitHub's runners are IPv4-only ... Use the Session pooler URL ..."
    exit 1
    ;;
esac

if [ "$DB_PORT" = '6543' ]; then
  echo "::error::... uses the transaction pooler (port 6543). pg_dump cannot run against it; that URL is the app's DATABASE_URL. Use the Session pooler URL on port 5432 ..."
  exit 1
fi

Those two messages are longer than the code around them. That ratio is right for a job whose reader is a person six months from now who has just rotated a secret and pasted whichever string the dashboard offered first.

Two archives, because the halves have different replacement costs

Every run writes two dumps of the same database:

Archive	Contents	Retention
full	everything, product catalogue rows included	~30 days
core	the same dump with the catalogue table's row data excluded	~365 days

The product catalogue table dominates the database by a wide margin, so full is large and kept for a month. core uses --exclude-table-data, which keeps the table definition, its indexes and its constraints, and drops only the rows. What is left is tiny and is the half that cannot be rebuilt from anything: accounts, entitlements, contribution rewards and their revisions, consent records, support threads, and the curated data the app depends on. A core restore produces a structurally complete database with an empty products table, which is a working system, not a pile of SQL.

Both are custom-format dumps (--format=custom --compress=6), which means pg_restore can be selective or parallel and --no-owner is a decision made at restore time rather than baked into the archive. Both cover every schema, including the managed auth schema, so accounts come back with the data rather than as orphaned rows.

Cost for all of this is about zero: R2 has a free storage tier and no egress fees, and a nightly dump is a couple of minutes of free Actions time.

A dump that died halfway is a file, and files look fine

The verification step is four lines and it is the reason I trust any of this:

for f in "$FULL_FILE" "$CORE_FILE"; do
  test -s "$f"
  pg_restore --list "$f" > /dev/null
done

test -s catches empty. pg_restore --list parses the whole table of contents, which catches the case that actually happens: a dump that was truncated when the connection dropped, leaving a plausibly sized file that no restore will ever complete. Checking the archive is readable in the job that produced it is the cheapest possible version of testing your backups, and it is the only one that runs every single night.

There is one more check, a warning rather than a failure:

if [ "$(stat -c%s "$CORE_FILE")" -ge "$(stat -c%s "$FULL_FILE")" ]; then
  echo "::warning::core dump is not smaller than the full dump; check that ${BULK_TABLE} still exists"
fi

If the two archives are the same size, the exclusion matched nothing: the table was renamed, moved schema, or never existed under the name in the env block. The backups would still be valid. The 365-day retention plan built on "core is small" would quietly be wrong.

Two more small decisions

The upload order is deliberate. core goes first, because it is the archive that matters most, and if the larger upload is the one that fails there is still a copy of the irreplaceable half.

And the cron is 42 3 * * * rather than 0 3 * * *. Off-peak, and off the top of the hour, because everyone else's scheduled jobs fire at :00 on the same shared runners.

The concurrency group is the last line of defence against the obvious own goal:

concurrency:
  group: db-backup
  cancel-in-progress: false

Never two at once, and never cancel the one already running. Cancelling a dump mid-flight wastes the run and produces precisely the truncated file the verification step exists to catch.

If you would like to see the product this is protecting, munchable.app is the whole thing, and munchable.app/about says who is behind it, which in this case is also the person GitHub emails at 03:45 UTC.