Beginning of the year happens to be luckily somewhat slow at customers, so last couple of weeks I’ve had the chance to work on our Open Source PostgreSQL monitoring tool called pgwatch2 and implement some stuff that was in the queue. Changes include only a couple of fixes and a lot of new features… so hell, I’m calling it “Feature Pack 3″… as we’ve had some bigger additions in the past also. Read on for a more detailed overview on the most important new stuff.

Becoming “Enterprizy”

Focus word this time could be “Enterprise”. Meaning – firstly trying to make pgwatch2 easier to deploy for larger companies who maybe need to think about security or container orchestration and secondly also adding robustness. Security even got some special attention – now there’s a pgwatch2 version suitable for running on OpenShift, i.e. Docker process runs under an unprivileged user. But there are also quite some new dashboards (see screenshots at the end), like the long-awaited “Top N queries”, that should delight all “standard” users and also some other smaller UI improvements.

And please do let us know on Github if you’re still missing something in the tool or are having difficulties with something – I think myself I’ve lost the ability to look at the tool with beginner eyes. Thus – any feedback would be highly appreciated!

Project Github link – here.

Version 1.3 full changelog – here.

Most important changes for v1.3.0

• “Non-root” Docker support

Suitable for example for OpenShift deployments.

• Explicit Docker volumes added to Postgres, Grafana, InfluxDB and pgwatch2 config

Using volumes was of course also possible before, but people didn’t seem to think about it, so now it’s more explicit. Volumes helps a lot for long term Docker deployments as it makes updating to new image versions quite easy compared to dump/restore.

• Dockerfiles for deploying “non-root” components separately

These can be used to do some advanced orchestrated setups. See the “docker” folder for details. There are also latest images available on Docker Hub, but it’s meant more for DIY still.

• Better Grafana access control vie env. variables

Now possible to turn off anononymous access and set admin user/password.

• New “AWS CloudWatch overview” dashboard

One can now easily monitor/alert on a mix of on-prem and AWS RDS managed DBs.

• New dashboard for PgBouncer statistics

Visualizes PgBouncer “SHOW STATS” commands. NB! Requires config DB schema
change for existing setups as a new “datasource type” field was introduced.

• New dashboard for “Top N” time consuming/frequent/slowest/IO-hungry queries added

Now one can see based on pg_stat_statements info, exactly which queries are the costliest (4 criterias available) over a user selected timeperiod. Lot’s of people have asked for that, so please, enjoy!

• New dashboard – “Biggest relations treemap”

Helps to visually highlight biggest tables/indexes. Uses a custom plugin for Grafana.

• InfluxDB HA support added, i.e. writing metrics to 2 independent DBs

Metrics gathering daemon can now store all gathered metrics in 2 independent InfluxDBs…so kind of homebrewn clustering. For real clustering one needs the commercial Influx version so be sure to check it out also if needing extra robustness, seems like a great product.

• Gatherer daemon robustness – a ringbuffer of max 100k metrics datapoints introduced

Previously if Influx was down metrics were gathered and saved in daemon memory till things blew up. 100k datasets translates into ~ 2GB of RAM.

• InfluxDB metrics retention period now configurable via ENV

Variable is called PW2_IRETENTIONDAYS. Default is 90 days.

• “Change detection” doesn’t expect superuser rights on the monitored DB anymore

• Web UI security – respecting the “only SSL connections” ENV variable also for Postgres

• Web UI security – now possible to disable exposing of component logs

• README improvements – a new section on custom deployments

• README improvements – a new docu on all available ENV variables, per subcomponent

Screenshots of new Dashboards

Stat Statements Top

PgBouncer Stats

Biggest relations

AWS RDS overview