MCP Server Postgres

🚀 PostgreSQL Model Context Protocol (PG-MCP) Server

An implementation of the Model Context Protocol (MCP) server designed to enhance AI agents' capabilities for PostgreSQL databases.

🚀 Quick Start

PG-MCP is a full-fledged server implementation of the Model Context Protocol for PostgreSQL databases. It offers a comprehensive API for AI agents to discover, connect to, query, and understand PostgreSQL databases through MCP's resource-oriented architecture.

This implementation is based on and extends the reference PostgreSQL MCP implementation, with the following key enhancements:

1. Full Server Implementation: A complete server for Server-Sent Events (SSE) transmission suitable for production environments.
2. Multi-Database Support: Connect to multiple PostgreSQL databases simultaneously.
3. Rich Catalog Information: Extract and expose table/column descriptions from the database catalog.
4. Extended Context: Provide detailed knowledge in YAML format about PostgreSQL extensions such as PostGIS and pgvector.
5. Query Explanation: Built-in tools for analyzing query execution plans.
6. Robust Connection Management: Comprehensive connection lifecycle management and secure handling of connection IDs.

✨ Features

Connection Management

• Connect Tool: Register a PostgreSQL connection string and obtain a secure connection ID.
• Disconnect Tool: Explicitly close a database connection.
• Connection Pool: Efficient connection management.

Query Tools

• pg_query: Execute read-only SQL queries using a connection ID.
• pg_explain: Analyze query execution plans and return results in JSON format.

Architecture of the Model Context Protocol (MCP) Server

The server is built on the following components:

• MCP: Foundation of the Model Context Protocol.
• FastMCP: A Python library for MCP.
• asyncpg: An asynchronous PostgreSQL client.
• YAML: Used for extended context information.

📦 Installation

Prerequisites

• Python 3.13+
• PostgreSQL database

Using Docker

# Clone the repository
git clone https://github.com/stuzero/pg-mcp.git
cd pg-mcp

# Build and run using Docker Compose
docker-compose up -d

Manual Installation

# Clone the repository
git clone https://github.com/stuzero/pg-mcp.git
cd pg-mcp

# Create and activate a virtual environment
python -m venv .venv
source .venv/bin/activate  # For Windows users: .venv\Scripts\activate

# Install using uv
uv sync --frozen

# Start the server
python -m server.app

💻 Usage Examples

Basic Usage

# Test basic server functionality
python test.py "postgresql://username:password@hostname:port/database"

# Use Claude for natural language to SQL conversion
python client/claude_cli.py "Show me the top 5 customers by total sales"