konnosDashboard
briven-backup.sh176 lines · main
1#!/usr/bin/env bash
2# Daily backup of Briven — Doltgres-native (PRIMARY DR) + stock-Postgres pg_dump
3# (secondary rollback). Runs on the France host via systemd
4# (briven-backup.{service,timer}).
5#
6# 2026-08-01 REWORK — why this changed:
7# The old `dolt-backup` SIDECAR looped `dolt_backup('sync-url', …)` over EVERY
8# database with no throttle, against a buggy DoltGres 0.56.6. That contributed
9# to a full-platform outage (engine locked under load). Fix: engine upgraded to
10# 0.57.2 (lock-subsystem fixes) with auto-GC disabled, and backups are now a
11# GENTLE, one-database-at-a-time, throttled pass run from this host job. The
12# sidecar is removed from compose. Proven safe under monitoring (2026-08-01).
13#
14# Backup layers:
15# 1. PRIMARY — `dolt_backup('sync-url', file:///backups/<db>)` for ALL live
16# DoltGres DBs (control + engine + every project) → doltgres_backups
17# volume (/backups). Restorable via `dolt backup restore`.
18# 2. auth.db — snapshot the engine users/grants file (nothing else backs it up).
19# 3. SECONDARY— pg_dump of stock-Postgres briven_control (rollback window helper).
20# 4. OFF-SITE — mirror the doltgres_backups volume to external S3 (Backblaze/R2/…)
21# when BRIVEN_BACKUP_S3_* is configured (see BACKUP-OFFSITE.md).
22#
23# Env (/etc/briven/backup.env, optional):
24# BRIVEN_DOLTGRES_CONTAINER default: briven-brivenfrance-uilsk6-doltgres-1
25# BRIVEN_DOLTGRES_BACKUPS_VOLUME default: briven-brivenfrance-uilsk6_doltgres_backups
26# BRIVEN_DOLTGRES_PASSWORD default: read from the doltgres container env
27# BRIVEN_BACKUP_THROTTLE_SECS default: 8 (pause between DBs — gentleness)
28# BRIVEN_BACKUP_PG_CONTAINER default: briven-brivenfrance-uilsk6-postgres-1
29# BRIVEN_BACKUP_PG_USER default: postgres
30# BRIVEN_BACKUP_PG_DBS default: briven_control (secondary pg_dump)
31# BRIVEN_BACKUP_S3_ENDPOINT/BUCKET/ACCESS_KEY/SECRET_KEY → off-site mirror
32# BRIVEN_BACKUP_LOCAL_RETENTION_DAYS default: 30
33#
34# Exit 1 if the dolt phase fails for any DB OR an off-site mirror fails, so
35# OnFailure=briven-backup-alert.service fires.
36
37set -euo pipefail
38
39BACKUP_ENV_FILE="/etc/briven/backup.env"
40if [ -f "$BACKUP_ENV_FILE" ]; then
41 # shellcheck disable=SC1090
42 source "$BACKUP_ENV_FILE"
43fi
44
45DC="${BRIVEN_DOLTGRES_CONTAINER:-briven-brivenfrance-uilsk6-doltgres-1}"
46BACKUPS_VOLUME="${BRIVEN_DOLTGRES_BACKUPS_VOLUME:-briven-brivenfrance-uilsk6_doltgres_backups}"
47THROTTLE="${BRIVEN_BACKUP_THROTTLE_SECS:-8}"
48PG_CONTAINER="${BRIVEN_BACKUP_PG_CONTAINER:-briven-brivenfrance-uilsk6-postgres-1}"
49PG_USER="${BRIVEN_BACKUP_PG_USER:-postgres}"
50# shellcheck disable=SC2206
51PG_DBS=(${BRIVEN_BACKUP_PG_DBS:-briven_control})
52LOCAL_BACKUP_ROOT="/var/backups/briven"
53LOCAL_RETENTION_DAYS="${BRIVEN_BACKUP_LOCAL_RETENTION_DAYS:-30}"
54
55STAMP="$(date -u +'%Y-%m-%d/%H-%M-%S')"
56STAMP_FLAT="$(date -u +'%Y-%m-%dT%H-%M-%SZ')"
57FAILURES=0
58FAILURE_DETAIL=""
59
60log() { printf '[%s] %s\n' "$(date -u +'%Y-%m-%dT%H:%M:%SZ')" "$*"; }
61die() { log "ERROR: $*"; exit 1; }
62fail() { log "WARN: $*"; FAILURES=$((FAILURES + 1)); FAILURE_DETAIL="${FAILURE_DETAIL:+${FAILURE_DETAIL}; }$*"; }
63
64# --- resolve doltgres password without printing it ---
65dolt_password() {
66 if [ -n "${BRIVEN_DOLTGRES_PASSWORD:-}" ]; then
67 printf '%s' "$BRIVEN_DOLTGRES_PASSWORD"; return 0
68 fi
69 docker inspect "$DC" --format '{{range .Config.Env}}{{println .}}{{end}}' 2>/dev/null \
70 | sed -n 's/^DOLTGRES_PASSWORD=//p' | head -1
71}
72
73# ===== PHASE 1: PRIMARY — gentle DoltGres-native backup of every live DB =====
74dolt_backup_all() {
75 docker inspect "$DC" >/dev/null 2>&1 || die "doltgres container not found: ${DC}"
76 local pw; pw="$(dolt_password)"
77 [ -n "$pw" ] || die "could not resolve DOLTGRES_PASSWORD"
78 local base="postgres://postgres:${pw}@127.0.0.1:5432"
79
80 log "dolt phase: enumerating live databases"
81 local dbs ok=0
82 dbs="$(docker exec "$DC" sh -lc \
83 "psql \"${base}/postgres?sslmode=disable\" -tAc \"select datname from pg_database where datname not in ('template0','template1','postgres')\"" \
84 2>/dev/null || true)"
85 [ -n "$dbs" ] || die "no databases enumerated (is doltgres serving?)"
86
87 for db in $dbs; do
88 # sync-url writes a restorable dolt archive to the server's /backups/<db>.
89 if docker exec "$DC" sh -lc \
90 "psql \"${base}/${db}?sslmode=disable\" -tAc \"select dolt_backup('sync-url','file:///backups/${db}')\"" \
91 >/dev/null 2>&1; then
92 log " ok dolt backup: ${db}"
93 ok=$((ok + 1))
94 else
95 fail "dolt backup failed: ${db}"
96 fi
97 sleep "$THROTTLE" # gentleness — never hammer the engine
98 done
99 log "dolt phase: ${ok} database(s) backed up (throttle=${THROTTLE}s)"
100
101 # auth.db snapshot — engine users/grants; keep newest 14.
102 if docker exec "$DC" test -f /var/lib/doltgres/auth.db 2>/dev/null; then
103 if docker exec "$DC" sh -lc \
104 "mkdir -p /backups/auth-db && cp /var/lib/doltgres/auth.db /backups/auth-db/auth.db.${STAMP_FLAT} && ls -1t /backups/auth-db | tail -n +15 | while read -r f; do rm -f \"/backups/auth-db/\$f\"; done" \
105 >/dev/null 2>&1; then
106 log " ok auth.db snapshot"
107 else
108 fail "auth.db snapshot failed"
109 fi
110 fi
111}
112
113# ===== PHASE 2: SECONDARY — pg_dump of stock-Postgres (rollback window) =====
114pg_dump_secondary() {
115 docker inspect "$PG_CONTAINER" >/dev/null 2>&1 || { log "pg secondary: container ${PG_CONTAINER} absent — skipping"; return 0; }
116 for db in "${PG_DBS[@]}"; do
117 local dir="${LOCAL_BACKUP_ROOT}/${db}/${STAMP%/*}"
118 local file="${dir}/${STAMP##*/}.dump.gz"
119 mkdir -p "$dir"
120 if docker exec "$PG_CONTAINER" pg_dump --username="$PG_USER" --format=custom --compress=0 \
121 --no-owner --no-privileges "$db" 2>/dev/null | gzip -9 > "$file"; then
122 log " ok pg_dump ${db}: $(stat -c%s "$file") bytes"
123 else
124 rm -f "$file"; fail "pg_dump failed: ${db}"
125 fi
126 done
127}
128
129# ===== PHASE 3: OFF-SITE — mirror the dolt backups volume to external S3 =====
130offsite_mirror() {
131 if [ -z "${BRIVEN_BACKUP_S3_ENDPOINT:-}" ] || [ -z "${BRIVEN_BACKUP_S3_BUCKET:-}" ] \
132 || [ -z "${BRIVEN_BACKUP_S3_ACCESS_KEY:-}" ] || [ -z "${BRIVEN_BACKUP_S3_SECRET_KEY:-}" ]; then
133 log "off-site mirror skipped (BRIVEN_BACKUP_S3_* unset) — see BACKUP-OFFSITE.md"
134 return 0
135 fi
136 local ep="${BRIVEN_BACKUP_S3_ENDPOINT#https://}"; ep="${ep#http://}"
137 local vol="/var/lib/docker/volumes/${BACKUPS_VOLUME}/_data"
138 [ -d "$vol" ] || { fail "off-site: backups volume path missing: ${vol}"; return 0; }
139 log "off-site mirror → s3://${BRIVEN_BACKUP_S3_BUCKET}/doltgres-backups/"
140 if docker run --rm -v "${vol}:/backups:ro" \
141 -e "MC_HOST_off=https://${BRIVEN_BACKUP_S3_ACCESS_KEY}:${BRIVEN_BACKUP_S3_SECRET_KEY}@${ep}" \
142 --entrypoint sh minio/mc:latest \
143 -c "mc mirror --overwrite --remove /backups off/${BRIVEN_BACKUP_S3_BUCKET}/doltgres-backups/" >/dev/null 2>&1; then
144 log "off-site mirror ok"
145 else
146 fail "off-site mirror failed"
147 fi
148 if [ -d "$LOCAL_BACKUP_ROOT" ]; then
149 docker run --rm -v "${LOCAL_BACKUP_ROOT}:/pgd:ro" \
150 -e "MC_HOST_off=https://${BRIVEN_BACKUP_S3_ACCESS_KEY}:${BRIVEN_BACKUP_S3_SECRET_KEY}@${ep}" \
151 --entrypoint sh minio/mc:latest \
152 -c "mc mirror --overwrite /pgd off/${BRIVEN_BACKUP_S3_BUCKET}/pg-dumps/" >/dev/null 2>&1 \
153 || fail "off-site mirror (pg dumps) failed"
154 fi
155}
156
157prune_local() {
158 [ -d "$LOCAL_BACKUP_ROOT" ] || return 0
159 log "pruning local pg dumps older than ${LOCAL_RETENTION_DAYS}d"
160 find "$LOCAL_BACKUP_ROOT" -type f -name '*.dump.gz' -mtime +"$LOCAL_RETENTION_DAYS" -delete || true
161 find "$LOCAL_BACKUP_ROOT" -type d -empty -delete || true
162}
163
164log "briven backup run starting (doltgres=${DC}, throttle=${THROTTLE}s)"
165dolt_backup_all # primary DR
166pg_dump_secondary # secondary rollback
167offsite_mirror # off-site (if configured)
168prune_local
169
170if [ "$FAILURES" -gt 0 ]; then
171 log "ERROR: ${FAILURES} failure(s): ${FAILURE_DETAIL}"
172 { echo "failures=${FAILURES}"; echo "detail=${FAILURE_DETAIL}"; } > /run/briven-backup-status
173 exit 1
174fi
175rm -f /run/briven-backup-status
176log "briven backup run complete — all layers ok"