84 lines
2.9 KiB
Python
Executable File
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())
|