A Model Context Protocol (MCP) server that enables Claude Desktop to interact with your local PostgreSQL database through natural language queries.
- 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
-
Save the files to a folder, e.g.,
C:\Users\YourUsername\postgres-mcp\ -
Open Command Prompt in that folder and run:
python -m venv venv venv\Scripts\activate pip install -r requirements.txt
-
Copy
.env.exampleto.envand edit with your database credentials -
Edit Claude Desktop config at:
C:\Users\YourUsername\AppData\Roaming\Claude\claude_desktop_config.jsonAdd 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" } } } } -
Restart Claude Desktop completely (Quit and reopen)
-
Look for the 🔌 icon in Claude Desktop - you're connected!
- Python 3.10 or higher
- PostgreSQL database running locally or remotely
- Claude Desktop application
# Create a virtual environment (recommended)
python -m venv venv
# Activate the virtual environment
# On Windows:
venv\Scripts\activate
# On macOS/Linux:
# source venv/bin/activate
# Install required packages
pip install -r requirements.txtCreate a .env file in the same directory as postgres_mcp.py:
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=your_database_name
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_passwordSecurity Note: Never commit your .env file to version control. Add it to .gitignore.
Verify your server works before connecting to Claude Desktop:
# Test that the file has no syntax errors
python -m py_compile postgres_mcp.py
# The server is ready if no errors appearFor Windows 11, the configuration file is located at:
%APPDATA%\Claude\claude_desktop_config.json
This typically expands to:
C:\Users\YourUsername\AppData\Roaming\Claude\claude_desktop_config.json
Other OS locations:
- macOS:
~/Library/Application Support/Claude/claude_desktop_config.json - Linux:
~/.config/Claude/claude_desktop_config.json
Edit the config file to add your PostgreSQL MCP server.
For Windows 11, your configuration will look like this:
{
"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_name",
"POSTGRES_USER": "your_username",
"POSTGRES_PASSWORD": "your_password"
}
}
}
}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 PythonC:\\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:
{
"mcpServers": {
"postgres": {
"command": "C:\\Users\\John\\Documents\\postgres-mcp\\venv\\Scripts\\python.exe",
"args": ["C:\\Users\\John\\Documents\\postgres-mcp\\postgres_mcp.py"],
"env": {
"POSTGRES_HOST": "localhost",
"POSTGRES_PORT": "5432",
"POSTGRES_DB": "myapp",
"POSTGRES_USER": "postgres",
"POSTGRES_PASSWORD": "secretpassword"
}
}
}
}Example for macOS/Linux:
{
"mcpServers": {
"postgres": {
"command": "/Users/yourname/projects/postgres-mcp/venv/bin/python",
"args": ["/Users/yourname/projects/postgres-mcp/postgres_mcp.py"],
"env": {
"POSTGRES_HOST": "localhost",
"POSTGRES_PORT": "5432",
"POSTGRES_DB": "myapp",
"POSTGRES_USER": "postgres",
"POSTGRES_PASSWORD": "secretpassword"
}
}
}
}After saving the configuration:
- Quit Claude Desktop completely
- Reopen Claude Desktop
- Look for the 🔌 icon indicating MCP servers are connected
Once configured, you can ask Claude to interact with your database:
"What tables are in my database?"
"Show me the structure of the users table"
"Give me a sample of 20 rows from the orders table"
"How many users signed up in the last month?"
"Show me the top 10 products by sales"
"Find all orders with status 'pending' from the last week"
"What's the average order value by customer segment?"
"Analyze customer retention rates by cohort"
"Which products have the highest return rate?"
"Show me sales trends over the last 6 months"
The MCP server provides these tools to Claude:
- postgres_execute_query: Execute any SELECT query
- postgres_list_tables: List all tables in a schema
- postgres_describe_table: Get table structure and constraints
- postgres_get_table_sample: Preview table data
All tools support both Markdown (human-readable) and JSON (machine-readable) output formats.
- 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
- Check that the config file is valid JSON (use a JSON validator)
- Verify all paths are absolute paths, not relative
- Ensure your virtual environment's Python path is correct
- Check Claude Desktop logs for error messages
- Verify PostgreSQL is running:
psql -U your_username -d your_database - Check your connection credentials in the config
- Ensure PostgreSQL accepts connections from localhost
- Verify the table exists: Ask Claude to list tables first
- Check you're using the correct schema (default is 'public')
- Ensure your user has SELECT permissions on the table
- Ensure you're using the virtual environment's Python
- Reinstall dependencies:
pip install -r requirements.txt - Verify Python version:
python --version(should be 3.10+)
Adjust the character limit for large result sets by modifying the constant in postgres_mcp.py:
CHARACTER_LIMIT = 25000 # Increase or decrease as neededCustomize the database connection pool in the database_lifespan function:
pool = await asyncpg.create_pool(
# ... other params ...
min_size=2, # Minimum connections
max_size=10 # Maximum connections
)To add a new tool to the server:
- Define a Pydantic model for input validation
- Create the tool function with
@mcp.tool()decorator - Add comprehensive docstrings
- Handle errors gracefully
Example:
class NewToolInput(BaseModel):
param: str = Field(..., description="Parameter description")
@mcp.tool(name="postgres_new_tool")
async def new_tool(params: NewToolInput, ctx) -> str:
"""Tool description."""
# Implementation
passThis MCP server is provided as-is for personal and commercial use.
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
Built using: