Metadata-Version: 2.4
Name: mcp-postgresql-ops
Version: 3.5.1
Summary: Add your description here
Author-email: JungJungIn <call518@gmail.com>
Requires-Python: <3.13,>=3.12
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: fastmcp>=2.12.3
Requires-Dist: asyncpg>=0.29.0
Requires-Dist: psycopg2-binary>=2.9.7
Requires-Dist: setuptools-scm>=10.0.5
Requires-Dist: pre-commit>=4.6.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0.0; extra == "dev"
Requires-Dist: pytest-asyncio>=0.21.0; extra == "dev"
Dynamic: license-file

# MCP-PostgreSQL-Ops

> Give Claude and other AI assistants read-only PostgreSQL observability for
> slow queries, locks, bloat, VACUUM, indexes, replication, WAL, and capacity.

[![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)

[![MCP Toplist](https://mcptoplist.com/badge/smithery%2Fcall518%2Fmcp-postgresql-ops.svg)](https://mcptoplist.com/server/smithery%2Fcall518%2Fmcp-postgresql-ops)

[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)
![Python](https://img.shields.io/badge/Python-3776AB?style=flat&logo=python&logoColor=white)
![Docker Pulls](https://img.shields.io/docker/pulls/call518/mcp-server-postgresql-ops)
![PostgreSQL](https://img.shields.io/badge/PostgreSQL-12--18-4169E1?style=flat&logo=postgresql&logoColor=white)

[![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)
![PyPI](https://img.shields.io/pypi/v/MCP-PostgreSQL-Ops?label=pypi%20package)
![PyPI - Downloads](https://img.shields.io/pypi/dm/MCP-PostgreSQL-Ops)

---

## What is MCP-PostgreSQL-Ops?

MCP-PostgreSQL-Ops lets Claude, OpenWebUI, and other MCP clients inspect a
PostgreSQL server through natural-language questions. It turns requests such as
“Which tables need VACUUM now?” into predefined, read-only SQL queries and
returns the evidence needed to diagnose the database.

- **Read-only tools:** no arbitrary SQL execution or data-changing operations.
- **PostgreSQL 12–18:** version-specific views and columns are detected
  automatically.
- **Useful without extensions:** core monitoring works immediately;
  `pg_stat_statements` and `pg_stat_monitor` add query analytics.
- **Deployment choice:** install a Claude Desktop extension, run with `uvx`, or
  connect OpenWebUI directly to the HTTP MCP server.

For an AI-generated code walkthrough, use
[![Ask DeepWiki](https://img.shields.io/badge/Ask-DeepWiki-blue)](https://deepwiki.com/call518/MCP-PostgreSQL-Ops).

## What can you ask?

```text
Show the current active connections and blocked sessions
Which queries consume the most execution time?
Find bloated tables and explain what needs attention first
Which tables need VACUUM or autovacuum tuning?
Show unused or inefficient indexes
Check replication, WAL, checkpoints, and background processes
Summarize database size, growth, and I/O activity
Draw the relationships for the ecommerce schema
```

**More requests to try:** see [Example Queries](#example-queries) for requests
by area (performance, health checks, capacity and VACUUM, structure,
troubleshooting, multi-step workflows) and per-tool examples.

Core tools work with normal catalog access. Broader cross-database visibility
depends on the PostgreSQL role; use a least-privilege monitoring account in
production.

**Example: slow-query evidence and recommendations**

![Slow-query analysis](img/screenshot-000.png)

More examples are available in the
[screenshots and usage wiki](https://github.com/call518/MCP-PostgreSQL-Ops/wiki).

## Quick Start

Choose one path. If you already have PostgreSQL and Claude Desktop, Option A
is the shortest. Option B provides a complete local demonstration with sample
databases and OpenWebUI.

<a id="claude-desktop-integration"></a>

### Option A: Claude Desktop extension (fastest)

Requirements: Claude Desktop with extension support and a PostgreSQL server
reachable from the computer running Claude Desktop.

1. Open the latest
   [GitHub Release](https://github.com/call518/MCP-PostgreSQL-Ops/releases)
   and download `mcp-postgresql-ops-<version>.mcpb`.
2. In Claude Desktop, open **Settings → Extensions → Advanced settings →
   Install Extension…** and select the downloaded file.
3. Enter the PostgreSQL host, port, user, password, and default database.
4. Ask Claude: `Show the current active PostgreSQL connections.`

The password field is marked sensitive and is stored by Claude Desktop. The
extension runs the server locally over `stdio`; it does not expose an HTTP
port.

### Option B: OpenWebUI 0.13 with the included demo database

Requirements: Git, Docker with the Compose plugin, and an OpenWebUI model
provider whose model supports tool calling.

**1. Configure**

```bash
git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops
cp .env.example .env
cp mcp-config.json.http mcp-config.json.http.local
chmod 600 mcp-config.json.http.local
openssl rand -hex 32   # use the output as WEBUI_SECRET_KEY
vi .env                # set WEBUI_SECRET_KEY; adjust ports if needed
```

`mcp-config.json.http.local` is ignored by Git and is mounted only into MCPO.
If `REMOTE_AUTH_ENABLE=true`, replace `<REMOTE_SECRET_KEY>` in that local file
with the same secret configured in `.env`. Leave the tracked
`mcp-config.json.http` file as a credential-free template.

The default `.env.example` credentials are only for the included disposable
PostgreSQL demo. Do not reuse them for an existing or externally reachable
database.

**2. Start**

```bash
docker compose up -d
docker compose ps --all  # wait for mcp-server to be healthy and init to exit 0
```

This starts OpenWebUI at `http://localhost:3003`, the MCP server at
`http://localhost:18003/mcp`, optional MCPO/OpenAPI compatibility at
`http://localhost:8003`, and a disposable PostgreSQL demo environment. The
demo includes ecommerce, analytics, inventory, and HR data plus intentional
bloat and index patterns for realistic monitoring questions.

**3. Connect OpenWebUI to the MCP server**

1. Open `http://localhost:3003`, create the administrator account, and add a
   model provider under **Settings → Admin → Connections**:
   - **OpenAI:** add an OpenAI API connection with URL
     `https://api.openai.com/v1`, enter your OpenAI API key (Auth **Bearer**),
     set **API Type** to **Chat Completions**, and save it.
   - **Ollama on the Docker host:** the Compose file already points OpenWebUI
     to `http://host.docker.internal:11434`; ensure Ollama is reachable from
     Docker and pull a tool-capable model.

   Confirm that the model appears in a new chat before continuing.
2. The Quick Start calls tools through Chat Completions with model reasoning
   turned off, using this default already present in `.env.example`:

   ```dotenv
   DEFAULT_MODEL_PARAMS={"reasoning_effort":"none"}
   ```

   This value requires **API Type: Chat Completions**. With **Responses**,
   OpenWebUI 0.13.0 still sends `reasoning_effort`, and every request fails with
   `Unsupported parameter: 'reasoning_effort'`. If the `open-webui-data` volume
   already exists, set the same value under **Settings → Admin → Models → Model
   Defaults → Model Parameters** because its saved database value takes
   precedence. To keep model reasoning through **Responses** instead, see
   [OpenAI reasoning models](#openai-reasoning-models).
3. Under **Settings → Admin → Tools → Integrations → External Tool Servers**,
   add a connection of type **MCP (Streamable HTTP)** with URL
   `http://mcp-server:8000/mcp` and authentication **None**. This matches the
   Quick Start default `REMOTE_AUTH_ENABLE=false`; enable and configure Bearer
   authentication before exposing the server outside the local Docker host.
4. Under **Settings → Admin → System → Interface → Chat**, enable **Tool
   Search** and **Defer Built-in Tools**, then set **Deferral Threshold** to `0`.
   This keeps the full PostgreSQL tool list within the model's tool limits.
5. In a new chat, enable the connection under **Integrations → Tools** and ask:
   `Use mcp-postgresql-ops to show tables that need VACUUM.`

If setup or tool calls fail, see [OpenWebUI Issues](#openwebui-issues) for
existing-volume precedence, per-model and per-chat overrides, and provider
compatibility notes.

> **MCPO is optional.** OpenWebUI 0.13 connects to the MCP server directly and
> does not require the MCPO proxy. To expose the tools as an OpenAPI/Swagger
> service for a legacy client, use MCPO at `http://localhost:8003`. The Compose
> file starts it so every project keeps the same deployment layout. To run
> without it, use `docker compose up -d mcp-server open-webui`.

To connect the Docker stack to an existing PostgreSQL server instead, update
the `POSTGRES_*` values in `.env` and run:

```bash
docker compose -f docker-compose.custom-db.yml up -d
```

## Why this project?

- **Operational evidence, not arbitrary SQL:** every tool uses a predefined
  monitoring query and returns inspectable PostgreSQL statistics.
- **Automatic version adaptation:** one server supports PostgreSQL 12 through
  18, including newer I/O, wait-event, checkpointer, and WAL views.
- **Maintenance intelligence:** bloat, VACUUM, autovacuum, indexes, locks,
  capacity, replication, and query performance are covered together.
- **Managed-service friendly:** core tools work on RDS/Aurora and with regular
  monitoring roles; advanced visibility still follows provider permissions.
- **Multiple clients:** Claude Desktop, OpenWebUI, any Streamable HTTP MCP
  client, and optional OpenAPI clients through MCPO.

## How it works

```text
Claude Desktop ── stdio ───────────────┐
                                       │
OpenWebUI 0.13 ─ Streamable HTTP ──────┼─> MCP-PostgreSQL-Ops
                                       │      │
OpenAPI client ─ optional MCPO proxy ──┘      └─> read-only SQL ─> PostgreSQL
```

The MCP server is the required component. OpenWebUI can call it directly;
MCPO is only an optional protocol adapter. The
[DeepWiki architecture](https://deepwiki.com/call518/MCP-PostgreSQL-Ops)
provides a code-level walkthrough.

## Installation options

| Setup | Best for | Requirements |
| --- | --- | --- |
| MCPB extension | Fastest Claude Desktop setup | Claude Desktop |
| `uvx --python 3.12 mcp-postgresql-ops` | Explicit local configuration | `uv` |
| Docker Compose | OpenWebUI and HTTP MCP clients | Docker Compose |
| Source checkout | Development and customization | Git, `uv` |

All methods run the same server. Choose one; they are not cumulative.

## Features and compatibility

### Tool Compatibility Matrix

> **Automatic Adaptation:** All tools work transparently across supported versions - no configuration needed!

### 🟢 **Extension-Independent Tools (No Extensions Required)**

| Tool Name | Extensions Required | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | System Views/Tables Used |
|-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|--------------------------|
| `get_server_info` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `version()`, `pg_extension` |
| `get_active_connections` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_activity` |
| `get_postgresql_config` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_settings` |
| `get_database_list` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_database` |
| `get_table_list` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `information_schema.tables` |
| `get_table_schema_info` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `information_schema.*`, `pg_indexes`, `pg_constraint` |
| `get_database_schema_info` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_namespace`, `pg_class`, `pg_proc` |
| `get_table_relationships` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_constraint`, `information_schema.*` |
| `get_user_list` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_user`, `pg_roles` |
| `get_index_usage_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_user_indexes` |
| `get_database_size_info` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_database_size()` |
| `get_table_size_info` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_total_relation_size()` |
| `get_vacuum_analyze_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ **Enhanced** | `pg_stat_user_tables` |
| `get_current_database_info` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_database`, `current_database()` |
| `get_table_bloat_analysis` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_user_tables` |
| `get_database_bloat_overview` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_user_tables` |
| `get_autovacuum_status` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_user_tables` |
| `get_autovacuum_activity` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_user_tables` |
| `get_running_vacuum_operations` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_activity` |
| `get_vacuum_effectiveness_analysis` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_user_tables` |
| `get_lock_monitoring` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_locks`, `pg_stat_activity` |
| `get_wal_status` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_current_wal_lsn()` |
| `get_database_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ **Enhanced** | `pg_stat_database` |
| `get_table_io_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_statio_user_tables` |
| `get_index_io_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_statio_user_indexes` |
| `get_database_conflicts_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | `pg_stat_database_conflicts` |

### 🚀 **Version-Aware Tools (Auto-Adapting)**

| Tool Name | Extensions Required | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | Special Features |
|-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|------------------|
| `get_io_stats` | ❌ None | ✅ Basic | ✅ Basic | ✅ Basic | ✅ Basic | ✅ **Enhanced** | ✅ **Enhanced** | ✅ **Enhanced** | PG16+: `pg_stat_io` support; PG18+: byte columns |
| `get_bgwriter_stats` | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ **Special** | ✅ **Enhanced** | PG17: Separate checkpointer stats; PG18+: `num_done`, `slru_written` |
| `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` |
| `get_all_tables_stats` | ❌ None | ✅ Compatible | ✅ **Enhanced** | ✅ **Enhanced** | ✅ **Enhanced** | ✅ **Enhanced** | ✅ **Enhanced** | ✅ **Enhanced** | PG13+: `n_ins_since_vacuum` tracking for vacuum maintenance optimization |
| `get_user_functions_stats` | ⚙️ Config Required | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | Requires `track_functions=pl` |
| `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 |
| `get_wal_summarizer_status` | ❌ None | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | ✅ | PG17+: WAL summarizer monitoring for incremental backups |
| `get_async_io_status` | ❌ None | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | PG18+: `pg_aios` async I/O subsystem monitoring |
| `get_per_backend_io_stats` | ❌ None | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ✅ | PG18+: Per-backend I/O and WAL statistics |

### 🟡 **Extension-Dependent Tools (Extensions Required)**

| Tool Name | Required Extension | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | Notes |
|-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|-------|
| `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` |
| `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` |

### 🆕 **Version-Specific Features**

#### PostgreSQL 17
- **`pg_wait_events` view**: Native wait event catalog with descriptions (used by `get_wait_events`)
- **WAL summarizer**: Monitoring for incremental backup support (used by `get_wal_summarizer_status`)
- **Replication slot enhancements**: `invalidation_reason` and `inactive_since` columns (used by `get_replication_status`)
- **`pg_stat_statements` `stats_since`**: Track when statistics were last reset (used by `get_pg_stat_statements_top_queries`)
- **VACUUM progress**: Index vacuum tracking in progress views (future enhancement for `get_running_vacuum_operations`)

#### PostgreSQL 18
- **`pg_aios` view**: Async I/O subsystem monitoring (used by `get_async_io_status`)
- **Per-backend I/O stats**: Individual backend I/O and WAL statistics (used by `get_per_backend_io_stats`)
- **VACUUM/ANALYZE time columns**: `total_vacuum_time`, `total_autovacuum_time`, `total_analyze_time`, `total_autoanalyze_time` cumulative timing (used by `get_vacuum_analyze_stats`)
- **`pg_stat_io` byte columns**: `read_bytes`, `write_bytes`, `extend_bytes` (used by `get_io_stats`)
- **Parallel worker stats**: `parallel_workers_launched`, `parallel_workers_to_launch` (used by `get_database_stats`)
- **Checkpointer enhancements**: `num_done`, `slru_written` columns (used by `get_bgwriter_stats`)

---

## Other local setups

### Run from PyPI with uvx

```bash
# Local stdio transport
uvx --python 3.12 mcp-postgresql-ops --type stdio

# Streamable HTTP transport
uvx --python 3.12 mcp-postgresql-ops \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000
```

### Run from source

```bash
git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops
uv sync
uv run mcp-postgresql-ops --help
```

The package is also available through `pip install mcp-postgresql-ops`.

## Advanced configuration

### Claude Desktop Configuration

(Optional) Run with Local Source:

```json
{
  "mcpServers": {
    "mcp-postgresql-ops": {
      "command": "uv",
      "args": ["run", "python", "-m", "mcp_postgresql_ops"],
      "env": {
        "POSTGRES_HOST": "127.0.0.1",
        "POSTGRES_PORT": "15432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "changeme",
        "POSTGRES_DB": "ecommerce"
      }
    }
  }
}
```

### Run the MCP server standalone

#### With PyPI and uvx

```bash
# Stdio mode
uvx --python 3.12 mcp-postgresql-ops \
  --type stdio

# HTTP mode
uvx --python 3.12 mcp-postgresql-ops \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000 \
  --log-level DEBUG
```

### (Option) Configure Multiple PostgreSQL Instances

```json
{
  "mcpServers": {
    "Postgresql-A": {
      "command": "uvx",
      "args": ["--python", "3.12", "mcp-postgresql-ops"],
      "env": {
        "POSTGRES_HOST": "a.foo.com",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "postgres",
        "POSTGRES_DB": "postgres"
      }
    },
    "Postgresql-B": {
      "command": "uvx",
      "args": ["--python", "3.12", "mcp-postgresql-ops"],
      "env": {
        "POSTGRES_HOST": "b.bar.com",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "postgres",
        "POSTGRES_DB": "postgres"
      }
    }
  }
}
```

#### /w Local Source

```bash
# Method 1: Module execution (for development, requires PYTHONPATH)
PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src
python -m mcp_postgresql_ops \
  --type stdio

# Method 2: Direct script (after uv installation in project directory)
uv run mcp-postgresql-ops \
  --type stdio

# Method 3: Installed package script (after pip/uv install)
mcp-postgresql-ops \
  --type stdio

# HTTP mode examples:
# Development mode
PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src
python -m mcp_postgresql_ops \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000 \
  --log-level DEBUG

# Production mode (after installation)
mcp-postgresql-ops \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000 \
  --log-level DEBUG
```

---

## CLI Arguments

- `--type`: Transport type (`stdio` or `streamable-http`) - Default: `stdio`
- `--host`: Host address for HTTP transport - Default: `127.0.0.1`  
- `--port`: Port number for HTTP transport - Default: `8000`
- `--auth-enable`: Enable Bearer token authentication for streamable-http mode - Default: `false`
- `--secret-key`: Secret key for Bearer token authentication (required when auth enabled)
- `--log-level`: Logging level (DEBUG, INFO, WARNING, ERROR, CRITICAL) - Default: `INFO`

---

## Environment Variables

| Variable | Description | Default | Project Default |
|----------|-------------|---------|-----------------|
| `PYTHONPATH` | Python module search path (only needed for development mode) | - | `/app/src` |
| `MCPO_CONFIG_FILE` | Ignored local MCPO configuration mounted by Docker Compose | `mcp-config.json.http.local` | `mcp-config.json.http.local` |
| `MCP_LOG_LEVEL` | Server logging verbosity (DEBUG, INFO, WARNING, ERROR) | `INFO` | `INFO` |
| `FASTMCP_TYPE` | MCP transport protocol (stdio for CLI, streamable-http for web) | `stdio` | `streamable-http` |
| `FASTMCP_HOST` | HTTP server bind address (0.0.0.0 for all interfaces) | `127.0.0.1` | `0.0.0.0` |
| `FASTMCP_PORT` | HTTP server port for MCP communication | `8000` | `8000` |
| `REMOTE_AUTH_ENABLE` | Enable Bearer token authentication for streamable-http mode (Default: `false` if undefined/null/empty) | `false` | `false` |
| `REMOTE_SECRET_KEY` | Secret key for Bearer token authentication (required when auth enabled) | - | empty |
| `PGSQL_VERSION` | PostgreSQL major version for Docker image selection | `16` | `16` |
| `PGDATA` | PostgreSQL data directory inside Docker container (**Do not modify**) | `/var/lib/postgresql/data` | `/data/db` |
| `POSTGRES_HOST` | PostgreSQL server hostname or IP address | `127.0.0.1` | `host.docker.internal` |
| `POSTGRES_PORT` | PostgreSQL server port number | `5432` | `15432` |
| `POSTGRES_USER` | PostgreSQL connection username (needs read permissions) | `postgres` | `postgres` |
| `POSTGRES_PASSWORD` | PostgreSQL user password (supports special characters) | `changeme` | `changeme` |
| `POSTGRES_DB` | Default database name for connections | `testdb` | `ecommerce` |
| `POSTGRES_MAX_CONNECTIONS` | PostgreSQL max_connections configuration parameter | `200` | `200` |
| `DOCKER_BIND_ADDRESS` | Host address for published Docker ports | `127.0.0.1` | `127.0.0.1` |
| `DOCKER_EXTERNAL_PORT_OPENWEBUI` | Host port mapping for Open WebUI container | `8080` | `3003` |
| `DOCKER_EXTERNAL_PORT_MCP_SERVER` | Host port mapping for MCP server container | `8080` | `18003` |
| `DOCKER_EXTERNAL_PORT_MCPO_PROXY` | Host port mapping for MCPO proxy container | `8000` | `8003` |
| `DOCKER_INTERNAL_PORT_POSTGRESQL` | PostgreSQL container internal port | `5432` | `5432` |

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

**Port Configuration**: The built-in PostgreSQL container uses port mapping `15432:5432` where:
- `POSTGRES_PORT=15432`: External port for host access and MCP server connections
- `DOCKER_INTERNAL_PORT_POSTGRESQL=5432`: Internal container port (PostgreSQL default)
- When using external PostgreSQL servers, set `POSTGRES_PORT` to match your server's actual port

---

## Prerequisites

### Required PostgreSQL Extensions

> For more details, see the [## Tool Compatibility Matrix](#tool-compatibility-matrix)

**Note**: Most MCP tools work without any PostgreSQL extensions. section below. Some advanced performance analysis tools require the following extensions:

```sql
-- Query performance statistics (required only for get_pg_stat_statements_top_queries)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Advanced monitoring (optional, used by get_pg_stat_monitor_recent_queries)
CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;
```

**Quick Setup**: For new PostgreSQL installations, add to `postgresql.conf`:
```
shared_preload_libraries = 'pg_stat_statements'
```
Then restart PostgreSQL and run the CREATE EXTENSION commands above.

- `pg_stat_statements` is required only for slow query analysis tools.
- `pg_stat_monitor` is optional and used for real-time query monitoring.
- All other tools work without these extensions.

### Minimum Requirements
- PostgreSQL 12+ (tested with PostgreSQL 17 and 18)
- Python 3.12
- Network access to PostgreSQL server
- Read permissions on system catalogs

### Required PostgreSQL Configuration

**⚠️ Statistics Collection Settings**:
Some MCP tools require specific PostgreSQL configuration parameters to collect statistics. Choose one of the following configuration methods:

**Tools affected by these settings**:
- **get_user_functions_stats**: Requires `track_functions = pl` or `track_functions = all`
- **get_table_io_stats** & **get_index_io_stats**: More accurate timing with `track_io_timing = on`
- **get_database_stats**: Enhanced I/O timing with `track_io_timing = on`

**Verification**:
After applying any method, verify the settings:
```sql
SELECT name, setting, context FROM pg_settings WHERE name IN ('track_activities', 'track_counts', 'track_io_timing', 'track_functions') ORDER BY name;

       name       | setting |  context  
------------------+---------+-----------
 track_activities | on      | superuser
 track_counts     | on      | superuser
 track_functions  | pl      | superuser
 track_io_timing  | on      | superuser
(4 rows)
```

#### Method 1: postgresql.conf (Recommended for Self-Managed PostgreSQL)
Add the following to your `postgresql.conf`:

```ini
# Basic statistics collection (usually enabled by default)
track_activities = on
track_counts = on

# Required for function statistics tools
track_functions = pl    # Enables PL/pgSQL function statistics collection

# Optional but recommended for accurate I/O timing
track_io_timing = on    # Enables I/O timing statistics collection
```

Then restart PostgreSQL server.

#### Method 2: PostgreSQL Startup Parameters
For Docker or command-line PostgreSQL startup:

```bash
# Docker example
docker run -d \
  -e POSTGRES_PASSWORD=mypassword \
  postgres:17 \
  -c track_activities=on \
  -c track_counts=on \
  -c track_functions=pl \
  -c track_io_timing=on

# Direct postgres command
postgres -D /data \
  -c track_activities=on \
  -c track_counts=on \
  -c track_functions=pl \
  -c track_io_timing=on
```

#### Method 3: Dynamic Configuration (AWS RDS, Azure, GCP, Managed Services)
For managed PostgreSQL services where you cannot modify `postgresql.conf`, use SQL commands to change settings dynamically:

```sql
-- Enable basic statistics collection (usually enabled by default)
ALTER SYSTEM SET track_activities = 'on';
ALTER SYSTEM SET track_counts = 'on';

-- Enable function statistics collection (requires superuser privileges)
ALTER SYSTEM SET track_functions = 'pl';

-- Enable I/O timing statistics (optional but recommended)
ALTER SYSTEM SET track_io_timing = 'on';

-- Reload configuration without restart (run separately)
SELECT pg_reload_conf();
```

**Alternative for session-level testing**:
```sql
-- Set for current session only (temporary)
SET track_activities = 'on';
SET track_counts = 'on';
SET track_functions = 'pl';
SET track_io_timing = 'on';
```

**Note**: When using command-line tools, run each SQL statement separately to avoid transaction block errors.

---

## RDS/Aurora Compatibility

- 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.
- On RDS/Aurora, prefer DB Parameter Group over ALTER SYSTEM for persistent settings.
  ```sql
  -- Verify preload setting
  SHOW shared_preload_libraries;

  -- Enable extension in target DB
  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  -- Recommended visibility for monitoring
  GRANT pg_read_all_stats TO <app_user>;
  ```

---

## Example Queries

Requests are grouped first by task area, then by tool. Each tool runs read-only
queries; results depend on the PostgreSQL version, installed extensions, and
the connected role's privileges.

### 🧭 Requests by Area

**📈 Database Performance Analysis**
- "Show database-wide performance statistics"
- "Analyze transaction commit ratios and I/O patterns"
- "Check buffer cache hit ratios for all databases"
- "Monitor temporary file usage and deadlock counts"

**🔧 Background Writer & Checkpoints**
- "Analyze checkpoint performance and timing"
- "Show background writer efficiency statistics"
- "Check buffer allocation and writing patterns"
- "Monitor checkpoint scheduling vs requested ratios"

**🔍 Server Health Check**
- "Check PostgreSQL server status."
- "What database am I connected to?"
- "Show current database connection details."
- "Verify if extensions are installed."
- "Show current active connection count."
- "Display PostgreSQL version and configuration."

**📊 Performance Analysis**
- "Show top 20 slowest queries."
- "Find unused indexes."
- "Analyze recent query activity."
- "Identify performance bottlenecks."
- "Show cache hit ratios for queries."

**💾 Capacity Management**
- "Check database sizes."
- "Find largest tables."
- "Show tables that need VACUUM."
- "Analyze disk usage by database."
- "Display table and index sizes."
- "Analyze table bloat and dead tuples."
- "Show database-wide bloat summary."
- "Check autovacuum status and trigger conditions."
- "Monitor recent autovacuum activity patterns."
- "Show currently running VACUUM operations."
- "Analyze VACUUM effectiveness and maintenance patterns."

**🗄️ Structure Analysis**
- "List all databases with owners."
- "Show tables in public schema."
- "Display user accounts and permissions."
- "Explore database structure."

**📈 Advanced Monitoring**
- "Monitor active sessions and queries."
- "Analyze index usage efficiency."
- "Check VACUUM and ANALYZE history."
- "Review PostgreSQL configuration settings."
- "Find memory-related configuration parameters."
- "Show all logging configuration options."
- "Search for connection-related settings."
- "Identify connection patterns."

### 🩺 Troubleshooting Requests

**Connection Issues**
- "Check PostgreSQL server connectivity."
- "Verify connection parameters."
- "Test database access permissions."

**Extension Problems**
- "Check if pg_stat_statements is installed."
- "Verify pg_stat_monitor availability."
- "Show installed extensions status."

**Performance Issues**
- "Analyze slow query performance."
- "Check database load and connections."
- "Review index usage efficiency."
- "Monitor recent query patterns."

### 🔁 Multi-Step Workflows

Send these requests in order to run a routine review.

**Regular Health Checks**
1. "Check server status and active connections."
2. "Show top 10 slowest queries from last hour."
3. "Verify all databases are accessible."
4. "Check if any tables need maintenance."

**Capacity Planning**
1. "Analyze database sizes and growth trends."
2. "Identify largest tables and indexes."
3. "Review disk usage by schema."
4. "Plan storage capacity requirements."

**Performance Optimization**
1. "Find queries consuming most resources."
2. "Identify unused or inefficient indexes."
3. "Analyze cache hit ratios."
4. "Monitor query execution patterns."

### 🟢 Extension-Independent Tools (Always Available)

- **get_server_info**
  - "Show PostgreSQL server version and extension status."
  - "Check if pg_stat_statements is installed."
  - "Check server compatibility."
  - "Show server version and compatibility features."
  - "Check what MCP tools are available on this PostgreSQL version."
  - "Displays feature availability matrix and upgrade recommendations."
- **get_active_connections**
  - "Show all active connections."
  - "List current sessions with database and user."
  - "Monitor current sessions and their running queries."
- **get_postgresql_config**
  - "Show all PostgreSQL configuration parameters."
  - "Find all memory-related configuration settings."
  - "Show PostgreSQL configuration parameter for shared_buffers."
  - "Search for connection-related settings."
  - "Find logging configuration."
  - "Check specific parameter."
- **get_database_list**
  - "List all databases and their sizes."
  - "Show database list with owner information."
  - "List all databases with their owners and sizes."
  - "Show database encoding and connection limits."
- **get_table_list**
  - "List all tables in the ecommerce database."
  - "Show table sizes in the public schema."
  - "List all tables in the default database."
  - "Show tables in specific database."
- **get_table_schema_info**
  - "Show detailed schema information for the customers table in ecommerce database."
  - "Get column details and constraints for products table in ecommerce database."
  - "Analyze table structure with indexes and foreign keys for orders table in sales schema of ecommerce database."
  - "Show schema overview for all tables in public schema of inventory database."
  - "Get complete table structure including constraints and indexes for employees table in hr_system database."
  - "Display column information with data types and constraints for inventory_items table in inventory database."
  - 📋 **Features**: Column types, constraints, indexes, foreign keys, table metadata
  - ⚠️ **Required**: `database_name` parameter must be specified
  - 🔄 **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.
- **get_database_schema_info**
  - "Show all schemas in ecommerce database with their contents."
  - "Get detailed information about sales schema in ecommerce database."
  - "Analyze schema structure and permissions for inventory database."
  - "Show schema overview with table counts and sizes for hr_system database."
  - "Display schema owners and access privileges for all schemas in ecommerce database."
  - "Get comprehensive schema statistics including object counts and sizes."
  - 📋 **Features**: Schema owners, permissions, object counts, sizes, contents
  - ⚠️ **Required**: `database_name` parameter must be specified
- **get_table_relationships**
  - "Show all relationships for customers table in ecommerce database."
  - "Analyze foreign key relationships for orders table in sales schema of ecommerce database."
  - "Get database-wide relationship overview for ecommerce database."
  - "Find all tables that reference products table in ecommerce database."
  - "Show cross-schema relationships in inventory database."
  - "Display all foreign key dependencies in hr_system database."
  - "Analyze table relationships including inbound and outbound foreign keys."
  - "Get complete relationship mapping for specific table with constraint details."
  - 📋 **Features**: Foreign key relationships (inbound/outbound), cross-schema dependencies, constraint details
  - ⚠️ **Required**: `database_name` parameter must be specified
  - 💡 **Usage**: Leave `table_name` empty for database-wide relationship analysis
  - 🔄 **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.
- **get_user_list**
  - "List all database users and their roles."
  - "Show user permissions for a specific database."
  - "Display all database users with their permissions."
  - "Show superuser status and account limitations for all users."
- **get_index_usage_stats**
  - "Analyze index usage efficiency."
  - "Find unused indexes in the current database."
  - "Analyze index usage in default database."
  - "Check index efficiency in specific database."
  - "Identify unused indexes with zero scans (look for 'Never used' entries)."
- **get_database_size_info**
  - "Show database capacity analysis."
  - "Find the largest databases by size."
  - "Show disk usage for all databases sorted by size."
  - "Calculate total storage consumption across all databases."
- **get_table_size_info**
  - "Show table and index size analysis."
  - "Find largest tables in a specific schema."
  - "Analyze table sizes in public schema."
  - "Check table sizes in specific database schema."
- **get_vacuum_analyze_stats**
  - "Show recent VACUUM and ANALYZE operations."
  - "List tables needing VACUUM."
  - "Review VACUUM and ANALYZE history for all tables."
  - "Check maintenance status in specific database."
  - "Find tables needing maintenance (check last_vacuum dates)."
- **get_current_database_info**
  - "What database am I connected to?"
  - "Show current database information and connection details."
  - "Display database encoding, collation, and size information."
  - "Show current database information and properties."
  - "Display database encoding, collation, and connection limits."
  - "Get current database size and configuration details."
  - "Show connection context and database properties."
  - 📋 **Features**: Database name, encoding, collation, size, connection limits
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
- **get_table_bloat_analysis**
  - "Analyze table bloat in the current database."
  - "Show dead tuple ratios and bloat estimates for user_logs table pattern."
  - "Find tables with high bloat that need VACUUM maintenance."
  - "Analyze bloat in specific schema with minimum 100 dead tuples."
  - "Analyze table bloat in the public schema."
  - "Show tables with high dead tuple ratios in ecommerce database."
  - "Find tables requiring VACUUM maintenance."
  - "Check bloat for tables with more than 5000 dead tuples."
  - "Identify bloated tables in inventory database public schema."
  - "Show estimated bloat sizes and VACUUM recommendations."
  - 📋 **Features**: Dead tuple ratios, bloat size estimates, VACUUM recommendations, pattern filtering
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
  - 💡 **Usage**: Extension-Independent approach using pg_stat_user_tables
- **get_database_bloat_overview**
  - "Show database-wide bloat summary by schema."
  - "Get high-level view of storage efficiency across all schemas."
  - "Identify schemas requiring maintenance attention."
  - "Get bloat overview for inventory database."
  - "Identify schemas with highest bloat ratios."
  - "Database maintenance planning with bloat statistics."
  - "Compare bloat across all schemas in ecommerce database."
  - "Show total estimated bloat and schema sizes."
  - 📋 **Features**: Schema-level aggregation, total bloat estimates, maintenance status
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
- **get_autovacuum_status**
  - "Check autovacuum configuration and trigger conditions."
  - "Show tables needing immediate autovacuum attention."
  - "Analyze autovacuum threshold percentages for public schema."
  - "Find tables approaching autovacuum trigger points."
  - "Analyze autovacuum threshold percentages for all tables."
  - "Find tables approaching autovacuum trigger points in public schema."
  - "Check autovacuum status for log tables pattern."
  - "Show autovacuum urgency analysis for ecommerce database."
  - "Monitor tables with dead tuple ratios near autovacuum thresholds."
  - 📋 **Features**: Trigger threshold analysis, urgency classification, configuration status
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
  - 💡 **Usage**: Extension-Independent autovacuum monitoring using pg_stat_user_tables
- **get_autovacuum_activity**
  - "Show autovacuum activity patterns for the last 48 hours."
  - "Monitor autovacuum execution frequency and timing."
  - "Find tables with irregular autovacuum patterns."
  - "Analyze recent autovacuum and autoanalyze history."
  - "Check autovacuum activity in public schema for last 7 days."
  - "Show tables with no recent autovacuum activity."
  - "Monitor autovacuum workload distribution across schemas."
  - "Identify tables with very high autovacuum frequency."
  - 📋 **Features**: Activity patterns, execution frequency, timing analysis
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
  - 💡 **Usage**: Historical autovacuum pattern analysis
- **get_running_vacuum_operations**
  - "Show currently running VACUUM and ANALYZE operations."
  - "Monitor active maintenance operations and their progress."
  - "Check if any VACUUM operations are blocking queries."
  - "Find long-running maintenance operations."
  - "Find long-running maintenance operations in ecommerce database."
  - "Show real-time status of all running maintenance operations."
  - "Monitor VACUUM FULL operations and their impact level."
  - 📋 **Features**: Real-time operation status, elapsed time, impact level, process details
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
  - 💡 **Usage**: Real-time maintenance monitoring using pg_stat_activity
- **get_vacuum_effectiveness_analysis**
  - "Analyze VACUUM effectiveness and maintenance patterns."
  - "Compare manual VACUUM vs autovacuum efficiency."
  - "Find tables with suboptimal maintenance patterns."
  - "Check VACUUM frequency vs table activity ratios."
  - "Find tables with suboptimal maintenance patterns in public schema."
  - "Identify tables with poor maintenance effectiveness."
  - "Show maintenance pattern analysis for all schemas."
  - "Find tables that need better VACUUM scheduling."
  - 📋 **Features**: Maintenance pattern analysis, effectiveness assessment, DML-to-VACUUM ratios
  - 🔧 **PostgreSQL 12-18**: Fully compatible, no extensions required
  - 💡 **Usage**: Strategic VACUUM analysis using existing statistics
- **get_lock_monitoring**
  - "Show all current locks and blocked sessions."
  - "Show only blocked sessions with granted=false filter."
  - "Monitor locks by specific user with username filter."
  - "Check exclusive locks with mode filter."
- **get_wal_status**
  - "Show WAL status and archiving information."
  - "Monitor WAL generation and current LSN position."
- **get_replication_status**
  - "Check replication connections and lag status."
  - "Monitor replication slots and WAL receiver status."
  - "Show replication status (version-compatible for PG12-18)."
  - "Check if replication is active or standby servers are connected."
- **get_database_stats**
  - "Show comprehensive database performance metrics."
  - "Analyze transaction commit ratios and I/O statistics."
  - "Monitor buffer cache hit ratios and temporary file usage."
  - "Show database-wide performance statistics."
  - "Analyze transaction commit ratios across all databases."
  - "Check buffer cache hit ratios and I/O statistics."
  - "Monitor temporary file usage and deadlock counts."
- **get_bgwriter_stats**
  - "Analyze checkpoint performance and timing."
  - "Show me checkpoint performance."
  - "Show background writer efficiency statistics."
  - "Monitor buffer allocation and fsync patterns."
  - "Show background writer efficiency and buffer statistics."
  - "Monitor checkpoint scheduling patterns."
  - "Check buffer allocation and fsync performance."
  - "Monitor checkpoint scheduling vs requested ratios."
- **get_user_functions_stats**
  - "Analyze user-defined function performance."
  - "Show function call counts and execution times."
  - "Identify performance bottlenecks in custom functions."
  - "Identify slow or frequently called functions."
  - "Monitor function performance bottlenecks."
  - ⚠️ **Requires**: `track_functions = pl` in postgresql.conf
- **get_table_io_stats**
  - "Analyze table I/O performance and buffer hit ratios."
  - "Identify tables with poor buffer cache performance."
  - "Monitor TOAST table I/O statistics."
  - "Show disk reads vs buffer cache hits for tables in public schema."
  - 💡 **Enhanced with**: `track_io_timing = on` for accurate timing
- **get_index_io_stats**
  - "Show index I/O performance and buffer efficiency."
  - "Identify indexes causing excessive disk I/O."
  - "Monitor index cache-friendliness patterns."
  - "Analyze index buffer hit ratios in specific schema."
  - 💡 **Enhanced with**: `track_io_timing = on` for accurate timing
- **get_database_conflicts_stats**
  - "Check replication conflicts on standby servers."
  - "Analyze conflict types and resolution statistics."
  - "Monitor standby server query cancellation patterns."
  - "Monitor WAL generation and current LSN position."
  - "Check replication conflicts on standby server."
  - "Monitor replication slots and WAL receiver status."
- **get_replication_status**
  - "Check replication connections and lag status."
  - "Monitor replication slots and WAL receiver status."

### 🚀 Version-Aware Tools (Auto-Adapting)

- **get_io_stats** (New!)
  - "Show comprehensive I/O statistics." (PostgreSQL 16+ provides detailed breakdown)
  - "Analyze I/O statistics."
  - "Analyze buffer cache efficiency and I/O timing."
  - "Monitor I/O patterns by backend type and context."
  - 📈 **PG16+**: Full pg_stat_io with timing, backend types, and contexts
  - 📊 **PG12-15**: Basic pg_statio_* fallback with buffer hit ratios
  - 📈 **PG18+**: Additional byte-level I/O columns (`read_bytes`, `write_bytes`, `extend_bytes`) for precise I/O size tracking
- **get_bgwriter_stats** (Enhanced!)
  - "Show background writer and checkpoint performance."
  - 📈 **PG17+**: Separate checkpointer and bgwriter statistics via `pg_stat_checkpointer`
  - 📊 **PG12-16**: Combined bgwriter stats (includes checkpointer data)
  - 📈 **PG18+**: Additional completed_checkpoints and slru_buffers_written columns
- **get_server_info** (Enhanced!)
  - "Show server version and compatibility features."
  - "Check server compatibility."
  - "Check what MCP tools are available on this PostgreSQL version."
  - "Displays feature availability matrix and upgrade recommendations."
- **get_all_tables_stats** (Enhanced!)
  - "Show comprehensive statistics for all tables." (version-compatible for PG12-18)
  - "Include system tables with include_system=true parameter."
  - "Analyze table access patterns and maintenance needs."
  - "Show comprehensive statistics for all user tables (version-compatible for PG12-18)."
  - "Include system tables in statistics analysis with include_system=true."
  - "Monitor dead tuple ratios and table activity."
  - "Show insertions since vacuum statistics (PG13+ only)."
  - 📈 **PG13+**: Tracks insertions since vacuum (`n_ins_since_vacuum`) for optimal maintenance scheduling
  - 📊 **PG12**: Compatible mode with NULL for unsupported columns
  - 📈 **PG18+**: VACUUM time columns (`total_vacuum_time`, `total_autovacuum_time`, `total_analyze_time`, `total_autoanalyze_time`) for maintenance duration tracking
- **get_wait_events** (New!)
  - "Show wait event types and descriptions."
  - "What wait events are available on this PostgreSQL version?"
  - "List available wait event types and their descriptions."
  - "Show wait events filtered by Lock type."
  - 📈 **PG17+**: Native `pg_wait_events` catalog with full descriptions
  - 📊 **PG12-16**: Fallback to `pg_stat_activity` current waits grouped by type
- **get_wal_summarizer_status** (New! PG 17+)
  - "Show WAL summarizer status for incremental backups."
  - "Monitor WAL summarization progress."
  - "Monitor WAL summarizer status for incremental backups."
  - "Check WAL summarizer progress and configuration."
  - 📈 **PG17+**: WAL summarizer monitoring via `pg_get_wal_summarizer_state()`
  - ❌ **PG12-16**: Not available (returns informational message)
- **get_async_io_status** (New! PG 18+)
  - "Show async I/O subsystem status."
  - "Monitor pg_aios for async I/O operations."
  - "Monitor async I/O subsystem status."
  - "Show pg_aios view for I/O operation monitoring."
  - 📈 **PG18+**: `pg_aios` view for async I/O monitoring
  - ❌ **PG12-17**: Not available (returns informational message)
- **get_per_backend_io_stats** (New! PG 18+)
  - "Show per-backend I/O and WAL statistics."
  - "Analyze I/O patterns by individual backend process."
  - 📈 **PG18+**: Per-backend I/O stats with WAL statistics
  - ❌ **PG12-17**: Not available (returns informational message)

### 🟡 Extension-Dependent Tools

- **get_pg_stat_statements_top_queries** (Requires `pg_stat_statements`)
  - "Show top 10 slowest queries."
  - "Analyze slow queries in the inventory database."
  - "Analyze slow queries in the ecommerce database."
  - "Show top 20 slowest queries."
  - "Find queries consuming most resources."
  - "Monitor query execution patterns."
  - "Show cache hit ratios for queries."
  - 📈 **Version-Compatible**: PG12 uses `total_time` → `total_exec_time` mapping; PG13+ uses native columns
  - 💡 **Cross-Version**: Automatically adapts query structure for PostgreSQL 12-18 compatibility
  - 📈 **PG17+ / PG18+**: Adds `stats_since` (PG17+) and `parallel_workers` (PG18+)
- **get_pg_stat_monitor_recent_queries** (Optional, uses `pg_stat_monitor`)
  - "Show recent queries in real time."
  - "Monitor query activity for the last 5 minutes."
  - "Monitor recent 15 queries with detailed stats."
  - "Track recent queries in ecommerce database."
  - 📈 **Version-Compatible**: PG12 uses `total_time` → `total_exec_time` mapping; PG13+ uses native columns
  - 💡 **Cross-Version**: Automatically adapts query structure for PostgreSQL 12-18 compatibility

### 🔧 Advanced Usage Examples

**Multi-Database Analysis**
- "Compare table sizes across databases."
- "Monitor performance across multiple databases using database_name parameter."
- "Analyze slow queries in specific database."
- "Check index efficiency in specific database."

**Schema Analysis**
- "Show detailed schema for products table with all constraints in ecommerce database."
- "Analyze table relationships and foreign key dependencies for sales.orders table in ecommerce database."
- "Get complete column information with data types for inventory_items table in inventory database."
- "Show all indexes and constraints for employees table in hr_system database."
- "Review schema structure for all tables in sales schema of ecommerce database."
- "Compare table schemas across different databases."

**Performance Deep Dive**
- "Find unused indexes."
- "Analyze recent query activity."
- "Identify performance bottlenecks."
- "Analyze cache hit ratios."
- "Monitor query execution patterns."

**Capacity Planning**
- "Analyze database sizes and growth trends."
- "Identify largest tables and indexes."
- "Review disk usage by schema."
- "Plan storage capacity requirements."

**Configuration Troubleshooting**
- "Search for connection-related settings."
- "Find logging configuration."
- "Check specific parameter."

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

---

## Troubleshooting

### Connection Issues
1. Check PostgreSQL server status
2. Verify connection parameters in `.env` file
3. Ensure network connectivity
4. Check user permissions

### Extension Errors
1. Run `get_server_info` to check extension status
2. Install missing extensions:
   ```sql
   CREATE EXTENSION pg_stat_statements;
   CREATE EXTENSION pg_stat_monitor;
   ```
3. Restart PostgreSQL if needed

### OpenWebUI Issues
1. **`Function tools with reasoning_effort are not supported for <model> in
   /v1/chat/completions`**: Some OpenAI reasoning models reject requests that
   combine tool calling (required for MCP tools) with a `reasoning_effort`
   value on the Chat Completions API.

   **Enabled by default**: `.env.example` sets this OpenWebUI global default,
   which applies `reasoning_effort=none` to every model:
   ```dotenv
   DEFAULT_MODEL_PARAMS={"reasoning_effort":"none"}
   ```
   OpenWebUI merges global default model params first, then per-model params,
   then per-chat params (later values win). If you still see the error, check:
   - **`.env` created before this setting was added**: Add the line above to
     `.env`.
   - **Existing `open-webui-data` volume**: The value is stored in the
     OpenWebUI database only on first startup, and the stored value takes
     precedence afterward. Set it in the Admin UI instead: in **Settings →
     Admin → Models**, open the default model settings and set **Reasoning
     Effort** to `none` under Advanced Params.
   - **Per-model or per-chat override**: Set **Reasoning Effort** to `none`
     in **Settings → Admin → Models → \<model\> → Advanced Params** or in the
     chat's **Controls → Advanced Params**.

   > **Note:** The global default sends `reasoning_effort` to every model.
   > Models or providers that do not support this parameter may reject the
   > request. In that case, remove the line from `.env` and set Reasoning
   > Effort per model instead.
2. **`Unsupported parameter: 'reasoning_effort' ... moved to
   'reasoning.effort'`**: OpenWebUI 0.13.0 sent the Chat Completions parameter
   `reasoning_effort` (even `none`) through an OpenAI connection with **API
   Type: Responses**. Set **API Type** to **Chat Completions** under **Settings
   → Admin → Connections → edit the OpenAI connection** (Quick Start default),
   or keep **Responses** and set Reasoning Effort to **Default** in every scope
   as described in [OpenAI reasoning models](#openai-reasoning-models). Then
   start a new chat.

### OpenAI reasoning models

For OpenAI reasoning models, the connection's **API Type** and the **Reasoning
Effort** parameter must match. Set **API Type** under **Settings → Admin →
Connections → edit the OpenAI connection**, and use one of these combinations:

| Setup | API Type | Reasoning Effort | Result |
| --- | --- | --- | --- |
| Quick Start default | **Chat Completions** | `none` (from `DEFAULT_MODEL_PARAMS`) | Tools work; model reasoning is off |
| Reasoning with tools | **Responses** | **Default** in every scope | Tools work; the model uses its default reasoning effort |

Other combinations fail with OpenWebUI 0.13.0:

- **Responses** with any `reasoning_effort` value, including `none`: OpenWebUI
  sends the Chat Completions parameter `reasoning_effort` unchanged, and the
  Responses API rejects it with `Unsupported parameter: 'reasoning_effort'`.
- **Chat Completions** with a reasoning effort other than `none`: the API
  rejects the request with `Function tools with reasoning_effort are not
  supported`.

To use model reasoning instead, set `DEFAULT_MODEL_PARAMS={}` before the first
start, set the connection's **API Type** to **Responses**, and leave
**Reasoning Effort** at **Default** under **Settings → Admin → Models → Model
Defaults** and **Settings → Admin → Models → _model_ → Advanced Params**.
Remove any `reasoning_effort` or `reasoning` override from the chat's
**Controls → Advanced Params** and **Settings → General → Advanced
Parameters**, then start a new chat. OpenWebUI can merge parameters from these
scopes, so an old override can still make the request fail. If the
`open-webui-data` volume already exists, clear `reasoning_effort` under
**Model Defaults** in the administrator UI, because the saved database value
takes precedence over `.env`.

Models and providers without this constraint may keep their normal API type
and reasoning settings.

### Configuration Issues
1. **"No data found" for function statistics**: Check `track_functions` setting
   ```sql
   SHOW track_functions;  -- Should be 'pl' or 'all'
   ```
   
   **Quick fix for managed services (AWS RDS, etc.)**:
   ```sql
   ALTER SYSTEM SET track_functions = 'pl';
   SELECT pg_reload_conf();
   ```

2. **Missing I/O timing data**: Enable timing collection
   ```sql
   SHOW track_io_timing;  -- Should be 'on'
   ```
   
   **Quick fix**:
   ```sql
   ALTER SYSTEM SET track_io_timing = 'on';
   SELECT pg_reload_conf();
   ```

3. **Apply configuration changes**:
   - **Self-managed**: Add settings to `postgresql.conf` and restart server
   - **Managed services**: Use `ALTER SYSTEM SET` + `SELECT pg_reload_conf()`
   - **Temporary testing**: Use `SET parameter = value` for current session
   - Generate some database activity to populate statistics

### Performance Issues
1. Use `limit` parameters to reduce result size
2. Run monitoring during off-peak hours
3. Check database load before running analysis

### Version Compatibility Issues

> For more details, see the [## Tool Compatibility Matrix](#tool-compatibility-matrix)

1. **Run compatibility check first**:
   ```bash
   # "Use get_server_info to check version and available features"
   ```

2. **Understanding feature availability**:
   - **PostgreSQL 18**: All features including async I/O, VACUUM timing, per-backend stats
   - **PostgreSQL 17**: Separate checkpointer stats, wait events, WAL summarizer
   - **PostgreSQL 16**: pg_stat_io view
   - **PostgreSQL 14+**: Parallel query tracking
   - **PostgreSQL 12-13**: Core functionality only

3. **If a tool shows "Not Available"**:
   - Feature requires newer PostgreSQL version
   - Tool will automatically use best available alternative
   - Consider upgrading PostgreSQL for enhanced monitoring

---

## Development

### Testing & Development

```bash
# Clone and setup for development
git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops
uv sync

# Test with MCP Inspector (loads .env automatically)
./run-mcp-inspector-local.sh

# Direct execution methods:
# 1. Using uv run (recommended for development)
uv run mcp-postgresql-ops --log-level DEBUG

# 2. Module execution (requires PYTHONPATH)
PYTHONPATH=src python -m mcp_postgresql_ops --log-level DEBUG

# 3. After installation
mcp-postgresql-ops --log-level DEBUG

# Test version compatibility (requires different PostgreSQL versions)
# Modify POSTGRES_HOST in .env to point to different versions
```

### Running Tests

The following test suites are available:

| Suite | File | Requires Docker |
|-------|------|----------------|
| Unit tests (version logic, tool schemas, generated SQL, release scripts) | every `tests/test_*.py` except `test_tools_integration.py` | No |
| Integration tests (all tools × PG 12–18) | `tests/test_tools_integration.py` | Yes |

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

```bash
# Run all tests (unit + integration) — Docker is managed automatically
uv run pytest -v

# Unit tests only (no Docker or PostgreSQL needed; same command as CI)
uv run pytest tests --ignore=tests/test_tools_integration.py -v

# Tool argument schema tests only (no Docker or PostgreSQL needed)
uv run pytest tests/test_tool_schema_null_defaults.py -v

# Integration tests only
uv run pytest tests/test_tools_integration.py -v
```

> **Note**: Docker must be running for integration tests. The test stack uses ports 5412–5418 (PG 12–18).

### Version Compatibility Testing

The MCP server automatically adapts to PostgreSQL versions 12-18. To test across versions:

1. **Set up test databases**: Different PostgreSQL versions (12, 14, 15, 16, 17, 18)
2. **Run compatibility tests**: Point to each version and verify tool behavior
3. **Check feature detection**: Ensure proper version detection and feature availability
4. **Verify fallback behavior**: Confirm graceful degradation on older versions

---

## Security Notes

- All tools are **read-only** - no data modification capabilities
- Sensitive information (passwords) are masked in outputs
- No direct SQL execution - only predefined queries
- Follows principle of least privilege

---

## Contributing

🤝 **Got ideas? Found bugs? Want to add cool features?**

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.

**Ways to contribute:**
- 🐛 Report issues or bugs
- 💡 Suggest new PostgreSQL monitoring features
- 📝 Improve documentation 
- 🚀 Submit pull requests
- ⭐ Star the repo if you find it useful!

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

---

## Optional MCPO/OpenAPI endpoint

OpenWebUI 0.13 uses the MCP server directly; MCPO is not required for that
connection. The standard Compose deployment still starts MCPO to preserve the
same deployment layout across this project's sibling MCP repositories. Open
the [MCPO Swagger endpoint](http://localhost:8003/mcp-postgresql-ops/docs), or
omit MCPO with `docker compose up -d mcp-server open-webui`.

MCPO reads the ignored file selected by `MCPO_CONFIG_FILE`. Create it from the
tracked template before starting Compose:

```bash
cp mcp-config.json.http mcp-config.json.http.local
chmod 600 mcp-config.json.http.local
```

When Bearer authentication is enabled, put the same `REMOTE_SECRET_KEY` in
`.env` and in the local file's `Authorization` header. Never place the secret
in the tracked template.

![MCPO Swagger APIs](img/screenshot-swagger-api.png)

---

## 🔐 Security & Authentication

### Bearer Token Authentication

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.

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

#### Configuration

**Enable Authentication:**

```bash
# In .env file
REMOTE_AUTH_ENABLE=true
REMOTE_SECRET_KEY=replace-with-a-strong-random-secret
```

**Or via CLI:**

```bash
# Module method
python -m mcp_postgresql_ops --type streamable-http --auth-enable \
  --secret-key replace-with-a-strong-random-secret

# Script method
mcp-postgresql-ops --type streamable-http --auth-enable \
  --secret-key replace-with-a-strong-random-secret
```

#### Security Levels

1. **stdio mode** (Default): Local-only access, no authentication needed
2. **streamable-http + REMOTE_AUTH_ENABLE=false**: Remote access without authentication ⚠️ **NOT RECOMMENDED for production**
3. **streamable-http + REMOTE_AUTH_ENABLE=true**: Remote access with Bearer token authentication ✅ **RECOMMENDED for production**

#### Client Configuration

When authentication is enabled, MCP clients must include the Bearer token in the Authorization header:

```json
{
  "mcpServers": {
    "mcp-postgresql-ops": {
      "type": "streamable-http",
      "url": "http://your-server:8000/mcp",
      "headers": {
        "Authorization": "Bearer replace-with-a-strong-random-secret"
      }
    }
  }
}
```

#### Security Best Practices

- **Always enable authentication** when using streamable-http mode in production
- **Use strong, randomly generated secret keys** (32+ characters recommended)
- **Use HTTPS** when possible (configure reverse proxy with SSL/TLS)
- **Restrict network access** using firewalls or network policies
- **Rotate secret keys regularly** for enhanced security
- **Monitor access logs** for unauthorized access attempts

#### Error Handling

When authentication fails, the server returns:
- **401 Unauthorized** for missing or invalid tokens
- **Detailed error messages** in JSON format for debugging

---

## 🚀 Adding Custom Tools

This MCP server is designed for easy extensibility. Follow these 4 simple steps to add your own custom tools:

### Step-by-Step Guide

#### 1. **Add Helper Functions (Optional)**

Add reusable data functions to `src/mcp_postgresql_ops/functions.py`:

```python
async def get_your_custom_data(target_database: str = None, limit: int = 20) -> List[Dict[str, Any]]:
    """Your custom data retrieval function."""
    try:
        # Example implementation - adapt to your PostgreSQL needs
        query = """
        SELECT 
            schemaname,
            tablename,
            attname as column_name,
            n_distinct,
            most_common_vals,
            most_common_freqs
        FROM pg_stats 
        WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
        ORDER BY schemaname, tablename, attname
        LIMIT $1
        """
        
        results = await execute_query(query, [limit], database=target_database)
        return results
        
    except Exception as e:
        logger.error(f"Failed to get custom data: {e}")
        raise
```

#### 2. **Create Your MCP Tool**

Add your tool function to `src/mcp_postgresql_ops/mcp_main.py`:

```python
@mcp.tool()
async def get_your_custom_analysis(limit: int = 50, database_name: Optional[str] = None) -> str:
    """
    [Tool Purpose]: Brief description of what your tool does
    
    [Exact Functionality]:
    - Feature 1: Data aggregation and analysis
    - Feature 2: Database monitoring and insights
    - Feature 3: Performance metrics and reporting
    
    [Required Use Cases]:
    - When user asks "your specific analysis request"
    - Your PostgreSQL-specific monitoring needs
    
    Args:
        limit: Maximum results (1-100)
        database_name: Target database name (optional, uses default if not specified)
    
    Returns:
        Formatted analysis results
    """
    try:
        # Always validate input limits
        limit = max(1, min(limit, 100))
        
        # Get your custom data
        results = await get_your_custom_data(target_database=database_name, limit=limit)
        
        if not results:
            return "No data found for custom analysis."
        
        # Format and return results
        return format_table_data(results, f"Custom Analysis Results (Top {len(results)})")
        
    except Exception as e:
        logger.error(f"Failed to get custom analysis: {e}")
        return f"Error: {str(e)}"
```

#### 3. **Update Imports**

Add your helper function to the imports section in `src/mcp_postgresql_ops/mcp_main.py` (around line 30):

```python
from .functions import (
    execute_query,
    execute_single_query,
    format_table_data,
    format_bytes,
    format_duration,
    get_server_version,
    check_extension_exists,
    get_pg_stat_statements_data,
    get_pg_stat_monitor_data,
    sanitize_connection_info,
    get_current_database_name,
    POSTGRES_CONFIG,
    get_your_custom_data,  # Add your new function here
)
```

#### 4. **Document Your Tool**

- **Docstring**: The tool docstring is what the model reads when choosing tools. Describe its purpose, arguments, and when to use or not use it.
- **Example requests**: Add sample requests for the tool to [Example Queries](#example-queries).
- **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.

#### 5. **Test Your Tool**

```bash
# Local testing with MCP Inspector
./run-mcp-inspector-local.sh

# Or test with Docker stack
docker compose up -d
docker compose logs -f mcp-server

# Test with natural language queries:
# "Show me your custom analysis"
# "Get custom analysis for ecommerce database"
# "Analyze custom data with limit 25"
```

### Important Notes

- **Multi-Database Support**: All tools support the optional `database_name` parameter to target specific databases
- **Input Validation**: Always validate `limit` parameters with `max(1, min(limit, 100))`
- **Error Handling**: Return user-friendly error messages instead of raising exceptions
- **Logging**: Use `logger.error()` for debugging while returning clean error messages to users
- **PostgreSQL Compatibility**: Your custom queries should work across PostgreSQL 12-18
- **Extension Dependencies**: If your tool requires specific extensions, check availability with `check_extension_exists()`

### Advanced Patterns

For version-aware queries or extension-dependent features, see existing tools like `get_pg_stat_statements_top_queries` for reference patterns.

That's it! Your custom tool is ready to use with natural language queries through any MCP client.

---

## License
Freely use, modify, and distribute under the **MIT License**.

---

## ⭐ Other Projects

**Other MCP servers by the same author:**

- [MCP-Airflow-API](https://github.com/call518/MCP-Airflow-API)
- [MCP-Ambari-API](https://github.com/call518/MCP-Ambari-API)
- [MCP-OpenStack-API](https://github.com/call518/MCP-OpenStack-API)
- [LogSentinelAI - LLB-Based Log Analyzer](https://github.com/call518/LogSentinelAI)
