🔄 PostgreSQL MCP Server – AI-Powered PostgreSQL Management & Monitoring

A powerful, AI-integrated PostgreSQL Model Context Protocol (MCP) server for automated database operations, monitoring, security, diagnostics, and optimization. Seamlessly manage PostgreSQL with tools and prompts designed for AI assistants like Claude and ChatGPT.

GitHub Repository: https://github.com/mukul975/postgres-mcp-server.git

---

📌 Why Use This?

• 🔍 Explore, diagnose & optimize PostgreSQL databases with over 200 specialized tools.
• 💬 Interact naturally using AI prompts for SQL generation, health checks, and performance reviews.
• 🛡️ Built-in security, permission validation, and error handling.
• ⚙️ Developer-ready integration for Claude, ChatGPT, and custom agents.

---

🔑 Features at a Glance

🔧 Core Database Operations

• ✅ Create/list databases & schemas
• ✅ Describe tables, analyze indexes
• ✅ Safe SELECT, UPDATE, DELETE execution

📊 Monitoring & Performance

• 📈 Query performance & execution plan insights
• 🔒 Lock analysis, replication lag checks
• 🚀 Index, query, and buffer pool optimization

🛡️ Security & Access Control

• 👥 User creation, role auditing
• 🔐 Privilege granting & connection monitoring

⚙️ Diagnostic Utilities

• 🧪 Table bloat check, autovacuum insights
• 💽 WAL logs, checkpoints, long transaction alerts

---

📚 Tool Categories

To help you navigate, tools are grouped under the following main categories:

• 🔧 General Database Operations
• 👥 User & Role Management
• 📈 Performance Monitoring & Tuning
• 🔁 Replication & Backup
• 🔒 Lock & Concurrency Management
• ✅ Constraint & Integrity Management
• 📊 Resource and Activity Monitoring
• 🧰 Index and Table Maintenance
• 🔬 Advanced Analysis & Diagnostics

---

🚀 Quick Start

1. Installation

python -m venv venv
source venv/bin/activate   # or venv\Scripts\activate on Windows
pip install -r requirements.txt

2. Configuration

cp .env.example .env  # or use copy on Windows
# Update with:
DATABASE_URL=postgresql://username:password@localhost:5432/db

3. Run the Server

python postgres_server.py

✅ For MCP CLI users:

mcp dev postgres_server.py

---

🧠 Integrate with Claude Desktop

Example config (claude_desktop_config.json):

<details> <summary>Windows</summary>

{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["postgres_server.py"],
      "cwd": "C:\\path\\to\\postgres-mcp-server",
      "env": {
        "DATABASE_URL": "postgresql://username:password@localhost:5432/database"
      }
    }
  }
}

</details> <details> <summary>macOS/Linux</summary>

{
  "mcpServers": {
    "postgres": {
      "command": "python",
      "args": ["postgres_server.py"],
      "cwd": "/absolute/path/to/postgres-mcp-server",
      "env": {
        "DATABASE_URL": "postgresql://username:password@localhost:5432/database"
      }
    }
  }
}

</details>

---

🔍 Tool Search & Complete Reference (238 Tools)

🔎 Tool Search Bar

Quick Tool Finder: Use Ctrl+F (or Cmd+F) to search for specific tools by name, category, or functionality.

📊 Tools by Category Overview

| Category | Count | Description | |----------|-------|-------------| | 🧱 Core Database | 26 | Basic database operations, schema management | | 👥 User & Security | 18 | User management, roles, permissions | | 📈 Performance | 45 | Monitoring, analysis, optimization | | 🔒 Locks & Concurrency | 22 | Lock analysis, blocking queries, deadlocks | | 🛠️ Maintenance | 28 | VACUUM, ANALYZE, table maintenance | | 📊 Index Management | 15 | Index creation, analysis, optimization | | 🔄 Replication & Backup | 20 | Replication monitoring, backup status | | 📋 Table Operations | 25 | Table statistics, constraints, data | | 📦 Extensions & Objects | 18 | Extensions, functions, triggers | | ⚙️ Configuration | 12 | Settings, variables, system info | | 🧪 Advanced Analysis | 29 | Deep diagnostics, predictions, recommendations |

---

📚 Complete Tool Reference

🧱 Core Database Operations (25 tools)

<details> <summary>Click to expand Core Database tools</summary>

• PostgreSQL_analyze_database - Run ANALYZE on entire database
• PostgreSQL_create_schema - Create new database schema
• PostgreSQL_describe_table - Get detailed table information
• PostgreSQL_drop_schema - Drop database schema
• PostgreSQL_execute_select_query - Execute SELECT queries
• PostgreSQL_execute_update_query - Execute INSERT/UPDATE/DELETE
• PostgreSQL_explain_query - Show query execution plan
• PostgreSQL_get_database_config - Get database configuration
• PostgreSQL_get_database_growth_trend - Analyze database growth
• PostgreSQL_get_database_size - Get database size information
• PostgreSQL_get_database_size_by_tablespace - Size by tablespace
• PostgreSQL_get_estimated_row_counts - Get estimated row counts
• PostgreSQL_get_server_version - Get PostgreSQL version info
• PostgreSQL_get_table_count - Get row count of table
• PostgreSQL_get_table_size - Get table size information
• PostgreSQL_get_table_size_summary - Comprehensive table sizes
• PostgreSQL_get_tablespace_info - Get tablespace information
• PostgreSQL_get_tablespace_usage - Tablespace usage statistics
• PostgreSQL_list_databases - List all databases
• PostgreSQL_list_schemas - List all schemas
• PostgreSQL_list_tables - List tables in schema
• PostgreSQL_list_views - List views in schema
• PostgreSQL_use_database - Switch to different database
• PostgreSQL_vacuum_analyze_table - VACUUM ANALYZE table
• PostgreSQL_kill_connection - Terminate database connection

</details>

👥 User & Security Management (18 tools)

<details> <summary>Click to expand User & Security tools</summary>

• PostgreSQL_create_user - Create database user/role
• PostgreSQL_drop_user - Drop database user/role
• PostgreSQL_get_column_privileges - Get column-level privileges
• PostgreSQL_get_role_attributes - Get role attributes and details
• PostgreSQL_get_table_permissions - Get table permissions
• PostgreSQL_grant_privileges - Grant table privileges
• PostgreSQL_list_roles_with_login - List roles with login capability
• PostgreSQL_list_roles_with_superuser - List superuser roles
• PostgreSQL_list_users_and_roles - List all users and roles
• PostgreSQL_revoke_privileges - Revoke table privileges
• PostgreSQL_security_audit - Basic security audit
• PostgreSQL_get_active_connections - Get active connections
• PostgreSQL_get_connection_limits - Connection limits and usage
• PostgreSQL_get_connection_pool_stats - Connection pool statistics
• PostgreSQL_get_idle_connections - Find idle connections
• PostgreSQL_connection_churn_analysis - Analyze connection patterns
• PostgreSQL_get_concurrent_connection_analysis - Concurrent connections
• PostgreSQL_analyze_connection_pool_efficiency - Pool efficiency

</details>

📈 Performance Monitoring & Analysis (45 tools)

<details> <summary>Click to expand Performance tools</summary>

• PostgreSQL_analyze_query_complexity - Analyze query complexity
• PostgreSQL_buffer_cache_hit_ratio - Cache hit ratios
• PostgreSQL_check_long_running_queries - Find long-running queries
• PostgreSQL_get_active_transactions - Get active transactions
• PostgreSQL_get_buffer_cache_contents - Buffer cache contents
• PostgreSQL_get_buffer_hit_ratios_detailed - Detailed hit ratios
• PostgreSQL_get_cache_hit_ratio - Cache hit ratios
• PostgreSQL_get_cache_hit_ratios - Buffer and index cache ratios
• PostgreSQL_get_checkpoint_analysis - Checkpoint analysis
• PostgreSQL_get_checkpoint_info - Checkpoint information
• PostgreSQL_get_checkpoint_stats - Checkpoint statistics
• PostgreSQL_get_column_statistics - Column statistics
• PostgreSQL_get_long_running_transactions - Long transactions
• PostgreSQL_get_memory_context_analysis - Memory context analysis
• PostgreSQL_get_memory_usage_stats - Memory usage statistics
• PostgreSQL_get_query_error_analysis - Query error analysis
• PostgreSQL_get_query_plan_cache_analysis - Query plan cache
• PostgreSQL_get_query_plan_cache_stats - Plan cache statistics
• PostgreSQL_get_query_plans - Query execution plans
• PostgreSQL_get_query_runtime_distribution - Query runtime stats
• PostgreSQL_get_slow_queries - Get slow queries
• PostgreSQL_get_slow_query_patterns - Slow query patterns
• PostgreSQL_get_slow_query_statements - Slow query statements
• PostgreSQL_get_table_access_patterns - Table access patterns
• PostgreSQL_get_table_io_stats - Table I/O statistics
• PostgreSQL_get_table_statistics - Table statistics
• PostgreSQL_get_temp_file_stats - Temporary file statistics
• PostgreSQL_get_temp_files - Temporary file usage
• PostgreSQL_get_top_heavy_queries - Top resource-heavy queries
• PostgreSQL_get_transaction_age_monitoring - Transaction age monitoring
• PostgreSQL_get_wait_events - Current wait events
• PostgreSQL_analyze_advanced_buffer_usage - Advanced buffer analysis
• PostgreSQL_buffer_cache_relation_analysis - Buffer cache per relation
• PostgreSQL_checkpoint_activity - Detailed checkpoint activity
• PostgreSQL_connection_pool_stats - Connection pool stats
• PostgreSQL_get_connection_pool_analysis - Connection pool analysis
• PostgreSQL_get_performance_regression_indicators - Performance regression
• PostgreSQL_high_io_tables - High I/O tables
• PostgreSQL_memory_usage_analysis - Memory usage analysis
• PostgreSQL_table_io_patterns - Table I/O patterns
• PostgreSQL_temp_file_usage - Temporary file usage monitoring
• PostgreSQL_temporary_objects_usage - Temporary objects analysis
• PostgreSQL_active_temp_file_users - Active temp file users
• PostgreSQL_get_high_wait_events - High wait events
• PostgreSQL_query_cancellation_analysis - Query cancellation analysis

</details>

🔒 Locks & Concurrency Management (22 tools)

<details> <summary>Click to expand Lock & Concurrency tools</summary>

• PostgreSQL_check_blocking_queries - Get blocking queries
• PostgreSQL_detect_conflicting_queries - Detect conflicting queries
• PostgreSQL_detect_foreign_key_lock_contention - FK lock contention
• PostgreSQL_detect_index_lock_waits - Index lock waits
• PostgreSQL_foreign_key_conflicts - Foreign key conflicts
• PostgreSQL_get_blocking_locks - Blocking and blocked queries
• PostgreSQL_get_lock_monitoring - Monitor current locks
• PostgreSQL_get_lock_statistics - Lock statistics with wait times
• PostgreSQL_get_lock_waits - Current lock waits
• PostgreSQL_get_locks_info - Current database locks
• PostgreSQL_deadlock_analysis - Deadlock history and patterns
• PostgreSQL_analyze_foreign_key_locks - Foreign key lock analysis
• PostgreSQL_predicate_lock_analysis - Predicate lock analysis
• PostgreSQL_longest_idle_transactions - Longest idle transactions
• PostgreSQL_long_running_prepared_transactions - Long prepared transactions
• PostgreSQL_orphaned_prepared_transactions - Orphaned prepared transactions
• PostgreSQL_prepared_transaction_retention - Prepared transaction retention
• PostgreSQL_get_prepared_transactions - Get prepared transactions
• PostgreSQL_analyze_transaction_wraparound - Transaction wraparound
• PostgreSQL_detect_transaction_wraparound_risk - Wraparound risk
• `PostgreSQL_transaction_wraparound