auto-discover-databases duplicated metrics should tell the user to add "master: true" in queries.yaml

What did you do?

Enabled auto-discover-databases with 0.12.0 with queries from official queries.yaml file

What did you expect to see?

Having all db related metrics like pg_stat_user_tables

What did you see instead? Under which circumstances?

no more PG metrics at all, just errors when I did call the metrics endpoint, see logs at the end

Environment

• System information:

Linux 5.15.0-56-generic x86_64

• postgres_exporter version:

Mar 30 08:47:44 postgresql00-data-ovh docker[2557848]: v0.12.0: Pulling from prometheuscommunity/postgres-exporter
Mar 30 08:47:44 postgresql00-data-ovh docker[2557848]: Digest: sha256:f34d50a64a4d558ad118ffc73be45a359ac8f30b8daba4b241458bcb9f94e254
Mar 30 08:47:44 postgresql00-data-ovh docker[2557848]: Status: Image is up to date for quay.io/prometheuscommunity/postgres-exporter:v0.12.0
Mar 30 08:47:44 postgresql00-data-ovh docker[2557848]: quay.io/prometheuscommunity/postgres-exporter:v0.12.0

• postgres_exporter flags:

--cap-drop ALL -e DATA_SOURCE_NAME=postgresql://pgautofailover_replicator@postgresql00.data.ovh.int.kaiko.com:5432/postgres?sslmode=disable --net host --read-only --user nobody --volume=/etc/postgres-exporter/config.yaml:/config.yaml --volume=/etc/postgres-exporter/queries.yaml:/queries.yaml quay.io/prometheuscommunity/postgres-exporter:v0.12.0 --config.file=/config.yaml --extend.query-path=/queries.yaml

• PostgreSQL version:

  13.10

• Logs:

72 error(s) occurred:
* collected metric "pg_process_idle_seconds" { label:<name:"application_name" value:"" > label:<name:"server" value:"postgresql01.data.ovh.int.kaiko.com:5432" > histogram:<sample_count:13 sample_sum:141 bucket:<cumulative_count:4 upper_bound:1 > bucket:<cumulative_count:4 upper_bound:2 > bucket:<cumulative_count:4 upper_bound:5 > bucket:<cumulative_count:14 upper_bound:15 > bucket:<cumulative_count:14 upper_bound:30 > bucket:<cumulative_count:14 upper_bound:60 > bucket:<cumulative_count:14 upper_bound:90 > bucket:<cumulative_count:14 upper_bound:120 > bucket:<cumulative_count:14 upper_bound:300 > > } was collected before with the same name and label values
* collected metric "pg_process_idle_seconds" { label:<name:"application_name" value:"DataGrip 2021.3.1" > label:<name:"server" value:"postgresql01.data.ovh.int.kaiko.com:5432" > histogram:<sample_count:1 sample_sum:71 bucket:<cumulative_count:0 upper_bound:1 > bucket:<cumulative_count:0 upper_bound:2 > bucket:<cumulative_count:0 upper_bound:5 > bucket:<cumulative_count:0 upper_bound:15 > bucket:<cumulative_count:0 upper_bound:30 > bucket:<cumulative_count:0 upper_bound:60 > bucket:<cumulative_count:1 upper_bound:90 > bucket:<cumulative_count:1 upper_bound:120 > bucket:<cumulative_count:1 upper_bound:300 > > } was collected before with the same name and label values
* collected metric "pg_process_idle_seconds" { label:<name:"application_name" value:"champi" > label:<name:"server" value:"postgresql01.data.ovh.int.kaiko.com:5432" > histogram:<sample_count:17 sample_sum:1 bucket:<cumulative_count:16 upper_bound:1 > bucket:<cumulative_count:17 upper_bound:2 > bucket:<cumulative_count:17 upper_bound:5 > bucket:<cumulative_count:17 upper_bound:15 > bucket:<cumulative_count:17 upper_bound:30 > bucket:<cumulative_count:17 upper_bound:60 > bucket:<cumulative_count:17 upper_bound:90 > bucket:<cumulative_count:17 upper_bound:120 > bucket:<cumulative_count:17 upper_bound:300 > > } was collected before with the same name and label values
...

The why :

Official queries.yaml has a query that could only be ran once (on 1 DB only) or would create duplicates.
So, it should have "master: true" added

diff --git a/queries.yaml b/queries.yaml
index 6f2008c..f906ae6 100644
--- a/queries.yaml
+++ b/queries.yaml
@@ -254,6 +254,7 @@ pg_process_idle:
       ARRAY_AGG(bucket) AS seconds_bucket
     FROM metrics JOIN buckets USING (application_name)
     GROUP BY 1, 2, 3
+  master: true
   metrics:
     - application_name:
         usage: "LABEL"

So, first, this line should be added to queries.yaml
Second, it could be good to tell the user that if that error occurs on custom queries, he should add that line to their problematic query

Start with the pg_process_idle query in queries.yaml and review how auto-discovered databases execute queries marked with master. Add the requested master setting there, then inspect the existing duplicate-metric error path to determine where custom-query guidance belongs. Done means the official query no longer produces duplicate metrics and users are told how to address the same error in custom queries.