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.

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.

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” — the OAuth token expired or was never fully configured. Re-run the tool’s reconnect/re-auth flow.
• “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.