New features for Cybertec’s pgwatch2 Postgres monitoring tool

We have written about our hatchling Open Source PostgreSQL monitoring tool called pgwatch2 already some months ago here and here but now I think it deserves another nudge as a lot of new features have found their way into the code.

For our new readers – pgwatch2 is a simple but flexible Postgres montoring tool, aiming to provide a nice balance between features / usability / out-of-the-box experience, concentrating on nice graphs using Grafana. Getting started with pgwatch2 by the way couldn’t be any easier and takes just minutes thanks to Docker – please check the links from above for more details.

So, find below a listing of those new features. Please feel free to check out the new version and let us know on Github if you’re still missing something in the tool or are having difficulties with something! Thanks a lot!

Project Github link – here

List of changes

  • Support for Graphite as metric storing database

Previously only InfluxDB was supported as metrics storing datastore – now when running the pgwatch2 daemon it is possible to specify that metrics should be stored also in Graphite. Use –datastore / –graphite-host / –graphite-port parameters for that. Metrics will be stored in the „pgwatch2“ namespace then. Sadly no out of the box dashboards for Graphite though, so you need to compose them manually, based on Influx ones…

  • SSL support for Grafana and the Web UI

Previously one could really recommend out-of-the-box image only for local / LAN / datacenter usage, but now with SSL support one can expose both the Web UI (configure the gathered metrics and hosts) and Grafana (graphs on gathere metrics) also over the Internet, given you’ll set some strong passwords of course. New self-signed (aka snake oil) SSL cert will generated on every container startup when the below flags are set, so it should be relatively OK.

 docker run ... -e PW2_WEBSSL=1 -e PW2_GRAFANASSL=1 --name pw2 cybertec/pgwatch2 
  • Support for the upcoming PostgreSQL version 10

New SQL text versions for some metrics. Has mostly to do with „*xlog*“ functions renamed to „*wal*“.

  • Support for beta/devel versions of Postgres as well as EDB Postgres Advanced Server

This had actually to do only with reading/transforming the Postgres server version number universally.

  • New „change detection“ feature and according dashboard

When the new „change_events“ metric is enabled, the pgwatch2 daemon will track the signature of tables/views/indexes, stored procedure code and values of server configuration parameters and will log an „change event“ when any changes detected. These events graphed together with the „CPU load“ and „Rollback ratio“ graphs should help to quickly pinpoint problems arising from new schema rollouts or config changes by the DBAs.

pgwatch2_change_detected_dash

  • New stored procedure details dashboard called „Sproc details“
  • New approximate table bloat monitoring metric and panel for „DB Overview“ dashboard (9.5+)
  • New „Queries per Second“ panel (QPS label) and according metric stat_statements_calls for the „DB Overview“ dashboard
  • New option in the Web Admin UI to declare the monitoring user as „superuser“, so that the metrics daemon will try to create the needed „metric fetching helpers“ (CPU load PL/Python sproc and superuser-workaround wrappers for pg_stat_statements and pgstattuple) automatically. This is especially useful for testing or ad hoc usage. For long term monitoring, from security point of view a non-privileged user would be still preferred.
  • Building the Docker image doesn’t need a local installion of Go anymore

The daemon is now complied inside the Docker. This increases of course the image size but makes experimenting and small behaviour adjustments easier.

  • Grafana updated from version 4.2.0 to 4.3.0

Histograms and heatmaps now possible! See demo here.

  • InfluxDB updated from 1.2.2 to 1.2.4
  • README updates

More infos on backup/restore and metrics setup.

Kaarel Moppel
I’ve been interested with databases for the last 10 years, working last 6 years exclusively with PostgreSQL. And still I’m constantly surprised by it’s powerful set of features and the fast pace of development by the globally friendly community. On my spare time I enjoy playing soccer and travelling.