1# =============================================================================
2# AI Software Factory — PostgreSQL 16 (StatefulSet, single replica) + PITR
3#
4# NOTE FOR PRODUCTION:
5# A self-hosted StatefulSet is the simple path. For production you are
6# strongly advised to use a MANAGED database instead (AWS RDS / Aurora,
7# GCP Cloud SQL, Azure Database for PostgreSQL) — you get automated
8# backups, patching, failover and monitoring for free. If you do that:
9# 1. delete this file from the kustomization resources list, and
10# 2. point the DATABASE_URL at the managed endpoint (the apps reference
11# the `factory-db` service name — create a Service with that name in
12# front of the endpoint, or change the env var).
13#
14# PITR (TASK-0135 backup/DR — RPO ≤ 5 min, RTO ≤ 1 h), wal-g v3 (pinned
15# image `chekkan/wal-g:v3.0.7`):
16#
17# WAL archiving — Postgres-native, the pattern wal-g's own PostgreSQL.md
18# prescribes: postgresql.conf `archive_command = '/opt/walg/
19# wal-push-wrapper.sh %p'` (the wrapper maps the spec env names onto
20# wal-g v3's and runs `wal-g wal-push %p` — see `postgres/configmap.yaml`).
21# Each completed WAL segment (~16 MB, seconds of writes under load) is
22# pushed to S3 the moment it completes. `archive_timeout = 60`
23# force-flushes a partial segment every 60 s so the RPO bound also holds
24# when the cluster is idle. The wal-g BINARY is staged into the shared
25# emptyDir `walg-tools` (mount `/opt/walg`) by the `walg-stager`
26# initContainer from the pinned image, so `archive_command` has no PATH
27# dependence.
28#
29# wal-g v3 env (VERIFIED against the pinned image, 2026-10):
30# - wal-g v3 REMOVED `WALG_S3_BUCKET` / `WALG_S3_ENDPOINT`. The storage
31# root is ONE variable: `WALG_S3_PREFIX=s3:
32# spec's "WAL_S3_BUCKET" requirement maps to the bucket component of
33# this prefix). wal-g organizes `basebackups/` and `wal_005/` under
34# that root itself — do NOT split base vs WAL into different roots
35# (PITR restore requires one storage per database).
36# - endpoint/region/creds come from the AWS_* names: AWS_ENDPOINT,
37# AWS_REGION, AWS_S3_FORCE_PATH_STYLE, AWS_ACCESS_KEY_ID,
38# AWS_SECRET_ACCESS_KEY (source: pkg/storages/s3/configure.go, v3.0.7).
39# - wal-g v3 has NO `wal-archivestart` and NO `backup-start` — archiving
40# is the archive_command above, and the daily base backup is a single
41# `wal-g backup-push` (online streaming mode, see cronjob-backup.yaml).
42#
43# Backup-health gate (stage 9, `scripts/backup-health.ts`, runbook §8.7)
44# reads the REAL ARTIFACTS — no component writes a synthetic marker
45# object (a marker without its artifact would be a false signal, and an
46# artifact without a marker a false alarm):
47# • "last base backup < 26 h" = newest object LastModified under
48# `pg/basebackups_005/` (each base ends with its
49# `*_backup_stop_sentinel.json`),
50# • "last WAL ship < 10 min" = newest object LastModified under
51# `pg/wal_005/` (the segments this archive_command ships),
52# • "last OS snapshot < 26 h" = newest SUCCESS snapshot start_time
53# from `GET /_snapshot/factory-s3/_all` (the snapshot CronJob puts
54# it in S3 under opensearch/snapshots/; the gate reads it via the
55# OS API, not a marker file).
56# Verdicts: reachable-but-stale → always FAIL; unreachable → loud
57# report-only SKIP in dev, hard FAIL in staging (BACKUP_HEALTH_STRICT /
58# DEPLOY_STAGING).
59#
60# S3 bucket: `WAL_S3_BUCKET` (factory-backups) must be created with
61# VERSIONING ON — S3 versioning is a bucket-level setting (runbook
62# §8.7), so a corrupt or bad backup can be rolled back to the previous
63# object version. A least-priv IAM policy for the backup identity is in
64# `postgres/walg-iam-policy.yaml` (put/get on the `pg/` prefix only).
65# =============================================================================
66apiVersion: apps/v1
67kind: StatefulSet
68metadata:
69 name: postgres
70 labels:
71 app.kubernetes.io/name: postgres
72 app.kubernetes.io/component: database
73spec:
74 serviceName: postgres
75 replicas: 1
76 selector:
77 matchLabels:
78 app.kubernetes.io/name: postgres
79 template:
80 metadata:
81 labels:
82 app.kubernetes.io/name: postgres
83 app.kubernetes.io/component: database
84 spec:
85 securityContext:
86 runAsUser: 999 # postgres user in the official image
87 runAsGroup: 999
88 initContainers:
89 # Stage the pinned wal-g binary into the shared emptyDir `walg-tools`
90 # (mounted at /opt/walg in the postgres container — that is where
91 # archive_command finds it). Verified against the image (2026-10):
92 # Ubuntu 24.04 base, wal-g v3.0.7 PostgreSQL build, binary at
93 # /usr/local/bin/wal-g. The image does NOT ship pg tools — which is
94 # fine here: archiving only needs the wal-g binary (postgres itself
95 # calls it per segment), and the daily base backup uses wal-g's
96 # ONLINE streaming mode (no local pg_basebackup needed, see
97 # cronjob-backup.yaml).
98 - name: walg-stager
99 image: chekkan/wal-g:v3.0.7
100 command:
101 - sh
102 - -c
103 - |
104 set -e
105 mkdir -p /opt/walg
106 cp /usr/local/bin/wal-g /opt/walg/wal-g
107 chmod 0755 /opt/walg/wal-g
108 # Copy the archive_command wrapper too (it maps the spec env
109 # names onto wal-g v3's, then runs `wal-g wal-push`; no marker
110 # writes — the gate reads the artifacts, see header).
111 cp /etc/pitr/wal-push-wrapper.sh /opt/walg/wal-push-wrapper.sh
112 chmod 0755 /opt/walg/wal-push-wrapper.sh
113 /opt/walg/wal-g --version
114 volumeMounts:
115 - name: walg-tools
116 mountPath: /opt/walg
117 - name: pitr-config
118 mountPath: /etc/pitr
119 readOnly: true
120 # Make the PITR config authoritative in $PGDATA (idempotent, safe on
121 # restarts of an already-initialized volume):
122 # - stage conf.d/pitr.conf (archive_command + archive_timeout +
123 # wal sizing) ONLY if not present — so a later manual edit of
124 # that file is never clobbered by a restart;
125 # - ensure `include_dir 'conf.d'` is in postgresql.conf (fresh
126 # volumes: postgresql.conf doesn't exist yet — initdb hasn't run —
127 # so only the conf.d staging happens now; the
128 # /docker-entrypoint-initdb.d/zz-first-boot-pitr.sh hook appends
129 # the include_dir line right after initdb).
130 - name: pitr-stager
131 image: busybox:1.36
132 command:
133 - sh
134 - -c
135 - |
136 set -e
137 PGD="${PGDATA:-/var/lib/postgresql/data/pgdata}"
138 mkdir -p "$PGD/conf.d"
139 if [ ! -f "$PGD/conf.d/pitr.conf" ]; then
140 cp /etc/pitr/pitr.conf "$PGD/conf.d/pitr.conf"
141 echo "[pitr-stager] staged $PGD/conf.d/pitr.conf"
142 fi
143 CONF="$PGD/postgresql.conf"
144 if [ -f "$CONF" ] && ! grep -q "include_dir 'conf.d'" "$CONF"; then
145 echo "include_dir 'conf.d'" >> "$CONF"
146 echo "[pitr-stager] appended include_dir to $CONF"
147 fi
148 env:
149 - name: PGDATA
150 value: /var/lib/postgresql/data/pgdata
151 volumeMounts:
152 - name: pgdata
153 mountPath: /var/lib/postgresql/data
154 - name: pitr-config
155 mountPath: /etc/pitr
156 readOnly: true
157 containers:
158 - name: postgres
159 image: postgres:16-alpine
160 ports:
161 - containerPort: 5432
162 name: postgres
163 envFrom:
164 # Secret `factory-db` (create per secret.example.yaml):
165 # POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB
166 - secretRef:
167 name: factory-db
168 env:
169 - name: PGDATA
170 value: /var/lib/postgresql/data/pgdata
171 # ---- wal-g S3 target (the spec's WAL_S3_BUCKET) ---------------
172 # ONE storage root for base backups AND WAL (wal-g v3 layout:
173 # <prefix>/basebackups/ + <prefix>/wal_005/ — do not split them).
174 # The wrapper the archive_command invokes exports this to the
175 # wal-g v3 name (WALG_S3_PREFIX) — see configmap.yaml, where the
176 # env → wal-g mapping is recorded and unit-tested by the
177 # runbook's staging checklist.
178 - name: WAL_S3_BUCKET
179 value: "factory-backups"
180 - name: WAL_S3_PREFIX
181 value: "pg"
182 - name: S3_ENDPOINT
183 valueFrom:
184 configMapKeyRef:
185 name: factory-config
186 key: S3_ENDPOINT
187 - name: S3_REGION
188 valueFrom:
189 configMapKeyRef:
190 name: factory-config
191 key: S3_REGION
192 # wal-g v3 credential names, mapped from the app's single set of
193 # storage credentials (see secret.example.yaml S3_ACCESS_KEY /
194 # S3_SECRET_KEY).
195 - name: AWS_ACCESS_KEY_ID
196 valueFrom:
197 secretKeyRef:
198 name: factory-secrets
199 key: S3_ACCESS_KEY
200 - name: AWS_SECRET_ACCESS_KEY
201 valueFrom:
202 secretKeyRef:
203 name: factory-secrets
204 key: S3_SECRET_KEY
205 resources:
206 requests:
207 cpu: 500m
208 memory: 1Gi
209 limits:
210 cpu: "2"
211 memory: 4Gi
212 volumeMounts:
213 - name: pgdata
214 mountPath: /var/lib/postgresql/data
215 - name: walg-tools
216 mountPath: /opt/walg
217 # First-boot hook: runs as postgres AFTER initdb, fresh volume
218 # only; appends `include_dir 'conf.d'` to the generated
219 # postgresql.conf.
220 - name: pitr-initdb-hook
221 mountPath: /docker-entrypoint-initdb.d/zz-first-boot-pitr.sh
222 subPath: zz-first-boot-pitr.sh
223 readOnly: true
224 startupProbe:
225 # Cold storage can be slow; ~2 min budget.
226 exec:
227 command:
228 - sh
229 - -c
230 - "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"
231 periodSeconds: 5
232 timeoutSeconds: 5
233 failureThreshold: 24
234 readinessProbe:
235 exec:
236 command:
237 - sh
238 - -c
239 - "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"
240 periodSeconds: 10
241 timeoutSeconds: 5
242 volumes:
243 - name: walg-tools
244 emptyDir: {}
245 - name: pitr-config
246 configMap:
247 name: factory-pg-pitr-config
248 - name: pitr-initdb-hook
249 configMap:
250 name: factory-pg-pitr-config
251 items:
252 - key: first-boot-pitr.sh
253 path: zz-first-boot-pitr.sh
254 defaultMode: 0755
255 # No PgBouncer / read replica / multi-replica HA in this file: the
256 # pooling + replica manifests are the Phase-15 "Pooling + replica"
257 # deliverable (k8s: pgbouncer deployment + DATABASE_REPLICA_URL); the
258 # PITR setup here is the backup/DR foundation they build on.
259 volumeClaimTemplates:
260 - metadata:
261 name: pgdata
262 spec:
263 accessModes: ["ReadWriteOnce"]
264 resources:
265 requests:
266 storage: 20Gi
267 # In production, set storageClassName here (e.g. gp3-encrypted on AWS).
268---
269apiVersion: v1
270kind: Service
271metadata:
272 # Named `factory-db` so application manifests can use a stable DNS name:
273 # postgresql:
274 name: factory-db
275 labels:
276 app.kubernetes.io/name: postgres
277spec:
278 type: ClusterIP
279 selector:
280 app.kubernetes.io/name: postgres
281 ports:
282 - name: postgres
283 port: 5432
284 targetPort: 5432
285
