MCP-PostgreSQL-Ops

MCP Server for PostgreSQL Operations and Monitoring

MCP-PostgreSQL-Opsis 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 whenpg_stat_statementsand (optionally)pg_stat_monitorextensions are installed.

- ✅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: Optionalpg_stat_statementsandpg_stat_monitorfor advanced query analytics.
- ✅Comprehensive Database Monitoring: Performance analysis, bloat detection, and maintenance recommendations.
- ✅Smart Query Analysis: Slow query identification withpg_stat_statementsandpg_stat_monitorintegration.
- ✅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.

- 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+).

Note:Thepostgresqlcontainer included indocker-compose.ymlis 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.envfile (see POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB).
- Indocker-compose.yml, comment out (disable) thepostgresandpostgres-init-extensionscontainers to avoid starting the built-in test database.

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/dbis preconfigured for the Percona PostgreSQL Docker image, which requires this specific path for proper write permissions.

# 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:
- PostgreSQLcontainer starts first with database initialization
- PostgreSQL Extensionscontainer installs extensions and creates comprehensive test data (~83K records)
- MCP ServerandMCPO Proxycontainers start after PostgreSQL is ready
- OpenWebUIcontainer starts last and may take additional time to load the web interface

💡 Tip: Wait 2-3 minutes after runningdocker-compose up -dbefore accessing OpenWebUI to ensure all services are fully initialized.

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

- The list of MCP tool features provided byswaggercan be found in the MCPO API Docs URL.

- e.g:http://localhost:8003/docs

📌Note: Web-UI configuration instructions are based on OpenWebUIv0.6.22. Menu locations and settings may differ in newer versions.
- logging in to OpenWebUI with an admin account
- go to "Settings" → "Tools" from the top menu.
- Enter thepostgresql-opsTool address (e.g.,http://localhost:8003/postgresql-ops) to connect MCP Tools.
- Setup Ollama or OpenAI.

Congratulations!Your MCP PostgreSQL Operations server is now ready for use. You can start exploring your databases with natural language 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?"

- Browse theExample Queries sectionbelow for more query examples
- Check outTool Usage Examples with Screenshotsfor visual guides
- Explore theTool Compatibility Matrixto understand available features

{ "mcpServers": { "mcp-postgresql-ops": { "command": "uvx", "args": ["--python", "3.12", "mcp-postgresql-ops"], "env": { "POSTGRES_HOST": "127.0.0.1", "POSTGRES_PORT": "15432", "POSTGRES_USER": "postgres", "POSTGRES_PASSWORD": "changeme!@34", "POSTGRES_DB": "ecommerce" } } } }

"Show all active connections in a clear and readable html table format."

"Show all relationships for customers table in ecommerce database as a Mermaid diagram."

# Install the package pip install mcp-postgresql-ops # Or with uv (faster) uv add mcp-postgresql-ops # Verify installation mcp-postgresql-ops --help

# Clone the repository git clone https://github.com/call518/MCP-PostgreSQL-Ops.git cd MCP-PostgreSQL-Ops # Install with uv (recommended) uv sync uv run mcp-postgresql-ops --help # Or with pip pip install -e . mcp-postgresql-ops --help

{ "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!@34", "POSTGRES_DB": "ecommerce" } } } }

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

# Method 1: Module execution (for development, requires PYTHONPATH) PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src python -m mcp_postgresql_ops \ --type stdio # Method 2: Direct script (after uv installation in project directory) uv run mcp-postgresql-ops \ --type stdio # Method 3: Installed package script (after pip/uv install) mcp-postgresql-ops \ --type stdio # HTTP mode examples: # Development mode PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src python -m mcp_postgresql_ops \ --type streamable-http \ --host 127.0.0.1 \ --port 8000 \ --log-level DEBUG # Production mode (after installation) mcp-postgresql-ops \ --type streamable-http \ --host 127.0.0.1 \ --port 8000 \ --log-level DEBUG

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

Note:POSTGRES_DBserves 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 mapping15432:5432where:

- POSTGRES_PORT=15432: External port for host access and MCP server connections
- DOCKER_INTERNAL_PORT_POSTGRESQL=5432: Internal container port (PostgreSQL default)
- When using external PostgreSQL servers, setPOSTGRES_PORTto match your server's actual port

For more details, see the## Tool Compatibility Matrix

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

shared_preload_libraries = 'pg_stat_statements'

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)

# Basic statistics collection (usually enabled by default) track_activities = on track_counts = on # Required for function statistics tools track_functions = pl # Enables PL/pgSQL function statistics collection # Optional but recommended for accurate I/O timing track_io_timing = on # Enables I/O timing statistics collection

# Docker example docker run -d \ -e POSTGRES_PASSWORD=mypassword \ postgres:17 \ -c track_activities=on \ -c track_counts=on \ -c track_functions=pl \ -c track_io_timing=on # Direct postgres command postgres -D /data \ -c track_activities=on \ -c track_counts=on \ -c track_functions=pl \ -c track_io_timing=on

-- 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();

-- Set for current session only (temporary) SET track_activities = 'on'; SET track_counts = 'on'; SET track_functions = 'pl'; SET track_io_timing = 'on';

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

- "Analyze index usage efficiency."
- "Find unused indexes in the current database."