How to Monitor Database Connections and Identify Issues Using MCP-PostgreSQL-Ops

Use the get_active_connections and get_lock_monitoring RPC tools exposed by the MCP-PostgreSQL-Ops FastMCP server to query pg_stat_activity and pg_locks in real-time, identifying idle transactions, connection spikes, and lock contention without modifying production data.

MCP-PostgreSQL-Ops is an open-source FastMCP server that exposes PostgreSQL system views as JSON-RPC tools, enabling programmatic monitoring of database connections and diagnostic workflows. The server executes read-only queries against pg_stat_activity and pg_locks, making it safe for production deployments including Amazon RDS, Aurora, and self-managed PostgreSQL 12–17 instances.

Core Connection Monitoring Tools

The monitoring capabilities center on two primary tools defined in src/mcp_postgresql_ops/mcp_main.py. Both are version-agnostic and require no extensions beyond the default system catalog.

get_active_connections: Real-Time Session Visibility

The get_active_connections tool queries pg_stat_activity to return every session currently attached to the server. Defined at lines 1502–1516 in src/mcp_postgresql_ops/mcp_main.py, it exposes PID, username, database name, client address, connection state, and the current query snippet.

This tool is essential for spotting sudden connection spikes, long-running transactions stuck in idle in transaction state, or rogue applications connecting from unexpected client_addr values. The implementation uses an async PostgreSQL driver through the shared execute_query helper in src/mcp_postgresql_ops/functions.py, ensuring non-blocking I/O when monitoring high-traffic instances.

get_lock_monitoring: Detecting Contention and Blocks

The get_lock_monitoring tool (lines 99–132 in src/mcp_postgresql_ops/mcp_main.py) queries pg_locks joined with pg_stat_activity to show all current locks, blocked sessions, and wait events. It accepts optional filters including granted, state, mode, and username to narrow results to specific contention patterns.

Lock monitoring identifies which session blocks another via the blocked_by field, revealing the root cause of connection-level hangs. Because locks are the most common source of PostgreSQL contention, this tool provides the diagnostic depth needed to resolve production incidents where applications appear unresponsive.

Architecture and Implementation

Understanding the implementation architecture explains why these tools are both performant and safe for production use.

FastMCP Tool Registration

The module initializes a single FastMCP instance (mcp = FastMCP("mcp-postgresql-ops")) and registers each monitoring function using the @mcp.tool() decorator. At runtime, FastMCP automatically generates JSON-RPC-compatible endpoints that expose function signatures and handle request routing. This structure makes the monitoring capabilities discoverable by MCP clients like Claude Desktop or OpenWebUI without manual API documentation.

Async Database Abstraction

All database operations flow through src/mcp_postgresql_ops/functions.py. The execute_query helper opens an asyncpg.Connection using credentials from environment variables (POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB). This abstraction isolates connection pooling, logs errors with sanitized credentials (via sanitize_connection_info), and converts raw rows into human-readable tables using format_table_data.

For version-specific features, src/mcp_postgresql_ops/version_compat.py generates appropriate SQL strings. However, the connection monitoring tools rely only on pg_stat_activity and pg_locks, which have stable schemas across PostgreSQL 12–17, allowing them to run unchanged across all supported versions.

Practical Monitoring Workflows

Use this four-step workflow to diagnose connection issues systematically.

1. Baseline with get_active_connections – Check for connections in idle in transaction state or high counts from single client_addr values. These patterns indicate connection pool exhaustion or application bugs holding transactions open.
2. Investigate blocks with get_lock_monitoring – If sessions appear stuck, query with {"granted": "false"} to see ungranted locks and identify the blocking PID via the blocked_by column.
3. Verify server capacity with get_server_info – Review the datconnlimit value and confirm pg_stat_statements extension status for later query-level analysis.
4. Isolate by database – Use get_database_list and get_current_database_info to check per-database connection limits, then repeat step 1 with the database_name parameter to isolate specific tenant impact.

Because the server is strictly read-only, no data-modifying SQL executes during these checks, eliminating the risk of accidental production changes.

Integration Examples

CLI Execution (Stdio Mode)

Start the server and invoke tools directly via standard input/output:

# Start the MCP server

uvx --python 3.12 mcp-postgresql-ops

# Request active connections

mcp-postgresql-ops --type stdio --command "get_active_connections"

HTTP API (Streamable-HTTP Mode)

Deploy as a persistent HTTP service for programmatic access:

# Start server on port 8000

uvx --python 3.12 mcp-postgresql-ops --type streamable-http --host 0.0.0.0 --port 8000

# Query active connections

curl -X POST http://localhost:8000/postgresql-ops \
  -H "Content-Type: application/json" \
  -d '{
        "jsonrpc": "2.0",
        "method": "get_active_connections",
        "params": {},
        "id": 1
      }'

Sample response:

{
  "jsonrpc": "2.0",
  "result": "=== Active Connections ===\npid | username | database_name | client_addr | state | current_query\n---\n12345 | app_user | ecommerce | 10.0.2.15 | idle in transaction | SELECT * FROM orders ..."
}

Filtered Lock Analysis

To identify specific contention sources, filter the lock monitor by ungranted locks and username:

curl -X POST http://localhost:8000/postgresql-ops \
  -H "Content-Type: application/json" \
  -d '{
        "jsonrpc": "2.0",
        "method": "get_lock_monitoring",
        "params": {
          "granted": "false",
          "username": "app_user"
        },
        "id": 2
      }'

Claude Desktop Integration

Add the server to your MCP configuration to enable natural language monitoring:

{
  "mcpServers": {
    "postgresql-ops": {
      "command": "uv",
      "args": ["run", "python", "-m", "mcp_postgresql_ops"],
      "env": {
        "POSTGRES_HOST": "127.0.0.1",
        "POSTGRES_PORT": "15432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "changeme!@34",
        "POSTGRES_DB": "ecommerce"
      }
    }
  }
}

With this configuration, asking "Show all active connections and any blocked sessions" triggers sequential calls to get_active_connections and get_lock_monitoring, returning a combined markdown table of current system state.

Summary

• MCP-PostgreSQL-Ops exposes connection monitoring through the get_active_connections and get_lock_monitoring RPC tools, implemented in src/mcp_postgresql_ops/mcp_main.py.
• The tools query pg_stat_activity and pg_locks using an async driver from src/mcp_postgresql_ops/functions.py, ensuring non-blocking production monitoring.
• All operations are read-only and version-agnostic, supporting PostgreSQL 12–17 without extensions.
• Workflows progress from connection counting to lock analysis to server capacity verification, isolating idle transactions and blocking PIDs systematically.
• Integration options include CLI stdio, HTTP JSON-RPC, and AI assistants via FastMCP-compatible clients.

Frequently Asked Questions

How does MCP-PostgreSQL-Ops differ from running SQL queries directly?

MCP-PostgreSQL-Ops provides a structured JSON-RPC interface with built-in formatting, error handling, and sanitization via sanitize_connection_info in src/mcp_postgresql_ops/functions.py. Unlike ad-hoc SQL, the tools return pre-formatted tables and integrate with MCP clients like Claude Desktop, allowing natural language queries to trigger precise system catalog lookups without manual psql connections.

Is it safe to use on production databases?

Yes. The server executes strictly read-only queries against system views and does not support data modification. The use of asyncpg and connection pooling in execute_query minimizes overhead, and credentials are masked in logs. This design makes the tool safe for high-traffic RDS, Aurora, and self-managed production clusters.

Which PostgreSQL versions are supported?

The connection monitoring tools support PostgreSQL 12 through 17. Because get_active_connections and get_lock_monitoring rely only on the stable system catalog views pg_stat_activity and pg_locks, they do not require version-specific SQL logic from src/mcp_postgresql_ops/version_compat.py and function identically across all supported releases.

How can I filter results to find specific blocking scenarios?

Use the get_lock_monitoring tool with the granted parameter set to "false" to show only ungranted locks, and combine it with username or mode filters to isolate specific application users or lock types. This targets the exact sessions causing contention rather than returning the full lock table, which can contain thousands of granted rows in busy systems.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}