Who this is for

This chapter covers the core features and capabilities of Database Monitoring Setup.

Who this is for

• Engineers/DBAs who run their own PostgreSQL or MySQL and want real visibility.
• Teams adding databases to an existing Prometheus/Grafana setup.
• Anyone tired of finding out the database was at 99% connections *after* the

outage.

You should be comfortable running an exporter, editing Prometheus/Alertmanager

config, and importing a Grafana dashboard. No paid tooling required.

Contents

database-monitoring-setup/
├── README.md
├── LICENSE
├── monitoring/
│   ├── prometheus.yml                # scrape config: postgres/mysql/node exporters
│   ├── alert.rules.yml               # PromQL alerts + recording rules, severities
│   └── alertmanager.yml              # severity-based routing, grouping, inhibition
├── dashboards/
│   ├── postgresql-overview.json      # importable Grafana dashboard (8 panels)
│   └── mysql-overview.json           # importable Grafana dashboard (8 panels)
├── exporters/
│   ├── postgres-exporter-setup.md    # least-privilege role + systemd + verify
│   ├── postgres-exporter-queries.yaml# custom queries: lag, activity, locks, bloat
│   └── mysqld-exporter-setup.md       # least-privilege user + collectors + verify
├── scripts/
│   ├── pg_healthcheck.sh             # one-shot PostgreSQL health probe
│   ├── mysql_healthcheck.sh          # one-shot MySQL health probe
│   └── replication_check.sh          # replica lag / replication-health gate
├── sql/
│   ├── postgres-metric-queries.sql   # cache ratio, lag, bloat, locks, slow queries
│   └── mysql-metric-queries.sql      # buffer pool, lag, locks, digests
└── docs/
    ├── key-metrics-guide.md          # what to watch and why; good/alert values
    └── alerting-runbook.md           # one response section per alert

Prerequisites

• A running Prometheus (2.x) and Grafana (9/10/11).
• postgres_exporter (0.11+) and/or mysqld_exporter (0.14+).
• PostgreSQL 13–16 and/or MySQL 8.0/8.4 / MariaDB 10.6+.
• promtool / amtool (optional) to validate config; psql/mysql clients for

the scripts.

Hostnames (db01.example.com), subnets, and passwords are placeholders —

replace them. Credentials are always passed via env/defaults files, never

hard-coded.

Quick start

Follow this guide to get Database Monitoring Setup up and running in your environment.

Quick start

1. Stand up the exporters using exporters/postgres-exporter-setup.md and

exporters/mysqld-exporter-setup.md. Each creates a least-privilege

monitoring role and verifies the metrics endpoint.

2. Install the custom Postgres queries (exporters/postgres-exporter-queries.yaml)

so the replication-lag/activity/bloat series the alerts need exist.

3. Add the scrape jobs — merge monitoring/prometheus.yml (edit the targets),

then validate and reload:

   promtool check config monitoring/prometheus.yml
   promtool check rules  monitoring/alert.rules.yml
   curl -X POST http://localhost:9090/-/reload

4. Wire up Alertmanager with monitoring/alertmanager.yml (point the

receivers at your paging/chat), then amtool check-config monitoring/alertmanager.yml.

5. Import the dashboards — in Grafana, *Dashboards → Import → Upload JSON*,

pick dashboards/postgresql-overview.json (and the MySQL one), and select your

Prometheus datasource when prompted.

6. Add the health checks to cron (optional) as a belt-and-suspenders probe:

   PGHOST=db01.example.com PGUSER=monitor PGDATABASE=app_db ./scripts/pg_healthcheck.sh
   ./scripts/replication_check.sh --engine mysql --max-lag 30

File-by-file guide

How the pieces fit together

  PostgreSQL ──► postgres_exporter ─┐
                                    ├─► Prometheus ──► alert.rules.yml ──► Alertmanager ──► page/ticket/info
  MySQL ───────► mysqld_exporter ───┘        │
                                             └─► Grafana dashboards (postgresql-overview / mysql-overview)

The recording rules in alert.rules.yml define cache-hit and connection ratios

once, so the dashboards and alerts always agree. Every alert carries a

runbook annotation pointing at the matching section of docs/alerting-runbook.md.