Postgres AIops MCP server

<!-- mcp-name: io.github.AIops-tools/postgres-aiops -->

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 pgstat 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:

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.

• Slow-query RCA — take the worst pgstatstatements entry (plus an optional

recency into a ranked, cited recommendation (VACUUM / tune autovacuum).

• Bloat & vacuum analysis — combine per-table dead-tuple ratio and autovacuum

name the root blocker (blocks others, waits on none), and give the action; a cycle is flagged as a likely deadlock.

• Blocking lock-chain RCA — build the wait-for tree from pgblockingpids,

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 POSTGRESAIOPSMASTER_PASSWORD (MCP/CI) or an interactive prompt (CLI).
• Reversibility: mutating writes fetch the real before-state first and record a faithful inverse — createindex↔dropindex; dropindex captures pggetindexdef so undo recreates it exactly; updatesetting captures the prior value so undo sets it back. Irreversible ops (terminatebackend, cancelquery, runvacuum, runanalyze, reindex, resetquerystats) 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)

Shortened. The full README is on GitHub.