betelgeusebytes/k8s/postgres/pg.yaml

# k8s/postgres/pg-init-sql-configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: pg-init-sql
  namespace: db
data:
  00_extensions.sql: |
    \connect gitea
    CREATE EXTENSION IF NOT EXISTS postgis;
    CREATE EXTENSION IF NOT EXISTS postgis_topology;
    CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
    CREATE EXTENSION IF NOT EXISTS pg_trgm;
    CREATE EXTENSION IF NOT EXISTS hstore;
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    CREATE EXTENSION IF NOT EXISTS citext;
    CREATE EXTENSION IF NOT EXISTS unaccent;
    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    DO $$ BEGIN
      CREATE EXTENSION IF NOT EXISTS plpython3u;
    EXCEPTION WHEN undefined_file THEN
      RAISE NOTICE 'plpython3u not available in this image';
    END $$;    
  01_tune.sql: |
    ALTER SYSTEM SET shared_buffers = '1GB';
    ALTER SYSTEM SET work_mem = '32MB';
    ALTER SYSTEM SET maintenance_work_mem = '512MB';
    ALTER SYSTEM SET max_connections = 200;
    SELECT pg_reload_conf();    
---
# k8s/postgres/pg-conf.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: pg-conf
  namespace: db
data:
  pg_hba.conf: |
    # Local connections
    local   all   all                                   trust
    host    all   all          127.0.0.1/32             trust
    host    all   all          ::1/128                  trust
    # TLS-only access from ANY external IP (harden as needed)
    hostssl all  all          0.0.0.0/0                 md5
    hostssl all  all          ::/0                      md5    
---
# k8s/postgres/pg-secret.yaml
apiVersion: v1
kind: Secret
metadata:
  name: pg18-secret
  namespace: db
type: Opaque
stringData:
  POSTGRES_PASSWORD: "pa$$word"
---
# k8s/postgres/pg-certificate.yaml
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: pg-tls
  namespace: db
spec:
  secretName: pg-tls
  dnsNames:
    - pg.betelgeusebytes.io
  issuerRef:
    kind: ClusterIssuer
    name: letsencrypt-prod
---
# k8s/postgres/postgres-svc.yaml
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: db
spec:
  selector:
    app: postgres
  ports:
    - name: postgres
      port: 5432
      targetPort: 5432
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-hl
  namespace: db
spec:
  clusterIP: None
  selector:
    app: postgres
  ports:
    - name: postgres
      port: 5432
      targetPort: 5432
---
# k8s/postgres/postgres.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: db
spec:
  serviceName: postgres-hl
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      securityContext:
        runAsUser: 999
        runAsGroup: 999
        fsGroup: 999
        fsGroupChangePolicy: "Always"
      initContainers:
        - name: install-certs
          image: busybox:1.36
          command:
            - sh
            - -c
            - |
              cp /in/tls.crt /out/server.crt
              cp /in/tls.key /out/server.key
              chown 999:999 /out/* || true
              chmod 600 /out/server.key              
          securityContext:
            runAsUser: 0
          volumeMounts:
            - { name: pg-tls,   mountPath: /in,  readOnly: true }
            - { name: pg-certs, mountPath: /out }
      containers:
        - name: postgres
          image: axxs/postgres:18-postgis-vector
          imagePullPolicy: IfNotPresent
          args:
            - -c
            - ssl=on
            - -c
            - ssl_cert_file=/certs/server.crt
            - -c
            - ssl_key_file=/certs/server.key
            - -c
            - hba_file=/etc/postgresql-custom/pg_hba.conf
          env:
            - name: POSTGRES_USER
              value: "app"
            - name: POSTGRES_DB
              value: "gitea"
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: pg18-secret
                  key: POSTGRES_PASSWORD
            - name: TZ
              value: "Europe/Paris"
          ports:
            - name: postgres
              containerPort: 5432
          volumeMounts:
            - { name: data,  mountPath: /var/lib/postgresql }        # PG18 expects parent, creates /var/lib/postgresql/18/main
            - { name: init,  mountPath: /docker-entrypoint-initdb.d, readOnly: true }
            - { name: pg-certs, mountPath: /certs }
            - { name: pg-conf,  mountPath: /etc/postgresql-custom }
          readinessProbe:
            exec: { command: ["sh","-c","pg_isready -U \"$POSTGRES_USER\" -d \"$POSTGRES_DB\" -h 127.0.0.1"] }
            initialDelaySeconds: 5
            periodSeconds: 10
            timeoutSeconds: 5
            failureThreshold: 6
          livenessProbe:
            exec: { command: ["sh","-c","pg_isready -U \"$POSTGRES_USER\" -d \"$POSTGRES_DB\" -h 127.0.0.1"] }
            initialDelaySeconds: 20
            periodSeconds: 10
            timeoutSeconds: 5
            failureThreshold: 6
          resources:
            requests: { cpu: "250m", memory: "512Mi" }
            limits:   { cpu: "1",    memory: "2Gi" }
      volumes:
        - name: init
          configMap:
            name: pg-init-sql
            defaultMode: 0444
        - name: pg-tls
          secret:
            secretName: pg-tls
        - name: pg-certs
          emptyDir: {}
        - name: pg-conf
          configMap:
            name: pg-conf
            defaultMode: 0444
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: local-ssd-hetzner
        resources:
          requests:
            storage: 80Gi


# kubectl -n ingress-nginx create configmap tcp-services \
#   --from-literal="5432=db/postgres:5432" \
#   -o yaml --dry-run=client | kubectl apply -f -
# kubectl -n ingress-nginx patch deploy ingress-nginx-controller \
#   --type='json' -p='[
#     {"op":"add","path":"/spec/template/spec/containers/0/args/-","value":"--tcp-services-configmap=$(POD_NAMESPACE)/tcp-services"}
#   ]'
# # controller must listen on hostPort:5432 (we already patched earlier)