Postgres MCP Server

A TypeScript-based Model Context Protocol (MCP) server that provides AI assistants with read-only access to PostgreSQL databases for comprehensive analysis and optimization recommendations.

Installation

NPM (Recommended)

npm install -g blankbrackets-postgres-mcp-server

Package: blankbrackets-postgres-mcp-server

Docker

docker pull blankbrackets/postgres-mcp-server:latest

Image: blankbrackets/postgres-mcp-server

Features

🎯 Comprehensive Database Analysis - Systematic analysis of entire database with prioritized action plans
🔒 Read-Only by Design - Multi-layer protection ensures no data modifications
📊 10 Specialized Tools - From table discovery to query performance analysis
🤖 AI-Optimized - Designed specifically for LLM-based database optimization
📈 Production-Ready - Extensive logging, error handling, and security features
⚡ Fast Performance - Sub-50ms tool execution for interactive use

What It Detects

• Performance Issues: Slow queries, missing indexes, excessive sequential scans
• Index Problems: Unused indexes, unindexed foreign keys, duplicate indexes
• Schema Issues: Poor data types, high null rates, bloat
• Health Problems: Replication lag, invalid constraints, sequences near max value
• Maintenance Needs: Tables needing VACUUM/ANALYZE, connection issues

Quick Start

Option 1: NPM (Easiest)

Install the published package globally:

npm install -g blankbrackets-postgres-mcp-server

Then configure in Claude Desktop:

{
  "mcpServers": {
    "postgres-analyzer": {
      "command": "blankbrackets-postgres-mcp-server",
      "env": {
        "DATABASE_URL": "postgresql://readonly_user:[email protected]:5432/your_db"
      }
    }
  }
}

Restart Claude Desktop and start analyzing!

Option 2: Docker

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

# Run with your database
docker run -i \
  -e DATABASE_URL="postgresql://readonly_user:[email protected]:5432/your_db" \
  -v $(pwd)/logs:/app/logs \
  postgres-mcp-server

See docs/DOCKER.md for detailed Docker instructions.

Option 3: From Source

# Clone the repository
git clone https://github.com/blankbrackets/postgres-mcp.git
cd postgres-mcp

# Install dependencies
npm install

# Build
npm run build

# Configure
cp env.example .env
# Edit .env with your DATABASE_URL

# Run
npm start

Configuration

Environment Variables:

DATABASE_URL=postgresql://readonly_user:[email protected]:5432/your_database
LOG_LEVEL=info              # Optional: error, warn, info, debug
QUERY_TIMEOUT=30000         # Optional: Query timeout in ms

Integration with AI Assistants

Claude Desktop (~/Library/Application Support/Claude/claude_desktop_config.json):

Using NPM package (recommended after npm install -g blankbrackets-postgres-mcp-server):

{
  "mcpServers": {
    "postgres-analyzer": {
      "command": "blankbrackets-postgres-mcp-server",
      "env": {
        "DATABASE_URL": "postgresql://readonly_user:[email protected]:5432/your_database"
      }
    }
  }
}

Using local build (for development):

{
  "mcpServers": {
    "postgres-analyzer": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-mcp/build/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://readonly_user:[email protected]:5432/your_database"
      }
    }
  }
}

See examples/ for more configuration examples including Cursor and Docker.

4. Usage

Restart your MCP client (Claude Desktop, Cursor, etc.) and ask:

Do a comprehensive analysis and optimization of my database

The AI will systematically analyze your database and provide actionable recommendations.

Tools

🎯 comprehensive_database_analysis - Start Here!

Scans the entire database for issues and creates a complete action plan.

Use when: "Optimize my database", "Find all issues", "Complete analysis"

Returns:

• Health score and critical issues summary
• Prioritized list of tables requiring attention
• Step-by-step analysis workflow
• Quick wins and long-term improvements

⭐ get_database_health - Overall Health Check

Database-wide metrics and health assessment.

Returns:

• Cache performance (hit ratios)
• Table statistics (vacuum, analyze, bloat)
• Index statistics (unused indexes)
• Connection utilization (active vs idle)
• Replication health (lag, slots)
• Constraint health (invalid constraints)
• Sequence health (near max value warnings)

📋 list_tables - Table Discovery

Lists all tables with schemas, types, row counts, and sizes.

Use before any table-specific tools to get exact table names.

🔥 analyze_query_performance - Find Slow Queries

Identifies slowest queries using pg_stat_statements or analyzes specific queries with EXPLAIN.

Returns:

• Top N slowest queries by execution time
• Query statistics (calls, mean/max time, cache hits)
• EXPLAIN plans for specific queries
• Optimization recommendations

Note: Install pg_stat_statements extension for best results.

📊 get_query_statistics - Table Performance Stats

Essential for table analysis! Provides detailed query/IO statistics for a specific table.

Returns:

• Sequential vs index scan counts
• Cache hit ratios (buffer cache, index cache)
• Insert/update/delete statistics
• Per-index usage statistics
• Bloat estimation
• Last vacuum/analyze timestamps

🎯 suggest_indexing_strategies - Index Optimization

Analyzes indexing for a specific table.

Returns:

• Index usage statistics
• Unused indexes (never scanned)
• Missing indexes on foreign keys
• Duplicate/redundant indexes
• High sequential scan warnings

🏗️ suggest_schema_optimizations - Schema Design

Analyzes schema design for a specific table.

Returns:

• Column statistics (nullability, cardinality, average width)
• Foreign key analysis (indexed vs unindexed)
• Table bloat metrics
• Data type issues (TEXT with low cardinality, oversized VARCHAR)

📝 get_table_info - Table Structure

Returns table structure: columns, data types, indexes, and constraints.

⚠️ execute_query - Custom SQL (Last Resort)

Execute custom READ-ONLY SQL queries.

Use only when specialized tools cannot provide the data.

Allowed: SELECT, WITH, EXPLAIN, SHOW, TABLE, VALUES
Blocked: INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, and all write operations

Resources

postgres://database/metadata

Complete schema structure with all schemas, tables, columns, indexes, and constraints.

postgres://system/catalog-reference

PostgreSQL system catalog documentation to prevent common query errors.

Read this before using execute_query!

Security

Read-Only Enforcement

The server is read-only by design with multiple protection layers:

1. Connection Level: default_transaction_read_only=on
2. SQL Validation: Blocks write keywords (INSERT, UPDATE, DELETE, etc.)
3. Code Level: No write operations exposed in any tool
4. Database User: Recommend SELECT-only privileges (see below)

Setting Up a Read-Only User

-- Create read-only user
CREATE USER postgres_mcp_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_database TO postgres_mcp_readonly;
GRANT USAGE ON SCHEMA public TO postgres_mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres_mcp_readonly;
GRANT pg_read_all_stats TO postgres_mcp_readonly;

See examples/setup-readonly-user.sql for complete setup.

Best Practices

• ✅ Use a dedicated read-only database user
• ✅ Never commit .env files to version control
• ✅ Use SSL/TLS for database connections in production
• ✅ Rotate credentials regularly
• ✅ Limit database access by IP if possible

Example Prompts

See examples/example-prompts.md for comprehensive examples.

Recommended workflow:

1. "Do a comprehensive analysis and optimization of my database"
   → Creates complete action plan

2. "What are my slowest queries?"
   → Identifies performance bottlenecks

3. "List all tables"
   → Discovers database structure

4. "Analyze the [table_name] table"
   → Deep dive into specific tables

Configuration

Environment Variables

DATABASE_URL=postgresql://user:password@host:port/database  # Required
QUERY_TIMEOUT=30000      # Optional: Query timeout in ms (default: 30000)
LOG_LEVEL=info           # Optional: error, warn, info, debug (default: info)

Logging

Logs are written to:

• File: logs/postgres-mcp-server.log (rotated at 10MB, 5 files kept)
• stderr: Captured by Claude Desktop/Cursor for debugging

# View logs
tail -f logs/postgres-mcp-server.log

# Enable debug logging
# Add to config: "LOG_LEVEL": "debug"

Development

# Install dependencies
npm install

# Development mode (with auto-reload)
npm run dev

# Build for production
npm run build

# Run production build
npm start

# Clean build directory
npm run clean

Architecture

• TypeScript with official @modelcontextprotocol/sdk
• Stdio transport for Claude Desktop and Cursor compatibility
• PostgreSQL client via pg library
• Winston logging with file rotation
• ES2022 modules with full type safety

See docs/ARCHITECTURE.md for detailed architecture documentation.

Troubleshooting

Common Issues

Server won't start:

• Check DATABASE_URL is set
• Verify database is accessible
• View logs: tail -f logs/postgres-mcp-server.log

Tool execution fails:

• Use list_tables first to discover exact table names
• Check PostgreSQL permissions
• Read postgres://system/catalog-reference resource

Case sensitivity errors:

• PostgreSQL is case-sensitive with quoted identifiers
• Use exact table names from list_tables
• Quote mixed-case tables: "MyTable"

See docs/TROUBLESHOOTING.md for detailed troubleshooting.

How It Works

1. Connection: Connects to PostgreSQL with read-only mode enforced
2. Discovery: LLM uses list_tables to see available tables
3. Analysis: Uses specialized tools to gather metrics
4. Recommendations: LLM analyzes data and suggests optimizations

All operations are read-only - the database cannot be modified.

Requirements

• Node.js: 18.0.0 or higher
• PostgreSQL: 9.6 or higher
• Database Access: Read permissions (SELECT)

Optional but recommended:

• pg_stat_statements extension for query performance analysis

PostgreSQL System Catalog Reference

The server queries PostgreSQL system tables like:

• information_schema.* - ANSI SQL standard metadata
• pg_stat_user_tables - Table statistics
• pg_stat_user_indexes - Index usage
• pg_indexes - Index definitions

Important: Column names vary between views. See docs/POSTGRESQL_REFERENCE.md.

Docker Support

Using Docker

# Build
docker build -t postgres-mcp-server .

# Run
docker run -i \
  -e DATABASE_URL="postgresql://user:[email protected]:5432/db" \
  postgres-mcp-server

Using with Docker Compose

See examples/docker-compose.yml:

docker-compose -f examples/docker-compose.yml up

Docker Hub ✅ Published

Pre-built multi-architecture images available:

# Pull latest version
docker pull blankbrackets/postgres-mcp-server:latest

# Or specific version
docker pull blankbrackets/postgres-mcp-server:1.0.0

# Run directly
docker run -i \
  -e DATABASE_URL="postgresql://user:[email protected]:5432/db" \
  blankbrackets/postgres-mcp-server:latest

Supported platforms: linux/amd64, linux/arm64

See docs/DOCKER.md for comprehensive Docker documentation.

Registry Submission

This server is ready for submission to:

• Smithery MCP Registry (smithery.ai) - Official MCP catalog
• NPM Registry (npmjs.com) - For easy installation
• Docker Hub (hub.docker.com) - For containerized deployment

See docs/REGISTRY_SUBMISSION.md for submission instructions.

Contributing

Contributions are welcome! Please see docs/CONTRIBUTING.md for guidelines.

Areas for Contribution

• Additional analysis tools
• Performance improvements
• Better error messages
• Documentation improvements
• Bug fixes
• Translations
• Integration examples

License

MIT License - see LICENSE file for details.

Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Logs: Check logs/postgres-mcp-server.log for debugging
• Documentation: See docs/ folder

Acknowledgments

Built with:

• Model Context Protocol by Anthropic
• PostgreSQL
• Node.js and TypeScript
• Winston for logging

Related Projects

• MCP Specification
• MCP TypeScript SDK
• PostgreSQL Documentation
• Postgres MCP Pro - Python-based alternative

Made with ❤️ for the MCP and PostgreSQL communities