Koder ID Postgres — backup & restore runbook

Backup chain

Daily logical backups of all koder-id Postgres databases live in two places:

1. Inside the id LXC at `varbackupskoder-id-pgkoder-id-pg-

   YYYY-MM-DDHH-MM.sql.gz. Written by a pgdumpall --clean --if-exists | gzip -9 pipeline driven by a systemd timer (koder-id-pg-backup.timer`, daily at 02:00 UTC + ≤5 min jitter). In-LXC retention: last 30 dailies.

2. On s.khost1 host at /srv/backup/koder-id-pg/. Pulled from

   the LXC by koder-id-pg-rsync.timer (daily at 02:30 UTC + ≤5 min jitter). Host retention: 30 dailies + 12 month-firsts + 2 year-firsts (date pattern in the filename drives the keep/drop decision).

Restore

Full restore (disaster: id LXC gone)

# 1. On a fresh PG instance (or the same id LXC after reprovisioning):
sudo -u postgres psql -c "CREATE ROLE koder_id WITH LOGIN PASSWORD '…' CREATEDB"

# 2. Pull the latest dump from s.khost1:
scp root@s.khost1:/srv/backup/koder-id-pg/koder-id-pg-2026-05-28_02-00.sql.gz /tmp/

# 3. Restore. `--clean --if-exists` in pg_dumpall means the dump
# itself drops + recreates every database, so this is idempotent.
gunzip -c /tmp/koder-id-pg-*.sql.gz | sudo -u postgres psql

# 4. Confirm. The 8 koder_id_* databases should exist with their
# tables and rows.
sudo -u postgres psql -l | grep koder_id_

Selective restore (one database)

pg_dumpall writes a single SQL file with all databases. To restore just one (e.g. lost the workspaces table by a botched migration):

gunzip -c koder-id-pg-2026-05-28_02-00.sql.gz \
  | sed -n '/\\connect koder_id_identity/,/\\connect koder_id_auth/p' \
  | sudo -u postgres psql -d koder_id_identity_recovery

Then psql into the running koderididentity and copy the specific table from the _recovery database.

Point-in-time recovery (PITR)

Not configured. Logical dumps lose anything after the last 02:00 UTC snapshot — worst case 24h of data loss. For PITR, switch to physical replication (pg_basebackup + WAL archiving) when traffic justifies it. See engine#184 notes for the trade-off.

Restore test (weekly)

Manual for now; automation is a future engine ticket. Recipe:

# On a spare LXC or container with PG17:
gunzip -c /srv/backup/koder-id-pg/koder-id-pg-$(date -u -d 'yesterday' +%Y-%m-%d)_02*.sql.gz | psql

# Then run the identity service against it briefly to confirm
# migrations don't re-run (they should already be applied):
KODER_ID_STORAGE_BACKEND=kdb1 POSTGRES_DSN=postgres://...:5432/koder_id_identity \
  /usr/local/bin/koder-id-identity 2>&1 | grep migration | head -5
# Expect: zero migration-applied lines (idempotent run).

Files

Monitoring

journalctl -t koder-id-pg-backup (in-LXC) and journalctl -t koder-id-pg-rsync (host) log size + count after each run. A failed run leaves the systemd unit in failed state; surface via the standard "unit failed" alert path in infra/observe.