Skip to content

Troubleshooting

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

Troubleshooting

Common issues and solutions for PostgreSQL MCP Server.

🔍 Quick Diagnostics

Check MCP Server Status

# 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)

🔌 Connection Issues

"Connection Refused"

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

"Authentication Failed"

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

"Database Does Not Exist"

Solution:

-- Create database
CREATE DATABASE mydb;

-- Or use existing database
\l  -- List all databases

🧩 Extension Issues

"Extension Not Found"

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.

"Permission Denied Creating Extension"

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;

⚠️ Query Execution Issues

"Potential SQL Injection Detected"

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]
)

"Operation Not Allowed in Restricted Mode"

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

"Statement Timeout"

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"

🐳 Docker Issues

Container Exits Immediately

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

Can't Connect to Host Database

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

📊 Performance Issues

Slow Query Performance

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

High Memory Usage

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';

Cache Hit Ratio Too Low

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

🔐 Security Issues

SSL/TLS Connection Failures

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

Permission Errors

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;

🔧 Tool-Specific Issues

pg_stat_statements Not Collecting Data

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();

Vector Search Returns No Results

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
)

Hypothetical Index Test Fails

Verify HypoPG:

SELECT * FROM hypopg_list_indexes();

If not installed:

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

🆘 Getting Help

1. Gather Information

# 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
""")

2. Check Logs

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

3. Open an Issue

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

📚 Related Documentation

🔗 External Resources

See Home for more tool categories.

Clone this wiki locally