Troubleshooting

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:

Verify PostgreSQL is running:

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

Check DATABASE_URI:

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

Verify firewall rules:

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

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:

Test credentials manually:

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

Verify user exists:

-- As superuser
SELECT usename FROM pg_user;

Reset password:

ALTER USER username PASSWORD 'new_password';

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:

Check installed extensions:

SELECT extname, extversion FROM pg_extension;

Install missing extensions:

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

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:

Optimize query:

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

Increase timeout:
```
SET statement_timeout = '60s';
```

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:

Verify SSL mode:

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

Check PostgreSQL SSL configuration:
```
SHOW ssl; -- Should be 'on'
```
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

Uh oh!

Troubleshooting

Troubleshooting

🔍 Quick Diagnostics

Check MCP Server Status

🔌 Connection Issues

"Connection Refused"

"Authentication Failed"

"Database Does Not Exist"

🧩 Extension Issues

"Extension Not Found"

"Permission Denied Creating Extension"

⚠️ Query Execution Issues

"Potential SQL Injection Detected"

"Operation Not Allowed in Restricted Mode"

"Statement Timeout"

🐳 Docker Issues

Container Exits Immediately

Can't Connect to Host Database

📊 Performance Issues

Slow Query Performance

High Memory Usage

Cache Hit Ratio Too Low

🔐 Security Issues

SSL/TLS Connection Failures

Permission Errors

🔧 Tool-Specific Issues

pg_stat_statements Not Collecting Data

Vector Search Returns No Results

Hypothetical Index Test Fails

🆘 Getting Help

1. Gather Information

2. Check Logs

3. Open an Issue

📚 Related Documentation

🔗 External Resources

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally