MCP PostgreSQL Operations
This server provides read-only PostgreSQL observability and diagnostics through predefined MCP tools.
Inspect server, database, schema, table, user, connection, and relationship metadata.
Monitor locks, blocked sessions, wait events, WAL, replication, conflicts, and WAL summarizer/async I/O status where supported.
Analyze query performance with pg_stat_statements/pg_stat_monitor, index usage, function stats, and database-wide statistics.
Review capacity, table/index/database sizes, bloat, dead tuples, VACUUM/ANALYZE history, autovacuum status/activity, and running maintenance.
Inspect I/O, buffer cache, bgwriter/checkpointer, table/index I/O, and per-backend I/O on supported PostgreSQL versions.
Filter many tools by database, schema, table pattern, user, lock state/type, limits, and similar parameters while remaining read-only.
Uses Docker containers for PostgreSQL testing environment and deployment of the MCP server components.
Provides comprehensive PostgreSQL database operations, monitoring, and management capabilities including performance analysis, bloat detection, query optimization, vacuum monitoring, schema exploration, and real-time database statistics across PostgreSQL versions 12-17.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@MCP PostgreSQL Operations show me the slowest queries from the last hour"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
MCP-PostgreSQL-Ops
Give Claude and other AI assistants read-only PostgreSQL observability for slow queries, locks, bloat, VACUUM, indexes, replication, WAL, and capacity.
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_statementsandpg_stat_monitoradd 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
.
Related MCP server: MCP-PostgreSQL-Ops
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 schemaMore 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

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.
Open the latest GitHub Release and download
mcp-postgresql-ops-<version>.mcpb.In Claude Desktop, open Settings β Extensions β Advanced settings β Install Extensionβ¦ and select the downloaded file.
Enter the PostgreSQL host, port, user, password, and default database.
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 neededmcp-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 0This 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
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.
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 withUnsupported parameter: 'reasoning_effort'. If theopen-webui-datavolume 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.Under Settings β Admin β Tools β Integrations β External Tool Servers, add a connection of type MCP (Streamable HTTP) with URL
http://mcp-server:8000/mcpand authentication None. This matches the Quick Start defaultREMOTE_AUTH_ENABLE=false; enable and configure Bearer authentication before exposing the server outside the local Docker host.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.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, usedocker 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 -dWhy 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 β> PostgreSQLThe 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
Setup | Best for | Requirements |
MCPB extension | Fastest Claude Desktop setup | Claude Desktop |
| Explicit local configuration |
|
Docker Compose | OpenWebUI and HTTP MCP clients | Docker Compose |
Source checkout | Development and customization | Git, |
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 |
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β Enhanced |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β Enhanced |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
| β None | β | β | β | β | β | β | β |
|
π 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 |
| β None | β Basic | β Basic | β Basic | β Basic | β Enhanced | β Enhanced | β Enhanced | PG16+: |
| β None | β | β | β | β | β | β Special | β Enhanced | PG17: Separate checkpointer stats; PG18+: |
| β None | β Compatible | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | PG13+: |
| β None | β Compatible | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | PG13+: |
| βοΈ Config Required | β | β | β | β | β | β | β | Requires |
| β None | β Fallback | β Fallback | β Fallback | β Fallback | β Fallback | β Native | β Native | PG17+: |
| β None | β | β | β | β | β | β | β | PG17+: WAL summarizer monitoring for incremental backups |
| β None | β | β | β | β | β | β | β | PG18+: |
| β 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 |
|
| β Compatible | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | PG12: |
|
| β Compatible | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | β Enhanced | PG12: |
π Version-Specific Features
PostgreSQL 17
pg_wait_eventsview: Native wait event catalog with descriptions (used byget_wait_events)WAL summarizer: Monitoring for incremental backup support (used by
get_wal_summarizer_status)Replication slot enhancements:
invalidation_reasonandinactive_sincecolumns (used byget_replication_status)pg_stat_statementsstats_since: Track when statistics were last reset (used byget_pg_stat_statements_top_queries)VACUUM progress: Index vacuum tracking in progress views (future enhancement for
get_running_vacuum_operations)
PostgreSQL 18
pg_aiosview: Async I/O subsystem monitoring (used byget_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_timecumulative timing (used byget_vacuum_analyze_stats)pg_stat_iobyte columns:read_bytes,write_bytes,extend_bytes(used byget_io_stats)Parallel worker stats:
parallel_workers_launched,parallel_workers_to_launch(used byget_database_stats)Checkpointer enhancements:
num_done,slru_writtencolumns (used byget_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 8000Run from source
git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops
uv sync
uv run mcp-postgresql-ops --helpThe 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 DEBUGCLI Arguments
--type: Transport type (stdioorstreamable-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 |
| Python module search path (only needed for development mode) | - |
|
| Ignored local MCPO configuration mounted by Docker Compose |
|
|
| Server logging verbosity (DEBUG, INFO, WARNING, ERROR) |
|
|
| MCP transport protocol (stdio for CLI, streamable-http for web) |
|
|
| HTTP server bind address (0.0.0.0 for all interfaces) |
|
|
| HTTP server port for MCP communication |
|
|
| Enable Bearer token authentication for streamable-http mode (Default: |
|
|
| Secret key for Bearer token authentication (required when auth enabled) | - | empty |
| PostgreSQL major version for Docker image selection |
|
|
| PostgreSQL data directory inside Docker container (Do not modify) |
|
|
| PostgreSQL server hostname or IP address |
|
|
| PostgreSQL server port number |
|
|
| PostgreSQL connection username (needs read permissions) |
|
|
| PostgreSQL user password (supports special characters) |
|
|
| Default database name for connections |
|
|
| PostgreSQL max_connections configuration parameter |
|
|
| Host address for published Docker ports |
|
|
| Host port mapping for Open WebUI container |
|
|
| Host port mapping for MCP server container |
|
|
| Host port mapping for MCPO proxy container |
|
|
| PostgreSQL container internal port |
|
|
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 connectionsDOCKER_INTERNAL_PORT_POSTGRESQL=5432: Internal container port (PostgreSQL default)When using external PostgreSQL servers, set
POSTGRES_PORTto 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_statementsis required only for slow query analysis tools.pg_stat_monitoris 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 = plortrack_functions = allget_table_io_stats & get_index_io_stats: More accurate timing with
track_io_timing = onget_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 collectionThen 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=onMethod 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
"Check server status and active connections."
"Show top 10 slowest queries from last hour."
"Verify all databases are accessible."
"Check if any tables need maintenance."
Capacity Planning
"Analyze database sizes and growth trends."
"Identify largest tables and indexes."
"Review disk usage by schema."
"Plan storage capacity requirements."
Performance Optimization
"Find queries consuming most resources."
"Identify unused or inefficient indexes."
"Analyze cache hit ratios."
"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_nameparameter must be specifiedπ Output change (3.5.0+): Referenced tables are schema-qualified (
b.p(id)instead ofp(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_nameparameter 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_nameparameter must be specifiedπ‘ Usage: Leave
table_nameempty 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 = plin 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 = onfor 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 = onfor 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_eventscatalog with full descriptionsπ PG12-16: Fallback to
pg_stat_activitycurrent 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_aiosview 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_timemapping; PG13+ uses native columnsπ‘ Cross-Version: Automatically adapts query structure for PostgreSQL 12-18 compatibility
π PG17+ / PG18+: Adds
stats_since(PG17+) andparallel_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_timemapping; 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
Check PostgreSQL server status
Verify connection parameters in
.envfileEnsure network connectivity
Check user permissions
Extension Errors
Run
get_server_infoto check extension statusInstall missing extensions:
CREATE EXTENSION pg_stat_statements; CREATE EXTENSION pg_stat_monitor;Restart PostgreSQL if needed
OpenWebUI Issues
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 areasoning_effortvalue on the Chat Completions API.Enabled by default:
.env.examplesets this OpenWebUI global default, which appliesreasoning_effort=noneto 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:
.envcreated before this setting was added: Add the line above to.env.Existing
open-webui-datavolume: 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 tononeunder Advanced Params.Per-model or per-chat override: Set Reasoning Effort to
nonein Settings β Admin β Models β <model> β Advanced Params or in the chat's Controls β Advanced Params.
Note: The global default sends
reasoning_effortto every model. Models or providers that do not support this parameter may reject the request. In that case, remove the line from.envand set Reasoning Effort per model instead.Unsupported parameter: 'reasoning_effort' ... moved to 'reasoning.effort': OpenWebUI 0.13.0 sent the Chat Completions parameterreasoning_effort(evennone) 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:
Setup | API Type | Reasoning Effort | Result |
Quick Start default | Chat Completions |
| 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_effortvalue, includingnone: OpenWebUI sends the Chat Completions parameterreasoning_effortunchanged, and the Responses API rejects it withUnsupported parameter: 'reasoning_effort'.Chat Completions with a reasoning effort other than
none: the API rejects the request withFunction 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
"No data found" for function statistics: Check
track_functionssettingSHOW 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();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();Apply configuration changes:
Self-managed: Add settings to
postgresql.confand restart serverManaged services: Use
ALTER SYSTEM SET+SELECT pg_reload_conf()Temporary testing: Use
SET parameter = valuefor current sessionGenerate some database activity to populate statistics
Performance Issues
Use
limitparameters to reduce result sizeRun monitoring during off-peak hours
Check database load before running analysis
Version Compatibility Issues
For more details, see the ## Tool Compatibility Matrix
Run compatibility check first:
# "Use get_server_info to check version and available features"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
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 versionsRunning Tests
The following test suites are available:
Suite | File | Requires Docker |
Unit tests (version logic, tool schemas, generated SQL, release scripts) | every | No |
Integration tests (all tools Γ PG 12β18) |
| 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.
# 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 -vNote: 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:
Set up test databases: Different PostgreSQL versions (12, 14, 15, 16, 17, 18)
Run compatibility tests: Point to each version and verify tool behavior
Check feature detection: Ensure proper version detection and feature availability
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.localWhen 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.

π 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_ENABLEdefaults tofalseif 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-secretOr 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-secretSecurity Levels
stdio mode (Default): Local-only access, no authentication needed
streamable-http + REMOTE_AUTH_ENABLE=false: Remote access without authentication β οΈ NOT RECOMMENDED for production
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}")
raise2. 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.mdonly 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_nameparameter to target specific databasesInput Validation: Always validate
limitparameters withmax(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 usersPostgreSQL 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:
Available Tools
37 toolsget_active_connectionsA
[Tool Purpose]: Retrieve all active connections and session information on current PostgreSQL server
[Exact Functionality]:
Retrieve list of all currently active connected sessions
Display user, database, and client address for each connection
Include session status and currently executing query information
[Required Use Cases]:
When user requests "active connections", "current sessions", "connection status", etc.
When server load or performance problem diagnosis is needed
When checking connection status of specific users or applications
[Strictly Prohibited Use Cases]:
Requests for forceful connection termination or session management
Requests for detailed query history of specific sessions
Requests for connection security or authentication-related changes
Returns: Information including PID, username, database name, client address, status, and current query
Inputs: no named parameters. Returns: result
get_all_tables_statsA
[Tool Purpose]: Get comprehensive statistics for all tables (including system tables if requested)
[Exact Functionality]:
Show detailed access statistics for all tables in database
Include sequential scans, index scans, and tuple operations
Provide live/dead tuple estimates and maintenance history
Option to include system catalog tables
[Required Use Cases]:
When user requests "all tables stats", "complete table statistics", etc.
When analyzing overall table usage patterns
When investigating table maintenance needs across the database
When getting comprehensive database activity overview
[Strictly Prohibited Use Cases]:
Requests for table maintenance operations (VACUUM, ANALYZE)
Requests for statistics reset or modification
Requests for table optimization actions
Inputs: database_name, include_system. Returns: result
get_async_io_statusA
[Tool Purpose]: Monitor asynchronous I/O subsystem status (PostgreSQL 18+)
[Exact Functionality]:
Show active async I/O operations from pg_aios view
Display I/O method configuration and concurrency settings
Report async I/O file handle usage
Falls back to I/O configuration info on PG < 18
[Required Use Cases]:
When user requests "async I/O status", "AIO monitoring", "I/O subsystem"
When investigating I/O performance and concurrency
When checking async I/O configuration
[Strictly Prohibited Use Cases]:
Requests for I/O configuration changes
Requests for storage modifications
Inputs: database_name. Returns: result
get_autovacuum_activityA
[Tool Purpose]: Monitor recent autovacuum and autoanalyze activity patterns and execution history
[Exact Functionality]:
Track recent autovacuum and autoanalyze execution patterns
Analyze autovacuum frequency and timing intervals
Show tables with most/least autovacuum activity
Calculate average time between autovacuum executions
Identify tables with irregular autovacuum patterns
[Required Use Cases]:
When user requests "autovacuum activity", "autovacuum history", "vacuum patterns", etc.
When monitoring autovacuum performance and effectiveness
When troubleshooting autovacuum scheduling issues
When analyzing autovacuum workload distribution
[Strictly Prohibited Use Cases]:
Requests for autovacuum process control or restart
Requests for autovacuum configuration modifications
Requests for manual vacuum scheduling
Inputs: limit, hours_back, schema_name, database_name. Returns: result
get_autovacuum_statusA
[Tool Purpose]: Analyze autovacuum configuration and current maintenance status for tables
[Exact Functionality]:
Analyze autovacuum trigger conditions based on dead tuple thresholds
Calculate current dead tuple ratios vs autovacuum trigger points
Show autovacuum configuration settings per table
Identify tables requiring immediate autovacuum attention
Estimate next autovacuum execution likelihood
[Required Use Cases]:
When user requests "autovacuum status", "autovacuum configuration", "vacuum trigger analysis", etc.
When planning autovacuum optimization and tuning
When troubleshooting autovacuum performance issues
When identifying tables with autovacuum problems
[Strictly Prohibited Use Cases]:
Requests for autovacuum configuration changes
Requests for manual VACUUM execution
Requests for autovacuum process restart or control
Inputs: limit, schema_name, database_name, table_pattern. Returns: result
get_bgwriter_statsA
[Tool Purpose]: Analyze background writer and checkpoint performance statistics with version compatibility
[Exact Functionality]:
Show checkpoint execution statistics (timed vs requested)
Display checkpoint timing information (write and sync times)
Provide buffer writing statistics by different processes
Analyze background writer performance and efficiency
Automatically adapts to PostgreSQL version (17+ uses separate checkpointer view)
[Required Use Cases]:
When user requests "checkpoint stats", "bgwriter performance", "buffer stats", etc.
When analyzing I/O performance and checkpoint impact
When investigating background writer efficiency
When troubleshooting checkpoint-related performance issues
[Strictly Prohibited Use Cases]:
Requests for checkpoint execution or configuration changes
Requests for background writer parameter modifications
Requests for statistics reset
Returns: Background writer and checkpoint performance statistics with version-appropriate data
Inputs: no named parameters. Returns: result
get_current_database_infoA
[Tool Purpose]: Get information about the current database connection
[Exact Functionality]:
Show the name of the currently connected database
Display database-specific information like encoding, locale, and size
Provide connection context for clarity in multi-database environments
[Required Use Cases]:
When user asks "what database am I connected to?", "current database", etc.
When clarifying database context for analysis operations
When troubleshooting connection issues or confirming target database
[Strictly Prohibited Use Cases]:
Requests for database structure changes or creation/deletion
Requests for user authentication or permission changes
Requests for configuration modifications
Inputs: database_name. Returns: result
get_database_bloat_overviewA
[Tool Purpose]: Provide database-wide bloat overview and summary statistics
[Exact Functionality]:
Summarize bloat statistics across all schemas
Identify schemas and tables with highest bloat ratios
Calculate total estimated bloat size per schema
Show aggregate dead tuple counts and maintenance status
[Required Use Cases]:
When user requests "database bloat overview", "bloat summary", etc.
When getting high-level view of database storage efficiency
When planning database maintenance activities
When investigating overall database performance issues
[Strictly Prohibited Use Cases]:
Requests for automatic maintenance operations
Requests for bloat cleanup or removal
Requests for schema or database restructuring
Inputs: limit, database_name. Returns: result
get_database_conflicts_statsA
[Tool Purpose]: Analyze query conflicts in standby/replica database environments
[Exact Functionality]:
Show conflict statistics for standby servers (only relevant on replicas)
Display conflicts by type (tablespace, lock, snapshot, bufferpin, deadlock)
Help diagnose replication-related performance issues
Provide conflict resolution statistics
[Required Use Cases]:
When user requests "replication conflicts", "standby conflicts", etc.
When analyzing replica server performance issues
When troubleshooting replication lag or conflicts
When monitoring standby server health
[Strictly Prohibited Use Cases]:
Requests for conflict resolution actions
Requests for replication configuration changes
Requests for statistics reset
Inputs: database_name. Returns: result
get_database_listA
[Tool Purpose]: Retrieve list of all databases and their basic information on PostgreSQL server
[Exact Functionality]:
Retrieve list of all databases on the server
Display owner, encoding, and size information for each database
Include database connection limit information
[Required Use Cases]:
When user requests "database list", "DB list", "database info", etc.
When need to check what databases exist on the server
When database size or owner information is needed
[Strictly Prohibited Use Cases]:
Requests for tables or schemas inside specific databases
Requests for database creation or deletion
Requests related to user permissions or security
Returns: Table-format information including database name, owner, encoding, size, and connection limit
Inputs: no named parameters. Returns: result
get_database_schema_infoA
[Tool Purpose]: Retrieve detailed information about database schemas (namespaces) and their contents
[Exact Functionality]:
Show all schemas in a database with their owners and permissions
Display schema-level statistics including table count and total size
List all objects (tables, views, functions) within specific schema
Show schema access privileges and usage patterns
[Required Use Cases]:
When user requests "database schema info", "schema overview", "namespace structure", etc.
When analyzing database organization and schema-level permissions
When exploring multi-schema database architecture
[Strictly Prohibited Use Cases]:
Requests for actual data inside tables
Requests for schema structure changes or DDL operations
Requests for individual table details (use get_table_schema_info for that)
Inputs: database_name (required), schema_name. Returns: result
get_database_size_infoA
[Tool Purpose]: Analyze size information and storage usage status of all databases in PostgreSQL server
[Exact Functionality]:
Retrieve disk usage for each database
Analyze overall server storage usage status
Provide database list sorted by size
[Required Use Cases]:
When user requests "database size", "disk usage", "storage space", etc.
When capacity management or cleanup is needed
When resource usage status by database needs to be identified
[Strictly Prohibited Use Cases]:
Requests for data deletion or cleanup operations
Requests for storage configuration changes
Requests related to backup or restore
Returns: Table-format information with database names and size information sorted by size
Inputs: no named parameters. Returns: result
get_database_statsA
[Tool Purpose]: Get comprehensive database-wide statistics and performance metrics
[Exact Functionality]:
Show database-wide transaction statistics (commits, rollbacks)
Display block I/O statistics (disk reads vs buffer hits)
Provide tuple operation statistics (returned, fetched, inserted, updated, deleted)
Show temporary file usage and deadlock counts
Include checksum failure information and I/O timing data
[Required Use Cases]:
When user requests "database statistics", "database performance", "transaction stats", etc.
When analyzing overall database performance and activity
When investigating I/O performance or buffer cache efficiency
When checking for deadlocks or temporary file usage
[Strictly Prohibited Use Cases]:
Requests for statistics reset or modification
Requests for database configuration changes
Requests for performance tuning actions
Returns: Comprehensive database statistics including transactions, I/O, tuples, and performance metrics
Inputs: no named parameters. Returns: result
get_index_io_statsA
[Tool Purpose]: Analyze I/O performance statistics for indexes (disk reads vs buffer cache hits)
[Exact Functionality]:
Show index-level I/O statistics and buffer hit ratios
Identify indexes with poor buffer cache performance
Provide detailed I/O performance metrics per index
Help optimize index and buffer cache usage
[Required Use Cases]:
When user requests "index I/O stats", "index buffer performance", etc.
When analyzing index-level I/O performance
When identifying indexes causing excessive disk I/O
When optimizing index buffer cache efficiency
[Strictly Prohibited Use Cases]:
Requests for index optimization actions
Requests for buffer cache configuration changes
Requests for statistics reset
Inputs: schema_name, database_name. Returns: result
get_index_usage_statsA
[Tool Purpose]: Analyze usage rate and performance statistics of all indexes in database
[Exact Functionality]:
Analyze usage frequency and efficiency of all indexes
Identify unused indexes
Provide scan count and tuple return statistics per index
[Required Use Cases]:
When user requests "index usage rate", "index performance", "unnecessary indexes", etc.
When database performance optimization is needed
When index cleanup or reorganization is required
[Strictly Prohibited Use Cases]:
Requests for index creation or deletion
Requests for index reorganization or REINDEX execution
Requests for statistics reset
Inputs: database_name. Returns: result
get_io_statsA
[Tool Purpose]: Analyze comprehensive I/O statistics across all database operations with version compatibility
[Exact Functionality]:
PostgreSQL 16+: Shows detailed I/O statistics from pg_stat_io (reads, writes, hits, timing)
PostgreSQL 12-15: Falls back to pg_statio_* views with basic I/O information
Provides buffer cache efficiency analysis and I/O timing when available
Identifies I/O patterns and performance bottlenecks
[Required Use Cases]:
When user requests "I/O stats", "I/O performance", "buffer cache analysis", etc.
When analyzing storage performance and buffer efficiency
When identifying I/O bottlenecks across different backend types
When comparing I/O patterns between relation types
[Strictly Prohibited Use Cases]:
Requests for I/O configuration changes or buffer tuning
Requests for storage or filesystem modifications
Requests for I/O statistics reset
Inputs: limit, database_name. Returns: result
get_lock_monitoringA
[Tool Purpose]: Monitor current locks and potential deadlocks in PostgreSQL
[Exact Functionality]:
List all current locks held and waited for by sessions
Show blocked and blocking sessions, lock types, and wait status
Help diagnose lock contention and deadlock risk
Filter results by granted status, state, mode, lock type, or username
[Required Use Cases]:
When user requests "lock monitoring", "deadlock check", "blocked sessions", etc.
When diagnosing performance issues due to locking
When checking for blocked or waiting queries
When filtering specific types of locks or users
[Strictly Prohibited Use Cases]:
Requests for killing sessions or force-unlocking
Requests for lock configuration changes
Requests for historical lock data (only current state is shown)
Inputs: mode, state, granted, locktype, +2 more. Returns: result
get_per_backend_io_statsA
[Tool Purpose]: Analyze per-backend I/O and WAL statistics (PostgreSQL 18+)
Note: Uses pg_stat_get_backend_io() and pg_stat_get_backend_wal() which are new in PG 18.
[Exact Functionality]:
Show I/O statistics broken down by individual backend process
Display per-backend WAL generation statistics
Identify backends with highest I/O activity
Falls back to version notice on PG < 18
[Required Use Cases]:
When user requests "per-backend I/O", "backend I/O stats", "which backend is doing most I/O"
When diagnosing I/O hotspots at the process level
When analyzing per-connection I/O patterns
[Strictly Prohibited Use Cases]:
Requests for backend termination
Requests for I/O configuration changes
Inputs: limit, database_name. Returns: result
get_pg_stat_monitor_recent_queriesA
[Tool Purpose]: Analyze recently executed queries and detailed monitoring information using pg_stat_monitor extension
[Exact Functionality]:
Retrieve detailed performance information of recently executed queries
Display client IP and time bucket information by execution period
Provide more detailed monitoring data than pg_stat_statements
[Required Use Cases]:
When user requests "recent queries", "detailed monitoring", "pg_stat_monitor", etc.
When real-time query performance monitoring is needed
When client-specific or time-based query analysis is required
[Strictly Prohibited Use Cases]:
When pg_stat_monitor extension is not installed
Requests for query execution or data modification
Requests for monitoring configuration changes or data reset
Inputs: limit, database_name. Returns: result
get_pg_stat_statements_top_queriesA
[Tool Purpose]: Analyze top queries that consumed the most time using pg_stat_statements extension
[Exact Functionality]:
Retrieve top query list based on total execution time
Display call count, average execution time, and cache hit rate for each query
Support identification of queries requiring performance optimization
[Required Use Cases]:
When user requests "slow queries", "performance analysis", "top queries", etc.
When database performance optimization is needed
When query performance monitoring or tuning is required
[Strictly Prohibited Use Cases]:
When pg_stat_statements extension is not installed
Requests for query execution or data modification
Requests for statistics data reset or configuration changes
Inputs: limit, database_name. Returns: result
get_postgresql_configA
[Tool Purpose]: Retrieve and analyze PostgreSQL server configuration parameter values
[Exact Functionality]:
Retrieve all PostgreSQL configuration parameters (when config_name is not specified)
Retrieve current value and description of specific configuration parameter
Filter configurations by text pattern (when filter_text is specified)
Display whether configuration can be changed and if restart is required
[Required Use Cases]:
When user requests "PostgreSQL config", "config", "parameters", etc.
When checking specific configuration values is needed
When searching for configurations containing specific text
When configuration status identification is needed for performance tuning
[Strictly Prohibited Use Cases]:
Requests for configuration value changes or modifications
Requests for PostgreSQL restart or reload
Requests for system-level configuration changes
Inputs: config_name, filter_text. Returns: result
get_replication_statusA
[Tool Purpose]: Monitor PostgreSQL replication status and statistics
[Exact Functionality]:
Show current replication connections and their status
Display replication lag information for standbys
Monitor WAL sender and receiver processes
Provide replication slot information and statistics
[Required Use Cases]:
When user requests "replication status", "standby lag", "replication monitoring", etc.
When diagnosing replication issues or performance problems
When checking replication slot usage and lag
[Strictly Prohibited Use Cases]:
Requests for replication configuration changes
Requests for replication slot creation or deletion
Requests for failover or switchover operations
Returns: Replication status including connections, lag information, slots, and statistics
Inputs: no named parameters. Returns: result
get_running_vacuum_operationsA
[Tool Purpose]: Monitor currently running VACUUM and ANALYZE operations in real-time
[Exact Functionality]:
Show all currently active VACUUM, ANALYZE, and REINDEX operations
Display operation progress, elapsed time, and process details
Identify blocking or long-running maintenance operations
Provide process IDs for operation tracking
[Required Use Cases]:
When user requests "running VACUUM", "active maintenance", "current VACUUM status", etc.
When troubleshooting slow or stuck VACUUM operations
When monitoring maintenance operation progress
When identifying maintenance operations that may be affecting performance
[Strictly Prohibited Use Cases]:
Requests for terminating or controlling VACUUM processes
Requests for starting new VACUUM operations
Requests for changing VACUUM parameters or configuration
Inputs: database_name. Returns: result
get_server_infoA
[Tool Purpose]: Check basic information and connection status of PostgreSQL server
[Exact Functionality]:
Retrieve PostgreSQL server version information
Display connection settings (with password masking)
Verify server accessibility
Check installation status of extensions (pg_stat_statements, pg_stat_monitor)
[Required Use Cases]:
When user requests "server info", "PostgreSQL status", "connection check", etc.
When basic database server information is needed
When preliminary check is needed before using monitoring tools
[Strictly Prohibited Use Cases]:
Requests for specific data or table information
Requests for performance statistics or monitoring data
Requests for configuration changes or administrative tasks
Returns: Comprehensive information including server version, connection info, and extension status
Inputs: no named parameters. Returns: result
get_table_bloat_analysisA
[Tool Purpose]: Analyze table bloat based on dead tuple statistics and size information
[Exact Functionality]:
Calculate bloat ratio based on dead tuples vs total tuples
Estimate bloat size in bytes and human-readable format
Show last VACUUM/AUTOVACUUM timestamps for maintenance tracking
Identify tables requiring VACUUM maintenance
Filter tables by name pattern using SQL LIKE or ILIKE matching
Sort results by bloat severity (dead tuple ratio and count)
[Required Use Cases]:
When user requests "table bloat", "bloat analysis", "dead tuples", etc.
When identifying tables that need VACUUM maintenance
When investigating database storage efficiency and space usage
When troubleshooting performance issues related to table bloat
When analyzing specific table groups (e.g., tables with "user", "log", "temp" in names)
[Strictly Prohibited Use Cases]:
Requests for automatic VACUUM execution
Requests for bloat removal or cleanup operations
Requests for table restructuring or data modification
Inputs: limit, schema_name, database_name, table_pattern, +1 more. Returns: result
get_table_io_statsA
[Tool Purpose]: Analyze I/O performance statistics for tables (disk reads vs buffer cache hits)
[Exact Functionality]:
Show heap, index, and TOAST table I/O statistics
Calculate buffer hit ratios for performance analysis
Identify tables with poor buffer cache performance
Provide detailed I/O breakdown by table component
[Required Use Cases]:
When user requests "table I/O stats", "buffer performance", "disk vs cache", etc.
When analyzing table-level I/O performance
When identifying tables causing excessive disk I/O
When optimizing buffer cache efficiency
[Strictly Prohibited Use Cases]:
Requests for I/O optimization actions
Requests for buffer cache configuration changes
Requests for statistics reset
Inputs: schema_name, database_name. Returns: result
get_table_listA
[Tool Purpose]: Retrieve list of all tables and their information from specified database (or current DB)
[Exact Functionality]:
Retrieve list of all tables in specified database
Display schema, owner, and size information for each table
Distinguish table types (regular tables, views, etc.)
[Required Use Cases]:
When user requests "table list", "table listing", "schema info", etc.
When need to understand structure of specific database
When table size or owner information is needed
[Strictly Prohibited Use Cases]:
Requests for data inside tables
Requests for table structure changes or creation/deletion
Requests for detailed column information of specific tables
Inputs: database_name. Returns: result
get_table_relationshipsA
[Tool Purpose]: Analyze table relationships including foreign keys, dependencies, and inheritance
[Exact Functionality]:
Show foreign key relationships (inbound and outbound)
Display view dependencies and table references
Analyze inheritance and partition relationships
Identify orphaned tables and relationship patterns
[Required Use Cases]:
When user requests "table relationships", "foreign keys", "dependencies", etc.
When analyzing database schema design and data model
When planning data migration or schema changes
[Strictly Prohibited Use Cases]:
Requests for actual data inside tables
Requests for relationship modifications or DDL operations
Requests for performance statistics (use other tools for that)
Inputs: database_name (required), table_name, schema_name, relationship_type. Returns: result
get_table_schema_infoA
[Tool Purpose]: Retrieve detailed schema information for specific table or all tables in a database
[Exact Functionality]:
Retrieve detailed column information including data types, constraints, defaults
Display primary keys, foreign keys, indexes, and other table constraints
Show table-level metadata such as size, row count estimates
[Required Use Cases]:
When user requests "table schema", "column info", "table structure", etc.
When detailed table design information is needed for development
When analyzing database structure and relationships
[Strictly Prohibited Use Cases]:
Requests for actual data inside tables
Requests for table structure changes or DDL operations
Requests for performance statistics (use other tools for that)
Inputs: database_name (required), table_name, schema_name. Returns: result
get_table_size_infoA
[Tool Purpose]: Analyze size information and index usage of all tables in specified schema
[Exact Functionality]:
Retrieve size information of all tables within schema
Analyze index size and total size per table
Provide table list sorted by size
[Required Use Cases]:
When user requests "table size", "schema capacity", "index usage", etc.
When storage analysis of specific schema is needed
When resource usage status per table needs to be identified
[Strictly Prohibited Use Cases]:
Requests for table data deletion or cleanup operations
Requests for index creation or deletion
Requests for table structure changes
Inputs: schema_name, database_name. Returns: result
get_user_functions_statsA
[Tool Purpose]: Analyze performance statistics for user-defined functions
[Exact Functionality]:
Show execution count and timing statistics for user functions
Calculate average execution time per function call
Identify performance bottlenecks in user-defined functions
Provide total and self execution time breakdown
[Required Use Cases]:
When user requests "function stats", "function performance", etc.
When analyzing user-defined function performance
When identifying slow or frequently called functions
When optimizing application function usage
[Strictly Prohibited Use Cases]:
Requests for function modification or optimization
Requests for statistics reset
Requests for function execution or testing
Inputs: database_name. Returns: result
get_user_listA
[Tool Purpose]: Retrieve list of all user accounts and permission information on PostgreSQL server
[Exact Functionality]:
Retrieve list of all database user accounts
Display permission information for each user (superuser, database creation rights, etc.)
Include account creation date and expiration date information
[Required Use Cases]:
When user requests "user list", "account info", "permission check", etc.
When user permission management or security inspection is needed
When account status overview is needed
[Strictly Prohibited Use Cases]:
Requests for user password information
Requests for user creation, deletion, or permission changes
Requests for specific user sessions or activity history
Returns: Table-format information including username, superuser status, permissions, and account status
Inputs: no named parameters. Returns: result
get_vacuum_analyze_statsA
[Tool Purpose]: Analyze VACUUM and ANALYZE execution history and statistics per table
[Exact Functionality]:
Retrieve last VACUUM/ANALYZE execution time for each table
Provide Auto VACUUM/ANALYZE execution count statistics
Analyze table activity with tuple insert/update/delete statistics
[Required Use Cases]:
When user requests "VACUUM status", "ANALYZE history", "table statistics", etc.
When database maintenance status overview is needed
When performance issues or statistics update status verification is required
[Strictly Prohibited Use Cases]:
Requests for VACUUM or ANALYZE execution
Requests for Auto VACUUM configuration changes
Requests for forced statistics update
Inputs: database_name. Returns: result
get_vacuum_effectiveness_analysisA
[Tool Purpose]: Analyze VACUUM effectiveness and maintenance patterns using existing statistics
[Exact Functionality]:
Compare manual VACUUM vs autovacuum effectiveness patterns
Analyze VACUUM frequency vs table activity (DML operations)
Identify tables with suboptimal VACUUM patterns
Calculate maintenance efficiency ratios without performance impact
Show VACUUM coverage analysis across all tables
[Required Use Cases]:
When user requests "VACUUM effectiveness", "maintenance efficiency", "VACUUM analysis", etc.
When planning manual VACUUM schedules or autovacuum tuning
When identifying tables with poor maintenance patterns
When analyzing overall database maintenance health
[Strictly Prohibited Use Cases]:
Requests for VACUUM execution or scheduling
Requests for autovacuum configuration changes
Requests for maintenance operation control
Inputs: limit, schema_name, database_name. Returns: result
get_wait_eventsA
[Tool Purpose]: List available wait event types and their descriptions (PostgreSQL 17+)
[Exact Functionality]:
Show all wait event types with human-readable descriptions from pg_wait_events catalog
Correlate with current active sessions waiting on events
Filter by specific wait event type
Falls back to basic pg_stat_activity wait info on PG < 17
[Required Use Cases]:
When user requests "wait events", "wait event descriptions", "what are sessions waiting on"
When diagnosing session waits and understanding wait event meanings
When analyzing wait patterns across the database
[Strictly Prohibited Use Cases]:
Requests for session termination or wait resolution
Requests for configuration changes
Inputs: database_name, wait_event_type. Returns: result
get_wal_statusA
[Tool Purpose]: Monitor WAL (Write Ahead Log) status and statistics
[Exact Functionality]:
Show current WAL location and LSN information
Display WAL file generation rate and size statistics
Monitor WAL archiving status and lag
Provide WAL-related configuration and activity metrics
[Required Use Cases]:
When user requests "WAL status", "WAL monitoring", "log shipping status", etc.
When diagnosing replication lag or WAL archiving issues
When monitoring database write activity and WAL generation
[Strictly Prohibited Use Cases]:
Requests for WAL configuration changes
Requests for manual WAL switching or archiving
Requests for WAL file manipulation or cleanup
Returns: WAL status information including current LSN, WAL files, archiving status, and statistics
Inputs: no named parameters. Returns: result
get_wal_summarizer_statusA
[Tool Purpose]: Monitor WAL summarizer status for incremental backup support (PostgreSQL 17+)
[Exact Functionality]:
Show WAL summarizer process state and progress
Display available WAL summaries for incremental backups
Report summarize_wal configuration status
Falls back to version notice on PG < 17
[Required Use Cases]:
When user requests "WAL summarizer", "incremental backup status", "WAL summary"
When checking incremental backup readiness
When monitoring WAL summarization progress
[Strictly Prohibited Use Cases]:
Requests for WAL summarizer configuration changes
Requests for backup execution
Inputs: database_name. Returns: result
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelogβ¦
TDQS
Scored across 37 tools
Most tools target distinct PostgreSQL monitoring areas, but there are several overlapping clusters: table/database stats vs. table size/list/all_tables_stats, the I/O family, and the vacuum/autovacuum/bloat family. The detailed descriptions help clarify scope, but an agent may still need to compare descriptions carefully to pick the right diagnostic.
All tool names follow a consistent snake_case get_* pattern, such as get_replication_status, get_table_list, and get_wait_events. There are no mixed casing styles or conflicting verb conventions.
With 37 tools, the set is very large for a monitoring-oriented server. While many correspond to real PostgreSQL views, several could be consolidated or grouped, and the volume increases selection burden and the chance of choosing an overlapping diagnostic.
The surface covers a wide monitoring domain: replication, WAL, locks, connections, schemas, sizes, query statistics, vacuum/bloat, I/O, configuration, and wait events. Minor gaps remain, such as broader session/progress monitoring and index bloat analysis, but the read-only monitoring scope is largely complete.
Maintenance
Related MCP Connectors
Query PostgreSQL databases in plain English β LLM-generated, safety-validated SQL.
Hosted MCP server for PostgreSQL diagnostics: slow queries, missing indexes, connection pressure.
Query, explore, and manage your databases through natural conversation.
Comprehensive PostgreSQL documentation and best practices, including ecosystem tools
Related MCP Servers
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to inspect and monitor PostgreSQL databases using natural language, covering performance analysis, bloat detection, lock/deadlock monitoring, autovacuum, schema inspection, and maintenance recommendations across PostgreSQL 12β18 without requiring extensions.-
- FlicenseNot gradedqualityAmaintenanceEnables natural-language monitoring and administration of PostgreSQL databases, covering performance analysis, bloat detection, lock and deadlock monitoring, autovacuum intelligence, and schema inspection. It works with PostgreSQL 12-18 and can run without extensions, with optional extensions for advanced query analytics.-
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to perform PostgreSQL database operations, monitoring, and management through natural language, including performance analysis, bloat detection, lock/deadlock monitoring, autovacuum and schema inspection, and maintenance recommendations across PostgreSQL 12-18.-
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to monitor and administer PostgreSQL 12-18 databases through natural language, covering performance and slow-query analysis, table bloat detection, connection and lock monitoring, autovacuum and maintenance recommendations, and schema discovery. All operations are read-only and work with regular user permissions, requiring no extensions unless optional query-analytics features are desired.-