MCP Server for PostgreSQL Operations and Monitoring

Architecture & Internal (DeepWiki)

Overview

MCP-PostgreSQL-Ops is a professional MCP server for PostgreSQL database operations, monitoring, and management. Supports PostgreSQL 12-18 with comprehensive database analysis, performance monitoring, and intelligent maintenance recommendations through natural language queries. Most features work independently, but advanced query analysis capabilities are enhanced when pg_stat_statements and (optionally) pg_stat_monitor extensions are installed.

Features

• ✅ Zero Configuration: Works with PostgreSQL 12-18 out-of-the-box with automatic version detection.
• ✅ Natural Language: Ask questions like "Show me slow queries" or "Analyze table bloat."
• ✅ Production Safe: Read-only operations, RDS/Aurora compatible with regular user permissions.
• ✅ Extension Enhanced: Optional pg_stat_statements and pg_stat_monitor for advanced query analytics.
• ✅ Comprehensive Database Monitoring: Performance analysis, bloat detection, and maintenance recommendations.
• ✅ Smart Query Analysis: Slow query identification with pg_stat_statements and pg_stat_monitor integration.
• ✅ Schema & Relationship Discovery: Database structure exploration with detailed relationship mapping.
• ✅ VACUUM & Autovacuum Intelligence: Real-time maintenance monitoring and effectiveness analysis.
• ✅ Multi-Database Operations: Seamless cross-database analysis and monitoring.
• ✅ Enterprise-Ready: Safe read-only operations with RDS/Aurora compatibility.
• ✅ Developer-Friendly: Simple codebase for easy customization and tool extension.

🔧 Advanced Capabilities

• Version-aware I/O statistics (enhanced on PostgreSQL 16+, byte columns on PG 18+).
• Real-time connection and lock monitoring.
• Background process and checkpoint analysis.
• Replication status and WAL monitoring.
• Database capacity and bloat analysis.
• Wait event catalog with descriptions (PG 17+).
• WAL summarizer monitoring for incremental backups (PG 17+).
• Async I/O subsystem monitoring (PG 18+).
• Per-backend I/O and WAL statistics (PG 18+).

Tool Usage Examples

📸 More Examples with Screenshots →

⭐ Quickstart (5 minutes)

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

If you want to use your own PostgreSQL instance instead of the built-in test container:

• Update the target PostgreSQL connection information in your .env file (see POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB).
• In docker-compose.yml, comment out (disable) the postgres and postgres-init-extensions containers to avoid starting the built-in test database.

Flow Diagram of Quickstart/Tutorial

1. Environment Setup

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

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

### Check and modify .env file
cp .env.example .env
vim .env

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

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

2. Start Demo Containers

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

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

⏰ Wait for Environment Setup: The initial environment setup takes a few minutes as containers are started in sequence:

1. PostgreSQL container starts first with database initialization
2. PostgreSQL Extensions container installs extensions and creates comprehensive test data (~83K records)
3. MCP Server and MCPO Proxy containers start after PostgreSQL is ready
4. OpenWebUI container starts last and may take additional time to load the web interface

💡 Tip: Wait 2-3 minutes after running docker-compose up -d before accessing OpenWebUI to ensure all services are fully initialized.

🔍 Check Container Status (Optional):

# Monitor container startup progress
docker-compose logs -f

# Check if all containers are running
docker-compose ps

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

3. Access to OpenWebUI

http://localhost:3003/

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

4. Registering the Tool in OpenWebUI

📌 Note: Web-UI configuration instructions are based on OpenWebUI v0.6.22. Menu locations and settings may differ in newer versions.

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

5. Complete!

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

🚀 Try These Example Queries:

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

📖 Next Steps:

• Browse the Example Queries section below for more query examples
• Check out Tool Usage Examples with Screenshots for visual guides
• Explore the Tool Compatibility Matrix to understand available features

(NOTE) Sample Test Data Overview

The create-test-data.sql script is executed by the postgres-init-extensions container (defined in docker-compose.yml) on first startup, automatically generating comprehensive test databases for MCP tool testing:

| Database | Purpose | Schema & Tables | Scale | |----------|---------|-----------------|-------| | ecommerce | E-commerce system | public: categories, products, customers, orders, order_items | 10 categories, 500 products, 100 customers, 200 orders, 400 order items | | analytics | Analytics & reporting | public: page_views, sales_summary | 1,000 page views, 30 sales summaries | | inventory | Warehouse management | public: suppliers, inventory_items, purchase_orders | 10 suppliers, 100 items, 50 purchase orders | | hr_system | HR management | public: departments, employees, payroll | 5 departments, 50 employees, 150 payroll records |

Test users created: app_readonly, app_readwrite, analytics_user, backup_user

Optimized for testing: Intentional table bloat, various indexes (used/unused), time-series data, complex relationships

Tool Compatibility Matrix

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

🟢 Extension-Independent Tools (No Extensions Required)

| Tool Name | Extensions Required | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | PG 18 | System Views/Tables Used | |-----------|-------------------|-------|-------|-------|-------|-------|-------|-------|--------------------------| | get_server_info | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | version(), pg_extension | | get_active_connections | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | pg_stat_activity | | get_postgresql_config | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | pg_settings | | get_database_list | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | pg_database | | get_table_list | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | information_schema.tables | | get_table_schema_info | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | information_schema.*, pg_indexes | | get_database_schema_info | ❌ None | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | pg_namespace, `