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

> Monitor PostgreSQL connections and identify issues with MCP-PostgreSQL-Ops. Use RPC tools to query pg_stat_activity and pg_locks for real-time insights into idle transactions, spikes, and lock contention.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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:

```bash

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

```bash

# 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:**

```json
{
  "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:

```bash
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:

```json
{
  "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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.