1# =============================================================================
2# AI Software Factory — PostgreSQL PITR: postgresql.conf additions + shims
3#
4# Three files are carried in this ConfigMap (all mounted read-only):
5#
6# pitr.conf — the archive settings, staged into
7# $PGDATA/conf.d/pitr.conf by the `pitr-stager`
8# initContainer (see statefulset.yaml), loaded via
9# `include_dir 'conf.d'`.
10# wal-push-wrapper.sh — the REAL archive_command target (staged into
11# /opt/walg by the `walg-stager` initContainer).
12# postgres: archive_command is run as a child of the
13# postmaster, so it inherits the postgres container's
14# env (WAL_S3_BUCKET, WAL_S3_PREFIX, S3_ENDPOINT,
15# S3_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY).
16# The wrapper maps the spec names onto the wal-g v3
17# env (VERIFIED against chekkan/wal-g:v3.0.7,
18# pkg/storages/s3/configure.go):
19#
20# WAL_S3_BUCKET + WAL_S3_PREFIX -> WALG_S3_PREFIX
21# S3_ENDPOINT -> AWS_ENDPOINT
22# S3_REGION -> AWS_REGION
23# AWS_ACCESS_KEY_ID / -> (kept as-is)
24# AWS_SECRET_ACCESS_KEY
25#
26# It runs `wal-g wal-push %p` and exits with
27# wal-g's exit code so a failed archive makes
28# Postgres KEEP the segment (never lose WAL).
29#
30# MARKERS (design note, TASK-0135): the backup-health
31# gate (scripts/backup-health.ts, runbook §8.7) reads
32# the REAL ARTIFACTS, not side-channel marker files —
33# "last WAL ship" = newest object LastModified under
34# pg/wal_005/ (the segments wal-g writes itself),
35# "last base backup" = newest under pg/basebackups_005/.
36# No synthetic marker is written here: a marker
37# without the segment would be a false signal, and a
38# segment without a marker would be a false alarm.
39# first-boot-pitr.sh — mounted into /docker-entrypoint-initdb.d/ so the
40# official entrypoint runs it AFTER initdb on a FRESH
41# volume: appends `include_dir 'conf.d'` to the
42# initdb-generated postgresql.conf (idempotent).
43#
44# S3 layout (bucket `factory-backups`, versioning ON — a bucket-level
45# setting, runbook §8.7). Layout names are wal-g v3.0.7 CONSTANTS (verified
46# from utility/utility.go: VersionStr "005"):
47# s3:
48# s3:
49# s3:
50# (No synthetic marker files: the gate reads the artifacts above, §8.7.)
51#
52# RPO ≤ 5 min: segments are pushed the moment they complete (per-segment
53# archive_command, seconds under load); `archive_timeout = 60` force-flushes
54# a partial segment at idle, so the bound holds even with no writes.
55# =============================================================================
56apiVersion: v1
57kind: ConfigMap
58metadata:
59 name: factory-pg-pitr-config
60 labels:
61 app.kubernetes.io/name: postgres
62 app.kubernetes.io/component: database
63 app.kubernetes.io/part-of: ai-software-factory
64data:
65 # Staged into $PGDATA/conf.d/pitr.conf (see postgres.yaml initContainers).
66 pitr.conf: |
67 # ---- PITR / WAL archiving (TASK-0135 backup/DR) ----
68 archive_mode = on
69 # Absolute path: the `walg-stager` initContainer stages the pinned wal-g
70 # binary + this wrapper into the shared emptyDir at /opt/walg. No PATH
71 # dependence, stable across image bumps.
72 archive_command = '/opt/walg/wal-push-wrapper.sh %p'
73 # Force-flush a partial WAL segment every 60 s so an IDLE cluster still
74 # ships (this is what makes RPO <= 5 min hold with no writes).
75 archive_timeout = 60
76 wal_keep_size = '512MB' # headroom for the replica / crash recovery
77 max_wal_size = '2GB'
78 min_wal_size = '80MB'
79 # The PITR restore runbook (docs/development/deployment.md §8.1) sets
80 # these AT RESTORE TIME on the restore instance (never on live):
81 # restore_command = '/opt/walg/wal-g wal-fetch %f %p'
82 # recovery_target_time = '<T, e.g. incident start>'
83 # recovery_target_action = 'pause'
84 # (wal-fetch exits 74/EX_IOERR when the archive runs out — that is the
85 # designed "end of archived WAL" signal; wal-g's PostgreSQL.md documents
86 # the wrapper trick for mapping other errors into 126–255.)
87
88 # Staged into /opt/walg/wal-push-wrapper.sh (executed by archive_command as
89 # a child of the postmaster → inherits the postgres container env).
90 wal-push-wrapper.sh: |
91 #!/bin/sh
92 # archive_command wrapper: map spec env -> wal-g v3 env, push the segment,
93 # exit with wal-g's code. A non-zero exit makes Postgres RETAIN the
94 # segment (never lose WAL).
95 #
96 # No marker file is written (TASK-0135 design): the backup-health gate
97 # (scripts/backup-health.ts, runbook §8.7) derives "last WAL ship" from
98 # the newest object LastModified under pg/wal_005/ — the segment wal-g
99 # just wrote IS the marker. A synthetic marker could diverge from the
100 # artifact and would be a false signal (or false alarm) either way.
101 set -u
102 seg="$1"
103
104 # --- spec names -> wal-g v3 names (verified against chekkan/wal-g:v3.0.7)
105 # wal-g v3 has NO WALG_S3_BUCKET / WALG_S3_ENDPOINT: the storage root is
106 # WALG_S3_PREFIX=s3:
107 # (prefix comes from the WAL_S3_PREFIX env, default "pg")
108 export WALG_S3_PREFIX="s3://${WAL_S3_BUCKET}/${WAL_S3_PREFIX:-pg}"
109 export AWS_ENDPOINT="${S3_ENDPOINT}"
110 export AWS_REGION="${S3_REGION:-us-east-1}"
111 export AWS_S3_FORCE_PATH_STYLE="true" # MinIO & most self-host S3
112 # AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY are already in the env.
113
114 /opt/walg/wal-g wal-push "$seg"
115 exit $?
116
117 # Runs as the postgres user, AFTER initdb, on a FRESH volume only.
118 first-boot-pitr.sh: |
119 #!/bin/sh
120 # Append `include_dir 'conf.d'` to the initdb-generated postgresql.conf so
121 # the staged $PGDATA/conf.d/pitr.conf is authoritative from first start.
122 # Idempotent (grep guard) — safe if it ever runs twice.
123 set -e
124 CONF="${PGDATA:-/var/lib/postgresql/data/pgdata}/postgresql.conf"
125 if [ -f "$CONF" ] && ! grep -q "include_dir 'conf.d'" "$CONF"; then
126 echo "include_dir 'conf.d'" >> "$CONF"
127 echo "[first-boot-pitr] appended include_dir 'conf.d' to $CONF"
128 fi
129
130 # ---- Per-app roles for the PgBouncer pools (TASK-0135) --------------
131 # The pooler (postgres/pgbouncer.yaml) opens one bounded pool per app
132 # role (api/worker/realtime/web) + a read-only `reader` for the replica
133 # pool (db.read()). All connect with the SAME password as the `factory`
134 # role (POSTGRES_PASSWORD, set by the entrypoint). The password is passed
135 # as a server GUC via PGOPTIONS (never on the command line) and read with
136 # current_setting('pw'); the roles inherit the `factory` role's privileges
137 # so they can use the `factory` database. Idempotent: the DO block checks
138 # pg_roles, safe on re-run. (A password containing `=` or space would need
139 # PGOPTIONS quoting — the factory-db secret is operator-controlled, no
140 # special chars required.)
141 PGOPTIONS="-c pw=${POSTGRES_PASSWORD}" psql \
142 -U "${POSTGRES_USER:-factory}" -d "${POSTGRES_DB:-factory}" \
143 -v ON_ERROR_STOP=1 <<'SQL'
144 DO $$
145 DECLARE r text;
146 BEGIN
147 FOREACH r IN ARRAY ARRAY['api','worker','realtime','web'] LOOP
148 IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = r) THEN
149 EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', r, current_setting('pw'));
150 END IF;
151 EXECUTE format('GRANT factory TO %I', r);
152 END LOOP;
153 IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') THEN
154 EXECUTE format('CREATE ROLE reader LOGIN PASSWORD %L INROLE pg_read_all_data', current_setting('pw'));
155 END IF;
156 END$$;
157 SQL
158 echo "[first-boot-pitr] app roles (api/worker/realtime/web/reader) ready"
159
