gem-hunter

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.

• Structured for AI — Reports and metrics designed for LLM consumption
• Issues workflow — Track problems from detection to resolution
• 45+ health checks — Bloat, indexes, queries, settings, security
• Active Session History — Postgres's answer to Oracle ASH
• Expert dashboards — Four Golden Signals methodology

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:

• Detailed reports with suggested fixes
• Issues workflow to track remediation
• Historical data across all your projects

Offline mode: Add --no-upload to 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 checkup also works. bunx postgresai if 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 created

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

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"

1. AI agent detects slow query from application logs
2. Runs postgresai checkup --check-id H002 → finds unused indexes
3. Gets structured JSON with analysis
4. Suggests DROP INDEX or creates PR

For checkup (express mode):

• Node.js 18+ (includes npm/npx)
• Network access to PostgreSQL 14-18

For full monitoring stack:

• Linux machine with Docker
• pg_stat_statements extension enabled

• Short alias: npx pgai checkup works too
• Can I use it without registration on console.postgres.ai? Yes, both express checkup and full-fledged monitoring are open-source and can be used without any connection to console.postgres.ai. In instructions above, just skip npx postgresai auth and:
  • Express checkup: use --no-upload
  • Full monitoring: omit --api-key
• Managed version: Express checkup finds problems. The managed version at console.postgres.ai also explains how to fix them and provides an Issues workflow to track fixes

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 demo target-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-postgres stores 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, then docker volume rm <name> while the stack is stopped.

The demo target-db / target-standby hold only throwaway sample data — just reset their volumes the same way (or re-run postgresai mon local-install --demo to 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 version

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

• Add any newly required .env keys for the newer stack (existing values, your secrets, and instances.yml targets are preserved)
• Refresh 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.
• Pull the Docker images for the PGAI_TAG you set
• Start the services on the new images

Note: The .env file contains configuration for the monitoring stack, including PGAI_TAG (the Docker image version tag), REPLICATOR_PASSWORD (generated password for the demo standby replication user), VM_AUTH_USERNAME, VM_AUTH_PASSWORD, and optionally GF_SECURITY_ADMIN_PASSWORD (Grafana admin password) and PGAI_REGISTRY (custom Docker registry). postgresai mon local-install preserves existing REPLICATOR_PASSWORD and VM_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 keys VM_DELETE_AUTH_KEY, VM_SNAPSHOT_AUTH_KEY, VM_FORCE_MERGE_AUTH_KEY and VM_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 .env keys and a matching docker-compose.yml (e.g., VM_AUTH_USERNAME / VM_AUTH_PASSWORD and the sink-prometheus auth wiring were added in 0.15 for VictoriaMetrics basic auth). postgresai mon local-install -y, postgresai mon update, and postgresai mon update-config all perform a purely-additive .env migration on every run (existing values preserved verbatim; newly-required keys appended with safe random defaults) and refresh docker-compose.yml to the new stack version on non-git installs (npx / npm install -g), backing up the old compose as docker-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 and sink-prometheus crashed with missing "VM_AUTH_USERNAME" env var. If you run docker compose directly and maintain .env yourself, add VM_AUTH_USERNAME=vmauth and a non-empty VM_AUTH_PASSWORD before upgrading, or run postgresai mon update-config once to have the CLI fill them in (and refresh the compose) for you. To rotate the VictoriaMetrics auth password, run VM_AUTH_PASSWORD="$(openssl rand -base64 18)" ./scripts/rotate-vm-auth.sh from the monitoring directory; the script updates .env and recreates sink-prometheus plus grafana together 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 the sink-prometheus command line, mon update and mon update-config bring that one service in line with the compose file after adding them — a plain mon restart is docker compose restart and would report success while leaving the old, keyless container running. rotate-vm-auth.sh does not touch the admin keys: to rotate one, edit .env and run docker compose up -d --no-deps sink-prometheus on a running stack (--no-deps avoids re-running config-init, which would re-seed prometheus.yml from 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

• Complete monitoring stack for Kubernetes
• pgwatch, VictoriaMetrics, and Grafana
• Automated report generation via CronJobs
• Node exporter and cAdvisor for system metrics
• Configurable resource limits and persistence

• README: Quick start and overview
• INSTALLATION_GUIDE: Detailed installation instructions
• RELEASE: Release process for maintainers

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:

• Updates Chart.yaml with the new version
• Creates a git tag
• Triggers GitLab CI/CD to package and publish the chart
• Creates a GitLab release with the packaged chart

postgres_ai monitoring generates automated health check reports based on postgres-checkup. Each report has a unique check ID and title:

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.

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

After running local-install:

• 🚀 MAIN: Grafana Dashboard: http://localhost:3000 (login: monitoring; password is shown at the end of local-install)

Technical URLs (for advanced users):

• Demo DB: postgresql://postgres:postgres@localhost:55432/target_database
• Monitoring: http://localhost:58080 (pgwatch)
• Metrics: http://localhost:59090 (VictoriaMetrics)

postgresai --help
postgresai mon --help

Get your access token at PostgresAI for automated report uploads and advanced analysis.

• Host stats for on-premise and managed Postgres setups
• pg_wait_sampling and pg_stat_kcache extension support
• Additional expert dashboards: autovacuum, checkpointer, lock analysis
• Query plan analysis and automated recommendations
• Enhanced AI integration capabilities

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

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

• Code standards and review process
• Dashboard design principles
• Testing requirements for monitoring components

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.

• 💬 Get support
• 📺 Postgres.TV (YouTube)
• 🎙️ Postgres FM Podcast
• 🐛 Report issues
• 📧 Enterprise support

Wait-event charts in monitoring and supporting materials follow the PostgresAI color scheme.