Database monitoring with Prometheus + Grafana

Sets up a central monitoring server (Prometheus, Alertmanager, Grafana) and adds node_exporter plus the database exporters you select to the monitored servers: PostgreSQL, MySQL, Oracle, SQL Server, MongoDB, Redis, Elasticsearch. Least-privilege monitoring users, systemd services, basic alert rules, email/webhook notification, datasource provisioning and ready-made dashboards are included. It continues the 'Connect monitoring' step at the end of the database installation guides; the result is a monitoring setup that shows the metrics of all servers on a single screen and sends a notification when something goes wrong.

Topology: 1 monitoring server + monitored servers

Before you start

• The monitoring server and the monitored Linux servers run a supported operating system; clocks are synchronized (chrony). Clock skew leads to "out of bounds" errors and false alerts in Prometheus.
• The monitoring server has enough disk for /var/lib/prometheus: at least a few GB for … servers × … days; a separate disk/LV is recommended. At least 2 CPUs and 4 GB RAM are required.
• The exporter ports are open from the monitoring server (…) to every server: 9100 (node)…, 9187 (PostgreSQL)……, 9104 (MySQL)……, 9161 (Oracle)……, 9216 (MongoDB)……, 9121 (Redis)……, 9114 (Elasticsearch)……; … to the SQL Server servers…. … (Grafana) from the users to the monitoring server.
• The servers can reach github.com over HTTPS (exporter and Prometheus binaries), and the monitoring server can also reach …rpm.grafana.com…apt.grafana.com…; if they cannot, download the files on another machine and copy them over.
• On the PostgreSQL servers, pg_hba.conf contains host all all 127.0.0.1/32 scram-sha-256 (or a narrower rule): the exporter connects locally over TCP with a password.
• You have an admin account on each database that can create the monitoring user (PostgreSQL: postgres, MySQL: root, Oracle: SYSDBA, SQL Server: sysadmin, MongoDB: userAdmin, Redis: a user with ACL privileges, Elasticsearch: elastic).
• Create entries in the password vault for the monitoring users' passwords. The passwords are prompted for interactively in the steps and are written only to files (0600) readable by the corresponding exporter user.

Phases of the guide

• Preparation — Install the basic tools and synchronize the clock · Firewall: open the exporter ports to the monitoring server · Firewall: open the Grafana port
• node_exporter (operating system metrics) — Download and install node_exporter · Define the node_exporter service · Start node_exporter
• PostgreSQL: postgres_exporter — Create the monitoring role (pg_monitor) · Set the monitoring role's password · Download and install postgres_exporter · Write the connection settings and the password file · Define the postgres_exporter service · Start postgres_exporter
• MySQL: mysqld_exporter — Create the monitoring user and the exporter credentials file · Download and install mysqld_exporter · Define the mysqld_exporter service · Start mysqld_exporter
• Oracle: oracledb_exporter — Create the monitoring user · Download and install oracledb_exporter · Write the exporter configuration · Define the oracledb_exporter service · Start oracledb_exporter
• SQL Server: sql_exporter — Create the monitoring login (on SQL Server …) · Download and install sql_exporter · Write the target configuration (…) · Define the sql_exporter service template · Start sql_exporter (…)
• MongoDB: mongodb_exporter — Create the monitoring user (mongosh) · Download and install mongodb_exporter · Write the credentials file · Define the mongodb_exporter service · Start mongodb_exporter
• Redis: redis_exporter — Create the monitoring user (ACL) · Download and install redis_exporter · Write the credentials file · Define the redis_exporter service · Start redis_exporter
• Elasticsearch: elasticsearch_exporter — Create the monitoring role and user · Download and install elasticsearch_exporter · Write the CA certificate and the credentials file · Define the elasticsearch_exporter service · Start elasticsearch_exporter
• Prometheus — Download and install Prometheus · Define the scrape targets (prometheus.yml) · Write the alert rules · Validate the configuration · Define the Prometheus service · Start Prometheus
• Alertmanager — Download and install Alertmanager · Write the notification configuration · Validate the configuration · Define the Alertmanager service · Start Alertmanager
• Grafana — Define the Grafana repository · Define the Grafana repository · Install Grafana · Configure the security settings · Define the Prometheus datasource (provisioning) · Define dashboard provisioning …
• Verification — Check that all targets are UP · Check that the rules are loaded · Send a test alert · Check the Grafana dashboards
• Post-installation — Back up the monitoring system · Apply the security checklist