PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides tools and prompts for PostgreSQL database monitoring and optimization.

Features

• Connection statistics and monitoring
• Database size information
• Index usage statistics
• Slow query detection
• Cache hit ratio monitoring
• Table statistics
• Table analysis tools
• Query EXPLAIN plan generation

Installation

go get github.com/yourusername/psql-go-mcp

Configuration

The server requires PostgreSQL connection details to be provided via environment variables:

export PG_HOST=localhost
export PG_PORT=5432
export PG_USER=postgres
export PG_PASSWORD=postgres
export PG_DB=postgres
export PG_SSLMODE=disable

Usage

Starting the Server

psql-go-mcp

Available Tools

1. postgresql/connection-stats: Get PostgreSQL connection statistics
2. postgresql/database-size: Get PostgreSQL database size information
3. postgresql/index-usage: Get PostgreSQL index usage statistics
4. postgresql/slow-queries: Get PostgreSQL slow query statistics
5. postgresql/cache-hit-ratio: Get PostgreSQL cache hit ratio
6. postgresql/table-stats: Get PostgreSQL table statistics
7. postgresql/analyze-table: Analyze a specific PostgreSQL table
8. postgresql/explain-query: Get the EXPLAIN plan for a PostgreSQL query

Available Prompts

1. connection-optimization: Guidance on optimizing PostgreSQL connections
2. query-optimization: Guidance on optimizing PostgreSQL queries

Using with MCP Inspector

The MCP Inspector provides a visual interface for interacting with the PostgreSQL MCP server. Here's how to use it:

1. Install the MCP Inspector:

   npm install -g @modelcontextprotocol/inspector
   

2. Create Inspector Configuration: Create a file named inspector-config.json with the following content:

   {
     "mcpServers": {
       "my-server": {
         "type": "sse",
         "url": "http://localhost:8080/events"
       }
     }
   }
   

3. Start the Inspector:

   npx @modelcontextprotocol/inspector --config inspector-config.json --server my-server
   

4. Access the Inspector UI:

   • Open your browser and navigate to http://127.0.0.1:6274
   • You should see the Inspector interface with your PostgreSQL MCP server connected
   • Use the interface to:
     • View available tools and prompts
     • Execute database monitoring tools
     • Get optimization guidance
     • Monitor real-time database statistics

Requirements

• Go 1.23 or later
• PostgreSQL server
• MCP-compatible client