AIops-tools/Postgres-AIops

> **Disclaimer**: Community-maintained open-source project. **Not affiliated with, endorsed by, or sponsored by the PostgreSQL Global Development Group or any vendor.** "PostgreSQL" and the elephant logo are trademarks of the PostgreSQL Community Association; all product/trademark names belong to their respective owners. MIT licensed.
Governed AI-ops for **PostgreSQL DBA operations** — connecting to a server with
**psycopg 3** and reading the system catalogs and pg_stat_* views — with a
**built-in governance harness**: unified audit log, token/runaway
budget guard, undo-token recording, and descriptive risk-tier labels.
Beyond the mock test suite, the reads, a governed write, and its undo have been
exercised against a live PostgreSQL 16.14 instance — see docs/VERIFICATION.md.
What it does
Three flagship signature analyses, plus the guarded reads and writes around them:
- **Slow-query RCA** — take the worst pg_stat_statements entry (plus an optional
EXPLAIN plan) and map its numbers — mean time, cache-hit ratio, temp spill,
call count, plan node types — to a cited cause and a concrete action. Every
finding carries its measured number, not a black-box verdict.
- **Bloat & vacuum analysis** — combine per-table dead-tuple ratio and autovacuum
recency into a ranked, cited recommendation (VACUUM / tune autovacuum).
- **Blocking lock-chain RCA** — build the wait-for tree from pg_blocking_pids,
name the **root blocker** (blocks others, waits on none), and give the action;
a cycle is flagged as a likely deadlock.
What works
- **CLI** (postgres-aiops ...): init, overview, server, activity, query, index, table, repl, analyze, remediate, secret, doctor, mcp.
- **MCP server** (postgres-aiops mcp or postgres-aiops-mcp): **35 tools** (25 read, 10 write), every one wrapped with the bundled @governed_tool harness.
- **Encrypted credentials**: the role password lives in an encrypted store ~/.postgres-aiops/secrets.enc (Fernet + scrypt) — **never plaintext on disk**. Unlock with a master password from POSTGRES_AIOPS_MASTER_PASSWORD (MCP/CI) or an interactive prompt (CLI).
- **Reversibility**: mutating writes fetch the **real before-state first** and record a faithful inverse — create_index↔drop_index; drop_index captures pg_get_indexdef so undo recreates it exactly; update_setting captures the prior value so undo sets it back. Irreversible ops (terminate_backend, cancel_query, run_vacuum, run_analyze, reindex, reset_query_stats) record prior stats for audit but declare no undo.
- **Safety**: every state-changing CLI op supports --dry-run and requires double confirmation; every write MCP tool takes a dry_run preview. All identifiers that cannot be parameterised (table/index/GUC names) are validated and quoted; all values are bound query parameters.
Capability matrix (35 MCP tools)
| Domain | Tools | Count | R/W |
|--------|-------|:-----:|:---:|
| **Overview** | overview | 1 | read |
| **Server** | server_version, show_settings, list_extensions, list_databases, list_roles | 5 | read |
| **Activity** | list_activity, long_running_queries, list_locks | 3 | read |
| **Queries** | top_queries, explain_query | 2 | read |
| **Indexes** | unused_indexes, missing_index_hints, index_bloat, invalid_indexes | 4 | read |
| **Tables** | table_sizes, table_bloat, autovacuum_status | 3 | read |
| **Replication** | replication_status, replication_slots, wal_status | 3 | read |
| **Analysis (flagship)** | slow_query_rca, bloat_and_vacuum_analysis, blocking_lock_chain_rca | 3 | read |