PostgreSQL MCP Server

A Model Context Protocol (MCP) server that enables Claude Desktop to interact with your local PostgreSQL database through natural language queries.

Features

• Execute SQL Queries: Run read-only SELECT queries safely

• Database Exploration: List tables and inspect schemas

• Table Description: Get detailed column information and constraints

• Sample Data: Preview table contents without full queries

• Multiple Output Formats: Markdown tables or JSON for different use cases

• Safety First: Read-only operations prevent accidental data modification

🪟 Quick Start for Windows 11

1. Save the files to a folder, e.g., C:\Users\YourUsername\postgres-mcp\

2. Open Command Prompt in that folder and run:

   python -m venv venv venv\Scripts\activate pip install -r requirements.txt

3. Copy and edit with your database credentials

4. Edit Claude Desktop config at:

   C:\Users\YourUsername\AppData\Roaming\Claude\claude_desktop_config.json

   Add this (with YOUR actual paths):

   { "mcpServers": { "postgres": { "command": "C:\\Users\\YourUsername\\postgres-mcp\\venv\\Scripts\\python.exe", "args": ["C:\\Users\\YourUsername\\postgres-mcp\\postgres_mcp.py"], "env": { "POSTGRES_HOST": "localhost", "POSTGRES_PORT": "5432", "POSTGRES_DB": "your_database", "POSTGRES_USER": "postgres", "POSTGRES_PASSWORD": "your_password" } } } }

5. Restart Claude Desktop completely (Quit and reopen)

6. Look for the 🔌 icon in Claude Desktop - you're connected!

Prerequisites

• Python 3.10 or higher

• PostgreSQL database running locally or remotely

• Claude Desktop application

Installation

1. Install Dependencies

2. Set Up Environment Variables

Create a .env file in the same directory as postgres_mcp.py:

Security Note: Never commit your .env file to version control. Add it to .gitignore.

3. Test the Server

Verify your server works before connecting to Claude Desktop:

Configuring Claude Desktop

1. Locate Your Claude Desktop Config

For Windows 11, the configuration file is located at:

This typically expands to:

Other OS locations:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

• Linux: ~/.config/Claude/claude_desktop_config.json

2. Add Your MCP Server

Edit the config file to add your PostgreSQL MCP server.

For Windows 11, your configuration will look like this:

Important: Replace the paths and credentials with your actual values:

• C:\\Users\\YourUsername\\postgres-mcp\\venv\\Scripts\\python.exe → Full path to your virtual environment's Python

• C:\\Users\\YourUsername\\postgres-mcp\\postgres_mcp.py → Full path to the server script

• Note the double backslashes (\\) in Windows paths for JSON

Complete Example for Windows 11:

Example for macOS/Linux:

3. Restart Claude Desktop

After saving the configuration:

1. Quit Claude Desktop completely

2. Reopen Claude Desktop

3. Look for the 🔌 icon indicating MCP servers are connected

Usage Examples

Once configured, you can ask Claude to interact with your database:

Explore Your Database

Query Your Data

Complex Analysis

Available Tools

The MCP server provides these tools to Claude:

1. postgres_execute_query: Execute any SELECT query

2. postgres_list_tables: List all tables in a schema

3. postgres_describe_table: Get table structure and constraints

4. postgres_get_table_sample: Preview table data

All tools support both Markdown (human-readable) and JSON (machine-readable) output formats.

Security Features

• Read-Only Operations: Only SELECT queries are allowed

• Query Validation: Blocks INSERT, UPDATE, DELETE, DROP, etc.

• Error Handling: Clear, actionable error messages

• Connection Pooling: Efficient database connection management

Troubleshooting

Claude Desktop doesn't show the MCP server

1. Check that the config file is valid JSON (use a JSON validator)

2. Verify all paths are absolute paths, not relative

3. Ensure your virtual environment's Python path is correct

4. Check Claude Desktop logs for error messages

"Connection refused" errors

1. Verify PostgreSQL is running: psql -U your_username -d your_database

2. Check your connection credentials in the config

3. Ensure PostgreSQL accepts connections from localhost

"Table not found" errors

1. Verify the table exists: Ask Claude to list tables first

2. Check you're using the correct schema (default is 'public')

3. Ensure your user has SELECT permissions on the table

Python module errors

1. Ensure you're using the virtual environment's Python

2. Reinstall dependencies: pip install -r requirements.txt

3. Verify Python version: python --version (should be 3.10+)

Advanced Configuration

Custom Response Limits

Adjust the character limit for large result sets by modifying the constant in postgres_mcp.py:

Connection Pool Settings

Customize the database connection pool in the database_lifespan function:

Development

Adding New Tools

To add a new tool to the server:

1. Define a Pydantic model for input validation

2. Create the tool function with @mcp.tool() decorator

3. Add comprehensive docstrings

4. Handle errors gracefully

Example:

License

This MCP server is provided as-is for personal and commercial use.

Support

For issues specific to this MCP server, review the troubleshooting section above.

For general MCP protocol questions, visit: https://modelcontextprotocol.io For Claude Desktop support, visit: https://support.claude.com

Credits

Built using:

• Model Context Protocol

• FastMCP

• asyncpg