Content
# PostgreSQL MCP Server Management System
A dynamic management system for running multiple PostgreSQL MCP (Model Context Protocol) server instances, each connecting to different databases with configurable settings.
## 🏗️ Architecture Overview
This system solves the problem of managing multiple PostgreSQL MCP servers by providing:
- **Dynamic server spawning** on different ports
- **Global configuration management** for operational settings
- **Per-database configuration** for connection details
- **REST API** for server lifecycle management
- **Process isolation** with no environment variable conflicts
## 🎯 Configuration Philosophy
The system follows a smart configuration approach:
### Global Settings (Shared Across All Servers)
- **Server Mode** (`sse`, `http`, `stdio`) - How servers communicate
- **Max Rows** - Default limit for SQL query results
- **Fuzzy Search** - Enable/disable fuzzy text search
- **Host** - Default host for server instances
### Per-Database Settings (Unique Per Server)
- **Database URL** - Each server connects to its own database
- **Optional Overrides** - Individual databases can override global defaults
### Benefits
✅ **No Conflicts**: Each MCP server process gets isolated environment variables
✅ **Easy Management**: Change global settings once, affects all servers
✅ **Flexibility**: Individual databases can override defaults when needed
✅ **Clean Separation**: Database connections vs. operational settings
## 📁 Project Structure
```
rag-pg/
├── main.py # Main entry point
├── requirements.txt # Dependencies
├── postgres_mcp_server.py # Original MCP server
├── setup.py # Setup script
├── src/
│ ├── config/
│ │ ├── models.py # Configuration data models
│ │ ├── manager.py # Database config management (JSON)
│ │ └── system_config.py # Global settings management
│ ├── server/
│ │ ├── template_server.py # MCP server template (reads env vars)
│ │ ├── process_manager.py # Server lifecycle management
│ │ └── port_manager.py # Port allocation
│ └── api/
│ ├── main.py # FastAPI application
│ └── routes.py # REST API endpoints
├── configs/ # Database configurations (JSON files)
├── logs/ # Server log files
└── test_config_system.py # Configuration test script
```
## 🚀 Quick Start
### 1. Setup Environment
```bash
# Install dependencies
pip install -r requirements.txt
# Setup directories and templates
python main.py setup
```
### 2. Create .env File
```bash
# API Configuration
API_HOST=localhost
API_PORT=3000
# Global MCP Server Defaults
MCP_SERVER_MODE=sse
MCP_MAX_ROWS=1000
MCP_ENABLE_FUZZY_SEARCH=true
```
### 3. Start Management API
```bash
python main.py api
```
The API will be available at:
- **API Server**: http://localhost:3000
- **API Documentation**: http://localhost:3000/docs
- **Health Check**: http://localhost:3000/health
## 📡 API Usage Examples
### Create Database Configuration
```bash
curl -X POST "http://localhost:3000/api/v1/configs" \
-H "Content-Type: application/json" \
-d '{
"name": "Production Database",
"database_url": "postgresql://user:pass@localhost:5432/proddb"
}'
```
### Start MCP Server Instance
```bash
curl -X POST "http://localhost:3000/api/v1/servers/{config_id}/start"
```
### List All Servers
```bash
curl "http://localhost:3000/api/v1/servers"
```
### Stop Server
```bash
curl -X POST "http://localhost:3000/api/v1/servers/{config_id}/stop"
```
## 🔧 Configuration Management
### Global Settings (system_config.json)
```json
{
"server_mode": "sse",
"max_rows": 1000,
"enable_fuzzy_search": true,
"host": "localhost"
}
```
### Database Configuration (configs/{config_id}.json)
```json
{
"id": "prod-db-1",
"name": "Production Database",
"connection": {
"database_url": "postgresql://user:pass@host:5432/db",
"max_rows_override": 2000, // Optional: override global default
"enable_fuzzy_search_override": false // Optional: override global default
},
"server": {
"host": "localhost",
"port": 8001, // Automatically assigned
"mode_override": "http" // Optional: override global default
},
"status": "running",
"created_at": "2024-01-01T12:00:00Z",
"updated_at": "2024-01-01T12:00:00Z"
}
```
## 🔄 How Server Spawning Works
1. **Configuration Created**: Database URL and optional overrides defined
2. **Port Allocation**: System assigns available port (8000-8100 range)
3. **Environment Setup**: Global + database-specific settings combined
4. **Process Launch**: New Python process starts with isolated environment
5. **Health Monitoring**: System tracks process status and logs
### Environment Variables Per Server Process
Each MCP server process receives its own environment:
```bash
# Server 1 Environment
MCP_CONFIG_ID=prod-db-1
MCP_DATABASE_URL=postgresql://user:pass@host1:5432/db1
MCP_SERVER_HOST=localhost
MCP_SERVER_PORT=8001
MCP_SERVER_MODE=sse # From global config
MCP_MAX_ROWS=1000 # From global config
MCP_ENABLE_FUZZY_SEARCH=true # From global config
# Server 2 Environment (different database, same settings)
MCP_CONFIG_ID=dev-db-1
MCP_DATABASE_URL=postgresql://user:pass@host2:5432/db2
MCP_SERVER_HOST=localhost
MCP_SERVER_PORT=8002 # Different port
MCP_SERVER_MODE=sse # Same global setting
MCP_MAX_ROWS=2000 # Overridden for this database
MCP_ENABLE_FUZZY_SEARCH=true # Same global setting
```
## 🛠️ MCP Server Tools
Each spawned server provides these tools:
- **`run_sql`**: Execute SELECT queries safely
- **`describe_table_schema`**: Get table structure details
- **`get_database_tables`**: List all tables and views
- **`get_database_schemas`**: List database schemas
- **`fuzzy_search_values`**: Search text columns with similarity
- **`get_sample_data`**: Fetch sample rows from tables
## 📊 Monitoring & Logs
### Server Status
- **stopped**: Server is not running
- **starting**: Server is being launched
- **running**: Server is active and healthy
- **stopping**: Server is being shut down
- **error**: Server encountered an error
- **config_error**: Configuration validation failed
### Log Files
Each server writes to its own log file:
```
logs/
├── prod-db-1_server.log
├── dev-db-1_server.log
└── test-db-1_server.log
```
### Health Endpoints
- **`GET /health`**: API server health
- **`GET /api/v1/health`**: Detailed system status
- **`GET /api/v1/servers/{config_id}/status`**: Individual server status
- **`GET /api/v1/servers/{config_id}/logs`**: Server log files
## 🔮 Future Enhancements
- **Database Storage**: Move from JSON files to PostgreSQL storage
- **Authentication**: Add API authentication and authorization
- **Metrics**: Prometheus metrics and Grafana dashboards
- **Auto-scaling**: Automatic server scaling based on load
- **Configuration Templates**: Predefined configuration templates
- **Backup/Restore**: Configuration backup and restore functionality
## 🧪 Testing
```bash
# Test configuration system
python test_config_system.py
# Test individual MCP server
MCP_CONFIG_ID=test \
MCP_DATABASE_URL=postgresql://user:pass@host:5432/db \
MCP_SERVER_HOST=localhost \
MCP_SERVER_PORT=8000 \
MCP_SERVER_MODE=sse \
python main.py server
```
## 📝 Development
### Adding New Configuration Options
1. Update `GlobalServerConfig` in `src/config/models.py`
2. Update `get_effective_config_for_database()` in `src/config/system_config.py`
3. Update environment variable mapping in `src/server/process_manager.py`
4. Update template server to read new environment variable
### API Endpoints
All endpoints are documented with OpenAPI/Swagger:
- **Configurations**: `/api/v1/configs/*`
- **Server Management**: `/api/v1/servers/*`
- **Health & Status**: `/api/v1/health`
## 🤝 Contributing
1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Test with `python test_config_system.py`
5. Submit a pull request
## 📄 License
This project is licensed under the MIT License.
---
**Built with ❤️ for efficient PostgreSQL MCP server management**
