error while serving metrics

[erezo9]

Hi, I have installed postgres-exporter via chart and i get this error while going to the metrics endpoint

n error has occurred while serving metrics:

collected metric "pg_stat_activity_process_idle_seconds" { label:<name:"application_name" value:"" > label:<name:"server" value:"SErvername:ip" > histogram:<sample_count:1 sample_sum:0 bucket:<cumulative_count:2 upper_bound:1 > bucket:<cumulative_count:2 upper_bound:2 > bucket:<cumulative_count:2 upper_bound:5 > bucket:<cumulative_count:2 upper_bound:15 > bucket:<cumulative_count:2 upper_bound:30 > bucket:<cumulative_count:2 upper_bound:60 > bucket:<cumulative_count:2 upper_bound:90 > bucket:<cumulative_count:2 upper_bound:120 > bucket:<cumulative_count:2 upper_bound:300 > > } was collected before with the same name and label values

[SuperQ]

For questions/help/support please use our community channels. There are more people available to potentially respond to your request and the whole community can benefit from the answers provided.

[LH8PPL]

It happens because he is using "auto-discover-databases", and the postgres exporter rans the same query on multiple databases. As I see it, You may need to add additional labels to make each collection metric more unique. But I don't know how to do that! @SuperQ how do you do that? remove the auto-discover-databases and use the extend.query-path with the queries.yaml? If so, how?

[SuperQ]

Sorry, I don't know. There are a number of broken features in this exporter that need to be fixed. Most of them because it doesn't follow best practices for exporters.

[SuperQ]

@erezo9 This exporter is meant to monitor PostgreSQL servers, not data in databases.

You probably want to use something like the sql_exporter.

[erezo9]

@SuperQ so what does the auto discover databases suppose to be used for?

[SuperQ]

I have no idea, I have never used this feature.

[d-mankowski-synerise]

Basically this error means that exporter tries to fetch this metric: pg_stat_activity_process_idle_seconds multiple times, even though there is no need for it (as it is database-independent, i.e. it is a per server metric, not per datatabase). This is what master: true is for - to fetch some metrics only once, because otherwise, you get the above error, when setting PG_EXPORTER_AUTO_DISCOVER_DATABASES to true.

So, for example:

pg_replication:
  query: "SELECT CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE GREATEST (0, EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))) END AS lag"
  master: true
  metrics:
    - lag:
        usage: "GAUGE"
        description: "Replication lag behind master in seconds"

You need to use master: true here, because metrics regarding replication are per server, not per database.

But in case of

pg_stat_user_tables:
  query: |
   SELECT
     current_database() datname,
     schemaname,
     relname,
     seq_scan,
     seq_tup_read,
     idx_scan,
     idx_tup_fetch,
     n_tup_ins,
     n_tup_upd,
     n_tup_del,
     n_tup_hot_upd,
     n_live_tup,
     n_dead_tup,
     n_mod_since_analyze,
     COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum,
     COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum,
     COALESCE(last_analyze, '1970-01-01Z') as last_analyze,
     COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze,
     vacuum_count,
     autovacuum_count,
     analyze_count,
     autoanalyze_count
   FROM
     pg_stat_user_tables
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of current database"
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - seq_scan:
        usage: "COUNTER"
        description: "Number of sequential scans initiated on this table"
    ...

you don't need (or want) to use master: true here, because information regarding tables are per database.

Note, that if you don't enable databases auto-discovery feature, then metrics (regarding tables, for example) will be scrapped only for the default postgres database. And with auto-discovery disabled, you don't need to use master: true at all, as it is redundant.

Hope that clarifies stuff a bit - at least this is how I understand this, based upon my observation how exporter behaves.

[gaima8]

@d-mankowski-synerise thanks very much, that sorted this problem for us. One note, you wrote not wanting metrics: true in each of the penultimate 2 paragraphs when you meant master: true. Simple typo thing you were right earlier in your post.

[d-mankowski-synerise]

@d-mankowski-synerise thanks very much, that sorted this problem for us. One note, you wrote not wanting metrics: true in each of the penultimate 2 paragraphs when you meant master: true. Simple typo thing you were right earlier in your post.

Thanks, fixed the typo