postgres-aiops

Use this skill whenever the user needs to operate or troubleshoot a PostgreSQL server/cluster as a DBA — a one-shot cluster health overview; server reads (version/uptime, settings, extensions, databases, roles); activity (sessions, idle-in-transaction, long-running queries, locks); query stats (pg_stat_statements top-N, EXPLAIN a statement); index health (unused indexes, missing-index hints, bloat, invalid/duplicate); table health (sizes, dead-tuple bloat, autovacuum status); replication (standby lag, replication slots, WAL); three flagship analyses — slow-query RCA (worst pg_stat_statements entry + EXPLAIN → cause/action), bloat & vacuum analysis (dead tuples + autovacuum lag → recommendation), and blocking lock-chain RCA (build the wait-for tree, name the root blocker); and guarded writes (terminate a backend, cancel a query, VACUUM/ANALYZE, create/drop an index, REINDEX, ALTER SYSTEM SET a parameter, reset query stats). Always use this skill for "postgres health check", "why is this query slow", "pg_stat_statements top queries", "EXPLAIN this", "table/index bloat", "which indexes are unused", "missing index", "autovacuum status", "who is blocking whom", "kill the backend holding the lock", "replication lag", "replication slots", "VACUUM this table", "create/drop an index", or "ALTER SYSTEM SET work_mem" when the context is a PostgreSQL database. Do NOT use when the target is OT / industrial equipment (Modbus, OPC-UA, PLCs — use industrial-aiops), a hypervisor, a storage appliance, a backup product, a container/cluster orchestrator, or a non-PostgreSQL database (negative routing hints only). Covers common PostgreSQL DBA operations with a built-in governance harness (audit, token budget, undo, risk-tiers). 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).

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