SQL Exporter: Error Collecting Metrics from Multiple PostgreSQL Databases

[ludatos]

Hello everyone,

I'm using the burningalchemist/sql_exporter Docker image to collect metrics from my PostgreSQL database and expose them to Prometheus. However, I'm encountering an issue where sql-exporter reports duplicate metrics errors.

Configuration Details

SQL Exporter Configuration (sql-exporter.yml):

global:
  scrape_timeout_offset: 500ms
  min_interval: 0s
  max_connections: 3
  max_idle_connections: 3

jobs:
  - job_name: db_targets
    collectors: [table_sizes]
    static_configs:
      - targets:
          pg_dev: "postgres://username:password@host1:port1/dbname1?sslmode=disable"
        labels:
          env: "dev"
          db_instance: "pg_dev"
      - targets:
          pg_recette: "postgres://username:password@host2:port2/dbname2?sslmode=disable"
        labels:
          env: "staging"
          db_instance: "pg_staging"

Collector Configuration (table_sizes.collector.yml):

collector_name: table_sizes

metrics:
  - metric_name: pg_table_sizes
    type: gauge
    help: "Size of tables"
    key_labels:
      - table_name
    values: [total_size]
    query: |
      SELECT
        relname AS table_name,
        pg_total_relation_size(relid) AS total_size
      FROM
        pg_catalog.pg_statio_user_tables
      ORDER BY
        total_size DESC;

Error Message

Despite the configuration, I keep encountering the following error in the sql-exporter logs:

ts=2024-10-10T17:03:05.842Z caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: collected metric \"pg_table_sizes\" { label:{name:\"job\"  value:\"db_targets\"}  label:{name:\"table_name\"  value:\"my_table\"}  label:{name:\"target\"  value:\"pg_dev\"}  gauge:{value:8192}} was collected before with the same name and label values"

Steps Taken

1. Removed Duplicate Labels: Ensured that labels are not duplicated in the configuration.
2. Purged Prometheus Data: Stopped Prometheus, deleted old data, and restarted Prometheus.
3. Restarted SQL Exporter: Restarted the SQL Exporter service with the updated configuration.

Need

I need a complete example of how to configure sql-exporter to collect metrics from multiple PostgreSQL databases without encountering duplicate metric errors.

Questions

1. Can someone provide a complete example configuration for sql-exporter that collects metrics from multiple PostgreSQL databases?
2. How can I ensure the uniqueness of metrics collected from multiple databases?
3. Are there any best practices for configuring SQL Exporter to avoid these issues?

Any help or suggestions would be greatly appreciated. Thank you!

[keithf4]

When you're collecting per-database metrics like this (the same metric but across different databases) you have to add something else into the metric labels that makes it different for each database you're connecting to. Easiest way with something like you have here (table size) is to use the current_database() function as an additional column. See example here:

https://github.com/CrunchyData/pgmonitor-extension/blob/main/sql/matviews/matviews.sql#L34

Also recommend including the schema to avoid similar issues with the same table in different schemas

[ludatos]

Thank you so much for your help and suggestions. I implemented the changes using the current_database() function and included the schema in the query as recommended. This has resolved the duplicate metrics errors I was encountering.

Here is the updated configuration for anyone facing a similar issue:

collector_name: table_sizes

metrics:
  - metric_name: pg_table_sizes
    type: gauge
    help: "Size of tables"
    key_labels:
      - table_name
      - schema_name
      - database_name
    values: [total_size]
    query: |
      SELECT
        schemaname AS schema_name,
        relname AS table_name,
        pg_total_relation_size(relid) AS total_size,
        current_database() AS database_name
      FROM
        pg_catalog.pg_statio_user_tables
      ORDER BY
        total_size DESC;