general MCP Server
๐ Give AI assistants full PostgreSQL DBA superpowers โ 30+ tools for performance analysis, bloat detection, lock/deadlock monitoring, autovacuum & schema inspection. No extensions required. PG 12-18.
Discovered via unknown and last synced 1w ago.
1. Install the package
uvx --python 3.12 mcp-postgresql-ops
2. Add to claude_desktop_config.json
{
"mcpServers": {
"mcp-postgresql-ops": {
"command": "uvx",
"args": [
"--python",
"3.12",
"mcp-postgresql-ops"
]
}
}
}Config file location: ~/Library/Application Support/Claude/claude_desktop_config.json (macOS) / %APPDATA%\Claude\claude_desktop_config.json (Windows)
โ None
โ None
โ None
โ None
โ None
PostgreSQL server port number
โ None
Host port mapping for MCPO proxy container
`5432`
โ None
`streamable-http`
`postgres`
โ None
`false`
### ๐ **Version-Aware Tools (Auto-Adapting)**
PostgreSQL user password (supports special characters)
โ None
โ None
Host port mapping for Open WebUI container
โ None
โ None
โ None
HTTP server bind address (0.0.0.0 for all interfaces)
Purpose
โ None
Description
โ None
โ None
โ None
โ None
`pg_stat_monitor`
superuser (4 rows) ``` #### Method 1: postgresql.conf (Recommended for Self-Managed PostgreSQL) Add the following to your `postgresql.conf`: ```ini # Basic statistics collection (usually enabled by default) track_activities = on track_counts = on # Required for function statistics tools track_functions = pl # Enables PL/pgSQL function statistics collection # Optional but recommended for accurate I/O timing track_io_timing = on # Enables I/O timing statistics collection ``` Then restart PostgreSQL server. #### Method 2: PostgreSQL Startup Parameters For Docker or command-line PostgreSQL startup: ```bash # Docker example docker run -d \ -e POSTGRES_PASSWORD=mypassword \ postgres:17 \ -c track_activities=on \ -c track_counts=on \ -c track_functions=pl \ -c track_io_timing=on # Direct postgres command postgres -D /data \ -c track_activities=on \ -c track_counts=on \ -c track_functions=pl \ -c track_io_timing=on ``` #### Method 3: Dynamic Configuration (AWS RDS, Azure, GCP, Managed Services) For managed PostgreSQL services where you cannot modify `postgresql.conf`, use SQL commands to change settings dynamically: ```sql -- Enable basic statistics collection (usually enabled by default) ALTER SYSTEM SET track_activities = 'on'; ALTER SYSTEM SET track_counts = 'on'; -- Enable function statistics collection (requires superuser privileges) ALTER SYSTEM SET track_functions = 'pl'; -- Enable I/O timing statistics (optional but recommended) ALTER SYSTEM SET track_io_timing = 'on'; -- Reload configuration without restart (run separately) SELECT pg_reload_conf(); ``` **Alternative for session-level testing**: ```sql -- Set for current session only (temporary) SET track_activities = 'on'; SET track_counts = 'on'; SET track_functions = 'pl'; SET track_io_timing = 'on'; ``` **Note**: When using command-line tools, run each SQL statement separately to avoid transaction block errors. --- ## RDS/Aurora Compatibility - This server is read-only and works with regular roles on RDS/Aurora. For advanced analysis enable pg_stat_statements; pg_stat_monitor is not available on managed engines. - On RDS/Aurora, prefer DB Parameter Group over ALTER SYSTEM for persistent settings. ```sql -- Verify preload setting SHOW shared_preload_libraries; -- Enable extension in target DB CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Recommended visibility for monitoring GRANT pg_read_all_stats TO <app_user>; ``` --- ## Example Queries ### ๐ข Extension-Independent Tools (Always Available) - **get_server_info** - "Show PostgreSQL server version and extension status." - "Check if pg_stat_statements is installed." - **get_active_connections** - "Show all active connections." - "List current sessions with database and user." - **get_postgresql_config** - "Show all PostgreSQL configuration parameters." - "Find all memory-related configuration settings." - **get_database_list** - "List all databases and their sizes." - "Show database list with owner information." - **get_table_list** - "List all tables in the ecommerce database." - "Show table sizes in the public schema." - **get_table_schema_info** - "Show detailed schema information for the customers table in ecommerce database." - "Get column details and constraints for products table in ecommerce database." - "Analyze table structure with indexes and foreign keys for orders table in sales schema of ecommerce database." - "Show schema overview for all tables in public schema of inventory database." - ๐ **Features**: Column types, constraints, indexes, foreign keys, table metadata - โ ๏ธ **Required**: `database_name` parameter must be specified - **get_database_schema_info** - "Show all schemas in ecommerce database with their contents." - "Get detailed information about sales schema in ecommerce database." - "Analyze schema structure and permissions for inventory database." - "Show schema overview with table counts and sizes for hr_system database." - ๐ **Features**: Schema owners, permissions, object counts, sizes, contents - โ ๏ธ **Required**: `database_name` parameter must be specified - **get_table_relationships** - "Show all relationships for customers table in ecommerce database." - "Analyze foreign key relationships for orders table in sales schema of ecommerce database." - "Get database-wide relationship overview for ecommerce database." - "Find all tables that reference products table in ecommerce database." - "Show cross-schema relationships in inventory database." - ๐ **Features**: Foreign key relationships (inbound/outbound), cross-schema dependencies, constraint details - โ ๏ธ **Required**: `database_name` parameter must be specified - ๐ก **Usage**: Leave `table_name` empty for database-wide relationship analysis - **get_user_list** - "List all database users and their roles." - "Show user permissions for a specific database." - **get_index_usage_stats** - "Analyze index usage efficiency." - "Find unused indexes in the current database." - **get_database_size_info** - "Show database capacity analysis." - "Find the largest databases by size." - **get_table_size_info** - "Show table and index size analysis." - "Find largest tables in a specific schema." - **get_vacuum_analyze_stats** - "Show recent VACUUM and ANALYZE operations." - "List tables needing VACUUM." - **get_current_database_info** - "What database am I connected to?" - "Show current database information and connection details." - "Display database encoding, collation, and size information." - ๐ **Features**: Database name, encoding, collation, size, connection limits - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - **get_table_bloat_analysis** - "Analyze table bloat in the current database." - "Show dead tuple ratios and bloat estimates for user_logs table pattern." - "Find tables with high bloat that need VACUUM maintenance." - "Analyze bloat in specific schema with minimum 100 dead tuples." - ๐ **Features**: Dead tuple ratios, bloat size estimates, VACUUM recommendations, pattern filtering - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - ๐ก **Usage**: Extension-Independent approach using pg_stat_user_tables - **get_database_bloat_overview** - "Show database-wide bloat summary by schema." - "Get high-level view of storage efficiency across all schemas." - "Identify schemas requiring maintenance attention." - ๐ **Features**: Schema-level aggregation, total bloat estimates, maintenance status - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - **get_autovacuum_status** - "Check autovacuum configuration and trigger conditions." - "Show tables needing immediate autovacuum attention." - "Analyze autovacuum threshold percentages for public schema." - "Find tables approaching autovacuum trigger points." - ๐ **Features**: Trigger threshold analysis, urgency classification, configuration status - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - ๐ก **Usage**: Extension-Independent autovacuum monitoring using pg_stat_user_tables - **get_autovacuum_activity** - "Show autovacuum activity patterns for the last 48 hours." - "Monitor autovacuum execution frequency and timing." - "Find tables with irregular autovacuum patterns." - "Analyze recent autovacuum and autoanalyze history." - ๐ **Features**: Activity patterns, execution frequency, timing analysis - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - ๐ก **Usage**: Historical autovacuum pattern analysis - **get_running_vacuum_operations** - "Show currently running VACUUM and ANALYZE operations." - "Monitor active maintenance operations and their progress." - "Check if any VACUUM operations are blocking queries." - "Find long-running maintenance operations." - ๐ **Features**: Real-time operation status, elapsed time, impact level, process details - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - ๐ก **Usage**: Real-time maintenance monitoring using pg_stat_activity - **get_vacuum_effectiveness_analysis** - "Analyze VACUUM effectiveness and maintenance patterns." - "Compare manual VACUUM vs autovacuum efficiency." - "Find tables with suboptimal maintenance patterns." - "Check VACUUM frequency vs table activity ratios." - ๐ **Features**: Maintenance pattern analysis, effectiveness assessment, DML-to-VACUUM ratios - ๐ง **PostgreSQL 12-18**: Fully compatible, no extensions required - ๐ก **Usage**: Strategic VACUUM analysis using existing statistics - **get_lock_monitoring** - "Show all current locks and blocked sessions." - "Show only blocked sessions with granted=false filter." - "Monitor locks by specific user with username filter." - "Check exclusive locks with mode filter." - **get_wal_status** - "Show WAL status and archiving information." - "Monitor WAL generation and current LSN position." - **get_replication_status** - "Check replication connections and lag status." - "Monitor replication slots and WAL receiver status." - **get_database_stats** - "Show comprehensive database performance metrics." - "Analyze transaction commit ratios and I/O statistics." - "Monitor buffer cache hit ratios and temporary file usage." - **get_bgwriter_stats** - "Analyze checkpoint performance and timing." - "Show me checkpoint performance." - "Show background writer efficiency statistics." - "Monitor buffer allocation and fsync patterns." - **get_user_functions_stats** - "Analyze user-defined function performance." - "Show function call counts and execution times." - "Identify performance bottlenecks in custom functions." - โ ๏ธ **Requires**: `track_functions = pl` in postgresql.conf - **get_table_io_stats** - "Analyze table I/O performance and buffer hit ratios." - "Identify tables with poor buffer cache performance." - "Monitor TOAST table I/O statistics." - ๐ก **Enhanced with**: `track_io_timing = on` for accurate timing - **get_index_io_stats** - "Show index I/O performance and buffer efficiency." - "Identify indexes causing excessive disk I/O." - "Monitor index cache-friendliness patterns." - ๐ก **Enhanced with**: `track_io_timing = on` for accurate timing - **get_database_conflicts_stats** - "Check replication conflicts on standby servers." - "Analyze conflict types and resolution statistics." - "Monitor standby server query cancellation patterns." - "Monitor WAL generation and current LSN position." - **get_replication_status** - "Check replication connections and lag status." - "Monitor replication slots and WAL receiver status." ### ๐ Version-Aware Tools (Auto-Adapting) - **get_io_stats** (New!) - "Show comprehensive I/O statistics." (PostgreSQL 16+ provides detailed breakdown) - "Analyze I/O statistics." - "Analyze buffer cache efficiency and I/O timing." - "Monitor I/O patterns by backend type and context." - ๐ **PG16+**: Full pg_stat_io with timing, backend types, and contexts - ๐ **PG12-15**: Basic pg_statio_* fallback with buffer hit ratios - **get_bgwriter_stats** (Enhanced!) - "Show background writer and checkpoint performance." - ๐ **PG17+**: Separate checkpointer and bgwriter statistics via `pg_stat_checkpointer` - ๐ **PG12-16**: Combined bgwriter stats (includes checkpointer data) - **get_server_info** (Enhanced!) - "Show server version and compatibility features." - "Check server compatibility." - "Check what MCP tools are available on this PostgreSQL version." - "Displays feature availability matrix and upgrade recommendations." - **get_all_tables_stats** (Enhanced!) - "Show comprehensive statistics for all tables." (version-compatible for PG12-18) - "Include system tables with include_system=true parameter." - "Analyze table access patterns and maintenance needs." - ๐ **PG13+**: Tracks insertions since vacuum (`n_ins_since_vacuum`) for optimal maintenance scheduling - ๐ **PG12**: Compatible mode with NULL for unsupported columns - **get_wait_events** (New!) - "Show wait event types and descriptions." - "What wait events are available on this PostgreSQL version?" - ๐ **PG17+**: Native `pg_wait_events` catalog with full descriptions - ๐ **PG12-16**: Fallback to `pg_stat_activity` current waits grouped by type - **get_wal_summarizer_status** (New! PG 17+) - "Show WAL summarizer status for incremental backups." - "Monitor WAL summarization progress." - ๐ **PG17+**: WAL summarizer monitoring via `pg_get_wal_summarizer_state()` - โ **PG12-16**: Not available (returns informational message) - **get_async_io_status** (New! PG 18+) - "Show async I/O subsystem status." - "Monitor pg_aios for async I/O operations." - ๐ **PG18+**: `pg_aios` view for async I/O monitoring - โ **PG12-17**: Not available (returns informational message) - **get_per_backend_io_stats** (New! PG 18+) - "Show per-backend I/O and WAL statistics." - "Analyze I/O patterns by individual backend process." - ๐ **PG18+**: Per-backend I/O stats with WAL statistics - โ **PG12-17**: Not available (returns informational message) ### ๐ก Extension-Dependent Tools - **get_pg_stat_statements_top_queries** (Requires `pg_stat_statements`) - "Show top 10 slowest queries." - "Analyze slow queries in the inventory database." - ๐ **Version-Compatible**: PG12 uses `total_time` โ `total_exec_time` mapping; PG13+ uses native columns - ๐ก **Cross-Version**: Automatically adapts query structure for PostgreSQL 12-18 compatibility - **get_pg_stat_monitor_recent_queries** (Optional, uses `pg_stat_monitor`) - "Show recent queries in real time." - "Monitor query activity for the last 5 minutes." - ๐ **Version-Compatible**: PG12 uses `total_time` โ `total_exec_time` mapping; PG13+ uses native columns - ๐ก **Cross-Version**: Automatically adapts query structure for PostgreSQL 12-18 compatibility **๐ก Pro Tip**: All tools support multi-database operations using the `database_name` parameter. This allows PostgreSQL superusers to analyze and monitor multiple databases from a single MCP server instance. --- ## Troubleshooting ### Connection Issues 1. Check PostgreSQL server status 2. Verify connection parameters in `.env` file 3. Ensure network connectivity 4. Check user permissions ### Extension Errors 1. Run `get_server_info` to check extension status 2. Install missing extensions: ```sql CREATE EXTENSION pg_stat_statements; CREATE EXTENSION pg_stat_monitor; ``` 3. Restart PostgreSQL if needed ### Configuration Issues 1. **"No data found" for function statistics**: Check `track_functions` setting ```sql SHOW track_functions; -- Should be 'pl' or 'all' ``` **Quick fix for managed services (AWS RDS, etc.)**: ```sql ALTER SYSTEM SET track_functions = 'pl'; SELECT pg_reload_conf(); ``` 2. **Missing I/O timing data**: Enable timing collection ```sql SHOW track_io_timing; -- Should be 'on' ``` **Quick fix**: ```sql ALTER SYSTEM SET track_io_timing = 'on'; SELECT pg_reload_conf(); ``` 3. **Apply configuration changes**: - **Self-managed**: Add settings to `postgresql.conf` and restart server - **Managed services**: Use `ALTER SYSTEM SET` + `SELECT pg_reload_conf()` - **Temporary testing**: Use `SET parameter = value` for current session - Generate some database activity to populate statistics ### Performance Issues 1. Use `limit` parameters to reduce result size 2. Run monitoring during off-peak hours 3. Check database load before running analysis ### Version Compatibility Issues > For more details, see the [## Tool Compatibility Matrix](#tool-compatibility-matrix) 1. **Run compatibility check first**: ```bash # "Use get_server_info to check version and available features" ``` 2. **Understanding feature availability**: - **PostgreSQL 18**: All features including async I/O, VACUUM timing, per-backend stats - **PostgreSQL 17**: Separate checkpointer stats, wait events, WAL summarizer - **PostgreSQL 16**: pg_stat_io view - **PostgreSQL 14+**: Parallel query tracking - **PostgreSQL 12-13**: Core functionality only 3. **If a tool shows "Not Available"**: - Feature requires newer PostgreSQL version - Tool will automatically use best available alternative - Consider upgrading PostgreSQL for enhanced monitoring --- ## Development ### Testing & Development ```bash # Clone and setup for development git clone https://github.com/call518/MCP-PostgreSQL-Ops.git cd MCP-PostgreSQL-Ops uv sync # Test with MCP Inspector (loads .env automatically) ./run-mcp-inspector-local.sh # Direct execution methods: # 1. Using uv run (recommended for development) uv run mcp-postgresql-ops --log-level DEBUG # 2. Module execution (requires PYTHONPATH) PYTHONPATH=src python -m mcp_postgresql_ops --log-level DEBUG # 3. After installation mcp-postgresql-ops --log-level DEBUG # Test version compatibility (requires different PostgreSQL versions) # Modify POSTGRES_HOST in .env to point to different versions ``` ### Running Tests Two test suites are available:
HTTP server port for MCP communication
Secret key for Bearer token authentication (required when auth enabled)
โ None
โ๏ธ Config Required
Default database name for connections
`200`
Requires Docker
`18003`
context ------------------+---------+----------- track_activities
โ None
`pg_stat_statements`
Python module search path (only needed for development mode)
Server logging verbosity (DEBUG, INFO, WARNING, ERROR)
`17`
PostgreSQL data directory inside Docker container (**Do not modify**)
PostgreSQL server hostname or IP address
Azure PostgreSQL for TypeScript (node-postgres) workflow skill. Use this skill when the user needs Connect to Azure Database for PostgreSQL Flexible Server from Node.js/TypeScript using the pg (node-postgres) package and the operator should preserve the upstream workflow, copied support files, and provenance before merging or handing off.
Azure.ResourceManager.PostgreSql (.NET) workflow skill. Use this skill when the user needs Azure PostgreSQL Flexible Server SDK for .NET. Database management for PostgreSQL Flexible Server deployments and the operator should preserve the upstream workflow, copied support files, and provenance before merging or handing off.
GenBI (Generative BI) for AI agents, an open-source, governed text-to-SQL through an open context layer that turns natural-language questions into trusted dashboards, charts, and SQL across 20+ data sources, such as BigQuery, Snowflake, PostgreSQL, ClickHouse, Amazon Redshift, Databricks and more.
The all-in-one, open-source backend platform for agentic coding. InsForge gives your coding agent database, auth, storage, compute, hosting, and AI gateway to ship full-stack apps end-to-end.
AI equity research agent with resilient workflows, Redis Lua single-flight, pgvector RAG, versioned reports, evidence tracing, and RAG evaluation.
Augment Swarm with durable execution to help you build reliable and scalable multi-agent systems.
Learn how to use the azure-resource-manager-postgresql-dotnet-v2 Claude skill. Complete guide with installation instructions and examples.
Learn how to use the azure-postgres-ts-v2 Claude skill. Complete guide with installation instructions and examples.