1# =============================================================================
2# AI Software Factory — PostgreSQL daily base backup (CronJob, 05:00 UTC)
3#
4# Part of the TASK-0135 backup/DR deliverable. Pairs with:
5# - postgres/statefulset.yaml (the PITR StatefulSet; WAL archiving runs
6# continuously via Postgres archive_command — see postgres/configmap.yaml)
7# - postgres/walg-iam-policy.yaml (least-priv S3 identity)
8#
9# What this Job does each run (0 5 * * * UTC):
10# 1. Stage the pinned wal-g v3.0.7 binary into an emptyDir (so the command
11# is stable across image bumps — the binary is NOT baked into the
12# postgres:16-alpine image).
13# 2. Wait for the live DB (pg_isready).
14# 3. `wal-g backup-push` in ONLINE STREAMING mode (PGHOST=factory-db, no
15# local data dir — verified against wal-g v3 PostgreSQL.md: "To stream
16# the backup data, leave out the data directory … use PGHOST or
17# --pghost"). This is a consistent physical base backup taken over
18# streaming replication; it needs no pg_basebackup and no maintenance
19# window.
20# 4. `wal-g wal-verify integrity timeline` — the "is the archive
21# actually restorable" sanity the runbook's drill relies on. VERIFIED
22# against cmd/pg/wal_verify.go: at least one check is REQUIRED
23# (integrity = segment hash check, timeline = continuity check); a
24# bare `wal-verify` exits with an argument error.
25# 5. Row-count sanity: a "successful" base backup of an empty DB must not
26# pass silently (pg_class count > 0).
27#
28# No marker object is written (TASK-0135 design): scripts/backup-health.ts
29# derives "last base backup < 26 h" from the newest object LastModified
30# under pg/basebackups_005/ — the base backup directory wal-g writes
31# (finishing with its *_backup_stop_sentinel.json) IS the marker. A
32# synthetic marker could diverge from the artifact and be a false signal
33# (or false alarm) either way.
34#
35# Retention: this Job does NOT delete old backups. `wal-g delete retain
36# FULL 7` (7 daily fulls, or `--keep-forever` on a marked one) is a
37# MANUAL / monthly operation in the runbook (§8.6) so an accidental
38# automation cannot destroy the PITR chain.
39#
40# wal-g v3 env (verified, see statefulset.yaml header): WALG_S3_PREFIX
41# (s3:
42# AWS_ACCESS_KEY_ID/AWS_SECRET_ACCESS_KEY. This Job sets them from the SAME
43# app config/secret sources the StatefulSet uses, so the base backup and the
44# WAL archive land in the one storage root PITR restore requires.
45# =============================================================================
46apiVersion: batch/v1
47kind: CronJob
48metadata:
49 name: pg-backup
50 labels:
51 app.kubernetes.io/name: postgres
52 app.kubernetes.io/component: backup
53 app.kubernetes.io/part-of: ai-software-factory
54spec:
55 schedule: "0 5 * * *" # 05:00 UTC daily (the release-pipeline
56 # backup-health gate expects < 26 h since
57 # the last success — one failed day still
58 # leaves ~24 h of margin).
59 timeZone: "UTC"
60 concurrencyPolicy: Forbid # never overlap a long backup with the next
61 successfulJobsHistoryLimit: 3
62 failedJobsHistoryLimit: 5
63 jobTemplate:
64 spec:
65 backoffLimit: 2
66 activeDeadlineSeconds: 3600 # 1 h budget; a base backup should be far
67 # faster (RTO budget is 1 h total)
68 template:
69 metadata:
70 labels:
71 app.kubernetes.io/name: postgres-backup
72 app.kubernetes.io/component: backup
73 app.kubernetes.io/part-of: ai-software-factory
74 spec:
75 restartPolicy: Never
76 initContainers:
77 # Stage the pinned wal-g binary (v3.0.7) into the shared emptyDir.
78 # Pinned image (verified 2026-10): Ubuntu 24.04 base, wal-g
79 # v3.0.7 PostgreSQL build, binary at /usr/local/bin/wal-g.
80 - name: walg-stager
81 image: chekkan/wal-g:v3.0.7
82 command:
83 - sh
84 - -c
85 - |
86 set -e
87 mkdir -p /opt/walg
88 cp /usr/local/bin/wal-g /opt/walg/wal-g
89 chmod 0755 /opt/walg/wal-g
90 /opt/walg/wal-g --version
91 volumeMounts:
92 - name: walg-tools
93 mountPath: /opt/walg
94 containers:
95 - name: backup
96 # postgres:16-alpine so pg_isready/psql exist for the readiness
97 # wait + post-backup row-count sanity; the wal-g binary is
98 # staged from the pinned image by the initContainer above.
99 image: postgres:16-alpine
100 command:
101 - sh
102 - -c
103 - |
104 set -eu
105 # ---- env: spec names -> wal-g v3 names (verified) -------
106 export WALG_S3_PREFIX="s3://${WAL_S3_BUCKET}/${WAL_S3_PREFIX:-pg}" # prefix env, default "pg" (same mapping as the archive_command wrapper)
107 export AWS_ENDPOINT="${S3_ENDPOINT}"
108 export AWS_REGION="${S3_REGION:-us-east-1}"
109 export AWS_S3_FORCE_PATH_STYLE="true"
110 # libpq target for ONLINE streaming backup-push (no data dir):
111 export PGHOST="${DATABASE_HOST:-factory-db}"
112 export PGPORT="${DATABASE_PORT:-5432}"
113
114 # 1) wait for the live DB (pg_isready, up to ~10 min)
115 echo "[pg-backup] waiting for postgres at ${PGHOST}:${PGPORT} …"
116 i=0
117 until pg_isready -q -h "$PGHOST" -p "$PGPORT" -U "$PGUSER"; do
118 i=$((i+1)); [ "$i" -ge 120 ] && { echo "DB not ready after 600s"; exit 1; }
119 sleep 5
120 done
121
122 # 2) online base backup (streaming; consistent, no window)
123 echo "[pg-backup] wal-g backup-push (online streaming) …"
124 /opt/walg/wal-g backup-push
125
126 # 3) verify the WAL archive (integrity + timeline; VERIFIED:
127 # at least one check name is required — a bare wal-verify
128 # exits with an argument error). The timeline check
129 # connects to the live DB (QueryCurrentWalSegment) to
130 # find the current segment — PGHOST/PGUSER are set above.
131 echo "[pg-backup] wal-g wal-verify integrity timeline …"
132 /opt/walg/wal-g wal-verify integrity timeline
133
134 # 4) row-count sanity (cheap; a base backup that "succeeded"
135 # but produced an empty DB must not pass silently)
136 cnt="$(psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" -tAc 'select count(*) from pg_catalog.pg_class')"
137 echo "[pg-backup] pg_class rows in backup: ${cnt}"
138 [ "${cnt:-0}" -gt 0 ] || { echo "backup appears EMPTY (pg_class=0)"; exit 1; }
139
140 # 5) done. The base-backup directory in pg/basebackups_005/
141 # (finished by its *_backup_stop_sentinel.json) is the
142 # artifact the backup-health gate (< 26 h) reads — no
143 # synthetic marker (TASK-0135 design note, header).
144 echo "[pg-backup] OK — base backup + wal-verify + row-count sanity"
145 env:
146 # --- wal-g / S3 (same sources as the StatefulSet) ---
147 - name: WAL_S3_BUCKET
148 value: "factory-backups"
149 - name: WAL_S3_PREFIX
150 value: "pg"
151 - name: S3_ENDPOINT
152 valueFrom:
153 configMapKeyRef: { name: factory-config, key: S3_ENDPOINT }
154 - name: S3_REGION
155 valueFrom:
156 configMapKeyRef: { name: factory-config, key: S3_REGION }
157 - name: AWS_ACCESS_KEY_ID
158 valueFrom: { secretKeyRef: { name: factory-secrets, key: S3_ACCESS_KEY } }
159 - name: AWS_SECRET_ACCESS_KEY
160 valueFrom: { secretKeyRef: { name: factory-secrets, key: S3_SECRET_KEY } }
161 # --- libpq (online backup-push connects as the factory user) ---
162 - name: DATABASE_HOST
163 valueFrom: { configMapKeyRef: { name: factory-config, key: DATABASE_HOST } }
164 - name: DATABASE_PORT
165 valueFrom: { configMapKeyRef: { name: factory-config, key: DATABASE_PORT } }
166 - name: PGUSER
167 valueFrom: { secretKeyRef: { name: factory-db, key: POSTGRES_USER } }
168 - name: PGPASSWORD
169 valueFrom: { secretKeyRef: { name: factory-db, key: POSTGRES_PASSWORD } }
170 - name: PGDATABASE
171 valueFrom: { secretKeyRef: { name: factory-db, key: POSTGRES_DB } }
172 volumeMounts:
173 - name: walg-tools
174 mountPath: /opt/walg
175 volumes:
176 - name: walg-tools
177 emptyDir: {}
178
