iflow-mcp_pg-mcp-server 0.1.0
PostgreSQL Model Context Protocol (MCP) server for AI agents
Requires Python: >=3.13

# 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