infrastructure/databases/clickhouse.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: clickhouse-data-pvc
  namespace: databases
spec:
  accessModes:
    - ReadWriteOnce
  storageClassName: longhorn
  resources:
    requests:
      storage: 120Gi
    limits:
      storage: 100Gi
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: clickhouse-logs-pvc
  namespace: databases
spec:
  accessModes:
    - ReadWriteOnce
  storageClassName: longhorn
  resources:
    requests:
      storage: 1Gi
    limits:
      storage: 1Gi
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: clickhouse-configmap
  namespace: databases
data:
  custom-config.xml: |
    <clickhouse>
      <logger>
        <level>warning</level>
        <size>100M</size>
        <console>1</console>
      </logger>
      <listen_host>0.0.0.0</listen_host>
      <http_port>8123</http_port>
      <tcp_port>9000</tcp_port>
      <prometheus>
          <endpoint>/metrics</endpoint>
          <port>9363</port>
          <metrics>true</metrics>
          <events>true</events>
          <asynchronous_metrics>true</asynchronous_metrics>
      </prometheus>
      <backups>
        <allowed_disk>s3_plain</allowed_disk>
      </backups>
    </clickhouse>    
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: clickhouse
  namespace: databases
spec:
  serviceName: "clickhouse"
  replicas: 1
  selector:
    matchLabels:
      ms: clickhouse
  template:
    metadata:
      labels:
        ms: clickhouse
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "9363"
    spec:
      terminationGracePeriodSeconds: 120
      containers:
        - name: clickhouse
          image: clickhouse/clickhouse-server:23.10
          ports:
            - containerPort: 8123
            - containerPort: 9000
            - containerPort: 9363
          volumeMounts:
            - name: data
              mountPath: /var/lib/clickhouse/
            - name: logs
              mountPath: /var/log/clickhouse-server/
            - name: config
              mountPath: /etc/clickhouse-server/config.d/
          env:
            - name: CLICKHOUSE_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: secrets
                  key: CLICKHOUSE_PASSWORD
            - name: AWS_ACCESS_KEY_ID
              valueFrom:
                secretKeyRef:
                  name: secrets
                  key: AWS_ACCESS_KEY_ID
            - name: AWS_SECRET_ACCESS_KEY
              valueFrom:
                secretKeyRef:
                  name: secrets
                  key: AWS_SECRET_ACCESS_KEY
      volumes:
        - name: data
          persistentVolumeClaim:
            claimName: clickhouse-data-pvc
        - name: logs
          persistentVolumeClaim:
            claimName: clickhouse-logs-pvc
        - name: config
          configMap:
            name: clickhouse-configmap
---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: clickhouse-backup
  namespace: databases
spec:
  schedule: "0 6 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          restartPolicy: Never
          containers:
            - name: clickhouse-backup
              image: clickhouse/clickhouse-server:23.10
              env:
                - name: CLICKHOUSE_PASSWORD
                  valueFrom:
                    secretKeyRef:
                      name: secrets
                      key: CLICKHOUSE_PASSWORD
                - name: AWS_ACCESS_KEY_ID
                  valueFrom:
                    secretKeyRef:
                      name: secrets
                      key: AWS_ACCESS_KEY_ID
                - name: AWS_SECRET_ACCESS_KEY
                  valueFrom:
                    secretKeyRef:
                      name: secrets
                      key: AWS_SECRET_ACCESS_KEY
              command:
                - /bin/sh
                - -c
                - >
                  clickhouse client --host clickhouse.databases --user default --password $CLICKHOUSE_PASSWORD --query="BACKUP TABLE nocodelytics_production.events TO S3('https://eu2.contabostorage.com/clickhouse/backup/events/$(date +\%Y-\%m-\%d).zip', '$AWS_ACCESS_KEY_ID', '$AWS_SECRET_ACCESS_KEY');" &&
                  clickhouse client --host clickhouse.databases --user default --password $CLICKHOUSE_PASSWORD --query="BACKUP TABLE nocodelytics_production.visitors TO S3('https://eu2.contabostorage.com/clickhouse/backup/visitors/$(date +\%Y-\%m-\%d).zip', '$AWS_ACCESS_KEY_ID', '$AWS_SECRET_ACCESS_KEY');"                  
---
apiVersion: v1
kind: Service
metadata:
  name: clickhouse
  namespace: databases
spec:
  type: ClusterIP
  selector:
    ms: clickhouse
  ports:
    - name: http
      port: 8123
      targetPort: 8123
    - name: native
      port: 9000
      targetPort: 9000
    - name: prometheus
      port: 9363
      targetPort: 9363