postgres-aiops

---
name: postgres-aiops
description: >
license: MIT
---

# 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 related trademarks belong to their owners. Source at [github.com/AIops-tools/Postgres-AIops](https://github.com/AIops-tools/Postgres-AIops) under the MIT license.

Governed PostgreSQL DBA operations — **35 MCP tools**, every one wrapped with the bundled `@governed_tool` harness: a local unified audit log under `~/.postgres-aiops/`, token/runaway budget guard, undo-token recording, and descriptive risk-tier labels. The role password is stored **encrypted** (`~/.postgres-aiops/secrets.enc`, Fernet + scrypt) — never plaintext on disk.

> **Standalone**: the governance harness is bundled in the package (`postgres_aiops.governance`) — postgres-aiops has no external skill-family dependency. Beyond the mock suite, the reads plus a governed write and its undo have been exercised against a live PostgreSQL 16.14 instance (see `docs/VERIFICATION.md`).

## What This Skill Does

| Domain | Tools | Count | Read or Write |
|--------|-------|:-----:|:-------------:|
| **Overview** | cluster health snapshot | 1 | 1 read |
| **Server** | version, settings, extensions, databases, roles | 5 | 5 read |
| **Activity** | sessions, long-running queries, locks | 3 | 3 read |
| **Queries** | top-N (pg_stat_statements), EXPLAIN | 2 | 2 read |
| **Indexes** | unused, missing hints, bloat, invalid/duplicate | 4 | 4 read |
| **Tables** | sizes, dead-tuple bloat, autovacuum status | 3 | 3 read |
| **Replication** | status/lag, slots, WAL | 3 | 3 read |
| **Analysis (flagship)** | slow-query RCA, bloat/vacuum, blocking chains | 3 | 3 read |
| **Writes** | terminate, cancel, drop-index | 3 | 3 write (high) |
| | vacuum, analyze, create-index, reindex, ALTER SYSTEM, reset-stats | 6 | 6 write (medium) |

The flagship analyses accept injected records for pure/offline analysis, or pull live from a configured target. `top_queries` / `slow_query_rca` require the `pg_stat_statements` extension; the read role should have `pg_monitor`.

## Quick Install

```bash
uv tool install postgres-aiops
postgres-aiops init       # interactive wizard: connection + encrypted password
postgres-aiops doctor
```

## When to Use This Skill

- Triage a cluster (`overview`): version/uptime, connections by state, idle-in-transaction, longest query, worst bloat, replica lag
- Root-cause a slow query (`analyze slow-query` / `slow_query_rca`): the worst `pg_stat_statements` entry + EXPLAIN → cited cause and action
- Decide what to vacuum (`analyze bloat-vacuum` / `bloat_and_vacuum_analysis`): tables ranked by dead-tuple ratio + autovacuum lag
- Untangle a lock pile-up (`analyze blocking` / `blocking_lock_chain_rca`): the wait-for tree with the root blocker named
- Find unused / missing / bloated indexes; check autovacuum status and table sizes; inspect replication lag and slots
- Terminate/cancel a backend, VACUUM/ANALYZE, create/drop an index (reversible), REINDEX, or ALTER SYSTEM SET — all with dry-run + double-confirm

**Do NOT use when** the target is OT/industrial equipment (use industrial-aiops), a hypervisor, a storage appliance, a backup product, a container cluster, or a non-PostgreSQL database.

## Related Skills — Skill Routing

| If the user wants… | Use |
|--------------------|-----|
| PostgreSQL DBA-ops: slow queries, bloat, locks, index/vacuum maintenance | **postgres-aiops** (this skill) |
| OT / industrial edge (Modbus, OPC-UA, PLC, PROFINET) | the **industrial-aiops** line |
| Hypervisor VM lifecycle (power, snapshot, migrate) | a hypervisor ops skill |
| Container/cluster lifecycle | a cluster ops skill |

## Common Workflows

### "The app got slow this afternoon" — root-cause and add the missing index

1. `postgres-aiops overview` → one-shot cluster picture: connections, database sizes, obvious saturation
2. `postgres-aiops analyze slow-query` → the worst `pg_stat_statements` entry with cited findings (seq scan, low cache-hit ratio, temp spill, high call count) and a concrete action for each
3. `postgres-aiops query explain "<sql>"` → confirm the plan yourself; a Seq Scan on a large table is the index signal
4. `postgres-aiops index missing` → the tool's own index hints, to cross-check that step 3's conclusion is not a one-off
5. `postgres-aiops remediate create-index <table> <col> --concurrently --dry-run` → preview the exact DDL; then run without `--dry-run` (double confirmation). `create_index` is reversible — an inverse `drop_index` is recorded
6. `postgres-aiops query reset` then re-run `analyze slow-query` after a while → confirm the query actually dropped out of the top, rather than assuming
7. **Failure branch**: if the new index does not help, or `--concurrently` left an `INVALID` index (`postgres-aiops index invalid`), roll it back with `postgres-aiops undo list` → `postgres-aiops undo apply <id>`. An invalid index still costs writes — drop it rather than leaving it behind.

### Reclaim table bloat and retire a redundant index (reversible)

1. `postgres-aiops analyze bloat-vacuum` → tables ranked by dead-tuple ratio and autovacuum lag, each citing the measured numbers
2. `postgres-aiops table autovacuum` → check whether autovacuum is simply behind (last run, thresholds) before doing it by hand
3. `postgres-aiops remediate vacuum <table> --analyze --dry-run` → preview; then run for real to `VACUUM ANALYZE` (double confirmation)
4. `postgres-aiops index unused` and `postgres-aiops index bloat` → find indexes that cost writes and return nothing
5. `postgres-aiops remediate drop-index <name> --concurrently --dry-run`, then for real → the tool captures `pg_get_indexdef` **before** dropping and records an inverse recreate descriptor
6. **Failure branch**: dropped the wrong index — `postgres-aiops undo apply <id>` recreates it from the captured definition (not a guess). Note `--full` on `remediate vacuum` takes an **exclusive lock** and rewrites the table; it has no undo, so never reach for it as a first response on a live table.

### Break a blocking pile-up during an incident

1. `postgres-aiops analyze blocking` → the wait-for chain, naming the **root blocker** pid rather than the visible victims
2. `postgres-aiops activity locks` → the raw lock rows behind the chain; confirm the blocker is what the RCA says it is
3. `postgres-aiops activity long --min-seconds 60` → how long the blocker has actually been running, and whether it is idle-in-transaction
4. `postgres-aiops remediate cancel <pid> --dry-run` → preview; then for real. **Cancel before terminate** — cancel ends the query, terminate kills the whole backend and rolls back its transaction
5. Only if cancel does not clear it: `postgres-aiops remediate terminate <pid>` (double confirmation)
6. **Failure branch**: both `cancel_query` and `terminate_backend` declare **no undo** — a killed session cannot be restored. The audit row in `~/.postgres-aiops/audit.db` captures the prior query text and state for the incident write-up. If the same blocker reappears, the fix is upstream (application transaction scope), not another terminate.

### Tune a parameter and prove it moved the needle (reversible)

1. `postgres-aiops server settings work_mem` → the current value and where it came from
2. `postgres-aiops analyze slow-query` → confirm a temp-spill finding is what actually motivates the change
3. `postgres-aiops remediate set work_mem 64MB --dry-run` → preview the `ALTER SYSTEM SET`; then run for real (double confirmation) — the prior value is captured and an inverse `update_setting` is recorded
4. Reload/restart per the parameter's context, then `postgres-aiops server settings work_mem` to confirm the value took effect
5. **Failure branch**: if the change causes memory pressure, `postgres-aiops undo apply <id>` restores the **prior** value. `ALTER SYSTEM` only writes `postgresql.auto.conf` — a parameter with `context = postmaster` needs a restart, so a "successful" write that did not change behaviour usually means the restart is still pending, not that the tool failed.

### Offline analysis (no live cluster)

1. Export `pg_stat_statements`, table-bloat, and blocking-pair rows to JSON
2. Feed them straight to the analysis tools — `slow_query_rca(statements=[...])`, `bloat_and_vacuum_analysis(tables=[...])`, `blocking_lock_chain_rca(pairs=[...])` — no connection or credentials required
3. **Failure branch**: a tool that rejects the injected rows means the export is missing the columns the analysis needs (calls/total_time/rows, dead-tuple counts, blocked/blocking pids) — re-export rather than hand-editing, so the findings stay traceable to the cluster.

## Governance & Safety

The skill delivers reads and writes and records them; it does **not** decide whether a write is
permitted. That is your agent's judgement, or the permission of the account you connect it with
(connect with a PostgreSQL role that has no write privileges (a read-only role, or one without
INSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy
file, or approval gate.

- **Audit is the guarantee, and it is not bypassable.** Every operation — MCP and CLI alike — is logged to `~/.postgres-aiops/audit.db` (relocatable via `POSTGRES_AIOPS_HOME`): params, result, status, duration, and the risk tier. The CLI writes the same row the MCP path does.
- `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations recorded on the audit row (who/why); they are never required and never block.
- **Runaway guard** — a safety backstop, not authorization: the same call looped in a tight window trips a circuit breaker. Disable with `POSTGRES_RUNAWAY_MAX=0`.
- Writes support `--dry-run` / `dry_run=True` and double confirmation at the CLI.
- Reversible writes fetch the real before-state and record an inverse descriptor; irreversible ops (terminate/cancel, vacuum/analyze, reindex, reset stats) record prior stats only.
- All values are bound query parameters; identifiers that cannot be parameterised are validated and quoted.

## References

- `references/capabilities.md` — full tool + field reference
- `references/cli-reference.md` — CLI command reference
- `references/setup-guide.md` — onboarding, credentials, and connectivity