migration from postgres_exporter

[kevinwincott]

are there any files to help with migrating from

https://github.com/prometheus-community/postgres_exporter

im using the original supplied queries file

prometheus-community/postgres_exporter@fe960c6#diff-996ccb161dc17b823796091b615a7a170f52ec79eaa4f69093a519498d1356f6

but cannot seem to replicate the queries for the same data

[burningalchemist]

Hey @kevinwincott, this project is developed independently from postgres_exporter, so I'm afraid there's no migration guide.

Could you please share the details what exactly doesn't work, and I'll try to help. The format is different indeed. But we can pick one of the queries, and I guess you can proceed with the rest.

[burningalchemist]

@kevinwincott For instance, the metric below:

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"

might be configured as follows:

metrics:
      - metric_name: pg_replication
        type: gauge
        help: 'Total number of times the transaction log has been expanded since last restart, per database.'
        values: [lag]
        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

If you'd like to populate labels from other columns, you need to use key_labels field and specify columns one by one.

[ridvankorkmaz]

Hey, sorry for making this question alive again,

Do you know to to migrate following postgresql_exporter metric to sql_exporter. Indeed apart from the example below, the real question is:

How to define HELP and TYPE for every metric column? (even is it possible?)

autovacuum:
    query: >-
        select datname, extract(epoch from now() - min(xact_start)) as age, count(*) as cnt
        from pg_stat_activity
        where query ~ '^autovacuum: '
          and state <> 'idle'
          and pid <> pg_backend_pid()
          and not pg_is_in_recovery()
        group by 1
    metrics:
        - datname:
            usage: "LABEL"
            description: "Database name"
        - age:
            usage: "GAUGE"
            description: "Age of the oldest autovacuum process"
        - cnt:
            usage: "GAUGE"
            description: "Number of active autovacuum processes running"

[keithf4]

Our project has migrated from postgres_exporter to sql_exporter. I found it easier to use the query_ref option to define the queries once in a separate section and then define the metrics from that query. Can see an example here:

https://github.com/CrunchyData/pgmonitor/blob/development/sql_exporter/common/crunchy_global_collector.yml

The original file from postgres_exporter that this is based on is here

https://github.com/CrunchyData/pgmonitor/blob/development/postgres_exporter/common/queries_global.yml