gem-hunter
Language: TypeScript
postgresai – Postgres observability built for humans and AI agents
Monitoring, health checks, and root cause analysis — built for humans and AI agents
Battle-tested in PostgresAI team's work with companies like GitLab, Miro, Chewy, Suno, Supabase, Gadget, and more — now packaged for easy use by humans and AI agents.
Traditional monitoring tools give you dashboards. postgresai gives AI agents the context they need to actually fix problems.
Part of Self-Driving Postgres — PostgresAI's open-source initiative to make Postgres autonomous.
Create a free account at postgres.ai
npx postgresai auth
This opens your browser to log in and saves your API key locally.
PGPASSWORD=secret npx postgresai checkup postgresql://user@host:5432/dbname
Open console.postgres.ai to see:
See demoOffline mode: Add
--no-uploadto run locally without an account or sending report data to the PostgresAI API.
Run specific checks or work offline:
# Run a specific check npx postgresai checkup --check-id H002 postgresql://... # Local JSON output only (no upload) npx postgresai checkup --no-upload --check-id H002 postgresql://...
Checkup reports are JSON. Use --json to print them or --output <dir> to save
them; uploaded reports show up in the Console.
Tips:
npx pgai checkupalso works.bunx postgresaiif you prefer Bun.
For continuous monitoring with dashboards, install the full stack on a Linux machine with Docker:
# Quick demo with sample data npx postgresai mon local-install --demo # → Open http://localhost:3000 # Production setup (Linux + Docker required) npx postgresai prepare-db postgresql://admin@host:5432/dbname # Create monitoring role with minimal permissions npx postgresai mon local-install --api-key=YOUR_TOKEN --db-url="postgresql://..."
Get your API key at console.postgres.ai — or use the fully managed version there.
All diagnostic queries are carefully designed to avoid the observer effect — they use timeouts, row limits, and non-blocking approaches. Battle-tested on production databases with dozens of TiB of data, hundreds of kTPS, and millions of DB objects.
Preview the setup SQL before running:
npx postgresai prepare-db --print-sql postgresql://... # Review what will be createdThe prepare-db step creates a read-only postgres_ai_mon user with minimal permissions, enables pg_stat_statements, and creates postgres_ai schema with a few helper views.
| Component | Purpose |
|---|---|
| Express checkup | Instant health checks — no setup needed |
| Grafana dashboards | 13+ expert views: Node Overview, Query Analysis, Wait Events, Indexes, Tables, Replication, and more |
| Scheduled reports | 45+ metrics collected continuously via pgwatch |
| Metrics collection | pgwatch v3 + VictoriaMetrics |
| AI-ready output | Structured JSON for Claude, GPT, and other agents |
| Claude Code plugin | Slash commands for health checks in your IDE |
| MCP server | Work with Issues from console.postgres.ai in AI coding tools |
postgresai is designed to feed AI agents with structured PostgreSQL diagnostics.
Install from the marketplace:
claude plugin marketplace add postgres-ai/postgresai claude plugin install pgai@postgresai
Then ask Claude to check your database. The postgresai skill
(SKILL.md) deploys PostgresAI
monitoring and reports the problems with exact fixes. Other agents
install the skill with npx skills add postgres-ai/postgresai --skill postgresai.
Work with Issues from console.postgres.ai:
/pgai:issues
# Install MCP server for your AI coding tool
npx postgresai mcp install
This enables AI agents to work with Issues and Action Items from console.postgres.ai directly within your IDE.
# Pipe checkup output to any LLM npx postgresai checkup --json postgresql://... | llm -s "analyze this Postgres health report" # Or use with Claude directly npx postgresai checkup --json postgresql://... | claude -p "find issues and suggest fixes"
postgresai checkup --check-id H002 → finds unused indexesDROP INDEX or creates PRFor checkup (express mode):
For full monitoring stack:
pg_stat_statements extension enablednpx pgai checkup works toonpx postgresai auth and:
--no-upload--api-key| Demo | demo.postgres.ai (login: demo / demo) |
| Docs | postgres.ai/docs |
| Issues | GitLab |
| Community | Postgres.FM · Postgres.TV |
PostgresAI — Self-Driving Postgres
For developers:
postgresai mon local-install --demo
Get a complete monitoring setup with demo data in under 2 minutes.
For production:
postgresai mon local-install --api-key=your_key # Then add your databases postgresai mon targets add "postgresql://user:pass@host:port/DB"
# Instance management postgresai mon targets add "postgresql://user:pass@host:port/DB" postgresai mon targets list postgresai mon targets test my-DB # Service management postgresai mon status postgresai mon logs postgresai mon restart # Health check postgresai mon health
⚠️ Breaking change in 0.15.0 — bundled PostgreSQL 15 → 17 requires a data migration.
0.15.0 upgrades the stack's bundled PostgreSQL from 15 to 17 (
sink-postgres, and on the demotarget-db/target-standby). PostgreSQL's on-disk format is not compatible across major versions, so the new images refuse to start on a PostgreSQL 15 data directory — an in-place upgrade will not come up until you migrate the data. Nothing is deleted automatically, and your externally-monitored databases are unaffected, but you must act on the bundled databases before bringing the stack up.
sink-postgresstores your historical monitoring measurements, so choose one:
- Preserve history — while still on the old images, dump it, then restore after the upgrade:
# before `mon stop`/upgrade, with sink-postgres (PG15) running: docker compose exec sink-postgres pg_dumpall -U postgres > sink-pg15.sql # after the stack is on PG17 (sink-postgres recreated empty): docker compose exec -T sink-postgres psql -U postgres < sink-pg15.sql- Start fresh — remove its volume so PostgreSQL 17 initializes a clean data directory. This discards all previously collected monitoring measurements (new metrics accumulate from scratch); find the volume with
docker volume ls | grep sink_postgres_data, thendocker volume rm <name>while the stack is stopped.The demo
target-db/target-standbyhold only throwaway sample data — just reset their volumes the same way (or re-runpostgresai mon local-install --demoto recreate them).Keep the old PostgreSQL 15 volume as a backup until you have verified PostgreSQL 17. The full upgrade steps are below; see also the monitoring upgrade docs.
To upgrade postgres_ai monitoring to a newer version:
npm install -g postgresai@0.15.0
Or if you're using npx:
npx postgresai@0.15.0 --version # verify the new versionpostgresai mon stop
mon update migrates .env (adds any newly-required keys), refreshes docker-compose.yml to the new stack version, and pulls images — all while preserving your user-managed instances.yml. It does not change PGAI_TAG, so set the new image tag yourself first — otherwise mon update just re-pulls and restarts the old version:
# In your monitoring directory (typically ~/.postgres_ai/), edit .env and set # PGAI_TAG to the version you are upgrading to (it should match your new CLI # version), e.g. for the 0.15 line: PGAI_TAG=0.15.0 postgresai mon update postgresai mon start
This will:
.env keys for the newer stack (existing values, your secrets, and instances.yml targets are preserved)docker-compose.yml to match the new stack version on non-git installs (e.g. npx / npm install -g), backing up the old file as docker-compose.yml.bak-<oldtag>-<hash> (the original is never overwritten on repeated runs) — this is what wires newly-required service config such as VM_AUTH_* on sink-prometheus. The fetched compose is validated before it replaces your working one, so a network proxy/login page can never clobber it. Git checkouts already get this via git pull, so it is skipped for them.PGAI_TAG you setNote: The
.envfile contains configuration for the monitoring stack, includingPGAI_TAG(the Docker image version tag),REPLICATOR_PASSWORD(generated password for the demo standby replication user),VM_AUTH_USERNAME,VM_AUTH_PASSWORD, and optionallyGF_SECURITY_ADMIN_PASSWORD(Grafana admin password) andPGAI_REGISTRY(custom Docker registry).postgresai mon local-installpreserves existingREPLICATOR_PASSWORDandVM_AUTH_*values or generates new ones when they are missing; Docker Compose requires these values and does not use known default passwords. It also holds the VictoriaMetrics admin-endpoint keysVM_DELETE_AUTH_KEY,VM_SNAPSHOT_AUTH_KEY,VM_FORCE_MERGE_AUTH_KEYandVM_PPROF_AUTH_KEY(added in 0.17, see #359), which follow the same preserve-or-generate lifecycle and guard the endpoints that can destroy the metrics store.
In-place upgrade note: Newer stack versions can require both additional
.envkeys and a matchingdocker-compose.yml(e.g.,VM_AUTH_USERNAME/VM_AUTH_PASSWORDand thesink-prometheusauth wiring were added in 0.15 for VictoriaMetrics basic auth).postgresai mon local-install -y,postgresai mon update, andpostgresai mon update-configall perform a purely-additive.envmigration on every run (existing values preserved verbatim; newly-required keys appended with safe random defaults) and refreshdocker-compose.ymlto the new stack version on non-git installs (npx /npm install -g), backing up the old compose asdocker-compose.yml.bak-<oldtag>-<hash>(the pristine original is preserved across repeated runs; the fetched compose is validated as a real stack file before it replaces yours). This closes the prior gap where npx upgrades kept a stale 0.14 compose andsink-prometheuscrashed withmissing "VM_AUTH_USERNAME" env var. If you rundocker composedirectly and maintain.envyourself, addVM_AUTH_USERNAME=vmauthand a non-emptyVM_AUTH_PASSWORDbefore upgrading, or runpostgresai mon update-configonce to have the CLI fill them in (and refresh the compose) for you. To rotate the VictoriaMetrics auth password, runVM_AUTH_PASSWORD="$(openssl rand -base64 18)" ./scripts/rotate-vm-auth.shfrom the monitoring directory; the script updates.envand recreatessink-prometheusplusgrafanatogether so datasource provisioning cannot reinsert stale credentials on restart. 0.17 added the VictoriaMetrics admin-endpoint keys the same way (VM_DELETE_AUTH_KEY,VM_SNAPSHOT_AUTH_KEY,VM_FORCE_MERGE_AUTH_KEY,VM_PPROF_AUTH_KEY); because those flags live on thesink-prometheuscommand line,mon updateandmon update-configbring that one service in line with the compose file after adding them — a plainmon restartisdocker compose restartand would report success while leaving the old, keyless container running.rotate-vm-auth.shdoes not touch the admin keys: to rotate one, edit.envand rundocker compose up -d --no-deps sink-prometheuson a running stack (--no-depsavoids re-runningconfig-init, which would re-seedprometheus.ymlfrom the image).
Alternative: Manual upgrade
If you prefer more control:
# Update the PGAI_TAG in .env to match your target stack version # Edit .env and set PGAI_TAG=0.15.0 # Migrate .env to add any newly-required keys (e.g. VM_AUTH_* for 0.15+) postgresai mon update-config # Pull new images docker compose pull # Start services postgresai mon start
After upgrading, verify services are running correctly:
postgresai mon status postgresai mon health
Check Grafana dashboards at http://localhost:3000 to confirm metrics are being collected.
Deploy postgres_ai monitoring to Kubernetes using helm:
# Install from the helm chart directory helm install postgres-ai-monitoring ./postgres_ai_helm # Or install from a release (replace <VERSION> with the desired release, e.g. 0.14.0) curl -LO https://gitlab.com/postgres-ai/postgresai/-/releases/helm-v<VERSION>/downloads/postgres-ai-monitoring-chart.tgz helm install postgres-ai-monitoring postgres-ai-monitoring-chart.tgz # Customize installation helm install postgres-ai-monitoring ./postgres_ai_helm -f custom-values.yaml
For maintainers creating new helm chart releases:
cd postgres_ai_helm # Test the chart locally ./test-release.sh # Create a new release (e.g. 0.14.0) ./release.sh <VERSION>
This automatically:
postgres_ai monitoring generates automated health check reports based on postgres-checkup. Each report has a unique check ID and title:
| Check ID | Title |
|---|---|
| A001 | System information |
| A002 | Version information |
| A003 | Postgres settings |
| A004 | Cluster information |
| A005 | Extensions |
| A006 | Postgres setting deviations |
| A007 | Altered settings |
| A008 | Disk usage and file system type |
A005 reads every database that accepts connections, each over its own connection with the checkup's credentials (at most 100 databases, within 60 seconds): installed extensions with versions, index counts per access method and the extension of their operator classes, row security policies and extended statistics. From the connected database it adds the logical slots with their plugin, the non-superuser roles with CREATE on each database, and the roles that can use replication. MD5 password hashes (pg_authid) and pg_hba.conf rules need a superuser: for any other role they are recorded as unknown, with the reason, never as none. A database that is not read is listed with the reason.
| Check ID | Title |
|---|---|
| D004 | pg_stat_statements and pg_stat_kcache settings |
| Check ID | Title |
|---|---|
| F001 | Autovacuum: current settings |
| F004 | Autovacuum: heap bloat (estimated) |
| F005 | Autovacuum: index bloat (estimated) |
| F009 | Xmin horizon and blockers |
F009 includes a 500-character query preview only when the checkup role can read all
statistics. Disable preview capture with
PGOPTIONS='-c postgresai.capture_query_preview=off' postgresai checkup ....
| Check ID | Title |
|---|---|
| G001 | Memory-related settings |
| Check ID | Title |
|---|---|
| H001 | Invalid indexes |
| H002 | Unused indexes |
| H004 | Redundant indexes |
| Check ID | Title |
|---|---|
| K001 | Globally aggregated query metrics |
| K003 | Top queries by total time (total_exec_time + total_plan_time) |
| K004 | Top queries by temp bytes written |
| K005 | Top queries by WAL generation |
| K006 | Top queries by shared blocks read |
| K007 | Top queries by shared blocks hit |
| Check ID | Title |
|---|---|
| M001 | Top queries by mean execution time |
| M002 | Top queries by rows (I/O intensity) |
| M003 | Top queries by I/O time |
| Check ID | Title |
|---|---|
| N001 | Wait events grouped by type and query |
After running local-install:
monitoring; password is shown at the end of local-install)Technical URLs (for advanced users):
postgresai --help postgresai mon --help
Get your access token at PostgresAI for automated report uploads and advanced analysis.
pg_wait_sampling and pg_stat_kcache extension supportPython-based report generation lives under reporter/ and now ships with a pytest suite.
Install dev dependencies (includes pytest, pytest-postgresql, psycopg, etc.):
python3 -m pip install -r reporter/requirements-dev.txt
Run only unit tests with mocked Prometheus interactions:
pytest tests/reporter
This automatically skips integration tests. Or run specific test files:
pytest tests/reporter/test_generators_unit.py -v pytest tests/reporter/test_formatters.py -v
Run the complete test suite (both unit and integration tests):
pytest tests/reporter --run-integration
Integration tests create a temporary PostgreSQL instance automatically and require PostgreSQL binaries (initdb, postgres) on your PATH. No manual database setup or environment variables are required - the tests create and destroy their own temporary PostgreSQL instances.
Summary:
pytest tests/reporter → Unit tests only (integration tests skipped)pytest tests/reporter --run-integration → Both unit and integration testsGenerate coverage report:
pytest tests/reporter -m unit --cov=reporter --cov-report=html
View the coverage report by opening htmlcov/index.html in your browser.
We welcome contributions from Postgres experts! Please check our GitLab repository for:
This project is licensed under the Apache License 2.0 - see the LICENSE file for details.
postgres_ai monitoring is developed by PostgresAI, bringing years of Postgres expertise into automated monitoring and analysis tools. We provide enterprise consulting and advanced Postgres solutions for fast-growing companies.
Wait-event charts in monitoring and supporting materials follow the PostgresAI color scheme.