deploy/platform/kubernetes/templates/feature-postgresql-monitor/psql.yaml

# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#
{{- if .Values.features.postgresqlMonitor.enabled }}

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: psql-slowsql-conf
data:
  psql-config-file: |
    listen_addresses = '*'
    max_connections = 100

    shared_buffers = 128MB

    max_wal_size = 1GB
    min_wal_size = 80MB

    log_timezone = 'Etc/UTC'

    datestyle = 'iso, mdy'

    timezone = 'Etc/UTC'

    lc_messages = 'en_US.utf8'

    lc_monetary = 'en_US.utf8'
    lc_numeric = 'en_US.utf8'
    lc_time = 'en_US.utf8'

    default_text_search_config = 'pg_catalog.english'

    logging_collector = on
    log_directory = 'log'
    log_filename = 'slow.log'
    log_rotation_age = 1d
    log_rotation_size = 10MB
    log_min_messages = info

    log_min_duration_statement = 1000
    log_checkpoints = on
    log_connections = on
    log_disconnections = on
    log_duration = on
    log_line_prefix = '%m'

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mock-sql-psql
  labels:
    app: psql-load
data:
  mock-sql: |
    CREATE TABLE COMPANY(
      ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL
    );
    SELECT pg_sleep(10);
    INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);
    UPDATE COMPANY SET SALARY = 15000 WHERE ID = 1;
    DELETE FROM COMPANY WHERE ID = 1;

---
apiVersion: v1
kind: Service
metadata:
  name: psql
spec:
  selector:
    app: psql
  ports:
    - protocol: TCP
      port: 5432
      name: psql
    - protocol: TCP
      port: 9187
      name: psql-exporter

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: psql-deployment
  labels:
    app: psql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: psql
  template:
    metadata:
      labels:
        app: psql
      annotations:
        sidecar.istio.io/inject: "false"
    spec:
      containers:
        - name: fluent-bit
          image: fluent/fluent-bit:1.9
          env:
            - name: SW_SERVICE
              valueFrom:
                fieldRef:
                  fieldPath: metadata.name
          volumeMounts:
            - name: slowsql-psql-fluent-bit
              mountPath: /fluent-bit/etc/
            - name: shared-data
              mountPath: /data/
        - name: psql
          image: postgres:14.1
          command: ["docker-entrypoint.sh", "-c", "config_file=/etc/postgresql.conf"]
          env:
            - name: POSTGRES_PASSWORD
              value: password
          ports:
            - containerPort: 5432
          volumeMounts:
            - name: psql-slowsql-conf
              mountPath: /etc/postgresql.conf
              subPath: postgresql.conf
            - name: shared-data
              mountPath: /var/lib/postgresql/data/
        - name: psql-exporter
          image: prometheuscommunity/postgres-exporter:v0.11.0
          resources:
            limits:
              cpu: 100m
              memory: "128Mi"
          env:
            - name: DATA_SOURCE_NAME
              value: postgresql://postgres:password@psql:5432/postgres?sslmode=disable
          ports:
            - containerPort: 9187
              name: metrics
      volumes:
        - name: shared-data
          emptyDir: {}
        - name: slowsql-psql-fluent-bit
          configMap:
            name: slowsql-psql-fluent-bit
            items:
              - key: fluent-bit-conf
                path: fluent-bit.conf
              - key: fluent-bit-parser-conf
                path: fluent-bit-parser.conf
              - key: fluent-bit-script-lua
                path: fluent-bit-script.lua
        - name: psql-slowsql-conf
          configMap:
            name: psql-slowsql-conf
            items:
              - key: psql-config-file
                path: postgresql.conf

---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: psql-load-deployment # @feature: postgresql; set up job to trigger postgresql commands, you don't need this in production env.
spec:
  schedule: "*/3 * * * *"
  successfulJobsHistoryLimit: 1
  failedJobsHistoryLimit: 1
  jobTemplate:
    spec:
      template:
        metadata:
          annotations:
            sidecar.istio.io/inject: "false"
        spec:
          restartPolicy: Never
          initContainers:
            - name: wait-for-psql
              image: busybox:1.30
              command:
                [
                  "sh",
                  "-c",
                  "until nc -z psql 5432 > /dev/null; do echo Waiting for psql.; sleep 2; done;",
                ]
          containers:
            - name: psql-load
              image: postgres:14.1
              env:
                - name: PGPASSWORD
                  value: password
              command:
                - bash
                - -c
                - "psql -f /conf/mock.sql -h psql -p 5432 -U postgres postgres"
              volumeMounts:
                - name: mock-sql-vol-psql
                  mountPath: /conf
          volumes:
            - name: mock-sql-vol-psql
              configMap:
                name: mock-sql-psql
                items:
                  - key: mock-sql
                    path: mock.sql

{{- end }}