call518/MCP-PostgreSQL-Ops

---
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.
> **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
> **⏰ 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):
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
1. go to "Settings" → "Tools" from the top menu.
1. Enter the postgresql-ops Tool address (e.g., http://localhost:8003/postgresql-ops) to connect MCP Tools.
1. 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.