AI-Powered Database Monitoring from the Linux Shell (with apt/dnf/zypper install steps)

If your database only screams when users do, it’s already too late. Static thresholds and ad‑hoc scripts miss subtle problems: gradual latency creep, lock storms at odd hours, or the one rogue migration that silently doubles I/O. This post shows how to bring a lightweight dose of Artificial Intelligence to your Linux Bash monitoring—so you can detect weirdness early, with minimal moving parts.

We’ll collect PostgreSQL metrics with Bash, learn “normal” behavior using an Isolation Forest (a simple, robust anomaly-detection algorithm), and alert via webhooks—all runnable from cron. You’ll get actionable commands, full install steps for apt, dnf, and zypper, and real-world patterns to watch.

Why AI for database monitoring?

• Static thresholds are brittle. AI models learn baselines from your actual workload (weekday vs. weekend, peaks vs. off-peak), reducing false alarms.

• Multivariate patterns matter. Spikes in reads are fine—unless accompanied by a drop in cache hits and sudden rollbacks. AI correlates metrics automatically.

• Early anomaly detection. Catch drift and unusual combinations before SLOs suffer.

What follows is a pragmatic, Bash-first way to add AI anomaly detection without overhauling your stack.

---

What we’ll build

• A Bash collector that samples key PostgreSQL counters into a CSV.

• A small Python script that turns counters into rates, learns normal patterns, and flags anomalies (using scikit-learn’s IsolationForest).

• A thin Bash wrapper to alert via a Slack-compatible webhook.

• A cron job to run it automatically.

You can extend this to any DB (MySQL/MariaDB, MongoDB, etc.) by swapping the collector query.

---

Prerequisites and installation

Packages we’ll use:

• PostgreSQL client (psql)

• Python 3 + pip + venv

• jq (for JSON parsing in Bash)

• curl (for webhooks)

• cron service (cronie on Fedora/RHEL)

Optional (for test load): pgbench

Install with your package manager:

• apt (Debian/Ubuntu):

sudo apt update
sudo apt install -y postgresql-client jq curl python3 python3-venv python3-pip cron
# Optional:
sudo apt install -y postgresql-contrib   # for pgbench
sudo apt install -y mailutils            # if you prefer email alerts
# Enable and start cron:
sudo systemctl enable --now cron

• dnf (Fedora/RHEL/CentOS Stream):

sudo dnf install -y postgresql jq curl python3 python3-pip cronie
# Optional:
sudo dnf install -y postgresql-contrib   # for pgbench
sudo dnf install -y mailx                # if you prefer email alerts
# Enable and start cron:
sudo systemctl enable --now crond

• zypper (openSUSE/SLE):

sudo zypper refresh
sudo zypper install -y postgresql jq curl python3 python3-pip cron
# Optional:
sudo zypper install -y postgresql-contrib   # for pgbench
sudo zypper install -y mailx                # if you prefer email alerts
# Enable and start cron:
sudo systemctl enable --now cron

Create a Python virtual environment and install the AI bits:

python3 -m venv .venv
. .venv/bin/activate
pip install --upgrade pip
pip install scikit-learn pandas numpy

Security tip: Use ~/.pgpass or environment variables to avoid hardcoding DB credentials.

# ~/.pgpass format: hostname:port:database:username:password
echo "localhost:5432:postgres:postgres:yourStrongPassword" >> ~/.pgpass
chmod 600 ~/.pgpass

---

Step 1 — Collect PostgreSQL metrics with Bash

Save as collect_pg_metrics.sh and make executable.

#!/usr/bin/env bash
set -euo pipefail

# Connection (override via env or .pgpass)
: "${PGHOST:=localhost}"
: "${PGPORT:=5432}"
: "${PGUSER:=postgres}"
: "${PGDATABASE:=postgres}"

# Output CSV
: "${METRICS_FILE:=/var/tmp/pg_metrics.csv}"

SQL='SELECT
  EXTRACT(EPOCH FROM now())::bigint AS epoch_s,
  xact_commit, xact_rollback, blks_read, blks_hit,
  tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted,
  conflicts, temp_files, temp_bytes, deadlocks,
  COALESCE(blk_read_time,0), COALESCE(blk_write_time,0)
FROM pg_stat_database
WHERE datname = current_database();'

mkdir -p "$(dirname "$METRICS_FILE")"

if [[ ! -s "$METRICS_FILE" ]]; then
  echo "epoch_s,xact_commit,xact_rollback,blks_read,blks_hit,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,conflicts,temp_files,temp_bytes,deadlocks,blk_read_time,blk_write_time" > "$METRICS_FILE"
fi

psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" -At -F',' -c "$SQL" >> "$METRICS_FILE"

Notes:

• These fields are cumulative counters; we’ll turn them into per-second rates later.

• For blk_read_time/blk_write_time, enable track_io_timing = on in PostgreSQL to get non-zero values.

---

Step 2 — Anomaly detection with a tiny AI model

Save as ai_detect.py (runs inside your venv).

#!/usr/bin/env python3
import os, sys, json
import pandas as pd
import numpy as np
from sklearn.ensemble import IsolationForest

METRICS_FILE = os.environ.get("METRICS_FILE", "/var/tmp/pg_metrics.csv")
WINDOW = int(os.environ.get("WINDOW", "500"))  # how many rows to use
MIN_BASELINE = int(os.environ.get("MIN_BASELINE", "60"))  # need at least N rows

def load_and_prepare(path):
    df = pd.read_csv(path)
    if len(df) < 2:
        return df, None
    # Compute time deltas
    df = df.tail(WINDOW).copy()
    df["dt"] = df["epoch_s"].diff().fillna(0)
    # Columns that are cumulative counters
    cols = [
        "xact_commit","xact_rollback","blks_read","blks_hit",
        "tup_returned","tup_fetched","tup_inserted","tup_updated","tup_deleted",
        "conflicts","temp_files","temp_bytes","deadlocks",
        "blk_read_time","blk_write_time"
    ]
    # Convert to per-second rates
    for c in cols:
        dc = df[c].diff()
        rate = dc / df["dt"].replace(0, np.nan)
        df[c+"_rps"] = rate.replace([np.inf, -np.inf], np.nan).fillna(0.0)

    # Feature set: choose rate columns (drop raw and helper cols)
    feature_cols = [c for c in df.columns if c.endswith("_rps")]
    X = df[feature_cols].astype(float)
    # Replace any NaN remaining with 0
    X = X.fillna(0.0)
    return df, X

def main():
    if not os.path.exists(METRICS_FILE):
        print(json.dumps({"ok": False, "error": "metrics_file_missing"}))
        return 1

    df, X = load_and_prepare(METRICS_FILE)
    if X is None or len(X) < MIN_BASELINE:
        print(json.dumps({"ok": True, "warming_up": True, "samples": 0 if X is None else len(X)}))
        return 0

    # Train on all but last sample; score the most recent one
    X_train = X.iloc[:-1]
    X_last = X.iloc[[-1]]

    # Isolation Forest: robust default for anomaly detection
    iso = IsolationForest(
        n_estimators=200,
        contamination=float(os.environ.get("CONTAMINATION", "0.02")),
        random_state=42
    )
    iso.fit(X_train)
    pred = iso.predict(X_last)[0]         # -1 anomalous, 1 normal
    score = iso.decision_function(X_last)[0]  # lower is more anomalous

    out = {
        "ok": True,
        "anomaly": bool(pred == -1),
        "score": float(score),
        "features": {k: float(v) for k, v in X_last.iloc[0].to_dict().items()},
        "rows_used": int(len(X_train)),
        "epoch_s": int(df["epoch_s"].iloc[-1]),
    }
    print(json.dumps(out))
    return 0

if __name__ == "__main__":
    sys.exit(main())

---

Step 3 — Wire it up with a Bash monitor and send alerts

Save as monitor.sh and make executable.

#!/usr/bin/env bash
set -euo pipefail

# Config: set these as env vars or edit here
: "${PGHOST:=localhost}"
: "${PGPORT:=5432}"
: "${PGUSER:=postgres}"
: "${PGDATABASE:=postgres}"
: "${METRICS_FILE:=/var/tmp/pg_metrics.csv}"

# AI params
: "${WINDOW:=500}"         # history window
: "${MIN_BASELINE:=60}"    # require at least N rows before alerting
: "${CONTAMINATION:=0.02}" # expected anomaly fraction (tune as needed)

# Alerts
: "${SLACK_WEBHOOK_URL:=}"        # set to enable Slack/webhook alerts
: "${ALERT_MINUTES:=10}"          # throttle repeated alerts
: "${STATE_FILE:=/var/tmp/ai_dbmon.lastalert}"

# Paths
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

export PGHOST PGPORT PGUSER PGDATABASE METRICS_FILE WINDOW MIN_BASELINE CONTAMINATION

# 1) Collect metrics
"${SCRIPT_DIR}/collect_pg_metrics.sh"

# 2) Run AI detector
OUT="$("${SCRIPT_DIR}/.venv/bin/python" "${SCRIPT_DIR}/ai_detect.py")" || true
echo "$OUT"

# 3) Parse and alert if needed
anomaly="$(echo "$OUT" | jq -r '.anomaly // false')"
warming="$(echo "$OUT" | jq -r '.warming_up // false')"
score="$(echo "$OUT" | jq -r '.score // empty')"

if [[ "$warming" == "true" ]]; then
  exit 0
fi

if [[ "$anomaly" == "true" ]]; then
  now=$(date +%s)
  last=0
  if [[ -f "$STATE_FILE" ]]; then
    last=$(cat "$STATE_FILE" || echo 0)
  fi
  min_gap=$((ALERT_MINUTES * 60))
  if (( now - last >= min_gap )); then
    echo "$now" > "$STATE_FILE"
    msg="AI DB Monitor: anomaly detected (score=${score}). Host=${PGHOST} DB=${PGDATABASE}"
    if [[ -n "${SLACK_WEBHOOK_URL}" ]]; then
      curl -sS -X POST -H 'Content-type: application/json' \
        --data "$(jq -n --arg text "$msg" '{text: $text}')" \
        "$SLACK_WEBHOOK_URL" >/dev/null || true
    else
      echo "ALERT: $msg" >&2
    fi
  fi
fi

Test locally:

# Export DB connection (or rely on ~/.pgpass)
export PGHOST=localhost PGPORT=5432 PGUSER=postgres PGDATABASE=postgres

# First run (collects one line)
./collect_pg_metrics.sh

# Run monitor (should “warm up” until enough data exists)
./monitor.sh

Tip: Run the collector every minute to build baseline quickly.

---

Step 4 — Schedule with cron

Run the monitor once per minute:

crontab -e

Add:

* * * * * /path/to/monitor.sh >> /var/log/ai_dbmon.log 2>&1

Make sure your cron service is running:

• apt/zypper: sudo systemctl status cron

• dnf: sudo systemctl status crond

Optional load generator (pgbench) to test alerts:

• apt: sudo apt install -y postgresql-contrib

• dnf: sudo dnf install -y postgresql-contrib

• zypper: sudo zypper install -y postgresql-contrib

Then:

# Initialize (against a test DB)
createdb benchdb
pgbench -i -s 5 benchdb
# Spike load for 2 minutes
pgbench -c 10 -T 120 benchdb

Watch /var/log/ai_dbmon.log or your webhook channel for anomalies.

---

Step 5 — What good looks like: real-world signals AI can catch

• Sudden cache miss surge: blks_read_rps spikes while blks_hit_rps drops—often after a cold deployment or bad plan regression.

• Rollback bursts: xact_rollback_rps up with tup_inserted_rps—common after a conflicting migration or broken write path.

• I/O stalls: blk_read_time_rps or blk_write_time_rps increases without matching throughput—potential disk pressure or noisy neighbor.

• Temp file storms: temp_files_rps and temp_bytes_rps spike—hash/merge operations spilling due to low work_mem or bad query.

A typical anomaly event (from ai_detect.py JSON):

{"ok": true, "anomaly": true, "score": -0.12, "features": {"xact_commit_rps": 135.1, "xact_rollback_rps": 7.3, "blks_read_rps": 2100.6, "blks_hit_rps": 150.2, ...}, "rows_used": 420, "epoch_s": 1720800101}

Lower scores mean “more anomalous.” Use this to enrich alerts and dashboards.

---

Tuning and production tips

• Baseline size: Increase MIN_BASELINE to a few hundred samples so the model sees a full cycle of your traffic pattern.

• Contamination: Start with CONTAMINATION=0.02 (2%). If you get too many alerts, lower it; if you miss anomalies, raise slightly (e.g., 0.05).

• Features: Add more signals from pg_stat_bgwriter, pg_stat_statements (e.g., mean_exec_time), and lock/slow query counts.

• Secrets: Prefer ~/.pgpass or environment injection via systemd units instead of committing passwords.

• Alert routing: Swap Slack for email with mailx/mailutils, or post to any webhook-friendly tool (Matrix, Teams, Discord).

Email example (if you installed mailx/mailutils):

echo "AI DB Monitor: anomaly detected (score=$score)" | mail -s "DB Anomaly" you@example.com

---

Why this approach works

• Lightweight: plain Bash + a small Python dependency. No heavy agents or large APM bills.

• Transparent: CSV in, CSV out, easy to debug and customize.

• Extensible: replace the collector for MySQL/MariaDB (SHOW GLOBAL STATUS deltas) or MongoDB (serverStatus) with the same pipeline.

---

Conclusion and next steps

You’ve just built an AI-assisted database monitor from the Linux shell:

• Collected live DB counters

• Learned a behavioral baseline

• Flagged anomalies and alerted on them

Next steps:

• Deploy to staging, tune CONTAMINATION and MIN_BASELINE, and validate alerts.

• Add more features (locks, latency, bgwriter stats).

• Pipe the anomaly score into Grafana/Prometheus for historical context.

If this saved you a 3 a.m. incident already, share it with your team. Got ideas or results to compare? Drop them into your README, or wire this into your existing on-call workflows.

Happy monitoring—and may your alerts be few and useful.