Postgresql Model Context Protocol (pg Mcp) Server 7fh

🚀 PostgreSQL Model Context Protocol (PG-MCP) Server

A Model Context Protocol (MCP) server designed for PostgreSQL databases, enhanced with AI agent capabilities.

For more information about the pg-mcp project, please refer to:

https://stuzero.github.io/pg-mcp/

🚀 Quick Start

PG-MCP is a full server implementation of the Model Context Protocol (MCP) for PostgreSQL databases. Through MCP's resource-oriented architecture, it provides a comprehensive API for AI agents to discover, connect to, query, and understand PostgreSQL databases.

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

1. Full Server Implementation: A complete server for production use, with SSE transmission.
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 YAML-based knowledge about PostgreSQL extensions (e.g., PostGIS and pgvector).
5. Query Explanation: Include tools specifically for analyzing query execution plans.
6. Powerful Connection Management: Efficient connection management for handling the database connection lifecycle and secure connection ID processing.

✨ Features

Connection Management

• Connection Tool: Register a PostgreSQL connection string and obtain a secure connection ID.
• Disconnection Tool: Explicitly close the database connection.
• Connection Pool Management: Efficient connection management and pooling.

Query Tools

• pg_query: Execute read-only SQL queries using the connection ID.
• pg_explain: Analyze the query execution plan and return it in JSON format.

Schema Discovery Resources

• List schemas and their descriptions.
• List tables, their descriptions, and row counts.
• Get column details, including data types and descriptions.
• View table constraints and indexes.
• Explore database extensions.

Data Access Resources

• Table sample data (with pagination).
• Get approximate row counts.

Extended Context

Built-in context information about PostgreSQL extensions, such as:

• PostGIS: Spatial data types, functions, and examples.
• pgvector: Vector similarity search capabilities and best practices.

Other extensions can be easily added via a YAML configuration file.

📦 Installation

Prerequisites

• Python 3.13+
• PostgreSQL database

Using Docker

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

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

Manual Installation

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

# Install dependencies and create a virtual environment (.venv)
uv sync

# Activate the virtual environment
source .venv/bin/activate  # On Windows: .venv\Scripts\activate

# Run the server
python -m server.app

💻 Usage Examples

Basic Usage

# Start the server
python -m server.app

# Access the API documentation
Open your browser and visit `http://localhost:8000/docs` to view the interactive API documentation.

# Use command-line tools
Use the `pg_query` and `pg_explain` commands to interact with the database.

Advanced Usage

# Execute a query
pg_query -H localhost -P 5432 -U postgres -d testdb -q "SELECT * FROM users LIMIT 10;"

# Analyze the execution plan
pg_explain -H localhost -P 5432 -U postgres -d testdb -q "SELECT COUNT(*) FROM orders;"

🔧 Technical Details

PG-MCP server adopts a layered architecture design, with the main components including:

• Web Service: Implements a RESTful API based on FastAPI.
• Database Adapter: Supports connection pooling and transaction management for PostgreSQL.
• Query Interpreter: Integrates tools for analyzing PostgreSQL query execution plans.
• Documentation Generation: Automatically generates API documentation (e.g., Swagger).

📚 Documentation

1. FastAPI Official Documentation
2. PostgreSQL Official Documentation
3. Model Context Protocol (MCP) Specification