The Problem

PostgreSQL users need a quick, read‑only health check that surfaces database‑level insights without writing to the cluster or exposing secrets externally. Existing tools often require privileged access, external services, or complex instrumentation.

What This Does

pgbot is a single binary that connects to a PostgreSQL instance as a read‑only user, reads system views, applies a rule engine, and emits a “findings‑first” health report.

  • The CLI is defined in cmd/pgbot/main.go; sub‑commands live in the cmd/pgbot/ package (inspect, queries, indexes, vacuum, etc.).
  • Core data collection happens in the internal/collect package; functions like collect.CollectStatistics read pg_stat_* tables.
  • Decision logic lives in internal/advisor; the Advisor struct processes collected metrics against a set of patterns (see internal/advisor/schema.go).
  • Optional AI explanations are provided by internal/ai; the Resolve function routes requests to providers (openai.go, anthropic.go, gemini.go, bedrock.go) based on environment variables (OPENAI_API_KEY, GEMINI_API_KEY, etc.).

How It Is Wired

  1. Entry Point – main.go parses flags, determines the command (e.g., inspect), and calls the corresponding function in cmd/pgbot/inspect.go.
  2. Inspection Flow – inspect invokes internal/collect.Gather (collects statistics, indexes, locks, etc.), then passes the payload to advisor.Advise.
  3. Advisor Output – advisor.Advise returns a slice of Finding objects; each finding is rendered via cmd/pgbot/report.go.
  4. AI Layer – If the --ask flag is set, cmd/pgbot/ask.go calls internal/ai.Resolve, which selects a provider based on the available key and forwards the prompt.
  5. Exit – The binary prints JSON (contract 1.5.0, defined in schema/) or a human‑readable dashboard (see docs/img/dashboard.png).

The most influential functions are main.go, inspect.go, collect.Gather, advisor.Advise, and ai.Resolve. Changes to advisor affect all commands; changes to collect affect only data gathering.

How To Use It

Setup

# Build from source
git clone https://github.com/moses-y/pgbot
cd pgbot
make build          # creates ./pgbot

or use the public installer:

curl -fsSL https://pgbot.dev/install | sh

Configuration

Set a read‑only connection string:

export DATABASE_URL="postgres://pgbot_ro@host:5432/db?sslmode=require"

Optional AI keys (only needed for pgbot ask / pgbot explain):

export OPENAI_API_KEY=sk-…

Running

./pgbot inspect

or with AI explanation:

./pgbot ask "Why is my cache hit low?"

Real‑World Use

In CI pipelines, a GitHub Action can run pgbot inspect --json > report.json and use the JSON schema to enforce health thresholds. In production, the binary can be scheduled nightly to surface regressions before they affect users.

Code Health & Issues

  • Tests: 130 test files, covering internal/collect, advisor, and ai modules.
  • CI: GitHub Actions (.github/workflows/ci.yml) run go test ./... and golangci-lint.
  • Static Analysis: golangci.toml enabled govet, staticcheck, and revive; no failures reported.
  • License: Apache‑2.0 (LICENSE).
  • Documentation: 81 Markdown files, including docs/configuration.md and a full JSON schema (schema/).

No structural red flags. The only potential maintenance burden is the AI provider abstraction, which relies on external keys; however, the rest of the tool is pure Go with deterministic behavior.

The Bottom Line

pgbot delivers a lightweight, read‑only health check for PostgreSQL with optional AI explanations. It is straightforward to build and integrate, and its rule engine is fully test‑covered. Teams that need a deterministic, no‑outside‑service diagnostic tool will benefit most; those that require real‑time alerts may need to supplement it with monitoring.