Adding PostgreSQL Queries Overview Dashboards to the PMM Plugin

Following on the heels of our PostgreSQL Tuples Statistics Dashboard, here’s another blog post describing how you can gain additional visibility of PostgreSQL queries using PMM. We take a look at using an extension called pg_stat_statements. This allows us to collect information about the various queries running in your PostgreSQL instance. We’ll describe how to check if you already have pg_stat_statements running, and if not how to enable the extension in PostgreSQL. Finally, we’ll see how to enable collection using a custom query file and pmm-admin option flag.

We have taken much of our inspiration for these new PG dashboards from Gregory Stark’s presentation at in 2018, where he demonstrated some excellent work using Grafana and Prometheus to build a PostgreSQL monitoring solution.

PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL, MongoDB, and PostgreSQL servers to ensure that your data works as efficiently as possible.

Setting up query monitoring in PMM for postgres

Postgres extensions, in simplified terms, are lower level APIs that exist within PostgreSQL that allow to change or extend its functionality. The extension pg_stat_statements is known as a contrib extension, found in the contrib directory of a PostgreSQL distribution.

So let’s check if this extension is in your database installation.

If no installed version is provided please enable the extension.

The next step is to teach our exporter to collect information from the extension. So we need create a file with a custom query or you may use the already created file in our exporter repository.

Now lets run the exporter with this file /home/ec2-user/queries.yaml. Please use flag extend.query-path as in the example below.

So now you can upload two dashboards in order to checks queries through the PMM interface.

Here are some screenshots from our test installation.

This is the first of the dashboards “PostgreSQL Queries Overview” that shows information about all queries and databases.

Please notice that the query id is clickable and leads to the second dashboard “PostgreSQL Query Drill-Down”. So you can check charts related to a selected query.

Both dashboards are available in GrafanaLab and have been adapted for PMM.

Share this post

Comments (3)

  • andrypein Reply

    which port number should be opened on firewall?
    if mysql are 42002 and 42000, then what about postgre?

    July 13, 2019 at 10:08 am
    • Vadim Yalovets Reply

      It’s 42005.

      July 14, 2019 at 1:56 pm
  • Vadim Yalovets Reply

    The default TCP port for PostgreSQL is usually 5432. It can be checked in the file postgresql.conf
    # grep “port = ” /etc/postgresql/10/main/postgresql.conf
    port = 5432 # (change requires restart)

    July 14, 2019 at 9:15 am

Leave a Reply