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 PGConf.eu 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 (20)

  • 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
      • Rushi Reply

        Hi Vadim, Yalovets, I have pg_stat_statements version 1.3 installed.. should it work ?

        I actually installed and when i ran postgres_exporter with –extend.query-path its still throwing error
        INFO[0173] Error running query on database “localhost:5432”: pg_stat_statements pq: relation “pg_stat_statements” does not exist source=”postgres_exporter.go:1286″

        November 13, 2019 at 2:05 pm
  • Vadim Yalovets Reply

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

    July 14, 2019 at 9:15 am
  • dinesh Reply

    i have done the setup as instructed above i could see dashboard but with no datapoints i have used the below commands with extend querypath with the yaml file provided above but still getting the no datapoints can you throw some light on this
    pmm-admin list doesnt show the options value does it have anything to do with missing data points

    [~]# pmm-admin add postgresql:metrics –host=localhost –password=yyy –port=5432 –user=xxx — –extend.query-path ./queries2.yaml
    OK, now monitoring PostgreSQL metrics using DSN postgresql:***@localhost:5432/postgres
    [root@db0pg1-1-prd ~]# pmm-admin list
    pmm-admin 1.17.0

    PMM Server | 10.252.203.232
    Client Name | db0pg1-1-prd.eng.sfdc.net
    Client Address | 10.252.203.232
    Service Manager | linux-systemd

    ——————- ————————– ———– ——– ————————————— ——–
    SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
    ——————- ————————– ———– ——– ————————————— ——–
    linux:metrics xxxxxx 42000 YES –
    postgresql:metrics xxxxx 42005 YES postgresql:***@localhost:5432/postgres

    September 26, 2019 at 10:02 pm
    • Vadim Yalovets Reply

      Hello Dinesh,

      Could you use a full path to your file for the flag extend.query-path?
      e.g.
      –extend.query-path /home/username/queries2.yaml

      October 31, 2019 at 4:12 am
  • Rushi Reply

    Rushi Reply
    Hi Vadim, Yalovets, I have pg_stat_statements version 1.3 installed.. should it work ?

    I actually installed and when i ran postgres_exporter with –extend.query-path its still throwing error
    INFO[0173] Error running query on database “localhost:5432”: pg_stat_statements pq: relation “pg_stat_statements” does not exist source=”postgres_exporter.go:1286″

    November 13, 2019 at 2:06 pm
    • Vadim Yalovets Reply

      Hi Rushi,
      Please make sure that your PostgreSQL instance was added to monitoring with a user that has superuser rights.
      So this user can get information from pg_stat_statements.

      e.g.
      pguser=# show is_superuser;
      is_superuser
      ————–
      on
      (1 row)

      pguser=# select count(*) from pg_stat_statements;
      -[ RECORD 1 ]
      count | 36

      November 14, 2019 at 8:12 am
  • Nenad Reply

    Hi Vadim,

    thank you for this nice tutorial!

    I have a question regarding pmm-admin rm postgresql:metrics PS_NODE-1 part….I get a massage :

    pmm-admin: error: enum value must be one of mysql,mongodb,postgresql,proxysql, got ‘postgresql:metrics’, try –help

    Also when I try docker exec -it pmm-server pmm-admin list , it says: Unauthorized. Please check username and password.

    BR

    Nenad

    March 13, 2020 at 6:48 am
    • Vadim Yalovets Reply

      Hi Nenad,

      What PMM version is used?

      Here are commands for PMM 2.x versions
      $ pmm-admin remove postgresql
      $ docker exec -it pmm-server pmm-admin list –server-url=http://username:password@localhost/

      March 13, 2020 at 7:33 am
  • Nick Reply

    Hi Vadim,

    Great tutorial.

    Can I and/or how can I add –extend.query-path on linux docker postgres exporter?

    March 24, 2020 at 6:18 am
    • Vadim Yalovets Reply

      Hi Nick,
      We don’t have a docker container for our version of postgres exporter.

      March 25, 2020 at 8:04 am
  • Siddesh Reply

    Hi Vadim,

    Please help …

    I have installed PMM1.6 and PMM client 1.71 , Node exprter working fine . Postgresql_exprters also working fine.

    But when i removed and added postgresql exporter with extend query pmm-admin check-network showing down..

    [root@sl73gseldbd192 prometheus]# pmm-admin add postgresql:metrics –user pmm_user –password secret postgres –host slxxxldbd192 –port 5432 –service-port 9187 –disable-ssl –extend.query-path /pgworkspace/software/prometheus/queries.yaml

    * Connection: Client <– Server
    ——————- ————— ——————— ——- ———- ———
    SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD
    ——————- ————— ——————— ——- ———- ———
    linux:metrics slxxxldbd192 10.160.109.221:9100 OK NO –
    postgresql:metrics postgres 10.160.109.221:9187 DOWN NO –

    PMM Server | slxxxeldbd217 (insecure SSL)
    Client Name | sxxxseldbd192.visa.com
    Client Address | 10.1xx0.1xx9.xx21
    Service Manager | linux-systemd

    ——————- ————— ———– ——– ——————————————– ————
    SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
    ——————- ————— ———– ——– ——————————————– ————
    linux:metrics sl7xxldbd192 9100 YES – scheme=http
    postgresql:metrics postgres 9187 YES postgresql:***@sl73gxxxd192:5432/postgres scheme=http

    pmm_user=# show is_superuser
    pmm_user-# ;
    is_superuser
    ————–
    on
    (1 row)

    March 30, 2020 at 8:02 am
  • Siddesh Reply

    Hi ,

    Typo in my pmm-server version .. its pmm-server-1.16.0-12

    Thanks
    Siddesh

    March 30, 2020 at 10:30 am
  • Siddesh Reply

    Hi Vadim,

    i tried with same Server and client version 1.17.3 .. with extend-query-path not working …

    time=”2020-03-31T09:35:20Z” level=info msg=”Starting postgres_exporter (version=, branch=, revision=)” source=”postgres_exporter.go:1132″
    time=”2020-03-31T09:35:20Z” level=info msg=”Build context (go=go1.10.1, user=, date=)” source=”postgres_exporter.go:1133″
    time=”2020-03-31T09:35:20Z” level=info msg=”Established new database connection.” source=”postgres_exporter.go:1015″
    time=”2020-03-31T09:35:20Z” level=info msg=”Semantic Version Changed: 0.0.0 -> 11.2.0″ source=”postgres_exporter.go:945″
    time=”2020-03-31T09:35:20Z” level=info msg=”Starting HTTP server for http://1x.1xx.1xx.2×1:9187/metrics …” source=”server.go:121″

    March 31, 2020 at 5:41 am
  • Siddesh Reply

    Hi Vadim,

    First , Thanks for the reply. It resolved after increasing the METRICS_RESOLUTION=5s

    Thanks
    Siddesh

    April 1, 2020 at 3:52 am
  • Savo Reply

    Hello,

    Is it possible to use this dashboard with already installed postgres_exporter from scratch, together with modified queries.yaml? Not to install PMM client separately? This is because i already have Prometheus and Grafana in environment and we have no need for PMM Server.

    BR.
    Savo

    October 7, 2020 at 6:56 am
    • Vadim Yalovets Reply

      Hi Savo,

      You should be able to use these dashboards. It were designed for PMM1 so no specific PMM2 variables are used yet (e.g. node_name, service_name …)

      October 7, 2020 at 12:26 pm
      • Savo Reply

        Great, will test it. Thanks Vadim! 🙂

        October 8, 2020 at 4:20 am

Leave a Reply