Skip to content
Back to search

MCP-PostgreSQL-Ops

🐘 Give AI assistants full PostgreSQL DBA superpowers β€” 30+ tools for performance analysis, bloat detection, lock/deadlock monitoring, autovacuum & schema inspection. No extensions required. PG 12-18.

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/call518/MCP-PostgreSQL-Ops 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/call518/MCP-PostgreSQL-Ops'

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/call518--mcp-postgresql-ops/badge.svg)](/listings/call518--mcp-postgresql-ops)

HTML

<a href="/listings/call518--mcp-postgresql-ops"><img src="/api/listings/call518--mcp-postgresql-ops/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

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

> **Note:** The `postgresql` container included in `docker-compose.yml` is intended for quickstart testing purposes only. You can connect to your own PostgreSQL instance by adjusting the environment variables as needed.

> **If you want to use your own PostgreSQL instance instead of the built-in test container:** > - Update the target PostgreSQL connection information in your `.env` file (see POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB). > - In `docker-compose.yml`, comment out (disable) the `postgres` and `postgres-init-extensions` containers to avoid starting the built-in test database.

Flow Diagram of Quickstart/Tutorial

1. Environment Setup

> **Note**: While superuser privileges provide access to all databases and system information, the MCP server also works with regular user permissions for basic monitoring tasks.

git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops

### Check and modify .env file
cp .env.example .env
vim .env
### No need to modify defaults, but if using your own PostgreSQL server, edit below:
POSTGRES_HOST=host.docker.internal
POSTGRES_PORT=15432  # External port for host access (mapped to internal 5432)
POSTGRES_USER=postgres
POSTGRES_PASSWORD=changeme!@34
POSTGRES_DB=ecommerce # Default connection DB. Superusers can access all DBs.

> **Note**: `PGDATA=/data/db` is preconfigured for the Percona PostgreSQL Docker image, which requires this specific path for proper write permissions.

2. Start Demo Containers

# Start all containers including built-in PostgreSQL for testing
docker-compose up -d

# Alternative: If using your own PostgreSQL instance
# Comment out postgres and postgres-init-extensions services in docker-compose.yml
# Then use the custom configuration:
# docker-compose -f docker-compose.custom-db.yml up -d

> **⏰ Wait for Environment Setup**: The initial environment setup takes a few minutes as containers are started in sequence: > 1. **PostgreSQL** container starts first with database initialization > 2. **PostgreSQL Extensions** container installs extensions and creates comprehensive test data (~83K records) > 3. **MCP Server** and **MCPO Proxy** containers start after PostgreSQL is ready > 4. **OpenWebUI** container starts last and may take additional time to load the web interface > > **πŸ’‘ Tip**: Wait 2-3 minutes after running `docker-compose up -d` before accessing OpenWebUI to ensure all services are fully initialized.

**πŸ” Check Container Status** (Optional):

# Monitor container startup progress
docker-compose logs -f

# Check if all containers are running
docker-compose ps

# Verify PostgreSQL is ready
docker-compose logs postgres | grep "ready to accept connections"

3. Access to OpenWebUI

http://localhost:3003/

- The list of MCP tool features provided by `swagger` can be found in the MCPO API Docs URL. - e.g: `http://localhost:8003/docs`

4. Registering the Tool in OpenWebUI

> πŸ“Œ **Note**: Web-UI configuration instructions are based on OpenWebUI **v0.6.22**. Menu locations and settings may differ in newer versions.

1. logging in to OpenWebUI with an admin account 1. go to "Settings" β†’ "Tools" from the top menu. 1. Enter the `postgresql-ops` Tool address (e.g., `http://localhost:8003/postgresql-ops`) to connect MCP Tools. 1. Setup Ollama or OpenAI.

5. Complete!

**Congratulations!** Your MCP PostgreSQL Operations server is now ready for use. You can start exploring your databases with natural language queries.

πŸš€ **Try These Example Queries:**

- **"Show me the current active connections"** - **"What are the slowest queries in the system?"** - **"Analyze table bloat across all databases"** - **"Show me database size information"** - **"What tables need VACUUM maintenance?"**

πŸ“– **Next Steps:**

- Browse the **[Example Queries section](#usage-examples)** below for more query examples

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.

README.md Β· README Β· 1,561 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. # MCP-PostgreSQL-Ops
  2. > Give Claude and other AI assistants read-only PostgreSQL observability for
  3. > slow queries, locks, bloat, VACUUM, indexes, replication, WAL, and capacity.
  4. [![MCPVault: claimed](https://mcpvault.io/badge/mcp-postgresql-ops.svg?theme=dark)](https://mcpvault.io/servers/mcp-postgresql-ops/health?utm_source=external_badge&utm_medium=referral&utm_campaign=mcp_health_report)
  5. [![MCP Toplist](https://mcptoplist.com/badge/smithery%2Fcall518%2Fmcp-postgresql-ops.svg)](https://mcptoplist.com/server/smithery%2Fcall518%2Fmcp-postgresql-ops)
  6. [![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)
  7. ![Python](https://img.shields.io/badge/Python-3776AB?style=flat&logo=python&logoColor=white)
  8. ![Docker Pulls](https://img.shields.io/docker/pulls/call518/mcp-server-postgresql-ops)
  9. ![PostgreSQL](https://img.shields.io/badge/PostgreSQL-12--18-4169E1?style=flat&logo=postgresql&logoColor=white)
  10. [![Deploy to PyPI with tag](https://github.com/call518/MCP-PostgreSQL-Ops/actions/workflows/pypi-publish.yml/badge.svg)](https://github.com/call518/MCP-PostgreSQL-Ops/actions/workflows/pypi-publish.yml)
  11. ![PyPI](https://img.shields.io/pypi/v/MCP-PostgreSQL-Ops?label=pypi%20package)
  12. ![PyPI - Downloads](https://img.shields.io/pypi/dm/MCP-PostgreSQL-Ops)
  13. ---
  14. ## What is MCP-PostgreSQL-Ops?
  15. MCP-PostgreSQL-Ops lets Claude, OpenWebUI, and other MCP clients inspect a
  16. PostgreSQL server through natural-language questions. It turns requests such as
  17. β€œWhich tables need VACUUM now?” into predefined, read-only SQL queries and
  18. returns the evidence needed to diagnose the database.
  19. - **Read-only tools:** no arbitrary SQL execution or data-changing operations.
  20. - **PostgreSQL 12–18:** version-specific views and columns are detected
  21. automatically.
  22. - **Useful without extensions:** core monitoring works immediately;
  23. `pg_stat_statements` and `pg_stat_monitor` add query analytics.
  24. - **Deployment choice:** install a Claude Desktop extension, run with `uvx`, or
  25. connect OpenWebUI directly to the HTTP MCP server.
  26. For an AI-generated code walkthrough, use
  27. [![Ask DeepWiki](https://img.shields.io/badge/Ask-DeepWiki-blue)](https://deepwiki.com/call518/MCP-PostgreSQL-Ops).
  28. ## What can you ask?
  29. ```text
  30. Show the current active connections and blocked sessions
  31. Which queries consume the most execution time?
  32. Find bloated tables and explain what needs attention first
  33. Which tables need VACUUM or autovacuum tuning?
  34. Show unused or inefficient indexes
  35. Check replication, WAL, checkpoints, and background processes
  36. Summarize database size, growth, and I/O activity
  37. Draw the relationships for the ecommerce schema
  38. ```
  39. **More requests to try:** see [Example Queries](#example-queries) for requests
  40. by area (performance, health checks, capacity and VACUUM, structure,
  41. troubleshooting, multi-step workflows) and per-tool examples.
  42. Core tools work with normal catalog access. Broader cross-database visibility
  43. depends on the PostgreSQL role; use a least-privilege monitoring account in
  44. production.
  45. **Example: slow-query evidence and recommendations**
  46. ![Slow-query analysis](img/screenshot-000.png)
  47. More examples are available in the
  48. [screenshots and usage wiki](https://github.com/call518/MCP-PostgreSQL-Ops/wiki).
  49. ## Quick Start
  50. Choose one path. If you already have PostgreSQL and Claude Desktop, Option A
  51. is the shortest. Option B provides a complete local demonstration with sample
  52. databases and OpenWebUI.
  53. <a id="claude-desktop-integration"></a>
  54. ### Option A: Claude Desktop extension (fastest)
  55. Requirements: Claude Desktop with extension support and a PostgreSQL server
  56. reachable from the computer running Claude Desktop.
  57. 1. Open the latest
  58. [GitHub Release](https://github.com/call518/MCP-PostgreSQL-Ops/releases)
  59. and download `mcp-postgresql-ops-<version>.mcpb`.
  60. 2. In Claude Desktop, open **Settings β†’ Extensions β†’ Advanced settings β†’
  61. Install Extension…** and select the downloaded file.
  62. 3. Enter the PostgreSQL host, port, user, password, and default database.
  63. 4. Ask Claude: `Show the current active PostgreSQL connections.`
  64. The password field is marked sensitive and is stored by Claude Desktop. The
  65. extension runs the server locally over `stdio`; it does not expose an HTTP
  66. port.
  67. ### Option B: OpenWebUI 0.13 with the included demo database
  68. Requirements: Git, Docker with the Compose plugin, and an OpenWebUI model
  69. provider whose model supports tool calling.
  70. **1. Configure**
  71. ```bash
  72. git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
  73. cd MCP-PostgreSQL-Ops
  74. cp .env.example .env
  75. cp mcp-config.json.http mcp-config.json.http.local
  76. chmod 600 mcp-config.json.http.local
  77. openssl rand -hex 32 # use the output as WEBUI_SECRET_KEY
  78. vi .env # set WEBUI_SECRET_KEY; adjust ports if needed
  79. ```
  80. `mcp-config.json.http.local` is ignored by Git and is mounted only into MCPO.
  81. If `REMOTE_AUTH_ENABLE=true`, replace `<REMOTE_SECRET_KEY>` in that local file
  82. with the same secret configured in `.env`. Leave the tracked
  83. `mcp-config.json.http` file as a credential-free template.
  84. The default `.env.example` credentials are only for the included disposable
  85. PostgreSQL demo. Do not reuse them for an existing or externally reachable
  86. database.
  87. **2. Start**
  88. ```bash
  89. docker compose up -d
  90. docker compose ps --all # wait for mcp-server to be healthy and init to exit 0
  91. ```
  92. This starts OpenWebUI at `http://localhost:3003`, the MCP server at
  93. `http://localhost:18003/mcp`, optional MCPO/OpenAPI compatibility at
  94. `http://localhost:8003`, and a disposable PostgreSQL demo environment. The
  95. demo includes ecommerce, analytics, inventory, and HR data plus intentional
  96. bloat and index patterns for realistic monitoring questions.
  97. **3. Connect OpenWebUI to the MCP server**
  98. 1. Open `http://localhost:3003`, create the administrator account, and add a
  99. model provider under **Settings β†’ Admin β†’ Connections**:
  100. - **OpenAI:** add an OpenAI API connection with URL
  101. `https://api.openai.com/v1`, enter your OpenAI API key (Auth **Bearer**),
  102. set **API Type** to **Chat Completions**, and save it.
  103. - **Ollama on the Docker host:** the Compose file already points OpenWebUI
  104. to `http://host.docker.internal:11434`; ensure Ollama is reachable from
  105. Docker and pull a tool-capable model.
  106. Confirm that the model appears in a new chat before continuing.
  107. 2. The Quick Start calls tools through Chat Completions with model reasoning
  108. turned off, using this default already present in `.env.example`:
  109. ```dotenv
  110. DEFAULT_MODEL_PARAMS={"reasoning_effort":"none"}
  111. ```
  112. This value requires **API Type: Chat Completions**. With **Responses**,
  113. OpenWebUI 0.13.0 still sends `reasoning_effort`, and every request fails with
  114. `Unsupported parameter: 'reasoning_effort'`. If the `open-webui-data` volume
  115. already exists, set the same value under **Settings β†’ Admin β†’ Models β†’ Model
  116. Defaults β†’ Model Parameters** because its saved database value takes
  117. precedence. To keep model reasoning through **Responses** instead, see
  118. [OpenAI reasoning models](#openai-reasoning-models).
  119. 3. Under **Settings β†’ Admin β†’ Tools β†’ Integrations β†’ External Tool Servers**,
  120. add a connection of type **MCP (Streamable HTTP)** with URL
  121. `http://mcp-server:8000/mcp` and authentication **None**. This matches the
  122. Quick Start default `REMOTE_AUTH_ENABLE=false`; enable and configure Bearer
  123. authentication before exposing the server outside the local Docker host.
  124. 4. Under **Settings β†’ Admin β†’ System β†’ Interface β†’ Chat**, enable **Tool
  125. Search** and **Defer Built-in Tools**, then set **Deferral Threshold** to `0`.
  126. This keeps the full PostgreSQL tool list within the model's tool limits.
  127. 5. In a new chat, enable the connection under **Integrations β†’ Tools** and ask:
  128. `Use mcp-postgresql-ops to show tables that need VACUUM.`
  129. If setup or tool calls fail, see [OpenWebUI Issues](#openwebui-issues) for
  130. existing-volume precedence, per-model and per-chat overrides, and provider
  131. compatibility notes.
  132. > **MCPO is optional.** OpenWebUI 0.13 connects to the MCP server directly and
  133. > does not require the MCPO proxy. To expose the tools as an OpenAPI/Swagger
  134. > service for a legacy client, use MCPO at `http://localhost:8003`. The Compose
  135. > file starts it so every project keeps the same deployment layout. To run
  136. > without it, use `docker compose up -d mcp-server open-webui`.
  137. To connect the Docker stack to an existing PostgreSQL server instead, update
  138. the `POSTGRES_*` values in `.env` and run:
  139. ```bash
  140. docker compose -f docker-compose.custom-db.yml up -d
  141. ```
  142. ## Why this project?
  143. - **Operational evidence, not arbitrary SQL:** every tool uses a predefined
  144. monitoring query and returns inspectable PostgreSQL statistics.
  145. - **Automatic version adaptation:** one server supports PostgreSQL 12 through
  146. 18, including newer I/O, wait-event, checkpointer, and WAL views.
  147. - **Maintenance intelligence:** bloat, VACUUM, autovacuum, indexes, locks,
  148. capacity, replication, and query performance are covered together.
  149. - **Managed-service friendly:** core tools work on RDS/Aurora and with regular
  150. monitoring roles; advanced visibility still follows provider permissions.
  151. - **Multiple clients:** Claude Desktop, OpenWebUI, any Streamable HTTP MCP
  152. client, and optional OpenAPI clients through MCPO.
  153. ## How it works
  154. ```text
  155. Claude Desktop ── stdio ───────────────┐
  156. β”‚
  157. OpenWebUI 0.13 ─ Streamable HTTP ──────┼─> MCP-PostgreSQL-Ops
  158. β”‚ β”‚
  159. OpenAPI client ─ optional MCPO proxy β”€β”€β”˜ └─> read-only SQL ─> PostgreSQL
  160. ```
  161. The MCP server is the required component. OpenWebUI can call it directly;
  162. MCPO is only an optional protocol adapter. The
  163. [DeepWiki architecture](https://deepwiki.com/call518/MCP-PostgreSQL-Ops)
  164. provides a code-level walkthrough.
  165. ## Installation options
  166. | Setup | Best for | Requirements |
  167. | --- | --- | --- |
  168. | MCPB extension | Fastest Claude Desktop setup | Claude Desktop |
  169. | `uvx --python 3.12 mcp-postgresql-ops` | Explicit local configuration | `uv` |
  170. | Docker Compose | OpenWebUI and HTTP MCP clients | Docker Compose |
  171. | Source checkout | Development and customization | Git, `uv` |
  172. All methods run the same server. Choose one; they are not cumulative.
  173. ## Features and compatibility
  174. ### Tool Compatibility Matrix
  175. > **Automatic Adaptation:** All tools work transparently across supported versions - no configuration needed!
  176. ### 🟒 **Extension-Independent Tools (No Extensions Required)**
  177. | Tool Name | Extensions Required | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | System Views/Tables Used |
  178. |-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|--------------------------|
  179. | `get_server_info` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `version()`, `pg_extension` |
  180. | `get_active_connections` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_activity` |
  181. | `get_postgresql_config` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_settings` |
  182. | `get_database_list` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_database` |
  183. | `get_table_list` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `information_schema.tables` |
  184. | `get_table_schema_info` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `information_schema.*`, `pg_indexes`, `pg_constraint` |
  185. | `get_database_schema_info` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_namespace`, `pg_class`, `pg_proc` |
  186. | `get_table_relationships` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_constraint`, `information_schema.*` |
  187. | `get_user_list` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_user`, `pg_roles` |
  188. | `get_index_usage_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_user_indexes` |
  189. | `get_database_size_info` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_database_size()` |
  190. | `get_table_size_info` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_total_relation_size()` |
  191. | `get_vacuum_analyze_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… **Enhanced** | `pg_stat_user_tables` |
  192. | `get_current_database_info` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_database`, `current_database()` |
  193. | `get_table_bloat_analysis` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_user_tables` |
  194. | `get_database_bloat_overview` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_user_tables` |
  195. | `get_autovacuum_status` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_user_tables` |
  196. | `get_autovacuum_activity` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_user_tables` |
  197. | `get_running_vacuum_operations` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_activity` |
  198. | `get_vacuum_effectiveness_analysis` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_user_tables` |
  199. | `get_lock_monitoring` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_locks`, `pg_stat_activity` |
  200. | `get_wal_status` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_current_wal_lsn()` |
  201. | `get_database_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… **Enhanced** | `pg_stat_database` |
  202. | `get_table_io_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_statio_user_tables` |
  203. | `get_index_io_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_statio_user_indexes` |
  204. | `get_database_conflicts_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | `pg_stat_database_conflicts` |
  205. ### πŸš€ **Version-Aware Tools (Auto-Adapting)**
  206. | Tool Name | Extensions Required | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | Special Features |
  207. |-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|------------------|
  208. | `get_io_stats` | ❌ None | βœ… Basic | βœ… Basic | βœ… Basic | βœ… Basic | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | PG16+: `pg_stat_io` support; PG18+: byte columns |
  209. | `get_bgwriter_stats` | ❌ None | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… **Special** | βœ… **Enhanced** | PG17: Separate checkpointer stats; PG18+: `num_done`, `slru_written` |
  210. | `get_replication_status` | ❌ None | βœ… Compatible | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | PG13+: `wal_status`, `safe_wal_size`; PG16+: enhanced WAL receiver; PG17+: `invalidation_reason`, `inactive_since` |
  211. | `get_all_tables_stats` | ❌ None | βœ… Compatible | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | PG13+: `n_ins_since_vacuum` tracking for vacuum maintenance optimization |
  212. | `get_user_functions_stats` | βš™οΈ Config Required | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | βœ… | Requires `track_functions=pl` |
  213. | `get_wait_events` | ❌ None | βœ… Fallback | βœ… Fallback | βœ… Fallback | βœ… Fallback | βœ… Fallback | βœ… **Native** | βœ… **Native** | PG17+: `pg_wait_events` catalog; PG12-16: fallback to `pg_stat_activity` current waits |
  214. | `get_wal_summarizer_status` | ❌ None | ❌ | ❌ | ❌ | ❌ | ❌ | βœ… | βœ… | PG17+: WAL summarizer monitoring for incremental backups |
  215. | `get_async_io_status` | ❌ None | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | βœ… | PG18+: `pg_aios` async I/O subsystem monitoring |
  216. | `get_per_backend_io_stats` | ❌ None | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | βœ… | PG18+: Per-backend I/O and WAL statistics |
  217. ### 🟑 **Extension-Dependent Tools (Extensions Required)**
  218. | Tool Name | Required Extension | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | Notes |
  219. |-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|-------|
  220. | `get_pg_stat_statements_top_queries` | `pg_stat_statements` | βœ… **Compatible** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | PG12: `total_time` β†’ `total_exec_time`; PG13+: native `total_exec_time`; PG17+: `stats_since` |
  221. | `get_pg_stat_monitor_recent_queries` | `pg_stat_monitor` | βœ… **Compatible** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | βœ… **Enhanced** | PG12: `total_time` β†’ `total_exec_time`; PG13+: native `total_exec_time` |
  222. ### πŸ†• **Version-Specific Features**
  223. #### PostgreSQL 17
  224. - **`pg_wait_events` view**: Native wait event catalog with descriptions (used by `get_wait_events`)
  225. - **WAL summarizer**: Monitoring for incremental backup support (used by `get_wal_summarizer_status`)
  226. - **Replication slot enhancements**: `invalidation_reason` and `inactive_since` columns (used by `get_replication_status`)
  227. - **`pg_stat_statements` `stats_since`**: Track when statistics were last reset (used by `get_pg_stat_statements_top_queries`)
  228. - **VACUUM progress**: Index vacuum tracking in progress views (future enhancement for `get_running_vacuum_operations`)
  229. #### PostgreSQL 18
  230. - **`pg_aios` view**: Async I/O subsystem monitoring (used by `get_async_io_status`)
  231. - **Per-backend I/O stats**: Individual backend I/O and WAL statistics (used by `get_per_backend_io_stats`)
  232. - **VACUUM/ANALYZE time columns**: `total_vacuum_time`, `total_autovacuum_time`, `total_analyze_time`, `total_autoanalyze_time` cumulative timing (used by `get_vacuum_analyze_stats`)
  233. - **`pg_stat_io` byte columns**: `read_bytes`, `write_bytes`, `extend_bytes` (used by `get_io_stats`)
  234. - **Parallel worker stats**: `parallel_workers_launched`, `parallel_workers_to_launch` (used by `get_database_stats`)
  235. - **Checkpointer enhancements**: `num_done`, `slru_written` columns (used by `get_bgwriter_stats`)
  236. ---
  237. ## Other local setups
  238. ### Run from PyPI with uvx
  239. ```bash
  240. # Local stdio transport
  241. uvx --python 3.12 mcp-postgresql-ops --type stdio
  242. # Streamable HTTP transport
  243. uvx --python 3.12 mcp-postgresql-ops \
  244. --type streamable-http \
  245. --host 127.0.0.1 \
  246. --port 8000
  247. ```
  248. ### Run from source
  249. ```bash
  250. git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
  251. cd MCP-PostgreSQL-Ops
  252. uv sync
  253. uv run mcp-postgresql-ops --help
  254. ```
  255. The package is also available through `pip install mcp-postgresql-ops`.
  256. ## Advanced configuration
  257. ### Claude Desktop Configuration
  258. (Optional) Run with Local Source:
  259. ```json
  260. {
  261. "mcpServers": {
  262. "mcp-postgresql-ops": {
  263. "command": "uv",
  264. "args": ["run", "python", "-m", "mcp_postgresql_ops"],
  265. "env": {
  266. "POSTGRES_HOST": "127.0.0.1",
  267. "POSTGRES_PORT": "15432",
  268. "POSTGRES_USER": "postgres",
  269. "POSTGRES_PASSWORD": "changeme",
  270. "POSTGRES_DB": "ecommerce"
  271. }
  272. }
  273. }
  274. }
  275. ```
  276. ### Run the MCP server standalone
  277. #### With PyPI and uvx
  278. ```bash
  279. # Stdio mode
  280. uvx --python 3.12 mcp-postgresql-ops \
  281. --type stdio
  282. # HTTP mode
  283. uvx --python 3.12 mcp-postgresql-ops \
  284. --type streamable-http \
  285. --host 127.0.0.1 \
  286. --port 8000 \
  287. --log-level DEBUG
  288. ```
  289. ### (Option) Configure Multiple PostgreSQL Instances
  290. ```json
  291. {
  292. "mcpServers": {
  293. "Postgresql-A": {
  294. "command": "uvx",
  295. "args": ["--python", "3.12", "mcp-postgresql-ops"],
  296. "env": {
  297. "POSTGRES_HOST": "a.foo.com",
  298. "POSTGRES_PORT": "5432",
  299. "POSTGRES_USER": "postgres",
  300. "POSTGRES_PASSWORD": "postgres",
  301. "POSTGRES_DB": "postgres"
  302. }
  303. },
  304. "Postgresql-B": {
  305. "command": "uvx",
  306. "args": ["--python", "3.12", "mcp-postgresql-ops"],
  307. "env": {
  308. "POSTGRES_HOST": "b.bar.com",
  309. "POSTGRES_PORT": "5432",
  310. "POSTGRES_USER": "postgres",
  311. "POSTGRES_PASSWORD": "postgres",
  312. "POSTGRES_DB": "postgres"
  313. }
  314. }
  315. }
  316. }
  317. ```
  318. #### /w Local Source
  319. ```bash
  320. # Method 1: Module execution (for development, requires PYTHONPATH)
  321. PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src
  322. python -m mcp_postgresql_ops \
  323. --type stdio
  324. # Method 2: Direct script (after uv installation in project directory)
  325. uv run mcp-postgresql-ops \
  326. --type stdio
  327. # Method 3: Installed package script (after pip/uv install)
  328. mcp-postgresql-ops \
  329. --type stdio
  330. # HTTP mode examples:
  331. # Development mode
  332. PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src
  333. python -m mcp_postgresql_ops \
  334. --type streamable-http \
  335. --host 127.0.0.1 \
  336. --port 8000 \
  337. --log-level DEBUG
  338. # Production mode (after installation)
  339. mcp-postgresql-ops \
  340. --type streamable-http \
  341. --host 127.0.0.1 \
  342. --port 8000 \
  343. --log-level DEBUG
  344. ```
  345. ---
  346. ## CLI Arguments
  347. - `--type`: Transport type (`stdio` or `streamable-http`) - Default: `stdio`
  348. - `--host`: Host address for HTTP transport - Default: `127.0.0.1`
  349. - `--port`: Port number for HTTP transport - Default: `8000`
  350. - `--auth-enable`: Enable Bearer token authentication for streamable-http mode - Default: `false`
  351. - `--secret-key`: Secret key for Bearer token authentication (required when auth enabled)
  352. - `--log-level`: Logging level (DEBUG, INFO, WARNING, ERROR, CRITICAL) - Default: `INFO`
  353. ---
  354. ## Environment Variables
  355. | Variable | Description | Default | Project Default |
  356. |----------|-------------|---------|-----------------|
  357. | `PYTHONPATH` | Python module search path (only needed for development mode) | - | `/app/src` |
  358. | `MCPO_CONFIG_FILE` | Ignored local MCPO configuration mounted by Docker Compose | `mcp-config.json.http.local` | `mcp-config.json.http.local` |
  359. | `MCP_LOG_LEVEL` | Server logging verbosity (DEBUG, INFO, WARNING, ERROR) | `INFO` | `INFO` |
  360. | `FASTMCP_TYPE` | MCP transport protocol (stdio for CLI, streamable-http for web) | `stdio` | `streamable-http` |
  361. | `FASTMCP_HOST` | HTTP server bind address (0.0.0.0 for all interfaces) | `127.0.0.1` | `0.0.0.0` |
  362. | `FASTMCP_PORT` | HTTP server port for MCP communication | `8000` | `8000` |
  363. | `REMOTE_AUTH_ENABLE` | Enable Bearer token authentication for streamable-http mode (Default: `false` if undefined/null/empty) | `false` | `false` |
  364. | `REMOTE_SECRET_KEY` | Secret key for Bearer token authentication (required when auth enabled) | - | empty |
  365. | `PGSQL_VERSION` | PostgreSQL major version for Docker image selection | `16` | `16` |
  366. | `PGDATA` | PostgreSQL data directory inside Docker container (**Do not modify**) | `/var/lib/postgresql/data` | `/data/db` |
  367. | `POSTGRES_HOST` | PostgreSQL server hostname or IP address | `127.0.0.1` | `host.docker.internal` |
  368. | `POSTGRES_PORT` | PostgreSQL server port number | `5432` | `15432` |
  369. | `POSTGRES_USER` | PostgreSQL connection username (needs read permissions) | `postgres` | `postgres` |
  370. | `POSTGRES_PASSWORD` | PostgreSQL user password (supports special characters) | `changeme` | `changeme` |
  371. | `POSTGRES_DB` | Default database name for connections | `testdb` | `ecommerce` |
  372. | `POSTGRES_MAX_CONNECTIONS` | PostgreSQL max_connections configuration parameter | `200` | `200` |
  373. | `DOCKER_BIND_ADDRESS` | Host address for published Docker ports | `127.0.0.1` | `127.0.0.1` |
  374. | `DOCKER_EXTERNAL_PORT_OPENWEBUI` | Host port mapping for Open WebUI container | `8080` | `3003` |
  375. | `DOCKER_EXTERNAL_PORT_MCP_SERVER` | Host port mapping for MCP server container | `8080` | `18003` |
  376. | `DOCKER_EXTERNAL_PORT_MCPO_PROXY` | Host port mapping for MCPO proxy container | `8000` | `8003` |
  377. | `DOCKER_INTERNAL_PORT_POSTGRESQL` | PostgreSQL container internal port | `5432` | `5432` |
  378. **Note**: `POSTGRES_DB` serves as the default target database for operations when no specific database is specified. In Docker environments, if set to a non-default name, this database will be automatically created during initial PostgreSQL startup.
  379. **Port Configuration**: The built-in PostgreSQL container uses port mapping `15432:5432` where:
  380. - `POSTGRES_PORT=15432`: External port for host access and MCP server connections
  381. - `DOCKER_INTERNAL_PORT_POSTGRESQL=5432`: Internal container port (PostgreSQL default)
  382. - When using external PostgreSQL servers, set `POSTGRES_PORT` to match your server's actual port
  383. ---
  384. ## Prerequisites
  385. ### Required PostgreSQL Extensions
  386. > For more details, see the [## Tool Compatibility Matrix](#tool-compatibility-matrix)
  387. **Note**: Most MCP tools work without any PostgreSQL extensions. section below. Some advanced performance analysis tools require the following extensions:
  388. ```sql
  389. -- Query performance statistics (required only for get_pg_stat_statements_top_queries)
  390. CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  391. -- Advanced monitoring (optional, used by get_pg_stat_monitor_recent_queries)
  392. CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;
  393. ```
  394. **Quick Setup**: For new PostgreSQL installations, add to `postgresql.conf`:
  395. ```
  396. shared_preload_libraries = 'pg_stat_statements'
  397. ```
  398. Then restart PostgreSQL and run the CREATE EXTENSION commands above.
  399. - `pg_stat_statements` is required only for slow query analysis tools.
  400. - `pg_stat_monitor` is optional and used for real-time query monitoring.
  401. - All other tools work without these extensions.
  402. ### Minimum Requirements
  403. - PostgreSQL 12+ (tested with PostgreSQL 17 and 18)
  404. - Python 3.12
  405. - Network access to PostgreSQL server
  406. - Read permissions on system catalogs
  407. ### Required PostgreSQL Configuration
  408. **⚠️ Statistics Collection Settings**:
  409. Some MCP tools require specific PostgreSQL configuration parameters to collect statistics. Choose one of the following configuration methods:
  410. **Tools affected by these settings**:
  411. - **get_user_functions_stats**: Requires `track_functions = pl` or `track_functions = all`
  412. - **get_table_io_stats** & **get_index_io_stats**: More accurate timing with `track_io_timing = on`
  413. - **get_database_stats**: Enhanced I/O timing with `track_io_timing = on`
  414. **Verification**:
  415. After applying any method, verify the settings:
  416. ```sql
  417. SELECT name, setting, context FROM pg_settings WHERE name IN ('track_activities', 'track_counts', 'track_io_timing', 'track_functions') ORDER BY name;
  418. name | setting | context
  419. ------------------+---------+-----------
  420. track_activities | on | superuser
  421. track_counts | on | superuser
  422. track_functions | pl | superuser
  423. track_io_timing | on | superuser
  424. (4 rows)
  425. ```
  426. #### Method 1: postgresql.conf (Recommended for Self-Managed PostgreSQL)
  427. Add the following to your `postgresql.conf`:
  428. ```ini
  429. # Basic statistics collection (usually enabled by default)
  430. track_activities = on
  431. track_counts = on
  432. # Required for function statistics tools
  433. track_functions = pl # Enables PL/pgSQL function statistics collection
  434. # Optional but recommended for accurate I/O timing
  435. track_io_timing = on # Enables I/O timing statistics collection
  436. ```
  437. Then restart PostgreSQL server.
  438. #### Method 2: PostgreSQL Startup Parameters
  439. For Docker or command-line PostgreSQL startup:
  440. ```bash
  441. # Docker example
  442. docker run -d \
  443. -e POSTGRES_PASSWORD=mypassword \
  444. postgres:17 \
  445. -c track_activities=on \
  446. -c track_counts=on \
  447. -c track_functions=pl \
  448. -c track_io_timing=on
  449. # Direct postgres command
  450. postgres -D /data \
  451. -c track_activities=on \
  452. -c track_counts=on \
  453. -c track_functions=pl \
  454. -c track_io_timing=on
  455. ```
  456. #### Method 3: Dynamic Configuration (AWS RDS, Azure, GCP, Managed Services)
  457. For managed PostgreSQL services where you cannot modify `postgresql.conf`, use SQL commands to change settings dynamically:
  458. ```sql
  459. -- Enable basic statistics collection (usually enabled by default)
  460. ALTER SYSTEM SET track_activities = 'on';
  461. ALTER SYSTEM SET track_counts = 'on';
  462. -- Enable function statistics collection (requires superuser privileges)
  463. ALTER SYSTEM SET track_functions = 'pl';
  464. -- Enable I/O timing statistics (optional but recommended)
  465. ALTER SYSTEM SET track_io_timing = 'on';
  466. -- Reload configuration without restart (run separately)
  467. SELECT pg_reload_conf();
  468. ```
  469. **Alternative for session-level testing**:
  470. ```sql
  471. -- Set for current session only (temporary)
  472. SET track_activities = 'on';
  473. SET track_counts = 'on';
  474. SET track_functions = 'pl';
  475. SET track_io_timing = 'on';
  476. ```
  477. **Note**: When using command-line tools, run each SQL statement separately to avoid transaction block errors.
  478. ---
  479. ## RDS/Aurora Compatibility
  480. - This server is read-only and works with regular roles on RDS/Aurora. For advanced analysis enable pg_stat_statements; pg_stat_monitor is not available on managed engines.
  481. - On RDS/Aurora, prefer DB Parameter Group over ALTER SYSTEM for persistent settings.
  482. ```sql
  483. -- Verify preload setting
  484. SHOW shared_preload_libraries;
  485. -- Enable extension in target DB
  486. CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  487. -- Recommended visibility for monitoring
  488. GRANT pg_read_all_stats TO <app_user>;
  489. ```
  490. ---
  491. ## Example Queries
  492. Requests are grouped first by task area, then by tool. Each tool runs read-only
  493. queries; results depend on the PostgreSQL version, installed extensions, and
  494. the connected role's privileges.
  495. ### 🧭 Requests by Area
  496. **πŸ“ˆ Database Performance Analysis**
  497. - "Show database-wide performance statistics"
  498. - "Analyze transaction commit ratios and I/O patterns"
  499. - "Check buffer cache hit ratios for all databases"
  500. - "Monitor temporary file usage and deadlock counts"
  501. **πŸ”§ Background Writer & Checkpoints**
  502. - "Analyze checkpoint performance and timing"
  503. - "Show background writer efficiency statistics"
  504. - "Check buffer allocation and writing patterns"
  505. - "Monitor checkpoint scheduling vs requested ratios"
  506. **πŸ” Server Health Check**
  507. - "Check PostgreSQL server status."
  508. - "What database am I connected to?"
  509. - "Show current database connection details."
  510. - "Verify if extensions are installed."
  511. - "Show current active connection count."
  512. - "Display PostgreSQL version and configuration."
  513. **πŸ“Š Performance Analysis**
  514. - "Show top 20 slowest queries."
  515. - "Find unused indexes."
  516. - "Analyze recent query activity."
  517. - "Identify performance bottlenecks."
  518. - "Show cache hit ratios for queries."
  519. **πŸ’Ύ Capacity Management**
  520. - "Check database sizes."
  521. - "Find largest tables."
  522. - "Show tables that need VACUUM."
  523. - "Analyze disk usage by database."
  524. - "Display table and index sizes."
  525. - "Analyze table bloat and dead tuples."
  526. - "Show database-wide bloat summary."
  527. - "Check autovacuum status and trigger conditions."
  528. - "Monitor recent autovacuum activity patterns."
  529. - "Show currently running VACUUM operations."
  530. - "Analyze VACUUM effectiveness and maintenance patterns."
  531. **πŸ—„οΈ Structure Analysis**
  532. - "List all databases with owners."
  533. - "Show tables in public schema."
  534. - "Display user accounts and permissions."
  535. - "Explore database structure."
  536. **πŸ“ˆ Advanced Monitoring**
  537. - "Monitor active sessions and queries."
  538. - "Analyze index usage efficiency."
  539. - "Check VACUUM and ANALYZE history."
  540. - "Review PostgreSQL configuration settings."
  541. - "Find memory-related configuration parameters."
  542. - "Show all logging configuration options."
  543. - "Search for connection-related settings."
  544. - "Identify connection patterns."
  545. ### 🩺 Troubleshooting Requests
  546. **Connection Issues**
  547. - "Check PostgreSQL server connectivity."
  548. - "Verify connection parameters."
  549. - "Test database access permissions."
  550. **Extension Problems**
  551. - "Check if pg_stat_statements is installed."
  552. - "Verify pg_stat_monitor availability."
  553. - "Show installed extensions status."
  554. **Performance Issues**
  555. - "Analyze slow query performance."
  556. - "Check database load and connections."
  557. - "Review index usage efficiency."
  558. - "Monitor recent query patterns."
  559. ### πŸ” Multi-Step Workflows
  560. Send these requests in order to run a routine review.
  561. **Regular Health Checks**
  562. 1. "Check server status and active connections."
  563. 2. "Show top 10 slowest queries from last hour."
  564. 3. "Verify all databases are accessible."
  565. 4. "Check if any tables need maintenance."
  566. **Capacity Planning**
  567. 1. "Analyze database sizes and growth trends."
  568. 2. "Identify largest tables and indexes."
  569. 3. "Review disk usage by schema."
  570. 4. "Plan storage capacity requirements."
  571. **Performance Optimization**
  572. 1. "Find queries consuming most resources."
  573. 2. "Identify unused or inefficient indexes."
  574. 3. "Analyze cache hit ratios."
  575. 4. "Monitor query execution patterns."
  576. ### 🟒 Extension-Independent Tools (Always Available)
  577. - **get_server_info**
  578. - "Show PostgreSQL server version and extension status."
  579. - "Check if pg_stat_statements is installed."
  580. - "Check server compatibility."
  581. - "Show server version and compatibility features."
  582. - "Check what MCP tools are available on this PostgreSQL version."
  583. - "Displays feature availability matrix and upgrade recommendations."
  584. - **get_active_connections**
  585. - "Show all active connections."
  586. - "List current sessions with database and user."
  587. - "Monitor current sessions and their running queries."
  588. - **get_postgresql_config**
  589. - "Show all PostgreSQL configuration parameters."
  590. - "Find all memory-related configuration settings."
  591. - "Show PostgreSQL configuration parameter for shared_buffers."
  592. - "Search for connection-related settings."
  593. - "Find logging configuration."
  594. - "Check specific parameter."
  595. - **get_database_list**
  596. - "List all databases and their sizes."
  597. - "Show database list with owner information."
  598. - "List all databases with their owners and sizes."
  599. - "Show database encoding and connection limits."
  600. - **get_table_list**
  601. - "List all tables in the ecommerce database."
  602. - "Show table sizes in the public schema."
  603. - "List all tables in the default database."
  604. - "Show tables in specific database."
  605. - **get_table_schema_info**
  606. - "Show detailed schema information for the customers table in ecommerce database."
  607. - "Get column details and constraints for products table in ecommerce database."
  608. - "Analyze table structure with indexes and foreign keys for orders table in sales schema of ecommerce database."
  609. - "Show schema overview for all tables in public schema of inventory database."
  610. - "Get complete table structure including constraints and indexes for employees table in hr_system database."
  611. - "Display column information with data types and constraints for inventory_items table in inventory database."
  612. - πŸ“‹ **Features**: Column types, constraints, indexes, foreign keys, table metadata
  613. - ⚠️ **Required**: `database_name` parameter must be specified
  614. - πŸ”„ **Output change (3.5.0+)**: Referenced tables are schema-qualified (`b.p(id)` instead of `p(id)`). A column in several foreign keys is shown in one COLUMN row, with its targets comma-separated in matching order (`a.p, b.p` / `id, id`). Per-partition FK clones that PostgreSQL creates for a partitioned referenced table are no longer listed.
  615. - **get_database_schema_info**
  616. - "Show all schemas in ecommerce database with their contents."
  617. - "Get detailed information about sales schema in ecommerce database."
  618. - "Analyze schema structure and permissions for inventory database."
  619. - "Show schema overview with table counts and sizes for hr_system database."
  620. - "Display schema owners and access privileges for all schemas in ecommerce database."
  621. - "Get comprehensive schema statistics including object counts and sizes."
  622. - πŸ“‹ **Features**: Schema owners, permissions, object counts, sizes, contents
  623. - ⚠️ **Required**: `database_name` parameter must be specified
  624. - **get_table_relationships**
  625. - "Show all relationships for customers table in ecommerce database."
  626. - "Analyze foreign key relationships for orders table in sales schema of ecommerce database."
  627. - "Get database-wide relationship overview for ecommerce database."
  628. - "Find all tables that reference products table in ecommerce database."
  629. - "Show cross-schema relationships in inventory database."
  630. - "Display all foreign key dependencies in hr_system database."
  631. - "Analyze table relationships including inbound and outbound foreign keys."
  632. - "Get complete relationship mapping for specific table with constraint details."
  633. - πŸ“‹ **Features**: Foreign key relationships (inbound/outbound), cross-schema dependencies, constraint details
  634. - ⚠️ **Required**: `database_name` parameter must be specified
  635. - πŸ’‘ **Usage**: Leave `table_name` empty for database-wide relationship analysis
  636. - πŸ”„ **Output change (3.5.0+)**: Foreign keys are read from `pg_constraint`, so each FK column pair is listed once. Same-named FKs on other tables or schemas are no longer mixed in. Statistics and "Referenced N times" now count FK constraints instead of joined rows, and per-partition FK clones are not counted.
  637. - **get_user_list**
  638. - "List all database users and their roles."
  639. - "Show user permissions for a specific database."
  640. - "Display all database users with their permissions."
  641. - "Show superuser status and account limitations for all users."
  642. - **get_index_usage_stats**
  643. - "Analyze index usage efficiency."
  644. - "Find unused indexes in the current database."
  645. - "Analyze index usage in default database."
  646. - "Check index efficiency in specific database."
  647. - "Identify unused indexes with zero scans (look for 'Never used' entries)."
  648. - **get_database_size_info**
  649. - "Show database capacity analysis."
  650. - "Find the largest databases by size."
  651. - "Show disk usage for all databases sorted by size."
  652. - "Calculate total storage consumption across all databases."
  653. - **get_table_size_info**
  654. - "Show table and index size analysis."
  655. - "Find largest tables in a specific schema."
  656. - "Analyze table sizes in public schema."
  657. - "Check table sizes in specific database schema."
  658. - **get_vacuum_analyze_stats**
  659. - "Show recent VACUUM and ANALYZE operations."
  660. - "List tables needing VACUUM."
  661. - "Review VACUUM and ANALYZE history for all tables."
  662. - "Check maintenance status in specific database."
  663. - "Find tables needing maintenance (check last_vacuum dates)."
  664. - **get_current_database_info**
  665. - "What database am I connected to?"
  666. - "Show current database information and connection details."
  667. - "Display database encoding, collation, and size information."
  668. - "Show current database information and properties."
  669. - "Display database encoding, collation, and connection limits."
  670. - "Get current database size and configuration details."
  671. - "Show connection context and database properties."
  672. - πŸ“‹ **Features**: Database name, encoding, collation, size, connection limits
  673. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  674. - **get_table_bloat_analysis**
  675. - "Analyze table bloat in the current database."
  676. - "Show dead tuple ratios and bloat estimates for user_logs table pattern."
  677. - "Find tables with high bloat that need VACUUM maintenance."
  678. - "Analyze bloat in specific schema with minimum 100 dead tuples."
  679. - "Analyze table bloat in the public schema."
  680. - "Show tables with high dead tuple ratios in ecommerce database."
  681. - "Find tables requiring VACUUM maintenance."
  682. - "Check bloat for tables with more than 5000 dead tuples."
  683. - "Identify bloated tables in inventory database public schema."
  684. - "Show estimated bloat sizes and VACUUM recommendations."
  685. - πŸ“‹ **Features**: Dead tuple ratios, bloat size estimates, VACUUM recommendations, pattern filtering
  686. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  687. - πŸ’‘ **Usage**: Extension-Independent approach using pg_stat_user_tables
  688. - **get_database_bloat_overview**
  689. - "Show database-wide bloat summary by schema."
  690. - "Get high-level view of storage efficiency across all schemas."
  691. - "Identify schemas requiring maintenance attention."
  692. - "Get bloat overview for inventory database."
  693. - "Identify schemas with highest bloat ratios."
  694. - "Database maintenance planning with bloat statistics."
  695. - "Compare bloat across all schemas in ecommerce database."
  696. - "Show total estimated bloat and schema sizes."
  697. - πŸ“‹ **Features**: Schema-level aggregation, total bloat estimates, maintenance status
  698. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  699. - **get_autovacuum_status**
  700. - "Check autovacuum configuration and trigger conditions."
  701. - "Show tables needing immediate autovacuum attention."
  702. - "Analyze autovacuum threshold percentages for public schema."
  703. - "Find tables approaching autovacuum trigger points."
  704. - "Analyze autovacuum threshold percentages for all tables."
  705. - "Find tables approaching autovacuum trigger points in public schema."
  706. - "Check autovacuum status for log tables pattern."
  707. - "Show autovacuum urgency analysis for ecommerce database."
  708. - "Monitor tables with dead tuple ratios near autovacuum thresholds."
  709. - πŸ“‹ **Features**: Trigger threshold analysis, urgency classification, configuration status
  710. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  711. - πŸ’‘ **Usage**: Extension-Independent autovacuum monitoring using pg_stat_user_tables
  712. - **get_autovacuum_activity**
  713. - "Show autovacuum activity patterns for the last 48 hours."
  714. - "Monitor autovacuum execution frequency and timing."
  715. - "Find tables with irregular autovacuum patterns."
  716. - "Analyze recent autovacuum and autoanalyze history."
  717. - "Check autovacuum activity in public schema for last 7 days."
  718. - "Show tables with no recent autovacuum activity."
  719. - "Monitor autovacuum workload distribution across schemas."
  720. - "Identify tables with very high autovacuum frequency."
  721. - πŸ“‹ **Features**: Activity patterns, execution frequency, timing analysis
  722. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  723. - πŸ’‘ **Usage**: Historical autovacuum pattern analysis
  724. - **get_running_vacuum_operations**
  725. - "Show currently running VACUUM and ANALYZE operations."
  726. - "Monitor active maintenance operations and their progress."
  727. - "Check if any VACUUM operations are blocking queries."
  728. - "Find long-running maintenance operations."
  729. - "Find long-running maintenance operations in ecommerce database."
  730. - "Show real-time status of all running maintenance operations."
  731. - "Monitor VACUUM FULL operations and their impact level."
  732. - πŸ“‹ **Features**: Real-time operation status, elapsed time, impact level, process details
  733. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  734. - πŸ’‘ **Usage**: Real-time maintenance monitoring using pg_stat_activity
  735. - **get_vacuum_effectiveness_analysis**
  736. - "Analyze VACUUM effectiveness and maintenance patterns."
  737. - "Compare manual VACUUM vs autovacuum efficiency."
  738. - "Find tables with suboptimal maintenance patterns."
  739. - "Check VACUUM frequency vs table activity ratios."
  740. - "Find tables with suboptimal maintenance patterns in public schema."
  741. - "Identify tables with poor maintenance effectiveness."
  742. - "Show maintenance pattern analysis for all schemas."
  743. - "Find tables that need better VACUUM scheduling."
  744. - πŸ“‹ **Features**: Maintenance pattern analysis, effectiveness assessment, DML-to-VACUUM ratios
  745. - πŸ”§ **PostgreSQL 12-18**: Fully compatible, no extensions required
  746. - πŸ’‘ **Usage**: Strategic VACUUM analysis using existing statistics
  747. - **get_lock_monitoring**
  748. - "Show all current locks and blocked sessions."
  749. - "Show only blocked sessions with granted=false filter."
  750. - "Monitor locks by specific user with username filter."
  751. - "Check exclusive locks with mode filter."
  752. - **get_wal_status**
  753. - "Show WAL status and archiving information."
  754. - "Monitor WAL generation and current LSN position."
  755. - **get_replication_status**
  756. - "Check replication connections and lag status."
  757. - "Monitor replication slots and WAL receiver status."
  758. - "Show replication status (version-compatible for PG12-18)."
  759. - "Check if replication is active or standby servers are connected."
  760. - **get_database_stats**
  761. - "Show comprehensive database performance metrics."
  762. - "Analyze transaction commit ratios and I/O statistics."
  763. - "Monitor buffer cache hit ratios and temporary file usage."
  764. - "Show database-wide performance statistics."
  765. - "Analyze transaction commit ratios across all databases."
  766. - "Check buffer cache hit ratios and I/O statistics."
  767. - "Monitor temporary file usage and deadlock counts."
  768. - **get_bgwriter_stats**
  769. - "Analyze checkpoint performance and timing."
  770. - "Show me checkpoint performance."
  771. - "Show background writer efficiency statistics."
  772. - "Monitor buffer allocation and fsync patterns."
  773. - "Show background writer efficiency and buffer statistics."
  774. - "Monitor checkpoint scheduling patterns."
  775. - "Check buffer allocation and fsync performance."
  776. - "Monitor checkpoint scheduling vs requested ratios."
  777. - **get_user_functions_stats**
  778. - "Analyze user-defined function performance."
  779. - "Show function call counts and execution times."
  780. - "Identify performance bottlenecks in custom functions."
  781. - "Identify slow or frequently called functions."
  782. - "Monitor function performance bottlenecks."
  783. - ⚠️ **Requires**: `track_functions = pl` in postgresql.conf
  784. - **get_table_io_stats**
  785. - "Analyze table I/O performance and buffer hit ratios."
  786. - "Identify tables with poor buffer cache performance."
  787. - "Monitor TOAST table I/O statistics."
  788. - "Show disk reads vs buffer cache hits for tables in public schema."
  789. - πŸ’‘ **Enhanced with**: `track_io_timing = on` for accurate timing
  790. - **get_index_io_stats**
  791. - "Show index I/O performance and buffer efficiency."
  792. - "Identify indexes causing excessive disk I/O."
  793. - "Monitor index cache-friendliness patterns."
  794. - "Analyze index buffer hit ratios in specific schema."
  795. - πŸ’‘ **Enhanced with**: `track_io_timing = on` for accurate timing
  796. - **get_database_conflicts_stats**
  797. - "Check replication conflicts on standby servers."
  798. - "Analyze conflict types and resolution statistics."
  799. - "Monitor standby server query cancellation patterns."
  800. - "Monitor WAL generation and current LSN position."
  801. - "Check replication conflicts on standby server."
  802. - "Monitor replication slots and WAL receiver status."
  803. - **get_replication_status**
  804. - "Check replication connections and lag status."
  805. - "Monitor replication slots and WAL receiver status."
  806. ### πŸš€ Version-Aware Tools (Auto-Adapting)
  807. - **get_io_stats** (New!)
  808. - "Show comprehensive I/O statistics." (PostgreSQL 16+ provides detailed breakdown)
  809. - "Analyze I/O statistics."
  810. - "Analyze buffer cache efficiency and I/O timing."
  811. - "Monitor I/O patterns by backend type and context."
  812. - πŸ“ˆ **PG16+**: Full pg_stat_io with timing, backend types, and contexts
  813. - πŸ“Š **PG12-15**: Basic pg_statio_* fallback with buffer hit ratios
  814. - πŸ“ˆ **PG18+**: Additional byte-level I/O columns (`read_bytes`, `write_bytes`, `extend_bytes`) for precise I/O size tracking
  815. - **get_bgwriter_stats** (Enhanced!)
  816. - "Show background writer and checkpoint performance."
  817. - πŸ“ˆ **PG17+**: Separate checkpointer and bgwriter statistics via `pg_stat_checkpointer`
  818. - πŸ“Š **PG12-16**: Combined bgwriter stats (includes checkpointer data)
  819. - πŸ“ˆ **PG18+**: Additional completed_checkpoints and slru_buffers_written columns
  820. - **get_server_info** (Enhanced!)
  821. - "Show server version and compatibility features."
  822. - "Check server compatibility."
  823. - "Check what MCP tools are available on this PostgreSQL version."
  824. - "Displays feature availability matrix and upgrade recommendations."
  825. - **get_all_tables_stats** (Enhanced!)
  826. - "Show comprehensive statistics for all tables." (version-compatible for PG12-18)
  827. - "Include system tables with include_system=true parameter."
  828. - "Analyze table access patterns and maintenance needs."
  829. - "Show comprehensive statistics for all user tables (version-compatible for PG12-18)."
  830. - "Include system tables in statistics analysis with include_system=true."
  831. - "Monitor dead tuple ratios and table activity."
  832. - "Show insertions since vacuum statistics (PG13+ only)."
  833. - πŸ“ˆ **PG13+**: Tracks insertions since vacuum (`n_ins_since_vacuum`) for optimal maintenance scheduling
  834. - πŸ“Š **PG12**: Compatible mode with NULL for unsupported columns
  835. - πŸ“ˆ **PG18+**: VACUUM time columns (`total_vacuum_time`, `total_autovacuum_time`, `total_analyze_time`, `total_autoanalyze_time`) for maintenance duration tracking
  836. - **get_wait_events** (New!)
  837. - "Show wait event types and descriptions."
  838. - "What wait events are available on this PostgreSQL version?"
  839. - "List available wait event types and their descriptions."
  840. - "Show wait events filtered by Lock type."
  841. - πŸ“ˆ **PG17+**: Native `pg_wait_events` catalog with full descriptions
  842. - πŸ“Š **PG12-16**: Fallback to `pg_stat_activity` current waits grouped by type
  843. - **get_wal_summarizer_status** (New! PG 17+)
  844. - "Show WAL summarizer status for incremental backups."
  845. - "Monitor WAL summarization progress."
  846. - "Monitor WAL summarizer status for incremental backups."
  847. - "Check WAL summarizer progress and configuration."
  848. - πŸ“ˆ **PG17+**: WAL summarizer monitoring via `pg_get_wal_summarizer_state()`
  849. - ❌ **PG12-16**: Not available (returns informational message)
  850. - **get_async_io_status** (New! PG 18+)
  851. - "Show async I/O subsystem status."
  852. - "Monitor pg_aios for async I/O operations."
  853. - "Monitor async I/O subsystem status."
  854. - "Show pg_aios view for I/O operation monitoring."
  855. - πŸ“ˆ **PG18+**: `pg_aios` view for async I/O monitoring
  856. - ❌ **PG12-17**: Not available (returns informational message)
  857. - **get_per_backend_io_stats** (New! PG 18+)
  858. - "Show per-backend I/O and WAL statistics."
  859. - "Analyze I/O patterns by individual backend process."
  860. - πŸ“ˆ **PG18+**: Per-backend I/O stats with WAL statistics
  861. - ❌ **PG12-17**: Not available (returns informational message)
  862. ### 🟑 Extension-Dependent Tools
  863. - **get_pg_stat_statements_top_queries** (Requires `pg_stat_statements`)
  864. - "Show top 10 slowest queries."
  865. - "Analyze slow queries in the inventory database."
  866. - "Analyze slow queries in the ecommerce database."
  867. - "Show top 20 slowest queries."
  868. - "Find queries consuming most resources."
  869. - "Monitor query execution patterns."
  870. - "Show cache hit ratios for queries."
  871. - πŸ“ˆ **Version-Compatible**: PG12 uses `total_time` β†’ `total_exec_time` mapping; PG13+ uses native columns
  872. - πŸ’‘ **Cross-Version**: Automatically adapts query structure for PostgreSQL 12-18 compatibility
  873. - πŸ“ˆ **PG17+ / PG18+**: Adds `stats_since` (PG17+) and `parallel_workers` (PG18+)
  874. - **get_pg_stat_monitor_recent_queries** (Optional, uses `pg_stat_monitor`)
  875. - "Show recent queries in real time."
  876. - "Monitor query activity for the last 5 minutes."
  877. - "Monitor recent 15 queries with detailed stats."
  878. - "Track recent queries in ecommerce database."
  879. - πŸ“ˆ **Version-Compatible**: PG12 uses `total_time` β†’ `total_exec_time` mapping; PG13+ uses native columns
  880. - πŸ’‘ **Cross-Version**: Automatically adapts query structure for PostgreSQL 12-18 compatibility
  881. ### πŸ”§ Advanced Usage Examples
  882. **Multi-Database Analysis**
  883. - "Compare table sizes across databases."
  884. - "Monitor performance across multiple databases using database_name parameter."
  885. - "Analyze slow queries in specific database."
  886. - "Check index efficiency in specific database."
  887. **Schema Analysis**
  888. - "Show detailed schema for products table with all constraints in ecommerce database."
  889. - "Analyze table relationships and foreign key dependencies for sales.orders table in ecommerce database."
  890. - "Get complete column information with data types for inventory_items table in inventory database."
  891. - "Show all indexes and constraints for employees table in hr_system database."
  892. - "Review schema structure for all tables in sales schema of ecommerce database."
  893. - "Compare table schemas across different databases."
  894. **Performance Deep Dive**
  895. - "Find unused indexes."
  896. - "Analyze recent query activity."
  897. - "Identify performance bottlenecks."
  898. - "Analyze cache hit ratios."
  899. - "Monitor query execution patterns."
  900. **Capacity Planning**
  901. - "Analyze database sizes and growth trends."
  902. - "Identify largest tables and indexes."
  903. - "Review disk usage by schema."
  904. - "Plan storage capacity requirements."
  905. **Configuration Troubleshooting**
  906. - "Search for connection-related settings."
  907. - "Find logging configuration."
  908. - "Check specific parameter."
  909. **πŸ’‘ Pro Tip**: All tools support multi-database operations using the `database_name` parameter. This allows PostgreSQL superusers to analyze and monitor multiple databases from a single MCP server instance.
  910. ---
  911. ## Troubleshooting
  912. ### Connection Issues
  913. 1. Check PostgreSQL server status
  914. 2. Verify connection parameters in `.env` file
  915. 3. Ensure network connectivity
  916. 4. Check user permissions
  917. ### Extension Errors
  918. 1. Run `get_server_info` to check extension status
  919. 2. Install missing extensions:
  920. ```sql
  921. CREATE EXTENSION pg_stat_statements;
  922. CREATE EXTENSION pg_stat_monitor;
  923. ```
  924. 3. Restart PostgreSQL if needed
  925. ### OpenWebUI Issues
  926. 1. **`Function tools with reasoning_effort are not supported for <model> in
  927. /v1/chat/completions`**: Some OpenAI reasoning models reject requests that
  928. combine tool calling (required for MCP tools) with a `reasoning_effort`
  929. value on the Chat Completions API.
  930. **Enabled by default**: `.env.example` sets this OpenWebUI global default,
  931. which applies `reasoning_effort=none` to every model:
  932. ```dotenv
  933. DEFAULT_MODEL_PARAMS={"reasoning_effort":"none"}
  934. ```
  935. OpenWebUI merges global default model params first, then per-model params,
  936. then per-chat params (later values win). If you still see the error, check:
  937. - **`.env` created before this setting was added**: Add the line above to
  938. `.env`.
  939. - **Existing `open-webui-data` volume**: The value is stored in the
  940. OpenWebUI database only on first startup, and the stored value takes
  941. precedence afterward. Set it in the Admin UI instead: in **Settings β†’
  942. Admin β†’ Models**, open the default model settings and set **Reasoning
  943. Effort** to `none` under Advanced Params.
  944. - **Per-model or per-chat override**: Set **Reasoning Effort** to `none`
  945. in **Settings β†’ Admin β†’ Models β†’ \<model\> β†’ Advanced Params** or in the
  946. chat's **Controls β†’ Advanced Params**.
  947. > **Note:** The global default sends `reasoning_effort` to every model.
  948. > Models or providers that do not support this parameter may reject the
  949. > request. In that case, remove the line from `.env` and set Reasoning
  950. > Effort per model instead.
  951. 2. **`Unsupported parameter: 'reasoning_effort' ... moved to
  952. 'reasoning.effort'`**: OpenWebUI 0.13.0 sent the Chat Completions parameter
  953. `reasoning_effort` (even `none`) through an OpenAI connection with **API
  954. Type: Responses**. Set **API Type** to **Chat Completions** under **Settings
  955. β†’ Admin β†’ Connections β†’ edit the OpenAI connection** (Quick Start default),
  956. or keep **Responses** and set Reasoning Effort to **Default** in every scope
  957. as described in [OpenAI reasoning models](#openai-reasoning-models). Then
  958. start a new chat.
  959. ### OpenAI reasoning models
  960. For OpenAI reasoning models, the connection's **API Type** and the **Reasoning
  961. Effort** parameter must match. Set **API Type** under **Settings β†’ Admin β†’
  962. Connections β†’ edit the OpenAI connection**, and use one of these combinations:
  963. | Setup | API Type | Reasoning Effort | Result |
  964. | --- | --- | --- | --- |
  965. | Quick Start default | **Chat Completions** | `none` (from `DEFAULT_MODEL_PARAMS`) | Tools work; model reasoning is off |
  966. | Reasoning with tools | **Responses** | **Default** in every scope | Tools work; the model uses its default reasoning effort |
  967. Other combinations fail with OpenWebUI 0.13.0:
  968. - **Responses** with any `reasoning_effort` value, including `none`: OpenWebUI
  969. sends the Chat Completions parameter `reasoning_effort` unchanged, and the
  970. Responses API rejects it with `Unsupported parameter: 'reasoning_effort'`.
  971. - **Chat Completions** with a reasoning effort other than `none`: the API
  972. rejects the request with `Function tools with reasoning_effort are not
  973. supported`.
  974. To use model reasoning instead, set `DEFAULT_MODEL_PARAMS={}` before the first
  975. start, set the connection's **API Type** to **Responses**, and leave
  976. **Reasoning Effort** at **Default** under **Settings β†’ Admin β†’ Models β†’ Model
  977. Defaults** and **Settings β†’ Admin β†’ Models β†’ _model_ β†’ Advanced Params**.
  978. Remove any `reasoning_effort` or `reasoning` override from the chat's
  979. **Controls β†’ Advanced Params** and **Settings β†’ General β†’ Advanced
  980. Parameters**, then start a new chat. OpenWebUI can merge parameters from these
  981. scopes, so an old override can still make the request fail. If the
  982. `open-webui-data` volume already exists, clear `reasoning_effort` under
  983. **Model Defaults** in the administrator UI, because the saved database value
  984. takes precedence over `.env`.
  985. Models and providers without this constraint may keep their normal API type
  986. and reasoning settings.
  987. ### Configuration Issues
  988. 1. **"No data found" for function statistics**: Check `track_functions` setting
  989. ```sql
  990. SHOW track_functions; -- Should be 'pl' or 'all'
  991. ```
  992. **Quick fix for managed services (AWS RDS, etc.)**:
  993. ```sql
  994. ALTER SYSTEM SET track_functions = 'pl';
  995. SELECT pg_reload_conf();
  996. ```
  997. 2. **Missing I/O timing data**: Enable timing collection
  998. ```sql
  999. SHOW track_io_timing; -- Should be 'on'
  1000. ```
  1001. **Quick fix**:
  1002. ```sql
  1003. ALTER SYSTEM SET track_io_timing = 'on';
  1004. SELECT pg_reload_conf();
  1005. ```
  1006. 3. **Apply configuration changes**:
  1007. - **Self-managed**: Add settings to `postgresql.conf` and restart server
  1008. - **Managed services**: Use `ALTER SYSTEM SET` + `SELECT pg_reload_conf()`
  1009. - **Temporary testing**: Use `SET parameter = value` for current session
  1010. - Generate some database activity to populate statistics
  1011. ### Performance Issues
  1012. 1. Use `limit` parameters to reduce result size
  1013. 2. Run monitoring during off-peak hours
  1014. 3. Check database load before running analysis
  1015. ### Version Compatibility Issues
  1016. > For more details, see the [## Tool Compatibility Matrix](#tool-compatibility-matrix)
  1017. 1. **Run compatibility check first**:
  1018. ```bash
  1019. # "Use get_server_info to check version and available features"
  1020. ```
  1021. 2. **Understanding feature availability**:
  1022. - **PostgreSQL 18**: All features including async I/O, VACUUM timing, per-backend stats
  1023. - **PostgreSQL 17**: Separate checkpointer stats, wait events, WAL summarizer
  1024. - **PostgreSQL 16**: pg_stat_io view
  1025. - **PostgreSQL 14+**: Parallel query tracking
  1026. - **PostgreSQL 12-13**: Core functionality only
  1027. 3. **If a tool shows "Not Available"**:
  1028. - Feature requires newer PostgreSQL version
  1029. - Tool will automatically use best available alternative
  1030. - Consider upgrading PostgreSQL for enhanced monitoring
  1031. ---
  1032. ## Development
  1033. ### Testing & Development
  1034. ```bash
  1035. # Clone and setup for development
  1036. git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
  1037. cd MCP-PostgreSQL-Ops
  1038. uv sync
  1039. # Test with MCP Inspector (loads .env automatically)
  1040. ./run-mcp-inspector-local.sh
  1041. # Direct execution methods:
  1042. # 1. Using uv run (recommended for development)
  1043. uv run mcp-postgresql-ops --log-level DEBUG
  1044. # 2. Module execution (requires PYTHONPATH)
  1045. PYTHONPATH=src python -m mcp_postgresql_ops --log-level DEBUG
  1046. # 3. After installation
  1047. mcp-postgresql-ops --log-level DEBUG
  1048. # Test version compatibility (requires different PostgreSQL versions)
  1049. # Modify POSTGRES_HOST in .env to point to different versions
  1050. ```
  1051. ### Running Tests
  1052. The following test suites are available:
  1053. | Suite | File | Requires Docker |
  1054. |-------|------|----------------|
  1055. | Unit tests (version logic, tool schemas, generated SQL, release scripts) | every `tests/test_*.py` except `test_tools_integration.py` | No |
  1056. | Integration tests (all tools Γ— PG 12–18) | `tests/test_tools_integration.py` | Yes |
  1057. `uv run pytest` automatically starts the Docker test containers (PG 12–18), waits for them to be fully initialized, runs all tests, then tears everything down.
  1058. ```bash
  1059. # Run all tests (unit + integration) β€” Docker is managed automatically
  1060. uv run pytest -v
  1061. # Unit tests only (no Docker or PostgreSQL needed; same command as CI)
  1062. uv run pytest tests --ignore=tests/test_tools_integration.py -v
  1063. # Tool argument schema tests only (no Docker or PostgreSQL needed)
  1064. uv run pytest tests/test_tool_schema_null_defaults.py -v
  1065. # Integration tests only
  1066. uv run pytest tests/test_tools_integration.py -v
  1067. ```
  1068. > **Note**: Docker must be running for integration tests. The test stack uses ports 5412–5418 (PG 12–18).
  1069. ### Version Compatibility Testing
  1070. The MCP server automatically adapts to PostgreSQL versions 12-18. To test across versions:
  1071. 1. **Set up test databases**: Different PostgreSQL versions (12, 14, 15, 16, 17, 18)
  1072. 2. **Run compatibility tests**: Point to each version and verify tool behavior
  1073. 3. **Check feature detection**: Ensure proper version detection and feature availability
  1074. 4. **Verify fallback behavior**: Confirm graceful degradation on older versions
  1075. ---
  1076. ## Security Notes
  1077. - All tools are **read-only** - no data modification capabilities
  1078. - Sensitive information (passwords) are masked in outputs
  1079. - No direct SQL execution - only predefined queries
  1080. - Follows principle of least privilege
  1081. ---
  1082. ## Contributing
  1083. 🀝 **Got ideas? Found bugs? Want to add cool features?**
  1084. We're always excited to welcome new contributors! Whether you're fixing a typo, adding a new monitoring tool, or improving documentation - every contribution makes this project better.
  1085. **Ways to contribute:**
  1086. - πŸ› Report issues or bugs
  1087. - πŸ’‘ Suggest new PostgreSQL monitoring features
  1088. - πŸ“ Improve documentation
  1089. - πŸš€ Submit pull requests
  1090. - ⭐ Star the repo if you find it useful!
  1091. **Pro tip:** The codebase is designed to be super friendly for adding new tools. Check out the existing `@mcp.tool()` functions in `mcp_main.py`.
  1092. ---
  1093. ## Optional MCPO/OpenAPI endpoint
  1094. OpenWebUI 0.13 uses the MCP server directly; MCPO is not required for that
  1095. connection. The standard Compose deployment still starts MCPO to preserve the
  1096. same deployment layout across this project's sibling MCP repositories. Open
  1097. the [MCPO Swagger endpoint](http://localhost:8003/mcp-postgresql-ops/docs), or
  1098. omit MCPO with `docker compose up -d mcp-server open-webui`.
  1099. MCPO reads the ignored file selected by `MCPO_CONFIG_FILE`. Create it from the
  1100. tracked template before starting Compose:
  1101. ```bash
  1102. cp mcp-config.json.http mcp-config.json.http.local
  1103. chmod 600 mcp-config.json.http.local
  1104. ```
  1105. When Bearer authentication is enabled, put the same `REMOTE_SECRET_KEY` in
  1106. `.env` and in the local file's `Authorization` header. Never place the secret
  1107. in the tracked template.
  1108. ![MCPO Swagger APIs](img/screenshot-swagger-api.png)
  1109. ---
  1110. ## πŸ” Security & Authentication
  1111. ### Bearer Token Authentication
  1112. For `streamable-http` mode, this MCP server supports Bearer token authentication to secure remote access. This is especially important when running the server in production environments.
  1113. > **Default Policy**: `REMOTE_AUTH_ENABLE` defaults to `false` if undefined, null, or empty. This ensures backward compatibility and prevents startup errors when the variable is not set.
  1114. #### Configuration
  1115. **Enable Authentication:**
  1116. ```bash
  1117. # In .env file
  1118. REMOTE_AUTH_ENABLE=true
  1119. REMOTE_SECRET_KEY=replace-with-a-strong-random-secret
  1120. ```
  1121. **Or via CLI:**
  1122. ```bash
  1123. # Module method
  1124. python -m mcp_postgresql_ops --type streamable-http --auth-enable \
  1125. --secret-key replace-with-a-strong-random-secret
  1126. # Script method
  1127. mcp-postgresql-ops --type streamable-http --auth-enable \
  1128. --secret-key replace-with-a-strong-random-secret
  1129. ```
  1130. #### Security Levels
  1131. 1. **stdio mode** (Default): Local-only access, no authentication needed
  1132. 2. **streamable-http + REMOTE_AUTH_ENABLE=false**: Remote access without authentication ⚠️ **NOT RECOMMENDED for production**
  1133. 3. **streamable-http + REMOTE_AUTH_ENABLE=true**: Remote access with Bearer token authentication βœ… **RECOMMENDED for production**
  1134. #### Client Configuration
  1135. When authentication is enabled, MCP clients must include the Bearer token in the Authorization header:
  1136. ```json
  1137. {
  1138. "mcpServers": {
  1139. "mcp-postgresql-ops": {
  1140. "type": "streamable-http",
  1141. "url": "http://your-server:8000/mcp",
  1142. "headers": {
  1143. "Authorization": "Bearer replace-with-a-strong-random-secret"
  1144. }
  1145. }
  1146. }
  1147. }
  1148. ```
  1149. #### Security Best Practices
  1150. - **Always enable authentication** when using streamable-http mode in production
  1151. - **Use strong, randomly generated secret keys** (32+ characters recommended)
  1152. - **Use HTTPS** when possible (configure reverse proxy with SSL/TLS)
  1153. - **Restrict network access** using firewalls or network policies
  1154. - **Rotate secret keys regularly** for enhanced security
  1155. - **Monitor access logs** for unauthorized access attempts
  1156. #### Error Handling
  1157. When authentication fails, the server returns:
  1158. - **401 Unauthorized** for missing or invalid tokens
  1159. - **Detailed error messages** in JSON format for debugging
  1160. ---
  1161. ## πŸš€ Adding Custom Tools
  1162. This MCP server is designed for easy extensibility. Follow these 4 simple steps to add your own custom tools:
  1163. ### Step-by-Step Guide
  1164. #### 1. **Add Helper Functions (Optional)**
  1165. Add reusable data functions to `src/mcp_postgresql_ops/functions.py`:
  1166. ```python
  1167. async def get_your_custom_data(target_database: str = None, limit: int = 20) -> List[Dict[str, Any]]:
  1168. """Your custom data retrieval function."""
  1169. try:
  1170. # Example implementation - adapt to your PostgreSQL needs
  1171. query = """
  1172. SELECT
  1173. schemaname,
  1174. tablename,
  1175. attname as column_name,
  1176. n_distinct,
  1177. most_common_vals,
  1178. most_common_freqs
  1179. FROM pg_stats
  1180. WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
  1181. ORDER BY schemaname, tablename, attname
  1182. LIMIT $1
  1183. """
  1184. results = await execute_query(query, [limit], database=target_database)
  1185. return results
  1186. except Exception as e:
  1187. logger.error(f"Failed to get custom data: {e}")
  1188. raise
  1189. ```
  1190. #### 2. **Create Your MCP Tool**
  1191. Add your tool function to `src/mcp_postgresql_ops/mcp_main.py`:
  1192. ```python
  1193. @mcp.tool()
  1194. async def get_your_custom_analysis(limit: int = 50, database_name: Optional[str] = None) -> str:
  1195. """
  1196. [Tool Purpose]: Brief description of what your tool does
  1197. [Exact Functionality]:
  1198. - Feature 1: Data aggregation and analysis
  1199. - Feature 2: Database monitoring and insights
  1200. - Feature 3: Performance metrics and reporting
  1201. [Required Use Cases]:
  1202. - When user asks "your specific analysis request"
  1203. - Your PostgreSQL-specific monitoring needs
  1204. Args:
  1205. limit: Maximum results (1-100)
  1206. database_name: Target database name (optional, uses default if not specified)
  1207. Returns:
  1208. Formatted analysis results
  1209. """
  1210. try:
  1211. # Always validate input limits
  1212. limit = max(1, min(limit, 100))
  1213. # Get your custom data
  1214. results = await get_your_custom_data(target_database=database_name, limit=limit)
  1215. if not results:
  1216. return "No data found for custom analysis."
  1217. # Format and return results
  1218. return format_table_data(results, f"Custom Analysis Results (Top {len(results)})")
  1219. except Exception as e:
  1220. logger.error(f"Failed to get custom analysis: {e}")
  1221. return f"Error: {str(e)}"
  1222. ```
  1223. #### 3. **Update Imports**
  1224. Add your helper function to the imports section in `src/mcp_postgresql_ops/mcp_main.py` (around line 30):
  1225. ```python
  1226. from .functions import (
  1227. execute_query,
  1228. execute_single_query,
  1229. format_table_data,
  1230. format_bytes,
  1231. format_duration,
  1232. get_server_version,
  1233. check_extension_exists,
  1234. get_pg_stat_statements_data,
  1235. get_pg_stat_monitor_data,
  1236. sanitize_connection_info,
  1237. get_current_database_name,
  1238. POSTGRES_CONFIG,
  1239. get_your_custom_data, # Add your new function here
  1240. )
  1241. ```
  1242. #### 4. **Document Your Tool**
  1243. - **Docstring**: The tool docstring is what the model reads when choosing tools. Describe its purpose, arguments, and when to use or not use it.
  1244. - **Example requests**: Add sample requests for the tool to [Example Queries](#example-queries).
  1245. - **Server instructions**: Edit `src/mcp_postgresql_ops/server_instructions.md` only when a rule that spans several tools changes. Clients inject it into the model context, and Claude Code truncates it beyond 4,096 characters, so keep it short and do not repeat tool descriptions.
  1246. #### 5. **Test Your Tool**
  1247. ```bash
  1248. # Local testing with MCP Inspector
  1249. ./run-mcp-inspector-local.sh
  1250. # Or test with Docker stack
  1251. docker compose up -d
  1252. docker compose logs -f mcp-server
  1253. # Test with natural language queries:
  1254. # "Show me your custom analysis"
  1255. # "Get custom analysis for ecommerce database"
  1256. # "Analyze custom data with limit 25"
  1257. ```
  1258. ### Important Notes
  1259. - **Multi-Database Support**: All tools support the optional `database_name` parameter to target specific databases
  1260. - **Input Validation**: Always validate `limit` parameters with `max(1, min(limit, 100))`
  1261. - **Error Handling**: Return user-friendly error messages instead of raising exceptions
  1262. - **Logging**: Use `logger.error()` for debugging while returning clean error messages to users
  1263. - **PostgreSQL Compatibility**: Your custom queries should work across PostgreSQL 12-18
  1264. - **Extension Dependencies**: If your tool requires specific extensions, check availability with `check_extension_exists()`
  1265. ### Advanced Patterns
  1266. For version-aware queries or extension-dependent features, see existing tools like `get_pg_stat_statements_top_queries` for reference patterns.
  1267. That's it! Your custom tool is ready to use with natural language queries through any MCP client.
  1268. ---
  1269. ## License
  1270. Freely use, modify, and distribute under the **MIT License**.
  1271. ---
  1272. ## ⭐ Other Projects
  1273. **Other MCP servers by the same author:**
  1274. - [MCP-Airflow-API](https://github.com/call518/MCP-Airflow-API)
  1275. - [MCP-Ambari-API](https://github.com/call518/MCP-Ambari-API)
  1276. - [MCP-OpenStack-API](https://github.com/call518/MCP-OpenStack-API)
  1277. - [LogSentinelAI - LLB-Based Log Analyzer](https://github.com/call518/LogSentinelAI)

Ratings and comments

Sign in to rate this skill.

Sign in

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