PostgreSQL Explorer MCP server

The PostgreSQL MCP server implements the Model Context Protocol (MCP) to provide a standardized interface between LLM applications and PostgreSQL databases. This server allows AI agents to query and interact with your database through a collection of useful tools.

Installation

Via Smithery

If you're using Claude Desktop, you can install automatically using Smithery:

npx -y @smithery/cli install @gldc/mcp-postgres --client claude

Manual Installation

1. Clone the repository and navigate to the directory:

git clone <repository-url>
cd mcp-postgres

2. Create and activate a virtual environment:

python -m venv venv
source venv/bin/activate  # On Windows, use: venv\Scripts\activate

3. Install dependencies:

pip install -r requirements.txt

Starting the Server

You can start the server with or without a database connection:

# Without a connection (server works but DB tools return friendly errors)
python postgres_server.py

# With connection via environment variable
export POSTGRES_CONNECTION_STRING="postgresql://username:password@host:port/database"
python postgres_server.py

# With connection via command line argument
python postgres_server.py --conn "postgresql://username:password@host:port/database"

Using Docker

# Build the image
docker build -t mcp-postgres .

# Run without database connection
docker run -p 8000:8000 mcp-postgres

# Run with database connection
docker run \
  -e POSTGRES_CONNECTION_STRING="postgresql://username:password@host:port/database" \
  -p 8000:8000 \
  mcp-postgres

Using Different Transport Methods

The server supports multiple transport methods:

# Streamable HTTP (recommended for streaming tool outputs)
python postgres_server.py --transport streamable-http --host 0.0.0.0 --port 8000

# SSE transport (server-sent events)
python postgres_server.py --transport sse --host 0.0.0.0 --port 8000 --mount /mcp

Available Tools

The server provides several tools for interacting with your PostgreSQL database:

Basic Tools

• query: Execute SQL queries against the database
• list_schemas: List all available schemas
• list_tables: List all tables in a specific schema
• describe_table: Get detailed information about a table's structure
• get_foreign_keys: Get foreign key relationships for a table
• find_relationships: Discover both explicit and implied relationships for a table
• db_identity: Show current db/user/host/port, search_path, and version

Typed Tools

• run_query(input): Execute with typed input and return results in markdown or JSON format
• run_query_json(input): Execute and return JSON-serializable rows
• list_schemas_json(input): List schemas with filters
• list_schemas_json_page(input): Paginated listing with filters and pattern matching
• list_tables_json(input): List tables within a schema with filters
• list_tables_json_page(input): Paginated tables listing with filters

Usage Examples

Execute a SQL Query

// run_query (markdown output)
{
  "sql": "SELECT * FROM information_schema.tables WHERE table_schema = %s",
  "parameters": ["public"],
  "row_limit": 50,
  "format": "markdown"
}

// run_query_json (JSON output)
{
  "sql": "SELECT now() as ts",
  "row_limit": 1
}

Get Database Identity

// db_identity (no input needed)
{}

List Schemas with Filters

{
  "include_system": false,
  "include_temp": false,
  "require_usage": true,
  "row_limit": 10000
}

Paginated Schema Listing with Pattern Filter

{
  "include_system": false,
  "include_temp": false,
  "require_usage": true,
  "page_size": 200,
  "cursor": null,
  "name_like": "sales_*",
  "case_sensitive": false
}

List Tables with Filters

{
  "db_schema": "public",
  "name_like": "orders_*",
  "case_sensitive": false,
  "table_types": ["BASE TABLE", "VIEW"],
  "row_limit": 1000
}

Paginated Table Listing

{
  "db_schema": "public",
  "page_size": 200,
  "cursor": null,
  "name_like": "orders_%"
}

Integration with MCP-Compatible Tools

To integrate with tools like Cursor, add the server to your ~/.cursor/mcp.json:

{
  "servers": {
    "postgres": {
      "command": "/path/to/venv/bin/python",
      "args": [
        "/path/to/postgres_server.py"
      ],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://username:password@host:5432/database?ssl=true"
      }
    }
  }
}

Security Options

Configure security settings with environment variables:

# Make the connection read-only (only SELECT/CTE/EXPLAIN/SHOW/VALUES allowed)
export POSTGRES_READONLY=true

# Limit statement execution time to 15 seconds
export POSTGRES_STATEMENT_TIMEOUT_MS=15000

Using a Python MCP Client

Here's an example of using the Python MCP client with Streamable HTTP:

import asyncio
from mcp.client import streamable_http
from mcp.client.session import ClientSession

async def main():
    url = "http://localhost:8000/mcp"
    async with streamable_http.streamablehttp_client(url) as (read, write, _get_session_id):
        session = ClientSession(read, write)
        init = await session.initialize()
        print("protocol:", init.protocolVersion)

        # List tools
        tools = await session.list_tools()
        print("tools:", [t.name for t in tools.tools])

        # Call typed tool: run_query_json
        result = await session.call_tool(
            "run_query_json",
            {"input": {"sql": "SELECT 1 AS n", "row_limit": 1}},
        )
        # Prefer structuredContent if provided; fallback to text content
        if result.structuredContent is not None:
            print("structured:", result.structuredContent)
        else:
            print("text blocks:", [getattr(b, "text", None) for b in result.content])

if __name__ == "__main__":
    asyncio.run(main())

How to install this MCP server

For Claude Code

To add this MCP server to Claude Code, run this command in your terminal:

claude mcp add-json "postgres" '{"command":"/path/to/venv/bin/python","args":["/path/to/postgres_server.py"],"env":{"POSTGRES_CONNECTION_STRING":"postgresql://username:password@host:5432/database?ssl=true"}}'

See the official Claude Code MCP documentation for more details.

For Cursor

There are two ways to add an MCP server to Cursor. The most common way is to add the server globally in the ~/.cursor/mcp.json file so that it is available in all of your projects.

If you only need the server in a single project, you can add it to the project instead by creating or adding it to the .cursor/mcp.json file.

Adding an MCP server to Cursor globally

To add a global MCP server go to Cursor Settings > Tools & Integrations and click "New MCP Server".

When you click that button the ~/.cursor/mcp.json file will be opened and you can add your server like this:

{
    "mcpServers": {
        "postgres": {
            "command": "/path/to/venv/bin/python",
            "args": [
                "/path/to/postgres_server.py"
            ],
            "env": {
                "POSTGRES_CONNECTION_STRING": "postgresql://username:password@host:5432/database?ssl=true"
            }
        }
    }
}

Adding an MCP server to a project

To add an MCP server to a project you can create a new .cursor/mcp.json file or add it to the existing one. This will look exactly the same as the global MCP server example above.

How to use the MCP server

Once the server is installed, you might need to head back to Settings > MCP and click the refresh button.

The Cursor agent will then be able to see the available tools the added MCP server has available and will call them when it needs to.

You can also explicitly ask the agent to use the tool by mentioning the tool name and describing what the function does.

For Claude Desktop

To add this MCP server to Claude Desktop:

1. Find your configuration file:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json
• Linux: ~/.config/Claude/claude_desktop_config.json

2. Add this to your configuration file:

{
    "mcpServers": {
        "postgres": {
            "command": "/path/to/venv/bin/python",
            "args": [
                "/path/to/postgres_server.py"
            ],
            "env": {
                "POSTGRES_CONNECTION_STRING": "postgresql://username:password@host:5432/database?ssl=true"
            }
        }
    }
}

3. Restart Claude Desktop for the changes to take effect