Database Backups (rclone / Google Drive)

A daily job dumps the production database and uploads it to Google Drive via rclone, with a 7-day rotating retention. The deploy workflow installs the backup script and registers its cron entry automatically on every deploy — the only manual step is the one-time rclone authorization below.

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

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

Verify end-to-end


# Confirm the cron entry is registered
crontab -l | grep backup-db.sh
 
# Manually trigger a backup right now
~/ranlanka/backup-db.sh
 
# Check recent runs
tail -20 ~/ranlanka/backup.log
 
# Confirm files are landing in Drive
rclone ls gdrive:ranlanka-backups

Re-authorizing an expired 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