MCP-PostgreSQL-Ops

MCP-PostgreSQL-Ops PostgreSQL डेटाबेस संचालन, निगरानी और प्रबंधन के लिए एक पेशेवर MCP सर्वर है। PostgreSQL 12-17 को सपोर्ट करता है, जिसमें व्यापक डेटाबेस विश्लेषण, प्रदर्शन निगरानी और प्राकृतिक भाषा प्रश्नों के माध्यम से बुद्धिमान रखरखाव सिफारिशें शामिल हैं।

GitHub
164

दस्तावेज़

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

MCP Toplist

License: MIT Python Docker Pulls PostgreSQL

Deploy to PyPI with tag PyPI PyPI - Downloads

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.

What can you ask?

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

More examples are available in the screenshots and usage 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.

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

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

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:

    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.

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

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

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 provides a code-level walkthrough.

Installation options

SetupBest forRequirements
MCPB extensionFastest Claude Desktop setupClaude Desktop
uvx --python 3.12 mcp-postgresql-opsExplicit local configurationuv
Docker ComposeOpenWebUI and HTTP MCP clientsDocker Compose
Source checkoutDevelopment and customizationGit, 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 NameExtensions RequiredPG 12PG 13PG 14PG 15PG 16PG 17PG 18System 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✅✅✅✅✅✅✅ Enhancedpg_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✅✅✅✅✅✅✅ Enhancedpg_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 NameExtensions RequiredPG 12PG 13PG 14PG 15PG 16PG 17PG 18Special Features
get_io_stats❌ None✅ Basic✅ Basic✅ Basic✅ Basic✅ Enhanced✅ Enhanced✅ EnhancedPG16+: pg_stat_io support; PG18+: byte columns
get_bgwriter_stats❌ None✅✅✅✅✅✅ Special✅ EnhancedPG17: Separate checkpointer stats; PG18+: num_done, slru_written
get_replication_status❌ None✅ Compatible✅ Enhanced✅ Enhanced✅ Enhanced✅ Enhanced✅ Enhanced✅ EnhancedPG13+: 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✅ EnhancedPG13+: 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✅ NativePG17+: 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 NameRequired ExtensionPG 12PG 13PG 14PG 15PG 16PG 17PG 18Notes
get_pg_stat_statements_top_queriespg_stat_statements✅ Compatible✅ Enhanced✅ Enhanced✅ Enhanced✅ Enhanced✅ Enhanced✅ EnhancedPG12: total_time → total_exec_time; PG13+: native total_exec_time; PG17+: stats_since
get_pg_stat_monitor_recent_queriespg_stat_monitor✅ Compatible✅ Enhanced✅ Enhanced✅ Enhanced✅ Enhanced✅ Enhanced✅ EnhancedPG12: 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

# 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

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:

{
  "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

# 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

{
  "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

# 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

VariableDescriptionDefaultProject Default
PYTHONPATHPython module search path (only needed for development mode)-/app/src
MCPO_CONFIG_FILEIgnored local MCPO configuration mounted by Docker Composemcp-config.json.http.localmcp-config.json.http.local
MCP_LOG_LEVELServer logging verbosity (DEBUG, INFO, WARNING, ERROR)INFOINFO
FASTMCP_TYPEMCP transport protocol (stdio for CLI, streamable-http for web)stdiostreamable-http
FASTMCP_HOSTHTTP server bind address (0.0.0.0 for all interfaces)127.0.0.10.0.0.0
FASTMCP_PORTHTTP server port for MCP communication80008000
REMOTE_AUTH_ENABLEEnable Bearer token authentication for streamable-http mode (Default: false if undefined/null/empty)falsefalse
REMOTE_SECRET_KEYSecret key for Bearer token authentication (required when auth enabled)-empty
PGSQL_VERSIONPostgreSQL major version for Docker image selection1616
PGDATAPostgreSQL data directory inside Docker container (Do not modify)/var/lib/postgresql/data/data/db
POSTGRES_HOSTPostgreSQL server hostname or IP address127.0.0.1host.docker.internal
POSTGRES_PORTPostgreSQL server port number543215432
POSTGRES_USERPostgreSQL connection username (needs read permissions)postgrespostgres
POSTGRES_PASSWORDPostgreSQL user password (supports special characters)changemechangeme
POSTGRES_DBDefault database name for connectionstestdbecommerce
POSTGRES_MAX_CONNECTIONSPostgreSQL max_connections configuration parameter200200
DOCKER_BIND_ADDRESSHost address for published Docker ports127.0.0.1127.0.0.1
DOCKER_EXTERNAL_PORT_OPENWEBUIHost port mapping for Open WebUI container80803003
DOCKER_EXTERNAL_PORT_MCP_SERVERHost port mapping for MCP server container808018003
DOCKER_EXTERNAL_PORT_MCPO_PROXYHost port mapping for MCPO proxy container80008003
DOCKER_INTERNAL_PORT_POSTGRESQLPostgreSQL container internal port54325432

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

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

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

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:

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

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

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

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

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

SetupAPI TypeReasoning EffortResult
Quick Start defaultChat Completionsnone (from DEFAULT_MODEL_PARAMS)Tools work; model reasoning is off
Reasoning with toolsResponsesDefault in every scopeTools 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

    SHOW track_functions;  -- Should be 'pl' or 'all'

    Quick fix for managed services (AWS RDS, etc.):

    ALTER SYSTEM SET track_functions = 'pl';
SELECT pg_reload_conf();

  2. Missing I/O timing data: Enable timing collection

    SHOW track_io_timing;  -- Should be 'on'

    Quick fix:

    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

  1. Run compatibility check first:

    # "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

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

SuiteFileRequires Docker
Unit tests (version logic, tool schemas, generated SQL, release scripts)every tests/test_*.py except test_tools_integration.pyNo
Integration tests (all tools × PG 12–18)tests/test_tools_integration.pyYes

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.

# 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, 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:

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

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

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

Or via CLI:

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

{
  "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:

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:

@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):

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

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