Percona Monitoring with Postgres Docker on local environment

You have a Postgres running in Docker container, but don't know how to monitor its active connections. Percona Monitor can be considering for using in this case.

Install PMM

https://docs.percona.com/percona-monitoring-and-management/setting-up/server/docker.html#run-docker-with-volume

To access the web UI with port 80

If you don't want to use the port 443 in local (because it might be complicated to setup the key/cert/pem), can also use port 80.

docker run --detach --restart always \
--publish 8889:80 \
-v pmm-data:/srv \
--name pmm-server \
percona/pmm-server:2

It will map port 8889 of host computer to port 80 on the container

Add new instance to PMM

After login into the tool, we can go to add the instance of DB that we want to do monitoring.

Then, ensure the pmm-server joins the network of DB, so it can connect to the instance DB host with container name:

docker network ls

Given example that DB of your service is running on dev_default, here is how to add pmm-server into that network:

docker network connect dev_default pmm-server

After pmm-server joining the network, we can just simply setup the instance:

Then click on Add service.

Enable the pg_stat_statements to have ability of looking at query analysis

This article is useful: https://gist.github.com/lfittl/1b0671ac07b33521ea35fcd22b0120f5

Query to test after enabling

SELECT /* pmm-agent:pgstatstatements */ "pg_stat_statements"."userid", "pg_stat_statements"."dbid", "pg_stat_statements"."queryid", "pg_stat_statements"."query", "pg_stat_statements"."calls", "pg_stat_statements"."total_time", "pg_stat_statements"."rows", "pg_stat_statements"."shared_blks_hit", "pg_stat_statements"."shared_blks_read", "pg_stat_statements"."shared_blks_dirtied", "pg_stat_statements"."shared_blks_written", "pg_stat_statements"."local_blks_hit", "pg_stat_statements"."local_blks_read", "pg_stat_statements"."local_blks_dirtied", "pg_stat_statements"."local_blks_written", "pg_stat_statements"."temp_blks_read", "pg_stat_statements"."temp_blks_written", "pg_stat_statements"."blk_read_time", "pg_stat_statements"."blk_write_time" FROM "pg_stat_statements" WHERE queryid IS NOT NULL AND query IS NOT NULL;

Practice some DB activities on the service, then choose the menu to see the new monitoring data in this menu

The data looks cool now when checking dev-db host.

It's done to setup PMM with Postgres on the local env.