Common issues and solutions for PostgreSQL MCP Server.

# Test basic connectivity list_schemas() # Check database health analyze_db_health(health_type="all") # Verify extensions get_top_queries(sort_by="total_time", limit=1)

Symptoms:

• Can't connect to database
• MCP server times out
• "Connection refused" error

Solutions:

1. Verify PostgreSQL is running:

   # Check if PostgreSQL is listening pg_isready -h localhost -p 5432 # Or check service status sudo systemctl status postgresql

2. Check DATABASE_URI:

   echo $DATABASE_URI # Should be: postgresql://user:pass@host:5432/dbname

3. Verify firewall rules:

   # Test connection telnet hostname 5432 # Or nc -zv hostname 5432

4. Check pg_hba.conf (PostgreSQL access control):

   # Allow connections from your IP host all all 192.168.1.0/24 md5 

Symptoms:

• "password authentication failed"
• "role does not exist"

Solutions:

1. Test credentials manually:

   psql "postgresql://user:pass@host:5432/db"

2. Verify user exists:

   -- As superuser SELECT usename FROM pg_user;

3. Reset password:

   ALTER USER username PASSWORD 'new_password';

4. Check pg_hba.conf authentication method:

   # Use md5 or scram-sha-256, not trust/reject host all all 0.0.0.0/0 scram-sha-256 

Solution:

-- Create database CREATE DATABASE mydb; -- Or use existing database \l -- List all databases

Symptoms:

• get_top_queries returns "pg_stat_statements not installed"
• vector_search returns "pgvector extension not installed"

Solutions:

1. Check installed extensions:

   SELECT extname, extversion FROM pg_extension;

2. Install missing extensions:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

3. For pg_stat_statements - requires restart:

   # Edit postgresql.conf shared_preload_libraries = 'pg_stat_statements'

   sudo systemctl restart postgresql

See Extension Setup for detailed installation.

Solution:

# Connect as superuser psql -U postgres -d mydb # Create extension CREATE EXTENSION pg_stat_statements; # Grant permissions GRANT USAGE ON SCHEMA public TO mcp_user;

Symptoms:

• Query rejected with injection warning
• "Use parameter binding" error

Solution:

# ❌ WRONG: String concatenation execute_sql(f"SELECT * FROM users WHERE id = {user_id}") # ✅ CORRECT: Parameter binding execute_sql( sql="SELECT * FROM users WHERE id = %s", params=[user_id] )

Symptoms:

• INSERT/UPDATE/DELETE blocked
• DDL operations rejected

Solutions:

Option 1: Use appropriate tool for operation

# For JSON updates json_update(table_name="users", json_column="profile", ...) # For inserts json_insert(table_name="users", json_column="profile", ...)

Option 2: Switch to unrestricted mode (development only)

postgres-mcp --access-mode=unrestricted

Symptoms:

• Query times out
• "canceling statement due to statement timeout"

Solutions:

1. Optimize query:

   # Check query plan explain_query(sql="...", analyze=True) # Get index recommendations suggest_indexes(use_query_log=True)

2. Increase timeout:

   SET statement_timeout = '60s';

3. Add to connection string:

   DATABASE_URI="postgresql://user:pass@host/db?options=-c statement_timeout=60s"

Solution:

# Check logs docker logs <container_id> # Verify DATABASE_URI is set docker run -i --rm \ -e DATABASE_URI="postgresql://..." \ neverinfamous/postgres-mcp:latest # Ensure -i flag for interactive mode

For localhost database (from Docker):

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

Diagnosis:

# 1. Find slow queries slow = get_top_queries(sort_by="mean_time", limit=10) # 2. Analyze execution plan for query in slow["queries"]: plan = explain_query(sql=query["query"], analyze=True) # 3. Get index suggestions suggestions = suggest_indexes(use_query_log=True) # 4. Check resource usage resources = resource_usage_analyze( include_cpu=True, include_memory=True, include_io=True )

Solutions:

• Add recommended indexes
• Optimize queries
• Increase shared_buffers
• Add more CPU/memory

Diagnosis:

metrics = monitor_real_time(include_io=True) resources = resource_usage_analyze(include_memory=True)

Solutions:

-- Reduce work_mem for large queries SET work_mem = '64MB'; -- Reduce maintenance_work_mem SET maintenance_work_mem = '256MB'; -- Check for memory leaks SELECT * FROM pg_stat_activity WHERE state != 'idle';

Check ratio:

resources = resource_usage_analyze(include_io=True) # Look for buffer_cache_hit_ratio < 95%

Solutions:

-- Increase shared_buffers (requires restart) -- In postgresql.conf shared_buffers = 4GB # 25% of RAM -- Or investigate disk-heavy queries

Solutions:

1. Verify SSL mode:

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

2. Check PostgreSQL SSL configuration:

   SHOW ssl; -- Should be 'on'

3. Try different SSL modes:

   • sslmode=disable - No SSL (testing only)
   • sslmode=prefer - Try SSL, fall back
   • sslmode=require - Require SSL
   • sslmode=verify-ca - Verify certificate

For read-only operations:

GRANT CONNECT ON DATABASE mydb TO mcp_user; GRANT USAGE ON SCHEMA public TO mcp_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_user; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO mcp_user;

For unrestricted mode:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mcp_user; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mcp_user;

Check configuration:

SHOW shared_preload_libraries; -- Must include 'pg_stat_statements' SHOW pg_stat_statements.track; -- Should be 'all'

Reset statistics:

SELECT pg_stat_statements_reset();

Verify pgvector:

SELECT '[1,2,3]'::vector; -- Check vector column exists \d+ your_table

Test query:

# Ensure vector dimensions match vector_search( table_name="documents", vector_column="embedding", query_vector=[0.1, 0.2, 0.3], # Same dimensions as stored vectors limit=5 )

Verify HypoPG:

SELECT * FROM hypopg_list_indexes();

If not installed:

• Use suggest_indexes instead (works without HypoPG)
• Or install HypoPG (Extension Setup)

# Database health health = analyze_db_health(health_type="all") # Extension status extensions = execute_sql("SELECT extname, extversion FROM pg_extension") # Server version version = execute_sql("SELECT version()") # Recent errors logs = execute_sql("""  SELECT * FROM pg_stat_activity  WHERE state = 'idle in transaction'  OR wait_event IS NOT NULL """)

PostgreSQL logs:

# Ubuntu/Debian tail -f /var/log/postgresql/postgresql-16-main.log # macOS (Homebrew) tail -f /usr/local/var/log/postgres.log # Docker docker logs postgres-container

MCP Server logs:

# Docker docker logs mcp-container # Python installation # Check terminal output or application logs

If you can't resolve the issue, open a GitHub issue with:

• PostgreSQL version
• MCP server version
• Extension versions
• Full error message
• Minimal reproduction steps
• Database health output

• Quick Start - Initial setup
• Extension Setup - Install extensions
• MCP Configuration - Configure server
• Security Best Practices - Security issues

• PostgreSQL Documentation
• PostgreSQL Wiki
• PostgreSQL Mailing Lists

See Home for more tool categories.