Blame · Line-by-line history

`BACKUP_RESTORE_DRILL.md`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

BACKUP_RESTORE_DRILL.mdBlame92 lines · 1 contributor

Normal view Raw

1e79799CC LABS App	1	# Backup Restore Drill
	2
	3	Proves the backup pipeline actually works — dumps prod, restores to a
	4	scratch DB, runs a sanity query, reports green/red. Run this BEFORE the
	5	first customer trusts gluecron with their repos. Then run it monthly
	6	from cron so the drill is rehearsed continuously, not theoretically.
	7
	8	## Pre-reqs
	9
	10	- `pg_dump` and `psql` on PATH (`apt-get install -y postgresql-client` on the box)
	11	- `DATABASE_URL` pointing at the prod DB (Neon)
	12	- `SCRATCH_DATABASE_URL` pointing at a writable empty DB (Neon branch is
	13	ideal; can also be a local Postgres for the drill)
	14	- Disk space for the dump (a few hundred MB once gluecron has real users)
	15
	16	## Run it
	17
	18	```sh
	19	docker compose exec gluecron bash scripts/backup-restore-drill.sh
	20	```
	21
	22	Or manually from anywhere with `psql`:
	23
	24	```sh
	25	DATABASE_URL="postgresql://...prod..." \
	26	SCRATCH_DATABASE_URL="postgresql://...scratch..." \
	27	bash scripts/backup-restore-drill.sh
	28	```
	29
	30	The script:
	31
	32	1. `pg_dump --format=custom` the prod DB into `/tmp/gluecron-drill-<ts>.dump`
	33	2. Drops + recreates `users` (and a few other key tables) in the scratch DB
	34	3. `pg_restore` the dump into scratch
	35	4. Runs verification queries:
	36	- `SELECT COUNT(*) FROM users;` matches prod
	37	- `SELECT COUNT(*) FROM repositories;` matches prod
	38	- `SELECT COUNT(*) FROM site_admins;` matches prod
	39	- Schema row count from `information_schema.tables` matches prod
	40	5. Reports PASS/FAIL per check and a summary at the end
	41	6. Cleans up the dump file unless `--keep-dump` is passed
	42
	43	A failed drill is a paging-grade alert. The most common cause is a
	44	schema drift between the dump and the restore DB — fix by either
	45	resetting scratch (`DROP DATABASE; CREATE DATABASE`) or by running
	46	`bun run db:migrate` on it before restoring.
	47
	48	## Cron it
	49
	50	Monthly is enough for the v1 user base. Add this to the box's crontab
	51	(remember to keep the env file accessible):
	52
	53	```cron
	54	# At 03:00 on day-of-month 1, run the backup-restore drill, log the result.
	55	0 3 1 * * cd /opt/gluecron && /usr/bin/env bash scripts/backup-restore-drill.sh >> /var/log/gluecron/drill.log 2>&1
	56	```
	57
	58	Then scrape the log via the existing alerting rule
	59	(`infra/alerts/gluecron.rules.yml`) by adding a once-monthly
	60	`drill_last_success_seconds` metric — the script writes a tiny
	61	`/var/lib/gluecron/drill-last-success` timestamp file when it passes
	62	which you can convert to a metric via node_exporter's textfile collector.
	63
	64	## What "green" looks like
	65
	66	```
	67	[drill 2026-05-13 03:00:01] dumping prod DB ...
	68	[drill 2026-05-13 03:00:08] dump complete: 47 MB at /tmp/gluecron-drill-1715569201.dump
	69	[drill 2026-05-13 03:00:08] restoring into scratch ...
	70	[drill 2026-05-13 03:00:31] restore complete
	71	[drill 2026-05-13 03:00:31] verification:
	72	PASS users count prod=128 scratch=128
	73	PASS repositories count prod=412 scratch=412
	74	PASS site_admins count prod=2 scratch=2
	75	PASS schema tables prod=96 scratch=96
	76	[drill 2026-05-13 03:00:32] All checks passed. Took 31s.
	77	```
	78
	79	## What "red" looks like
	80
	81	```
	82	FAIL users count prod=128 scratch=0
	83	```
	84
	85	=> the restore didn't actually populate. Re-run with `--verbose` and check
	86	`pg_restore` output. Usually a permissions issue on the scratch DB.
	87
	88	```
	89	FAIL schema tables prod=96 scratch=84
	90	```
	91
	92	=> schema drift. Run `bun run db:migrate` against scratch, then re-run.