🥇Monitoring PostgreSQL within OpenShift

Good day, residents of Habr!

Today, I want to share how we really wanted to monitor Postgres and a couple of entities within the OpenShift cluster, and how we did it.

We started with:

OpenShift
Helm
Prometheus

Working with the Java application was quite simple and straightforward, to be more precise:

1) Adding to build.gradle

 implementation "io.micrometer:micrometer-registry-prometheus"

2) Starting Prometheus with the configuration

 - job_name: 'job-name'
    metrics_path: '/actuator/prometheus'
    scrape_interval: 5s
    kubernetes_sd_configs:
    - role: pod
      namespaces:
        names: 
          - 'name'

3) Adding a display in Grafana

Everything was quite straightforward until the moment we wanted to monitor the databases that are nearby in the namespace (yes, it's bad, nobody does that, but it happens).

How does it work?

Besides the Pod with Postgres and Prometheus, we also need another entity — the exporter.

An exporter, in abstract terms, is an agent that collects metrics from an application or even server. The Postgres exporter is written in Go and works by executing SQL scripts on the database and then retrieving the results for Prometheus. This also allows you to extend the metrics collected by adding your own.

We deploy it like this (an example of deployment.yaml, not binding):


---
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: postgres-exporter
  labels:
    app: {{ .Values.name }}
    monitoring: prometheus
spec:
  serviceName: {{ .Values.name }}
  replicas: 1
  revisionHistoryLimit: 5
  template:
    metadata:
      labels:
        app: postgres-exporter
        monitoring: prometheus
    spec:
      containers:
      - env:
        - name: DATA_SOURCE_URI
          value: postgresdb:5432/pstgr?sslmode=disable
        - name: DATA_SOURCE_USER
          value: postgres
        - name: DATA_SOURCE_PASS
          value: postgres
        resources:
          limits:
            cpu: 100m
            memory: 50Mi
          requests:
            cpu: 100m
            memory: 50Mi
        livenessProbe:
          tcpSocket:
            port: metrics
          initialDelaySeconds: 30
          periodSeconds: 30
        readinessProbe:
          tcpSocket:
            port: metrics
          initialDelaySeconds: 10
          periodSeconds: 30
        image: exporter
        name: postgres-exporter
        ports:
        - containerPort: 9187
          name: metrics

We also needed a service and image stream for it.

After deployment, we really want everyone to see each other.

We add this piece to the Prometheus config:

  - job_name: 'postgres_exporter'
    metrics_path: '/metrics'
    scrape_interval: 5s
    dns_sd_configs:
    - names:
      - 'postgres-exporter'
      type: 'A'
      port: 9187

And that's when everything started working; we just need to add all this goodness to Grafana and enjoy the results.

In addition to being able to add your own queries, you can also change the settings in Prometheus to collect the necessary metrics more targetedly.

The same approach was taken for: