# ============================================================================= # AI Software Factory — PostgreSQL PITR: postgresql.conf additions + shims # # Three files are carried in this ConfigMap (all mounted read-only): # # pitr.conf — the archive settings, staged into # $PGDATA/conf.d/pitr.conf by the `pitr-stager` # initContainer (see statefulset.yaml), loaded via # `include_dir 'conf.d'`. # wal-push-wrapper.sh — the REAL archive_command target (staged into # /opt/walg by the `walg-stager` initContainer). # postgres: archive_command is run as a child of the # postmaster, so it inherits the postgres container's # env (WAL_S3_BUCKET, WAL_S3_PREFIX, S3_ENDPOINT, # S3_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY). # The wrapper maps the spec names onto the wal-g v3 # env (VERIFIED against chekkan/wal-g:v3.0.7, # pkg/storages/s3/configure.go): # # WAL_S3_BUCKET + WAL_S3_PREFIX -> WALG_S3_PREFIX # S3_ENDPOINT -> AWS_ENDPOINT # S3_REGION -> AWS_REGION # AWS_ACCESS_KEY_ID / -> (kept as-is) # AWS_SECRET_ACCESS_KEY # # It runs `wal-g wal-push %p` and exits with # wal-g's exit code so a failed archive makes # Postgres KEEP the segment (never lose WAL). # # MARKERS (design note, TASK-0135): the backup-health # gate (scripts/backup-health.ts, runbook §8.7) reads # the REAL ARTIFACTS, not side-channel marker files — # "last WAL ship" = newest object LastModified under # pg/wal_005/ (the segments wal-g writes itself), # "last base backup" = newest under pg/basebackups_005/. # No synthetic marker is written here: a marker # without the segment would be a false signal, and a # segment without a marker would be a false alarm. # first-boot-pitr.sh — mounted into /docker-entrypoint-initdb.d/ so the # official entrypoint runs it AFTER initdb on a FRESH # volume: appends `include_dir 'conf.d'` to the # initdb-generated postgresql.conf (idempotent). # # S3 layout (bucket `factory-backups`, versioning ON — a bucket-level # setting, runbook §8.7). Layout names are wal-g v3.0.7 CONSTANTS (verified # from utility/utility.go: VersionStr "005"): # s3://factory-backups/pg/basebackups_005/… daily base backups (wal-g) # s3://factory-backups/pg/wal_005/… archived WAL segments (wal-g) # s3://factory-backups/opensearch/snapshots/… OS daily snapshots (CronJob) # (No synthetic marker files: the gate reads the artifacts above, §8.7.) # # RPO ≤ 5 min: segments are pushed the moment they complete (per-segment # archive_command, seconds under load); `archive_timeout = 60` force-flushes # a partial segment at idle, so the bound holds even with no writes. # ============================================================================= apiVersion: v1 kind: ConfigMap metadata: name: factory-pg-pitr-config labels: app.kubernetes.io/name: postgres app.kubernetes.io/component: database app.kubernetes.io/part-of: ai-software-factory data: # Staged into $PGDATA/conf.d/pitr.conf (see postgres.yaml initContainers). pitr.conf: | # ---- PITR / WAL archiving (TASK-0135 backup/DR) ---- archive_mode = on # Absolute path: the `walg-stager` initContainer stages the pinned wal-g # binary + this wrapper into the shared emptyDir at /opt/walg. No PATH # dependence, stable across image bumps. archive_command = '/opt/walg/wal-push-wrapper.sh %p' # Force-flush a partial WAL segment every 60 s so an IDLE cluster still # ships (this is what makes RPO <= 5 min hold with no writes). archive_timeout = 60 wal_keep_size = '512MB' # headroom for the replica / crash recovery max_wal_size = '2GB' min_wal_size = '80MB' # The PITR restore runbook (docs/development/deployment.md §8.1) sets # these AT RESTORE TIME on the restore instance (never on live): # restore_command = '/opt/walg/wal-g wal-fetch %f %p' # recovery_target_time = '' # recovery_target_action = 'pause' # (wal-fetch exits 74/EX_IOERR when the archive runs out — that is the # designed "end of archived WAL" signal; wal-g's PostgreSQL.md documents # the wrapper trick for mapping other errors into 126–255.) # Staged into /opt/walg/wal-push-wrapper.sh (executed by archive_command as # a child of the postmaster → inherits the postgres container env). wal-push-wrapper.sh: | #!/bin/sh # archive_command wrapper: map spec env -> wal-g v3 env, push the segment, # exit with wal-g's code. A non-zero exit makes Postgres RETAIN the # segment (never lose WAL). # # No marker file is written (TASK-0135 design): the backup-health gate # (scripts/backup-health.ts, runbook §8.7) derives "last WAL ship" from # the newest object LastModified under pg/wal_005/ — the segment wal-g # just wrote IS the marker. A synthetic marker could diverge from the # artifact and would be a false signal (or false alarm) either way. set -u seg="$1" # --- spec names -> wal-g v3 names (verified against chekkan/wal-g:v3.0.7) # wal-g v3 has NO WALG_S3_BUCKET / WALG_S3_ENDPOINT: the storage root is # WALG_S3_PREFIX=s3:///, S3 options use AWS_* names. # (prefix comes from the WAL_S3_PREFIX env, default "pg") export WALG_S3_PREFIX="s3://${WAL_S3_BUCKET}/${WAL_S3_PREFIX:-pg}" export AWS_ENDPOINT="${S3_ENDPOINT}" export AWS_REGION="${S3_REGION:-us-east-1}" export AWS_S3_FORCE_PATH_STYLE="true" # MinIO & most self-host S3 # AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY are already in the env. /opt/walg/wal-g wal-push "$seg" exit $? # Runs as the postgres user, AFTER initdb, on a FRESH volume only. first-boot-pitr.sh: | #!/bin/sh # Append `include_dir 'conf.d'` to the initdb-generated postgresql.conf so # the staged $PGDATA/conf.d/pitr.conf is authoritative from first start. # Idempotent (grep guard) — safe if it ever runs twice. set -e CONF="${PGDATA:-/var/lib/postgresql/data/pgdata}/postgresql.conf" if [ -f "$CONF" ] && ! grep -q "include_dir 'conf.d'" "$CONF"; then echo "include_dir 'conf.d'" >> "$CONF" echo "[first-boot-pitr] appended include_dir 'conf.d' to $CONF" fi # ---- Per-app roles for the PgBouncer pools (TASK-0135) -------------- # The pooler (postgres/pgbouncer.yaml) opens one bounded pool per app # role (api/worker/realtime/web) + a read-only `reader` for the replica # pool (db.read()). All connect with the SAME password as the `factory` # role (POSTGRES_PASSWORD, set by the entrypoint). The password is passed # as a server GUC via PGOPTIONS (never on the command line) and read with # current_setting('pw'); the roles inherit the `factory` role's privileges # so they can use the `factory` database. Idempotent: the DO block checks # pg_roles, safe on re-run. (A password containing `=` or space would need # PGOPTIONS quoting — the factory-db secret is operator-controlled, no # special chars required.) PGOPTIONS="-c pw=${POSTGRES_PASSWORD}" psql \ -U "${POSTGRES_USER:-factory}" -d "${POSTGRES_DB:-factory}" \ -v ON_ERROR_STOP=1 <<'SQL' DO $$ DECLARE r text; BEGIN FOREACH r IN ARRAY ARRAY['api','worker','realtime','web'] LOOP IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = r) THEN EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', r, current_setting('pw')); END IF; EXECUTE format('GRANT factory TO %I', r); END LOOP; IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') THEN EXECUTE format('CREATE ROLE reader LOGIN PASSWORD %L INROLE pg_read_all_data', current_setting('pw')); END IF; END$$; SQL echo "[first-boot-pitr] app roles (api/worker/realtime/web/reader) ready"