Postgres-AIops is a member of the AIops-tools family aimed at PostgreSQL DBA operations, providing governed AI operations capabilities. It connects to PostgreSQL servers via psycopg 3, reads system catalogs and pg_stat_* views, and has a built-in governance framework, including unified audit logging, token/runaway budget protection, revoked token records, and descriptive risk-level labels. The project is a community-maintained open-source project under the MIT license, is not affiliated with the PostgreSQL Global Development Group or any vendor, and is not affiliated with, endorsed by, or sponsored by them.
In terms of purpose, the tool targets the daily operational tasks of PostgreSQL DBAs, providing three flagship signature analyses along with protected read and write operations built around them. Slow query root cause analysis retrieves the worst pg_stat_statements entries (optionally with EXPLAIN plans), maps their values (average time, cache hit ratio, temporary spill, call count, plan node type) to cited causes and concrete actions, with each finding carrying measured values rather than black-box conclusions. Bloat and vacuum analysis combines per-table dead tuple ratios with the most recent autovacuum execution time to generate ranked, cited recommendations (VACUUM or adjust autovacuum). Blocking lock chain root cause analysis builds a wait tree from pg_blocking_pids, identifies the root blocker (the one blocking others and not waiting on anyone) and gives action recommendations; if a cycle forms, it is flagged as a possible deadlock.
Key capabilities include CLI (postgres-aiops ...) commands: init, overview, server, activity, query, index, table, repl, analyze, remediate, secret, doctor, mcp. The MCP server (postgres-aiops mcp or postgres-aiops-mcp) provides 35 tools (25 read, 10 write), each wrapped in the built-in @governed_tool governance framework. Credential encryption: role passwords are stored in the encrypted store ~/.postgres-aiops/secrets.enc (Fernet + scrypt), and plaintext never appears on disk. It can be accessed via POSTGRES_AIOPS_MASTER_PASSWOUnlocked by RD environment variables (MCP/CI) or interactive prompts (CLI). Reversibility: before a change is written, the real prior state is obtained and a faithful inverse operation is recorded—create_index corresponds to drop_index; drop_index captures pg_get_indexdef, so it can be precisely rebuilt on undo; update_setting captures the previous value and restores it on undo. Irreversible operations (terminate_backend, cancel_query, run_vacuum, run_analyze, reindex, reset_query_stats) record prior statistics for auditing, but are declared non-undoable. Safety: every state-changing CLI operation supports --dry-run and requires double confirmation; every write MCP tool accepts a dry_run preview. All identifiers that cannot be parameterized (table/index/GUC names) are validated and quoted; all values are bound as query parameters.
In terms of usage, it can run as a CLI or as a standalone MCP server (stdio). It can also be installed as a Claude Code plugin, where a single installation provides both the skill and the MCP server; or as an OpenClaw plugin, with the same bundle published on ClawHub, where a single installation delivers both the skill and its MCP server, and after installation the OpenClaw gateway must be restarted to load the plugin. The MCP server is obtained via uv and pinned to the package version declared by the plugin, so that audit lines can be traced back to the code that wrote them. Credentials are still configured via postgres-aiops init. On shared or long-lived hosts, it is recommended to use interactive prompts, or to inject from a secret manager, using it only when that command requires it.
Regarding the governance framework, every MCP tool goes through its own @governed_tool framework: Auditing—every invocation (arguments, result, status, duration, risk level, and any operator-provided approver/reason) is recorded to ~/.postgres-aiops/audit.db (relocatable via POSTGRES_AIOPS_HOME), CLI writes the same audit lines as the MCP path, and there is no unaudited entry point. Runaway protection—a safety fallback rather than an authorization gate: repeated invocations of the same call in a tight loop trigger a circuit breaker. It can be disabled via POSTGRES_RUNAWAY_MAX=0; an optional hard cap is available via POSTGRES_MAX_TOOL_CALLS / POSTGRES_MAX_TOOL_SECONDS. Undo records — reversible writes record an inverse-operation descriptor based on the captured prior state. Risk level — a descriptive label for the audit row derived from risk_level, without any gating.
What the tool decides and does not decide: it delivers PostgreSQL DBA operations (reads and writes) accurately and efficiently and records every operation, but does not decide whether a write is permitted. That is the agent's judgment, or the permissions of the connecting account: connect with a PostgreSQL role that has no write permissions (a read-only role, or a role without INSERT/UPDATE/DELETE/DDL), and writes will fail server-side — where permissions actually reside. Hence there is no read-only switch, policy file, or approval gate to configure. The only guarantee is no silent operations: every call, whether via MCP or CLI, generates an audit row in ~/.postgres-aiops/audit.db, and destructive writes still capture prior state and record an inverse operation when one exists. Each tool declares risk_level as a descriptive level (none/confirm/review) carried into the audit row, so a reviewer can see at a glance that the row is a high-risk delete operation. It is a label, not a gate.
The applicable scenario is PostgreSQL database administrator operational work, including slow query analysis, bloat and vacuum management, lock chain analysis, index management, replication lag monitoring, and more. When running smaller/local models, refer to agent-guardrails.md, which lists the guardrails the tool now enforces as well as ready-made system prompts for the remainder.
Regarding limitations: this project is a community-maintained open-source project, not affiliated with the PostgreSQL Global Development Group, and is not affiliated with, endorsed by, or sponsored by it. Do not use it for OT/industrial edge (Modbus, OPC-UA, PROFINET) — see the separate industrial-aiops series — nor for application schema migrations or ORM management. top_queries / slow_query_rca require the pg_stat_statements extension; read roles should have pg_monitor privileges. Coverage is deliberately a lean subset of the PostgreSQL catalog and maintenance surface; missing views, metrics, or maintenance commands can be raised as an issue or PR. Apart from the simulation test suite,Reads, governed writes, and their undo have been verified on a live PostgreSQL 16.14 instance, as detailed in docs/VERIFICATION.md. Running postgres-aiops doctor is the fastest way to perform a live check.
