mcp-postgres 2.0.0

High-performance MCP server for PostgreSQL with CPU-aware connection pooling and optimized buffers
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

use serde_json::{json, Value};
use tokio_postgres::Client;
use crate::errors::Result as MCPResult;

/// 16. List connections
pub async fn list_connections(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
    let rows = client
        .query(
            "SELECT pid, usename::text, application_name, state,
                    state_change::text, backend_start::text, query_start::text
             FROM pg_stat_activity
             WHERE pid != pg_backend_pid()
             ORDER BY backend_start DESC",
            &[],
        )
        .await?;

    let connections: Vec<Value> = rows
        .iter()
        .map(|row| {
            json!({
                "pid": row.get::<_, i32>(0),
                "user": row.get::<_, Option<String>>(1),
                "application": row.get::<_, Option<String>>(2),
                "state": row.get::<_, Option<String>>(3),
                "state_change": row.get::<_, Option<String>>(4),
                "backend_start": row.get::<_, Option<String>>(5),
                "query_start": row.get::<_, Option<String>>(6),
            })
        })
        .collect();

    Ok(json!({ "connections": connections }))
}

/// 18. Show current user
pub async fn show_current_user(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
    let rows = client
        .query("SELECT current_user, current_database(), version()", &[])
        .await?;

    let row = &rows[0];

    Ok(json!({
        "user": row.get::<_, String>(0),
        "database": row.get::<_, String>(1),
        "version": row.get::<_, String>(2),
    }))
}

/// 19. Show running queries
pub async fn show_running_queries(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
    let rows = client
        .query(
            "SELECT pid, usename, application_name, state, query, query_start
             FROM pg_stat_activity
             WHERE state != 'idle' AND pid != pg_backend_pid()
             ORDER BY query_start DESC",
            &[],
        )
        .await?;

    let queries: Vec<Value> = rows
        .iter()
        .map(|row| {
            json!({
                "pid": row.get::<_, i32>(0),
                "user": row.get::<_, String>(1),
                "application": row.get::<_, Option<String>>(2),
                "state": row.get::<_, String>(3),
                "query": row.get::<_, Option<String>>(4),
                "query_start": row.get::<_, Option<String>>(5),
            })
        })
        .collect();

    Ok(json!({ "queries": queries }))
}

/// 20. Show connection summary
pub async fn show_connection_summary(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
    let rows = client
        .query(
            "SELECT state, count(*) as count
             FROM pg_stat_activity
             GROUP BY state
             ORDER BY count DESC",
            &[],
        )
        .await?;

    let summary: Vec<Value> = rows
        .iter()
        .map(|row| {
            json!({
                "state": row.get::<_, Option<String>>(0).unwrap_or_else(|| "unknown".to_string()),
                "count": row.get::<_, i64>(1),
            })
        })
        .collect();

    Ok(json!({ "summary": summary }))
}