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:
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) <email@example.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:firstname.lastname@example.org: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.