support exporting metrics like pgbouncer

[DeoLeung]

Is your feature request related to a problem? Please describe.
we are using spreaker/prometheus-pgbouncer-exporter to monitor our pgbouncers, it will be great if pgcat has compatible metrics

Describe the solution you'd like
could be scrape by prometheus exporters, typically existing pgbouncer exporters

Describe alternatives you've considered
use general postgres exporter and manually craft the stat sql

[chhetripradeep]

Can i pick this issue and add a prometheus compatible metrics endpoint.

[levkk]

I'm not too familiar with how Prometheus does this. Is this more than having an admin database (pgbouncer and pgcat) with commands that can show the stats?

Currently, PgCat supports a decent amount of statistics like SHOW POOLS, SHOW STATS, SHOW LISTS, etc. I used Datadog as my reference for implementing the admin queries at the time, so if Prometheus needs more (I believe PgBouncer supports a few more as well), I think that's a good idea.

psql -d pgcat -c 'SHOW STATS'
psql -d pgbouncer -c 'SHOW STATS'
psql -d pgbouncer -c 'SHOW POOLS'

[danielhoherd]

@levkk the way prometheus gets its metrics is by periodically scraping an HTTP endpoint that has the metrics listed in a specific format. If you click in to this demo instance https://prometheus.demo.do.prometheus.io/targets and click on one of the endpoints you'll see how the data is exposed.

EG:

# HELP alertmanager_alerts How many alerts by state.
# TYPE alertmanager_alerts gauge
alertmanager_alerts{state="active"} 1
alertmanager_alerts{state="suppressed"} 0
# HELP alertmanager_alerts_invalid_total The total number of received alerts that were invalid.
# TYPE alertmanager_alerts_invalid_total counter
alertmanager_alerts_invalid_total{version="v1"} 0
alertmanager_alerts_invalid_total{version="v2"} 1
# HELP alertmanager_alerts_received_total The total number of received alerts.
# TYPE alertmanager_alerts_received_total counter
alertmanager_alerts_received_total{status="firing",version="v1"} 334117
alertmanager_alerts_received_total{status="firing",version="v2"} 533196
alertmanager_alerts_received_total{status="resolved",version="v1"} 0
alertmanager_alerts_received_total{status="resolved",version="v2"} 0

Sometimes this data is exposed by the process that is serving the service that is being monitored, and sometimes the data is exposed by a secondary service, usually called <servicename>-exporter, eg: https://github.com/prometheus-community/pgbouncer_exporter. It seems like what you are talking about with the psql commands in the prior comment, this might be possible to do as a secondary service.

Also, to see the real value you'd get from exporting metrics, check out grafana and alertmanager here: https://demo.do.prometheus.io, or this one that is a much better demo of grafana https://play.grafana.org/

[levkk]

Seems straight forward to implement, e.g. HTTP endpoint (probably with hyper, we use Tokio already) which would fetch the metrics with crate::stats::get_stats() and output them as text formatted as Prometheus would expect. I'm a bit busy with something else at the moment, so please submit a PR and I'll be happy to review it.

[dat2]

@chhetripradeep @levkk I've created a PR for this in #107. Feel free to review and leave any comments!

[levkk]

Thank you very much for your contribution. Welcome to the project!