A nightly Postgres backup for $0, and the four things that break it

CogniPrep's database is on Supabase's free plan, which provides no managed backups. For a product with paying customers that is not a state you can leave alone, and the paid tiers were not where the money needed to go yet. So the entire backup strategy is one GitHub Actions workflow: pg_dump to a custom-format archive, uploaded to Cloudflare R2.

Cost is effectively zero. R2 has no egress fees and a 10 GB free tier, and a nightly dump occupies a runner for one or two minutes, inside the free Actions allowance. The interesting part is not the idea, it is that four separate things had to be got right before it worked at all, and each of them fails in a way that does not obviously point at the cause.

1. The connection string must be the session pooler

Supabase offers three ways to reach the database, and only one of them works here.

• The transaction pooler on port 6543 is what the application uses. pg_dump cannot run through a pooler in transaction mode.
• The direct host is IPv6-only on the free tier, and GitHub's hosted runners are IPv4-only, so the connection times out with nothing useful in the log.
• The session pooler on port 5432 is the one that works.

Getting this wrong produces either a pg_dump error about an unsupported operation or a hang. Neither says "you picked the wrong one of three strings that all look like a Postgres URL", so the workflow says it instead, right next to the secret:

env:
  # MUST be the SESSION pooler URL (port 5432).
  # Not the transaction pooler (6543, breaks pg_dump) and not the direct
  # db.<ref>.supabase.co host (IPv6-only on Free, unreachable from here).
  SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }}

The same constraint applies to the restore, which is why the runbook repeats it.

2. The client version has to beat the server, and PATH decides which client you get

pg_dump's major version must be at least the server's. It can always dump an older server, so installing the current stable client is the safe move. The trap is that the runner already ships a Postgres client, and its PATH entry wins:

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

Installing the newer client is not enough; the install has to be asserted and the PATH pinned for later steps. Otherwise you get a green apt step followed by a version-mismatch abort, which reads like a server problem and is not.

3. R2 is S3-compatible, and "compatible" has edges

Two environment variables exist purely for that:

AWS_DEFAULT_REGION: auto
AWS_REQUEST_CHECKSUM_CALCULATION: when_required
AWS_RESPONSE_CHECKSUM_VALIDATION: when_required

R2 ignores the region, but the AWS CLI insists on having one. And AWS CLI v2 started sending request checksums by default, which R2's endpoint can reject. Both are one-line fixes that take an afternoon to find, because the error surfaces as a generic upload failure.

4. A dump that is not verified is not a backup

The step that costs nothing and matters most:

test -s "$FILE"
pg_restore --list "$FILE" > /dev/null

The first line catches an empty file. The second asks pg_restore to read the archive's table of contents, which catches a truncated or corrupt archive before it is uploaded. A backup job that uploads whatever fell out of pg_dump is a job that can be green for six months and useless on the one day you need it.

Three smaller decisions worth copying

The workflow never deletes anything. Retention is an R2 lifecycle rule on the prefix. Deleting backups from CI is how you lose them to a date-arithmetic bug, and the blast radius of a bad expression is very different when it runs in your bucket configuration versus in a shell loop with credentials.

The cron is at 03:17 UTC, not 03:00. Off-peak, and off the top of the hour, because every other cron on the platform fires at :00.

Concurrency is a queue, not a cancel:

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

Two dumps must never overlap, but cancelling one mid-dump just wastes the run. Let the new one wait.

The credentials are scoped to a separate bucket. A dedicated backups bucket with an R2 token that can only see it means the credentials sitting in GitHub cannot read or delete application assets, and an application-side bug cannot touch backups. A shared bucket with a db-backups/ prefix works, and is a strictly weaker blast radius for no saving.

The part I have not done

Failure alerting is GitHub's default email to repo admins on a failed scheduled workflow. That is genuinely enough for one nightly job, and pretending otherwise would mean building a notification path I would then have to maintain. What I would not accept is silence.

The upgrade path is written down rather than assumed: the managed daily backups on the paid tier, and point-in-time recovery as an add-on above that. Even after upgrading, this job stays. It is an independent, downloadable copy that lives outside the database provider's account, and "our backups and our database are in the same account" is a sentence worth never saying.

See it for yourself. You cannot inspect someone else's backups, which is the point of them. What you can read is the claim they support: the Data Security section of cogniprep.app/privacy lists encrypted database storage alongside the rest of the posture, and the retention statements elsewhere on that page are only meaningful if a restore is actually possible. A backup job is the implementation detail behind a privacy policy sentence, and the verification step above is the difference between that sentence being true and being aspirational.