Content
# PostgreSQL Model Context Protocol (PG-MCP) Server
A Model Context Protocol (MCP) server for PostgreSQL databases with enhanced capabilities for AI agents.
More info on the pg-mcp project here:
### [https://stuzero.github.io/pg-mcp/](https://stuzero.github.io/pg-mcp/)
## Overview
PG-MCP is a server implementation of the [Model Context Protocol](https://modelcontextprotocol.io) for PostgreSQL databases. It provides a comprehensive API for AI agents to discover, connect to, query, and understand PostgreSQL databases through MCP's resource-oriented architecture.
This implementation builds upon and extends the [reference Postgres MCP implementation](https://github.com/modelcontextprotocol/servers/tree/main/src/postgres) with several key enhancements:
1. **Full Server Implementation**: Built as a complete server with SSE transport for production use
2. **Multi-database Support**: Connect to multiple PostgreSQL databases simultaneously
3. **Rich Catalog Information**: Extracts and exposes table/column descriptions from the database catalog
4. **Extension Context**: Provides detailed YAML-based knowledge about PostgreSQL extensions like PostGIS and pgvector
5. **Query Explanation**: Includes a dedicated tool for analyzing query execution plans
6. **Robust Connection Management**: Proper lifecycle for database connections with secure connection ID handling
## Features
### Connection Management
- **Connect Tool**: Register PostgreSQL connection strings and get a secure connection ID
- **Disconnect Tool**: Explicitly close database connections when done
- **Connection Pooling**: Efficient connection management with pooling
### Query Tools
- **pg_query**: Execute read-only SQL queries using a connection ID
- **pg_explain**: Analyze query execution plans in JSON format
### Schema Discovery Resources
- List schemas with descriptions
- List tables with descriptions and row counts
- Get column details with data types and descriptions
- View table constraints and indexes
- Explore database extensions
### Data Access Resources
- Sample table data (with pagination)
- Get approximate row counts
### Extension Context
Built-in contextual information for PostgreSQL extensions like:
- **PostGIS**: Spatial data types, functions, and examples
- **pgvector**: Vector similarity search functions and best practices
Additional extensions can be easily added via YAML config files.
## Installation
### Prerequisites
- Python 3.13+
- PostgreSQL database(s)
### Using Docker
```bash
# Clone the repository
git clone https://github.com/stuzero/pg-mcp-server.git
cd pg-mcp-server
# Build and run with Docker Compose
docker-compose up -d
```
### Manual Installation
```bash
# 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
### Testing the Server
The repository includes test scripts to verify server functionality:
```bash
# Basic server functionality test
python test.py "postgresql://username:password@hostname:port/database"
# Claude-powered natural language to SQL conversion
python example-clients/claude_cli.py "Show me the top 5 customers by total sales"
```
The `claude_cli.py` script requires environment variables:
```
# .env file
DATABASE_URL=postgresql://username:password@hostname:port/database
ANTHROPIC_API_KEY=your-anthropic-api-key
PG_MCP_URL=http://localhost:8000/sse
```
### For AI Agents
Example prompt for use with agents:
```
Use the PostgreSQL MCP server to analyze the database.
Available tools:
- connect: Register a database connection string and get a connection ID
- disconnect: Close a database connection
- pg_query: Execute SQL queries using a connection ID
- pg_explain: Get query execution plans
You can explore schema resources via:
pgmcp://{conn_id}/schemas
pgmcp://{conn_id}/schemas/{schema}/tables
pgmcp://{conn_id}/schemas/{schema}/tables/{table}/columns
A comprehensive database description is available at this resource:
pgmcp://{conn_id}/
```
## Architecture
This server is built on:
- **MCP**: The Model Context Protocol foundation
- **FastMCP**: Python library for MCP
- **asyncpg**: Asynchronous PostgreSQL client
- **YAML**: For extension context information
## Security Considerations
- The server runs in read-only mode by default (enforced via transaction settings)
- Connection details are never exposed in resource URLs, only opaque connection IDs
- Database credentials only need to be sent once during the initial connection
## Contributing
Contributions are welcome! Areas for expansion:
- Additional PostgreSQL extension context files
- More schema introspection resources
- Query optimization suggestions
Connection Info
You Might Also Like
markitdown
MarkItDown-MCP is a lightweight server for converting URIs to Markdown.
markitdown
Python tool for converting files and office documents to Markdown.
Filesystem
Node.js MCP Server for filesystem operations with dynamic access control.
Sequential Thinking
A structured MCP server for dynamic problem-solving and reflective thinking.
Fetch
Retrieve and process content from web pages by converting HTML into markdown format.
TrendRadar
TrendRadar: Your hotspot assistant for real news in just 30 seconds.