# PostgreSQL MCP Server - Version 1.1.1
*Last Updated October 9, 2025 - Production/Stable v1.1.1*
Enterprise-grade PostgreSQL MCP server with enhanced security, comprehensive testing, AI-native database operations, intelligent meta-awareness, and guided workflows.
[](https://hub.docker.com/r/writenotenow/postgres-mcp-enhanced)
[](https://opensource.org/licenses/MIT)
[](https://pypi.org/project/postgres-mcp-enhanced/)
[](SECURITY.md)
[](https://github.com/neverinfamous/postgres-mcp/security/code-scanning)
---
## 🔍 **[AI-Powered Documentation Search →](https://search.adamic.tech)**
Can't find what you're looking for? Use our **AI-powered search interface** to search both PostgreSQL and SQLite MCP Server documentation:
- 🤖 **Natural Language Queries** - Ask questions in plain English
- ⚡ **Instant Results** - AI-enhanced answers with source attribution
- 📚 **Comprehensive Coverage** - Searches all 63 PostgreSQL tools + 73 SQLite tools
- 🎯 **Smart Context** - Understands technical questions and provides relevant examples
**[→ Try AI Search Now](https://search.adamic.tech)**
Example queries: "How do I optimize PostgreSQL query performance?", "What PostGIS features are available?", "How do I use pgvector for semantic search?"
---
## 📚 **[Complete Documentation - Visit the Wiki →](https://github.com/neverinfamous/postgres-mcp/wiki)**
For detailed documentation, examples, and guides, visit our comprehensive wiki:
- **[Quick Start Guide](https://github.com/neverinfamous/postgres-mcp/wiki/Quick-Start)** - Get running in 30 seconds
- **[Installation & Configuration](https://github.com/neverinfamous/postgres-mcp/wiki/Installation-and-Configuration)** - Detailed setup
- **[All Tool Categories](https://github.com/neverinfamous/postgres-mcp/wiki/Home)** - 63 specialized tools
- **[Security Best Practices](https://github.com/neverinfamous/postgres-mcp/wiki/Security-and-Best-practices)** - Production security
- **[Troubleshooting](https://github.com/neverinfamous/postgres-mcp/wiki/Troubleshooting)** - Common issues
---
## 🚀 **Quick Overview**
**63 specialized MCP tools** + **10 intelligent resources** + **10 guided prompts** for PostgreSQL operations:
### MCP Tools (63)
- **Core Database (9)**: Schema management, SQL execution, health monitoring
- **JSON Operations (11)**: JSONB operations, validation, security scanning
- **Text Processing (5)**: Similarity search, full-text search, fuzzy matching
- **Statistical Analysis (8)**: Descriptive stats, correlation, regression, time series
- **Performance Intelligence (6)**: Query optimization, index tuning, workload analysis
- **Vector/Semantic Search (8)**: Embeddings, similarity search, clustering
- **Geospatial (7)**: Distance calculation, spatial queries, GIS operations
- **Backup & Recovery (4)**: Backup planning, restore validation, scheduling
- **Monitoring & Alerting (5)**: Real-time monitoring, capacity planning, alerting
### MCP Resources (10) - Database Meta-Awareness
- **database://schema**: Complete schema with tables, columns, indexes
- **database://capabilities**: Server capabilities and installed extensions
- **database://performance**: Query performance metrics from pg_stat_statements
- **database://health**: Comprehensive health status
- **database://extensions**: Installed extensions with versions
- **database://indexes**: Index usage statistics and recommendations
- **database://connections**: Active connections and pool status
- **database://replication**: Replication status and lag
- **database://vacuum**: Vacuum status and transaction ID wraparound
- **database://locks**: Current lock information
- **database://statistics**: Table statistics quality
### MCP Prompts (10) - Guided Workflows
- **optimize_query**: Step-by-step query optimization
- **index_tuning**: Comprehensive index analysis
- **database_health_check**: Full health assessment
- **setup_pgvector**: Complete pgvector setup guide
- **json_operations**: JSONB best practices
- **performance_baseline**: Establish performance baselines
- **backup_strategy**: Design backup strategy
- **setup_postgis**: PostGIS setup and usage
- **explain_analyze_workflow**: Deep dive into EXPLAIN plans
- **extension_setup**: Extension installation guide
Enhanced with **pg_stat_statements**, **hypopg**, **pgvector**, and **PostGIS** extensions.
---
## 📋 **Prerequisites**
1. **PostgreSQL Database** (version 13-18)
2. **Environment Variable**: `DATABASE_URI="postgresql://user:pass@host:5432/db"`
3. **MCP Client**: Claude Desktop, Cursor, or compatible client
**See [Installation Guide](https://github.com/neverinfamous/postgres-mcp/wiki/Installation-and-Configuration) for detailed setup instructions.**
---
## 🚀 **Quick Start**
### **Docker (Recommended)**
```bash
docker pull neverinfamous/postgres-mcp:latest
docker run -i --rm \
-e DATABASE_URI="postgresql://user:pass@localhost:5432/db" \
neverinfamous/postgres-mcp:latest \
--access-mode=restricted
```
### **Python Installation**
```bash
pip install postgres-mcp-enhanced
postgres-mcp --access-mode=restricted
```
### **From Source**
```bash
git clone https://github.com/neverinfamous/postgres-mcp.git
cd postgres-mcp
uv sync
uv run pytest -v
```
**📖 [See Full Installation Guide →](https://github.com/neverinfamous/postgres-mcp/wiki/Installation-and-Configuration)**
---
## 🛡️ **Security-First Design**
**Zero known vulnerabilities** - Comprehensive security audit passed:
- ✅ SQL injection prevention with parameter binding
- ✅ 20+ security test cases covering all attack vectors
- ✅ Dual security modes (restricted/unrestricted)
- ✅ Advanced query validation
- ✅ CodeQL security scanning passing
- ✅ **Pyright strict mode** - 2,000+ type issues resolved, 100% type-safe codebase
**Security Modes:**
- **Restricted (Production)**: Read-only, query validation, resource limits
- **Unrestricted (Development)**: Full access with parameter binding protection
**📖 [Security Best Practices →](https://github.com/neverinfamous/postgres-mcp/wiki/Security-and-Best-Practices)**
---
## 🏢 **Enterprise Features**
### **🔍 Real-Time Monitoring**
- Database health monitoring (indexes, connections, vacuum, buffer cache)
- Query performance tracking via **pg_stat_statements**
- Capacity planning and growth forecasting
- Replication lag monitoring
### **⚡ Performance Optimization**
- AI-powered index tuning with DTA algorithms
- Hypothetical index testing via **hypopg** (zero-risk)
- Query plan analysis and optimization
- Workload analysis and slow query detection
### **🧠 AI-Native Operations**
- Vector similarity search via **pgvector**
- Geospatial operations via **PostGIS**
- Semantic search and clustering
- Natural language database interactions
**📖 [Explore All Features →](https://github.com/neverinfamous/postgres-mcp/wiki/Home)**
---
## 📊 **Features Overview**
### MCP Tools (63)
Explore comprehensive documentation for each category:
| Category | Tools | Documentation |
|----------|-------|---------------|
| **Core Database** | 9 | [Core Tools →](https://github.com/neverinfamous/postgres-mcp/wiki/Core-Database-Tools) |
| **JSON Operations** | 11 | [JSON Tools →](https://github.com/neverinfamous/postgres-mcp/wiki/JSON-Operations) |
| **Text Processing** | 5 | [Text Tools →](https://github.com/neverinfamous/postgres-mcp/wiki/Text-Processing) |
| **Statistical Analysis** | 8 | [Stats Tools →](https://github.com/neverinfamous/postgres-mcp/wiki/Statistical-Analysis) |
| **Performance Intelligence** | 6 | [Performance →](https://github.com/neverinfamous/postgres-mcp/wiki/Performance-Intelligence) |
| **Vector/Semantic Search** | 8 | [Vector Search →](https://github.com/neverinfamous/postgres-mcp/wiki/Vector-Semantic-Search) |
| **Geospatial** | 7 | [GIS Tools →](https://github.com/neverinfamous/postgres-mcp/wiki/Geospatial-Operations) |
| **Backup & Recovery** | 4 | [Backup Tools →](https://github.com/neverinfamous/postgres-mcp/wiki/Backup-Recovery) |
| **Monitoring & Alerting** | 5 | [Monitoring →](https://github.com/neverinfamous/postgres-mcp/wiki/Monitoring-Alerting) |
### MCP Resources (10) - NEW in v1.1.0! 🎉
Resources provide real-time database meta-awareness - AI can access these automatically without explicit tool calls:
| Resource | Purpose | When to Use |
|----------|---------|-------------|
| **database://schema** | Complete database structure | Understanding database layout before queries |
| **database://capabilities** | Server features and extensions | Checking what operations are available |
| **database://performance** | Query performance metrics | Identifying slow queries proactively |
| **database://health** | Database health status | Proactive monitoring and issue detection |
| **database://extensions** | Extension inventory | Verifying required features are installed |
| **database://indexes** | Index usage statistics | Finding unused or missing indexes |
| **database://connections** | Connection pool status | Monitoring connection utilization |
| **database://replication** | Replication lag and status | Ensuring replica consistency |
| **database://vacuum** | Vacuum and wraparound status | Preventing transaction ID exhaustion |
| **database://locks** | Lock contention information | Diagnosing deadlocks and blocking |
| **database://statistics** | Statistics quality | Ensuring accurate query planning |
**💡 Key Benefit:** Resources reduce token usage by providing cached context vs. repeated queries!
### MCP Prompts (10) - NEW in v1.1.0! 🎉
Prompts provide guided workflows for complex operations - step-by-step instructions with examples:
| Prompt | Purpose | Use Case |
|--------|---------|----------|
| **optimize_query** | Query optimization workflow | Analyzing and improving slow queries |
| **index_tuning** | Index analysis and recommendations | Finding unused/missing/duplicate indexes |
| **database_health_check** | Comprehensive health assessment | Regular maintenance and monitoring |
| **setup_pgvector** | pgvector installation and setup | Implementing semantic search |
| **json_operations** | JSONB best practices | Optimizing JSON queries and indexes |
| **performance_baseline** | Baseline establishment | Setting up performance monitoring |
| **backup_strategy** | Backup planning and design | Designing enterprise backup strategy |
| **setup_postgis** | PostGIS installation and usage | Implementing geospatial features |
| **explain_analyze_workflow** | Deep plan analysis | Understanding query execution |
| **extension_setup** | Extension installation guide | Installing and configuring extensions |
**💡 Key Benefit:** Prompts guide users through complex multi-step operations with PostgreSQL best practices!
**📖 [View Complete Documentation →](https://github.com/neverinfamous/postgres-mcp/wiki/Home)**
---
## 🔧 **PostgreSQL Extensions**
Required extensions for full functionality:
- **pg_stat_statements** (built-in) - Query performance tracking
- **pg_trgm** & **fuzzystrmatch** (built-in) - Text similarity
- **hypopg** (optional) - Hypothetical index testing
- **pgvector** (optional) - Vector similarity search
- **PostGIS** (optional) - Geospatial operations
**Quick Setup:**
```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
```
**📖 [Extension Setup Guide →](https://github.com/neverinfamous/postgres-mcp/wiki/Extension-Setup)**
---
## 🆕 **Recent Updates**
### **Version 1.1.0 Release** 🎉 (October 4, 2025)
- **🌟 NEW: MCP Resources (10)**: Real-time database meta-awareness
- Instant access to schema, capabilities, performance, health
- Reduces token usage by providing cached context
- AI can access database state without explicit queries
- **🌟 NEW: MCP Prompts (10)**: Guided workflows for complex operations
- Step-by-step query optimization workflow
- Comprehensive index tuning guide
- Complete database health assessment
- pgvector and PostGIS setup guides
- JSONB best practices and optimization
- **✨ Intelligent Assistant**: Transforms from tool collection to database expert
- Proactive optimization suggestions
- Context-aware recommendations
- PostgreSQL-specific best practices
- **🔒 Code Quality**: Pyright strict mode compliance
- Resolved 2,000+ type issues
- 100% type-safe codebase
- Enhanced reliability and maintainability
- **📦 Zero Breaking Changes**: All existing tools work unchanged
### **Version 1.0.0 Release** 🎉 (October 3, 2025)
- **Production Ready**: Enterprise-grade PostgreSQL MCP server
- **63 Specialized Tools**: Complete feature set across 9 categories
- **Zero Known Vulnerabilities**: Comprehensive security audit passed
- **Type Safety**: Pyright strict mode compliance
- **Multi-Platform**: Windows, Linux, macOS (amd64, arm64)
### **Phase 5 Complete** ✅ (October 3, 2025)
- **Backup & Recovery**: 4 new tools for enterprise backup planning
- **Monitoring & Alerting**: 5 new tools for real-time monitoring
- **All 63 Tools Ready**: Complete Phase 5 implementation
### **Phase 4 Complete** ✅ (October 3, 2025)
- **Vector Search**: 8 tools with pgvector integration
- **Geospatial**: 7 tools with PostGIS integration
- **Extension Support**: pgvector v0.8.0, PostGIS v3.5.0
### **Phase 3 Complete** ✅ (October 3, 2025)
- **Statistical Analysis**: 8 advanced statistics tools
- **Performance Intelligence**: 6 optimization tools
---
## 📖 **Configuration**
### **Claude Desktop**
```json
{
"mcpServers": {
"postgres-mcp": {
"command": "docker",
"args": ["run", "-i", "--rm", "-e", "DATABASE_URI",
"neverinfamous/postgres-mcp:latest", "--access-mode=restricted"],
"env": {
"DATABASE_URI": "postgresql://user:pass@localhost:5432/db"
}
}
}
}
```
### **Cursor IDE**
```json
{
"mcpServers": {
"postgres-mcp": {
"command": "postgres-mcp",
"args": ["--access-mode=restricted"],
"env": {
"DATABASE_URI": "postgresql://user:pass@localhost:5432/db"
}
}
}
}
```
**📖 [MCP Configuration Guide →](https://github.com/neverinfamous/postgres-mcp/wiki/MCP-Configuration)**
---
## 🔧 **Troubleshooting**
**Common Issues:**
- **Connection Refused**: Verify PostgreSQL is running with `pg_isready`
- **Extension Not Found**: Install required extensions (see Extension Setup)
- **Permission Denied**: Check database user permissions
- **MCP Server Not Found**: Validate MCP client configuration
**📖 [Full Troubleshooting Guide →](https://github.com/neverinfamous/postgres-mcp/wiki/Troubleshooting)**
---
## 🧪 **Testing**
```bash
# Run all tests
uv run pytest -v
# Security tests
python security/run_security_test.py
# With coverage
uv run pytest --cov=src tests/
```
**Test Results:**
- ✅ Security: 20/20 passed (100% protection)
- ✅ SQL Injection: All vectors blocked
- ✅ Integration: All operations validated
- ✅ Type Safety: Pyright strict mode (2,000+ issues resolved)
- ✅ Compatibility: PostgreSQL 13-18 supported
---
## 🏆 **Why Choose This Server?**
- ✅ **Zero Known Vulnerabilities** - Comprehensive security audit passed
- ✅ **Pyright Strict Mode** - 2,000+ type issues resolved, 100% type-safe codebase
- ✅ **Enterprise-Grade** - Production-ready with advanced features
- ✅ **63 Specialized Tools** - Complete database operation coverage
- ✅ **10 Intelligent Resources** - Real-time database meta-awareness (NEW in v1.1.0!)
- ✅ **10 Guided Prompts** - Step-by-step workflows for complex operations (NEW in v1.1.0!)
- ✅ **AI Assistant Capabilities** - Proactive optimization and recommendations
- ✅ **Real-Time Analytics** - pg_stat_statements integration
- ✅ **AI-Native** - Vector search, semantic operations, ML-ready
- ✅ **Active Maintenance** - Regular updates and security patches
- ✅ **Comprehensive Documentation** - 16-page wiki with examples
**🌟 v1.1.0 Differentiation:** Only PostgreSQL MCP server with intelligent meta-awareness and guided workflows!
---
## 🔗 **Links**
- **[📚 Complete Wiki](https://github.com/neverinfamous/postgres-mcp/wiki)** - Full documentation
- **[📝 GitHub Gists](https://gist.github.com/neverinfamous/7a47b6ca39857c7a8e06c4f7e6537a16)** - 7 practical examples and use cases
- **[🛡️ Security Policy](SECURITY.md)** - Vulnerability reporting
- **[🤝 Contributing](CONTRIBUTING.md)** - Development guidelines
- **[🐳 Docker Hub](https://hub.docker.com/r/neverinfamous/postgres-mcp)** - Container images (coming soon)
- **[📦 PyPI Package](https://pypi.org/project/postgres-mcp-enhanced/)** - Python package
**GitHub Gists - Practical Examples:**
- **Complete Feature Showcase** - All 63 tools with comprehensive examples
- **Security Best Practices** - SQL injection prevention and production security
- **Performance Intelligence** - Query optimization and index tuning strategies
- **Vector/Semantic Search** - pgvector integration and AI-native operations
- **Enterprise Monitoring** - Real-time monitoring and alerting workflows
- **Geospatial Operations** - PostGIS integration and spatial queries
- **JSON/JSONB Operations** - Advanced JSONB operations and validation
---
## 📈 **Project Stats**
- **Version 1.1.0** - Intelligent assistant release (October 4, 2025)
- **63 MCP Tools** across 9 categories
- **10 MCP Resources** - Database meta-awareness (NEW!)
- **10 MCP Prompts** - Guided workflows (NEW!)
- **100% Type Safe** - Pyright strict mode (2,000+ issues resolved)
- **Zero Known Vulnerabilities** - Security audit passed
- **Zero Linter Errors** - Clean codebase with comprehensive type checking
- **PostgreSQL 13-18** - Full compatibility
- **Multi-platform** - Windows, Linux, macOS (amd64, arm64)
- **7,500+ lines** - 14 modules, comprehensive implementation
---
## 📄 **License & Security**
- **License**: MIT - see [LICENSE](LICENSE) file
- **Security**: Report vulnerabilities to admin@adamic.tech
- **Contributing**: See [CONTRIBUTING.md](CONTRIBUTING.md)
---
*Enterprise-grade PostgreSQL MCP server with comprehensive security, real-time analytics, and AI-native operations.*
Raw data
{
"_id": null,
"home_page": null,
"name": "postgres-mcp-enhanced",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.12",
"maintainer_email": null,
"keywords": "database, mcp, model-context-protocol, pgvector, postgis, postgres, postgresql",
"author": null,
"author_email": "Chris LeRoux <admin@adamic.tech>",
"download_url": "https://files.pythonhosted.org/packages/01/97/cc7a210fddafe05745a5283963ca71861f658c28454655f2b940480d5aea/postgres_mcp_enhanced-1.1.1.tar.gz",
"platform": null,
"description": "# PostgreSQL MCP Server - Version 1.1.1\n\n*Last Updated October 9, 2025 - Production/Stable v1.1.1*\n\nEnterprise-grade PostgreSQL MCP server with enhanced security, comprehensive testing, AI-native database operations, intelligent meta-awareness, and guided workflows.\n\n[](https://hub.docker.com/r/writenotenow/postgres-mcp-enhanced)\n[](https://opensource.org/licenses/MIT)\n\n\n[](https://pypi.org/project/postgres-mcp-enhanced/)\n[](SECURITY.md)\n[](https://github.com/neverinfamous/postgres-mcp/security/code-scanning)\n\n---\n\n## \ud83d\udd0d **[AI-Powered Documentation Search \u2192](https://search.adamic.tech)**\n\nCan't find what you're looking for? Use our **AI-powered search interface** to search both PostgreSQL and SQLite MCP Server documentation:\n\n- \ud83e\udd16 **Natural Language Queries** - Ask questions in plain English\n- \u26a1 **Instant Results** - AI-enhanced answers with source attribution\n- \ud83d\udcda **Comprehensive Coverage** - Searches all 63 PostgreSQL tools + 73 SQLite tools\n- \ud83c\udfaf **Smart Context** - Understands technical questions and provides relevant examples\n\n**[\u2192 Try AI Search Now](https://search.adamic.tech)**\n\nExample queries: \"How do I optimize PostgreSQL query performance?\", \"What PostGIS features are available?\", \"How do I use pgvector for semantic search?\"\n\n---\n\n## \ud83d\udcda **[Complete Documentation - Visit the Wiki \u2192](https://github.com/neverinfamous/postgres-mcp/wiki)**\n\nFor detailed documentation, examples, and guides, visit our comprehensive wiki:\n- **[Quick Start Guide](https://github.com/neverinfamous/postgres-mcp/wiki/Quick-Start)** - Get running in 30 seconds\n- **[Installation & Configuration](https://github.com/neverinfamous/postgres-mcp/wiki/Installation-and-Configuration)** - Detailed setup\n- **[All Tool Categories](https://github.com/neverinfamous/postgres-mcp/wiki/Home)** - 63 specialized tools\n- **[Security Best Practices](https://github.com/neverinfamous/postgres-mcp/wiki/Security-and-Best-practices)** - Production security\n- **[Troubleshooting](https://github.com/neverinfamous/postgres-mcp/wiki/Troubleshooting)** - Common issues\n\n---\n\n## \ud83d\ude80 **Quick Overview**\n\n**63 specialized MCP tools** + **10 intelligent resources** + **10 guided prompts** for PostgreSQL operations:\n\n### MCP Tools (63)\n- **Core Database (9)**: Schema management, SQL execution, health monitoring\n- **JSON Operations (11)**: JSONB operations, validation, security scanning\n- **Text Processing (5)**: Similarity search, full-text search, fuzzy matching\n- **Statistical Analysis (8)**: Descriptive stats, correlation, regression, time series\n- **Performance Intelligence (6)**: Query optimization, index tuning, workload analysis\n- **Vector/Semantic Search (8)**: Embeddings, similarity search, clustering\n- **Geospatial (7)**: Distance calculation, spatial queries, GIS operations\n- **Backup & Recovery (4)**: Backup planning, restore validation, scheduling\n- **Monitoring & Alerting (5)**: Real-time monitoring, capacity planning, alerting\n\n### MCP Resources (10) - Database Meta-Awareness\n- **database://schema**: Complete schema with tables, columns, indexes\n- **database://capabilities**: Server capabilities and installed extensions\n- **database://performance**: Query performance metrics from pg_stat_statements\n- **database://health**: Comprehensive health status\n- **database://extensions**: Installed extensions with versions\n- **database://indexes**: Index usage statistics and recommendations\n- **database://connections**: Active connections and pool status\n- **database://replication**: Replication status and lag\n- **database://vacuum**: Vacuum status and transaction ID wraparound\n- **database://locks**: Current lock information\n- **database://statistics**: Table statistics quality\n\n### MCP Prompts (10) - Guided Workflows\n- **optimize_query**: Step-by-step query optimization\n- **index_tuning**: Comprehensive index analysis\n- **database_health_check**: Full health assessment\n- **setup_pgvector**: Complete pgvector setup guide\n- **json_operations**: JSONB best practices\n- **performance_baseline**: Establish performance baselines\n- **backup_strategy**: Design backup strategy\n- **setup_postgis**: PostGIS setup and usage\n- **explain_analyze_workflow**: Deep dive into EXPLAIN plans\n- **extension_setup**: Extension installation guide\n\nEnhanced with **pg_stat_statements**, **hypopg**, **pgvector**, and **PostGIS** extensions.\n\n---\n\n## \ud83d\udccb **Prerequisites**\n\n1. **PostgreSQL Database** (version 13-18)\n2. **Environment Variable**: `DATABASE_URI=\"postgresql://user:pass@host:5432/db\"`\n3. **MCP Client**: Claude Desktop, Cursor, or compatible client\n\n**See [Installation Guide](https://github.com/neverinfamous/postgres-mcp/wiki/Installation-and-Configuration) for detailed setup instructions.**\n\n---\n\n## \ud83d\ude80 **Quick Start**\n\n### **Docker (Recommended)**\n```bash\ndocker pull neverinfamous/postgres-mcp:latest\n\ndocker run -i --rm \\\n -e DATABASE_URI=\"postgresql://user:pass@localhost:5432/db\" \\\n neverinfamous/postgres-mcp:latest \\\n --access-mode=restricted\n```\n\n### **Python Installation**\n```bash\npip install postgres-mcp-enhanced\npostgres-mcp --access-mode=restricted\n```\n\n### **From Source**\n```bash\ngit clone https://github.com/neverinfamous/postgres-mcp.git\ncd postgres-mcp\nuv sync\nuv run pytest -v\n```\n\n**\ud83d\udcd6 [See Full Installation Guide \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Installation-and-Configuration)**\n\n---\n\n## \ud83d\udee1\ufe0f **Security-First Design**\n\n**Zero known vulnerabilities** - Comprehensive security audit passed:\n- \u2705 SQL injection prevention with parameter binding\n- \u2705 20+ security test cases covering all attack vectors\n- \u2705 Dual security modes (restricted/unrestricted)\n- \u2705 Advanced query validation\n- \u2705 CodeQL security scanning passing\n- \u2705 **Pyright strict mode** - 2,000+ type issues resolved, 100% type-safe codebase\n\n**Security Modes:**\n- **Restricted (Production)**: Read-only, query validation, resource limits\n- **Unrestricted (Development)**: Full access with parameter binding protection\n\n**\ud83d\udcd6 [Security Best Practices \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Security-and-Best-Practices)**\n\n---\n\n## \ud83c\udfe2 **Enterprise Features**\n\n### **\ud83d\udd0d Real-Time Monitoring**\n- Database health monitoring (indexes, connections, vacuum, buffer cache)\n- Query performance tracking via **pg_stat_statements**\n- Capacity planning and growth forecasting\n- Replication lag monitoring\n\n### **\u26a1 Performance Optimization**\n- AI-powered index tuning with DTA algorithms\n- Hypothetical index testing via **hypopg** (zero-risk)\n- Query plan analysis and optimization\n- Workload analysis and slow query detection\n\n### **\ud83e\udde0 AI-Native Operations**\n- Vector similarity search via **pgvector**\n- Geospatial operations via **PostGIS**\n- Semantic search and clustering\n- Natural language database interactions\n\n**\ud83d\udcd6 [Explore All Features \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Home)**\n\n---\n\n## \ud83d\udcca **Features Overview**\n\n### MCP Tools (63)\n\nExplore comprehensive documentation for each category:\n\n| Category | Tools | Documentation |\n|----------|-------|---------------|\n| **Core Database** | 9 | [Core Tools \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Core-Database-Tools) |\n| **JSON Operations** | 11 | [JSON Tools \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/JSON-Operations) |\n| **Text Processing** | 5 | [Text Tools \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Text-Processing) |\n| **Statistical Analysis** | 8 | [Stats Tools \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Statistical-Analysis) |\n| **Performance Intelligence** | 6 | [Performance \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Performance-Intelligence) |\n| **Vector/Semantic Search** | 8 | [Vector Search \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Vector-Semantic-Search) |\n| **Geospatial** | 7 | [GIS Tools \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Geospatial-Operations) |\n| **Backup & Recovery** | 4 | [Backup Tools \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Backup-Recovery) |\n| **Monitoring & Alerting** | 5 | [Monitoring \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Monitoring-Alerting) |\n\n### MCP Resources (10) - NEW in v1.1.0! \ud83c\udf89\n\nResources provide real-time database meta-awareness - AI can access these automatically without explicit tool calls:\n\n| Resource | Purpose | When to Use |\n|----------|---------|-------------|\n| **database://schema** | Complete database structure | Understanding database layout before queries |\n| **database://capabilities** | Server features and extensions | Checking what operations are available |\n| **database://performance** | Query performance metrics | Identifying slow queries proactively |\n| **database://health** | Database health status | Proactive monitoring and issue detection |\n| **database://extensions** | Extension inventory | Verifying required features are installed |\n| **database://indexes** | Index usage statistics | Finding unused or missing indexes |\n| **database://connections** | Connection pool status | Monitoring connection utilization |\n| **database://replication** | Replication lag and status | Ensuring replica consistency |\n| **database://vacuum** | Vacuum and wraparound status | Preventing transaction ID exhaustion |\n| **database://locks** | Lock contention information | Diagnosing deadlocks and blocking |\n| **database://statistics** | Statistics quality | Ensuring accurate query planning |\n\n**\ud83d\udca1 Key Benefit:** Resources reduce token usage by providing cached context vs. repeated queries!\n\n### MCP Prompts (10) - NEW in v1.1.0! \ud83c\udf89\n\nPrompts provide guided workflows for complex operations - step-by-step instructions with examples:\n\n| Prompt | Purpose | Use Case |\n|--------|---------|----------|\n| **optimize_query** | Query optimization workflow | Analyzing and improving slow queries |\n| **index_tuning** | Index analysis and recommendations | Finding unused/missing/duplicate indexes |\n| **database_health_check** | Comprehensive health assessment | Regular maintenance and monitoring |\n| **setup_pgvector** | pgvector installation and setup | Implementing semantic search |\n| **json_operations** | JSONB best practices | Optimizing JSON queries and indexes |\n| **performance_baseline** | Baseline establishment | Setting up performance monitoring |\n| **backup_strategy** | Backup planning and design | Designing enterprise backup strategy |\n| **setup_postgis** | PostGIS installation and usage | Implementing geospatial features |\n| **explain_analyze_workflow** | Deep plan analysis | Understanding query execution |\n| **extension_setup** | Extension installation guide | Installing and configuring extensions |\n\n**\ud83d\udca1 Key Benefit:** Prompts guide users through complex multi-step operations with PostgreSQL best practices!\n\n**\ud83d\udcd6 [View Complete Documentation \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Home)**\n\n---\n\n## \ud83d\udd27 **PostgreSQL Extensions**\n\nRequired extensions for full functionality:\n- **pg_stat_statements** (built-in) - Query performance tracking\n- **pg_trgm** & **fuzzystrmatch** (built-in) - Text similarity\n- **hypopg** (optional) - Hypothetical index testing\n- **pgvector** (optional) - Vector similarity search\n- **PostGIS** (optional) - Geospatial operations\n\n**Quick Setup:**\n```sql\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;\nCREATE EXTENSION IF NOT EXISTS pg_trgm;\nCREATE EXTENSION IF NOT EXISTS fuzzystrmatch;\n```\n\n**\ud83d\udcd6 [Extension Setup Guide \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Extension-Setup)**\n\n---\n\n## \ud83c\udd95 **Recent Updates**\n\n### **Version 1.1.0 Release** \ud83c\udf89 (October 4, 2025)\n- **\ud83c\udf1f NEW: MCP Resources (10)**: Real-time database meta-awareness\n - Instant access to schema, capabilities, performance, health\n - Reduces token usage by providing cached context\n - AI can access database state without explicit queries\n- **\ud83c\udf1f NEW: MCP Prompts (10)**: Guided workflows for complex operations\n - Step-by-step query optimization workflow\n - Comprehensive index tuning guide\n - Complete database health assessment\n - pgvector and PostGIS setup guides\n - JSONB best practices and optimization\n- **\u2728 Intelligent Assistant**: Transforms from tool collection to database expert\n - Proactive optimization suggestions\n - Context-aware recommendations\n - PostgreSQL-specific best practices\n- **\ud83d\udd12 Code Quality**: Pyright strict mode compliance\n - Resolved 2,000+ type issues\n - 100% type-safe codebase\n - Enhanced reliability and maintainability\n- **\ud83d\udce6 Zero Breaking Changes**: All existing tools work unchanged\n\n### **Version 1.0.0 Release** \ud83c\udf89 (October 3, 2025)\n- **Production Ready**: Enterprise-grade PostgreSQL MCP server\n- **63 Specialized Tools**: Complete feature set across 9 categories\n- **Zero Known Vulnerabilities**: Comprehensive security audit passed\n- **Type Safety**: Pyright strict mode compliance\n- **Multi-Platform**: Windows, Linux, macOS (amd64, arm64)\n\n### **Phase 5 Complete** \u2705 (October 3, 2025)\n- **Backup & Recovery**: 4 new tools for enterprise backup planning\n- **Monitoring & Alerting**: 5 new tools for real-time monitoring\n- **All 63 Tools Ready**: Complete Phase 5 implementation\n\n### **Phase 4 Complete** \u2705 (October 3, 2025)\n- **Vector Search**: 8 tools with pgvector integration\n- **Geospatial**: 7 tools with PostGIS integration\n- **Extension Support**: pgvector v0.8.0, PostGIS v3.5.0\n\n### **Phase 3 Complete** \u2705 (October 3, 2025)\n- **Statistical Analysis**: 8 advanced statistics tools\n- **Performance Intelligence**: 6 optimization tools\n\n---\n\n## \ud83d\udcd6 **Configuration**\n\n### **Claude Desktop**\n```json\n{\n \"mcpServers\": {\n \"postgres-mcp\": {\n \"command\": \"docker\",\n \"args\": [\"run\", \"-i\", \"--rm\", \"-e\", \"DATABASE_URI\", \n \"neverinfamous/postgres-mcp:latest\", \"--access-mode=restricted\"],\n \"env\": {\n \"DATABASE_URI\": \"postgresql://user:pass@localhost:5432/db\"\n }\n }\n }\n}\n```\n\n### **Cursor IDE**\n```json\n{\n \"mcpServers\": {\n \"postgres-mcp\": {\n \"command\": \"postgres-mcp\",\n \"args\": [\"--access-mode=restricted\"],\n \"env\": {\n \"DATABASE_URI\": \"postgresql://user:pass@localhost:5432/db\"\n }\n }\n }\n}\n```\n\n**\ud83d\udcd6 [MCP Configuration Guide \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/MCP-Configuration)**\n\n---\n\n## \ud83d\udd27 **Troubleshooting**\n\n**Common Issues:**\n- **Connection Refused**: Verify PostgreSQL is running with `pg_isready`\n- **Extension Not Found**: Install required extensions (see Extension Setup)\n- **Permission Denied**: Check database user permissions\n- **MCP Server Not Found**: Validate MCP client configuration\n\n**\ud83d\udcd6 [Full Troubleshooting Guide \u2192](https://github.com/neverinfamous/postgres-mcp/wiki/Troubleshooting)**\n\n---\n\n## \ud83e\uddea **Testing**\n\n```bash\n# Run all tests\nuv run pytest -v\n\n# Security tests\npython security/run_security_test.py\n\n# With coverage\nuv run pytest --cov=src tests/\n```\n\n**Test Results:**\n- \u2705 Security: 20/20 passed (100% protection)\n- \u2705 SQL Injection: All vectors blocked\n- \u2705 Integration: All operations validated\n- \u2705 Type Safety: Pyright strict mode (2,000+ issues resolved)\n- \u2705 Compatibility: PostgreSQL 13-18 supported\n\n\n---\n\n## \ud83c\udfc6 **Why Choose This Server?**\n\n- \u2705 **Zero Known Vulnerabilities** - Comprehensive security audit passed\n- \u2705 **Pyright Strict Mode** - 2,000+ type issues resolved, 100% type-safe codebase\n- \u2705 **Enterprise-Grade** - Production-ready with advanced features\n- \u2705 **63 Specialized Tools** - Complete database operation coverage\n- \u2705 **10 Intelligent Resources** - Real-time database meta-awareness (NEW in v1.1.0!)\n- \u2705 **10 Guided Prompts** - Step-by-step workflows for complex operations (NEW in v1.1.0!)\n- \u2705 **AI Assistant Capabilities** - Proactive optimization and recommendations\n- \u2705 **Real-Time Analytics** - pg_stat_statements integration\n- \u2705 **AI-Native** - Vector search, semantic operations, ML-ready\n- \u2705 **Active Maintenance** - Regular updates and security patches\n- \u2705 **Comprehensive Documentation** - 16-page wiki with examples\n\n**\ud83c\udf1f v1.1.0 Differentiation:** Only PostgreSQL MCP server with intelligent meta-awareness and guided workflows!\n\n---\n\n## \ud83d\udd17 **Links**\n\n- **[\ud83d\udcda Complete Wiki](https://github.com/neverinfamous/postgres-mcp/wiki)** - Full documentation\n- **[\ud83d\udcdd GitHub Gists](https://gist.github.com/neverinfamous/7a47b6ca39857c7a8e06c4f7e6537a16)** - 7 practical examples and use cases\n- **[\ud83d\udee1\ufe0f Security Policy](SECURITY.md)** - Vulnerability reporting\n- **[\ud83e\udd1d Contributing](CONTRIBUTING.md)** - Development guidelines\n- **[\ud83d\udc33 Docker Hub](https://hub.docker.com/r/neverinfamous/postgres-mcp)** - Container images (coming soon)\n- **[\ud83d\udce6 PyPI Package](https://pypi.org/project/postgres-mcp-enhanced/)** - Python package\n\n**GitHub Gists - Practical Examples:**\n- **Complete Feature Showcase** - All 63 tools with comprehensive examples\n- **Security Best Practices** - SQL injection prevention and production security\n- **Performance Intelligence** - Query optimization and index tuning strategies\n- **Vector/Semantic Search** - pgvector integration and AI-native operations\n- **Enterprise Monitoring** - Real-time monitoring and alerting workflows\n- **Geospatial Operations** - PostGIS integration and spatial queries\n- **JSON/JSONB Operations** - Advanced JSONB operations and validation\n\n---\n\n## \ud83d\udcc8 **Project Stats**\n\n- **Version 1.1.0** - Intelligent assistant release (October 4, 2025)\n- **63 MCP Tools** across 9 categories\n- **10 MCP Resources** - Database meta-awareness (NEW!)\n- **10 MCP Prompts** - Guided workflows (NEW!)\n- **100% Type Safe** - Pyright strict mode (2,000+ issues resolved)\n- **Zero Known Vulnerabilities** - Security audit passed\n- **Zero Linter Errors** - Clean codebase with comprehensive type checking\n- **PostgreSQL 13-18** - Full compatibility\n- **Multi-platform** - Windows, Linux, macOS (amd64, arm64)\n- **7,500+ lines** - 14 modules, comprehensive implementation\n\n---\n\n## \ud83d\udcc4 **License & Security**\n\n- **License**: MIT - see [LICENSE](LICENSE) file\n- **Security**: Report vulnerabilities to admin@adamic.tech\n- **Contributing**: See [CONTRIBUTING.md](CONTRIBUTING.md)\n\n---\n\n*Enterprise-grade PostgreSQL MCP server with comprehensive security, real-time analytics, and AI-native operations.*\n",
"bugtrack_url": null,
"license": null,
"summary": "Enterprise PostgreSQL MCP Server - Enhanced fork with comprehensive security, AI-native operations, and intelligent meta-awareness",
"version": "1.1.1",
"project_urls": {
"Documentation": "https://github.com/neverinfamous/postgres-mcp/wiki",
"Homepage": "https://github.com/neverinfamous/postgres-mcp",
"Issues": "https://github.com/neverinfamous/postgres-mcp/issues",
"Repository": "https://github.com/neverinfamous/postgres-mcp"
},
"split_keywords": [
"database",
" mcp",
" model-context-protocol",
" pgvector",
" postgis",
" postgres",
" postgresql"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "c1bfd9e7d12b38255932c4f6e64a8d47ecf067a26c103dec33d6500d7f043908",
"md5": "09c7e5f1a123355118379158968cca0a",
"sha256": "1999db1c76e0b13480c594ac640b4a51571ba8492fb77386af944979eb5540e8"
},
"downloads": -1,
"filename": "postgres_mcp_enhanced-1.1.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "09c7e5f1a123355118379158968cca0a",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.12",
"size": 172796,
"upload_time": "2025-10-09T05:00:45",
"upload_time_iso_8601": "2025-10-09T05:00:45.990015Z",
"url": "https://files.pythonhosted.org/packages/c1/bf/d9e7d12b38255932c4f6e64a8d47ecf067a26c103dec33d6500d7f043908/postgres_mcp_enhanced-1.1.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "0197cc7a210fddafe05745a5283963ca71861f658c28454655f2b940480d5aea",
"md5": "e9ef666ef32dade6f6c0f31780ea6f78",
"sha256": "22458dabb420141baba065ab6c4b3238eb6de39c1c91235e4e7bf420857053cd"
},
"downloads": -1,
"filename": "postgres_mcp_enhanced-1.1.1.tar.gz",
"has_sig": false,
"md5_digest": "e9ef666ef32dade6f6c0f31780ea6f78",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.12",
"size": 297953,
"upload_time": "2025-10-09T05:00:47",
"upload_time_iso_8601": "2025-10-09T05:00:47.298461Z",
"url": "https://files.pythonhosted.org/packages/01/97/cc7a210fddafe05745a5283963ca71861f658c28454655f2b940480d5aea/postgres_mcp_enhanced-1.1.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-09 05:00:47",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "neverinfamous",
"github_project": "postgres-mcp",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "postgres-mcp-enhanced"
}
JSON
