Postgres MCP Pro

## Overview **Postgres MCP Pro** is an open source Model Context Protocol (MCP) server built to support you and your AI agents throughout the **entire development process**—from initial coding, through testing and deployment, and to production tuning and maintenance. Postgres MCP Pro does much more than wrap a database connection. Features include: - **🔍 Database Health** - analyze index health, connection utilization, buffer cache, vacuum health, sequence limits, replication lag, and more. - **⚡ Index Tuning** - explore thousands of possible indexes to find the best solution for your workload, using industrial-strength algorithms. - **📈 Query Plans** - validate and optimize performance by reviewing EXPLAIN plans and simulating the impact of hypothetical indexes. - **🧠 Schema Intelligence** - context-aware SQL generation based on detailed understanding of the database schema. - **🛡️ Safe SQL Execution** - configurable access control, including support for read-only mode and safe SQL parsing, making it usable for both development and production. Postgres MCP Pro supports both the [Standard Input/Output (stdio)](https://modelcontextprotocol.io/docs/concepts/transports#standard-input%2Foutput-stdio) and [Server-Sent Events (SSE)](https://modelcontextprotocol.io/docs/concepts/transports#server-sent-events-sse) transports, for flexibility in different environments. For additional background on why we built Postgres MCP Pro, see [our launch blog post](https://www.crystaldba.ai/blog/post/announcing-postgres-mcp-server-pro). ## Demo *From Unusable to Lightning Fast* - **Challenge:** We generated a movie app using an AI assistant, but the SQLAlchemy ORM code ran painfully slow. - **Solution:** Using Postgres MCP Pro with Cursor, we fixed the performance issues in minutes. What we did: - 🚀 Fixed performance - including ORM queries, indexing, and caching - 🛠️ Fixed a broken page - by prompting the agent to explore the data, fix queries, and add related content. - 🧠 Improved the top movies - by exploring the data and fixing the ORM query to surface more relevant results. See the video below or read the [play-by-play](examples/movie-app.md). https://github.com/user-attachments/assets/24e05745-65e9-4998-b877-a368f1eadc13 ## Quick Start ### Prerequisites Before getting started, ensure you have: 1. Access credentials for your database. 2. Docker *or* Python 3.12 or higher. #### Access Credentials You can confirm your access credentials are valid by using `psql` or a GUI tool such as [pgAdmin](https://www.pgadmin.org/). #### Docker or Python The choice to use Docker or Python is yours. We generally recommend Docker because Python users can encounter more environment-specific issues. However, it often makes sense to use whichever method you are most familiar with. ### Installation Choose one of the following methods to install Postgres MCP Pro: #### Option 1: Using Docker Pull the Postgres MCP Pro MCP server Docker image. This image contains all necessary dependencies, providing a reliable way to run Postgres MCP Pro in a variety of environments. ```bash docker pull crystaldba/postgres-mcp ``` #### Option 2: Using Python If you have `pipx` installed you can install Postgres MCP Pro with: ```bash pipx install postgres-mcp ``` Otherwise, install Postgres MCP Pro with `uv`: ```bash uv pip install postgres-mcp ``` If you need to install `uv`, see the [uv installation instructions](https://docs.astral.sh/uv/getting-started/installation/). ### Configure Your AI Assistant We provide full instructions for configuring Postgres MCP Pro with Claude Desktop. Many MCP clients have similar configuration files, you can adapt these steps to work with the client of your choice. #### Claude Desktop Configuration You will need to edit the Claude Desktop configuration file to add Postgres MCP Pro. The location of this file depends on your operating system: - MacOS: `~/Library/Application Support/Claude/claude_desktop_config.json` - Windows: `%APPDATA%/Claude/claude_desktop_config.json` You can also use `Settings` menu item in Claude Desktop to locate the configuration file. You will now edit the `mcpServers` section of the configuration file. ##### If you are using Docker ```json { "mcpServers": { "postgres": { "command": "docker", "args": [ "run", "-i", "--rm", "-e", "DATABASE_URI", "crystaldba/postgres-mcp", "--access-mode=unrestricted" ], "env": { "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname" } } } } ``` The Postgres MCP Pro Docker image will automatically remap the hostname `localhost` to work from inside of the container. - MacOS/Windows: Uses `host.docker.internal` automatically - Linux: Uses `172.17.0.1` or the appropriate host address automatically ##### If you are using `uvx` ```json { "mcpServers": { "postgres": { "command": "uvx", "args": [ "postgres-mcp", "--access-mode=unrestricted" ], "env": { "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname" } } } } ``` ##### If you are using `pipx` ```json { "mcpServers": { "postgres": { "command": "postgres-mcp", "args": [ "--access-mode=unrestricted" ], "env": { "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname" } } } } ``` ##### If you are using `uv` ```json { "mcpServers": { "postgres": { "command": "uv", "args": [ "run", "postgres-mcp", "--access-mode=unrestricted" ], "env": { "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname" } } } } ``` ##### Connection URI Replace `postgresql://...` with your [Postgres database connection URI](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS). ##### Access Mode Postgres MCP Pro supports multiple *access modes* to give you control over the operations that the AI agent can perform on the database: - **Unrestricted Mode**: Allows full read/write access to modify data and schema. It is suitable for development environments. - **Restricted Mode**: Limits operations to read-only transactions and imposes constraints on resource utilization (presently only execution time). It is suitable for production environments. To use restricted mode, replace `--access-mode=unrestricted` with `--access-mode=restricted` in the configuration examples above. #### Other MCP Clients Many MCP clients have similar configuration files to Claude Desktop, and you can adapt the examples above to work with the client of your choice. - If you are using Cursor, you can use navigate from the `Command Palette` to `Cursor Settings`, then open the `MCP` tab to access the configuration file. - If you are using Windsurf, you can navigate to from the `Command Palette` to `Open Windsurf Settings Page` to access the configuration file. - If you are using Goose run `goose configure`, then select `Add Extension`. - If you are using Qodo Gen, open the Chat panel, click `Connect more tools`, click `+ Add new MCP`, then add the new configuration. ## SSE Transport Postgres MCP Pro supports the [SSE transport](https://modelcontextprotocol.io/docs/concepts/transports#server-sent-events-sse), which allows multiple MCP clients to share one server, possibly a remote server. To use the SSE transport, you need to start the server with the `--transport=sse` option. For example, with Docker run: ```bash docker run -p 8000:8000 \ -e DATABASE_URI=postgresql://username:password@localhost:5432/dbname \ crystaldba/postgres-mcp --access-mode=unrestricted --transport=sse ``` Then update your MCP client configuration to call the the MCP server. For example, in Cursor's `mcp.json` or Cline's `cline_mcp_settings.json` you can put: ```json { "mcpServers": { "postgres": { "type": "sse", "url": "http://localhost:8000/sse" } } } ``` For Windsurf, the format in `mcp_config.json` is slightly different: ```json { "mcpServers": { "postgres": { "type": "sse", "serverUrl": "http://localhost:8000/sse" } } } ``` ## Postgres Extension Installation (Optional) To enable index tuning and comprehensive performance analysis you need to load the `pg_stat_statements` and `hypopg` extensions on your database. - The `pg_stat_statements` extension allows Postgres MCP Pro to analyze query execution statistics. For example, this allows it to understand which queries are running slow or consuming significant resources. - The `hypopg` extension allows Postgres MCP Pro to simulate the behavior of the Postgres query planner after adding indexes. ### Installing ---