Skip to main content

Postgres in 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: 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 > '
- '-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: {}