PostgreSQLPostgreSQL is a widely-used Open Source database and has been the DBMS of the year for the past 2 years in DB-Engine rankings. As such, there is always a need for reliable and robust monitoring solutions. While there are some commercial monitoring tools, there is an equally good number of open source tools available for monitoring PostgreSQL. Percona Monitoring and Management (PMM) is one of those open source solutions that have continuous improvements and is maintained forever by Percona. It is simple to set up and easy to use.

Don’t have Percona Monitoring and Management installed yet? Install it here.

PMM can monitor not only PostgreSQL but also MySQL and MongoDB databases, so it is a simple monitoring solution for monitoring multiple types of databases. In this blog post, you will see all the steps involved in monitoring PostgreSQL databases using PMM.

This is what we will be discussing:

  1. Using the PMM docker image to create a PMM server.
  2. Installing PMM client on a Remote PostgreSQL server and connecting the PostgreSQL Client to PMM Server.
  3. Creating required users and permissions on the PostgreSQL server.
  4. Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

If you already know how to create a PMM Server, please skip the PMM server setup and proceed to the PostgreSQL client setup.

Using the PMM docker image to create a PMM server

PMM is a client-server architecture where clients are the PostgreSQL, MySQL, or MongoDB databases and the server is the PMM Server. We see a list of metrics on the Grafana dashboard by connecting to the PMM server on the UI. In order to demonstrate this setup, I have created 2 virtual machines where one of them is the PMM Server and the second server is the PostgreSQL database server.

Step 1 : 

On the PMM Server, install and start docker.

Here are the installation instructions of PMM Server.

Step 2 :

Pull the pmm-server docker image. I am using the latest PMM2 docker image for this setup.

You see a docker image of size 1.48 GB downloaded after the above step.

Step 3 :

Create a container for persistent PMM data.

Step 4 :

Create and launch the PMM Server. In the following step, you can see that we are binding the port 80 of the container to the port 80 of the host machine. Likewise for port 443.

At this stage, you can modify certain settings such as the memory you wish to allocate to the container or the CPU share, etc. You can also see more such configurable options using docker run --help. The following is just an example of how you can modify the above step with some memory or CPU allocations.

You can list the containers started for validation using docker ps.

Step 5 : 

You can now see the PMM Server Dashboard in the browser using the Host IP address. For my setup, the PMM Server’s IP Address is 192.168.80.10. As soon as you put the IP in the browser, you will be asked to enter the credentials as seen in the image below. Default user and password are both: admin

create a PMM server

And then you will be asked to change the password or skip.

PMM Server setup is completed after this step.

Installing PMM client on a Remote PostgreSQL server

I have a PostgreSQL 11.5 Server running on 192.168.80.20. The following steps demonstrate how we can install and configure the PMM client to enable monitoring from the PMM server ( 192.168.80.10).

Before you proceed further, you must ensure that ports 80 and 443 are both enabled on the PMM server for the PG 11 Server to connect. In order to test that, I have used telnet to validate whether ports 80 and 443 are open on the PMM Server for the pg11 server.

Step 6 :

There are very few steps you need to perform on the PostgreSQL server to enable it as a client for PMM server. The first step is to install the PMM Client on the PostgreSQL Database server as follows. Based on the current PMM release, I am installing pmm2-client today. But, this may change once we have a new PMM release.

Step 7 :

The next step is to connect the client (PostgreSQL server) to the PMM Server. We could use pmm-admin config in order to achieve that. Following is a simple syntax that you could use in general.

The following are the flags and other options I could use with my setup.

So the final syntax for my setup looks like the below. We can run this command as root or by using the sudo command.

Syntax : 7a

Syntax : 7b

You could also use a simple syntax such as following without node-address, node-type, node-name :

But when you use such a simple syntax as above, node-address, node-type, node-name are defaulted to certain values. If the defaults are incorrect due to your server configuration, you may better pass these details explicitly like I have done in the syntax : 7a. In order to validate whether the defaults are correct, you can simply use # pmm-admin config --help. In the following log, you see that the node-address  defaults to 10.0.2.15 which is incorrect for my setup. It should be 192.168.80.20.

Below is an example where the default settings were perfect because I had configured my database server the right way.

Using steps 6 and 7a, I have finished installing the PMM client on the PostgreSQL server and also connected it to the PMM Server. If the above steps are successful, you should see the client listed under Nodes, as seen in the following image. Else, something went wrong.

Creating required users and permissions on the PostgreSQL server

In order to monitor your PostgreSQL server using PMM, you need to create a user *using* which the database stats can be collected by the PMM agent. However, starting from PostgreSQL 10, you do not need to grant SUPERUSER or use SECURITY DEFINER (to avoid granting SUPERUSER). You can simply grant the role pg_monitor to a user (monitoring user). In my next blog post, you will see how we could use SECURITY DEFINER to avoid granting SUPERUSER for monitoring PostgreSQL databases with 9.6 or older.

Assuming that your PostgreSQL Version is 10 or higher, you can use the following steps.

Step 1 : 

Create a postgres user that can be used for monitoring. You could choose any username; pmm_user in the following command is just an example.

Step 2 : 

Grant pg_monitor role to the pmm_user.

Step 3 : 

If you are not using localhost, but using the IP address of the PostgreSQL server while enabling monitoring in the next steps, you should ensure to add appropriate entries to enable connections from the IP and the pmm_user in the pg_hba.conf file.

In the above step, replace 192.168.80.20 with the appropriate PostgreSQL Server’s IP address.

Step 4 : 

Validate whether you are able to connect as pmm_user to the postgres database from the postgres server itself.

Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

Using PMM, we can monitor several metrics in PostgreSQL such as database connections, locks, checkpoint stats, transactions, temp usage, etc. However, you could additionally enable Query Analytics to look at the query performance and understand the queries that need some tuning. Let us see how we can simply enable PostgreSQL monitoring with and without QAN.

Without QAN

Step 1 :

In order to start monitoring PostgreSQL, we could simply use pmm-admin add postgresql. It accepts additional arguments such as the service name and PostgreSQL address and port. As we are talking about enabling monitoring without QAN, we could use the flag: --query-source=none to disable QAN.

Step 2 :

Once you have enabled monitoring, you could validate the same using pmm-admin list.

You can now access the PostgreSQL Dashboards and see several metrics being monitored.

With QAN

With PMM2, there is an additional step needed to enable QAN. You should create a database with the same name as the monitoring user ( pmm_user here). And then, you should create the extension: pg_stat_statements in that database. This behavior is going to change on the next release so that you can avoid creating the database.

Step 1 : 

Create the database with the same name as the monitoring user. Create the extension: pg_stat_statements in the database.

Step 2 : 

If shared_preload_libraries has not been set to pg_stat_statements, we need to set it and restart PostgreSQL.

Step 3 :

In the previous steps, we used the flag: --query-source=none to disable QAN. In order to enable QAN, you could just remove this flag and use pmm-admin add postgresql without the flag.

Step 4 : 

Once the above step is completed, you could validate the same again using pmm-admin list. But this time, you should see an additional service: qan-postgresql-pgstatements-agent .

After this step, you can now see the Queries and their statistics captured on the Query Analytics Dashboard.

Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. PMM works for both Community PostgreSQL and also the Percona Distribution for PostgreSQL. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.

Optimize your PostgreSQL performance with Percona Monitoring and Management (PMM). This on-demand webinar, led by PostgreSQL experts will guide you through setting up and maximizing PMM for your PostgreSQL deployments. Learn to customize metrics and dashboards, and gain skills in troubleshooting and root cause analysis.

Watch the recorded webinar

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
giant

why QAN need new database then name of PMM user? is possible using other name of database for QAN?

0
avivallarapu

Thank You for reading through the blog post. This is a known bug with PMM2 that you need to have a database that is same as the monitoring user. With the next PMM release, you should see this fixed. This way you could pass any database or leave it the default (postgres). However, this dependency today should not affect any of the PMM functionalities.

0
Yogesh

Is it possible to monitor replication lag and their topology using Pmm by default ?

0
avivallarapu

Not yet Yogesh. But this is one of the features you will see in the future releases. Thank you for the feedback.

0
Sumit Aneja

Just checking whether this feature was implemented?

0
Avinash Vallarapu

Not yet. We have recently finished the development for this feature internally. I will keep the thread posted as soon as it is deployed as part of the next release.

1
Rodrigo Buch

I am getting the following error:

postgres @ easydocdb02: ~ $ psql -c -d pmm_user “CREATE EXTENSION pg_stat_statements”
psql: FATAL: Peer authentication failed for user “CREATE EXTENSION pg_stat_statements”

0
GSPANN

You may learn how to monitor MySQL database with Grafana and Prometheus. Here is the link – https://www.gspann.com/resources/blogs/mysql-database-monitoring-with-grafana-and-prometheus

0
janardhan

I have added postgresql 10.6 version database(rds postgresql) in PMM but it is not showing any quires in PMM QAN.

any suggestions would be appreciated.

0
vinay jaiswal

Anyone noticed PMM 2 for postgresql , db uptime is not showing correct because it display the time on the basis postgresql_exporter process (time() – process_start_time_seconds{node_name=”xxx\\.xxx\\.xxxpmm-server”,job=”postgres_exporter.*”})) . Is it bug and it designed by nature for postgresql DB .

while db uptime shows correct for mysql/MongoDB. mysql_global_status_uptime ,mongodb_instance_uptime_seconds

1