search

prometheus-community / postgres_exporter

A PostgreSQL metric exporter for Prometheus
Apache License 2.0
2.81k stars 742 forks source link

collected metric "pg_stat_activity_process_idle_seconds_sum" was collected before with the same name and label values #479

Open reslavath opened 3 years ago

reslavath commented 3 years ago

Hello everyone,

I'm currently seeing below error in my VM. Can someone please help me out.

postgres_exporter package : postgres_exporter_v0.8.0_linux-amd64 OS : Red Hat Enterprise Linux release 8.3 (Ootpa) PostgreSQL_version : PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit PostgreSQL_user_privilege : SUPER USER

redacted the IP

curl -s http://10.11.12.13:9187/metrics
An error has occurred while serving metrics:

14 error(s) occurred:
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:5 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:5 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:4 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:3 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:2 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:1 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_sum" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:0 > } was collected before with the same name and label values
* collected metric "pg_stat_activity_process_idle_seconds_count" { label:<name:"application_name" value:"" > label:<name:"server" value:"10.11.12.13:6524" > untyped:<value:7 > } was collected before with the same name and label values
postgres=#    WITH
      metrics AS (
        SELECT
          application_name,
          SUM(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))::bigint)::float AS process_idle_seconds_sum,
          COUNT(*) AS process_idle_seconds_count
        FROM pg_stat_activity
        WHERE state = 'idle'
        GROUP BY application_name
      ),
      buckets AS (
        SELECT
          application_name,
          le,
          SUM(
            CASE WHEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) <= le
              THEN 1
              ELSE 0
            END
          )::bigint AS bucket
        FROM
          pg_stat_activity,
          UNNEST(ARRAY[1, 2, 5, 15, 30, 60, 90, 120, 300]) AS le
        GROUP BY application_name, le
        ORDER BY application_name, le
      )
    SELECT
      application_name,
      process_idle_seconds_sum,
      process_idle_seconds_count,
      ARRAY_AGG(le) AS process_idle_seconds,
      ARRAY_AGG(bucket) AS process_idle_seconds_bucket
    FROM metrics JOIN buckets USING (application_name)
    GROUP BY 1, 2, 3;
 application_name | process_idle_seconds_sum | process_idle_seconds_count |    process_idle_seconds     | process_idle_seconds_bucket
------------------+--------------------------+----------------------------+-----------------------------+-----------------------------
                  |                       80 |                          8 | {1,2,5,15,30,60,90,120,300} | {0,0,0,8,8,8,8,8,8}
(1 row)

postgres=#
zoonage commented 3 years ago

Are you running with the auto discover databases turned on? If so could be related to #429

reslavath commented 3 years ago

after removing "--auto-discover-databases " in POSTGRES_EXPORTER_OPTS I don't see any errors. . thanks for the help.

root@hostname[DEV][tmp] # cat /usr/lib/systemd/system/postgres_exporter.service
# -*- mode: conf -*-

[Unit]

Description=Prometheus exporter for PostgreSQL server metrics.
Documentation=https://github.com/wrouesnel/postgres_exporter
After=network.target

[Service]
EnvironmentFile=/etc/default/postgres_exporter
ExecStart=/usr/bin/postgres_exporter $POSTGRES_EXPORTER_OPTS
Restart=on-failure

[Install]
WantedBy=multi-user.target

root@hostname[DEV][tmp] # cat /etc/default/postgres_exporter
POSTGRES_EXPORTER_OPTS="  --exclude-databases=template0,template1 --web.listen-address=0.0.0.0:9187 --extend.query-path=/etc/postgres_exporter/queries.yml --log.level=debug"
DATA_SOURCE_NAME="postgresql://mon_user@10.11.12.13:6524/postgres?sslmode=disable"

root@hostname[DEV][tmp] #
KES777 commented 2 years ago

Seems related to: https://github.com/prometheus-community/postgres_exporter/issues/706