PostgreSQL OpenTelemetry Assets

PostgreSQL is an open-source object-relational database management system known for its extensibility, standards compliance, and reliability. It handles ACID-compliant transactions with MVCC, making it suitable for concurrent workloads ranging from single-node deployments to large-scale OLTP/OLAP systems.

This content pack provides dashboards, alert rules, and SLO templates for PostgreSQL monitoring. The assets use metrics and events from the OpenTelemetry PostgreSQL receiver (postgresqlreceiver) and cover connection capacity, transaction throughput, query performance, I/O health, lock contention, and active query analysis.

The PostgreSQL OpenTelemetry assets have been tested with:

• OpenTelemetry PostgreSQL receiver v0.145.0
• PostgreSQL 16.13

You need Elasticsearch for storing and searching your data and Kibana for visualizing and managing it. You can use our hosted Elasticsearch Service on Elastic Cloud, which is recommended, or self-manage the Elastic Stack on your own hardware.

Before collecting data, you must configure PostgreSQL to allow the collector to connect and gather metrics. Create a dedicated user with read access to monitoring views:

CREATE USER otel WITH PASSWORD 'your_password';
GRANT pg_monitor TO otel;
GRANT SELECT ON pg_stat_database TO otel;
GRANT SELECT ON pg_stat_activity TO otel;
		

For top-query statistics (db.server.top_query events) and query samples (db.server.query_sample), enable the pg_stat_statements extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
		

Grant the monitoring user access to pg_stat_statements:

GRANT SELECT ON pg_stat_statements TO otel;
		

Configure the OpenTelemetry Collector (or Elastic OpenTelemetry Collector) to receive PostgreSQL metrics and events and export them to Elasticsearch. The following example uses the postgresqlreceiver with the elasticsearch/otel exporter.

Placeholders

receivers:
  postgresql:
    endpoint: <POSTGRES_ENDPOINT>
    username: <POSTGRES_USER>
    password: ${env:POSTGRES_PASSWORD}
    databases: [postgres]
    # Omit databases or list multiple to scrape all desired databases
    metrics:
      postgresql.blks_hit:
        enabled: true
      postgresql.blks_read:
        enabled: true
      postgresql.database.locks:
        enabled: true
      postgresql.deadlocks:
        enabled: true
      postgresql.sequential_scans:
        enabled: true
      postgresql.temp_files:
        enabled: true
      postgresql.temp.io:
        enabled: true
      postgresql.tup_deleted:
        enabled: true
      postgresql.tup_fetched:
        enabled: true
      postgresql.tup_inserted:
        enabled: true
      postgresql.tup_returned:
        enabled: true
      postgresql.tup_updated:
        enabled: true
      postgresql.wal.delay:
        enabled: true
      postgresql.function.calls:
        enabled: true
    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: 200
      max_explain_each_interval: 1000
      query_plan_cache_size: 1000
      query_plan_cache_ttl: 1h

exporters:
  elasticsearch/otel:
    endpoints: [<ES_ENDPOINT>]
    mapping:
      mode: otel

service:
  pipelines:
    metrics/postgresql:
      receivers: [postgresql]
      exporters: [elasticsearch/otel]
    logs/postgresql:
      receivers: [postgresql]
      exporters: [elasticsearch/otel]
		

Refer to the metadata.yaml of the OpenTelemetry PostgreSQL receiver for details on available metrics.

The postgresqlreceiver emits event logs from pg_stat_activity (db.server.query_sample) and pg_stat_statements (db.server.top_query). Events are stored in logs-postgresqlreceiver.otel-* and distinguished by the event_name field. Query samples include real-time execution time, backend state, and wait events; top-query events provide aggregated statistics per normalized query.

This integration includes one or more Kibana dashboards that visualizes the data collected by the integration. The screenshots below illustrate how the ingested data is displayed.

← →

×

×

×

×

×

×

×