Skip to content

Installation and Configuration

Jump to bottom
Temp edited this page Oct 3, 2025 · 1 revision

Installation and Configuration

Complete installation guide for PostgreSQL MCP Server across all platforms and deployment methods.

📋 System Requirements

PostgreSQL

  • Version: 13, 14, 15, 16, or 17
  • Access: Network connectivity to database
  • Credentials: Valid username and password
  • Extensions: See Extension Setup

System Requirements

  • Python: 3.10 or later (for Python installation)
  • Docker: 20.10+ (for Docker installation)
  • Memory: 512 MB minimum, 1 GB recommended
  • Storage: 100 MB for server files

🚀 Installation Methods

Method 1: Docker (Recommended)

Fastest and most reliable installation method.

Pull the Image

docker pull neverinfamous/postgres-mcp:latest

Run with Environment Variable

docker run -i --rm \
  -e DATABASE_URI="postgresql://username:password@localhost:5432/dbname" \
  neverinfamous/postgres-mcp:latest \
  --access-mode=restricted

Using Docker Compose

version: '3.8'
services:
  postgres-mcp:
    image: neverinfamous/postgres-mcp:latest
    stdin_open: true
    environment:
      - DATABASE_URI=postgresql://user:pass@postgres:5432/db
    command: ["--access-mode=restricted"]
    depends_on:
      - postgres

  postgres:
    image: postgres:16
    environment:
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=mydb
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Start services:

docker-compose up -d

Method 2: Python/pip Installation

For Python environments and development.

Install from PyPI

pip install postgres-mcp

Verify Installation

postgres-mcp --version

Run the Server

export DATABASE_URI="postgresql://user:pass@localhost:5432/db"
postgres-mcp --access-mode=restricted

Method 3: From Source

For development and customization.

Clone Repository

git clone https://github.com/neverinfamous/postgres-mcp.git
cd postgres-mcp

Install Dependencies (using uv)

# Install uv if not installed
curl -LsSf https://astral.sh/uv/install.sh | sh

# Sync dependencies
uv sync

Run Tests

uv run pytest -v

Run Server

export DATABASE_URI="postgresql://user:pass@localhost:5432/db"
uv run python -m postgres_mcp.server --access-mode=restricted

🔧 Configuration

Environment Variables

DATABASE_URI (Required)

# Basic format
export DATABASE_URI="postgresql://username:password@hostname:port/database"

# With SSL
export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=require"

# With connection pooling
export DATABASE_URI="postgresql://user:pass@host:5432/db?pool_size=20"

# Cloud providers
# AWS RDS
export DATABASE_URI="postgresql://admin:pass@mydb.region.rds.amazonaws.com:5432/db"

# Google Cloud SQL
export DATABASE_URI="postgresql://user:pass@/db?host=/cloudsql/project:region:instance"

# Azure Database
export DATABASE_URI="postgresql://user@server:pass@server.postgres.database.azure.com:5432/db"

Access Mode

# Restricted mode (production) - read-only
--access-mode=restricted

# Unrestricted mode (development) - full access
--access-mode=unrestricted

MCP Client Configuration

See MCP Configuration for detailed client setup.

Claude Desktop (Quick Reference)

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "DATABASE_URI=postgresql://user:pass@host:5432/db",
        "neverinfamous/postgres-mcp:latest",
        "--access-mode=restricted"
      ]
    }
  }
}

Cursor IDE (Quick Reference)

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "postgres-mcp",
      "args": ["--access-mode=restricted"],
      "env": {
        "DATABASE_URI": "postgresql://user:pass@host:5432/db"
      }
    }
  }
}

🔐 Security Configuration

Database User Setup

Create Read-Only User (Restricted Mode)

-- Create user
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';

-- Grant connection
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO mcp_readonly;

-- Grant SELECT on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;

-- Grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_readonly;

-- Grant EXECUTE on functions (for extensions)
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO mcp_readonly;

Create Full Access User (Unrestricted Mode)

-- Create user
CREATE USER mcp_admin WITH PASSWORD 'secure_password';

-- Grant full privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO mcp_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mcp_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mcp_admin;

SSL/TLS Configuration

Enable SSL in PostgreSQL

Edit postgresql.conf:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/root.crt'

Connect with SSL

export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=require"

# With certificate verification
export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=verify-full&sslrootcert=/path/to/root.crt"

🧩 Extension Installation

Required Extensions

Install these extensions for core functionality:

-- Query tracking (required for performance tools)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Text similarity (required for text tools)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Fuzzy matching (required for text tools)
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

Note: pg_stat_statements requires PostgreSQL restart after enabling in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Optional Extensions

-- Hypothetical indexes (for performance testing)
CREATE EXTENSION IF NOT EXISTS hypopg;

-- Vector similarity (for AI/ML applications)
CREATE EXTENSION IF NOT EXISTS vector;

-- Geospatial operations
CREATE EXTENSION IF NOT EXISTS postgis;

See Extension Setup for detailed installation instructions.

✅ Verification

Test Connection

# Using MCP client
list_schemas()
# Expected: List of database schemas

Check Database Health

analyze_db_health(health_type="all")
# Expected: Comprehensive health report

Verify Extensions

get_top_queries(sort_by="total_time", limit=5)
# Expected: Top 5 queries (requires pg_stat_statements)

Test Security Mode

# In restricted mode, this should fail:
execute_sql(sql="DROP TABLE test")
# Expected: "Operation not allowed in restricted mode"

🐳 Advanced Docker Configuration

Custom Network

# Create network
docker network create postgres-mcp-net

# Run PostgreSQL
docker run -d \
  --name postgres \
  --network postgres-mcp-net \
  -e POSTGRES_PASSWORD=password \
  postgres:16

# Run MCP server
docker run -i --rm \
  --network postgres-mcp-net \
  -e DATABASE_URI="postgresql://postgres:password@postgres:5432/postgres" \
  neverinfamous/postgres-mcp:latest

Volume Mounting

# Mount configuration files
docker run -i --rm \
  -v $(pwd)/config:/app/config \
  -e DATABASE_URI="postgresql://user:pass@host:5432/db" \
  neverinfamous/postgres-mcp:latest

Health Checks

version: '3.8'
services:
  postgres-mcp:
    image: neverinfamous/postgres-mcp:latest
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "postgres", "-U", "user"]
      interval: 30s
      timeout: 10s
      retries: 3

🔍 Troubleshooting Installation

Docker Issues

Container Exits Immediately

# Check logs
docker logs <container_id>

# Ensure -i flag is used
docker run -i --rm ...

# Verify DATABASE_URI is set
docker exec <container_id> env | grep DATABASE_URI

Can't Connect to Database

# From host
docker run -i --rm \
  -e DATABASE_URI="postgresql://user:pass@host.docker.internal:5432/db" \
  neverinfamous/postgres-mcp:latest

# Or use host networking
docker run -i --rm --network=host \
  -e DATABASE_URI="postgresql://user:pass@localhost:5432/db" \
  neverinfamous/postgres-mcp:latest

Python Installation Issues

Module Not Found

# Ensure correct Python version
python --version  # Should be 3.10+

# Install in virtual environment
python -m venv venv
source venv/bin/activate  # Linux/Mac
# or
venv\Scripts\activate  # Windows
pip install postgres-mcp

Permission Errors

# Use --user flag
pip install --user postgres-mcp

# Or use virtual environment (recommended)

Connection Issues

Authentication Failed

-- Check pg_hba.conf
# Allow password authentication
host    all    all    0.0.0.0/0    scram-sha-256

Database Not Found

# List databases
psql -h localhost -U postgres -l

# Create database if needed
createdb -h localhost -U postgres mydb

📊 Performance Tuning

PostgreSQL Configuration

Optimize for MCP server workload:

# postgresql.conf

# Memory
shared_buffers = 4GB              # 25% of RAM
work_mem = 64MB                   # Per query operation
maintenance_work_mem = 1GB        # For maintenance operations

# Connections
max_connections = 100             # Adjust based on workload

# Query Performance
effective_cache_size = 12GB       # 75% of RAM
random_page_cost = 1.1           # For SSDs

# Logging (for pg_stat_statements)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Connection Pooling

Add to DATABASE_URI:

export DATABASE_URI="postgresql://user:pass@host:5432/db?pool_size=20&max_overflow=10"

📚 Next Steps

After installation:

  1. Extension Setup - Install PostgreSQL extensions
  2. MCP Configuration - Configure your MCP client
  3. Quick Start - Start using the tools
  4. Security Best Practices - Secure your deployment

🔗 Additional Resources

See Home for more tool categories.

Clone this wiki locally