Skip to main content

Postgres on Kubernetes

Overview#

PostgreSQL running in Kubernetes can be monitored in SnappyFlow using two approaches:

PostgreSQL monitoring with sfKubeAgent#

sfKubeAgent is run as a sidecar with the configMap shown below. The config map instantiates plugins for metrics, general logs and slow queries. 

apiVersion: v1 kind: ConfigMap metadata:   name: postgres-configmap data:   config.yaml: |-     key: <profile_key>     metrics:       plugins:       - name: postgres         enabled: true         interval: 60         config:           documentsTypes:    #user can enable all or only needed documents             - databaseDetails             - indexDetails8             - queryDetails             - serverDetails             - tableDetails           host: 127.0.0.1           user: <userName>           password: <password>           port: 5432     logging:       plugins:       - name: postgres-general         enabled: true         config:           log_level:             - error             - warning             - info             - log           log_path: /var/log/postgres/*.log       - name: postgres-slowquery         enabled: true         config:           log_path: /var/log/postgres/*.log

The example of PostgreSQL pod with Postgres and sfKubeAgent containers is shown below: 

kind: Pod apiVersion: v1 metadata:  name: postgres-pod  labels:   snappyflow/appname: <app_name>   snappyflow/projectname: <project_name> spec:  containers:  - name: postgres-container    securityContext: {}    image: "postgres:9.6"    args: ["-c", "log_statement=all", "-c", "log_min_messages=warning", "-c", "log_min_duration_statement=200", "-c","log_directory=/var/log/postgres","-c","log_line_prefix=< %m > ","-c","log_filename=postgresql-%Y-%m-%d_%H%M%S.log","-c","log_truncate_on_rotation=off","-c","log_rotation_age=1d","-c","logging_collector=on"]    imagePullPolicy: IfNotPresent    ports:    - name: tcp      containerPort: 5432      protocol: TCP    env:    - name: POSTGRES_PASSWORD      value: <password>    - name: POSTGRES_USER      value: <userName>    volumeMounts:      - name: varlog        mountPath: /var/log/postgres    # Snappyflow's sfkubeagent container  - name: sfagent-container    image: snappyflowml/sfagent:latest    imagePullPolicy: Always    command:      - /app/sfagent      - -enable-console-log    env:      - name: APP_NAME        value: <app_name>      - name: PROJECT_NAME        value: <project_name>    volumeMounts:      - name: configmap-postgres        mountPath: /opt/sfagent/config.yaml        subPath: config.yaml      - name: varlog        mountPath: /var/log/postgres  volumes:  - name: configmap-postgres    configMap:      name: postgres-configmap  - name: varlog    emptyDir: {}

Viewing data and dashboards#

  • Data generated by plugin can be viewed in “browse data” page inside the respective application under plugin=postgres and documentType= serverDetails, databaseDetails, tableDetails, IndexDetails
  • Dashboard for this data can be instantiated by Importing dashboard template PostgreSQL to the application dashboard

PostgreSQL monitoring with Prometheus#

Refer to Prometheus Exporter overview to understand how SnappyFlow monitors using Prometheus exporters.

Pre-requisites#

  • Prometheus exporter is deployed as a side-car in the application container and the exporter port is accessible to sfPod

Configurations#

kind: Pod apiVersion: v1 metadata:  name: postgres-pod  labels:   snappyflow/appname: <app_name>   snappyflow/projectname: <project_name>   snappyflow/component: postgresql spec:  containers:  - name: postgres-exporter    image: bitnami/postgres-exporter    ports:    - name: pg-exporter      containerPort: 9187    command: ["/bin/sh", "-c"]    args: ['DATA_SOURCE_NAME="postgresql://<user_name>:<password>@localhost:5432/<dbname>?sslmode=disable" /opt/bitnami/postgres-exporter/bin/postgres_exporter']  - name: postgres-container    securityContext: {}    image: "postgres:9.6"    args: ["-c", "log_statement=all", "-c", "log_min_messages=warning", "-c", "log_min_duration_statement=200", "-c","log_line_prefix=< %m > "]    imagePullPolicy: IfNotPresent    ports:    - name: tcp      containerPort: 5432      protocol: TCP    env:    - name: POSTGRES_PASSWORD      value: <password>    - name: POSTGRES_USER      value: <user_name>    - name: POSTGRES_DB      value: <dbname>

Viewing data and dashboards#

  • Data generated by plugin can be viewed in browse data page inside the respective application under plugin=kube-prom-postgres and documentType= psql
  • Dashboard for this data can be instantiated by Importing dashboard template PostgreSQL_Prom to the application dashboard

PostgreSQL Pod Centralized Logging#

Pls refer to Centralized Logging Overview to understand how SnappyFlow implements centralized logging Centralized logging approach requires the application pod to stream logs to stdout, which is achieved by running a busy box container as shown below. 

kind: PodapiVersion: v1metadata:  name: postgres-pod  labels:    snappyflow/appname: <app_name>    snappyflow/projectname: <project_name>    snappyflow/component: postgresqlspec:  containers:    - name: postgres-exporter      image: bitnami/postgres-exporter      ports:        - name: pg-exporter          containerPort: 9187      command:        - /bin/sh        - '-c'      args:        - >-          DATA_SOURCE_NAME="postgresql://<user_name>:<password>@localhost:5432/<dbname>?sslmode=disable"          /opt/bitnami/postgres-exporter/bin/postgres_exporter    - name: postgres-container      securityContext: {}      image: 'postgres:9.6'      args:        - '-c'        - log_statement=all        - '-c'        - log_min_messages=warning        - '-c'        - log_min_duration_statement=200        - '-c'        - 'log_line_prefix=< %m > '        - '-c'        - log_directory=/var/log/postgres        - '-c'        - log_filename=postgresql.log        - '-c'        - logging_collector=on      imagePullPolicy: IfNotPresent      ports:        - name: tcp          containerPort: 5432          protocol: TCP      env:        - name: POSTGRES_PASSWORD          value: <password>        - name: POSTGRES_USER          value: <user_name>        - name: POSTGRES_DB          value: <dbname>      volumeMounts:        - name: postgres-log          mountPath: /var/log/postgres    - name: postgres-general      image: busybox      command:        - /bin/sh        - '-c'      args:        - tail -n+1 -f /var/log/postgres/*.log      volumeMounts:        - name: postgres-log          mountPath: /var/log/postgres  volumes:    - name: postgres-log      emptyDir: {}