Files

84 lines
2.9 KiB
Python
Executable File

#!/usr/bin/env python3
"""Монитор соединений Postgres (пункт 4).
Раз в 5 минут пишет ряд: всего/idle/active и топ-базы. Предупреждает,
когда занято ≥80% max_connections две проверки подряд (и «отпустило» —
тихо). Нужен, чтобы вовремя решить про PgBouncer, а не узнать о пике
по «too many clients» в логах Hermes.
"""
import json
import os
import subprocess
import sys
import time
from datetime import date
SUP = open("/root/.pg-superuser-pass").read().strip()
DSN = "psql -w -h 192.168.1.151 -U postgres -d postgres -t -A -F'|' -c"
OUTDIR = "/var/lib/hub-pg"
STATE = OUTDIR + "/streak"
NOTIFY = "/opt/buryan-hub/scripts/notify.py"
def pg(q):
import os as _os
env = dict(_os.environ, PGPASSWORD=SUP)
r = subprocess.run(["psql", "-w", "-h", "192.168.1.151", "-U", "postgres",
"-d", "postgres", "-t", "-A", "-F", "|", "-c", q],
capture_output=True, text=True, timeout=30, env=env)
return (r.stdout or "").strip()
def notify(title, text, priority="high"):
try:
subprocess.run(["/usr/bin/python3", NOTIFY, "--priority", priority,
"--title", title, text], capture_output=True,
text=True, timeout=120)
except Exception:
pass
def main():
limit = int(pg("SHOW max_connections;") or 0)
row = pg("SELECT count(*), count(*) FILTER (WHERE state='idle'), "
"count(*) FILTER (WHERE state='active') FROM pg_stat_activity;")
total = idle = active = -1
try:
total, idle, active = [int(x) for x in row.split("|")[:3]]
except Exception:
pass
top = pg("SELECT datname || '=' || count(*) FROM pg_stat_activity "
"WHERE datname IS NOT NULL GROUP BY datname ORDER BY 2 DESC LIMIT 5;")
rec = {"ts": time.strftime("%Y-%m-%dT%H:%M:%S"), "limit": limit,
"total": total, "idle": idle, "active": active,
"top": [x for x in top.splitlines() if x]}
os.makedirs(OUTDIR, exist_ok=True)
with open(os.path.join(OUTDIR, "%s.jsonl" % date.today().isoformat()), "a",
encoding="utf-8") as f:
f.write(json.dumps(rec, ensure_ascii=False) + "\n")
print(json.dumps(rec, ensure_ascii=False))
if limit <= 0 or total < 0:
return 1
pct = 100.0 * total / limit
streak = 0
try:
streak = int(open(STATE).read().strip() or 0)
except Exception:
streak = 0
streak = streak + 1 if pct >= 80 else 0
try:
open(STATE, "w").write(str(streak))
except Exception:
pass
if streak == 2:
notify("Postgres: соединения",
"Занято %d из %d (%.0f%%), idle %d. Нужен PgBouncer или лимиты."
% (total, limit, pct, idle))
return 0
if __name__ == "__main__":
sys.exit(main())