{"id":"61ff535b-0aa9-41d0-b88a-6d8dd6a55c46","entityType":"agent","slug":"clawhub-zw008-postgres-aiops","name":"postgres-aiops","canonicalUrl":"https://www.xpersona.co/agent/clawhub-zw008-postgres-aiops","canonicalPath":"/agent/clawhub-zw008-postgres-aiops","generatedAt":"2026-10-10T15:50:57.905Z","source":"CLAWHUB","claimStatus":"UNCLAIMED","verificationTier":"NONE","summary":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":null},"description":"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).","descriptionLabel":"Source description","evidenceSummary":"Capability contract not published. No trust telemetry is available yet. 1.4K downloads reported by the source. Last updated 10/10/2026.","installCommand":"clawhub skill install s171xgnmqse0nqvgqvqnaq5f9183kyre:postgres-aiops","sourceUrl":"https://clawhub.ai/zw008/postgres-aiops","homepage":"https://clawhub.ai/zw008/skills/postgres-aiops","primaryLinks":[{"label":"View on ClawHub","url":"https://clawhub.ai/zw008/postgres-aiops","kind":"source"},{"label":"Homepage","url":"https://clawhub.ai/zw008/skills/postgres-aiops","kind":"homepage"}],"safetyScore":84,"overallRank":62,"popularityScore":63,"trustScore":null,"claimedByName":null,"isOwner":false,"seoDescription":"postgres-aiops technical dossier on Xpersona with agent coverage, OPENCLEW support, and live trust metadata."},"coverage":{"evidence":{"source":"public-profile","verified":false,"confidence":"medium","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":null},"protocols":[{"protocol":"OPENCLEW","label":"OpenClaw","status":"self-declared","notes":"Declared in the public agent profile."}],"capabilities":[],"verifiedCount":0,"selfDeclaredCount":1,"capabilityMatrix":{"rows":[{"key":"OPENCLEW","type":"protocol","support":"unknown","confidenceSource":"profile","notes":"Listed on profile"}],"flattenedTokens":"protocol:OPENCLEW|unknown|profile"}},"adoption":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":null},"stars":null,"forks":null,"downloads":1427,"packageName":null,"latestVersion":"0.10.3","tractionLabel":"1.4K downloads"},"release":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":null},"lastUpdatedAt":"2026-10-10T12:45:12.703Z","lastCrawledAt":"2026-10-10T12:45:12.703Z","lastIndexedAt":null,"nextCrawlAt":"2026-10-11T12:45:12.703Z","lastVerifiedAt":null,"highlights":[{"version":"0.10.3","createdAt":"2026-09-15T06:15:14.292Z","changelog":"- Removed the sample skill-card.md file from the package. - No changes to features or functionality.","fileCount":7,"zipByteSize":18184},{"version":"0.10.2","createdAt":"2026-09-12T14:38:42.180Z","changelog":"- Minor update with documentation cleanup and skill card removal. - Removed redundant skill-card.md file. - Updated SKILL.md to improve installation instructions and references. - Changed OpenClaw plugin registry reference in the install docs.","fileCount":7,"zipByteSize":18166},{"version":"0.10.1","createdAt":"2026-09-12T10:22:18.943Z","changelog":"postgres-aiops 0.10.1 - Documentation updates in SKILL.md, including improved installation instructions for use as an OpenClaw plugin. - Added details about MCP server requirements (requires `uvx` on PATH) and clarifications on usage scenarios. - Removed skill-card.md file.","fileCount":7,"zipByteSize":18172},{"version":"0.10.0","createdAt":"2026-09-12T01:10:02.831Z","changelog":"postgres-aiops 0.10.0 - Updated SKILL.md metadata to require either the postgres-aiops or uvx binary, and refined optional environment variable documentation. - Removed the skill-card.md file.","fileCount":7,"zipByteSize":17947},{"version":"0.9.0","createdAt":"2026-08-10T06:53:26.492Z","changelog":"- Removed the sample file skill-card.md. - No user-facing or functional changes to the Postgres AIops skill.","fileCount":7,"zipByteSize":18166},{"version":"0.8.0","createdAt":"2026-08-03T05:54:23.778Z","changelog":"- Removed the file: skill-card.md. - No functionality or behavior was changed; this update only affects bundled documentation files. - All skill operations, features, and compatibility remain unchanged.","fileCount":7,"zipByteSize":18118},{"version":"0.7.0","createdAt":"2026-08-02T09:41:08.131Z","changelog":"- Removed the file skill-card.md. - No functional or user-facing changes; this is a documentation cleanup only. - All features and governance harness remain unchanged.","fileCount":7,"zipByteSize":18125},{"version":"0.6.0","createdAt":"2026-07-21T09:42:32.531Z","changelog":"postgres-aiops 0.6.0 - Governance description clarified across docs: \"policy engine\" term removed to better reflect current functionality; governance now described as audit, token budget, undo, risk-tiers. - SKILL.md and references updated to consistently reflect new governance terminology. - Documentation improved for clarity and accuracy in describing features and usage. - Removed legacy/unused file: skill-card.md.","fileCount":7,"zipByteSize":18011}]},"execution":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No published capability contract is available yet."},"installCommand":"clawhub skill install s171xgnmqse0nqvgqvqnaq5f9183kyre:postgres-aiops","setupComplexity":"low","setupSteps":["Install using `clawhub skill install s171xgnmqse0nqvgqvqnaq5f9183kyre:postgres-aiops` in an isolated environment before connecting it to live workloads.","No published capability contract is available yet, so validate auth and request/response behavior manually.","Review the upstream CLAWHUB listing at https://clawhub.ai/zw008/postgres-aiops before using production credentials."],"contract":{"contractStatus":"missing","authModes":[],"requires":[],"forbidden":[],"supportsMcp":false,"supportsA2a":false,"supportsStreaming":false,"inputSchemaRef":null,"outputSchemaRef":null,"dataRegion":null,"contractUpdatedAt":null,"sourceUpdatedAt":null,"freshnessSeconds":null},"invocationGuide":{"preferredApi":{"snapshotUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/snapshot","contractUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/contract","trustUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/trust"},"curlExamples":["curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/snapshot\"","curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/contract\"","curl -s \"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/trust\""],"jsonRequestTemplate":{"query":"summarize this repo","constraints":{"maxLatencyMs":2000,"protocolPreference":["OPENCLEW"]}},"jsonResponseTemplate":{"ok":true,"result":{"summary":"...","confidence":0.9},"meta":{"source":"CLAWHUB","generatedAt":"2026-10-10T15:50:57.901Z"}},"retryPolicy":{"maxAttempts":3,"backoffMs":[500,1500,3500],"retryableConditions":["HTTP_429","HTTP_503","NETWORK_TIMEOUT"]}},"endpoints":{"dossierUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/dossier","snapshotUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/snapshot","contractUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/contract","trustUrl":"https://www.xpersona.co/api/v1/agents/clawhub-zw008-postgres-aiops/trust"}},"reliability":{"evidence":{"source":"runtime-metrics","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No trust, reliability, or runtime telemetry is available."},"trust":{"status":"unavailable","handshakeStatus":"UNKNOWN","verificationFreshnessHours":null,"reputationScore":null,"p95LatencyMs":null,"successRate30d":null,"fallbackRate":null,"attempts30d":null,"trustUpdatedAt":null,"trustConfidence":"unknown","sourceUpdatedAt":null,"freshnessSeconds":null},"decisionGuardrails":{"doNotUseIf":["Contract metadata is missing or unavailable for deterministic execution."],"safeUseWhen":[],"riskFlags":["missing_or_unavailable_contract","trust_data_unavailable","schema_references_missing"],"operationalConfidence":"low"},"executionMetrics":{"observedLatencyMsP50":null,"observedLatencyMsP95":null,"estimatedCostUsd":null,"uptime30d":null,"rateLimitRpm":null,"rateLimitBurst":null,"lastVerifiedAt":null,"verificationSource":null},"runtimeMetrics":{"successRate":null,"avgLatencyMs":null,"avgCostUsd":null,"hallucinationRate":null,"retryRate":null,"disputeRate":null,"p50Latency":null,"p95Latency":null,"lastUpdated":null}},"benchmarks":{"evidence":{"source":"no-benchmark-data","verified":false,"confidence":"low","updatedAt":null,"emptyReason":"No benchmark suites or observed failure patterns are available."},"suites":[],"failurePatterns":[]},"artifacts":{"evidence":{"source":"CLAWHUB","verified":false,"confidence":"medium","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":null},"readme":"Skill: postgres-aiops\n\nOwner: zw008\n\nSummary: 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).\n\nTags: latest:0.10.3\n\nVersion history:\n\nv0.10.3 | 2026-09-15T06:15:14.292Z | auto\n\n- Removed the sample skill-card.md file from the package.\n- No changes to features or functionality.\n\nv0.10.2 | 2026-09-12T14:38:42.180Z | auto\n\n- Minor update with documentation cleanup and skill card removal.\n- Removed redundant skill-card.md file.\n- Updated SKILL.md to improve installation instructions and references.\n- Changed OpenClaw plugin registry reference in the install docs.\n\nv0.10.1 | 2026-09-12T10:22:18.943Z | auto\n\npostgres-aiops 0.10.1\n\n- Documentation updates in SKILL.md, including improved installation instructions for use as an OpenClaw plugin.\n- Added details about MCP server requirements (requires `uvx` on PATH) and clarifications on usage scenarios.\n- Removed skill-card.md file.\n\nv0.10.0 | 2026-09-12T01:10:02.831Z | auto\n\npostgres-aiops 0.10.0\n\n- Updated SKILL.md metadata to require either the postgres-aiops or uvx binary, and refined optional environment variable documentation.\n- Removed the skill-card.md file.\n\nv0.9.0 | 2026-08-10T06:53:26.492Z | auto\n\n- Removed the sample file skill-card.md.\n- No user-facing or functional changes to the Postgres AIops skill.\n\nv0.8.0 | 2026-08-03T05:54:23.778Z | auto\n\n- Removed the file: skill-card.md.\n- No functionality or behavior was changed; this update only affects bundled documentation files.\n- All skill operations, features, and compatibility remain unchanged.\n\nv0.7.0 | 2026-08-02T09:41:08.131Z | auto\n\n- Removed the file skill-card.md.\n- No functional or user-facing changes; this is a documentation cleanup only.\n- All features and governance harness remain unchanged.\n\nv0.6.0 | 2026-07-21T09:42:32.531Z | auto\n\npostgres-aiops 0.6.0\n\n- Governance description clarified across docs: \"policy engine\" term removed to better reflect current functionality; governance now described as audit, token budget, undo, risk-tiers.\n- SKILL.md and references updated to consistently reflect new governance terminology.\n- Documentation improved for clarity and accuracy in describing features and usage.\n- Removed legacy/unused file: skill-card.md.\n\nv0.5.0 | 2026-07-20T11:16:54.450Z | auto\n\n- Removed the file: skill-card.md\n- No functional or feature changes; maintenance update only.\n\nv0.4.0 | 2026-07-19T03:52:52.805Z | auto\n\n**Postgres AIops 0.4.0 Changelog**\n\n- Added agent guardrails documentation for enhanced operational guidance.\n- Updated flagship analysis and tool count documentation (now 35 tools), with improved summaries and skill metadata.\n- Expanded compatibility and verification status: several live operations now validated against PostgreSQL 16.14; see docs/VERIFICATION.md for coverage details.\n- Clarified governance, safety, and credential handling in all references and docs.\n- Removed deprecated skill-card.md; documentation consolidated and revised for clarity.\n\nv0.3.0 | 2026-07-17T05:56:30.311Z | auto\n\n- Removed the file: skill-card.md\n- No significant changes to functionality or skill description.\n- Minor maintenance: skill-card documentation removed.\n\nv0.2.0 | 2026-07-13T13:09:57.840Z | auto\n\npostgres-aiops 0.2.0\n\n- Removed the skill-card.md file.\n- Updated SKILL.md with latest details—no functional or compatibility changes.\n\nv0.1.0 | 2026-07-13T06:24:32.893Z | auto\n\nInitial release of postgres-aiops (preview):\n\n- Provides governed PostgreSQL DBA operations with 33 tools, all wrapped with an audit/policy/risk/undo harness.\n- Supports cluster health overview, server activity/readings, query stats, index and table health, and replication checks.\n- Includes flagship root-cause analyses: slow-query RCA, bloat/vacuum analysis, and lock-chain RCA.\n- Enables guarded write actions (terminate/cancel backend, VACUUM/ANALYZE, create/drop/reindex index, ALTER SYSTEM SET) with audit, dry-run, and undo.\n- Encrypted credential storage and strict SQL safety included.\n- Standalone, preview-only: runs mock validations, not live operations; credentials never stored in plaintext.\n- Do not use for non-PostgreSQL or OT/industrial targets.\n\nArchive index:\n\nArchive v0.10.3: 7 files, 18184 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2684b), SKILL.md (15905b), _meta.json (134b)\n\nFile v0.10.3:SKILL.md\n\n---\nname: postgres-aiops\nslug: postgres-aiops\ndisplayName: \"Postgres AIops\"\nsummary: \"Governed PostgreSQL DBA ops: slow-query RCA, bloat/vacuum & blocking-lock analysis; 35 MCP tools.\"\nlicense: MIT\nhomepage: https://github.com/AIops-tools/Postgres-AIops\ntags: [aiops, mcp, governance, postgres]\ndescription: >\n  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).\n  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.\n  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).\n  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).\ninstaller:\n  kind: uv\n  package: postgres-aiops\nargument-hint: \"[pid / table / index name or describe your DBA task]\"\nallowed-tools:\n  - Bash\nmetadata: {\"openclaw\":{\"requires\":{\"anyBins\":[\"postgres-aiops\",\"uvx\"]},\"optional\":{\"env\":[\"POSTGRES_AIOPS_CONFIG\",\"POSTGRES_AIOPS_MASTER_PASSWORD\"]},\"homepage\":\"https://github.com/AIops-tools/Postgres-AIops\",\"emoji\":\"🐘\",\"os\":[\"macos\",\"linux\"]}}\ncompatibility: >\n  Standalone, self-governed PostgreSQL DBA operations. The governance harness (audit, token/runaway budget, undo, risk-tiers) is bundled in the package — no external skill-family dependency. Connects via psycopg 3 and reads the system catalogs and pg_stat_* views.\n  All write operations are audited to a local SQLite DB under ~/.postgres-aiops/ (relocatable via POSTGRES_AIOPS_HOME).\n  Credentials: the PostgreSQL role password is stored ENCRYPTED in ~/.postgres-aiops/secrets.enc (Fernet/AES-128 + scrypt-derived key) — never plaintext on disk. Run 'postgres-aiops init' to onboard, or 'postgres-aiops secret set <target>' to add one. The store is unlocked by a master password from POSTGRES_AIOPS_MASTER_PASSWORD (non-interactive/MCP/CI) or an interactive prompt (CLI on a TTY). A legacy plaintext env var PG_<TARGET_NAME_UPPER>_PASSWORD is still honoured as a fallback with a deprecation warning (migrate with 'postgres-aiops secret migrate'). The password is passed to psycopg.connect at connect time and held only in memory; it is never logged or echoed.\n  SQL safety: all values are bound query parameters; the few identifiers that cannot be parameterised (table/index/GUC names, ORDER BY columns, index methods) are validated against strict allow-lists and quoted before interpolation. EXPLAIN rejects multi-statement input.\n  State-changing operations require double confirmation at the CLI layer and support --dry-run. All write tools pass through the @governed_tool decorator (budget guard + audit + risk-tier tagging) and take a dry_run preview. Reversible writes fetch the real before-state first and record a faithful inverse (create_index↔drop_index, where drop captures pg_get_indexdef; update_setting restores the prior value); irreversible ops (terminate/cancel, vacuum/analyze, reindex, reset stats) record prior stats only.\n  Webhooks: none — no outbound network calls beyond the configured PostgreSQL connection.\n  SSL: sslmode follows libpq (default prefer); set require/verify-full on untrusted networks.\n  Transitive dependencies: psycopg[binary] (PostgreSQL driver) and the MCP SDK. No post-install scripts or background services.\n  Verification status: the catalog / pg_stat_* reads, the bloat/vacuum RCA, and the create_index/drop_index governed write path (audit + undo) have been exercised against a live PostgreSQL 16.14 instance; docs/VERIFICATION.md records what was and was not covered. Community-maintained; not affiliated with the PostgreSQL project — trademarks belong to their owners.\n---\n\n# Postgres AIops\n\n> **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.\n\nGoverned 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.\n\n> **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`).\n\n## What This Skill Does\n\n| Domain | Tools | Count | Read or Write |\n|--------|-------|:-----:|:-------------:|\n| **Overview** | cluster health snapshot | 1 | 1 read |\n| **Server** | version, settings, extensions, databases, roles | 5 | 5 read |\n| **Activity** | sessions, long-running queries, locks | 3 | 3 read |\n| **Queries** | top-N (pg_stat_statements), EXPLAIN | 2 | 2 read |\n| **Indexes** | unused, missing hints, bloat, invalid/duplicate | 4 | 4 read |\n| **Tables** | sizes, dead-tuple bloat, autovacuum status | 3 | 3 read |\n| **Replication** | status/lag, slots, WAL | 3 | 3 read |\n| **Analysis (flagship)** | slow-query RCA, bloat/vacuum, blocking chains | 3 | 3 read |\n| **Writes** | terminate, cancel, drop-index | 3 | 3 write (high) |\n| | vacuum, analyze, create-index, reindex, ALTER SYSTEM, reset-stats | 6 | 6 write (medium) |\n\nThe 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`.\n\n## Quick Install\n\n```bash\nuv tool install postgres-aiops\npostgres-aiops init       # interactive wizard: connection + encrypted password\npostgres-aiops doctor\n```\n\nOr as an OpenClaw plugin, which installs this skill and its MCP server together:\n\n```bash\nopenclaw plugins install clawhub:@zw008/postgres-aiops\nopenclaw skills info postgres-aiops          # expect: Visible to model: yes\n```\n\nNeeds `uvx` on `PATH`: the MCP server is fetched with uv, pinned to this release.\n\n## When to Use This Skill\n\n- Triage a cluster (`overview`): version/uptime, connections by state, idle-in-transaction, longest query, worst bloat, replica lag\n- Root-cause a slow query (`analyze slow-query` / `slow_query_rca`): the worst `pg_stat_statements` entry + EXPLAIN → cited cause and action\n- Decide what to vacuum (`analyze bloat-vacuum` / `bloat_and_vacuum_analysis`): tables ranked by dead-tuple ratio + autovacuum lag\n- Untangle a lock pile-up (`analyze blocking` / `blocking_lock_chain_rca`): the wait-for tree with the root blocker named\n- Find unused / missing / bloated indexes; check autovacuum status and table sizes; inspect replication lag and slots\n- Terminate/cancel a backend, VACUUM/ANALYZE, create/drop an index (reversible), REINDEX, or ALTER SYSTEM SET — all with dry-run + double-confirm\n\n**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.\n\n## Related Skills — Skill Routing\n\n| If the user wants… | Use |\n|--------------------|-----|\n| PostgreSQL DBA-ops: slow queries, bloat, locks, index/vacuum maintenance | **postgres-aiops** (this skill) |\n| OT / industrial edge (Modbus, OPC-UA, PLC, PROFINET) | the **industrial-aiops** line |\n| Hypervisor VM lifecycle (power, snapshot, migrate) | a hypervisor ops skill |\n| Container/cluster lifecycle | a cluster ops skill |\n\n## Common Workflows\n\n### \"The app got slow this afternoon\" — root-cause and add the missing index\n\n1. `postgres-aiops overview` → one-shot cluster picture: connections, database sizes, obvious saturation\n2. `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\n3. `postgres-aiops query explain \"<sql>\"` → confirm the plan yourself; a Seq Scan on a large table is the index signal\n4. `postgres-aiops index missing` → the tool's own index hints, to cross-check that step 3's conclusion is not a one-off\n5. `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\n6. `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\n7. **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.\n\n### Reclaim table bloat and retire a redundant index (reversible)\n\n1. `postgres-aiops analyze bloat-vacuum` → tables ranked by dead-tuple ratio and autovacuum lag, each citing the measured numbers\n2. `postgres-aiops table autovacuum` → check whether autovacuum is simply behind (last run, thresholds) before doing it by hand\n3. `postgres-aiops remediate vacuum <table> --analyze --dry-run` → preview; then run for real to `VACUUM ANALYZE` (double confirmation)\n4. `postgres-aiops index unused` and `postgres-aiops index bloat` → find indexes that cost writes and return nothing\n5. `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\n6. **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.\n\n### Break a blocking pile-up during an incident\n\n1. `postgres-aiops analyze blocking` → the wait-for chain, naming the **root blocker** pid rather than the visible victims\n2. `postgres-aiops activity locks` → the raw lock rows behind the chain; confirm the blocker is what the RCA says it is\n3. `postgres-aiops activity long --min-seconds 60` → how long the blocker has actually been running, and whether it is idle-in-transaction\n4. `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\n5. Only if cancel does not clear it: `postgres-aiops remediate terminate <pid>` (double confirmation)\n6. **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.\n\n### Tune a parameter and prove it moved the needle (reversible)\n\n1. `postgres-aiops server settings work_mem` → the current value and where it came from\n2. `postgres-aiops analyze slow-query` → confirm a temp-spill finding is what actually motivates the change\n3. `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\n4. Reload/restart per the parameter's context, then `postgres-aiops server settings work_mem` to confirm the value took effect\n5. **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.\n\n### Offline analysis (no live cluster)\n\n1. Export `pg_stat_statements`, table-bloat, and blocking-pair rows to JSON\n2. 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\n3. **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.\n\n## Governance & Safety\n\nThe skill delivers reads and writes and records them; it does **not** decide whether a write is\npermitted. That is your agent's judgement, or the permission of the account you connect it with\n(connect with a PostgreSQL role that has no write privileges (a read-only role, or one without\nINSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy\nfile, or approval gate.\n\n- **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.\n- `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations recorded on the audit row (who/why); they are never required and never block.\n- **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`.\n- Writes support `--dry-run` / `dry_run=True` and double confirmation at the CLI.\n- 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.\n- All values are bound query parameters; identifiers that cannot be parameterised are validated and quoted.\n\n## References\n\n- `references/capabilities.md` — full tool + field reference\n- `references/cli-reference.md` — CLI command reference\n- `references/setup-guide.md` — onboarding, credentials, and connectivity\n\nFile v0.10.3:_meta.json\n\n{\n  \"ownerId\": \"kn7b067awq2s97bn3d7p5qfhw5827pxc\",\n  \"slug\": \"postgres-aiops\",\n  \"version\": \"0.10.3\",\n  \"publishedAt\": 1789452914292\n}\n\nFile v0.10.3:references/agent-guardrails.md\n\n# Agent guardrails — running postgres-aiops with a smaller / local model\n\nIf you drive these tools with a local model (Llama, Qwen, Mistral … via Goose,\nOllama, LM Studio, or any OpenAI-compatible runtime), you will get noticeably\nbetter results with a short system prompt. This page gives you one, and — more\nimportantly — tells you which guardrails you **no longer need to write**, because\nthe tool now enforces them itself.\n\nThe distinction matters. A guardrail in a prompt is a request. A guardrail in the\nharness is a guarantee. Anything below that we could move into the harness, we did.\n\n## What the tool now enforces — do not waste prompt budget on these\n\n| You might be tempted to prompt | Why you don't need to |\n|---|---|\n| \"Don't invent a value when a field is missing\" | A column the server returned as SQL `NULL` comes back as `null`, never as `\"\"`. `lastAutovacuum: null` means the table was *never* autovacuumed; `unit: null` means the setting is not a numeric quantity; `plugin: null` means the replication slot is physical, not logical. Absent and empty are distinguishable in the payload. |\n| \"Tell me if the output was cut off\" | Anything with a `limit` returns `{\"statements\": [...], \"returned\": N, \"limit\": L, \"truncated\": true/false}`. Truncation is **measured** — one extra row is fetched — not guessed from the row count happening to equal the limit. An analysis that pulled a truncated read also carries `sourceTruncated`. |\n| \"Preserve the ordering / tell me what's most urgent\" | Ranked reads are already ordered worst-first (`table_bloat` by dead tuples, `index_bloat` by estimated bloat, `blocking_lock_chain_rca` by how many backends a root blocker holds up), and every finding cites the number that triggered it. |\n| \"Confirm before anything destructive\" | Destructive operations require a `--dry-run`-able preview + double confirmation at the CLI for high-risk tiers such as `terminate_backend` and `drop_index`. |\n| \"Log what you did\" | Every call is audited to `~/.postgres-aiops/audit.db` regardless of what the model says it did. Reversible writes (`create_index`, `drop_index`, `update_setting`) also record an undo token capturing the pre-change state. |\n\n**Authorization is not this tool's job.** Whether a write is permitted is decided by the account\nyou connect it with (connect with a PostgreSQL role that has no write privileges — a read-only\nrole, or one without INSERT/UPDATE/DELETE/DDL — and the write fails at the server) or by your\nagent's prompt. `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations\nrecorded on the audit row; they are never required and never block a call.\n\n## What still needs a prompt\n\nThese are model-behaviour problems the harness cannot fix from the outside.\nCopy this into your agent's system prompt:\n\n```text\nYou operate a PostgreSQL server through the postgres-aiops MCP tools.\n\nTOOL USE\n- Before answering any question about the current database, you MUST call a\n  tool. Never answer from memory or assumption.\n- Actually invoke the tool. Do not describe the call you would make, and do not\n  emit an example JSON response in place of calling it.\n- If a tool call fails, report the real error verbatim. Never fill the gap with\n  a plausible-sounding answer.\n\nREADING RESULTS\n- Read the whole result before concluding. If a result has \"truncated\": true\n  (or \"sourceTruncated\": true), say so and re-run with a higher limit instead of\n  treating the partial result as complete.\n- A null field means the server returned SQL NULL for that column. Report it as\n  \"not available\" or, where it is meaningful, as what the NULL means — a null\n  lastAutovacuum means the table has never been autovacuumed. Never infer it.\n- Report values exactly as returned. Do not normalise, translate, or prettify\n  states, wait events, LSNs, or identifiers.\n- Cite the measured number from each finding's \"detail\" when explaining a cause.\n\nSCOPE\n- Separate observation from interpretation. State what the tools returned, then\n  any interpretation, clearly marked as such.\n- Do not assert a performance, bloat, or replication problem unless a tool\n  result supports it.\n- Do not add generic PostgreSQL tuning advice that does not follow from the\n  tool output.\n- Keep the identifiers straight. A database, a schema, a relation (table), an\n  index, a backend pid, and a queryid are all different things: a pid is an OS\n  process id from pg_stat_activity, a queryid is a pg_stat_statements\n  fingerprint, and a relation is named schema.table. Never pass one where\n  another is expected, and never invent a schema qualification.\n- pg_stat_statements counters are cumulative since the last stats reset, and\n  idx_scan is cumulative too. Do not describe them as \"recent\" activity.\n```\n\n## Recommended setup for a local model\n\nUntil you trust the setup, connect the tool with a read-only PostgreSQL role\n(no INSERT/UPDATE/DELETE/DDL) — that is real enforcement at the server, not a\nprompt asking the model to behave:\n\n```bash\npostgres-aiops init       # point it at a read-only role\npostgres-aiops doctor\n```\n\nThen, when you are ready to allow writes, point `init` (or `secret set`) at a\nrole with write privileges, and optionally set an approver so the audit trail\ncarries an accountable name:\n\n```bash\nexport POSTGRES_AUDIT_APPROVED_BY=\"your.name@example.com\"\nexport POSTGRES_AUDIT_RATIONALE=\"scheduled maintenance window 2026-07-20\"\n```\n\n## If your model still struggles\n\nSome behaviours are model-capacity limits rather than prompt problems:\n\n- **Multi-tool workflows time out or drift.** Prefer the flagship analyses —\n  `slow_query_rca`, `bloat_and_vacuum_analysis`, `blocking_lock_chain_rca` — and\n  `overview`. They do the multi-step correlation inside one call, so the model\n  does not have to chain reads and keep pids and queryids straight.\n- **The model ignores later tool results in a long context.** Ask narrower\n  questions and use `--limit` deliberately rather than pulling whole catalogs.\n  `show_settings` in particular returns hundreds of rows without a pattern —\n  always pass one.\n- **The model describes calls instead of making them.** This is usually a\n  runtime/tool-calling-format mismatch, not a prompt problem — check that your\n  client advertises the tools in the format your model was trained on.\n\n## A note on verification\n\nUnlike a purely mocked integration, postgres-aiops has been exercised against a\nreal PostgreSQL 16 server: the bloat/vacuum RCA correctly identified a table with\n~50% dead tuples, and the `create_index` / `drop_index` governance path was\nconfirmed end-to-end (audit row written, undo token capturing the prior\n`pg_get_indexdef` output). Treat the read paths as verified and the more exotic\nwrite paths as preview.\n\nFeedback on running this with a specific local model is genuinely useful —\nopen an issue at\n[github.com/AIops-tools/Postgres-AIops](https://github.com/AIops-tools/Postgres-AIops/issues)\nwith the model, runtime, and what went wrong.\n\nFile v0.10.3:references/capabilities.md\n\n# postgres-aiops capabilities\n\n> 35 MCP tools (25 read, 10 write). Catalog / `pg_stat_*` queries have been\n> exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n> `top_queries` / `slow_query_rca` require the `pg_stat_statements` extension;\n> the read role should have `pg_monitor`.\n\n## Read tools (25)\n\n| Tool | Source | Returns |\n|------|--------|---------|\n| `overview` | several reads (resilient) | version, uptime, connections by state, idleInTransaction, longestQuery, worstBloatTable, replicas |\n| `server_version` | `version()`, `pg_postmaster_start_time()` | version, serverVersion, uptime, inRecovery, dataDirectory |\n| `show_settings` | `pg_settings` | name, setting, unit, category, context, source, pendingRestart |\n| `list_extensions` | `pg_extension` + available | name, installedVersion, defaultVersion, updateAvailable |\n| `list_databases` | `pg_database` | name, owner, encoding, sizeBytes, sizePretty |\n| `list_roles` | `pg_roles` | name, superuser, canLogin, replication, connLimit |\n| `list_activity` | `pg_stat_activity` | total, byState, idleInTransaction[], sessions[] |\n| `long_running_queries` | `pg_stat_activity` | thresholdSeconds, count, queries[] (oldest first) |\n| `list_locks` | `pg_locks`⋈`pg_stat_activity` | total, waitingCount, waiting[], locks[] |\n| `top_queries` | `pg_stat_statements` | orderBy, statements[] (calls, total/mean ms, cacheHitRatioPct) |\n| `explain_query` | `EXPLAIN (FORMAT JSON)` | analyze, plan (JSON) |\n| `unused_indexes` | `pg_stat_user_indexes` | count, reclaimableBytes, indexes[] (idx_scan=0) |\n| `missing_index_hints` | `pg_stat_user_tables` | tables[] with high seq_scan vs idx_scan |\n| `index_bloat` | `pg_class`/`pg_index` | indexes[] with estBloatBytes/estBloatPct (coarse) |\n| `invalid_indexes` | `pg_index` | invalid[], duplicates[] |\n| `table_sizes` | `pg_class` | tables[] total/table/index/toast bytes |\n| `table_bloat` | `pg_stat_user_tables` | tables[] deadPct (dead/(live+dead)) |\n| `autovacuum_status` | `pg_stat_user_tables` | dead tuples, modSinceAnalyze, last (auto)vacuum/analyze |\n| `replication_status` | `pg_stat_replication` | replicas[] with replayLagBytes |\n| `replication_slots` | `pg_replication_slots` | slots[], inactive[] (retain WAL) |\n| `wal_status` | WAL fns + `pg_stat_archiver` | currentLsn, walLevel, max/minWalSize, archiver |\n| `slow_query_rca` | pg_stat_statements + EXPLAIN | worst{}, findings[] (cited cause/action) |\n| `bloat_and_vacuum_analysis` | table-bloat rows | recommendations[] (cited reasons + action) |\n| `blocking_lock_chain_rca` | `pg_blocking_pids` pairs | roots[], worstRootPid, deadlockSuspected |\n| `undo_list` | local undo store | recorded, not-yet-applied reversible writes: undoId, ts, originalTool, inverseTool, note |\n\nThe flagship analyses accept injected records (`statements=` / `tables=` /\n`pairs=`) for pure/offline analysis, or pull live from a configured `target`.\n\n## Write tools (10)\n\n| Tool | Risk | SQL | Undo / safety |\n|------|------|-----|---------------|\n| `terminate_backend` | **high** | `pg_terminate_backend(pid)` | captures pid+query for audit; no safe inverse; dry-run + double-confirm |\n| `cancel_query` | **high** | `pg_cancel_backend(pid)` | captures pid+query; no inverse; dry-run + double-confirm |\n| `drop_index` | **high** | `DROP INDEX` | captures `pg_get_indexdef` FIRST; undo = recreate exactly; dry-run + double-confirm |\n| `run_vacuum` | medium | `VACUUM [FULL] [ANALYZE]` | records prior dead-tuple/last-vac stats; no undo |\n| `run_analyze` | medium | `ANALYZE` | records prior stats; no undo |\n| `create_index` | medium | `CREATE [UNIQUE] INDEX [CONCURRENTLY]` | returns created name; undo = drop it |\n| `reindex` | medium | `REINDEX INDEX/TABLE/SCHEMA` | rebuild in place; no undo |\n| `update_setting` | medium | `ALTER SYSTEM SET` | captures prior value; undo = set back; reports pg_reload_conf needed |\n| `reset_query_stats` | medium | `pg_stat_statements_reset()` | irreversible; no undo |\n| `undo_apply` | medium | dispatches the recorded inverse tool | executes a recorded inverse; the inverse runs through its own governed tool (its real risk tier + audit row are recorded there); single-use token; supports `dry_run` |\n\nAll values are bound query parameters; identifiers that cannot be parameterised\n(table/index/GUC names, ORDER BY columns, index methods, REINDEX kinds) are\nvalidated against strict allow-lists and quoted before interpolation.\n\n## Out of scope (by design)\n\n- Application-schema **migrations** / DDL beyond index maintenance\n- ORM / model management\n- Logical or physical **backup/restore** orchestration (pg_dump, PITR)\n- Role/grant management and `CREATE`/`DROP DATABASE`\n- OT / industrial equipment (use the `industrial-aiops` line)\n\nWant one of these? Open an issue or PR — feedback and contributions welcome.\n\nFile v0.10.3:references/cli-reference.md\n\n# postgres-aiops CLI reference\n\n> Catalog / `pg_stat_*` queries have been exercised against a live PostgreSQL 16.14 instance\n> (see docs/VERIFICATION.md).\n\n## Setup & diagnostics\n\n```bash\npostgres-aiops init                      # interactive onboarding wizard\npostgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())\npostgres-aiops overview [--target <t>]   # one-shot cluster health snapshot\npostgres-aiops mcp                       # start the MCP server (stdio transport)\n```\n\n## Secrets (encrypted store ~/.postgres-aiops/secrets.enc)\n\n```bash\npostgres-aiops secret set <target> [--value <pw>]    # store password (hidden prompt if no --value)\npostgres-aiops secret list                            # names only — values never shown\npostgres-aiops secret rm <target>\npostgres-aiops secret migrate                         # import legacy plaintext .env (PG_<T>_PASSWORD)\npostgres-aiops secret rotate-password                 # re-encrypt under a new master password\n```\n\n## Read commands\n\n```bash\npostgres-aiops server version                 # version, uptime, recovery state\npostgres-aiops server settings [pattern]      # pg_settings (optional name filter)\npostgres-aiops server databases               # databases + sizes\npostgres-aiops server roles\npostgres-aiops server extensions\n\npostgres-aiops activity list [--state active] # pg_stat_activity + per-state counts\npostgres-aiops activity long [--min-seconds 60]\npostgres-aiops activity locks\n\npostgres-aiops query top [--order-by total_time] [--limit 20]   # pg_stat_statements\npostgres-aiops query explain \"<sql>\" [--analyze]\n\npostgres-aiops index unused                   # zero-scan indexes\npostgres-aiops index missing                  # missing-index hints\npostgres-aiops index bloat [--limit 50]\npostgres-aiops index invalid                  # invalid + duplicate\n\npostgres-aiops table sizes [--limit 20]\npostgres-aiops table bloat [--limit 50]       # dead-tuple bloat proxy\npostgres-aiops table autovacuum [--limit 50]\n\npostgres-aiops repl status                    # standby lag\npostgres-aiops repl slots\npostgres-aiops repl wal\n\npostgres-aiops analyze slow-query [--explain \"<sql>\"] [--limit 20]   # flagship RCA\npostgres-aiops analyze bloat-vacuum [--limit 50]\npostgres-aiops analyze blocking\n```\n\n## Write commands (governed; risk tier in parentheses)\n\n```bash\npostgres-aiops remediate terminate <pid> [--dry-run]                     # (high) no undo; double confirm\npostgres-aiops remediate cancel <pid> [--dry-run]                        # (high) no undo; double confirm\npostgres-aiops remediate drop-index <name> [--concurrently] [--dry-run]  # (high) reversible; double confirm\npostgres-aiops remediate vacuum <table> [--full] [--analyze] [--dry-run] # (medium)\npostgres-aiops remediate analyze-table <table> [--dry-run]               # (medium)\npostgres-aiops remediate create-index <table> <cols...> [--name N] [--unique] [--concurrently] [--dry-run]  # (medium) reversible\npostgres-aiops remediate reindex <name> [--kind INDEX|TABLE|SCHEMA] [--concurrently] [--dry-run]            # (medium)\npostgres-aiops remediate set <name> <value> [--dry-run]                  # (medium) ALTER SYSTEM; reversible\n```\n\n## Common options\n\n- `--target, -t <name>` — target name from `config.yaml` (omit to use the default/first target)\n- `--dry-run` — print the statement that would run, change nothing\n- State-changing commands require two confirmations at the CLI layer\n\n## Truncation\n\nEvery command that takes `--limit` returns an envelope — `{\"...\": [...],\n\"returned\": N, \"limit\": L, \"truncated\": bool}` — and fetches one row past the\nlimit so `truncated` is measured, not inferred from the row count. When a read\nis cut short the JSON on stdout stays clean and a notice is written to stderr:\n\n```\n… truncated at 50 rows (50 returned) — re-run with a higher --limit to see the rest.\n```\n\n`analyze slow-query` / `analyze bloat-vacuum` pull a limited read themselves, so\nthey also report `sourceTruncated` / `sourceLimit` when their input was partial.\n\n## What decides whether a write runs\n\nThe tool does not decide whether a write is permitted — that is the agent's\njudgement, or the permission of the PostgreSQL role you connect it with:\nconnect with a role that has no write privileges (a read-only role, or one\nwithout INSERT/UPDATE/DELETE/DDL) and the write fails at the server. Every\ncall, over MCP and over the CLI alike, is still audited. See\n[agent-guardrails.md](agent-guardrails.md).\n\nFile v0.10.3:references/setup-guide.md\n\n# postgres-aiops setup & security guide\n\n> Reads, a governed write, and its undo have been exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n\n## 1. Install\n\n```bash\nuv tool install postgres-aiops\n```\n\n## 2. Prepare a role\n\npostgres-aiops connects with psycopg 3 and reads the system catalogs and\n`pg_stat_*` views. A least-privilege monitoring role works for the reads:\n\n```sql\nCREATE ROLE aiops LOGIN PASSWORD 'change-me';\nGRANT pg_monitor TO aiops;                 -- read visibility into pg_stat_*\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- for top_queries / slow_query_rca\n```\n\nMaintenance writes (VACUUM, CREATE/DROP INDEX, REINDEX) require ownership of the\ntarget objects; `ALTER SYSTEM` requires a superuser or `pg_read_all_settings` +\nthe appropriate privilege.\n\n## 3. Onboard\n\n```bash\npostgres-aiops init\n```\n\nThe wizard collects (non-secret) connection details into\n`~/.postgres-aiops/config.yaml` and stores the password **encrypted** into\n`~/.postgres-aiops/secrets.enc`. Example config:\n\n```yaml\ntargets:\n  - name: primary\n    host: 10.0.0.30\n    port: 5432\n    dbname: appdb\n    user: aiops\n    sslmode: require          # disable/allow/prefer/require/verify-ca/verify-full\n```\n\n## 4. Non-interactive use (MCP server / CI / cron)\n\nExport the master password so the encrypted store can be unlocked without a\nprompt:\n\n```bash\nexport POSTGRES_AIOPS_MASTER_PASSWORD='your-master-password'\n```\n\n## Credential security\n\n- The password is **never** written to disk in plaintext. It lives only in\n  `~/.postgres-aiops/secrets.enc`, encrypted with Fernet (AES-128-CBC + HMAC),\n  the key derived from your master password via scrypt. Only a per-store random\n  salt and the ciphertext are on disk (chmod 600); the master password itself is\n  never stored.\n- A legacy plaintext env var `PG_<TARGET_NAME_UPPER>_PASSWORD` is still honoured\n  as a fallback with a deprecation warning — migrate with `postgres-aiops secret\n  migrate` (it imports then renames the old `.env`).\n- The password is passed to `psycopg.connect` at connect time and held only in\n  memory; it is never logged or echoed. Exception text and tracebacks are\n  scrubbed of secret-shaped strings before being written to the audit log.\n\n## SQL safety\n\n- All values (pids, thresholds, limits, setting values) are **bound query\n  parameters** — never string-formatted into SQL.\n- The few identifiers that cannot be parameterised (table/index/GUC names,\n  `ORDER BY` columns, index methods, `REINDEX` kinds) are validated against\n  strict allow-lists and double-quoted before interpolation; anything that is not\n  a plain identifier is rejected.\n- `EXPLAIN` rejects multi-statement input (an embedded `;` is refused).\n\n## Governance harness state\n\nState lives under `~/.postgres-aiops/` (relocate with `POSTGRES_AIOPS_HOME`):\n\n- `audit.db` — every tool call (SQLite), with risk tier and an optional\n  approver/rationale annotation (`POSTGRES_AUDIT_APPROVED_BY` /\n  `POSTGRES_AUDIT_RATIONALE` — never required, never blocking)\n- `undo.db` — inverse descriptors for reversible writes (e.g. `drop_index`)\n- budget / runaway guard — caps cumulative tool calls and wall-time; trips on\n  tight poll/retry loops\n\n## Verify\n\n```bash\npostgres-aiops doctor\n```\n\n`doctor` checks the config file, the encrypted store and its permissions, that a\npassword is present per target, and (unless `--skip-auth`) connectivity by\nrunning `SELECT version()`.\n\nFile v0.10.3:skill-card.md\n\n## Description:\n\nPostgres AIops helps agents operate and troubleshoot PostgreSQL clusters with health checks, catalog and pg_stat reads, RCA analyses for slow queries, bloat/vacuum and blocking locks, and governed DBA write commands.\n\nThis skill is ready for commercial/non-commercial use.\n\n## Publisher:\n\n[zw008](https://clawhub.ai/user/zw008)\n\n### License/Terms of Use:\n\nMIT-0\n\n## Use Case:\n\nDevelopers, database administrators, and operations engineers use this skill to inspect PostgreSQL health, diagnose slow queries, bloat, autovacuum, replication, and blocking locks, and prepare or run governed maintenance actions.\n\n### Deployment Geography for Use:\n\nGlobal\n\n## Known Risks and Mitigations:\n\nRisk: The skill can invoke powerful PostgreSQL state-changing operations without an enforced approval gate.\n\nMitigation: Connect with a read-only pg_monitor-style role by default, require a separate human approval process for destructive operations, and reserve elevated privileges for approved maintenance windows.\n\nRisk: The release evidence flags installation of an unpinned external implementation.\n\nMitigation: Use a pinned, reviewed postgres-aiops package version and run the skill from an isolated account or container.\n\nRisk: Database credentials and audit state are local operational assets that can expose or affect production systems if mishandled.\n\nMitigation: Set POSTGRES_AIOPS_HOME to a protected location, protect the master password, and verify filesystem permissions before connecting to sensitive databases.\n\n## Reference(s):\n\n- [postgres-aiops capabilities](references/capabilities.md)\n- [postgres-aiops CLI reference](references/cli-reference.md)\n- [postgres-aiops setup & security guide](references/setup-guide.md)\n- [Agent guardrails](references/agent-guardrails.md)\n- [Postgres AIops GitHub repository](https://github.com/AIops-tools/Postgres-AIops)\n- [ClawHub skill page](https://clawhub.ai/zw008/skills/postgres-aiops)\n\n## Skill Output:\n\n**Output Type(s):** [text, markdown, shell commands, configuration, guidance]\n\n**Output Format:** [Markdown guidance with inline shell commands, configuration snippets, and structured database findings.]\n\n**Output Parameters:** [1D]\n\n**Other Properties Related to Output:** [May include risk-tiered recommendations, dry-run commands, and notes when source database reads are truncated.]\n\n## Skill Version(s):\n\n0.10.3 (source: server release evidence)\n\n## Ethical Considerations:\n\nUsers should evaluate whether this skill is appropriate for their environment, review any generated or modified files before relying on them, and apply their organization's safety, security, and compliance requirements before deployment.\n\nArchive v0.10.2: 7 files, 18166 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2608b), SKILL.md (15905b), _meta.json (134b)\n\nFile v0.10.2:SKILL.md\n\n---\nname: postgres-aiops\nslug: postgres-aiops\ndisplayName: \"Postgres AIops\"\nsummary: \"Governed PostgreSQL DBA ops: slow-query RCA, bloat/vacuum & blocking-lock analysis; 35 MCP tools.\"\nlicense: MIT\nhomepage: https://github.com/AIops-tools/Postgres-AIops\ntags: [aiops, mcp, governance, postgres]\ndescription: >\n  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).\n  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.\n  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).\n  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).\ninstaller:\n  kind: uv\n  package: postgres-aiops\nargument-hint: \"[pid / table / index name or describe your DBA task]\"\nallowed-tools:\n  - Bash\nmetadata: {\"openclaw\":{\"requires\":{\"anyBins\":[\"postgres-aiops\",\"uvx\"]},\"optional\":{\"env\":[\"POSTGRES_AIOPS_CONFIG\",\"POSTGRES_AIOPS_MASTER_PASSWORD\"]},\"homepage\":\"https://github.com/AIops-tools/Postgres-AIops\",\"emoji\":\"🐘\",\"os\":[\"macos\",\"linux\"]}}\ncompatibility: >\n  Standalone, self-governed PostgreSQL DBA operations. The governance harness (audit, token/runaway budget, undo, risk-tiers) is bundled in the package — no external skill-family dependency. Connects via psycopg 3 and reads the system catalogs and pg_stat_* views.\n  All write operations are audited to a local SQLite DB under ~/.postgres-aiops/ (relocatable via POSTGRES_AIOPS_HOME).\n  Credentials: the PostgreSQL role password is stored ENCRYPTED in ~/.postgres-aiops/secrets.enc (Fernet/AES-128 + scrypt-derived key) — never plaintext on disk. Run 'postgres-aiops init' to onboard, or 'postgres-aiops secret set <target>' to add one. The store is unlocked by a master password from POSTGRES_AIOPS_MASTER_PASSWORD (non-interactive/MCP/CI) or an interactive prompt (CLI on a TTY). A legacy plaintext env var PG_<TARGET_NAME_UPPER>_PASSWORD is still honoured as a fallback with a deprecation warning (migrate with 'postgres-aiops secret migrate'). The password is passed to psycopg.connect at connect time and held only in memory; it is never logged or echoed.\n  SQL safety: all values are bound query parameters; the few identifiers that cannot be parameterised (table/index/GUC names, ORDER BY columns, index methods) are validated against strict allow-lists and quoted before interpolation. EXPLAIN rejects multi-statement input.\n  State-changing operations require double confirmation at the CLI layer and support --dry-run. All write tools pass through the @governed_tool decorator (budget guard + audit + risk-tier tagging) and take a dry_run preview. Reversible writes fetch the real before-state first and record a faithful inverse (create_index↔drop_index, where drop captures pg_get_indexdef; update_setting restores the prior value); irreversible ops (terminate/cancel, vacuum/analyze, reindex, reset stats) record prior stats only.\n  Webhooks: none — no outbound network calls beyond the configured PostgreSQL connection.\n  SSL: sslmode follows libpq (default prefer); set require/verify-full on untrusted networks.\n  Transitive dependencies: psycopg[binary] (PostgreSQL driver) and the MCP SDK. No post-install scripts or background services.\n  Verification status: the catalog / pg_stat_* reads, the bloat/vacuum RCA, and the create_index/drop_index governed write path (audit + undo) have been exercised against a live PostgreSQL 16.14 instance; docs/VERIFICATION.md records what was and was not covered. Community-maintained; not affiliated with the PostgreSQL project — trademarks belong to their owners.\n---\n\n# Postgres AIops\n\n> **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.\n\nGoverned 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.\n\n> **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`).\n\n## What This Skill Does\n\n| Domain | Tools | Count | Read or Write |\n|--------|-------|:-----:|:-------------:|\n| **Overview** | cluster health snapshot | 1 | 1 read |\n| **Server** | version, settings, extensions, databases, roles | 5 | 5 read |\n| **Activity** | sessions, long-running queries, locks | 3 | 3 read |\n| **Queries** | top-N (pg_stat_statements), EXPLAIN | 2 | 2 read |\n| **Indexes** | unused, missing hints, bloat, invalid/duplicate | 4 | 4 read |\n| **Tables** | sizes, dead-tuple bloat, autovacuum status | 3 | 3 read |\n| **Replication** | status/lag, slots, WAL | 3 | 3 read |\n| **Analysis (flagship)** | slow-query RCA, bloat/vacuum, blocking chains | 3 | 3 read |\n| **Writes** | terminate, cancel, drop-index | 3 | 3 write (high) |\n| | vacuum, analyze, create-index, reindex, ALTER SYSTEM, reset-stats | 6 | 6 write (medium) |\n\nThe 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`.\n\n## Quick Install\n\n```bash\nuv tool install postgres-aiops\npostgres-aiops init       # interactive wizard: connection + encrypted password\npostgres-aiops doctor\n```\n\nOr as an OpenClaw plugin, which installs this skill and its MCP server together:\n\n```bash\nopenclaw plugins install clawhub:@zw008/postgres-aiops\nopenclaw skills info postgres-aiops          # expect: Visible to model: yes\n```\n\nNeeds `uvx` on `PATH`: the MCP server is fetched with uv, pinned to this release.\n\n## When to Use This Skill\n\n- Triage a cluster (`overview`): version/uptime, connections by state, idle-in-transaction, longest query, worst bloat, replica lag\n- Root-cause a slow query (`analyze slow-query` / `slow_query_rca`): the worst `pg_stat_statements` entry + EXPLAIN → cited cause and action\n- Decide what to vacuum (`analyze bloat-vacuum` / `bloat_and_vacuum_analysis`): tables ranked by dead-tuple ratio + autovacuum lag\n- Untangle a lock pile-up (`analyze blocking` / `blocking_lock_chain_rca`): the wait-for tree with the root blocker named\n- Find unused / missing / bloated indexes; check autovacuum status and table sizes; inspect replication lag and slots\n- Terminate/cancel a backend, VACUUM/ANALYZE, create/drop an index (reversible), REINDEX, or ALTER SYSTEM SET — all with dry-run + double-confirm\n\n**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.\n\n## Related Skills — Skill Routing\n\n| If the user wants… | Use |\n|--------------------|-----|\n| PostgreSQL DBA-ops: slow queries, bloat, locks, index/vacuum maintenance | **postgres-aiops** (this skill) |\n| OT / industrial edge (Modbus, OPC-UA, PLC, PROFINET) | the **industrial-aiops** line |\n| Hypervisor VM lifecycle (power, snapshot, migrate) | a hypervisor ops skill |\n| Container/cluster lifecycle | a cluster ops skill |\n\n## Common Workflows\n\n### \"The app got slow this afternoon\" — root-cause and add the missing index\n\n1. `postgres-aiops overview` → one-shot cluster picture: connections, database sizes, obvious saturation\n2. `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\n3. `postgres-aiops query explain \"<sql>\"` → confirm the plan yourself; a Seq Scan on a large table is the index signal\n4. `postgres-aiops index missing` → the tool's own index hints, to cross-check that step 3's conclusion is not a one-off\n5. `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\n6. `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\n7. **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.\n\n### Reclaim table bloat and retire a redundant index (reversible)\n\n1. `postgres-aiops analyze bloat-vacuum` → tables ranked by dead-tuple ratio and autovacuum lag, each citing the measured numbers\n2. `postgres-aiops table autovacuum` → check whether autovacuum is simply behind (last run, thresholds) before doing it by hand\n3. `postgres-aiops remediate vacuum <table> --analyze --dry-run` → preview; then run for real to `VACUUM ANALYZE` (double confirmation)\n4. `postgres-aiops index unused` and `postgres-aiops index bloat` → find indexes that cost writes and return nothing\n5. `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\n6. **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.\n\n### Break a blocking pile-up during an incident\n\n1. `postgres-aiops analyze blocking` → the wait-for chain, naming the **root blocker** pid rather than the visible victims\n2. `postgres-aiops activity locks` → the raw lock rows behind the chain; confirm the blocker is what the RCA says it is\n3. `postgres-aiops activity long --min-seconds 60` → how long the blocker has actually been running, and whether it is idle-in-transaction\n4. `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\n5. Only if cancel does not clear it: `postgres-aiops remediate terminate <pid>` (double confirmation)\n6. **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.\n\n### Tune a parameter and prove it moved the needle (reversible)\n\n1. `postgres-aiops server settings work_mem` → the current value and where it came from\n2. `postgres-aiops analyze slow-query` → confirm a temp-spill finding is what actually motivates the change\n3. `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\n4. Reload/restart per the parameter's context, then `postgres-aiops server settings work_mem` to confirm the value took effect\n5. **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.\n\n### Offline analysis (no live cluster)\n\n1. Export `pg_stat_statements`, table-bloat, and blocking-pair rows to JSON\n2. 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\n3. **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.\n\n## Governance & Safety\n\nThe skill delivers reads and writes and records them; it does **not** decide whether a write is\npermitted. That is your agent's judgement, or the permission of the account you connect it with\n(connect with a PostgreSQL role that has no write privileges (a read-only role, or one without\nINSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy\nfile, or approval gate.\n\n- **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.\n- `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations recorded on the audit row (who/why); they are never required and never block.\n- **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`.\n- Writes support `--dry-run` / `dry_run=True` and double confirmation at the CLI.\n- 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.\n- All values are bound query parameters; identifiers that cannot be parameterised are validated and quoted.\n\n## References\n\n- `references/capabilities.md` — full tool + field reference\n- `references/cli-reference.md` — CLI command reference\n- `references/setup-guide.md` — onboarding, credentials, and connectivity\n\nFile v0.10.2:_meta.json\n\n{\n  \"ownerId\": \"kn7b067awq2s97bn3d7p5qfhw5827pxc\",\n  \"slug\": \"postgres-aiops\",\n  \"version\": \"0.10.2\",\n  \"publishedAt\": 1789223922180\n}\n\nFile v0.10.2:references/agent-guardrails.md\n\n# Agent guardrails — running postgres-aiops with a smaller / local model\n\nIf you drive these tools with a local model (Llama, Qwen, Mistral … via Goose,\nOllama, LM Studio, or any OpenAI-compatible runtime), you will get noticeably\nbetter results with a short system prompt. This page gives you one, and — more\nimportantly — tells you which guardrails you **no longer need to write**, because\nthe tool now enforces them itself.\n\nThe distinction matters. A guardrail in a prompt is a request. A guardrail in the\nharness is a guarantee. Anything below that we could move into the harness, we did.\n\n## What the tool now enforces — do not waste prompt budget on these\n\n| You might be tempted to prompt | Why you don't need to |\n|---|---|\n| \"Don't invent a value when a field is missing\" | A column the server returned as SQL `NULL` comes back as `null`, never as `\"\"`. `lastAutovacuum: null` means the table was *never* autovacuumed; `unit: null` means the setting is not a numeric quantity; `plugin: null` means the replication slot is physical, not logical. Absent and empty are distinguishable in the payload. |\n| \"Tell me if the output was cut off\" | Anything with a `limit` returns `{\"statements\": [...], \"returned\": N, \"limit\": L, \"truncated\": true/false}`. Truncation is **measured** — one extra row is fetched — not guessed from the row count happening to equal the limit. An analysis that pulled a truncated read also carries `sourceTruncated`. |\n| \"Preserve the ordering / tell me what's most urgent\" | Ranked reads are already ordered worst-first (`table_bloat` by dead tuples, `index_bloat` by estimated bloat, `blocking_lock_chain_rca` by how many backends a root blocker holds up), and every finding cites the number that triggered it. |\n| \"Confirm before anything destructive\" | Destructive operations require a `--dry-run`-able preview + double confirmation at the CLI for high-risk tiers such as `terminate_backend` and `drop_index`. |\n| \"Log what you did\" | Every call is audited to `~/.postgres-aiops/audit.db` regardless of what the model says it did. Reversible writes (`create_index`, `drop_index`, `update_setting`) also record an undo token capturing the pre-change state. |\n\n**Authorization is not this tool's job.** Whether a write is permitted is decided by the account\nyou connect it with (connect with a PostgreSQL role that has no write privileges — a read-only\nrole, or one without INSERT/UPDATE/DELETE/DDL — and the write fails at the server) or by your\nagent's prompt. `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations\nrecorded on the audit row; they are never required and never block a call.\n\n## What still needs a prompt\n\nThese are model-behaviour problems the harness cannot fix from the outside.\nCopy this into your agent's system prompt:\n\n```text\nYou operate a PostgreSQL server through the postgres-aiops MCP tools.\n\nTOOL USE\n- Before answering any question about the current database, you MUST call a\n  tool. Never answer from memory or assumption.\n- Actually invoke the tool. Do not describe the call you would make, and do not\n  emit an example JSON response in place of calling it.\n- If a tool call fails, report the real error verbatim. Never fill the gap with\n  a plausible-sounding answer.\n\nREADING RESULTS\n- Read the whole result before concluding. If a result has \"truncated\": true\n  (or \"sourceTruncated\": true), say so and re-run with a higher limit instead of\n  treating the partial result as complete.\n- A null field means the server returned SQL NULL for that column. Report it as\n  \"not available\" or, where it is meaningful, as what the NULL means — a null\n  lastAutovacuum means the table has never been autovacuumed. Never infer it.\n- Report values exactly as returned. Do not normalise, translate, or prettify\n  states, wait events, LSNs, or identifiers.\n- Cite the measured number from each finding's \"detail\" when explaining a cause.\n\nSCOPE\n- Separate observation from interpretation. State what the tools returned, then\n  any interpretation, clearly marked as such.\n- Do not assert a performance, bloat, or replication problem unless a tool\n  result supports it.\n- Do not add generic PostgreSQL tuning advice that does not follow from the\n  tool output.\n- Keep the identifiers straight. A database, a schema, a relation (table), an\n  index, a backend pid, and a queryid are all different things: a pid is an OS\n  process id from pg_stat_activity, a queryid is a pg_stat_statements\n  fingerprint, and a relation is named schema.table. Never pass one where\n  another is expected, and never invent a schema qualification.\n- pg_stat_statements counters are cumulative since the last stats reset, and\n  idx_scan is cumulative too. Do not describe them as \"recent\" activity.\n```\n\n## Recommended setup for a local model\n\nUntil you trust the setup, connect the tool with a read-only PostgreSQL role\n(no INSERT/UPDATE/DELETE/DDL) — that is real enforcement at the server, not a\nprompt asking the model to behave:\n\n```bash\npostgres-aiops init       # point it at a read-only role\npostgres-aiops doctor\n```\n\nThen, when you are ready to allow writes, point `init` (or `secret set`) at a\nrole with write privileges, and optionally set an approver so the audit trail\ncarries an accountable name:\n\n```bash\nexport POSTGRES_AUDIT_APPROVED_BY=\"your.name@example.com\"\nexport POSTGRES_AUDIT_RATIONALE=\"scheduled maintenance window 2026-07-20\"\n```\n\n## If your model still struggles\n\nSome behaviours are model-capacity limits rather than prompt problems:\n\n- **Multi-tool workflows time out or drift.** Prefer the flagship analyses —\n  `slow_query_rca`, `bloat_and_vacuum_analysis`, `blocking_lock_chain_rca` — and\n  `overview`. They do the multi-step correlation inside one call, so the model\n  does not have to chain reads and keep pids and queryids straight.\n- **The model ignores later tool results in a long context.** Ask narrower\n  questions and use `--limit` deliberately rather than pulling whole catalogs.\n  `show_settings` in particular returns hundreds of rows without a pattern —\n  always pass one.\n- **The model describes calls instead of making them.** This is usually a\n  runtime/tool-calling-format mismatch, not a prompt problem — check that your\n  client advertises the tools in the format your model was trained on.\n\n## A note on verification\n\nUnlike a purely mocked integration, postgres-aiops has been exercised against a\nreal PostgreSQL 16 server: the bloat/vacuum RCA correctly identified a table with\n~50% dead tuples, and the `create_index` / `drop_index` governance path was\nconfirmed end-to-end (audit row written, undo token capturing the prior\n`pg_get_indexdef` output). Treat the read paths as verified and the more exotic\nwrite paths as preview.\n\nFeedback on running this with a specific local model is genuinely useful —\nopen an issue at\n[github.com/AIops-tools/Postgres-AIops](https://github.com/AIops-tools/Postgres-AIops/issues)\nwith the model, runtime, and what went wrong.\n\nFile v0.10.2:references/capabilities.md\n\n# postgres-aiops capabilities\n\n> 35 MCP tools (25 read, 10 write). Catalog / `pg_stat_*` queries have been\n> exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n> `top_queries` / `slow_query_rca` require the `pg_stat_statements` extension;\n> the read role should have `pg_monitor`.\n\n## Read tools (25)\n\n| Tool | Source | Returns |\n|------|--------|---------|\n| `overview` | several reads (resilient) | version, uptime, connections by state, idleInTransaction, longestQuery, worstBloatTable, replicas |\n| `server_version` | `version()`, `pg_postmaster_start_time()` | version, serverVersion, uptime, inRecovery, dataDirectory |\n| `show_settings` | `pg_settings` | name, setting, unit, category, context, source, pendingRestart |\n| `list_extensions` | `pg_extension` + available | name, installedVersion, defaultVersion, updateAvailable |\n| `list_databases` | `pg_database` | name, owner, encoding, sizeBytes, sizePretty |\n| `list_roles` | `pg_roles` | name, superuser, canLogin, replication, connLimit |\n| `list_activity` | `pg_stat_activity` | total, byState, idleInTransaction[], sessions[] |\n| `long_running_queries` | `pg_stat_activity` | thresholdSeconds, count, queries[] (oldest first) |\n| `list_locks` | `pg_locks`⋈`pg_stat_activity` | total, waitingCount, waiting[], locks[] |\n| `top_queries` | `pg_stat_statements` | orderBy, statements[] (calls, total/mean ms, cacheHitRatioPct) |\n| `explain_query` | `EXPLAIN (FORMAT JSON)` | analyze, plan (JSON) |\n| `unused_indexes` | `pg_stat_user_indexes` | count, reclaimableBytes, indexes[] (idx_scan=0) |\n| `missing_index_hints` | `pg_stat_user_tables` | tables[] with high seq_scan vs idx_scan |\n| `index_bloat` | `pg_class`/`pg_index` | indexes[] with estBloatBytes/estBloatPct (coarse) |\n| `invalid_indexes` | `pg_index` | invalid[], duplicates[] |\n| `table_sizes` | `pg_class` | tables[] total/table/index/toast bytes |\n| `table_bloat` | `pg_stat_user_tables` | tables[] deadPct (dead/(live+dead)) |\n| `autovacuum_status` | `pg_stat_user_tables` | dead tuples, modSinceAnalyze, last (auto)vacuum/analyze |\n| `replication_status` | `pg_stat_replication` | replicas[] with replayLagBytes |\n| `replication_slots` | `pg_replication_slots` | slots[], inactive[] (retain WAL) |\n| `wal_status` | WAL fns + `pg_stat_archiver` | currentLsn, walLevel, max/minWalSize, archiver |\n| `slow_query_rca` | pg_stat_statements + EXPLAIN | worst{}, findings[] (cited cause/action) |\n| `bloat_and_vacuum_analysis` | table-bloat rows | recommendations[] (cited reasons + action) |\n| `blocking_lock_chain_rca` | `pg_blocking_pids` pairs | roots[], worstRootPid, deadlockSuspected |\n| `undo_list` | local undo store | recorded, not-yet-applied reversible writes: undoId, ts, originalTool, inverseTool, note |\n\nThe flagship analyses accept injected records (`statements=` / `tables=` /\n`pairs=`) for pure/offline analysis, or pull live from a configured `target`.\n\n## Write tools (10)\n\n| Tool | Risk | SQL | Undo / safety |\n|------|------|-----|---------------|\n| `terminate_backend` | **high** | `pg_terminate_backend(pid)` | captures pid+query for audit; no safe inverse; dry-run + double-confirm |\n| `cancel_query` | **high** | `pg_cancel_backend(pid)` | captures pid+query; no inverse; dry-run + double-confirm |\n| `drop_index` | **high** | `DROP INDEX` | captures `pg_get_indexdef` FIRST; undo = recreate exactly; dry-run + double-confirm |\n| `run_vacuum` | medium | `VACUUM [FULL] [ANALYZE]` | records prior dead-tuple/last-vac stats; no undo |\n| `run_analyze` | medium | `ANALYZE` | records prior stats; no undo |\n| `create_index` | medium | `CREATE [UNIQUE] INDEX [CONCURRENTLY]` | returns created name; undo = drop it |\n| `reindex` | medium | `REINDEX INDEX/TABLE/SCHEMA` | rebuild in place; no undo |\n| `update_setting` | medium | `ALTER SYSTEM SET` | captures prior value; undo = set back; reports pg_reload_conf needed |\n| `reset_query_stats` | medium | `pg_stat_statements_reset()` | irreversible; no undo |\n| `undo_apply` | medium | dispatches the recorded inverse tool | executes a recorded inverse; the inverse runs through its own governed tool (its real risk tier + audit row are recorded there); single-use token; supports `dry_run` |\n\nAll values are bound query parameters; identifiers that cannot be parameterised\n(table/index/GUC names, ORDER BY columns, index methods, REINDEX kinds) are\nvalidated against strict allow-lists and quoted before interpolation.\n\n## Out of scope (by design)\n\n- Application-schema **migrations** / DDL beyond index maintenance\n- ORM / model management\n- Logical or physical **backup/restore** orchestration (pg_dump, PITR)\n- Role/grant management and `CREATE`/`DROP DATABASE`\n- OT / industrial equipment (use the `industrial-aiops` line)\n\nWant one of these? Open an issue or PR — feedback and contributions welcome.\n\nFile v0.10.2:references/cli-reference.md\n\n# postgres-aiops CLI reference\n\n> Catalog / `pg_stat_*` queries have been exercised against a live PostgreSQL 16.14 instance\n> (see docs/VERIFICATION.md).\n\n## Setup & diagnostics\n\n```bash\npostgres-aiops init                      # interactive onboarding wizard\npostgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())\npostgres-aiops overview [--target <t>]   # one-shot cluster health snapshot\npostgres-aiops mcp                       # start the MCP server (stdio transport)\n```\n\n## Secrets (encrypted store ~/.postgres-aiops/secrets.enc)\n\n```bash\npostgres-aiops secret set <target> [--value <pw>]    # store password (hidden prompt if no --value)\npostgres-aiops secret list                            # names only — values never shown\npostgres-aiops secret rm <target>\npostgres-aiops secret migrate                         # import legacy plaintext .env (PG_<T>_PASSWORD)\npostgres-aiops secret rotate-password                 # re-encrypt under a new master password\n```\n\n## Read commands\n\n```bash\npostgres-aiops server version                 # version, uptime, recovery state\npostgres-aiops server settings [pattern]      # pg_settings (optional name filter)\npostgres-aiops server databases               # databases + sizes\npostgres-aiops server roles\npostgres-aiops server extensions\n\npostgres-aiops activity list [--state active] # pg_stat_activity + per-state counts\npostgres-aiops activity long [--min-seconds 60]\npostgres-aiops activity locks\n\npostgres-aiops query top [--order-by total_time] [--limit 20]   # pg_stat_statements\npostgres-aiops query explain \"<sql>\" [--analyze]\n\npostgres-aiops index unused                   # zero-scan indexes\npostgres-aiops index missing                  # missing-index hints\npostgres-aiops index bloat [--limit 50]\npostgres-aiops index invalid                  # invalid + duplicate\n\npostgres-aiops table sizes [--limit 20]\npostgres-aiops table bloat [--limit 50]       # dead-tuple bloat proxy\npostgres-aiops table autovacuum [--limit 50]\n\npostgres-aiops repl status                    # standby lag\npostgres-aiops repl slots\npostgres-aiops repl wal\n\npostgres-aiops analyze slow-query [--explain \"<sql>\"] [--limit 20]   # flagship RCA\npostgres-aiops analyze bloat-vacuum [--limit 50]\npostgres-aiops analyze blocking\n```\n\n## Write commands (governed; risk tier in parentheses)\n\n```bash\npostgres-aiops remediate terminate <pid> [--dry-run]                     # (high) no undo; double confirm\npostgres-aiops remediate cancel <pid> [--dry-run]                        # (high) no undo; double confirm\npostgres-aiops remediate drop-index <name> [--concurrently] [--dry-run]  # (high) reversible; double confirm\npostgres-aiops remediate vacuum <table> [--full] [--analyze] [--dry-run] # (medium)\npostgres-aiops remediate analyze-table <table> [--dry-run]               # (medium)\npostgres-aiops remediate create-index <table> <cols...> [--name N] [--unique] [--concurrently] [--dry-run]  # (medium) reversible\npostgres-aiops remediate reindex <name> [--kind INDEX|TABLE|SCHEMA] [--concurrently] [--dry-run]            # (medium)\npostgres-aiops remediate set <name> <value> [--dry-run]                  # (medium) ALTER SYSTEM; reversible\n```\n\n## Common options\n\n- `--target, -t <name>` — target name from `config.yaml` (omit to use the default/first target)\n- `--dry-run` — print the statement that would run, change nothing\n- State-changing commands require two confirmations at the CLI layer\n\n## Truncation\n\nEvery command that takes `--limit` returns an envelope — `{\"...\": [...],\n\"returned\": N, \"limit\": L, \"truncated\": bool}` — and fetches one row past the\nlimit so `truncated` is measured, not inferred from the row count. When a read\nis cut short the JSON on stdout stays clean and a notice is written to stderr:\n\n```\n… truncated at 50 rows (50 returned) — re-run with a higher --limit to see the rest.\n```\n\n`analyze slow-query` / `analyze bloat-vacuum` pull a limited read themselves, so\nthey also report `sourceTruncated` / `sourceLimit` when their input was partial.\n\n## What decides whether a write runs\n\nThe tool does not decide whether a write is permitted — that is the agent's\njudgement, or the permission of the PostgreSQL role you connect it with:\nconnect with a role that has no write privileges (a read-only role, or one\nwithout INSERT/UPDATE/DELETE/DDL) and the write fails at the server. Every\ncall, over MCP and over the CLI alike, is still audited. See\n[agent-guardrails.md](agent-guardrails.md).\n\nFile v0.10.2:references/setup-guide.md\n\n# postgres-aiops setup & security guide\n\n> Reads, a governed write, and its undo have been exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n\n## 1. Install\n\n```bash\nuv tool install postgres-aiops\n```\n\n## 2. Prepare a role\n\npostgres-aiops connects with psycopg 3 and reads the system catalogs and\n`pg_stat_*` views. A least-privilege monitoring role works for the reads:\n\n```sql\nCREATE ROLE aiops LOGIN PASSWORD 'change-me';\nGRANT pg_monitor TO aiops;                 -- read visibility into pg_stat_*\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- for top_queries / slow_query_rca\n```\n\nMaintenance writes (VACUUM, CREATE/DROP INDEX, REINDEX) require ownership of the\ntarget objects; `ALTER SYSTEM` requires a superuser or `pg_read_all_settings` +\nthe appropriate privilege.\n\n## 3. Onboard\n\n```bash\npostgres-aiops init\n```\n\nThe wizard collects (non-secret) connection details into\n`~/.postgres-aiops/config.yaml` and stores the password **encrypted** into\n`~/.postgres-aiops/secrets.enc`. Example config:\n\n```yaml\ntargets:\n  - name: primary\n    host: 10.0.0.30\n    port: 5432\n    dbname: appdb\n    user: aiops\n    sslmode: require          # disable/allow/prefer/require/verify-ca/verify-full\n```\n\n## 4. Non-interactive use (MCP server / CI / cron)\n\nExport the master password so the encrypted store can be unlocked without a\nprompt:\n\n```bash\nexport POSTGRES_AIOPS_MASTER_PASSWORD='your-master-password'\n```\n\n## Credential security\n\n- The password is **never** written to disk in plaintext. It lives only in\n  `~/.postgres-aiops/secrets.enc`, encrypted with Fernet (AES-128-CBC + HMAC),\n  the key derived from your master password via scrypt. Only a per-store random\n  salt and the ciphertext are on disk (chmod 600); the master password itself is\n  never stored.\n- A legacy plaintext env var `PG_<TARGET_NAME_UPPER>_PASSWORD` is still honoured\n  as a fallback with a deprecation warning — migrate with `postgres-aiops secret\n  migrate` (it imports then renames the old `.env`).\n- The password is passed to `psycopg.connect` at connect time and held only in\n  memory; it is never logged or echoed. Exception text and tracebacks are\n  scrubbed of secret-shaped strings before being written to the audit log.\n\n## SQL safety\n\n- All values (pids, thresholds, limits, setting values) are **bound query\n  parameters** — never string-formatted into SQL.\n- The few identifiers that cannot be parameterised (table/index/GUC names,\n  `ORDER BY` columns, index methods, `REINDEX` kinds) are validated against\n  strict allow-lists and double-quoted before interpolation; anything that is not\n  a plain identifier is rejected.\n- `EXPLAIN` rejects multi-statement input (an embedded `;` is refused).\n\n## Governance harness state\n\nState lives under `~/.postgres-aiops/` (relocate with `POSTGRES_AIOPS_HOME`):\n\n- `audit.db` — every tool call (SQLite), with risk tier and an optional\n  approver/rationale annotation (`POSTGRES_AUDIT_APPROVED_BY` /\n  `POSTGRES_AUDIT_RATIONALE` — never required, never blocking)\n- `undo.db` — inverse descriptors for reversible writes (e.g. `drop_index`)\n- budget / runaway guard — caps cumulative tool calls and wall-time; trips on\n  tight poll/retry loops\n\n## Verify\n\n```bash\npostgres-aiops doctor\n```\n\n`doctor` checks the config file, the encrypted store and its permissions, that a\npassword is present per target, and (unless `--skip-auth`) connectivity by\nrunning `SELECT version()`.\n\nFile v0.10.2:skill-card.md\n\n## Description:\n\nPostgres AIops helps agents operate and troubleshoot PostgreSQL clusters as a DBA, including health checks, query and index diagnostics, lock analysis, replication checks, and governed maintenance writes.\n\nThis skill is ready for commercial/non-commercial use.\n\n## Publisher:\n\n[zw008](https://clawhub.ai/user/zw008)\n\n### License/Terms of Use:\n\nMIT-0\n\n## Use Case:\n\nDevelopers, SREs, and database administrators use this skill to inspect PostgreSQL cluster health, diagnose slow queries, bloat, locks, and replication issues, and plan or execute guarded maintenance operations.\n\n### Deployment Geography for Use:\n\nGlobal\n\n## Known Risks and Mitigations:\n\nRisk: The skill can make live PostgreSQL changes, including terminating sessions, dropping indexes, and changing server settings.\n\nMitigation: Start with a dedicated read-only PostgreSQL role and enable write-capable credentials only for planned maintenance where those actions are acceptable.\n\nRisk: Credential and local state exposure could affect database access because the skill stores configuration and encrypted secrets locally.\n\nMitigation: Protect POSTGRES_AIOPS_HOME and POSTGRES_AIOPS_MASTER_PASSWORD, keep the local state directory access-restricted, and migrate away from legacy plaintext password environment variables.\n\nRisk: A reproducible pinned install is not enforced by the skill evidence.\n\nMitigation: Verify the postgres-aiops package source and version before installation, and pin the package version where the runtime supports it.\n\n## Reference(s):\n\n- [ClawHub skill page](https://clawhub.ai/zw008/skills/postgres-aiops)\n- [Project homepage](https://github.com/AIops-tools/Postgres-AIops)\n- [Capabilities reference](references/capabilities.md)\n- [CLI reference](references/cli-reference.md)\n- [Setup and security guide](references/setup-guide.md)\n- [Agent guardrails](references/agent-guardrails.md)\n\n## Skill Output:\n\n**Output Type(s):** [text, markdown, shell commands, configuration, guidance]\n\n**Output Format:** [Markdown guidance with inline shell commands and JSON-like tool result summaries]\n\n**Output Parameters:** [1D]\n\n**Other Properties Related to Output:** [May include dry-run SQL previews, RCA findings, audit and undo guidance, and PostgreSQL configuration steps.]\n\n## Skill Version(s):\n\n0.10.2 (source: server release metadata)\n\n## Ethical Considerations:\n\nUsers should evaluate whether this skill is appropriate for their environment, review any generated or modified files before relying on them, and apply their organization's safety, security, and compliance requirements before deployment.\n\nArchive v0.10.1: 7 files, 18172 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2645b), SKILL.md (15911b), _meta.json (134b)\n\nFile v0.10.1:SKILL.md\n\n---\nname: postgres-aiops\nslug: postgres-aiops\ndisplayName: \"Postgres AIops\"\nsummary: \"Governed PostgreSQL DBA ops: slow-query RCA, bloat/vacuum & blocking-lock analysis; 35 MCP tools.\"\nlicense: MIT\nhomepage: https://github.com/AIops-tools/Postgres-AIops\ntags: [aiops, mcp, governance, postgres]\ndescription: >\n  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).\n  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.\n  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).\n  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).\ninstaller:\n  kind: uv\n  package: postgres-aiops\nargument-hint: \"[pid / table / index name or describe your DBA task]\"\nallowed-tools:\n  - Bash\nmetadata: {\"openclaw\":{\"requires\":{\"anyBins\":[\"postgres-aiops\",\"uvx\"]},\"optional\":{\"env\":[\"POSTGRES_AIOPS_CONFIG\",\"POSTGRES_AIOPS_MASTER_PASSWORD\"]},\"homepage\":\"https://github.com/AIops-tools/Postgres-AIops\",\"emoji\":\"🐘\",\"os\":[\"macos\",\"linux\"]}}\ncompatibility: >\n  Standalone, self-governed PostgreSQL DBA operations. The governance harness (audit, token/runaway budget, undo, risk-tiers) is bundled in the package — no external skill-family dependency. Connects via psycopg 3 and reads the system catalogs and pg_stat_* views.\n  All write operations are audited to a local SQLite DB under ~/.postgres-aiops/ (relocatable via POSTGRES_AIOPS_HOME).\n  Credentials: the PostgreSQL role password is stored ENCRYPTED in ~/.postgres-aiops/secrets.enc (Fernet/AES-128 + scrypt-derived key) — never plaintext on disk. Run 'postgres-aiops init' to onboard, or 'postgres-aiops secret set <target>' to add one. The store is unlocked by a master password from POSTGRES_AIOPS_MASTER_PASSWORD (non-interactive/MCP/CI) or an interactive prompt (CLI on a TTY). A legacy plaintext env var PG_<TARGET_NAME_UPPER>_PASSWORD is still honoured as a fallback with a deprecation warning (migrate with 'postgres-aiops secret migrate'). The password is passed to psycopg.connect at connect time and held only in memory; it is never logged or echoed.\n  SQL safety: all values are bound query parameters; the few identifiers that cannot be parameterised (table/index/GUC names, ORDER BY columns, index methods) are validated against strict allow-lists and quoted before interpolation. EXPLAIN rejects multi-statement input.\n  State-changing operations require double confirmation at the CLI layer and support --dry-run. All write tools pass through the @governed_tool decorator (budget guard + audit + risk-tier tagging) and take a dry_run preview. Reversible writes fetch the real before-state first and record a faithful inverse (create_index↔drop_index, where drop captures pg_get_indexdef; update_setting restores the prior value); irreversible ops (terminate/cancel, vacuum/analyze, reindex, reset stats) record prior stats only.\n  Webhooks: none — no outbound network calls beyond the configured PostgreSQL connection.\n  SSL: sslmode follows libpq (default prefer); set require/verify-full on untrusted networks.\n  Transitive dependencies: psycopg[binary] (PostgreSQL driver) and the MCP SDK. No post-install scripts or background services.\n  Verification status: the catalog / pg_stat_* reads, the bloat/vacuum RCA, and the create_index/drop_index governed write path (audit + undo) have been exercised against a live PostgreSQL 16.14 instance; docs/VERIFICATION.md records what was and was not covered. Community-maintained; not affiliated with the PostgreSQL project — trademarks belong to their owners.\n---\n\n# Postgres AIops\n\n> **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.\n\nGoverned 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.\n\n> **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`).\n\n## What This Skill Does\n\n| Domain | Tools | Count | Read or Write |\n|--------|-------|:-----:|:-------------:|\n| **Overview** | cluster health snapshot | 1 | 1 read |\n| **Server** | version, settings, extensions, databases, roles | 5 | 5 read |\n| **Activity** | sessions, long-running queries, locks | 3 | 3 read |\n| **Queries** | top-N (pg_stat_statements), EXPLAIN | 2 | 2 read |\n| **Indexes** | unused, missing hints, bloat, invalid/duplicate | 4 | 4 read |\n| **Tables** | sizes, dead-tuple bloat, autovacuum status | 3 | 3 read |\n| **Replication** | status/lag, slots, WAL | 3 | 3 read |\n| **Analysis (flagship)** | slow-query RCA, bloat/vacuum, blocking chains | 3 | 3 read |\n| **Writes** | terminate, cancel, drop-index | 3 | 3 write (high) |\n| | vacuum, analyze, create-index, reindex, ALTER SYSTEM, reset-stats | 6 | 6 write (medium) |\n\nThe 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`.\n\n## Quick Install\n\n```bash\nuv tool install postgres-aiops\npostgres-aiops init       # interactive wizard: connection + encrypted password\npostgres-aiops doctor\n```\n\nOr as an OpenClaw plugin, which installs this skill and its MCP server together:\n\n```bash\nopenclaw plugins install clawhub:@aiops-tools/postgres-aiops\nopenclaw skills info postgres-aiops          # expect: Visible to model: yes\n```\n\nNeeds `uvx` on `PATH`: the MCP server is fetched with uv, pinned to this release.\n\n## When to Use This Skill\n\n- Triage a cluster (`overview`): version/uptime, connections by state, idle-in-transaction, longest query, worst bloat, replica lag\n- Root-cause a slow query (`analyze slow-query` / `slow_query_rca`): the worst `pg_stat_statements` entry + EXPLAIN → cited cause and action\n- Decide what to vacuum (`analyze bloat-vacuum` / `bloat_and_vacuum_analysis`): tables ranked by dead-tuple ratio + autovacuum lag\n- Untangle a lock pile-up (`analyze blocking` / `blocking_lock_chain_rca`): the wait-for tree with the root blocker named\n- Find unused / missing / bloated indexes; check autovacuum status and table sizes; inspect replication lag and slots\n- Terminate/cancel a backend, VACUUM/ANALYZE, create/drop an index (reversible), REINDEX, or ALTER SYSTEM SET — all with dry-run + double-confirm\n\n**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.\n\n## Related Skills — Skill Routing\n\n| If the user wants… | Use |\n|--------------------|-----|\n| PostgreSQL DBA-ops: slow queries, bloat, locks, index/vacuum maintenance | **postgres-aiops** (this skill) |\n| OT / industrial edge (Modbus, OPC-UA, PLC, PROFINET) | the **industrial-aiops** line |\n| Hypervisor VM lifecycle (power, snapshot, migrate) | a hypervisor ops skill |\n| Container/cluster lifecycle | a cluster ops skill |\n\n## Common Workflows\n\n### \"The app got slow this afternoon\" — root-cause and add the missing index\n\n1. `postgres-aiops overview` → one-shot cluster picture: connections, database sizes, obvious saturation\n2. `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\n3. `postgres-aiops query explain \"<sql>\"` → confirm the plan yourself; a Seq Scan on a large table is the index signal\n4. `postgres-aiops index missing` → the tool's own index hints, to cross-check that step 3's conclusion is not a one-off\n5. `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\n6. `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\n7. **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.\n\n### Reclaim table bloat and retire a redundant index (reversible)\n\n1. `postgres-aiops analyze bloat-vacuum` → tables ranked by dead-tuple ratio and autovacuum lag, each citing the measured numbers\n2. `postgres-aiops table autovacuum` → check whether autovacuum is simply behind (last run, thresholds) before doing it by hand\n3. `postgres-aiops remediate vacuum <table> --analyze --dry-run` → preview; then run for real to `VACUUM ANALYZE` (double confirmation)\n4. `postgres-aiops index unused` and `postgres-aiops index bloat` → find indexes that cost writes and return nothing\n5. `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\n6. **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.\n\n### Break a blocking pile-up during an incident\n\n1. `postgres-aiops analyze blocking` → the wait-for chain, naming the **root blocker** pid rather than the visible victims\n2. `postgres-aiops activity locks` → the raw lock rows behind the chain; confirm the blocker is what the RCA says it is\n3. `postgres-aiops activity long --min-seconds 60` → how long the blocker has actually been running, and whether it is idle-in-transaction\n4. `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\n5. Only if cancel does not clear it: `postgres-aiops remediate terminate <pid>` (double confirmation)\n6. **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.\n\n### Tune a parameter and prove it moved the needle (reversible)\n\n1. `postgres-aiops server settings work_mem` → the current value and where it came from\n2. `postgres-aiops analyze slow-query` → confirm a temp-spill finding is what actually motivates the change\n3. `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\n4. Reload/restart per the parameter's context, then `postgres-aiops server settings work_mem` to confirm the value took effect\n5. **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.\n\n### Offline analysis (no live cluster)\n\n1. Export `pg_stat_statements`, table-bloat, and blocking-pair rows to JSON\n2. 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\n3. **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.\n\n## Governance & Safety\n\nThe skill delivers reads and writes and records them; it does **not** decide whether a write is\npermitted. That is your agent's judgement, or the permission of the account you connect it with\n(connect with a PostgreSQL role that has no write privileges (a read-only role, or one without\nINSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy\nfile, or approval gate.\n\n- **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.\n- `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations recorded on the audit row (who/why); they are never required and never block.\n- **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`.\n- Writes support `--dry-run` / `dry_run=True` and double confirmation at the CLI.\n- 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.\n- All values are bound query parameters; identifiers that cannot be parameterised are validated and quoted.\n\n## References\n\n- `references/capabilities.md` — full tool + field reference\n- `references/cli-reference.md` — CLI command reference\n- `references/setup-guide.md` — onboarding, credentials, and connectivity\n\nFile v0.10.1:_meta.json\n\n{\n  \"ownerId\": \"kn7b067awq2s97bn3d7p5qfhw5827pxc\",\n  \"slug\": \"postgres-aiops\",\n  \"version\": \"0.10.1\",\n  \"publishedAt\": 1789208538943\n}\n\nFile v0.10.1:references/agent-guardrails.md\n\n# Agent guardrails — running postgres-aiops with a smaller / local model\n\nIf you drive these tools with a local model (Llama, Qwen, Mistral … via Goose,\nOllama, LM Studio, or any OpenAI-compatible runtime), you will get noticeably\nbetter results with a short system prompt. This page gives you one, and — more\nimportantly — tells you which guardrails you **no longer need to write**, because\nthe tool now enforces them itself.\n\nThe distinction matters. A guardrail in a prompt is a request. A guardrail in the\nharness is a guarantee. Anything below that we could move into the harness, we did.\n\n## What the tool now enforces — do not waste prompt budget on these\n\n| You might be tempted to prompt | Why you don't need to |\n|---|---|\n| \"Don't invent a value when a field is missing\" | A column the server returned as SQL `NULL` comes back as `null`, never as `\"\"`. `lastAutovacuum: null` means the table was *never* autovacuumed; `unit: null` means the setting is not a numeric quantity; `plugin: null` means the replication slot is physical, not logical. Absent and empty are distinguishable in the payload. |\n| \"Tell me if the output was cut off\" | Anything with a `limit` returns `{\"statements\": [...], \"returned\": N, \"limit\": L, \"truncated\": true/false}`. Truncation is **measured** — one extra row is fetched — not guessed from the row count happening to equal the limit. An analysis that pulled a truncated read also carries `sourceTruncated`. |\n| \"Preserve the ordering / tell me what's most urgent\" | Ranked reads are already ordered worst-first (`table_bloat` by dead tuples, `index_bloat` by estimated bloat, `blocking_lock_chain_rca` by how many backends a root blocker holds up), and every finding cites the number that triggered it. |\n| \"Confirm before anything destructive\" | Destructive operations require a `--dry-run`-able preview + double confirmation at the CLI for high-risk tiers such as `terminate_backend` and `drop_index`. |\n| \"Log what you did\" | Every call is audited to `~/.postgres-aiops/audit.db` regardless of what the model says it did. Reversible writes (`create_index`, `drop_index`, `update_setting`) also record an undo token capturing the pre-change state. |\n\n**Authorization is not this tool's job.** Whether a write is permitted is decided by the account\nyou connect it with (connect with a PostgreSQL role that has no write privileges — a read-only\nrole, or one without INSERT/UPDATE/DELETE/DDL — and the write fails at the server) or by your\nagent's prompt. `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations\nrecorded on the audit row; they are never required and never block a call.\n\n## What still needs a prompt\n\nThese are model-behaviour problems the harness cannot fix from the outside.\nCopy this into your agent's system prompt:\n\n```text\nYou operate a PostgreSQL server through the postgres-aiops MCP tools.\n\nTOOL USE\n- Before answering any question about the current database, you MUST call a\n  tool. Never answer from memory or assumption.\n- Actually invoke the tool. Do not describe the call you would make, and do not\n  emit an example JSON response in place of calling it.\n- If a tool call fails, report the real error verbatim. Never fill the gap with\n  a plausible-sounding answer.\n\nREADING RESULTS\n- Read the whole result before concluding. If a result has \"truncated\": true\n  (or \"sourceTruncated\": true), say so and re-run with a higher limit instead of\n  treating the partial result as complete.\n- A null field means the server returned SQL NULL for that column. Report it as\n  \"not available\" or, where it is meaningful, as what the NULL means — a null\n  lastAutovacuum means the table has never been autovacuumed. Never infer it.\n- Report values exactly as returned. Do not normalise, translate, or prettify\n  states, wait events, LSNs, or identifiers.\n- Cite the measured number from each finding's \"detail\" when explaining a cause.\n\nSCOPE\n- Separate observation from interpretation. State what the tools returned, then\n  any interpretation, clearly marked as such.\n- Do not assert a performance, bloat, or replication problem unless a tool\n  result supports it.\n- Do not add generic PostgreSQL tuning advice that does not follow from the\n  tool output.\n- Keep the identifiers straight. A database, a schema, a relation (table), an\n  index, a backend pid, and a queryid are all different things: a pid is an OS\n  process id from pg_stat_activity, a queryid is a pg_stat_statements\n  fingerprint, and a relation is named schema.table. Never pass one where\n  another is expected, and never invent a schema qualification.\n- pg_stat_statements counters are cumulative since the last stats reset, and\n  idx_scan is cumulative too. Do not describe them as \"recent\" activity.\n```\n\n## Recommended setup for a local model\n\nUntil you trust the setup, connect the tool with a read-only PostgreSQL role\n(no INSERT/UPDATE/DELETE/DDL) — that is real enforcement at the server, not a\nprompt asking the model to behave:\n\n```bash\npostgres-aiops init       # point it at a read-only role\npostgres-aiops doctor\n```\n\nThen, when you are ready to allow writes, point `init` (or `secret set`) at a\nrole with write privileges, and optionally set an approver so the audit trail\ncarries an accountable name:\n\n```bash\nexport POSTGRES_AUDIT_APPROVED_BY=\"your.name@example.com\"\nexport POSTGRES_AUDIT_RATIONALE=\"scheduled maintenance window 2026-07-20\"\n```\n\n## If your model still struggles\n\nSome behaviours are model-capacity limits rather than prompt problems:\n\n- **Multi-tool workflows time out or drift.** Prefer the flagship analyses —\n  `slow_query_rca`, `bloat_and_vacuum_analysis`, `blocking_lock_chain_rca` — and\n  `overview`. They do the multi-step correlation inside one call, so the model\n  does not have to chain reads and keep pids and queryids straight.\n- **The model ignores later tool results in a long context.** Ask narrower\n  questions and use `--limit` deliberately rather than pulling whole catalogs.\n  `show_settings` in particular returns hundreds of rows without a pattern —\n  always pass one.\n- **The model describes calls instead of making them.** This is usually a\n  runtime/tool-calling-format mismatch, not a prompt problem — check that your\n  client advertises the tools in the format your model was trained on.\n\n## A note on verification\n\nUnlike a purely mocked integration, postgres-aiops has been exercised against a\nreal PostgreSQL 16 server: the bloat/vacuum RCA correctly identified a table with\n~50% dead tuples, and the `create_index` / `drop_index` governance path was\nconfirmed end-to-end (audit row written, undo token capturing the prior\n`pg_get_indexdef` output). Treat the read paths as verified and the more exotic\nwrite paths as preview.\n\nFeedback on running this with a specific local model is genuinely useful —\nopen an issue at\n[github.com/AIops-tools/Postgres-AIops](https://github.com/AIops-tools/Postgres-AIops/issues)\nwith the model, runtime, and what went wrong.\n\nFile v0.10.1:references/capabilities.md\n\n# postgres-aiops capabilities\n\n> 35 MCP tools (25 read, 10 write). Catalog / `pg_stat_*` queries have been\n> exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n> `top_queries` / `slow_query_rca` require the `pg_stat_statements` extension;\n> the read role should have `pg_monitor`.\n\n## Read tools (25)\n\n| Tool | Source | Returns |\n|------|--------|---------|\n| `overview` | several reads (resilient) | version, uptime, connections by state, idleInTransaction, longestQuery, worstBloatTable, replicas |\n| `server_version` | `version()`, `pg_postmaster_start_time()` | version, serverVersion, uptime, inRecovery, dataDirectory |\n| `show_settings` | `pg_settings` | name, setting, unit, category, context, source, pendingRestart |\n| `list_extensions` | `pg_extension` + available | name, installedVersion, defaultVersion, updateAvailable |\n| `list_databases` | `pg_database` | name, owner, encoding, sizeBytes, sizePretty |\n| `list_roles` | `pg_roles` | name, superuser, canLogin, replication, connLimit |\n| `list_activity` | `pg_stat_activity` | total, byState, idleInTransaction[], sessions[] |\n| `long_running_queries` | `pg_stat_activity` | thresholdSeconds, count, queries[] (oldest first) |\n| `list_locks` | `pg_locks`⋈`pg_stat_activity` | total, waitingCount, waiting[], locks[] |\n| `top_queries` | `pg_stat_statements` | orderBy, statements[] (calls, total/mean ms, cacheHitRatioPct) |\n| `explain_query` | `EXPLAIN (FORMAT JSON)` | analyze, plan (JSON) |\n| `unused_indexes` | `pg_stat_user_indexes` | count, reclaimableBytes, indexes[] (idx_scan=0) |\n| `missing_index_hints` | `pg_stat_user_tables` | tables[] with high seq_scan vs idx_scan |\n| `index_bloat` | `pg_class`/`pg_index` | indexes[] with estBloatBytes/estBloatPct (coarse) |\n| `invalid_indexes` | `pg_index` | invalid[], duplicates[] |\n| `table_sizes` | `pg_class` | tables[] total/table/index/toast bytes |\n| `table_bloat` | `pg_stat_user_tables` | tables[] deadPct (dead/(live+dead)) |\n| `autovacuum_status` | `pg_stat_user_tables` | dead tuples, modSinceAnalyze, last (auto)vacuum/analyze |\n| `replication_status` | `pg_stat_replication` | replicas[] with replayLagBytes |\n| `replication_slots` | `pg_replication_slots` | slots[], inactive[] (retain WAL) |\n| `wal_status` | WAL fns + `pg_stat_archiver` | currentLsn, walLevel, max/minWalSize, archiver |\n| `slow_query_rca` | pg_stat_statements + EXPLAIN | worst{}, findings[] (cited cause/action) |\n| `bloat_and_vacuum_analysis` | table-bloat rows | recommendations[] (cited reasons + action) |\n| `blocking_lock_chain_rca` | `pg_blocking_pids` pairs | roots[], worstRootPid, deadlockSuspected |\n| `undo_list` | local undo store | recorded, not-yet-applied reversible writes: undoId, ts, originalTool, inverseTool, note |\n\nThe flagship analyses accept injected records (`statements=` / `tables=` /\n`pairs=`) for pure/offline analysis, or pull live from a configured `target`.\n\n## Write tools (10)\n\n| Tool | Risk | SQL | Undo / safety |\n|------|------|-----|---------------|\n| `terminate_backend` | **high** | `pg_terminate_backend(pid)` | captures pid+query for audit; no safe inverse; dry-run + double-confirm |\n| `cancel_query` | **high** | `pg_cancel_backend(pid)` | captures pid+query; no inverse; dry-run + double-confirm |\n| `drop_index` | **high** | `DROP INDEX` | captures `pg_get_indexdef` FIRST; undo = recreate exactly; dry-run + double-confirm |\n| `run_vacuum` | medium | `VACUUM [FULL] [ANALYZE]` | records prior dead-tuple/last-vac stats; no undo |\n| `run_analyze` | medium | `ANALYZE` | records prior stats; no undo |\n| `create_index` | medium | `CREATE [UNIQUE] INDEX [CONCURRENTLY]` | returns created name; undo = drop it |\n| `reindex` | medium | `REINDEX INDEX/TABLE/SCHEMA` | rebuild in place; no undo |\n| `update_setting` | medium | `ALTER SYSTEM SET` | captures prior value; undo = set back; reports pg_reload_conf needed |\n| `reset_query_stats` | medium | `pg_stat_statements_reset()` | irreversible; no undo |\n| `undo_apply` | medium | dispatches the recorded inverse tool | executes a recorded inverse; the inverse runs through its own governed tool (its real risk tier + audit row are recorded there); single-use token; supports `dry_run` |\n\nAll values are bound query parameters; identifiers that cannot be parameterised\n(table/index/GUC names, ORDER BY columns, index methods, REINDEX kinds) are\nvalidated against strict allow-lists and quoted before interpolation.\n\n## Out of scope (by design)\n\n- Application-schema **migrations** / DDL beyond index maintenance\n- ORM / model management\n- Logical or physical **backup/restore** orchestration (pg_dump, PITR)\n- Role/grant management and `CREATE`/`DROP DATABASE`\n- OT / industrial equipment (use the `industrial-aiops` line)\n\nWant one of these? Open an issue or PR — feedback and contributions welcome.\n\nFile v0.10.1:references/cli-reference.md\n\n# postgres-aiops CLI reference\n\n> Catalog / `pg_stat_*` queries have been exercised against a live PostgreSQL 16.14 instance\n> (see docs/VERIFICATION.md).\n\n## Setup & diagnostics\n\n```bash\npostgres-aiops init                      # interactive onboarding wizard\npostgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())\npostgres-aiops overview [--target <t>]   # one-shot cluster health snapshot\npostgres-aiops mcp                       # start the MCP server (stdio transport)\n```\n\n## Secrets (encrypted store ~/.postgres-aiops/secrets.enc)\n\n```bash\npostgres-aiops secret set <target> [--value <pw>]    # store password (hidden prompt if no --value)\npostgres-aiops secret list                            # names only — values never shown\npostgres-aiops secret rm <target>\npostgres-aiops secret migrate                         # import legacy plaintext .env (PG_<T>_PASSWORD)\npostgres-aiops secret rotate-password                 # re-encrypt under a new master password\n```\n\n## Read commands\n\n```bash\npostgres-aiops server version                 # version, uptime, recovery state\npostgres-aiops server settings [pattern]      # pg_settings (optional name filter)\npostgres-aiops server databases               # databases + sizes\npostgres-aiops server roles\npostgres-aiops server extensions\n\npostgres-aiops activity list [--state active] # pg_stat_activity + per-state counts\npostgres-aiops activity long [--min-seconds 60]\npostgres-aiops activity locks\n\npostgres-aiops query top [--order-by total_time] [--limit 20]   # pg_stat_statements\npostgres-aiops query explain \"<sql>\" [--analyze]\n\npostgres-aiops index unused                   # zero-scan indexes\npostgres-aiops index missing                  # missing-index hints\npostgres-aiops index bloat [--limit 50]\npostgres-aiops index invalid                  # invalid + duplicate\n\npostgres-aiops table sizes [--limit 20]\npostgres-aiops table bloat [--limit 50]       # dead-tuple bloat proxy\npostgres-aiops table autovacuum [--limit 50]\n\npostgres-aiops repl status                    # standby lag\npostgres-aiops repl slots\npostgres-aiops repl wal\n\npostgres-aiops analyze slow-query [--explain \"<sql>\"] [--limit 20]   # flagship RCA\npostgres-aiops analyze bloat-vacuum [--limit 50]\npostgres-aiops analyze blocking\n```\n\n## Write commands (governed; risk tier in parentheses)\n\n```bash\npostgres-aiops remediate terminate <pid> [--dry-run]                     # (high) no undo; double confirm\npostgres-aiops remediate cancel <pid> [--dry-run]                        # (high) no undo; double confirm\npostgres-aiops remediate drop-index <name> [--concurrently] [--dry-run]  # (high) reversible; double confirm\npostgres-aiops remediate vacuum <table> [--full] [--analyze] [--dry-run] # (medium)\npostgres-aiops remediate analyze-table <table> [--dry-run]               # (medium)\npostgres-aiops remediate create-index <table> <cols...> [--name N] [--unique] [--concurrently] [--dry-run]  # (medium) reversible\npostgres-aiops remediate reindex <name> [--kind INDEX|TABLE|SCHEMA] [--concurrently] [--dry-run]            # (medium)\npostgres-aiops remediate set <name> <value> [--dry-run]                  # (medium) ALTER SYSTEM; reversible\n```\n\n## Common options\n\n- `--target, -t <name>` — target name from `config.yaml` (omit to use the default/first target)\n- `--dry-run` — print the statement that would run, change nothing\n- State-changing commands require two confirmations at the CLI layer\n\n## Truncation\n\nEvery command that takes `--limit` returns an envelope — `{\"...\": [...],\n\"returned\": N, \"limit\": L, \"truncated\": bool}` — and fetches one row past the\nlimit so `truncated` is measured, not inferred from the row count. When a read\nis cut short the JSON on stdout stays clean and a notice is written to stderr:\n\n```\n… truncated at 50 rows (50 returned) — re-run with a higher --limit to see the rest.\n```\n\n`analyze slow-query` / `analyze bloat-vacuum` pull a limited read themselves, so\nthey also report `sourceTruncated` / `sourceLimit` when their input was partial.\n\n## What decides whether a write runs\n\nThe tool does not decide whether a write is permitted — that is the agent's\njudgement, or the permission of the PostgreSQL role you connect it with:\nconnect with a role that has no write privileges (a read-only role, or one\nwithout INSERT/UPDATE/DELETE/DDL) and the write fails at the server. Every\ncall, over MCP and over the CLI alike, is still audited. See\n[agent-guardrails.md](agent-guardrails.md).\n\nFile v0.10.1:references/setup-guide.md\n\n# postgres-aiops setup & security guide\n\n> Reads, a governed write, and its undo have been exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n\n## 1. Install\n\n```bash\nuv tool install postgres-aiops\n```\n\n## 2. Prepare a role\n\npostgres-aiops connects with psycopg 3 and reads the system catalogs and\n`pg_stat_*` views. A least-privilege monitoring role works for the reads:\n\n```sql\nCREATE ROLE aiops LOGIN PASSWORD 'change-me';\nGRANT pg_monitor TO aiops;                 -- read visibility into pg_stat_*\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- for top_queries / slow_query_rca\n```\n\nMaintenance writes (VACUUM, CREATE/DROP INDEX, REINDEX) require ownership of the\ntarget objects; `ALTER SYSTEM` requires a superuser or `pg_read_all_settings` +\nthe appropriate privilege.\n\n## 3. Onboard\n\n```bash\npostgres-aiops init\n```\n\nThe wizard collects (non-secret) connection details into\n`~/.postgres-aiops/config.yaml` and stores the password **encrypted** into\n`~/.postgres-aiops/secrets.enc`. Example config:\n\n```yaml\ntargets:\n  - name: primary\n    host: 10.0.0.30\n    port: 5432\n    dbname: appdb\n    user: aiops\n    sslmode: require          # disable/allow/prefer/require/verify-ca/verify-full\n```\n\n## 4. Non-interactive use (MCP server / CI / cron)\n\nExport the master password so the encrypted store can be unlocked without a\nprompt:\n\n```bash\nexport POSTGRES_AIOPS_MASTER_PASSWORD='your-master-password'\n```\n\n## Credential security\n\n- The password is **never** written to disk in plaintext. It lives only in\n  `~/.postgres-aiops/secrets.enc`, encrypted with Fernet (AES-128-CBC + HMAC),\n  the key derived from your master password via scrypt. Only a per-store random\n  salt and the ciphertext are on disk (chmod 600); the master password itself is\n  never stored.\n- A legacy plaintext env var `PG_<TARGET_NAME_UPPER>_PASSWORD` is still honoured\n  as a fallback with a deprecation warning — migrate with `postgres-aiops secret\n  migrate` (it imports then renames the old `.env`).\n- The password is passed to `psycopg.connect` at connect time and held only in\n  memory; it is never logged or echoed. Exception text and tracebacks are\n  scrubbed of secret-shaped strings before being written to the audit log.\n\n## SQL safety\n\n- All values (pids, thresholds, limits, setting values) are **bound query\n  parameters** — never string-formatted into SQL.\n- The few identifiers that cannot be parameterised (table/index/GUC names,\n  `ORDER BY` columns, index methods, `REINDEX` kinds) are validated against\n  strict allow-lists and double-quoted before interpolation; anything that is not\n  a plain identifier is rejected.\n- `EXPLAIN` rejects multi-statement input (an embedded `;` is refused).\n\n## Governance harness state\n\nState lives under `~/.postgres-aiops/` (relocate with `POSTGRES_AIOPS_HOME`):\n\n- `audit.db` — every tool call (SQLite), with risk tier and an optional\n  approver/rationale annotation (`POSTGRES_AUDIT_APPROVED_BY` /\n  `POSTGRES_AUDIT_RATIONALE` — never required, never blocking)\n- `undo.db` — inverse descriptors for reversible writes (e.g. `drop_index`)\n- budget / runaway guard — caps cumulative tool calls and wall-time; trips on\n  tight poll/retry loops\n\n## Verify\n\n```bash\npostgres-aiops doctor\n```\n\n`doctor` checks the config file, the encrypted store and its permissions, that a\npassword is present per target, and (unless `--skip-auth`) connectivity by\nrunning `SELECT version()`.\n\nFile v0.10.1:skill-card.md\n\n## Description:\n\nPostgres AIops helps agents operate and troubleshoot PostgreSQL clusters with health checks, pg_stat diagnostics, root-cause analysis workflows, and governed maintenance actions.\n\nThis skill is ready for commercial/non-commercial use.\n\n## Publisher:\n\n[zw008](https://clawhub.ai/user/zw008)\n\n### License/Terms of Use:\n\nMIT-0\n\n## Use Case:\n\nDevelopers, SREs, and database administrators use this skill to inspect PostgreSQL health, diagnose slow queries, bloat, replication, and lock issues, and prepare or execute audited maintenance actions.\n\n### Deployment Geography for Use:\n\nGlobal\n\n## Known Risks and Mitigations:\n\nRisk: The skill exposes database-changing PostgreSQL operations through an agent without a built-in approval gate.\n\nMitigation: Start with a read-only PostgreSQL role, use dry-run previews where available, and enable maintenance credentials only during deliberate maintenance windows.\n\nRisk: Credentials and operational authority may be exposed if passwords are passed through unsafe channels.\n\nMitigation: Use the encrypted postgres-aiops secret store and avoid passing passwords through command-line arguments or long-lived environment variables.\n\nRisk: High-impact actions such as terminating sessions, dropping indexes, vacuuming, reindexing, or changing server settings can disrupt production workloads.\n\nMitigation: Review the audit trail, prefer reversible operations when possible, and run high-impact changes only with an appropriately privileged role and an explicit maintenance rationale.\n\n## Reference(s):\n\n- [ClawHub skill page](https://clawhub.ai/zw008/skills/postgres-aiops)\n- [Project homepage](https://github.com/AIops-tools/Postgres-AIops)\n- [Capabilities reference](references/capabilities.md)\n- [CLI reference](references/cli-reference.md)\n- [Setup and security guide](references/setup-guide.md)\n- [Agent guardrails](references/agent-guardrails.md)\n\n## Skill Output:\n\n**Output Type(s):** [Text, Markdown, Shell commands, Configuration, Guidance]\n\n**Output Format:** [Markdown or plain text with inline shell commands and structured tool results]\n\n**Output Parameters:** [1D]\n\n**Other Properties Related to Output:** [May include PostgreSQL diagnostic findings, dry-run previews, audit-oriented maintenance recommendations, and JSON-shaped MCP tool outputs.]\n\n## Skill Version(s):\n\n0.10.1 (source: server release metadata)\n\n## Ethical Considerations:\n\nUsers should evaluate whether this skill is appropriate for their environment, review any generated or modified files before relying on them, and apply their organization's safety, security, and compliance requirements before deployment.\n\nArchive v0.10.0: 7 files, 17947 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2452b), SKILL.md (15595b), _meta.json (134b)\n\nFile v0.10.0:SKILL.md\n\n---\nname: postgres-aiops\nslug: postgres-aiops\ndisplayName: \"Postgres AIops\"\nsummary: \"Governed PostgreSQL DBA ops: slow-query RCA, bloat/vacuum & blocking-lock analysis; 35 MCP tools.\"\nlicense: MIT\nhomepage: https://github.com/AIops-tools/Postgres-AIops\ntags: [aiops, mcp, governance, postgres]\ndescription: >\n  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).\n  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.\n  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).\n  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).\ninstaller:\n  kind: uv\n  package: postgres-aiops\nargument-hint: \"[pid / table / index name or describe your DBA task]\"\nallowed-tools:\n  - Bash\nmetadata: {\"openclaw\":{\"requires\":{\"anyBins\":[\"postgres-aiops\",\"uvx\"]},\"optional\":{\"env\":[\"POSTGRES_AIOPS_CONFIG\",\"POSTGRES_AIOPS_MASTER_PASSWORD\"]},\"homepage\":\"https://github.com/AIops-tools/Postgres-AIops\",\"emoji\":\"🐘\",\"os\":[\"macos\",\"linux\"]}}\ncompatibility: >\n  Standalone, self-governed PostgreSQL DBA operations. The governance harness (audit, token/runaway budget, undo, risk-tiers) is bundled in the package — no external skill-family dependency. Connects via psycopg 3 and reads the system catalogs and pg_stat_* views.\n  All write operations are audited to a local SQLite DB under ~/.postgres-aiops/ (relocatable via POSTGRES_AIOPS_HOME).\n  Credentials: the PostgreSQL role password is stored ENCRYPTED in ~/.postgres-aiops/secrets.enc (Fernet/AES-128 + scrypt-derived key) — never plaintext on disk. Run 'postgres-aiops init' to onboard, or 'postgres-aiops secret set <target>' to add one. The store is unlocked by a master password from POSTGRES_AIOPS_MASTER_PASSWORD (non-interactive/MCP/CI) or an interactive prompt (CLI on a TTY). A legacy plaintext env var PG_<TARGET_NAME_UPPER>_PASSWORD is still honoured as a fallback with a deprecation warning (migrate with 'postgres-aiops secret migrate'). The password is passed to psycopg.connect at connect time and held only in memory; it is never logged or echoed.\n  SQL safety: all values are bound query parameters; the few identifiers that cannot be parameterised (table/index/GUC names, ORDER BY columns, index methods) are validated against strict allow-lists and quoted before interpolation. EXPLAIN rejects multi-statement input.\n  State-changing operations require double confirmation at the CLI layer and support --dry-run. All write tools pass through the @governed_tool decorator (budget guard + audit + risk-tier tagging) and take a dry_run preview. Reversible writes fetch the real before-state first and record a faithful inverse (create_index↔drop_index, where drop captures pg_get_indexdef; update_setting restores the prior value); irreversible ops (terminate/cancel, vacuum/analyze, reindex, reset stats) record prior stats only.\n  Webhooks: none — no outbound network calls beyond the configured PostgreSQL connection.\n  SSL: sslmode follows libpq (default prefer); set require/verify-full on untrusted networks.\n  Transitive dependencies: psycopg[binary] (PostgreSQL driver) and the MCP SDK. No post-install scripts or background services.\n  Verification status: the catalog / pg_stat_* reads, the bloat/vacuum RCA, and the create_index/drop_index governed write path (audit + undo) have been exercised against a live PostgreSQL 16.14 instance; docs/VERIFICATION.md records what was and was not covered. Community-maintained; not affiliated with the PostgreSQL project — trademarks belong to their owners.\n---\n\n# Postgres AIops\n\n> **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.\n\nGoverned 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.\n\n> **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`).\n\n## What This Skill Does\n\n| Domain | Tools | Count | Read or Write |\n|--------|-------|:-----:|:-------------:|\n| **Overview** | cluster health snapshot | 1 | 1 read |\n| **Server** | version, settings, extensions, databases, roles | 5 | 5 read |\n| **Activity** | sessions, long-running queries, locks | 3 | 3 read |\n| **Queries** | top-N (pg_stat_statements), EXPLAIN | 2 | 2 read |\n| **Indexes** | unused, missing hints, bloat, invalid/duplicate | 4 | 4 read |\n| **Tables** | sizes, dead-tuple bloat, autovacuum status | 3 | 3 read |\n| **Replication** | status/lag, slots, WAL | 3 | 3 read |\n| **Analysis (flagship)** | slow-query RCA, bloat/vacuum, blocking chains | 3 | 3 read |\n| **Writes** | terminate, cancel, drop-index | 3 | 3 write (high) |\n| | vacuum, analyze, create-index, reindex, ALTER SYSTEM, reset-stats | 6 | 6 write (medium) |\n\nThe 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`.\n\n## Quick Install\n\n```bash\nuv tool install postgres-aiops\npostgres-aiops init       # interactive wizard: connection + encrypted password\npostgres-aiops doctor\n```\n\n## When to Use This Skill\n\n- Triage a cluster (`overview`): version/uptime, connections by state, idle-in-transaction, longest query, worst bloat, replica lag\n- Root-cause a slow query (`analyze slow-query` / `slow_query_rca`): the worst `pg_stat_statements` entry + EXPLAIN → cited cause and action\n- Decide what to vacuum (`analyze bloat-vacuum` / `bloat_and_vacuum_analysis`): tables ranked by dead-tuple ratio + autovacuum lag\n- Untangle a lock pile-up (`analyze blocking` / `blocking_lock_chain_rca`): the wait-for tree with the root blocker named\n- Find unused / missing / bloated indexes; check autovacuum status and table sizes; inspect replication lag and slots\n- Terminate/cancel a backend, VACUUM/ANALYZE, create/drop an index (reversible), REINDEX, or ALTER SYSTEM SET — all with dry-run + double-confirm\n\n**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.\n\n## Related Skills — Skill Routing\n\n| If the user wants… | Use |\n|--------------------|-----|\n| PostgreSQL DBA-ops: slow queries, bloat, locks, index/vacuum maintenance | **postgres-aiops** (this skill) |\n| OT / industrial edge (Modbus, OPC-UA, PLC, PROFINET) | the **industrial-aiops** line |\n| Hypervisor VM lifecycle (power, snapshot, migrate) | a hypervisor ops skill |\n| Container/cluster lifecycle | a cluster ops skill |\n\n## Common Workflows\n\n### \"The app got slow this afternoon\" — root-cause and add the missing index\n\n1. `postgres-aiops overview` → one-shot cluster picture: connections, database sizes, obvious saturation\n2. `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\n3. `postgres-aiops query explain \"<sql>\"` → confirm the plan yourself; a Seq Scan on a large table is the index signal\n4. `postgres-aiops index missing` → the tool's own index hints, to cross-check that step 3's conclusion is not a one-off\n5. `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\n6. `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\n7. **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.\n\n### Reclaim table bloat and retire a redundant index (reversible)\n\n1. `postgres-aiops analyze bloat-vacuum` → tables ranked by dead-tuple ratio and autovacuum lag, each citing the measured numbers\n2. `postgres-aiops table autovacuum` → check whether autovacuum is simply behind (last run, thresholds) before doing it by hand\n3. `postgres-aiops remediate vacuum <table> --analyze --dry-run` → preview; then run for real to `VACUUM ANALYZE` (double confirmation)\n4. `postgres-aiops index unused` and `postgres-aiops index bloat` → find indexes that cost writes and return nothing\n5. `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\n6. **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.\n\n### Break a blocking pile-up during an incident\n\n1. `postgres-aiops analyze blocking` → the wait-for chain, naming the **root blocker** pid rather than the visible victims\n2. `postgres-aiops activity locks` → the raw lock rows behind the chain; confirm the blocker is what the RCA says it is\n3. `postgres-aiops activity long --min-seconds 60` → how long the blocker has actually been running, and whether it is idle-in-transaction\n4. `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\n5. Only if cancel does not clear it: `postgres-aiops remediate terminate <pid>` (double confirmation)\n6. **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.\n\n### Tune a parameter and prove it moved the needle (reversible)\n\n1. `postgres-aiops server settings work_mem` → the current value and where it came from\n2. `postgres-aiops analyze slow-query` → confirm a temp-spill finding is what actually motivates the change\n3. `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\n4. Reload/restart per the parameter's context, then `postgres-aiops server settings work_mem` to confirm the value took effect\n5. **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.\n\n### Offline analysis (no live cluster)\n\n1. Export `pg_stat_statements`, table-bloat, and blocking-pair rows to JSON\n2. 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\n3. **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.\n\n## Governance & Safety\n\nThe skill delivers reads and writes and records them; it does **not** decide whether a write is\npermitted. That is your agent's judgement, or the permission of the account you connect it with\n(connect with a PostgreSQL role that has no write privileges (a read-only role, or one without\nINSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy\nfile, or approval gate.\n\n- **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.\n- `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations recorded on the audit row (who/why); they are never required and never block.\n- **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`.\n- Writes support `--dry-run` / `dry_run=True` and double confirmation at the CLI.\n- 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.\n- All values are bound query parameters; identifiers that cannot be parameterised are validated and quoted.\n\n## References\n\n- `references/capabilities.md` — full tool + field reference\n- `references/cli-reference.md` — CLI command reference\n- `references/setup-guide.md` — onboarding, credentials, and connectivity\n\nFile v0.10.0:_meta.json\n\n{\n  \"ownerId\": \"kn7b067awq2s97bn3d7p5qfhw5827pxc\",\n  \"slug\": \"postgres-aiops\",\n  \"version\": \"0.10.0\",\n  \"publishedAt\": 1789175402831\n}\n\nFile v0.10.0:references/agent-guardrails.md\n\n# Agent guardrails — running postgres-aiops with a smaller / local model\n\nIf you drive these tools with a local model (Llama, Qwen, Mistral … via Goose,\nOllama, LM Studio, or any OpenAI-compatible runtime), you will get noticeably\nbetter results with a short system prompt. This page gives you one, and — more\nimportantly — tells you which guardrails you **no longer need to write**, because\nthe tool now enforces them itself.\n\nThe distinction matters. A guardrail in a prompt is a request. A guardrail in the\nharness is a guarantee. Anything below that we could move into the harness, we did.\n\n## What the tool now enforces — do not waste prompt budget on these\n\n| You might be tempted to prompt | Why you don't need to |\n|---|---|\n| \"Don't invent a value when a field is missing\" | A column the server returned as SQL `NULL` comes back as `null`, never as `\"\"`. `lastAutovacuum: null` means the table was *never* autovacuumed; `unit: null` means the setting is not a numeric quantity; `plugin: null` means the replication slot is physical, not logical. Absent and empty are distinguishable in the payload. |\n| \"Tell me if the output was cut off\" | Anything with a `limit` returns `{\"statements\": [...], \"returned\": N, \"limit\": L, \"truncated\": true/false}`. Truncation is **measured** — one extra row is fetched — not guessed from the row count happening to equal the limit. An analysis that pulled a truncated read also carries `sourceTruncated`. |\n| \"Preserve the ordering / tell me what's most urgent\" | Ranked reads are already ordered worst-first (`table_bloat` by dead tuples, `index_bloat` by estimated bloat, `blocking_lock_chain_rca` by how many backends a root blocker holds up), and every finding cites the number that triggered it. |\n| \"Confirm before anything destructive\" | Destructive operations require a `--dry-run`-able preview + double confirmation at the CLI for high-risk tiers such as `terminate_backend` and `drop_index`. |\n| \"Log what you did\" | Every call is audited to `~/.postgres-aiops/audit.db` regardless of what the model says it did. Reversible writes (`create_index`, `drop_index`, `update_setting`) also record an undo token capturing the pre-change state. |\n\n**Authorization is not this tool's job.** Whether a write is permitted is decided by the account\nyou connect it with (connect with a PostgreSQL role that has no write privileges — a read-only\nrole, or one without INSERT/UPDATE/DELETE/DDL — and the write fails at the server) or by your\nagent's prompt. `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations\nrecorded on the audit row; they are never required and never block a call.\n\n## What still needs a prompt\n\nThese are model-behaviour problems the harness cannot fix from the outside.\nCopy this into your agent's system prompt:\n\n```text\nYou operate a PostgreSQL server through the postgres-aiops MCP tools.\n\nTOOL USE\n- Before answering any question about the current database, you MUST call a\n  tool. Never answer from memory or assumption.\n- Actually invoke the tool. Do not describe the call you would make, and do not\n  emit an example JSON response in place of calling it.\n- If a tool call fails, report the real error verbatim. Never fill the gap with\n  a plausible-sounding answer.\n\nREADING RESULTS\n- Read the whole result before concluding. If a result has \"truncated\": true\n  (or \"sourceTruncated\": true), say so and re-run with a higher limit instead of\n  treating the partial result as complete.\n- A null field means the server returned SQL NULL for that column. Report it as\n  \"not available\" or, where it is meaningful, as what the NULL means — a null\n  lastAutovacuum means the table has never been autovacuumed. Never infer it.\n- Report values exactly as returned. Do not normalise, translate, or prettify\n  states, wait events, LSNs, or identifiers.\n- Cite the measured number from each finding's \"detail\" when explaining a cause.\n\nSCOPE\n- Separate observation from interpretation. State what the tools returned, then\n  any interpretation, clearly marked as such.\n- Do not assert a performance, bloat, or replication problem unless a tool\n  result supports it.\n- Do not add generic PostgreSQL tuning advice that does not follow from the\n  tool output.\n- Keep the identifiers straight. A database, a schema, a relation (table), an\n  index, a backend pid, and a queryid are all different things: a pid is an OS\n  process id from pg_stat_activity, a queryid is a pg_stat_statements\n  fingerprint, and a relation is named schema.table. Never pass one where\n  another is expected, and never invent a schema qualification.\n- pg_stat_statements counters are cumulative since the last stats reset, and\n  idx_scan is cumulative too. Do not describe them as \"recent\" activity.\n```\n\n## Recommended setup for a local model\n\nUntil you trust the setup, connect the tool with a read-only PostgreSQL role\n(no INSERT/UPDATE/DELETE/DDL) — that is real enforcement at the server, not a\nprompt asking the model to behave:\n\n```bash\npostgres-aiops init       # point it at a read-only role\npostgres-aiops doctor\n```\n\nThen, when you are ready to allow writes, point `init` (or `secret set`) at a\nrole with write privileges, and optionally set an approver so the audit trail\ncarries an accountable name:\n\n```bash\nexport POSTGRES_AUDIT_APPROVED_BY=\"your.name@example.com\"\nexport POSTGRES_AUDIT_RATIONALE=\"scheduled maintenance window 2026-07-20\"\n```\n\n## If your model still struggles\n\nSome behaviours are model-capacity limits rather than prompt problems:\n\n- **Multi-tool workflows time out or drift.** Prefer the flagship analyses —\n  `slow_query_rca`, `bloat_and_vacuum_analysis`, `blocking_lock_chain_rca` — and\n  `overview`. They do the multi-step correlation inside one call, so the model\n  does not have to chain reads and keep pids and queryids straight.\n- **The model ignores later tool results in a long context.** Ask narrower\n  questions and use `--limit` deliberately rather than pulling whole catalogs.\n  `show_settings` in particular returns hundreds of rows without a pattern —\n  always pass one.\n- **The model describes calls instead of making them.** This is usually a\n  runtime/tool-calling-format mismatch, not a prompt problem — check that your\n  client advertises the tools in the format your model was trained on.\n\n## A note on verification\n\nUnlike a purely mocked integration, postgres-aiops has been exercised against a\nreal PostgreSQL 16 server: the bloat/vacuum RCA correctly identified a table with\n~50% dead tuples, and the `create_index` / `drop_index` governance path was\nconfirmed end-to-end (audit row written, undo token capturing the prior\n`pg_get_indexdef` output). Treat the read paths as verified and the more exotic\nwrite paths as preview.\n\nFeedback on running this with a specific local model is genuinely useful —\nopen an issue at\n[github.com/AIops-tools/Postgres-AIops](https://github.com/AIops-tools/Postgres-AIops/issues)\nwith the model, runtime, and what went wrong.\n\nFile v0.10.0:references/capabilities.md\n\n# postgres-aiops capabilities\n\n> 35 MCP tools (25 read, 10 write). Catalog / `pg_stat_*` queries have been\n> exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n> `top_queries` / `slow_query_rca` require the `pg_stat_statements` extension;\n> the read role should have `pg_monitor`.\n\n## Read tools (25)\n\n| Tool | Source | Returns |\n|------|--------|---------|\n| `overview` | several reads (resilient) | version, uptime, connections by state, idleInTransaction, longestQuery, worstBloatTable, replicas |\n| `server_version` | `version()`, `pg_postmaster_start_time()` | version, serverVersion, uptime, inRecovery, dataDirectory |\n| `show_settings` | `pg_settings` | name, setting, unit, category, context, source, pendingRestart |\n| `list_extensions` | `pg_extension` + available | name, installedVersion, defaultVersion, updateAvailable |\n| `list_databases` | `pg_database` | name, owner, encoding, sizeBytes, sizePretty |\n| `list_roles` | `pg_roles` | name, superuser, canLogin, replication, connLimit |\n| `list_activity` | `pg_stat_activity` | total, byState, idleInTransaction[], sessions[] |\n| `long_running_queries` | `pg_stat_activity` | thresholdSeconds, count, queries[] (oldest first) |\n| `list_locks` | `pg_locks`⋈`pg_stat_activity` | total, waitingCount, waiting[], locks[] |\n| `top_queries` | `pg_stat_statements` | orderBy, statements[] (calls, total/mean ms, cacheHitRatioPct) |\n| `explain_query` | `EXPLAIN (FORMAT JSON)` | analyze, plan (JSON) |\n| `unused_indexes` | `pg_stat_user_indexes` | count, reclaimableBytes, indexes[] (idx_scan=0) |\n| `missing_index_hints` | `pg_stat_user_tables` | tables[] with high seq_scan vs idx_scan |\n| `index_bloat` | `pg_class`/`pg_index` | indexes[] with estBloatBytes/estBloatPct (coarse) |\n| `invalid_indexes` | `pg_index` | invalid[], duplicates[] |\n| `table_sizes` | `pg_class` | tables[] total/table/index/toast bytes |\n| `table_bloat` | `pg_stat_user_tables` | tables[] deadPct (dead/(live+dead)) |\n| `autovacuum_status` | `pg_stat_user_tables` | dead tuples, modSinceAnalyze, last (auto)vacuum/analyze |\n| `replication_status` | `pg_stat_replication` | replicas[] with replayLagBytes |\n| `replication_slots` | `pg_replication_slots` | slots[], inactive[] (retain WAL) |\n| `wal_status` | WAL fns + `pg_stat_archiver` | currentLsn, walLevel, max/minWalSize, archiver |\n| `slow_query_rca` | pg_stat_statements + EXPLAIN | worst{}, findings[] (cited cause/action) |\n| `blo\n\nArchive v0.9.0: 7 files, 18166 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2947b), SKILL.md (15706b), _meta.json (133b)\n\nArchive v0.8.0: 7 files, 18118 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2881b), SKILL.md (15706b), _meta.json (133b)\n\nArchive v0.7.0: 7 files, 18125 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2960b), SKILL.md (15706b), _meta.json (133b)\n\nArchive v0.6.0: 7 files, 18011 bytes\n\nFiles: references/agent-guardrails.md (7010b), references/capabilities.md (4829b), references/cli-reference.md (4520b), references/setup-guide.md (3452b), skill-card.md (2724b), SKILL.md (15706b), _meta.json (133b)\n\nArchive v0.5.0: 7 files, 17663 bytes\n\nFiles: references/agent-guardrails.md (6708b), references/capabilities.md (4820b), references/cli-reference.md (4307b), references/setup-guide.md (3403b), skill-card.md (2946b), SKILL.md (15284b), _meta.json (133b)\n\nArchive v0.4.0: 7 files, 17643 bytes\n\nFiles: references/agent-guardrails.md (6708b), references/capabilities.md (4820b), references/cli-reference.md (4307b), references/setup-guide.md (3403b), skill-card.md (2894b), SKILL.md (15284b), _meta.json (133b)","readmeExcerpt":"Skill: postgres-aiops Owner: zw008 Summary: 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, mi","codeSnippets":[],"executableExamples":[{"language":"bash","snippet":"uv tool install postgres-aiops\npostgres-aiops init       # interactive wizard: connection + encrypted password\npostgres-aiops doctor"},{"language":"bash","snippet":"openclaw plugins install clawhub:@zw008/postgres-aiops\nopenclaw skills info postgres-aiops          # expect: Visible to model: yes"},{"language":"text","snippet":"You operate a PostgreSQL server through the postgres-aiops MCP tools.\n\nTOOL USE\n- Before answering any question about the current database, you MUST call a\n  tool. Never answer from memory or assumption.\n- Actually invoke the tool. Do not describe the call you would make, and do not\n  emit an example JSON response in place of calling it.\n- If a tool call fails, report the real error verbatim. Never fill the gap with\n  a plausible-sounding answer.\n\nREADING RESULTS\n- Read the whole result before concluding. If a result has \"truncated\": true\n  (or \"sourceTruncated\": true), say so and re-run with a higher limit instead of\n  treating the partial result as complete.\n- A null field means the server returned SQL NULL for that column. Report it as\n  \"not available\" or, where it is meaningful, as what the NULL means — a null\n  lastAutovacuum means the table has never been autovacuumed. Never infer it.\n- Report values exactly as returned. Do not normalise, translate, or prettify\n  states, wait events, LSNs, or identifiers.\n- Cite the measured number from each finding's \"detail\" when explaining a cause.\n\nSCOPE\n- Separate observation from interpretation. State what the tools returned, then\n  any interpretation, clearly marked as such.\n- Do not assert a performance, bloat, or replication problem unless a tool\n  result supports it.\n- Do not add generic PostgreSQL tuning advice that does not follow from the\n  tool output.\n- Keep the identifiers straight. A database, a schema, a relation (table), an\n  index, a backend pid, and a queryid are all different things: a pid is an OS\n  process id from pg_stat_activity, a queryid is a pg_stat_statements\n  fingerprint, and a relation is named schema.table. Never pass one where\n  another is expected, and never invent a schema qualification.\n- pg_stat_statements counters are cumulative since the last stats reset, and\n  idx_scan is cumulative too. Do not describe them as \"recent\" activity."},{"language":"bash","snippet":"postgres-aiops init       # point it at a read-only role\npostgres-aiops doctor"},{"language":"bash","snippet":"export POSTGRES_AUDIT_APPROVED_BY=\"your.name@example.com\"\nexport POSTGRES_AUDIT_RATIONALE=\"scheduled maintenance window 2026-07-20\""},{"language":"bash","snippet":"postgres-aiops init                      # interactive onboarding wizard\npostgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())\npostgres-aiops overview [--target <t>]   # one-shot cluster health snapshot\npostgres-aiops mcp                       # start the MCP server (stdio transport)"}],"parameters":null,"dependencies":[],"permissions":[],"extractedFiles":[{"path":"SKILL.md","content":"---\nname: postgres-aiops\nslug: postgres-aiops\ndisplayName: \"Postgres AIops\"\nsummary: \"Governed PostgreSQL DBA ops: slow-query RCA, bloat/vacuum & blocking-lock analysis; 35 MCP tools.\"\nlicense: MIT\nhomepage: https://github.com/AIops-tools/Postgres-AIops\ntags: [aiops, mcp, governance, postgres]\ndescription: >\n  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).\n  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.\n  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).\n  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).\ninstaller:\n  kind: uv\n  package: postgres-aiops\nargument-hint: \"[pid / table / index name or describe your DBA task]\"\nallowed-tools:\n  - Bash\nmetadata: {\"openclaw\":{\"requires\":{\"anyBins\":[\"postgres-aiops\",\"uvx\"]},\"optional\":{\"env\":[\"POSTGRES_AIOPS_CONFIG\",\"POSTGRES_AIOPS_MASTER_PASSWORD\"]},\"homepage\":\"https://github.com/AIops-tools/Postgres-AIops\",\"emoji\":\"🐘\",\"os\":[\"macos\",\"linux\"]}}\ncompatibility: >\n  Standalone, self-governed PostgreSQL DBA operations. The governance harness (audit, token/runaway budget, undo, risk-tiers) is bundled in the package — no external skill-family dependency. Connects via psycopg 3 and reads the system catalogs and pg_stat_* views.\n  All write operations are audited to a local SQLite DB under ~/.postgres-aiops/ (relocatable via POSTGRES_AIOPS_HOME)"},{"path":"_meta.json","content":"{\n  \"ownerId\": \"kn7b067awq2s97bn3d7p5qfhw5827pxc\",\n  \"slug\": \"postgres-aiops\",\n  \"version\": \"0.10.3\",\n  \"publishedAt\": 1789452914292\n}"},{"path":"references/agent-guardrails.md","content":"# Agent guardrails — running postgres-aiops with a smaller / local model\n\nIf you drive these tools with a local model (Llama, Qwen, Mistral … via Goose,\nOllama, LM Studio, or any OpenAI-compatible runtime), you will get noticeably\nbetter results with a short system prompt. This page gives you one, and — more\nimportantly — tells you which guardrails you **no longer need to write**, because\nthe tool now enforces them itself.\n\nThe distinction matters. A guardrail in a prompt is a request. A guardrail in the\nharness is a guarantee. Anything below that we could move into the harness, we did.\n\n## What the tool now enforces — do not waste prompt budget on these\n\n| You might be tempted to prompt | Why you don't need to |\n|---|---|\n| \"Don't invent a value when a field is missing\" | A column the server returned as SQL `NULL` comes back as `null`, never as `\"\"`. `lastAutovacuum: null` means the table was *never* autovacuumed; `unit: null` means the setting is not a numeric quantity; `plugin: null` means the replication slot is physical, not logical. Absent and empty are distinguishable in the payload. |\n| \"Tell me if the output was cut off\" | Anything with a `limit` returns `{\"statements\": [...], \"returned\": N, \"limit\": L, \"truncated\": true/false}`. Truncation is **measured** — one extra row is fetched — not guessed from the row count happening to equal the limit. An analysis that pulled a truncated read also carries `sourceTruncated`. |\n| \"Preserve the ordering / tell me what's most urgent\" | Ranked reads are already ordered worst-first (`table_bloat` by dead tuples, `index_bloat` by estimated bloat, `blocking_lock_chain_rca` by how many backends a root blocker holds up), and every finding cites the number that triggered it. |\n| \"Confirm before anything destructive\" | Destructive operations require a `--dry-run`-able preview + double confirmation at the CLI for high-risk tiers such as `terminate_backend` and `drop_index`. |\n| \"Log what you did\" | Every call is audited to `~/.postgres-aiops/audit.db` regardless of what the model says it did. Reversible writes (`create_index`, `drop_index`, `update_setting`) also record an undo token capturing the pre-change state. |\n\n**Authorization is not this tool's job.** Whether a write is permitted is decided by the account\nyou connect it with (connect with a PostgreSQL role that has no write privileges — a read-only\nrole, or one without INSERT/UPDATE/DELETE/DDL — and the write fails at the server) or by your\nagent's prompt. `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations\nrecorded on the audit row; they are never required and never block a call.\n\n## What still needs a prompt\n\nThese are model-behaviour problems the harness cannot fix from the outside.\nCopy this into your agent's system prompt:\n\n```text\nYou operate a PostgreSQL server through the postgres-aiops MCP tools.\n\nTOOL USE\n- Before answering any question about the current database, you MUST call a\n  tool. Never answer from memo"},{"path":"references/capabilities.md","content":"# postgres-aiops capabilities\n\n> 35 MCP tools (25 read, 10 write). Catalog / `pg_stat_*` queries have been\n> exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).\n> `top_queries` / `slow_query_rca` require the `pg_stat_statements` extension;\n> the read role should have `pg_monitor`.\n\n## Read tools (25)\n\n| Tool | Source | Returns |\n|------|--------|---------|\n| `overview` | several reads (resilient) | version, uptime, connections by state, idleInTransaction, longestQuery, worstBloatTable, replicas |\n| `server_version` | `version()`, `pg_postmaster_start_time()` | version, serverVersion, uptime, inRecovery, dataDirectory |\n| `show_settings` | `pg_settings` | name, setting, unit, category, context, source, pendingRestart |\n| `list_extensions` | `pg_extension` + available | name, installedVersion, defaultVersion, updateAvailable |\n| `list_databases` | `pg_database` | name, owner, encoding, sizeBytes, sizePretty |\n| `list_roles` | `pg_roles` | name, superuser, canLogin, replication, connLimit |\n| `list_activity` | `pg_stat_activity` | total, byState, idleInTransaction[], sessions[] |\n| `long_running_queries` | `pg_stat_activity` | thresholdSeconds, count, queries[] (oldest first) |\n| `list_locks` | `pg_locks`⋈`pg_stat_activity` | total, waitingCount, waiting[], locks[] |\n| `top_queries` | `pg_stat_statements` | orderBy, statements[] (calls, total/mean ms, cacheHitRatioPct) |\n| `explain_query` | `EXPLAIN (FORMAT JSON)` | analyze, plan (JSON) |\n| `unused_indexes` | `pg_stat_user_indexes` | count, reclaimableBytes, indexes[] (idx_scan=0) |\n| `missing_index_hints` | `pg_stat_user_tables` | tables[] with high seq_scan vs idx_scan |\n| `index_bloat` | `pg_class`/`pg_index` | indexes[] with estBloatBytes/estBloatPct (coarse) |\n| `invalid_indexes` | `pg_index` | invalid[], duplicates[] |\n| `table_sizes` | `pg_class` | tables[] total/table/index/toast bytes |\n| `table_bloat` | `pg_stat_user_tables` | tables[] deadPct (dead/(live+dead)) |\n| `autovacuum_status` | `pg_stat_user_tables` | dead tuples, modSinceAnalyze, last (auto)vacuum/analyze |\n| `replication_status` | `pg_stat_replication` | replicas[] with replayLagBytes |\n| `replication_slots` | `pg_replication_slots` | slots[], inactive[] (retain WAL) |\n| `wal_status` | WAL fns + `pg_stat_archiver` | currentLsn, walLevel, max/minWalSize, archiver |\n| `slow_query_rca` | pg_stat_statements + EXPLAIN | worst{}, findings[] (cited cause/action) |\n| `bloat_and_vacuum_analysis` | table-bloat rows | recommendations[] (cited reasons + action) |\n| `blocking_lock_chain_rca` | `pg_blocking_pids` pairs | roots[], worstRootPid, deadlockSuspected |\n| `undo_list` | local undo store | recorded, not-yet-applied reversible writes: undoId, ts, originalTool, inverseTool, note |\n\nThe flagship analyses accept injected records (`statements=` / `tables=` /\n`pairs=`) for pure/offline analysis, or pull live from a configured `target`.\n\n## Write tools (10)\n\n| Tool | Risk | SQL | Undo / safety |\n|------|------|---"},{"path":"references/cli-reference.md","content":"# postgres-aiops CLI reference\n\n> Catalog / `pg_stat_*` queries have been exercised against a live PostgreSQL 16.14 instance\n> (see docs/VERIFICATION.md).\n\n## Setup & diagnostics\n\n```bash\npostgres-aiops init                      # interactive onboarding wizard\npostgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())\npostgres-aiops overview [--target <t>]   # one-shot cluster health snapshot\npostgres-aiops mcp                       # start the MCP server (stdio transport)\n```\n\n## Secrets (encrypted store ~/.postgres-aiops/secrets.enc)\n\n```bash\npostgres-aiops secret set <target> [--value <pw>]    # store password (hidden prompt if no --value)\npostgres-aiops secret list                            # names only — values never shown\npostgres-aiops secret rm <target>\npostgres-aiops secret migrate                         # import legacy plaintext .env (PG_<T>_PASSWORD)\npostgres-aiops secret rotate-password                 # re-encrypt under a new master password\n```\n\n## Read commands\n\n```bash\npostgres-aiops server version                 # version, uptime, recovery state\npostgres-aiops server settings [pattern]      # pg_settings (optional name filter)\npostgres-aiops server databases               # databases + sizes\npostgres-aiops server roles\npostgres-aiops server extensions\n\npostgres-aiops activity list [--state active] # pg_stat_activity + per-state counts\npostgres-aiops activity long [--min-seconds 60]\npostgres-aiops activity locks\n\npostgres-aiops query top [--order-by total_time] [--limit 20]   # pg_stat_statements\npostgres-aiops query explain \"<sql>\" [--analyze]\n\npostgres-aiops index unused                   # zero-scan indexes\npostgres-aiops index missing                  # missing-index hints\npostgres-aiops index bloat [--limit 50]\npostgres-aiops index invalid                  # invalid + duplicate\n\npostgres-aiops table sizes [--limit 20]\npostgres-aiops table bloat [--limit 50]       # dead-tuple bloat proxy\npostgres-aiops table autovacuum [--limit 50]\n\npostgres-aiops repl status                    # standby lag\npostgres-aiops repl slots\npostgres-aiops repl wal\n\npostgres-aiops analyze slow-query [--explain \"<sql>\"] [--limit 20]   # flagship RCA\npostgres-aiops analyze bloat-vacuum [--limit 50]\npostgres-aiops analyze blocking\n```\n\n## Write commands (governed; risk tier in parentheses)\n\n```bash\npostgres-aiops remediate terminate <pid> [--dry-run]                     # (high) no undo; double confirm\npostgres-aiops remediate cancel <pid> [--dry-run]                        # (high) no undo; double confirm\npostgres-aiops remediate drop-index <name> [--concurrently] [--dry-run]  # (high) reversible; double confirm\npostgres-aiops remediate vacuum <table> [--full] [--analyze] [--dry-run] # (medium)\npostgres-aiops remediate analyze-table <table> [--dry-run]               # (medium)\npostgres-aiops remediate create-index <table> <cols...> [--name N] [--unique] [--concurrently] [--dry-run]  # (medium) reversible\npostg"}],"languages":[],"docsSourceLabel":"CLAWHUB","editorialOverview":null,"editorialQuality":{"score":100,"threshold":65,"status":"thin","wordCount":2035,"uniquenessScore":39,"reasons":["uniqueness-below-45"]}},"media":{"evidence":{"source":"no-media","verified":false,"confidence":"low","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":"No screenshots, media assets, or demo links are available."},"primaryImageUrl":null,"mediaAssetCount":0,"assets":[],"demoUrl":null},"ownerResources":{"evidence":{"source":"unclaimed","verified":false,"confidence":"low","updatedAt":"2026-10-10T12:45:12.703Z","emptyReason":"This page has not been claimed by the agent owner."},"hasCustomPage":false,"customPageUpdatedAt":null,"customLinks":[],"structuredLinks":{"docsUrl":null,"demoUrl":null,"supportUrl":null,"pricingUrl":null,"statusUrl":null},"customPage":null},"relatedAgents":{"evidence":{"source":"protocol-neighbors","verified":false,"confidence":"medium","updatedAt":"2026-10-10T15:50:57.905Z","emptyReason":null},"items":[{"id":"8ebccd8e-3863-4187-8355-c3f14e1f9edf","entityType":"agent","canonicalPath":"/agent/iofficeai-aionui","slug":"iofficeai-aionui","name":"AionUi","description":"Free, local, open-source 24/7 Cowork app and OpenClaw for Gemini CLI, Claude Code, Codex, OpenCode, Qwen Code, Goose CLI, Auggie, and more | 🌟 Star if you like it!","url":"https://github.com/iOfficeAI/AionUi","homepage":"https://www.aionui.com","source":"GITHUB_REPOS","protocols":["MCP","OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-10-09T19:11:12.944Z","createdAt":"2026-02-25T03:38:16.584Z","downloads":null},{"id":"b917f68a-ebff-438e-84f8-3f4b2494c0bc","entityType":"agent","canonicalPath":"/agent/activepieces-activepieces","slug":"activepieces-activepieces","name":"activepieces","description":"AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents","url":"https://github.com/activepieces/activepieces","homepage":"https://www.activepieces.com","source":"GITHUB_REPOS","protocols":["OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-04-15T02:22:12.426Z","createdAt":"2026-02-25T03:38:12.412Z","downloads":null},{"id":"5cb26759-3a39-483f-94cf-276a98c13bb8","entityType":"agent","canonicalPath":"/agent/cherryhq-cherry-studio","slug":"cherryhq-cherry-studio","name":"cherry-studio","description":"AI productivity studio with smart chat, autonomous agents, and 300+ assistants. Unified access to frontier LLMs","url":"https://github.com/CherryHQ/cherry-studio","homepage":"https://cherry-ai.com","source":"GITHUB_REPOS","protocols":["MCP","OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-04-11T14:38:40.986Z","createdAt":"2026-02-25T03:38:19.379Z","downloads":null},{"id":"6f6582d0-5d76-4f0f-b81d-86520247950b","entityType":"agent","canonicalPath":"/agent/copilotkit-copilotkit","slug":"copilotkit-copilotkit","name":"CopilotKit","description":"The Frontend for Agents & Generative UI. React + Angular","url":"https://github.com/CopilotKit/CopilotKit","homepage":"https://docs.copilotkit.ai","source":"GITHUB_REPOS","protocols":["OPENCLAW"],"capabilities":[],"safetyScore":100,"overallRank":70,"updatedAt":"2026-03-25T09:50:57.846Z","createdAt":"2026-02-25T03:39:14.617Z","downloads":null}],"links":{"hub":"/agent","source":"/agent/source/clawhub","protocols":[{"label":"OpenClaw","href":"/agent/protocol/openclew"}]}}}