A nightly pg_dump that fails with the reason, not with "Network is unreachable"

Munchable's Postgres runs on a hosted tier that provides no managed backups. That makes a nightly GitHub Actions job the only line of defence against data loss, so the workflow is intentionally simple and loud: pg_dump to Cloudflare R2, and if it fails, the platform emails the repo admins. This post is about the checks that were added to it 27 minutes after it first shipped, and the ones that were there from the start.

Two archives, two retention windows

#   full  every schema and every row, catalog.products included. Large (the
#         catalog is the bulk of the database), so it is kept for ~30 days.
#   core  the same dump with catalog.products' ROWS excluded (its table
#         definition, indexes and constraints are still there). Tiny, because
#         what is left is the irreplaceable half: accounts, entitlements,
#         contribution rewards and their revisions, consent records, support
#         threads, and the curated taxonomy and rule entries. Cheap to keep for
#         a year and fast to restore.

The product catalogue is roughly 400 MB and dominates the dump. It is also the half that could be rebuilt. Accounts, consent records, support threads and the curated data are a few megabytes and cannot. So the small archive is kept for a year, the big one for a month, and the whole thing fits inside R2's free storage tier at effectively zero cost.

Retention is not done by the workflow. "The workflow never deletes anything, because deleting backups from CI is how you lose them to a date-math bug." The bucket has lifecycle rules per prefix instead.

The preflight that was added after the first run

The first run died with Network is unreachable, which says nothing about why. The cause was the connection string. The hosting provider offers three ways in, and two of them cannot work from this runner:

# Preflight the connection string before pg_dump gets a chance to fail
# with a bare "Network is unreachable", which says nothing about why.
# Only the host and port are read out of the secret here, never the
# password, and the host is already visible in libpq's own errors.
DB_HOSTPORT="$(printf '%s' "$SUPABASE_DB_URL" | sed -E 's#^[a-zA-Z+]+://##; s#^.*@##; s#[/?].*$##')"

case "$DB_HOST" in
  db.*.supabase.co)
    echo "::error::SUPABASE_DB_URL points at the direct connection host (${DB_HOST}). That host is IPv6-only on this plan and GitHub's runners are IPv4-only, so the connection cannot be made. Use the Session pooler URL instead. See docs/DB-BACKUPS.md."
    exit 1
    ;;
esac

if [ "$DB_PORT" = '6543' ]; then
  echo "::error::SUPABASE_DB_URL 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

The direct host is IPv6-only, and GitHub's hosted runners are IPv4-only. The transaction pooler on port 6543 is what the app uses, and pg_dump cannot run against it because it needs session-level features the transaction mode does not provide. Only the session pooler on port 5432 works. A wrong secret now fails in the first seconds with an error annotation that names the fix.

Two details in that block are worth copying. It parses only the host and port out of the secret, never the password, and it justifies that by pointing out libpq prints the host in its own errors anyway. And each message points at the runbook file in the repo, so the person reading a red workflow at 4am has somewhere to go.

The checks that were there from the start

# Fail fast if either dump is empty or the archive is unreadable or
# truncated. pg_restore --list parses the whole table of contents, so
# it catches a dump that died halfway.
for f in "$FULL_FILE" "$CORE_FILE"; do
  test -s "$f"
  pg_restore --list "$f" > /dev/null
done

# A core dump that is somehow bigger than the full one means the
# exclusion silently matched nothing, which is worth knowing about.
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

A zero-byte file is the classic silent backup failure. A truncated custom-format archive is the subtler one, and pg_restore --list is a cheap way to prove the table of contents is intact without restoring anything. The third check is about a rename: if the bulk table ever moves, --exclude-table-data matches nothing, and the "small" archive quietly becomes the large one.

The small archive is uploaded first, on purpose. It is the one that matters most, so if the larger upload is the one that fails, the important half is already safe.

The client version trap

The runner ships an older Postgres client whose PATH entry wins by default. Dumping a newer server with an older pg_dump aborts. The install step pins the path to the version it just installed and fails loudly if the binary is not actually there, rather than letting the next step discover it.

Two environment variables handle R2's S3 compatibility: AWS_DEFAULT_REGION=auto, because R2 ignores the region but the CLI insists on one, and request and response checksum calculation set to when_required, because the CLI's default CRC32 checksums can trip R2's endpoint.

Why it stays even if the plan changes

The docs note that a paid tier would add managed daily backups, and that this job would stay anyway, because it is an independent, downloadable copy that lives outside the database provider's account. A backup you can only restore through the vendor that lost the data is half a backup.

There is nothing to click on for this one. The database behind munchable.app is dumped every night at 03:42 UTC, off the top of the hour to avoid everyone else's cron jobs, and the restore runbook has been exercised. An earlier post covered the other side of data hygiene on the same database, deleting 50,000 rows inside a 60 second serverless function.