pg-health 2.0.0

CLI + MCP PostgreSQL health, migration safety, and query explain toolkit for agents

English | 中文

PG Health 🐘

CLI + MCP-first, agent-native PostgreSQL health, migration safety, and query explain toolkit.

pg-health lives in your terminal and inside your AI agent (via MCP). Want a browser dashboard instead? See its sibling pg-dash.

Features

• 🚦 check-migration — static safety analysis of a migration SQL file (14 rules: missing CONCURRENTLY, NOT NULL without DEFAULT, DROP TABLE, prepared-statement cache invalidation, …)
• 🔬 explain — EXPLAIN (ANALYZE, BUFFERS) with a readable plan tree and heuristic warnings (large Seq Scan, heavy Nested Loop)
• 🔍 Health check — 20+ rules: cache hit ratio, connection usage, unused indexes, table bloat, replication lag, lock waits, vacuum stats, missing FK indexes, transaction ID age, WAL/background writer stats, config audit, and more
• 🛠️ suggest / fix — prioritized recommendations with one-line SQL, plus safe auto-fixes (drop unused indexes, vacuum, analyze) with dry-run by default
• 📱 Notifications — Telegram, Slack, webhook, email
• 📈 History & trend — SQLite-backed time series over repeat checks
• 🤖 MCP server — 5 first-class tools for agents (check, suggest, fix, check_migration, explain), installed by default

Quick Start

# Install (pipx recommended)
pipx install pg-health

# Run a health check
pg-health check -c "postgresql://user:pass@host:5432/dbname"

# Analyze a migration before you ship it
pg-health check-migration migrations/20260423_add_role.sql

# EXPLAIN a slow query
pg-health explain "SELECT * FROM users WHERE email = 'a@b.c'" -c "postgresql://..."

# Start the MCP server for Claude / Cursor / etc.
pg-health-mcp

CLI Usage

# Basic check
pg-health check -c "postgresql://user:pass@localhost:5432/mydb"

# Save report to JSON file
pg-health check -c "..." -o report.json

# JSON output to stdout (agent-friendly)
pg-health check -c "..." --json

# Quiet mode - just output status (OK, WARNING, or CRITICAL)
pg-health check -c "..." --quiet

# With custom thresholds
pg-health check -c "..." --config config.yaml

# Get actionable recommendations
pg-health suggest -c "..."

# Apply quick fixes (with dry-run)
pg-health fix unused-indexes -c "..." --dry-run
pg-health fix vacuum -c "..." --tables orders,users
pg-health fix all -c "..."

# Analyze a migration SQL file (exits 1 on errors — CI-friendly)
pg-health check-migration migrations/20260423_add_role.sql
pg-health check-migration migration.sql --json

# EXPLAIN a query with plan tree rendering
pg-health explain "SELECT * FROM orders WHERE user_id = 1" -c "..."
pg-health explain "SELECT * FROM users" -c "..." --no-analyze
pg-health explain "..." -c "..." --json

# Start web interface
pg-health serve --port 8767

# Generate status badge
pg-health badge -c "..." -o badge.svg

Exit Codes

The check command returns meaningful exit codes for automation:

Exit Code	Status	Meaning
0	OK	All checks passed
1	WARNING	One or more warnings
2	CRITICAL	One or more critical issues

Agent-Friendly Output

For CI/CD pipelines and monitoring scripts:

# JSON output for parsing
pg-health check -c "..." --json | jq '.status'

# Simple status for shell scripts
if [ "$(pg-health check -c "..." --quiet)" = "OK" ]; then
  echo "Database healthy"
fi

# Use exit codes
pg-health check -c "..." --quiet
case $? in
  0) echo "OK" ;;
  1) echo "WARNING - check logs" ;;
  2) echo "CRITICAL - immediate attention needed" ;;
esac

MCP Server (for AI Agents)

pg-health ships with an MCP server for integration with Claude, Cursor, and other agents — installed by default as of v2.0. No extras needed.

Add to Claude Desktop / Claude Code

Add to ~/.claude/mcp.json (or ~/.config/claude/claude_desktop_config.json):

{
  "mcpServers": {
    "pg-health": {
      "command": "pg-health-mcp",
      "env": { "DATABASE_URL": "postgresql://user:pass@host:5432/db" }
    }
  }
}

Available Tools

Tool	Description
pg_health_check	Run comprehensive health check, returns status + issues
pg_health_suggest	Get prioritized optimization recommendations
pg_health_fix	Apply fixes (dry-run by default for safety)
pg_health_check_migration	Analyze a migration SQL file for safety issues (14 static rules)
pg_health_explain	EXPLAIN ANALYZE a SELECT query, return parsed plan tree

Migrating from pg-dash MCP

If your ~/.claude/mcp.json currently has the pg-dash MCP server:

"pg-dash": {
  "command": "npx",
  "args": ["-y", "@indiekitai/pg-dash-mcp"],
  "env": { "DATABASE_URL": "..." }
}

Replace it with:

"pg-health": {
  "command": "pg-health-mcp",
  "env": { "DATABASE_URL": "..." }
}

Existing agent prompts keep working during the transition — the following deprecated tool names delegate to the new pg_health_* tools for 3 months, then get removed in v3.0:

Deprecated alias	Routes to
pg_dash_health	pg_health_check
pg_dash_check_migration	pg_health_check_migration
pg_dash_analyze_query	pg_health_explain
pg_dash_unused_indexes	subset of pg_health_check

The aliases prepend a one-line banner reminding you to update — use that as your TODO signal.

Other pg_dash_* tools that used to ship with pg-dash MCP (pg_dash_schema_diff, pg_dash_bloat, pg_dash_query_stats, …) do not have CLI equivalents. They live on as web UI features in pg-dash.

Example Usage

Claude or other agents can:

> Check my database health
[uses pg_health_check with your connection string]

> Is this migration safe to deploy?
[uses pg_health_check_migration with the path to the SQL file]

> Why is this query slow?
[uses pg_health_explain with the query]

> What should I optimize?
[uses pg_health_suggest]

> Drop the unused indexes (dry run first)
[uses pg_health_fix with fix_type="unused-indexes", dry_run=True]

Direct Python Usage

from pg_health.mcp_server import (
    pg_health_check, pg_health_suggest, pg_health_fix,
    pg_health_check_migration, pg_health_explain,
)

result = pg_health_check("postgresql://user:pass@host:5432/db")
suggestions = pg_health_suggest("postgresql://...")
fixes = pg_health_fix("postgresql://...", fix_type="unused-indexes", dry_run=True)
migration_report = pg_health_check_migration("postgresql://...", "./migration.sql")
plan = pg_health_explain("postgresql://...", "SELECT * FROM users WHERE id = 1")

---

Status Badge

Generate an SVG badge for dashboards or READMEs:

# Output to file
pg-health badge -c "..." -o badge.svg

# Output to stdout (pipe to file or HTTP response)
pg-health badge -c "..."

Badge shows:

• Green "DB Health | OK" - All checks passed
• Yellow "DB Health | 2 warnings" - Warning-level issues
• Red "DB Health | CRITICAL" - Critical issues detected

Auto-Suggest

Get actionable recommendations based on your database health:

pg-health suggest -c "postgresql://user:pass@host:5432/db"

Output:

🔍 Analyzing database health...

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Recommendations
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

🔴 HIGH PRIORITY

1. Increase shared_buffers
   Why: Cache hit ratio is 89.0% (should be >95%)
   Impact: Better cache hit ratio means faster queries
   Action: Edit postgresql.conf, set shared_buffers to ~25% of RAM. Current: 128MB

2. VACUUM ANALYZE public.orders
   Why: 150,000 dead tuples (15.2% bloat)
   Impact: Reclaim disk space, improve query performance
   SQL: VACUUM ANALYZE public.orders;

🟡 MEDIUM PRIORITY

3. Drop unused index idx_old_column
   Why: 0 scans since stats reset, 50MB wasted
   Impact: Free 50MB disk space, faster writes
   SQL: DROP INDEX public.idx_old_column;

🟢 LOW PRIORITY

4. Consider partitioning public.logs
   Why: Table is 12GB with 50,000,000 rows
   Impact: Improved query performance, easier maintenance
   Action: Partition by date/time column if available

Recommendations include:

• Cache tuning - shared_buffers optimization when cache hit ratio is low
• Unused indexes - Indexes that waste space and slow writes
• Vacuum suggestions - Tables with high dead tuple counts
• Missing indexes - Tables with heavy sequential scans
• Partitioning hints - Large tables that could benefit from partitioning
• Statistics updates - Tables with outdated statistics
• Slow query analysis - Via pg_stat_statements

Quick Fix

Apply fixes automatically or preview with --dry-run:

# Preview what would be dropped
pg-health fix unused-indexes -c "..." --dry-run

# Actually drop unused indexes
pg-health fix unused-indexes -c "..."

# Vacuum specific tables
pg-health fix vacuum -c "..." --tables orders,users

# Run all safe fixes
pg-health fix all -c "..." --dry-run

Safe fixes (can auto-execute):

Fix Type	What it does
unused-indexes	DROP INDEX for indexes with 0 scans
vacuum	VACUUM ANALYZE tables with high dead tuples
analyze	ANALYZE tables with outdated statistics
all	Run all of the above

Unsafe operations (suggest only shows SQL, never executes):

• CREATE INDEX (may lock table)
• Config changes (requires restart)
• Schema changes

Configuration

Threshold Configuration

Create a YAML config file to customize thresholds:

# config.yaml
thresholds:
  cache_hit_ratio:
    warning: 0.95    # Warn if < 95%
    critical: 0.90   # Critical if < 90%
  
  connections:
    warning: 0.70    # Warn if > 70% of max_connections
    critical: 0.90
  
  replication_lag:
    warning: 10      # Warn if > 10 seconds behind
    critical: 60     # Critical if > 60 seconds behind
  
  dead_tuples:
    warning: 100000  # Warn if any table has > 100k dead tuples
    critical: 1000000
  
  lock_waits:
    warning: 5       # Warn if > 5 queries waiting for locks
    critical: 20
  
  table_bloat:
    warning: 0.10    # Warn if > 10% dead tuples
    critical: 0.20

Use the config:

# Via command line
pg-health check -c "..." --config config.yaml

# Via environment variable
export PG_HEALTH_CONFIG=/path/to/config.yaml
pg-health check -c "..."

See config.example.yaml for a complete example.

Environment Variables

# Set connection string
DATABASE_URL=postgresql://user:password@host:5432/database

# Set config file path
PG_HEALTH_CONFIG=/path/to/config.yaml

API Usage

# JSON API (AI-friendly)
curl -X POST https://pg.indiekit.ai/api/check \
  -H "Content-Type: application/json" \
  -d '{"connection_string": "postgresql://user:pass@host:5432/db"}'

Response:

{
  "ok": true,
  "report": {
    "database_name": "mydb",
    "checks": [
      {"name": "Cache Hit Ratio", "severity": "ok", "message": "..."},
      {"name": "Replication Lag", "severity": "info", "message": "Not a replica"},
      {"name": "Lock Waits", "severity": "ok", "message": "0 waiting locks"},
      ...
    ],
    "unused_indexes": [...],
    "tables": [...],
    "slow_queries": [...],
    "vacuum_stats": [...]
  }
}

Note: Special characters in password (like @) are auto-encoded.

Health Checks

Check	OK	Warning	Critical
Cache Hit Ratio	> 95%	90-95%	< 90%
Index Hit Ratio	> 95%	90-95%	< 90%
Connection Usage	< 70%	70-90%	> 90%
Replication Lag	< 10s	10-60s	> 60s
Lock Waits	≤ 5	6-20	> 20
Dead Tuples	< 100k	100k-1M	> 1M
Table Bloat	< 10% dead	10-20% dead	> 20% dead
Duplicate Indexes	0	1+ pairs	-
FK Missing Indexes	0-3	4+	-
Transaction ID Age	< 500M	500M-1B	> 1B

All thresholds are configurable via the YAML config file.

Requirements

• Python 3.11+
• PostgreSQL 12+ (for pg_stat_statements)
• PyYAML (for custom thresholds)

Cloud Database Support

Supabase

# Get connection string from Supabase Dashboard > Settings > Database
pg-health check -c "postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres"

# Or use direct connection (port 5432)
pg-health check -c "postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres"

Neon

# Get connection string from Neon Console
pg-health check -c "postgresql://[user]:[password]@[endpoint].neon.tech/[database]?sslmode=require"

AWS RDS / Aurora

pg-health check -c "postgresql://[user]:[password]@[instance].rds.amazonaws.com:5432/[database]"

Note: Some checks (like pg_stat_statements) may require enabling extensions or specific permissions on managed databases.

Privacy

Your connection string is never stored. All checks run in real-time and results are not saved on our servers.

Notifications

Get alerts when health checks find issues.

Telegram

# Set credentials
export PG_HEALTH_TELEGRAM_TOKEN="your-bot-token"
export PG_HEALTH_TELEGRAM_CHAT_ID="your-chat-id"

# Run check and notify
pg-health notify -c "postgresql://..." --provider telegram

# Only notify if there are issues (default)
pg-health notify -c "..." --only-issues

# Always notify (even if healthy)
pg-health notify -c "..." --always

Slack

export PG_HEALTH_SLACK_WEBHOOK="https://hooks.slack.com/services/..."
pg-health notify -c "..." --provider slack

Webhook (generic)

export PG_HEALTH_WEBHOOK_URL="https://your-server.com/webhook"
pg-health notify -c "..." --provider webhook

Webhook payload:

{
  "database": "mydb",
  "status": "warning",
  "has_issues": true,
  "checks": [...],
  "summary": {"total_checks": 20, "warnings": 2, "criticals": 0}
}

Cron Example

# Run every hour, only alert on issues
0 * * * * pg-health notify -c "$DATABASE_URL" --only-issues

Email

export PG_HEALTH_SMTP_HOST="smtp.gmail.com"
export PG_HEALTH_SMTP_PORT="587"
export PG_HEALTH_SMTP_USER="[e-mail]"
export PG_HEALTH_SMTP_PASS="app-password"
export PG_HEALTH_EMAIL_FROM="[e-mail]"
export PG_HEALTH_EMAIL_TO="[e-mail]"

pg-health notify -c "..." --provider email

Historical Trends

Track health metrics over time with built-in SQLite storage.

# Run check and save to history
pg-health check -c "..." --save

# View history
pg-health history
pg-health history --database mydb --days 30

# List available metrics
pg-health trend mydb

# View specific metric trend
pg-health trend mydb --metric "Cache Hit Ratio.ratio"
pg-health trend mydb --metric "Connection Usage.usage_ratio" --days 30

Data is stored in ~/.pg-health/history.db (configurable via PG_HEALTH_DATA_DIR).

Available metrics include:

• Cache/Index hit ratios
• Connection counts
• Lock waits
• Background writer stats
• Database size

Roadmap

• Replication lag monitoring
• Lock wait detection
• Vacuum statistics
• Configurable thresholds
• JSON output mode
• Status badges
• Auto-suggest recommendations
• Quick fix commands
• Telegram notifications
• Slack notifications
• Webhook notifications
• Email alerts (SMTP)
• Historical trends (SQLite)
• Supabase/Neon support (via connection string)
• Scheduled checks (built-in daemon)

License

MIT