Provides tools for interacting with PostgreSQL databases, including SQL query execution, schema and table management, index analysis, query plan analysis, user and role management, permission inspection, and database monitoring.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type @ followed by the MCP server name and your instructions, e.g., "@PostgreSQL MCP Server Explain the query plan for SELECT * FROM orders WHERE user_id = 42"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
A PostgreSQL Model Context Protocol (MCP) server that provides tools for interacting with PostgreSQL databases via HTTP/JSON-RPC.
This project follows a layered modular design:
postgresql_mcp/
├── config.py # Configuration (env vars, CLI args)
├── db/
│ └── pool.py # Connection pool management
├── queries/
│ ├── schema.py # Schema SQL definitions
│ ├── table.py # Table SQL definitions
│ └── version.py # Version SQL
├── common/
│ ├── response.py # Unified JSON response helpers
│ └── formatting.py # Data formatting helpers
├── tools/
│ ├── query.py # execute_query
│ ├── schema.py # list_schemas, list_all_tables
│ ├── table.py # list_tables, describe_table, etc.
│ └── version.py # get_version
├── server.py # MCP server setup
└── tests/Layer | Module | Responsibility |
Config |
| Environment variable loading, dataclass configs |
DB |
| asyncpg connection pool, context manager |
SQL |
| Raw SQL statements (string constants) |
Common |
| Response formatting, error handling, row processing |
Tools |
| MCP tool implementations (business logic) |
Server |
| FastMCP instantiation, tool registration |
Related MCP server: PostgreSQL MCP Server
SQL Query Execution - SELECT, INSERT, UPDATE, DELETE with result formatting
Schema Management - introspect schemas, list all tables
Table Management - list tables, describe structure, row counts, indexes
Version Query - get PostgreSQL version info
pip install -r requirements.txtCreate a .env file:
PG_HOST=127.0.0.1
PG_PORT=5432
PG_DATABASE=postgres
PG_USER=postgres
PG_PASSWORD=your_password
SERVER_PORT=8000# Windows: run.bat or run.py
# macOS/Linux: run.sh or run.py
python run.pypython http_mcp_server.py --port 9000 --db-host 192.168.1.100The server starts on http://0.0.0.0:8000.
curl http://127.0.0.1:8000/The server exposes 9 tools via the MCP protocol:
Tool | Description |
| Execute SQL (SELECT/INSERT/UPDATE/DELETE) |
| Analyze SQL execution plan (EXPLAIN) with performance metrics |
| List database schemas |
| List all tables across all schemas |
| List tables in a specific schema |
| Get table structure (columns, types, PKs) |
| Get approximate row count |
| Get index information |
| Get PostgreSQL version |
The server supports automatic discovery of tool modules from the tools/ directory.
When you add a new .py file to tools/, it is automatically registered at runtime
without restarting the server.
Create a .py file in the tools/ directory (e.g., tools/_example_tool.py)
Define async functions and add them to a __tools__ list:
async def my_tool(param: str) -> str:
"""Description of my tool.
Args:
param: Description of parameter.
Returns:
JSON string with results.
"""
...your code...
__tools__ = [
(my_tool, "my_tool", "Description of my tool"),
]Save the file — the server auto-discovers and registers it
Variable | Default | Description |
|
| Enable/disable dynamic tool loading |
To disable: set HOTPLUG_ENABLED=false in .env.
The tools/_example_tool.py file demonstrates how to create a dynamic tool.
It queries the orders table and returns weekly sales data.
{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "execute_query",
"arguments": {
"sql": "SELECT * FROM users LIMIT 10",
"limit": 10
}
}
}Add this server to your MCP client configuration:
{
"mcpServers": {
"postgres": {
"url": "http://127.0.0.1:8000/mcp"
}
}
}A sample config file is available in docs/mcp_config.json.
Variable | Default | Description |
|
| PostgreSQL host |
|
| PostgreSQL port |
|
| Database name |
|
| Database user |
| (empty) | Database password |
|
| Server port |
|
| Server host |
|
| Log level |
Command-line flags override .env values:
python http_mcp_server.py --port 8000 --db-host 192.168.1.100 --db-name mydbpostgresql-mcp/
├── postgresql_mcp/
│ ├── __init__.py
│ ├── config.py # Configuration management
│ ├── db/
│ │ ├── __init__.py
│ │ └── pool.py # Connection pool (asyncpg)
│ ├── queries/
│ │ ├── __init__.py
│ │ ├── schema.py # Schema SQL queries
│ │ ├── table.py # Table SQL queries
│ │ └── version.py # Version SQL query
│ ├── common/
│ │ ├── __init__.py
│ │ ├── response.py # Response helpers
│ │ └── formatting.py # Data formatting helpers
│ ├── tools/
│ │ ├── __init__.py
│ │ ├── query.py
│ │ ├── schema.py
│ │ ├── table.py
│ │ └── version.py
│ ├── server.py # MCP server setup
│ └── tests/
├── run.py # Cross-platform launcher (Windows/macOS/Linux)
├── run.bat # Windows wrapper (delegates to run.py)
├── run.sh # macOS/Linux wrapper (delegates to run.py)
├── http_mcp_server.py # Direct entry point
├── docs/
│ ├── README.md
│ └── mcp_config.json # Sample MCP client config
├── pyproject.toml # Project metadata
├── requirements.txt # Dependencies
├── .env.example # Environment template
└── README.mdUse environment variables for sensitive configuration
Never commit database credentials (.env is gitignored)
Use connection pooling (enabled by default)
Set appropriate database user permissions (least privilege)
Consider using SSL/TLS for database connections in production
execute_query has a 100,000-character SQL length limit to prevent abuse
MIT License
This server cannot be deployed
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
