Welcome to the postgres_exporter wiki!

Sample queries for Grafana from postgres_exporter that might be useful:

You will want to update to your filters (inside {}).

# of connections: pg_stat_database_numbackends{job="prodpg-exporter"}

# of locks (by lock type) for a specific DB: pg_locks_count{job="prodpg-exporter",datname="MYDB"}
legend_format: {{mode}}

Cache hit rate by database: rate(pg_stat_database_blks_hit{job="prodpg-exporter"}[5m])
legend_format: {{datname}}

disk reads per sec by database: rate(pg_stat_database_blks_read{instance="proddb"}[5m])
legend_format: {{datname}}

You can have the exporter run custom queries by setting the PG_EXPORTER_EXTEND_QUERY_PATH environment variable and pointing it to a .yaml file.

When specifying queries.yml, use the following format as defined here:

https://github.com/prometheus-community/postgres_exporter/blob/2a5692c0283fddf96e776cc73c2fc0d5caed1af6/cmd/postgres_exporter/queries.go#L26-L32

metric_name:
  master: true # (optional)
  cache_seconds: 30 # (optional)
  query: "SELECT metric_1, metric_2 FROM table"
  metrics:
    - metric_1:
        usage: "LABEL"
        description: "Metric 1 description"
    - metric_2:
        usage: "GAUGE"
        description: "Metric 2 description"

Metrics use the format defined here: https://github.com/prometheus-community/postgres_exporter/blob/2a5692c0283fddf96e776cc73c2fc0d5caed1af6/cmd/postgres_exporter/postgres_exporter.go#L69C1-L75

• usage: string
• description: string
• metric_mapping: map[string]float64, Optional column mapping for MAPPEDMETRIC
• pg_version: Semantic version ranges which are supported. Unsupported columns are not queried (internally converted to DISCARD).

Usage must be one of these values: https://github.com/prometheus-community/postgres_exporter/blob/2a5692c0283fddf96e776cc73c2fc0d5caed1af6/cmd/postgres_exporter/postgres_exporter.go#L32-L51

• DISCARD ignores a column
• LABEL identifies a column as a label
• COUNTER identifies a column as a counter
• GAUGE identifies a column as a gauge
• MAPPEDMETRIC identifies a column as a mapping of text values
• DURATION identifies a column as a text duration (and converted to milliseconds)
• HISTOGRAM identifies a column as a histogram