Database Backups (Google Drive & Cloudflare R2)

A daily job dumps the production database and uploads it to Google Drive (via rclone) and Cloudflare R2 (via AWS CLI S3), each with a 7-day rotating retention. The upload stages run independently — so a failure in Google Drive (e.g. OAuth token expiry) will never prevent the Cloudflare R2 backup from succeeding.

This page covers setup. For restore steps and common failure symptoms, see Database Backups troubleshooting.

Failure alerts (Slack)

The backup script catches any failure — pg_dump failing, an empty dump, or an upload failing on either Google Drive or Cloudflare R2 — and posts a message to a Slack incoming webhook if one is configured. Without it, failures are still logged to backup.log but nothing actively notifies anyone.

Create a Slack incoming webhook

In Slack: create (or reuse) an app with an Incoming Webhook enabled for the channel you want alerts in, and copy the webhook URL (https://hooks.slack.com/services/...). Treat this URL as a secret — anyone with it can post into that channel.

Add it as a GitHub Actions secret

Repo → Settings → Secrets and variables → Actions → New repository secret, name SLACK_WEBHOOK_URL, value is the webhook URL. The deploy workflow reads this secret and bakes SLACK_WEBHOOK_URL=... directly into the cron line it registers — so once the secret exists, every future deploy keeps alerting wired up automatically, with no manual server-side step.

(Only if you can’t redeploy right now) wire it in manually


crontab -e

Edit the existing backup-db.sh line to prefix the command with the env var, e.g.:


0 18 * * * SLACK_WEBHOOK_URL=https://hooks.slack.com/services/... /home/ubuntu/ranlanka/backup-db.sh >> /home/ubuntu/ranlanka/backup.log 2>&1

This manual edit is only a stopgap — the next deploy will overwrite the cron line with whatever the SLACK_WEBHOOK_URL GitHub secret currently produces. Add the GitHub secret too, or this will silently revert.

Naming & rotation

Backup files are named by ISO day-of-week (backup-day1.sql.gz through backup-day7.sql.gz), not by date — so day 8’s upload naturally overwrites day 1’s file. This gives a fixed 7-day rolling window with no separate pruning job: the upload step is the pruning step.

The cron entry itself runs once daily (commonly scheduled for a low-traffic window in your local timezone, converted to the server’s UTC cron time).

One-time setup: rclone + Google Drive

This part is interactive (Google OAuth) and can’t be scripted by CI — do it once, by hand, after the first deploy.

Install rclone on the server


curl https://rclone.org/install.sh | sudo bash

Configure the Google Drive remote


rclone config

Create a new remote named exactly gdrive, choose Google Drive as the storage type. Leave client_id/client_secret blank for now (see “use your own OAuth client” below for the production-correct version), scope drive (full access), leave root_folder_id and service_account_file blank, decline advanced config, and decline auto-config (the server is headless, no browser).

At this point it prints an rclone authorize "drive" "..." command. Copy that exact line (yours will have a different token) and run it on a machine with a browser — not the server:


# Install rclone locally too, then:
rclone authorize "drive" "<paste the token from above>"

This opens your browser, you log into the Google account backups should go to, grant access, and the terminal prints a token blob. Copy that whole blob back into the server’s config_token> prompt.

Answer n to “Configure as a Shared Drive?” (unless you specifically want one), then y to keep the remote.

Confirm it’s working


rclone lsd gdrive:
rclone about gdrive:

Empty output from lsd alone before the first backup has run is expected — the destination folder is created automatically on first upload. about printing real quota/usage numbers confirms auth is genuinely working (rather than just “not erroring”).

Use your own OAuth client, not rclone’s shared one

Leaving client_id/client_secret blank uses rclone’s own shared Google API credentials — providers periodically retire shared developer credentials used by CLI tools (rclone lsd gdrive: prints a notice once this happens), which silently breaks scheduled uploads with no other warning. Set up your own:

Enable the Drive API on a GCP project


gcloud auth login <your-account>@gmail.com
gcloud projects list
gcloud config set project <PROJECT_ID>
gcloud services enable drive.googleapis.com --project=<PROJECT_ID>

In the Console UI (one-time, a couple of minutes): User type External, any app name, add the Drive scope matching your remote’s configured scope, and add the backup Google account as a test user. Save.

Create an OAuth client ID

Console UI → Credentials → Create Credentials → OAuth client ID → Application type Desktop app. Copy the Client ID and Client Secret it shows you.

Console UI → OAuth consent screen → Publishing status → Publish App — do this even though the app is unverified.

Leaving the app in “Testing” status makes Google auto-expire the refresh token after 7 days, which silently breaks the backup cron a week later with no warning until the log shows an auth failure. Publishing an unverified app just adds a one-time “unverified app” click-through warning on the next re-authorization — harmless for a single-user internal script.

Re-configure the remote with real credentials

Back on the server:


rclone config
# e (edit) -> gdrive -> walk through the same prompts as before,
# but paste the real client_id / client_secret this time
# n (Shared Drive?) -> y (keep this remote)

Confirm the retirement warning is gone:


rclone lsd gdrive:   # no notice about a shared/retiring client = success

Cloudflare R2 Backup Setup

To enable secondary backups to Cloudflare R2, provide the following environment variables in your server’s .env or crontab:


R2_ACCOUNT_ID="your-cloudflare-account-id"
R2_ACCESS_KEY_ID="your-r2-access-key-id"
R2_SECRET_ACCESS_KEY="your-r2-secret-access-key"
R2_BUCKET_NAME="ranlanka-crm"

The backup script automatically uses the AWS CLI S3 endpoint to stream backups to s3://ranlanka-crm/backups/postgres/ranlanka-db-backup-day{1-7}.sql.gz.

Verify end-to-end


# Confirm the cron entry is registered (and, if configured, includes SLACK_WEBHOOK_URL)
crontab -l | grep backup-db.sh
 
# Manually trigger a backup right now
SLACK_WEBHOOK_URL=<your-webhook-url> ~/ranlanka/backup-db.sh
 
# Check recent runs
tail -20 ~/ranlanka/backup.log
 
# Confirm files are landing in Google Drive
rclone ls gdrive:ranlanka-backups
 
# Confirm files are landing in Cloudflare R2
aws s3 ls s3://ranlanka-crm/backups/postgres/ \
  --endpoint-url "https://${R2_ACCOUNT_ID}.r2.cloudflarestorage.com"

A successful run prints “Backup complete” and posts nothing to Slack — alerts only fire on failure. To confirm the alert path itself works without touching real data, force a failure on purpose (e.g. point DB_CONTAINER at a container that doesn’t exist) and confirm a Slack message lands:


SLACK_WEBHOOK_URL=<your-webhook-url> DB_CONTAINER=does-not-exist ~/ranlanka/backup-db.sh
echo $?   # non-zero confirms the script actually failed, not just logged a warning

Re-authorizing an expired Google Drive remote

Same interactive flow as initial setup, targeting the existing remote instead of creating a new one:


rclone config reconnect gdrive:
# prints a fresh "rclone authorize ..." line if needed - run it on a
# machine with a browser, paste the resulting token back