Postgesql Monitoring With OpenTelemetry

OpenTelemetry collector has postgresql receiver able to connect to Postgresql and collect basic metrics and logs.

It connects to stats tables and combines metrics and logs. It can extract cumulative counters and stats for top N query based on number of calls or exec time.

Postgresql Helm Chart

#

helm repo add bitnami https://charts.bitnami.com/bitnami

Configure the database. Create values.yaml:

primary:
  extendedConfiguration: |
    shared_preload_libraries = 'pgaudit,pg_stat_statements'

Install the chart:

helm install pgdb bitnami/postgresql -n pgdb --create-namespace -f values.yaml

Create Sample Database

#

create database oteltest;

\c oteltest

CREATE TABLE test_data ( id SERIAL PRIMARY KEY, value TEXT ); 

INSERT INTO test_data (value) SELECT 'test-' || generate_series(1, 10000); 

GRANT pg_monitor TO postgres;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

OpenTelemetry Collector Config

#

receivers:
    postgresql:
      endpoint: pgdb-postgresql.pgdb.svc.cluster.local:5432
      transport: tcp
      username: postgres
      password: YOUR_PASSWORD
      tls:
        insecure: true

      databases:
        - oteltest

      events:
        db.server.query_sample:
          enabled: true

        db.server.top_query:
          enabled: true
      query_sample_collection:
        max_rows_per_query: 100
    
      top_query_collection:
        max_rows_per_query: 100
        top_n_query: 10
        collection_interval: 60s
        max_explain_each_interval: 0

PromQL Queries

#

Top N queries by calls

#

topk(10,
  sum by (db_query_text) (
    sum_over_time(
      {service_instance_id="pgdb-postgresql.pgdb.svc.cluster.local:5432"}
      | db_system_name="postgresql"
      | db_query_text != ""
      | unwrap postgresql_calls
      [5m]
    )
  )
)

Top N queries by exec time

#

topk(10,
  sum by (db_query_text) (
    sum_over_time(
      {service_instance_id="pgdb-postgresql.pgdb.svc.cluster.local:5432"}
      | db_system_name="postgresql"
      | db_query_text != ""
      | unwrap postgresql_total_exec_time
      [5m]
    )
  )
)