A reusable daily backup approach, extracted from CalDave (scripts/backup.sh). Verified working in production: 31 consecutive daily backups, test-restored successfully on 2026-09-09.

The shape

Cron inside the app container runs a script that dumps Postgres, verifies the dump, ships it to S3-compatible storage, prunes old copies, and deletes the local file. No backup service, no extra infrastructure -- about 100 lines of bash.

cron (04:00 UTC) -> pg_dump | gzip -> verify -> aws s3 cp -> prune >30d -> rm local

Three non-obvious pieces

1. Cron doesn't inherit the container's environment

This is the thing that silently breaks. The entrypoint dumps the vars cron will need to a file, and the crontab sources it:

# entrypoint.sh, before starting cron
env | grep -E '^(AWS_|BUCKET_|POSTGRES_)' > /etc/environment
cron

# crontab line
0 4 * * * . /etc/environment; /app/scripts/backup.sh >> /data/backup.log 2>&1

2. Dump over the unix socket as the postgres user

Avoids passwords entirely if pg_hba.conf uses trust for local connections:

su postgres -c "pg_dump mydb" | gzip > "$BACKUP_FILE"

3. Verify before uploading, and clean up unconditionally

A truncated dump that uploads "successfully" is worse than no backup, because it looks fine in the log:

trap 'rm -f "$BACKUP_FILE"' EXIT     # runs on success AND failure

[ -s "$BACKUP_FILE" ] || { echo "ERROR: empty"; exit 1; }
gzip -t "$BACKUP_FILE" || { echo "ERROR: corrupt"; exit 1; }

The trap matters on a small volume -- without it, a failure mid-upload leaves multi-hundred-MB files behind until the disk fills.

Pruning

Timestamp the filename (app-YYYYMMDD-HHMMSS.sql.gz), then compare the embedded date against a cutoff. String comparison on YYYYMMDD sorts correctly, so no date parsing is needed:

CUTOFF_DATE=$(date -u -d "30 days ago" +%Y%m%d)
aws s3 ls "s3://$BUCKET_NAME/daily/" --endpoint-url "$AWS_ENDPOINT_URL_S3" \
  | awk '{print $4}' \
  | while read f; do
      d=$(echo "$f" | sed -n 's/app-\([0-9]\{8\}\)-.*/\1/p')
      [ -n "$d" ] && [ "$d" -lt "$CUTOFF_DATE" ] && aws s3 rm "s3://$BUCKET_NAME/daily/$f"
    done

Pruning is best-effort with || true -- a failed delete shouldn't fail an otherwise successful backup.

To reuse it

1. Copy scripts/backup.sh into the new project.
2. Swap the database name and the filename prefix.
3. Set BUCKET_NAME, AWS_ENDPOINT_URL_S3, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY.
4. Add the crontab line and the /etc/environment write to your entrypoint.

On Fly.io, fly storage create sets all four AWS secrets automatically. Any S3-compatible store works by changing the endpoint.

Verifying a backup actually restores

"The log says success" is not verification. Restore into a scratch database and compare row counts against live:

# 1. Pull the dump and check integrity
aws s3 cp "s3://$BUCKET_NAME/daily/app-YYYYMMDD-HHMMSS.sql.gz" . \
  --endpoint-url "$AWS_ENDPOINT_URL_S3"
gzip -t app-YYYYMMDD-HHMMSS.sql.gz

# 2. Restore into a throwaway database
su postgres -c "createdb restoretest"
gunzip -c app-YYYYMMDD-HHMMSS.sql.gz | psql -q restoretest

# 3. Compare against live, then clean up
psql restoretest -c "SELECT count(*) FROM your_main_table;"
psql mydb       -c "SELECT count(*) FROM your_main_table;"
su postgres -c "dropdb restoretest"

Expect the restored counts to be slightly lower than live -- that's the activity since the snapshot. Identical counts on a busy app are suspicious.

Worth doing once per project, and worth scripting if the data matters.

Limitations to know before relying on this

• Daily snapshots only. Worst-case data loss is ~24 hours. There is no WAL archiving and no point-in-time recovery. For anything where a day of loss is unacceptable, this pattern is the wrong starting point.
• Retention is a hard floor. At 30 days, a problem introduced and not noticed within a month has no recovery point.
• Same-host database. If Postgres runs inside the app VM, recovery from a lost volume is a full rebuild plus restore, not a failover. The offsite copy in S3 is what makes this survivable at all.

To narrow the loss window cheaply, run the cron more often (e.g. 0 */6 * * *). Retention is a one-line change to the cutoff.