Skip to content
Back to search

postgresai

MarketplaceWorks with Claude CodeGitHub

postgresai – Postgres observability built for humans and AI agents

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today

Check it deeper on your machine

It has changed since its scan on 11 Nov 2026.

As a Claude Code plugin, install it and then ask Claude Code:

/plugin marketplace add keithmackay/mackayi/plugin install scanskillsafety@mackayiDo a deep scan of https://github.com/postgres-ai/postgresai with scanskillsafety

Or from a terminal, clone it and run it:

git clone https://github.com/keithmackay/scanskillsafety-skill ~/.claude/skills/scanskillsafetynode ~/.claude/skills/scanskillsafety/cli.cjs --deep 'https://github.com/postgres-ai/postgresai'

It checks the exact commit: every file, its packages against OSV (api.osv.dev), how it compares with this page's scan (findsafeskills.com receives the repository's address), and, if you agree, Claude reading every file with your own Claude subscription. It never runs the code.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/postgres-ai--postgresai/badge.svg)](/listings/postgres-ai--postgresai)

HTML

<a href="/listings/postgres-ai--postgresai"><img src="/api/listings/postgres-ai--postgresai/badge.svg" alt="findsafeskills safety rating" /></a>

Install

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

Install dev dependencies (includes `pytest`, `pytest-postgresql`, `psycopg`, etc.):

python3 -m pip install -r reporter/requirements-dev.txt

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

.claude-plugin/marketplace.json · Manifest · 23 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. {
  2. "name": "postgresai",
  3. "owner": {
  4. "name": "PostgresAI",
  5. "email": "team@postgres.ai"
  6. },
  7. "metadata": {
  8. "description": "AI-native PostgreSQL observability",
  9. "version": "1.0.0"
  10. },
  11. "plugins": [
  12. {
  13. "name": "pgai",
  14. "source": "./.claude",
  15. "description": "Monitoring, health checks, and root cause analysis — built for humans and AI agents",
  16. "version": "1.0.0",
  17. "author": {
  18. "name": "PostgresAI"
  19. },
  20. "license": "Apache-2.0"
  21. }
  22. ]
  23. }
README.md · README · 607 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. <div align="center">
  2. # postgresai
  3. ### AI-native PostgreSQL observability
  4. [![npm](https://img.shields.io/npm/v/postgresai?style=flat-square&logo=npm)](https://www.npmjs.com/package/postgresai)
  5. [![License](https://img.shields.io/badge/license-Apache%202.0-blue?style=flat-square)](LICENSE)
  6. [![PostgreSQL](https://img.shields.io/badge/PostgreSQL-14--18-336791?style=flat-square&logo=postgresql&logoColor=white)](https://postgresql.org)
  7. [![CLI Coverage](https://img.shields.io/gitlab/pipeline-coverage/postgres-ai%2Fpostgresai?branch=main&job_name=cli%3Anode%3Atests&label=CLI%20coverage&style=flat-square)](https://gitlab.com/postgres-ai/postgresai/-/pipelines)
  8. [![Reporter Coverage](https://img.shields.io/gitlab/pipeline-coverage/postgres-ai%2Fpostgresai?branch=main&job_name=reporter%3Atests&label=Reporter%20coverage&style=flat-square)](https://gitlab.com/postgres-ai/postgresai/-/pipelines)
  9. **Monitoring, health checks, and root cause analysis — built for humans and AI agents**
  10. [Live Demo](https://demo.postgres.ai) ·
  11. [Documentation](https://postgres.ai/docs) ·
  12. [Get started](#get-started)
  13. </div>
  14. <div align="center">
  15. <img src="assets/postgresai.png" alt="postgresai" width="500">
  16. </div>
  17. Battle-tested in PostgresAI team's work with [companies like GitLab, Miro, Chewy, Suno, Supabase, Gadget, and more](https://postgres.ai) — now packaged for easy use by humans and AI agents.
  18. ## Why postgresai?
  19. Traditional monitoring tools give you dashboards. **`postgresai` gives AI agents the context they need to actually fix problems.**
  20. - **Structured for AI** — Reports and metrics designed for LLM consumption
  21. - **Issues workflow** — Track problems from detection to resolution
  22. - **45+ health checks** — Bloat, indexes, queries, settings, security
  23. - **Active Session History** — Postgres's answer to Oracle ASH
  24. - **Expert dashboards** — Four Golden Signals methodology
  25. Part of [Self-Driving Postgres](https://postgres.ai/blog/20250725-self-driving-postgres) — PostgresAI's open-source initiative to make Postgres autonomous.
  26. ## Get started
  27. ### 1. Sign up
  28. Create a free account at [postgres.ai](https://postgres.ai)
  29. ### 2. Authenticate
  30. ```bash
  31. npx postgresai auth
  32. ```
  33. This opens your browser to log in and saves your API key locally.
  34. ### 3. Run health checks
  35. ```bash
  36. PGPASSWORD=secret npx postgresai checkup postgresql://user@host:5432/dbname
  37. ```
  38. ### 4. View results
  39. Open [console.postgres.ai](https://console.postgres.ai) to see:
  40. - Detailed reports with suggested fixes
  41. - Issues workflow to track remediation
  42. - Historical data across all your projects
  43. > **Offline mode:** Add `--no-upload` to run locally without an account or sending report data to the PostgresAI API.
  44. <details open>
  45. <summary>See demo</summary>
  46. <div align="center">
  47. <img src="assets/demo-checkup.gif" alt="postgresai checkup demo" width="700">
  48. </div>
  49. </details>
  50. ## Express Checkup
  51. Run specific checks or work offline:
  52. ```bash
  53. # Run a specific check
  54. npx postgresai checkup --check-id H002 postgresql://...
  55. # Local JSON output only (no upload)
  56. npx postgresai checkup --no-upload --check-id H002 postgresql://...
  57. ```
  58. Checkup reports are JSON. Use `--json` to print them or `--output <dir>` to save
  59. them; uploaded reports show up in the Console.
  60. > **Tips:** `npx pgai checkup` also works. `bunx postgresai` if you prefer Bun.
  61. ## Full monitoring stack
  62. For continuous monitoring with dashboards, install the full stack on a Linux machine with Docker:
  63. ```bash
  64. # Quick demo with sample data
  65. npx postgresai mon local-install --demo
  66. # → Open http://localhost:3000
  67. # Production setup (Linux + Docker required)
  68. npx postgresai prepare-db postgresql://admin@host:5432/dbname # Create monitoring role with minimal permissions
  69. npx postgresai mon local-install --api-key=YOUR_TOKEN --db-url="postgresql://..."
  70. ```
  71. Get your API key at [console.postgres.ai](https://console.postgres.ai) — or use the fully managed version there.
  72. ### Production-safe
  73. All diagnostic queries are carefully designed to avoid the [observer effect](https://en.wikipedia.org/wiki/Observer_effect_(information_technology)) — 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.
  74. Preview the setup SQL before running:
  75. ```bash
  76. npx postgresai prepare-db --print-sql postgresql://... # Review what will be created
  77. ```
  78. 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.
  79. ## What's inside
  80. | Component | Purpose |
  81. |-----------|---------|
  82. | **Express checkup** | Instant health checks — no setup needed |
  83. | **Grafana dashboards** | 13+ expert views: Node Overview, Query Analysis, Wait Events, Indexes, Tables, Replication, and more |
  84. | **Scheduled reports** | 45+ metrics collected continuously via pgwatch |
  85. | **Metrics collection** | pgwatch v3 + VictoriaMetrics |
  86. | **AI-ready output** | Structured JSON for Claude, GPT, and other agents |
  87. | **Claude Code plugin** | Slash commands for health checks in your IDE |
  88. | **MCP server** | Work with Issues from [console.postgres.ai](https://console.postgres.ai) in AI coding tools |
  89. ## For AI developers
  90. `postgresai` is designed to feed AI agents with structured PostgreSQL diagnostics.
  91. ### Claude Code plugin
  92. Install from the marketplace:
  93. ```bash
  94. claude plugin marketplace add postgres-ai/postgresai
  95. claude plugin install pgai@postgresai
  96. ```
  97. Then ask Claude to check your database. The `postgresai` skill
  98. ([SKILL.md](.claude/skills/postgresai/SKILL.md)) deploys PostgresAI
  99. monitoring and reports the problems with exact fixes. Other agents
  100. install the skill with `npx skills add postgres-ai/postgresai --skill postgresai`.
  101. Work with Issues from [console.postgres.ai](https://console.postgres.ai):
  102. ```
  103. /pgai:issues
  104. ```
  105. <details>
  106. <summary>See demo</summary>
  107. <div align="center">
  108. <img src="assets/demo-claude-code.gif" alt="Claude Code plugin demo" width="700">
  109. </div>
  110. </details>
  111. ### MCP server (Claude Code, Cursor, Windsurf)
  112. ```bash
  113. # Install MCP server for your AI coding tool
  114. npx postgresai mcp install
  115. ```
  116. <details>
  117. <summary>See demo</summary>
  118. <div align="center">
  119. <img src="assets/demo-mcp.gif" alt="MCP install demo" width="700">
  120. </div>
  121. </details>
  122. This enables AI agents to work with Issues and Action Items from [console.postgres.ai](https://console.postgres.ai) directly within your IDE.
  123. ### CLI + LLM
  124. ```bash
  125. # Pipe checkup output to any LLM
  126. npx postgresai checkup --json postgresql://... | llm -s "analyze this Postgres health report"
  127. # Or use with Claude directly
  128. npx postgresai checkup --json postgresql://... | claude -p "find issues and suggest fixes"
  129. ```
  130. <details>
  131. <summary>See demo</summary>
  132. <div align="center">
  133. <img src="assets/demo-llm.gif" alt="CLI + LLM demo" width="700">
  134. </div>
  135. </details>
  136. ### Sample AI workflow
  137. 1. AI agent detects slow query from application logs
  138. 2. Runs `postgresai checkup --check-id H002` → finds unused indexes
  139. 3. Gets structured JSON with analysis
  140. 4. Suggests `DROP INDEX` or creates PR
  141. ## Requirements
  142. **For checkup (express mode):**
  143. - Node.js 18+ (includes npm/npx)
  144. - Network access to PostgreSQL 14-18
  145. **For full monitoring stack:**
  146. - Linux machine with Docker
  147. - `pg_stat_statements` extension enabled
  148. ## Tips
  149. - **Short alias:** `npx pgai checkup` works too
  150. - **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:
  151. - Express checkup: use `--no-upload`
  152. - Full monitoring: omit `--api-key`
  153. - **Managed version:** Express checkup finds problems. The managed version at [console.postgres.ai](https://console.postgres.ai) also explains how to fix them and provides an Issues workflow to track fixes
  154. ## Links
  155. | | |
  156. |---|---|
  157. | **Demo** | [demo.postgres.ai](https://demo.postgres.ai) (login: `demo` / `demo`) |
  158. | **Docs** | [postgres.ai/docs](https://postgres.ai/docs) |
  159. | **Issues** | [GitLab](https://gitlab.com/postgres-ai/postgresai/-/issues) |
  160. | **Community** | [Postgres.FM](https://postgres.fm) · [Postgres.TV](https://postgres.tv) |
  161. ---
  162. <div align="center">
  163. **[PostgresAI](https://postgres.ai)** — Self-Driving Postgres
  164. </div>
  165. ## 🎯 Use cases
  166. **For developers:**
  167. ```bash
  168. postgresai mon local-install --demo
  169. ```
  170. Get a complete monitoring setup with demo data in under 2 minutes.
  171. **For production:**
  172. ```bash
  173. postgresai mon local-install --api-key=your_key
  174. # Then add your databases
  175. postgresai mon targets add "postgresql://user:pass@host:port/DB"
  176. ```
  177. ## 🔧 Management commands
  178. ```bash
  179. # Instance management
  180. postgresai mon targets add "postgresql://user:pass@host:port/DB"
  181. postgresai mon targets list
  182. postgresai mon targets test my-DB
  183. # Service management
  184. postgresai mon status
  185. postgresai mon logs
  186. postgresai mon restart
  187. # Health check
  188. postgresai mon health
  189. ```
  190. ## 🔄 Upgrading
  191. > ⚠️ **Breaking change in 0.15.0 — bundled PostgreSQL 15 → 17 requires a data migration.**
  192. >
  193. > 0.15.0 upgrades the stack's bundled PostgreSQL from 15 to 17 (`sink-postgres`, and on the
  194. > demo `target-db` / `target-standby`). PostgreSQL's on-disk format is not compatible across major
  195. > versions, so the new images **refuse to start** on a PostgreSQL 15 data directory — an in-place
  196. > upgrade will not come up until you migrate the data. Nothing is deleted automatically, and your
  197. > externally-monitored databases are unaffected, but you must act on the bundled databases before
  198. > bringing the stack up.
  199. >
  200. > **`sink-postgres`** stores your historical monitoring measurements, so choose one:
  201. > - **Preserve history** — while still on the old images, dump it, then restore after the upgrade:
  202. > ```bash
  203. > # before `mon stop`/upgrade, with sink-postgres (PG15) running:
  204. > docker compose exec sink-postgres pg_dumpall -U postgres > sink-pg15.sql
  205. > # after the stack is on PG17 (sink-postgres recreated empty):
  206. > docker compose exec -T sink-postgres psql -U postgres < sink-pg15.sql
  207. > ```
  208. > - **Start fresh** — remove its volume so PostgreSQL 17 initializes a clean data directory.
  209. > This **discards all previously collected monitoring measurements** (new metrics accumulate
  210. > from scratch); find the volume with `docker volume ls | grep sink_postgres_data`, then
  211. > `docker volume rm <name>` while the stack is stopped.
  212. >
  213. > The demo `target-db` / `target-standby` hold only throwaway sample data — just reset their
  214. > volumes the same way (or re-run `postgresai mon local-install --demo` to recreate them).
  215. >
  216. > Keep the old PostgreSQL 15 volume as a backup until you have verified PostgreSQL 17. The full
  217. > upgrade steps are below; see also the
  218. > [monitoring upgrade docs](https://postgres.ai/docs/monitoring/getting-started).
  219. To upgrade postgres_ai monitoring to a newer version:
  220. ### Step 1: Update the CLI
  221. ```bash
  222. npm install -g postgresai@0.15.0
  223. ```
  224. Or if you're using npx:
  225. ```bash
  226. npx postgresai@0.15.0 --version # verify the new version
  227. ```
  228. ### Step 2: Stop running services
  229. ```bash
  230. postgresai mon stop
  231. ```
  232. ### Step 3: Pull new Docker images and restart
  233. `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:
  234. ```bash
  235. # In your monitoring directory (typically ~/.postgres_ai/), edit .env and set
  236. # PGAI_TAG to the version you are upgrading to (it should match your new CLI
  237. # version), e.g. for the 0.15 line: PGAI_TAG=0.15.0
  238. postgresai mon update
  239. postgresai mon start
  240. ```
  241. This will:
  242. - Add any newly required `.env` keys for the newer stack (existing values, your secrets, and `instances.yml` targets are preserved)
  243. - 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.
  244. - Pull the Docker images for the `PGAI_TAG` you set
  245. - Start the services on the new images
  246. > **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.
  247. > **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).
  248. **Alternative: Manual upgrade**
  249. If you prefer more control:
  250. ```bash
  251. # Update the PGAI_TAG in .env to match your target stack version
  252. # Edit .env and set PGAI_TAG=0.15.0
  253. # Migrate .env to add any newly-required keys (e.g. VM_AUTH_* for 0.15+)
  254. postgresai mon update-config
  255. # Pull new images
  256. docker compose pull
  257. # Start services
  258. postgresai mon start
  259. ```
  260. ### Verify the upgrade
  261. After upgrading, verify services are running correctly:
  262. ```bash
  263. postgresai mon status
  264. postgresai mon health
  265. ```
  266. Check Grafana dashboards at http://localhost:3000 to confirm metrics are being collected.
  267. ## ☸️ Kubernetes deployment
  268. Deploy postgres_ai monitoring to Kubernetes using helm:
  269. ```bash
  270. # Install from the helm chart directory
  271. helm install postgres-ai-monitoring ./postgres_ai_helm
  272. # Or install from a release (replace <VERSION> with the desired release, e.g. 0.14.0)
  273. curl -LO https://gitlab.com/postgres-ai/postgresai/-/releases/helm-v<VERSION>/downloads/postgres-ai-monitoring-chart.tgz
  274. helm install postgres-ai-monitoring postgres-ai-monitoring-chart.tgz
  275. # Customize installation
  276. helm install postgres-ai-monitoring ./postgres_ai_helm -f custom-values.yaml
  277. ```
  278. ### Helm chart features
  279. - Complete monitoring stack for Kubernetes
  280. - pgwatch, VictoriaMetrics, and Grafana
  281. - Automated report generation via CronJobs
  282. - Node exporter and cAdvisor for system metrics
  283. - Configurable resource limits and persistence
  284. ### Helm chart documentation
  285. - [README](./postgres_ai_helm/README.md): Quick start and overview
  286. - [INSTALLATION_GUIDE](./postgres_ai_helm/INSTALLATION_GUIDE.md): Detailed installation instructions
  287. - [RELEASE](./postgres_ai_helm/RELEASE.md): Release process for maintainers
  288. ### Creating helm releases
  289. For maintainers creating new helm chart releases:
  290. ```bash
  291. cd postgres_ai_helm
  292. # Test the chart locally
  293. ./test-release.sh
  294. # Create a new release (e.g. 0.14.0)
  295. ./release.sh <VERSION>
  296. ```
  297. This automatically:
  298. - Updates Chart.yaml with the new version
  299. - Creates a git tag
  300. - Triggers GitLab CI/CD to package and publish the chart
  301. - Creates a GitLab release with the packaged chart
  302. ## 📋 Checkup reports
  303. postgres_ai monitoring generates automated health check reports based on [postgres-checkup](https://gitlab.com/postgres-ai/postgres-checkup). Each report has a unique check ID and title:
  304. ### A. General / Infrastructural
  305. | Check ID | Title |
  306. |----------|-------|
  307. | A001 | System information |
  308. | A002 | Version information |
  309. | A003 | Postgres settings |
  310. | A004 | Cluster information |
  311. | A005 | Extensions |
  312. | A006 | Postgres setting deviations |
  313. | A007 | Altered settings |
  314. | A008 | Disk usage and file system type |
  315. A005 reads every database that accepts connections, each over its own connection with
  316. the checkup's credentials (at most 100 databases, within 60 seconds): installed
  317. extensions with versions, index counts per access method and the extension of their
  318. operator classes, row security policies, extended statistics and deferrable unique or
  319. primary-key constraints. From the connected
  320. database it adds the logical slots with their plugin, the non-superuser roles with
  321. CREATE on each database, and the roles that can use replication. MD5 password hashes
  322. (pg_authid) and pg_hba.conf rules need a superuser: for any other role they are
  323. recorded as unknown, with the reason, never as none. A database that is not read is
  324. listed with the reason.
  325. A monitoring box writes A005 too: its reporter reads each monitored node once a day with
  326. the connection pgwatch uses (the node's `conn_str`), each query in its own read-only
  327. transaction. A node it cannot reach is listed with the reason and no database.
  328. ### D. Monitoring / Troubleshooting
  329. | Check ID | Title |
  330. |----------|-------|
  331. | D004 | pg_stat_statements and pg_stat_kcache settings |
  332. ### F. Autovacuum, Bloat
  333. | Check ID | Title |
  334. |----------|-------|
  335. | F001 | Autovacuum: current settings |
  336. | F004 | Autovacuum: heap bloat (estimated) |
  337. | F005 | Autovacuum: index bloat (estimated) |
  338. | F009 | Xmin horizon and blockers |
  339. F009 includes a 500-character query preview only when the checkup role can read all
  340. statistics. Disable preview capture with
  341. `PGOPTIONS='-c postgresai.capture_query_preview=off' postgresai checkup ...`.
  342. ### G. Performance / Connections / Memory-related settings
  343. | Check ID | Title |
  344. |----------|-------|
  345. | G001 | Memory-related settings |
  346. ### H. Index analysis
  347. | Check ID | Title |
  348. |----------|-------|
  349. | H001 | Invalid indexes |
  350. | H002 | Unused indexes |
  351. | H004 | Redundant indexes |
  352. ### K. SQL query analysis
  353. | Check ID | Title |
  354. |----------|-------|
  355. | K001 | Globally aggregated query metrics |
  356. | K003 | Top queries by total time (total_exec_time + total_plan_time) |
  357. | K004 | Top queries by temp bytes written |
  358. | K005 | Top queries by WAL generation |
  359. | K006 | Top queries by shared blocks read |
  360. | K007 | Top queries by shared blocks hit |
  361. ### M. SQL query analysis (top queries)
  362. | Check ID | Title |
  363. |----------|-------|
  364. | M001 | Top queries by mean execution time |
  365. | M002 | Top queries by rows (I/O intensity) |
  366. | M003 | Top queries by I/O time |
  367. ### N. Wait events analysis
  368. | Check ID | Title |
  369. |----------|-------|
  370. | N001 | Wait events grouped by type and query |
  371. ## 🌐 Access points
  372. After running local-install:
  373. - **🚀 MAIN: Grafana Dashboard**: http://localhost:3000 (login: `monitoring`; password is shown at the end of local-install)
  374. Technical URLs (for advanced users):
  375. - **Demo DB**: postgresql://postgres:postgres@localhost:55432/target_database
  376. - **Monitoring**: http://localhost:58080 (pgwatch)
  377. - **Metrics**: http://localhost:59090 (VictoriaMetrics)
  378. ## 📖 Help
  379. ```bash
  380. postgresai --help
  381. postgresai mon --help
  382. ```
  383. ## 🔑 PostgresAI access token
  384. Get your access token at [PostgresAI](https://postgres.ai) for automated report uploads and advanced analysis.
  385. ## 🛣️ Roadmap
  386. - Host stats for on-premise and managed Postgres setups
  387. - `pg_wait_sampling` and `pg_stat_kcache` extension support
  388. - Additional expert dashboards: autovacuum, checkpointer, lock analysis
  389. - Query plan analysis and automated recommendations
  390. - Enhanced AI integration capabilities
  391. ## 🧪 Testing
  392. Python-based report generation lives under `reporter/` and now ships with a pytest suite.
  393. ### Installation
  394. Install dev dependencies (includes `pytest`, `pytest-postgresql`, `psycopg`, etc.):
  395. ```bash
  396. python3 -m pip install -r reporter/requirements-dev.txt
  397. ```
  398. ### Running tests
  399. #### Unit tests only (fast, no external services required)
  400. Run only unit tests with mocked Prometheus interactions:
  401. ```bash
  402. pytest tests/reporter
  403. ```
  404. This automatically skips integration tests. Or run specific test files:
  405. ```bash
  406. pytest tests/reporter/test_generators_unit.py -v
  407. pytest tests/reporter/test_formatters.py -v
  408. ```
  409. #### All tests: unit + integration (requires PostgreSQL)
  410. Run the complete test suite (both unit and integration tests):
  411. ```bash
  412. pytest tests/reporter --run-integration
  413. ```
  414. 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.
  415. **Summary:**
  416. - `pytest tests/reporter` → **Unit tests only** (integration tests skipped)
  417. - `pytest tests/reporter --run-integration` → **Both unit and integration tests**
  418. ### Test coverage
  419. Generate coverage report:
  420. ```bash
  421. pytest tests/reporter -m unit --cov=reporter --cov-report=html
  422. ```
  423. View the coverage report by opening `htmlcov/index.html` in your browser.
  424. ## 🤝 Contributing
  425. We welcome contributions from Postgres experts! Please check our [GitLab repository](https://gitlab.com/postgres-ai/postgresai) for:
  426. - Code standards and review process
  427. - Dashboard design principles
  428. - Testing requirements for monitoring components
  429. ## 📄 License
  430. This project is licensed under the Apache License 2.0 - see the [LICENSE](LICENSE) file for details.
  431. ## 🏢 About PostgresAI
  432. postgres_ai monitoring is developed by [PostgresAI](https://postgres.ai), bringing years of Postgres expertise into automated monitoring and analysis tools. We provide enterprise consulting and advanced Postgres solutions for fast-growing companies.
  433. ## 📞 Support & community
  434. - 💬 [Get support](https://postgres.ai/contact)
  435. - 📺 [Postgres.TV (YouTube)](https://postgres.tv)
  436. - 🎙️ [Postgres FM Podcast](https://postgres.fm)
  437. - 🐛 [Report issues](https://gitlab.com/postgres-ai/postgresai/-/issues)
  438. - 📧 [Enterprise support](https://postgres.ai/consulting)
  439. ## Visualization standard
  440. Wait-event charts in monitoring and supporting materials follow the
  441. [PostgresAI color scheme](docs/COLOR_SCHEME.md).

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.