Add Postgres Dashboards using Prometheus source

[fryorcraken]

[fryorcraken]

I have also tried to get some shard stats via prometheus but can't seem to make the pg-export config work:

pg_tb_messages_by_pubsub:
  query: |
    select pubsubtopic, count(*) AS messages FROM (SELECT id, array_agg(pubsubtopic ORDER BY pubsubtopic) AS pubsubtopic FROM messages GROUP BY id) sub GROUP BY pubsubtopic ORDER BY pubsubtopic;
  metrics:
    - metric_name: pubsub
      type: counter
      value_label: operation
      values:
        - pubsubtopic
        - messages

postgres-exporter_1  | ts=2024-01-12T03:12:07.338Z caller=postgres_exporter.go:647 level=info msg="Semantic version changed" server=postgres:5432 from=0.0.0 to=15.4.0
postgres-exporter_1  | ts=2024-01-12T03:12:07.339Z caller=postgres_exporter.go:674 level=error msg="Failed to reload user queries" path=/etc/pgexporter/queries.yml err="yaml: unmarshal errors:\n  line 279: cannot unmarshal !!str `pubsub` into main.MappingOptions\n  line 280: cannot unmarshal !!str `counter` into main.MappingOptions\n  line 281: cannot unmarshal !!str `operation` into main.MappingOptions\n  line 283: cannot unmarshal !!seq into main.MappingOptions"

Only documentation I was able to find is in https://github.com/burningalchemist/sql_exporter/blob/master/documentation/sql_exporter.yml

Alternatively, we could configure Postgres as a source in Grafana directly.

[Ivansete-status]

I've replied in a discord thread but I'm adding a comment so that it is not lost amongst all the Discord theads :)

This worked to me:

pg_tb_messages_by_pubsub:
  query: |
    select pubsubtopic, count(*) AS messages FROM (SELECT id, array_agg(pubsubtopic ORDER BY pubsubtopic) AS pubsubtopic FROM messages GROUP BY id) sub GROUP BY pubsubtopic ORDER BY pubsubtopic;
  metrics:
    - pubsubtopic:
        usage: "LABEL"
        description: "pubsubtopic"
    - messages:
        usage: "COUNTER"
        description: "Number of messages for the given pubsub topic"

nwaku-monitoring-1705051300888.json

[Ivansete-status]

@fryorcraken - I made a force-push after resolving conflicts in the nwaku-monitoring.json and adding the panel that you added originally. With that, we continue adding the following panel to the dashboard: