S
SkillNav

PostgreSQL Database Management Server

数据与存储

by HenkDz

轻松分析、管理并优化 PostgreSQL 数据库，支持调试、schema 管理和数据迁移，并通过实时洞察与建议提升性能与稳定性。

把 PostgreSQL 的调试、Schema 管理和迁移放到一处，边运维边拿到实时性能洞察与优化建议，省心也更稳。

184GitHub

什么是 PostgreSQL Database Management Server？

轻松分析、管理并优化 PostgreSQL 数据库，支持调试、schema 管理和数据迁移，并通过实时洞察与建议提升性能与稳定性。

核心功能 (18 个工具)

pg_analyze_database

Analyze PostgreSQL database configuration and performance

pg_debug_database

Debug common PostgreSQL issues

pg_manage_schema

Manage PostgreSQL schema - get schema info, create/alter tables, manage enums. Examples: operation="get_info" for table lists, operation="create_table" with tableName and columns, operation="get_enums" to list enums, operation="create_enum" with enumName and values

pg_manage_functions

Manage PostgreSQL functions - get, create, or drop functions with a single tool. Examples: operation="get" to list functions, operation="create" with functionName="test_func", parameters="" (empty for no params), returnType="TEXT", functionBody="SELECT 'Hello'"

pg_manage_triggers

Manage PostgreSQL triggers - get, create, drop, and enable/disable triggers. Examples: operation="get" to list triggers, operation="create" with triggerName, tableName, functionName, operation="drop" with triggerName and tableName, operation="set_state" with triggerName, tableName, enable

pg_manage_indexes

Manage PostgreSQL indexes - get, create, drop, reindex, and analyze usage with a single tool. Examples: operation="get" to list indexes, operation="create" with indexName, tableName, columns, operation="analyze_usage" for performance analysis

pg_manage_constraints

Manage PostgreSQL constraints - get, create foreign keys, drop foreign keys, create constraints, drop constraints. Examples: operation="get" to list constraints, operation="create_fk" with constraintName, tableName, columnNames, referencedTable, referencedColumns

pg_manage_rls

Manage PostgreSQL Row-Level Security - enable/disable RLS and manage policies. Examples: operation="enable" with tableName="users", operation="create_policy" with tableName, policyName, using, check

pg_manage_users

Manage PostgreSQL users and permissions - create, drop, alter users, grant/revoke permissions. Examples: operation="create" with username="testuser", operation="grant" with username, permissions, target, targetType

pg_manage_query

Manage PostgreSQL query analysis and performance - operation="explain" for EXPLAIN plans, operation="get_slow_queries" for slow query analysis, operation="get_stats" for query statistics, operation="reset_stats" for clearing statistics

pg_execute_query

Execute SELECT queries and data retrieval operations - operation="select/count/exists" with query and optional parameters. Examples: operation="select", query="SELECT * FROM users WHERE created_at > $1", parameters=["2024-01-01"]

pg_execute_mutation

Execute data modification operations (INSERT/UPDATE/DELETE/UPSERT) - operation="insert/update/delete/upsert" with table and data. Examples: operation="insert", table="users", data={"name":"John","email":"john@example.com"}

pg_execute_sql

Execute arbitrary SQL statements - sql="ANY_VALID_SQL" with optional parameters and transaction support. Examples: sql="CREATE INDEX ...", sql="WITH complex_cte AS (...) SELECT ...", transactional=true

pg_manage_comments

Manage PostgreSQL object comments - get, set, remove comments on tables, columns, functions, and other database objects. Examples: operation="get" with objectType="table", objectName="users", operation="set" with comment text, operation="bulk_get" for discovery

pg_export_table_data

Export table data to JSON or CSV format

pg_import_table_data

Import data from JSON or CSV file into a table

pg_copy_between_databases

Copy data between two databases

pg_monitor_database

Get real-time monitoring information for a PostgreSQL database

README

PostgreSQL MCP Server

smithery badge

A Model Context Protocol (MCP) server that provides comprehensive PostgreSQL database management capabilities for AI assistants.

<a href="https://glama.ai/mcp/servers/@HenkDz/postgresql-mcp-server"> <img width="380" height="200" src="https://glama.ai/mcp/servers/@HenkDz/postgresql-mcp-server/badge" alt="PostgreSQL Server MCP server" /> </a>

Features

🚀 What's New: This server has been completely redesigned from 46 individual tools to 17 intelligent tools through consolidation (34→8 meta-tools) and enhancement (+4 new tools), providing better AI discovery while adding powerful data manipulation and comment management capabilities.

Quick Start

Prerequisites

  • Node.js ≥18.0.0
  • Access to a PostgreSQL server
  • (Optional) An MCP client like Cursor or Claude for AI integration

Install MCP Server

Option 1: npm (Recommended)

bash
# Install globally
npm install -g @henkey/postgres-mcp-server

# Or run directly with npx (no installation)
# Use env var for connection string (optional)
export POSTGRES_CONNECTION_STRING="postgresql://user:pass@localhost:5432/db"
npx @henkey/postgres-mcp-server
# Or pass directly:
npx @henkey/postgres-mcp-server --connection-string "postgresql://user:pass@localhost:5432/db"

Verify installation

npx @henkey/postgres-mcp-server --help

Add to your MCP client configuration:

json
{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "npx",
      "args": [
        "@henkey/postgres-mcp-server",
        "--connection-string", "postgresql://user:password@host:port/database"
      ]
    }
  }
}

Option 2: Install via Smithery

bash
npx -y @smithery/cli install @HenkDz/postgresql-mcp-server --client claude

Option 3: Docker (Recommended for Production)

bash
# Build the Docker image
docker build -t postgres-mcp-server .

# Run with environment variable
docker run -i --rm \
  -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
  postgres-mcp-server

Add to your MCP client configuration:

json
{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "henkey/postgres-mcp:latest",
        "-e",
        "POSTGRES_CONNECTION_STRING"
      ],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://user:password@host:port/database"
      }
    }
  }
}

Option 4: Manual Installation (Development)

bash
git clone <repository-url>
cd postgresql-mcp-server
npm install
npm run build

Add to your MCP client configuration:

json
{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "node",
      "args": [
        "/path/to/postgresql-mcp-server/build/index.js",
        "--connection-string", "postgresql://user:password@host:port/database"
      ]
    }
  }
}

What's Included

17 powerful tools organized into three categories:

  • 🔄 Consolidation: 34 original tools consolidated into 8 intelligent meta-tools
  • 🔧 Specialized: 5 tools kept separate for complex operations
  • 🆕 Enhancement: 4 brand new tools (not in original 46)

📊 Consolidated Meta-Tools (8 tools)

  • Schema Management - Tables, columns, ENUMs, constraints
  • User & Permissions - Create users, grant/revoke permissions
  • Query Performance - EXPLAIN plans, slow queries, statistics
  • Index Management - Create, analyze, optimize indexes
  • Functions - Create, modify, manage stored functions
  • Triggers - Database trigger management
  • Constraints - Foreign keys, checks, unique constraints
  • Row-Level Security - RLS policies and management

🚀 Enhancement Tools (4 NEW tools)

Brand new capabilities not available in the original 46 tools

  • Execute Query - SELECT operations with count/exists support
  • Execute Mutation - INSERT/UPDATE/DELETE/UPSERT operations
  • Execute SQL - Arbitrary SQL execution with transaction support
  • Comments Management - Comprehensive comment management for all database objects

🔧 Specialized Tools (5 tools)

  • Database Analysis - Performance and configuration analysis
  • Debug Database - Troubleshoot connection, performance, locks
  • Data Export/Import - JSON/CSV data migration
  • Copy Between Databases - Cross-database data transfer
  • Real-time Monitoring - Live database metrics and alerts

Example Usage

typescript
// Analyze database performance
{ "analysisType": "performance" }

// Create a table with constraints
{
  "operation": "create_table",
  "tableName": "users", 
  "columns": [
    { "name": "id", "type": "SERIAL PRIMARY KEY" },
    { "name": "email", "type": "VARCHAR(255) UNIQUE NOT NULL" }
  ]
}

// Query data with parameters
{
  "operation": "select",
  "query": "SELECT * FROM users WHERE created_at > $1",
  "parameters": ["2024-01-01"],
  "limit": 100
}

// Insert new data
{
  "operation": "insert",
  "table": "users",
  "data": {"name": "John Doe", "email": "john@example.com"},
  "returning": "*"
}

// Find slow queries
{
  "operation": "get_slow_queries",
  "limit": 5,
  "minDuration": 100
}

// Execute a parameterized SELECT query
{
  "operation": "select",
  "query": "SELECT * FROM users WHERE id = $1",
  "parameters": [1]
}

// Perform an INSERT mutation
{
  "operation": "insert",
  "table": "products",
  "data": {"name": "New Product", "price": 99.99},
  "returning": "id"
}

// Manage database object comments
{
  "operation": "set",
  "objectType": "table",
  "objectName": "users",
  "comment": "Main user account information table"
}

📚 Documentation

📋 Complete Tool Schema Reference - All 18 tool parameters & examples in one place

For additional information, see the docs/ folder:

Features Highlights

🔄 Consolidation Achievements

✅ 34→8 meta-tools - Intelligent consolidation for better AI discovery
✅ Multiple operations per tool - Unified schemas with operation parameters
✅ Smart parameter validation - Clear error messages and type safety

🆕 Enhanced Data Capabilities

✅ Complete CRUD operations - INSERT/UPDATE/DELETE/UPSERT with parameterized queries
✅ Flexible querying - SELECT with count/exists support and safety limits ✅ Arbitrary SQL execution - Transaction support for complex operations

🔧 Production Ready

✅ Flexible connection - CLI args, env vars, or per-tool configuration
✅ Security focused - SQL injection prevention, parameterized queries
✅ Robust architecture - Connection pooling, comprehensive error handling

Docker Usage

The PostgreSQL MCP Server is fully Docker-compatible and can be used in production environments.

Building the Image

bash
# Build locally
docker build -t postgres-mcp-server .

# Or pull from Docker Hub
docker pull henkey/postgres-mcp:latest

Running with Environment Variables

bash
# Basic usage (using Docker Hub image)
docker run -i --rm \
  -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
  henkey/postgres-mcp:latest

# Or with locally built image
docker run -i --rm \
  -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
  postgres-mcp-server

# With tools configuration
docker run -i --rm \
  -e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
  -e POSTGRES_TOOLS_CONFIG="/app/config/tools.json" \
  -v /path/to/config:/app/config \
  postgres-mcp-server

Docker Compose Example

yaml
version: '3.8'
services:
  postgres-mcp:
    build: .
    environment:
      - POSTGRES_CONNECTION_STRING=postgresql://user:password@postgres:5432/database
    depends_on:
      - postgres
    stdin_open: true
    tty: true

  postgres:
    image: postgres:15
    environment:
      - POSTGRES_DB=database
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=password
    ports:
      - "5432:5432"

MCP Client Configuration

For use with MCP clients like Cursor or Claude Desktop:

json
{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "henkey/postgres-mcp:latest",
        "-e",
        "POSTGRES_CONNECTION_STRING"
      ],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://user:password@host:port/database"
      }
    }
  }
}

Prerequisites

  • Node.js ≥ 18.0.0 (for local development)
  • Docker (for containerized deployment)
  • PostgreSQL server access
  • Valid connection credentials

Contributing

  1. Fork the repository
  2. Create a feature branch
  3. Commit your changes
  4. Create a Pull Request

See Development Guide for detailed setup instructions.

License

AGPLv3 License - see LICENSE file for details.

常见问题

PostgreSQL Database Management Server 是什么？

轻松分析、管理并优化 PostgreSQL 数据库，支持调试、schema 管理和数据迁移，并通过实时洞察与建议提升性能与稳定性。

PostgreSQL Database Management Server 提供哪些工具？

提供 18 个工具，包括 pg_analyze_database、pg_debug_database、pg_manage_schema 等。

相关 Skills

技术栈评估

by alirezarezvani

Universal
热门

对比框架、数据库和云服务，结合 5 年 TCO、安全风险、生态活力与迁移复杂度做量化评估，适合技术选型、栈升级和替换路线决策。

✎ 帮你系统比较技术栈优劣，不只看功能，还把TCO、安全性和生态健康度一起量化，选型和迁移决策更稳。

数据与存储
未扫描15.4k

资深数据科学家

by alirezarezvani

Universal
热门

覆盖实验设计、特征工程、预测建模、因果推断与模型评估，适合用 Python/R/SQL 做 A/B 测试、时序分析和生产级 ML 落地，支撑数据驱动决策。

✎ 从 A/B 测试、因果分析到预测建模一条龙搞定，既有硬核统计方法也懂业务沟通，特别适合把数据结论真正落地。

数据与存储
未扫描15.4k

资深架构师

by alirezarezvani

Universal
热门

适合系统设计评审、ADR记录和扩展性规划，分析依赖与耦合，权衡单体或微服务、数据库与技术栈选型，并输出Mermaid、PlantUML、ASCII架构图。

✎ 搞系统设计、技术选型和扩展规划时，用它能更快理清架构决策与依赖关系，还能直接产出 Mermaid/PlantUML 图，方案讨论效率很高。

数据与存储
未扫描15.4k

相关 MCP Server

SQLite 数据库

编辑精选

by Anthropic

热门

SQLite 是让 AI 直接查询本地数据库进行数据分析的 MCP 服务器。

✎ 这个服务器解决了 AI 无法直接访问 SQLite 数据库的问题，适合需要快速分析本地数据集的开发者。不过，作为参考实现，它可能缺乏生产级的安全特性，建议在受控环境中使用。

数据与存储
85.9k

PostgreSQL 数据库

编辑精选

by Anthropic

热门

PostgreSQL 是让 Claude 直接查询和管理你的数据库的 MCP 服务器。

✎ 这个服务器解决了开发者需要手动编写 SQL 查询的痛点，特别适合数据分析师或后端开发者快速探索数据库结构。不过，由于是参考实现，生产环境使用前务必评估安全风险，别指望它能处理复杂事务。

数据与存储
85.9k

Firecrawl 智能爬虫

编辑精选

by Firecrawl

热门

Firecrawl 是让 AI 直接抓取网页并提取结构化数据的 MCP 服务器。

✎ 它解决了手动写爬虫的麻烦，让 Claude 能直接访问动态网页内容。最适合需要实时数据的研究者或开发者，比如监控竞品价格或抓取新闻。但要注意，它依赖第三方 API，可能涉及隐私和成本问题。

数据与存储
6.3k

评论