For those who are coming from Oracle world, the best alternative database is probably Postgres, because of many similarities between those two Db engines (data types, tablespace concept etc.).

 

However, one of the first thing you want to do is to grab a full control over what is going on in your database.

If you licensed Oracle, you can install phenomenal, free (only a couple of extra options are charged) Oracle Enterprise Manager Cloud control that can provide a full visibility and control of your database (and not only database as it can monitor OSB, WebLogic, Linux, Solaris, JVM…).

 

For monitoring a Postgres, there are a couple of good tools (especially Solarwinds DPA), but they are all commercial.

Knowing what to expect from a good monitoring tool, after a bit of investigation I ended up with only two possible candidates (I didn’t consider pgAdmin as it might be good for administration, but monitoring part is far below my expectations).

First free monitoring tool that I considered was pgWatch (link is provided down below):

https://pgwatch.com/

 

At the first moment pgWatch looked promising, but after some complications with manual installation (Docker version is another option), and unnecessary complications with many options (Python 3 for Web UI, Postgres or InfluxDb for storing a metrics), and basic documentation I gave up.

 

Second (and last) free monitoring tool I wanted to examine was Percona PMM.

It is not necessary to present Percona, company with excellent reputation in open source community.

Percona PMM consists of a several Open Source components (Postgres, Graphana, Victoriametrics).

To make installation easier, PMM comes in two versions:

  • VirtualBox image
  • Docker container

 

Postgres is the latest additions to Percona PMM (MySQL was first, followed by MongoDb)

Documentation for Percona PMM is quite exhaustive (200+ pages) which gives impression of seriousness, and is a couple of times larger when compared with PgWatch docs.

Although some features when monitoring Postgres are still missing (like the execution plan for top statements), the tool provides more than enough information to find out what is going on inside of your database.

PMM has a many features such as multi-tenancy, alerting, exceptional graphs and metrics etc.

Prerequisite is to install extension for each Postgres database you want to monitor.

 

There are two extensions you can choose from:

  • pg_stat_statements
  • pg_stat_monitor

 

While the former one is well known, Percona team has created a new extension called “pg_stat_monitor” which extends the pg_stat_monitor with bucket-based data aggregation.

 

Unfortunately, pg_stat_monitor is still in beta, so you need to wait for a while, but you can play with in non-productions environments.

 

To install pg_stat_statements (currently the only option you can install on production), you need to perform the following steps:

 

Edit the: 
vim /var/lib/pgsql/12/data/postgresql.conf

and add the following: 
shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on

As postgres OS user execute: 
psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"

To install Docker version of the PMM, you can use the following steps (assuming that you already have Docker installed):

 

docker pull percona/pmm-server:2
Trying to pull repository docker.io/percona/pmm-server ... 
2: Pulling from docker.io/percona/pmm-server
2d473b07cdd5: Pull complete 
2e15b861e583: Pull complete 
Digest: sha256:b09a5d90a4d7b5350b6dfaed498d4570fc5d0c31c3c12e865b566b5fb748edf8
Status: Downloaded newer image for docker.io/percona/pmm-server:2


docker image ls
REPOSITORY                     TAG                 IMAGE ID            CREATED             SIZE
docker.io/percona/pmm-server   2                   4d143324aa8b        4 weeks ago         1.87 GB


$ docker create -v /srv --name pmm-data percona/pmm-server:2 /bin/true
43e967d4bd12a00ea44f769ea1a925616c39070d1be10a1c8d904fa9342c4b75


$ docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data --name pmm-server --cpu-shares 100 --memory 1024m --restart always percona/pmm-server:2

a6898d4d9f4a227448d8122c64a58a645c1a8396e67b6861fd0e5c9bb949e547


docker ps
CONTAINER ID        IMAGE                  COMMAND                CREATED             STATUS                    PORTS                                      NAMES
a6898d4d9f4a        percona/pmm-server:2   "/opt/entrypoint.sh"   38 seconds ago      Up 36 seconds (healthy)   0.0.0.0:80->80/tcp, 0.0.0.0:443->443/tcp   pmm-server

 

After that, you should be able to login:

http: 172.25.1.12/graph/login

 

On each database you want to monitor, you need to install Percona client:

 

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Loaded plugins: fastestmirror, langpacks
percona-release-latest.noarch.rpm                                                                                                                                                   |  19 kB  00:00:00     
Examining /var/tmp/yum-root-qpluHS/percona-release-latest.noarch.rpm: percona-release-1.0-25.noarch
Marking /var/tmp/yum-root-qpluHS/percona-release-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package percona-release.noarch 0:1.0-25 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================================================================================================
 Package                                          Arch                                    Version                                    Repository                                                       Size
===========================================================================================================================================================================================================
Installing:
 percona-release                                  noarch                                  1.0-25                                     /percona-release-latest.noarch                                   31 k

Transaction Summary
===========================================================================================================================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : percona-release-1.0-25.noarch                                                                                                                                                           1/1 
* Enabling the Percona Original repository
<*> All done!
* Enabling the Percona Release repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.

For example, to enable the Percona Server 8.0 repository use:

  percona-release setup ps80

Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.

For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html

  Verifying  : percona-release-1.0-25.noarch                                                                                                                                                           1/1 

Installed:
  percona-release.noarch 0:1.0-25                                                                                                                                                                          

Complete!


yum install pmm2-client
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: centos.lonyai.com
 * epel: mirror.inode.at
 * extras: centos.lonyai.com
 * updates: centos.lonyai.com
Resolving Dependencies
--> Running transaction check
---> Package pmm2-client.x86_64 0:2.13.0-6.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================================================================================================
 Package                                        Arch                                      Version                                          Repository                                                 Size
===========================================================================================================================================================================================================
Installing:
 pmm2-client                                    x86_64                                    2.13.0-6.el7                                     percona-release-x86_64                                     46 M

Transaction Summary
===========================================================================================================================================================================================================
Install  1 Package

Total download size: 46 M
Installed size: 46 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/pmm2-client-2.13.0-6.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY=========-   ] 5.5 MB/s |  44 MB  00:00:00 ETA 
Public key for pmm2-client-2.13.0-6.el7.x86_64.rpm is not installed
pmm2-client-2.13.0-6.el7.x86_64.rpm                                                                                                                                                 |  46 MB  00:00:08     
Retrieving key from file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
Importing GPG key 0x8507EFA5:
 Userid     : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
 Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
 Package    : percona-release-1.0-25.noarch (@/percona-release-latest.noarch)
 From       : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pmm2-client-2.13.0-6.el7.x86_64                                                                                                                                                         1/1 
  Verifying  : pmm2-client-2.13.0-6.el7.x86_64                                                                                                                                                         1/1 

Installed:
  pmm2-client.x86_64 0:2.13.0-6.el7                                                                                                                                                                        

Complete!



$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin99@172.25.1.12:443 172.25.1.11 generic pg-client



Node registration 

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP_ADDRESS>:443 <IP_ADDRESS2> generic pg-client --force
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.



Creating required users and permissions on the PostgreSQL server

$ psql -c "CREATE USER pmm_user WITH ENCRYPTED PASSWORD 'jp'"
CREATE ROLE


$ psql -c "GRANT pg_monitor to pmm_user"
GRANT ROLE

psql -c "select pg_reload_conf()"


Putting database under monitoring
# pmm-admin add postgresql --username=pmm_user --password=pass pgtest <>IP_ADDRESS:5432
PostgreSQL Service added.
Service ID  : /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
Service name: postgres


List everything what is registered
pmm-admin list
Service type                Service name                        Address and port       Service ID
PostgreSQL                  pgtest                              <IP_ADDRESS>:5432       /service_id/84ca42af-80de-4167-9335-72308eb3bacf

Agent type                  Status     Metrics Mode   Agent ID                                      Service ID
pmm_agent                   Connected    /agent_id/992364e4-03f3-42b8-bdec-46960105c345  
node_exporter               Running    pull  /agent_id/1aa6db50-ce29-4be5-8f2b-08524f1188ba  
postgres_exporter           Running    pull  /agent_id/32e30bbc-5df3-494a-99e8-e69ddfd9506f /service_id/84ca42af-80de-4167-9335-72308eb3bacf 
postgresql_pgstatements_agent Running      /agent_id/257b80e8-bc22-421c-a2c6-388222560aa7 /service_id/84ca42af-80de-4167-9335-72308eb3bacf 
vmagent                     Running    push  /agent_id/3c0e0d96-2052-4428-bb2b-e8e9f876f928

 

It’s interesting that Percona decided to replace one of the Open source components used for creating PMM – Prometeus, with Victoriametrics.

 

I’m just guessing, but the reason for that might be this one:

https://valyala.medium.com/prometheus-vs-victoriametrics-benchmark-on-node-exporter-metrics-4ca29c75590f

 

It’s important to note that PMM can work in two modes:

  • pool mode (pooling the metrics out)
  • pushing mode (requires less open ports and is recommended way in the latest releases of PMM)

 

Finally, below you can find a couple of slides of this wonderful, free tool.

 

First page
Postgres instance summary
Conflicts and lock details
Disk statistics
Query analytics
Query details
Query statistics


Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.