How to Publish custom PSQL query in pgwatch2 into grafana

[vamsichikkam]

Hi,

We are trying to add custom query in pgwatch2. Could you please help us how to reflect the data from all source(s) into influxdb database with historical infromation and publish that into grafana as graphs.

At this time, we can add the query for 1 db in the grafana , how to publish that for all different sources of database(s).

[kmoppel]

Hi!
The process of adding a custom query looks like that:

1. Define the custom query (metric) in one of the below ways:
   a) on Web UI /metrics tab
   b) directly in the pgwatch2 configuration DB pgwatch2.metrics table
   c) in the "pgwatch2/metrics" folder with appropriate path/file naming if using the "file based" approach

2. Start using the new metric
   a) Change some preset config to include your new metric name (with an appropriate interval of course)
   b) Create a new preset config to include your new metric name
   c) Use a "custom config" for your monitored database

3. After some minutes the Gatherer will pick up the config change and you should get some metrics in the Influxdb. Can be check with: "select * from $my_metric_name limit 1" directly from Influx

4. Change Grafana dashboards any way you like to include your metric

[darshansh]

Hi ,
I would like to add postgresql query as custom metric and see it's data on grafana. How I can follow step 2 as you mentioned above ? Could you please explain steps ?

Thanks
Darshan Shah

[darshansh]

Hi!
The process of adding a custom query looks like that:

1. Define the custom query (metric) in one of the below ways:
   a) on Web UI /metrics tab
   b) directly in the pgwatch2 configuration DB pgwatch2.metrics table
   c) in the "pgwatch2/metrics" folder with appropriate path/file naming if using the "file based" approach
2. Start using the new metric
   a) Change some preset config to include your new metric name (with an appropriate interval of course)
   b) Create a new preset config to include your new metric name
   c) Use a "custom config" for your monitored database
3. After some minutes the Gatherer will pick up the config change and you should get some metrics in the Influxdb. Can be check with: "select * from $my_metric_name limit 1" directly from Influx
4. Change Grafana dashboards any way you like to include your metric

Hi ,
I would like to add postgresql query as custom metric and see it's data on grafana. How I can follow step 2 as you mentioned above ? Could you please explain steps ?

I have set up pgwatch2 using docker on my windows machine. It's is perfectly working ok with new features. I have added new metric using admin web url 8080. how to change in preset config in docker ?

Thanks
Darshan Shah

[darshansh]

Hi!
The process of adding a custom query looks like that:

1. Define the custom query (metric) in one of the below ways:
   a) on Web UI /metrics tab
   b) directly in the pgwatch2 configuration DB pgwatch2.metrics table
   c) in the "pgwatch2/metrics" folder with appropriate path/file naming if using the "file based" approach
2. Start using the new metric
   a) Change some preset config to include your new metric name (with an appropriate interval of course)
   b) Create a new preset config to include your new metric name
   c) Use a "custom config" for your monitored database
3. After some minutes the Gatherer will pick up the config change and you should get some metrics in the Influxdb. Can be check with: "select * from $my_metric_name limit 1" directly from Influx
4. Change Grafana dashboards any way you like to include your metric

Hi ,
I would like to add postgresql query as custom metric and see it's data on grafana. How I can follow step 2 as you mentioned above ? Could you please explain steps ?

I have set up pgwatch2 using docker on my windows machine. It's is perfectly working ok with new features. I have added new metric using admin web url 8080. how to change in preset config in docker ?

Thanks
Darshan Shah

I see the steps to change in preset config.
https://github.com/cybertec-postgresql/pgwatch2/blob/master/README.md

Usage (Docker based, for file or ad-hoc based see further below)
by default the "pgwatch2" configuration database running inside Docker is being monitored so that you can immediately see some graphs, but you should add new databases by opening the "admin interface" at 127.0.0.1:8080/dbs or logging into the Postgres config DB and inserting into "pgwatch2.monitored_db" table (db - pgwatch2 , default user/pw - pgwatch2/pgwatch2admin). Note that it can take up to 2min before you see any metrics for newly inserted databases.
one can create new Grafana dashboards (and change settings, create users, alerts, ...) after logging in as "admin" (admin/pgwatch2admin)
metrics (and their intervals) that are to be gathered can be customized for every database by using a preset config like "minimal", "basic" or "exhaustive" (monitored_db.preset_config table) or a custom JSON config.
to add a new metrics yourself (simple SQL queries returing point-in-time values) head to http://127.0.0.1:8080/metrics. The queries should always include a "epoch_ns" column and "tag_" prefix can be used for columns that should be tags (thus indexed) in InfluxDB.
a list of available metrics together with some instructions is also visible from the "Documentation" dashboard
some predefine metrics (cpu_load, stat_statements) require installing helper functions (look into "pgwatch2/sql" folder) on monitored DBs
for effective graphing you want to familiarize yourself with basic InfluxQL and the non_negative_derivative() function which is very handy as Postgres statistics are mostly evergrowing counters. Documentation here.
for troubleshooting, logs for the components are visible under http://127.0.0.1:8080/logs/[pgwatch2|postgres|webui|influxdb|grafana] or by logging into the docker container under /var/logs/supervisor/

[kmoppel]

Hi @darshansh, to start using a custom metric when using default "config DB" based Docker setup, either add the metric to an existing preset or create your our on the "Metric definitions" tab and then making sure from the "DBs" tab the your monitored hosts are using your changed preset. Screenshot:

Other way is to skip using presets and define a "custom JSON config" on the "DBs" page. Screenshot: