PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides PostgreSQL integration tools for Claude Code.

Features

• List Databases: List all databases on the PostgreSQL server
• List Schemas: List all schemas in the current database
• List Tables: List tables in a specific schema with optional metadata (size, row count)
• Describe Table: Get detailed table structure (columns, types, constraints, defaults)
• Execute Query: Execute read-only SQL queries (SELECT and WITH statements only)
• List Indexes: List indexes for a specific table with usage statistics
• Explain Query: Get execution plans for SQL queries to analyze performance
• Get Table Stats: Get detailed statistics for tables (row count, size, etc.)
• Security-first design with read-only operations by default
• Compatible with Claude Code's MCP architecture

Prerequisites

• Go 1.25 or later
• Docker (required for running integration tests)
• Access to PostgreSQL databases

Installation

Option 1: Install with Homebrew (Recommended for macOS/Linux)

# Add the tap and install
brew tap sgaunet/homebrew-tools
brew install sgaunet/tools/postgresql-mcp

Option 2: Download from GitHub Releases

1. Download the latest release:

   Visit the releases page and download the appropriate binary for your platform:

   • macOS: postgresql-mcp_VERSION_darwin_amd64 (Intel) or postgresql-mcp_VERSION_darwin_arm64 (Apple Silicon)
   • Linux: postgresql-mcp_VERSION_linux_amd64 (x86_64) or postgresql-mcp_VERSION_linux_arm64 (ARM64)
   • Windows: postgresql-mcp_VERSION_windows_amd64.exe

2. Make it executable (macOS/Linux):

   chmod +x postgresql-mcp_*
   

3. Move to a location in your PATH:

   # Example for macOS/Linux
   sudo mv postgresql-mcp_* /usr/local/bin/postgresql-mcp
   

Option 3: Build from Source

1. Clone the repository:

   git clone https://github.com/sgaunet/postgresql-mcp.git
   cd postgresql-mcp
   

2. Build the project:

   task build
   

   Or manually:

   go build -o postgresql-mcp
   

3. Install to your PATH:

   sudo mv postgresql-mcp /usr/local/bin/
   

Installation for a project

Add the MCP server in the configuration of the project. At the root of your project, create a file named `.mcap.json' with the following content:

{
  "mcpServers": {
    "postgres": {
      "type": "stdio",
      "command": "postgresql-mcp",
      "args": [],
      "env": {
        "POSTGRES_URL": "postgres://postgres:password@localhost:5432/postgres?sslmode=disable"
      }
    }
  }
}

Don't forget to add the .mcp.json file in your .gitignore file if you don't want to commit it. It usually make sense to declare the MCP server for postgresl at the project level, as the database connection is project specific.

Configuration

The PostgreSQL MCP server can be configured via environment variables.

Connection

• POSTGRES_URL: PostgreSQL connection URL (format: postgres://user:password@host:port/dbname?sslmode=prefer)
• DATABASE_URL: Alternative to POSTGRES_URL if POSTGRES_URL is not set

Example:

export POSTGRES_URL="postgres://user:password@localhost:5432/mydb?sslmode=prefer"
# or
export DATABASE_URL="postgres://user:password@localhost:5432/mydb?sslmode=prefer"

Note: Connection environment variables are optional. Use the connect_database tool for explicit connection management. The server will attempt to reconnect automatically when a tool is requested.

Tuning

Connection Management

The server automatically manages database connections with health checks and transparent reconnection:

1. Before every tool operation, the server pings the database to verify the connection is alive.
2. If the ping fails (e.g., database restart, network interruption), the server logs a warning and attempts one automatic reconnection using the original connection parameters.
3. If reconnection succeeds, the operation proceeds normally (with a slight delay).
4. If reconnection fails, the operation returns an error asking the user to reconnect via connect_database.

Only one reconnection attempt is made per operation — there is no retry loop or backoff. For environments with frequent connection drops, consider tuning POSTGRES_MCP_CONN_MAX_LIFETIME and POSTGRES_MCP_CONN_MAX_IDLE_TIME to recycle connections proactively.

Available Tools

The PostgreSQL MCP server provides 9 database tools for interacting with PostgreSQL databases. For detailed information about each tool, including parameters, return values, and examples, see the Tools Documentation.

Security

This MCP server is designed with security as a priority:

• Read-only by default: Only SELECT and WITH queries are permitted
• Parameterized queries: Protection against SQL injection
• Connection validation: Ensures valid database connections before operations
• Error handling: Comprehensive error handling with detailed logging

Usage with Claude Code

1. Configure the MCP server in your Claude Code settings.

2. Set up your database connection via environment variables:

   export POSTGRES_URL="postgres://user:pass@localhost:5432/mydb"
   

3. Use the tools in your conversations:

   List all tables in the public schema
   Describe the users table
   Execute query: SELECT * FROM users LIMIT 10
   

Documentation

• Tools Documentation - Detailed reference for all available tools with parameters and examples
• Architecture - Design patterns, layer responsibilities, and system architecture

Development

Building

go build -o postgresql-mcp

Testing

Unit Tests

# Run unit tests only (no Docker required)
SKIP_INTEGRATION_TESTS=true go test ./...

Integration Tests

# Run all tests including integration tests (requires Docker)
go test ./...

# Run only integration tests
go test -run "TestIntegration" ./...

Note: Integration tests use testcontainers to automatically spin up PostgreSQL instances in Docker containers. This ensures tests are isolated, reproducible, and don't require manual PostgreSQL setup.

Dependencies

• mcp-go - MCP protocol implementation
• pgx/v5 - PostgreSQL driver and connection pool (pgxpool)
• testcontainers-go - Integration testing with Docker containers

Troubleshooting

Connection Issues

• Verify PostgreSQL is running and accessible
• Check the POSTGRES_URL or DATABASE_URL environment variable is correctly set
• Ensure the connection string format is correct: postgres://user:password@host:port/dbname?sslmode=prefer
• Verify database credentials and permissions
• Check firewall and network connectivity

Permission Issues

• Ensure the database user has appropriate read permissions
• Verify the user can connect to the specified database
• Check if the user has access to the schemas and tables you're trying to query

Query Errors

• Remember that only SELECT and WITH statements are allowed
• Ensure proper SQL syntax
• Check that referenced tables and columns exist
• Verify you have read permissions on the objects being queried

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Submit a pull request

License

This project is licensed under MIT license.