Skip to content

Postgres & Upload Backup Runbook

Status: launch-required operations procedure Owners: platform / on-call RPO target: ≤ 24 hours (daily encrypted backup) RTO target: ≤ 4 hours for database restore to a new host

CaseSolo production Postgres lives on the self-hosted Hostinger fleet (hostssh). Object media is on Cloudflare R2 (or the configured S3-compatible bucket). This runbook is the minimum launch story — not a full DR program.

What must be backed up

| Asset | Location | Tooling | |---|---|---| | Application database | Production DATABASE_URL / DIRECT_URL Postgres | pg_dump (custom format) | | Uploaded documents / evidence | R2 / S3 bucket named in env | Encrypted independent backup; S3 versioning where supported | | Schema truth | prisma/migrations/** in git | Already in repo | | Secrets | hostssh app env | Export only through the platform; never commit |

Installed daily database backup

As of 2026-09-05, casesolo-backup.timer runs daily at 03:15 UTC with up to five minutes jitter and persistent catch-up. The installed script is maintained in scripts/ops/casesolo-backup.sh; service/timer definitions are alongside it. The companion media helper runs inside the app image and streams an immutable archive of object bytes plus key/size/checksum manifest. It reads the existing root-owned /etc/restic/r2.env and uploads to the encrypted fleet R2 repository. The first service run and offsite checksum recovery passed; see planning/evidence/casesolo-offsite-recovery-2026-09-05.md.

Inspect with systemctl status casesolo-backup.timer and journalctl -u casesolo-backup.service. A manual run uses systemctl start casesolo-backup.service. Automatic pruning is intentionally absent; retention history, cleanup review and a named failure-response owner remain required. The production media bucket was empty before the drill. A temporary synthetic object was subsequently backed up and restored with matching checksums, then removed from the source bucket. Existing document-record media remains unproven.

In-app backup proof (task-0797)

As of 2026-09-12 the script records an append-only backup_receipts row per run (record_backup_receipt in scripts/ops/casesolo-backup.sh: DB SUCCESS after the db offsite upload, MEDIA SUCCESS after the media offsite upload, DB FAILED when pg_dump fails). Recording is fully guarded and never changes the script's failure semantics — a failed offsite upload still aborts before media with no SUCCESS receipt. The Settings page (admin-only) shows a Backup proof card backed by GET /api/settings/backup-status, which derives fresh/stale/failed/missing from receipt timestamps (fresh ≤ 36h, RPO ≤ 24h): no receipts means the card says "No backups recorded yet" — it never shows a static healthy. The writer activates on the next scheduled host run; verify then that a fresh row appears and the card flips to Receipt fresh. That badge is the job receipt only. It does not name a recovery owner or complete a restore drill.

Daily database backup (manual fallback)

Run from a machine that can reach the private DB (bastion / app box):

# Prefer DIRECT_URL so dumps do not go through a pooler.
export BACKUP_DIR=/var/backups/casesolo
export STAMP=$(date -u +%Y%m%dT%H%M%SZ)
mkdir -p "$BACKUP_DIR"

pg_dump "${DIRECT_URL:-$DATABASE_URL}" \
  --format=custom \
  --no-owner \
  --no-acl \
  --file="$BACKUP_DIR/casesolo-${STAMP}.dump"

# Required before broad launch: encrypt before offsite copy
# gpg --symmetric --cipher-algo AES256 "$BACKUP_DIR/casesolo-${STAMP}.dump"

# Required: ship to a verified independent encrypted destination
# Example only: use the fleet restic target after its access and retention are verified
# restic -r "$RESTIC_REPOSITORY" backup "$BACKUP_DIR/casesolo-${STAMP}.dump"

Retain at least 14 daily and 4 weekly dumps. Delete older local dumps after confirming the offsite copy succeeded.

Restore drill (required before broad launch)

  1. Provision an empty Postgres database (staging or throwaway) with the production extensions available, including pgvector. Plain postgres images cannot restore this dump: the 2026-09-05 drill failed at CREATE EXTENSION vector until the installed pgvector/pgvector:pg18 image was used. Match the production major version for final release proof.
  2. Restore:
pg_restore --clean --if-exists --no-owner --no-acl \
  --dbname="$RESTORE_DATABASE_URL" \
  /path/to/casesolo-YYYYMMDD.dump
  1. Point a staging app at the restored DB (DATABASE_URL + DIRECT_URL).
  2. Smoke:
  1. Record the drill date, duration, and any schema drift in the ops log.

Upload / media recovery

Daily media archives are tagged casesolo-media in Restic. Retrieve the exact archive path from its snapshot using restic dump SNAPSHOT PATH > restored.tar into a protected directory. Verify the archive checksum from the backup log, then manifest.json and every object SHA-256 before restoring original keys to a new bucket. Archive filenames are hashes; original keys are stored in the manifest. Do not overwrite production objects as part of a drill.

  1. Identify the actual object-storage provider. Cloudflare R2 does not implement S3 bucket versioning; do not use a versioning checkbox as R2 recovery evidence. Source: Cloudflare S3 API compatibility, checked 2026-09-05.
  2. For R2, verify an independently retained encrypted backup and restore a representative object from it. A lifecycle policy or same-bucket copy is not proof of recovery from deletion or bucket loss. For AWS S3, record versioning status and test version recovery as an additional protection.
  3. For full-bucket loss, restore from the configured independent backup destination and verify object bytes against the retained checksum. Record the actual destination and receipt; the name gm-fleet-backups alone does not establish that a backup exists.

Launch gate

Broad launch remains blocked until:

Attach receipts to planning/LAUNCH_SIGNOFF_SCORECARD.md.

Source: docs/dev/postgres-backup-runbook.md