Database Backups

Backup vs. restore are not the same tool

A backup script only ever writes — it dumps the database and uploads the result; nothing about that process reads it back. Restoring is a manual, separate step, run against whatever target database you choose. Treat restore as deliberately manual and un-automated, since it’s a destructive operation against whatever connection you point it at.

Format matters — the most common restore mistake

A plain pg_dump with no explicit format flag produces plain-text SQL by default, not a binary archive format. That means:

• The output is a literal .sql script of CREATE TABLE/INSERT/etc. statements (commonly gzipped for storage).
• It must be restored with psql (or a SQL-script-execution tool), never a binary-archive restore tool — a binary-archive tool only understands the binary formats and fails immediately with something like “did not find magic string in file header” on a plain-text file, even after decompressing it correctly. That error means “wrong tool for this file’s format,” not “the file is corrupted.”

To restore from the command line:

psql --host=<target-host> --port=5432 --username=<user> --dbname=<target-db> -f backup.sql

Restoring into a non-empty database

If the dump wasn’t created with clean/drop guards, replaying it against a database that already has these tables will fail with “relation already exists” for every table. Either restore into an empty database/schema, or manually drop the conflicting objects first.

Recovering same-day data after restoring an older backup

A common scenario: the database fails partway through a day, after new rows were already created since the last backup ran. Restoring the most recent backup brings back everything up to that point, but not the hours of data added afterward — and naively replaying the backup on top of the current (damaged but still-populated) database doesn’t work either, for the same “relation already exists” / duplicate-row reason described above. This is a general property of plain, non---clean dumps, not specific to any one table or app.

The reliable approach is to restore into a separate, empty target first, then reconcile — never restore directly onto a live database that already has rows:

SELECT * FROM <table> WHERE created_at > '<backup timestamp>';

Rolling backups without separate pruning

A simple, low-maintenance rotation pattern: name each day’s backup file by day-of-week (not by date), so uploading day 8’s backup naturally overwrites day 1’s file at the storage destination. This gives a fixed rolling window (e.g. 7 days) with no separate cleanup/pruning job needed — the upload step is the pruning step.

How you find out about a failure

The backup script posts to a Slack webhook (SLACK_WEBHOOK_URL, see setup) whenever it fails, so an outage surfaces the same day instead of being discovered by manually eyeballing file timestamps in Drive. This is exactly how a real incident was found in practice: rclone’s OAuth token expired (invalid_grant: maybe token expired?) and the upload step failed silently for a full week before anyone noticed — the day-of-week rotation made the gap easy to miss, since a stale slot still looks like a normal, just-older file rather than an obviously-missing one. If SLACK_WEBHOOK_URL isn’t configured, backup.log still records every failure, but nothing actively pages anyone.

OAuth-based cloud storage uploads: common failure signatures

If a backup script uploads to a cloud storage provider via an OAuth-based CLI tool:

• “Command not found” in the backup log — the upload tool was never installed on the host; the cron entry exists but every run fails at the upload step.
• Auth error / “couldn’t fetch token” / invalid_grant: maybe token expired? — the OAuth token expired or was never fully configured. Most commonly this means the OAuth consent screen is still in Testing status, which makes Google auto-expire the refresh token after exactly 7 days regardless of how often the script runs successfully in between — publish the app (see setup) rather than just reconnecting, or the same failure recurs on the same weekly cadence. Re-run the tool’s reconnect/re-auth flow (rclone config reconnect gdrive:) to restore uploads immediately either way.
• “Access blocked” / “developer-approved testers only” during the authorize step — the OAuth consent screen exists but the account doing the authorizing isn’t on its allowed testers list (or a different account than expected is logged into the browser during the interactive auth step).
• Only some of the expected rotation slots ever appear — each dated/named slot is only created the first time that slot’s backup actually succeeds; this is expected until a full rotation cycle has completed at least once, not a bug.