Managed databases
Kofi’s app runs on a platform with a managed Postgres 17 on Neon. There’s no server of his own to run a container on. So a scheduled GitHub Actions workflow takes the backups, and his laptop runs the drills.
-
On his laptop, set everything up and check it once.
Terminal window mkdir ~/backups/kofi-app && cd ~/backups/kofi-apppgb initpgb keygen # private key -> password manager and this .envIn
.env:DATABASE_URLis a direct connection, ending in?sslmode=require:- Neon: the host without
-pooler. - Supabase: the Session pooler string, port 5432 (see below).
- Neon: the host without
PG_MAJOR=17, the R2 settings andAGE_RECIPIENTS. On this laptop, also uncommentAGE_IDENTITYwith the private key.
Terminal window pgb doctor # all ✓: his laptop can reach Neon and R2, and the keys matchpgb backup && pgb drill # the whole round trip, once, by hand -
In a private GitHub repo, copy
examples/nightly-backup.ymlto.github/workflows/. Set the image tag (pg17) and the prefix. Then add these secrets under Settings → Secrets and variables → Actions:DATABASE_URLAGE_RECIPIENTS, the public key onlyR2_ENDPOINTandR2_BUCKETR2_ACCESS_KEY_IDandR2_SECRET_ACCESS_KEYHEARTBEAT_URL(and optionallyHEARTBEAT_FAIL_URL)
-
Run it once by hand: Actions → nightly backup → Run workflow. Back on the laptop,
pgb listshows the new backup, andpgb verifyopens it.
From then on, GitHub backs up every night. A failed run emails him, and the heartbeat tells him if the schedule ever stops.
The workflow
Section titled “The workflow”The core of it:
on: schedule: - cron: "17 2 * * *"jobs: backup: runs-on: ubuntu-24.04 steps: - env: DATABASE_URL: ${{ secrets.DATABASE_URL }} AGE_RECIPIENTS: ${{ secrets.AGE_RECIPIENTS }} # the PUBLIC key only S3_ENDPOINT: ${{ secrets.R2_ENDPOINT }} S3_BUCKET: ${{ secrets.R2_BUCKET }} S3_PREFIX: myapp AWS_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }} AWS_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }} run: | docker run --rm -e DATABASE_URL -e AGE_RECIPIENTS -e S3_ENDPOINT -e S3_BUCKET \ -e S3_PREFIX -e AWS_ACCESS_KEY_ID -e AWS_SECRET_ACCESS_KEY \ ghcr.io/iotabyt/pgbackup-r2:pg17 backup- Only the public key goes in the secrets. The workflow can write backups but never read them. The private key stays in the password manager and on the laptop.
-e NAMEwith no value passes the variable through from the step’s environment, so secrets never appear on a command line.- Set a heartbeat. GitHub disables scheduled workflows in repos with no activity for 60 days, and scheduled runs can start some minutes late. A heartbeat notices when backups stop.
The database must accept outside connections
Section titled “The database must accept outside connections”GitHub’s runners connect from changing addresses all over Azure, so an IP allowlist won’t let them in unless it allows them all.
- Neon: use the direct host, without
-pooler.pg_dumpcan’t work through a transaction-mode pooler. - Supabase: the direct host (
db.<ref>.supabase.co) is IPv6-only, and GitHub’s runners have no IPv6. Use the Session pooler connection string (port 5432), not the Transaction pooler (port 6543). - Crunchy Bridge, RDS and others: the database needs a public endpoint with TLS, and a user that can read every table.
Monthly drills: prove a backup restores, every month.
pgbackup-r2 is an open-source tool from Iotabyt. MIT licensed.