postgresql.yaml

{{- if and (not .Values.disableAllApplications) .Values.applications.postgresql.enabled }}
{{- if .Values.primaryTenant }}
---
apiVersion: helm.cattle.io/v1
kind: HelmChart
metadata:
  name: postgres-operator
  namespace: kube-system
spec:
  chart: oci://registry.developers.crunchydata.com/crunchydata/pgo
  version: 6.0.0
  targetNamespace: kube-system
  valuesContent: |-
    relatedImages:
      postgres_17:
        image: ghcr.io/arch-anes/crunchy-postgres:ubi9-17.7-2550
    replicas: 1
    resources:
      controller:
        requests:
          cpu: 500m
          memory: 256Mi
        limits:
          memory: 512Mi

{{- if and (not .Values.disableAllApplications) .Values.applications.prometheus.enabled }}
---
apiVersion: helm.cattle.io/v1
kind: HelmChart
metadata:
  name: postgresql-monitoring
  namespace: kube-system
spec:
  chart: generic
  repo: https://arch-anes.github.io/charts
  version: 0.2.1
  targetNamespace: kube-system
  valuesContent: |-
    objects:
      # Source: https://github.com/ducnguyen-kasha-io/kasha-lab/blob/a954f765383ed322bdb1b6acda2be7628680ffc4/raw-templates/postgres-pgo/podmonitor.yaml
      - apiVersion: monitoring.coreos.com/v1
        kind: PodMonitor
        metadata:
          name: pgo-metrics
          namespace: kube-system
          labels:
            app: postgres-operator
        spec:
          selector:
            matchLabels:
              postgres-operator.crunchydata.com/control-plane: "pgo"
          podMetricsEndpoints:
            - port: metrics
              scheme: https
              interval: 15s
              scrapeTimeout: 15s
              tlsConfig:
                insecureSkipVerify: true
      - apiVersion: monitoring.coreos.com/v1
        kind: PodMonitor
        metadata:
          name: crunchy-postgres-exporter
          namespace: kube-system
        spec:
          namespaceSelector:
            any: true
          selector:
            matchLabels:
              postgres-operator.crunchydata.com/crunchy-postgres-exporter: "true"
          podMetricsEndpoints:
            - honorLabels: true
              path: /metrics
              port: exporter
              # Source: https://tinyurl.com/muyn5sc8
              relabelings:
                # Keep exporter port and drop all others
                - sourceLabels: [__meta_kubernetes_pod_container_port_number]
                  action: keep
                  regex: "9187"
                # Set label for namespace
                - sourceLabels: [__meta_kubernetes_namespace]
                  targetLabel: kubernetes_namespace
                # Set label for pod name
                - sourceLabels: [__meta_kubernetes_pod_name]
                  targetLabel: pod
                # Convert namespace and cluster name to pg_cluster=namespace:cluster
                - sourceLabels: [__meta_kubernetes_namespace,__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_cluster]
                  targetLabel: pg_cluster
                  separator: ":"
                  replacement: '$1$2'
                # Convert kubernetes pod ip to ip
                - sourceLabels: [__meta_kubernetes_pod_ip]
                  targetLabel: ip
                # Convert postgres-operator.crunchydata.com/instance to deployment
                - sourceLabels: [__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_instance]
                  targetLabel: deployment
                # Convert postgres-operator.crunchydata.com/role to role
                - sourceLabels: [__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_role]
                  targetLabel: role
{{- end }}
{{- end }}

# ---
# apiVersion: v1
# kind: Secret
# metadata:
#   name: postgresql-backup-credentials
#   namespace: "{{ .Values.applicationsNamespace }}"
# type: Opaque
# stringData:
#   s3.conf: |-
#     [global]
#     repo1-s3-key=some-id
#     repo1-s3-key-secret=some-key
#     repo1-cipher-pass=some-encryption-key

---
apiVersion: helm.cattle.io/v1
kind: HelmChart
metadata:
  name: postgresql
  namespace: "{{ .Values.applicationsNamespace }}"
spec:
  chart: generic
  repo: https://arch-anes.github.io/charts
  version: 0.2.1
  targetNamespace: "{{ .Values.applicationsNamespace }}"
  failurePolicy: abort # Do not uninstall this chart on failure, otherwise the cluster will be deleted
  takeOwnership: true
  valuesContent: |-
    objects:
      - apiVersion: v1
        kind: ConfigMap
        metadata:
          name: postgresql
          namespace: "{{ .Values.applicationsNamespace }}"
        data:
          bootstrap.sql: |
            \c postgres
            \o /tmp/grant_users_public_permissions.sql
            \pset format unaligned
            \pset tuples_only on

            WITH dbs AS (
              SELECT datname AS dbname,
                    regexp_replace(datname, '(_main|_log|_cache)$', '') AS username
              FROM pg_database
              WHERE NOT datistemplate AND datallowconn
            )
            SELECT
              'DO $$ BEGIN ' ||
              'IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ' || quote_literal(username) || ') THEN ' ||
                'EXECUTE ''ALTER DATABASE ' || quote_ident(dbname) || ' OWNER TO ' || quote_ident(username) || '''; ' ||
              'END IF; END $$;' || E'\n' ||

              '\connect ' || quote_ident(dbname) || E';\n' ||

              'DO $$ BEGIN ' ||
              'IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ' || quote_literal(username) || ') THEN ' ||
                'EXECUTE ''ALTER SCHEMA public OWNER TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''GRANT ALL ON SCHEMA public TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO ' || quote_ident(username) || '''; ' ||
                'EXECUTE ''ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO ' || quote_ident(username) || '''; ' ||
              'END IF; END $$;' || E'\n'
            FROM dbs;
            \o
            \i /tmp/grant_users_public_permissions.sql
{{- if .Values.applications.immich.enabled }}
            \c immich
            BEGIN;
            CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;
            CREATE EXTENSION IF NOT EXISTS vector CASCADE;
            CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
            COMMIT;
{{- end }}
{{- if .Values.applications.odoo.enabled }}
            \c odoo
            BEGIN;
            CREATE EXTENSION IF NOT EXISTS vector CASCADE;
            COMMIT;
{{- end }}
{{- if .Values.applications.tracearr.enabled }}
            \c tracearr
            BEGIN;
            CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
            COMMIT;
{{- end }}
      - apiVersion: postgres-operator.crunchydata.com/v1
        kind: PostgresCluster
        metadata:
          name: postgresql
          namespace: "{{ .Values.applicationsNamespace }}"
        spec:
          postgresVersion: 17
          instances:
            - name: instance1
              replicas: {{ include "ha.replicas" . }}
              # Schedule pods on separate nodes
              affinity:
                podAntiAffinity:
                  requiredDuringSchedulingIgnoredDuringExecution:
                    - topologyKey: kubernetes.io/hostname
                      labelSelector:
                        matchLabels:
                          postgres-operator.crunchydata.com/cluster: postgresql
                          postgres-operator.crunchydata.com/instance-set: instance1
              resources:
                requests:
                  cpu: "1000m"
                  memory: "1Gi"
                limits:
                  hugepages-2Mi: "2Gi"
                  memory: "4Gi"
              dataVolumeClaimSpec:
                accessModes:
                - "ReadWriteOnce"
                storageClassName: local-path-ephemeral
                resources:
                  requests:
                    storage: 10Gi
          config:
            parameters:
              password_encryption: scram-sha-256
              max_connections: "1000"
              shared_preload_libraries: "cube,earthdistance,vector,vchord,timescaledb"
          patroni:
            dynamicConfiguration:
              postgresql:
                # https://github.com/CrunchyData/postgres-operator/issues/2534#issuecomment-891202207
                pg_hba:
                  - "hostnossl all all 10.42.0.0/16 scram-sha-256"
                  - "hostnossl all all 10.43.0.0/16 scram-sha-256"
                  - "hostssl all all 10.42.0.0/16 scram-sha-256"
                  - "hostssl all all 10.43.0.0/16 scram-sha-256"
          users:
            - name: postgres
{{- if .Values.applications.authentik.enabled }}
            - name: authentik
              databases:
                - authentik
{{- end }}
{{- if .Values.applications.bazarr.enabled }}
            - name: bazarr
              databases:
                - bazarr
{{- end }}
{{- if .Values.applications.gotify.enabled }}
            - name: gotify
              databases:
                - gotify
{{- end }}
{{- if .Values.applications.home_assistant.enabled }}
            - name: home-assistant
              databases:
                - home-assistant
{{- end }}
{{- if .Values.applications.immich.enabled }}
            - name: immich
              databases:
                - immich
{{- end }}
{{- if .Values.applications.inventree.enabled }}
            - name: inventree
              databases:
                - inventree
{{- end }}
{{- if .Values.applications.miniflux.enabled }}
            - name: miniflux
              password:
                type: AlphaNumeric
              databases:
                - miniflux
{{- end }}
{{- if .Values.applications.n8n.enabled }}
            - name: n8n
              databases:
                - n8n
{{- end }}
{{- if .Values.applications.netbox.enabled }}
            - name: netbox
              databases:
                - netbox
{{- end }}
{{- if .Values.applications.nextcloud.enabled }}
            - name: nextcloud
              databases:
                - nextcloud
{{- end }}
{{- if .Values.applications.obico.enabled }}
            - name: obico
              password:
                type: AlphaNumeric
              databases:
                - obico
{{- end }}
{{- if .Values.applications.odoo.enabled }}
            - name: odoo
              databases:
                - odoo
{{- end }}
{{- if .Values.applications.pgadmin4.enabled }}
            - name: pgadmin4
              password:
                type: AlphaNumeric
              databases:
                - pgadmin4
{{- end }}
{{- if .Values.applications.prowlarr.enabled }}
            - name: prowlarr
              databases:
                - prowlarr_log
                - prowlarr_main
{{- end }}
{{- if .Values.applications.radarr.enabled }}
            - name: radarr
              databases:
                - radarr_log
                - radarr_main
{{- end }}
{{- if .Values.applications.sonarr.enabled }}
            - name: sonarr
              databases:
                - sonarr_log
                - sonarr_main
{{- end }}
{{- if .Values.applications.speedtest_tracker.enabled }}
            - name: speedtest-tracker
              databases:
                - speedtest-tracker
{{- end }}
{{- if .Values.applications.stalwart.enabled }}
            - name: stalwart
              databases:
                - stalwart
{{- end }}
{{- if .Values.applications.tracearr.enabled }}
            - name: tracearr
              password:
                type: AlphaNumeric
              databases:
                - tracearr
{{- end }}
{{- if .Values.applications.wakapi.enabled }}
            - name: wakapi
              databases:
                - wakapi
{{- end }}
{{- with .Values.applications.postgresql.additionalUsers }}
{{- toYaml . | nindent 12 }}
{{- end }}
          databaseInitSQL:
            name: postgresql
            key: bootstrap.sql
{{- if .Values.applications.prometheus.enabled }}
          monitoring:
            pgmonitor:
              exporter: {}
{{- end }}
          backups:
            pgbackrest:
              manual:
                repoName: repo1
                options:
                  - --type=full
              global:
                compress-level: '1'
                compress-level-network: '1'
                compress-type: zst
                repo1-s3-uri-style: path # Backblaze support
                repo1-retention-full: "5" # Keep 5 full backups
                repo1-cipher-type: aes-256-cbc
                repo2-retention-full: "2"
              configuration:
                - secret:
                    name: postgresql-backup-credentials
              repos:
                - name: repo1
                  {{- .Values.applications.postgresql.remoteBackupLocation | toYaml | nindent 18 }}
                  schedules:
                    full: "0 8 * * 2" # Every Tuesday at 8:00 AM
                    incremental: "15 12 * * *" # Every day at 12:15 PM
                - name: repo2
                  schedules:
                    full: "0 8 * * 1" # Every Monday at 8:00 AM
                    incremental: "45 * * * *" # Every hour at :45
                  volume:
                    volumeClaimSpec:
                      accessModes:
                      - "ReadWriteOnce"
                      storageClassName: local-path-persistent-namespaced
                      resources:
                        requests:
                          storage: 10Gi
{{- end }}