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):
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:
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:
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.
Comments