Installing CrunchyData PostgreSQL Operator

CrunchyData's operator provides a "batteries included" approach to PostgreSQL on Kuberentes.

It allows creating many databases from a single custom resource. Includes replication, connection pooling, monitoring and backups.

The documentation recommends forking the repository before cloning.

https://github.com/CrunchyData/postgres-operator-examples/

1-Install the operator

kubectl apply -k kustomize/install/namespace
kubectl apply --server-side -k kustomize/install/default

This will create the postgres-operator namespace and install the operator resources there.

2-Install monitoring

kubectl apply -k kustomize/monitoring

This will install the dedicated Graphana instance to monitor all operator deployed databases.

The deployment includes a web services which can be exposed via an ingress or accessed via port forwarding.

3-Deploy a database

There are many configuration options to enable. This is the one I use for Mayan EDMS.

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: mayan-edms-testing
spec:
  backups:
    pgbackrest:
      global:
        repo1-retention-full: "14"
        repo1-retention-full-type: time
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-2
      manual:
        repoName: repo1
        options:
         - --type=full
           --compress-type=bz2
      repos:
      - name: repo1
        schedules:
          full: "0 0 * * 0"
          differential: "0 0 * * *"
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 2Gi
  #image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.9-2
  instances:
    - affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: mayan-edms-testing
                  postgres-operator.crunchydata.com/instance-set: instance1
      dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
      minAvailable: 1
      name: instance1
      replicas: 3
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.3.0-0
  patroni:
    dynamicConfiguration:
      synchronous_mode: true
      postgresql:
        parameters:
          max_parallel_workers: 16
          max_worker_processes: 16
          shared_buffers: 512MB
          work_mem: 16MB
          synchronous_commit: "on"
      synchronous_mode: true
      synchronous_mode_strict: true
  postgresVersion: 13
  proxy:
    pgBouncer:
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: mayan-edms-testing
                  postgres-operator.crunchydata.com/role: pgbouncer
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.17-5
      minAvailable: 1
      replicas: 3

This operator resource will deploy a database with the following characteristics:

• 3 replicas with pod anti-affinity to improve the distribution over the nodes.
• 3 connection pooling pods with pod anti-affinity for the same purpose.
• Automated backup, a fill backup on Sundays at 12am, differential backups at 12am every other day except Sundays. Retention period of 14 days.
• Manual backups set to full and compressed with BZip2.
• Synchronous replication to ensure data integrity over availability.
• 4x the default PostgreSQL resources for some settings.

4-Data persistency

After deployment database volumes need to be marked as "retain" to avoid losing data if the database resource is deleted.

kubectl get pvc -n mayan-edms-testing --selector=postgres-operator.crunchydata.com/cluster=mayan-edms-testing

kubectl patch pv pvc-aef7ee64-4495-4813-b896-8a67edc53e58  -p '{"spec":{"persistentVolumeReclaimPolicy":"Retain"}}'

This must be done for all persistent volumes of the database and the backups.

5-Obtaining credentials

If the application does not support secrets, the credentials must be obtained from the secrets.

PG_CLUSTER_USER_SECRET_NAME=mayan-edms-testing-pguser
PG_CLUSTER_NAMESPACE=mayan-edms-testing

PGPASSWORD=$(kubectl get secrets -n "${PG_CLUSTER_NAMESPACE}" "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.password | base64decode}}') \
PGUSER=$(kubectl get secrets -n "${PG_CLUSTER_NAMESPACE}" "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.user | base64decode}}') \
PGDATABASE=$(kubectl get secrets -n "${PG_CLUSTER_NAMESPACE}" "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.dbname | base64decode}}') \
;echo "${PGUSER}:${PGPASSWORD}@${PGDATABASE}"