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
- 🔍 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.
- ✅ Create/list databases & schemas
- ✅ Describe tables, analyze indexes
- ✅ Safe SELECT, UPDATE, DELETE execution
- 📈 Query performance & execution plan insights
- 🔒 Lock analysis, replication lag checks
- 🚀 Index, query, and buffer pool optimization
- 👥 User creation, role auditing
- 🔐 Privilege granting & connection monitoring
- 🧪 Table bloat check, autovacuum insights
- 💽 WAL logs, checkpoints, long transaction alerts
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
python -m venv venv
source venv/bin/activate # or venv\Scripts\activate on Windows
pip install -r requirements.txtcp .env.example .env # or use copy on Windows
# Update with:
DATABASE_URL=postgresql://username:password@localhost:5432/dbpython postgres_server.py✅ For MCP CLI users:
mcp dev postgres_server.pyWindows
{
"mcpServers": {
"postgres": {
"command": "python",
"args": ["postgres_server.py"],
"cwd": "C:\\path\\to\\postgres-mcp-server",
"env": {
"DATABASE_URL": "postgresql://username:password@localhost:5432/database"
}
}
}
}macOS/Linux
{
"mcpServers": {
"postgres": {
"command": "python",
"args": ["postgres_server.py"],
"cwd": "/absolute/path/to/postgres-mcp-server",
"env": {
"DATABASE_URL": "postgresql://username:password@localhost:5432/database"
}
}
}
}Quick Tool Finder: Use Ctrl+F (or Cmd+F) to search for specific tools by name, category, or functionality.
| Category | Count | Description |
|---|---|---|
| 🧱 Core Database | 25 | 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 |
Click to expand Core Database tools
PostgreSQL_analyze_database- Run ANALYZE on entire databasePostgreSQL_create_schema- Create new database schemaPostgreSQL_describe_table- Get detailed table informationPostgreSQL_drop_schema- Drop database schemaPostgreSQL_execute_select_query- Execute SELECT queriesPostgreSQL_execute_update_query- Execute INSERT/UPDATE/DELETEPostgreSQL_explain_query- Show query execution planPostgreSQL_get_database_config- Get database configurationPostgreSQL_get_database_growth_trend- Analyze database growthPostgreSQL_get_database_size- Get database size informationPostgreSQL_get_database_size_by_tablespace- Size by tablespacePostgreSQL_get_estimated_row_counts- Get estimated row countsPostgreSQL_get_server_version- Get PostgreSQL version infoPostgreSQL_get_table_count- Get row count of tablePostgreSQL_get_table_size- Get table size informationPostgreSQL_get_table_size_summary- Comprehensive table sizesPostgreSQL_get_tablespace_info- Get tablespace informationPostgreSQL_get_tablespace_usage- Tablespace usage statisticsPostgreSQL_list_databases- List all databasesPostgreSQL_list_schemas- List all schemasPostgreSQL_list_tables- List tables in schemaPostgreSQL_list_views- List views in schemaPostgreSQL_use_database- Switch to different databasePostgreSQL_vacuum_analyze_table- VACUUM ANALYZE tablePostgreSQL_kill_connection- Terminate database connection
Click to expand User & Security tools
PostgreSQL_create_user- Create database user/rolePostgreSQL_drop_user- Drop database user/rolePostgreSQL_get_column_privileges- Get column-level privilegesPostgreSQL_get_role_attributes- Get role attributes and detailsPostgreSQL_get_table_permissions- Get table permissionsPostgreSQL_grant_privileges- Grant table privilegesPostgreSQL_list_roles_with_login- List roles with login capabilityPostgreSQL_list_roles_with_superuser- List superuser rolesPostgreSQL_list_users_and_roles- List all users and rolesPostgreSQL_revoke_privileges- Revoke table privilegesPostgreSQL_security_audit- Basic security auditPostgreSQL_get_active_connections- Get active connectionsPostgreSQL_get_connection_limits- Connection limits and usagePostgreSQL_get_connection_pool_stats- Connection pool statisticsPostgreSQL_get_idle_connections- Find idle connectionsPostgreSQL_connection_churn_analysis- Analyze connection patternsPostgreSQL_get_concurrent_connection_analysis- Concurrent connectionsPostgreSQL_analyze_connection_pool_efficiency- Pool efficiency
Click to expand Performance tools
PostgreSQL_analyze_query_complexity- Analyze query complexityPostgreSQL_buffer_cache_hit_ratio- Cache hit ratiosPostgreSQL_check_long_running_queries- Find long-running queriesPostgreSQL_get_active_transactions- Get active transactionsPostgreSQL_get_buffer_cache_contents- Buffer cache contentsPostgreSQL_get_buffer_hit_ratios_detailed- Detailed hit ratiosPostgreSQL_get_cache_hit_ratio- Cache hit ratiosPostgreSQL_get_cache_hit_ratios- Buffer and index cache ratiosPostgreSQL_get_checkpoint_analysis- Checkpoint analysisPostgreSQL_get_checkpoint_info- Checkpoint informationPostgreSQL_get_checkpoint_stats- Checkpoint statisticsPostgreSQL_get_column_statistics- Column statisticsPostgreSQL_get_long_running_transactions- Long transactionsPostgreSQL_get_memory_context_analysis- Memory context analysisPostgreSQL_get_memory_usage_stats- Memory usage statisticsPostgreSQL_get_query_error_analysis- Query error analysisPostgreSQL_get_query_plan_cache_analysis- Query plan cachePostgreSQL_get_query_plan_cache_stats- Plan cache statisticsPostgreSQL_get_query_plans- Query execution plansPostgreSQL_get_query_runtime_distribution- Query runtime statsPostgreSQL_get_slow_queries- Get slow queriesPostgreSQL_get_slow_query_patterns- Slow query patternsPostgreSQL_get_slow_query_statements- Slow query statementsPostgreSQL_get_table_access_patterns- Table access patternsPostgreSQL_get_table_io_stats- Table I/O statisticsPostgreSQL_get_table_statistics- Table statisticsPostgreSQL_get_temp_file_stats- Temporary file statisticsPostgreSQL_get_temp_files- Temporary file usagePostgreSQL_get_top_heavy_queries- Top resource-heavy queriesPostgreSQL_get_transaction_age_monitoring- Transaction age monitoringPostgreSQL_get_wait_events- Current wait eventsPostgreSQL_analyze_advanced_buffer_usage- Advanced buffer analysisPostgreSQL_buffer_cache_relation_analysis- Buffer cache per relationPostgreSQL_checkpoint_activity- Detailed checkpoint activityPostgreSQL_connection_pool_stats- Connection pool statsPostgreSQL_get_connection_pool_analysis- Connection pool analysisPostgreSQL_get_performance_regression_indicators- Performance regressionPostgreSQL_high_io_tables- High I/O tablesPostgreSQL_memory_usage_analysis- Memory usage analysisPostgreSQL_table_io_patterns- Table I/O patternsPostgreSQL_temp_file_usage- Temporary file usage monitoringPostgreSQL_temporary_objects_usage- Temporary objects analysisPostgreSQL_active_temp_file_users- Active temp file usersPostgreSQL_get_high_wait_events- High wait eventsPostgreSQL_query_cancellation_analysis- Query cancellation analysis
Click to expand Lock & Concurrency tools
PostgreSQL_check_blocking_queries- Get blocking queriesPostgreSQL_detect_conflicting_queries- Detect conflicting queriesPostgreSQL_detect_foreign_key_lock_contention- FK lock contentionPostgreSQL_detect_index_lock_waits- Index lock waitsPostgreSQL_foreign_key_conflicts- Foreign key conflictsPostgreSQL_get_blocking_locks- Blocking and blocked queriesPostgreSQL_get_lock_monitoring- Monitor current locksPostgreSQL_get_lock_statistics- Lock statistics with wait timesPostgreSQL_get_lock_waits- Current lock waitsPostgreSQL_get_locks_info- Current database locksPostgreSQL_deadlock_analysis- Deadlock history and patternsPostgreSQL_analyze_foreign_key_locks- Foreign key lock analysisPostgreSQL_predicate_lock_analysis- Predicate lock analysisPostgreSQL_longest_idle_transactions- Longest idle transactionsPostgreSQL_long_running_prepared_transactions- Long prepared transactionsPostgreSQL_orphaned_prepared_transactions- Orphaned prepared transactionsPostgreSQL_prepared_transaction_retention- Prepared transaction retentionPostgreSQL_get_prepared_transactions- Get prepared transactionsPostgreSQL_analyze_transaction_wraparound- Transaction wraparoundPostgreSQL_detect_transaction_wraparound_risk- Wraparound riskPostgreSQL_transaction_wraparound_monitoring- Wraparound monitoringPostgreSQL_vacuum_freeze_age_analysis- Freeze age analysis
Click to expand Maintenance tools
PostgreSQL_analyze_autovacuum_efficiency- Autovacuum efficiencyPostgreSQL_analyze_table_freeze_stats- Table freeze statisticsPostgreSQL_autovacuum_tuning- Autovacuum tuning recommendationsPostgreSQL_check_table_bloat- Check table bloatPostgreSQL_get_autovacuum_activity- Autovacuum operationsPostgreSQL_get_autovacuum_settings- Autovacuum settingsPostgreSQL_get_autovacuum_stats_per_table- Autovacuum per tablePostgreSQL_get_bloated_tables- Find bloated tablesPostgreSQL_get_table_bloat_estimation- Table bloat estimationPostgreSQL_get_table_fragmentation_analysis- Table fragmentationPostgreSQL_get_vacuum_analyze_recommendations- VACUUM recommendationsPostgreSQL_get_vacuum_inefficiency_tables- Inefficient VACUUM tablesPostgreSQL_get_vacuum_progress- Monitor VACUUM progressPostgreSQL_vacuum_analyze_table- VACUUM ANALYZE tablePostgreSQL_vacuum_progress_monitoring- VACUUM progress monitoringPostgreSQL_analyze_vacuum_efficiency- Vacuum efficiency analysisPostgreSQL_detect_table_bloat_regression- Table bloat regressionPostgreSQL_maintenance_window_activity- Maintenance window analysisPostgreSQL_monitor_autovacuum_progress- Monitor autovacuumPostgreSQL_non_autovacuum_friendly_datatypes- Non-autovacuum datatypesPostgreSQL_partition_maintenance- Partition maintenancePostgreSQL_vacuum_analyze_frequency_analysis- VACUUM frequencyPostgreSQL_analyze_table_freeze_stats- Freeze stats analysisPostgreSQL_get_vacuum_inefficiency_tables- VACUUM inefficiencyPostgreSQL_toast_table_excessive_usage- TOAST table usagePostgreSQL_unlogged_tables_analysis- Unlogged tables analysisPostgreSQL_statistics_reset_frequency- Statistics reset frequencyPostgreSQL_parameter_sniffing_detection- Parameter sniffing detection
Click to expand Index tools
PostgreSQL_analyze_index_bloat- Identify index bloatPostgreSQL_create_index- Create index on tablePostgreSQL_drop_index- Drop indexPostgreSQL_get_index_maintenance_status- Index maintenance statusPostgreSQL_get_index_usage- Index usage statisticsPostgreSQL_get_index_usage_stats- Comprehensive index usagePostgreSQL_get_unused_indexes- Find unused indexesPostgreSQL_identify_index_redundancy- Redundant indexesPostgreSQL_index_bloat_maintenance_analysis- Index bloat maintenancePostgreSQL_index_dead_tuples_analysis- Dead tuples per indexPostgreSQL_index_redundancy_detection- Index redundancy detectionPostgreSQL_index_scan_efficiency- Index scan efficiencyPostgreSQL_list_indexes- List table indexesPostgreSQL_analyze_index_effectiveness- Index effectivenessPostgreSQL_plan_invalidation_analysis- Plan invalidation analysis
Click to expand Replication & Backup tools
PostgreSQL_backup_recovery_info- Backup and recovery infoPostgreSQL_get_backup_details- Last backup statusPostgreSQL_get_backup_recovery_readiness- Backup readinessPostgreSQL_get_backup_status- Backup status informationPostgreSQL_get_logical_replication_stats- Logical replication statsPostgreSQL_get_publication_details- Publication detailsPostgreSQL_get_publication_subscription_details- Pub/sub detailsPostgreSQL_get_publication_tables- Publication tablesPostgreSQL_get_publications- Logical replication publicationsPostgreSQL_get_replication_slot_details- Replication slot detailsPostgreSQL_get_replication_slot_infos- Replication slot infoPostgreSQL_get_replication_slots- Replication slotsPostgreSQL_get_replication_stats- Replication statisticsPostgreSQL_get_replication_status- Replication statusPostgreSQL_get_subscription_info- Subscription informationPostgreSQL_get_wal_archiving_settings- WAL archiving settingsPostgreSQL_get_wal_stats- WAL statisticsPostgreSQL_get_write_ahead_log_analysis- WAL analysisPostgreSQL_diagnose_logical_replication_lag- Replication lag diagnosisPostgreSQL_logical_replication_slot_lag- Logical replication lag
Click to expand Table Operations tools
PostgreSQL_check_constraint_violations- Constraint violationsPostgreSQL_constraint_violations- Data integrity issuesPostgreSQL_foreign_key_orphaned_references- Orphaned referencesPostgreSQL_foreign_keys_referencing_table- Tables referencing tablePostgreSQL_get_constraint_violation_risks- Constraint risksPostgreSQL_get_constraint_violations- Constraint violationsPostgreSQL_get_foreign_keys- Foreign key relationshipsPostgreSQL_get_table_constraints- Table constraintsPostgreSQL_get_table_inheritance- Table inheritancePostgreSQL_get_table_rules- Table rulesPostgreSQL_list_foreign_key_references- Foreign key referencesPostgreSQL_list_table_rules- List table rulesPostgreSQL_check_table_inheritance- Table inheritance analysisPostgreSQL_get_partitioned_tables- Partitioned tables infoPostgreSQL_get_partition_details- Partitioning detailsPostgreSQL_get_partition_info_detailed- Detailed partition infoPostgreSQL_get_materialized_views- List materialized viewsPostgreSQL_get_materialized_view_stats- Materialized view statsPostgreSQL_monitor_materialized_views- Monitor materialized viewsPostgreSQL_refresh_materialized_view- Refresh materialized viewPostgreSQL_get_toast_tables- TOAST tables informationPostgreSQL_get_sequences- All sequences with valuesPostgreSQL_get_sequence_value- Get sequence valuePostgreSQL_list_sequences- List sequences in schemaPostgreSQL_reset_sequence- Reset sequence value
Click to expand Extensions & Objects tools
PostgreSQL_extension_usage- Extension usage statisticsPostgreSQL_get_detailed_foreign_tables- Foreign tables detailsPostgreSQL_get_event_triggers- Event triggersPostgreSQL_get_event_triggers_detailed- Detailed event triggersPostgreSQL_get_extension_list- Installed extensionsPostgreSQL_get_extension_usage_analysis- Extension usage analysisPostgreSQL_get_foreign_tables- Foreign tables and wrappersPostgreSQL_get_full_text_search_configs- Full-text search configsPostgreSQL_get_functions- User-defined functionsPostgreSQL_get_text_search_configs- Text search configurationsPostgreSQL_get_trigger_performance_impact- Trigger performancePostgreSQL_get_triggers- All triggers in databasePostgreSQL_list_event_triggers_detailed- Detailed event triggersPostgreSQL_list_foreign_tables_detailed- Detailed foreign tablesPostgreSQL_list_functions- Functions in schemaPostgreSQL_list_triggers- Triggers on tablePostgreSQL_assess_trigger_performance_impact- Trigger impactPostgreSQL_long_execution_triggers- Long execution triggers
Click to expand Configuration tools
PostgreSQL_get_database_config- Database configurationPostgreSQL_get_important_settings- Important settingsPostgreSQL_get_server_version- Server version infoPostgreSQL_checkpoint_activity- Checkpoint activityPostgreSQL_disk_usage_forecast- Disk usage forecastPostgreSQL_get_checkpoint_analysis- Checkpoint analysisPostgreSQL_get_checkpoint_info- Checkpoint informationPostgreSQL_get_checkpoint_stats- Checkpoint statisticsPostgreSQL_monitor_checkpoint_efficiency- Checkpoint efficiencyPostgreSQL_monitor_wal_generation_rate- WAL generation ratePostgreSQL_wal_segment_recycling_analysis- WAL recyclingPostgreSQL_check_database_encoding_collation- Encoding/collation check
Click to expand Advanced Analysis tools
PostgreSQL_analyze_sequence_usage- Sequence usage analysisPostgreSQL_get_sequence_usage_risks- Sequence exhaustion risksPostgreSQL_get_sequence_usage_stats- Sequence usage statisticsPostgreSQL_predict_sequence_exhaustion- Predict sequence exhaustionPostgreSQL_detect_table_bloat_regression- Table bloat regressionPostgreSQL_detect_transaction_wraparound_risk- Wraparound riskPostgreSQL_diagnose_logical_replication_lag- Replication lag diagnosisPostgreSQL_disk_usage_forecast- Disk usage forecastingPostgreSQL_get_database_growth_trend- Database growth trendsPostgreSQL_get_performance_regression_indicators- Performance regressionPostgreSQL_get_query_error_analysis- Query error patternsPostgreSQL_maintenance_window_activity- Maintenance patternsPostgreSQL_memory_usage_analysis- Memory usage analysisPostgreSQL_monitor_connection_patterns- Connection patternsPostgreSQL_replication_lag_detailed- Detailed replication lagPostgreSQL_replication_slot_activity_analysis- Slot activityPostgreSQL_check_function_performance- Function performancePostgreSQL_check_replication_lag_details- Replication lag detailsPostgreSQL_analyze_buffer_utilization- Buffer utilizationPostgreSQL_analyze_query_plans- Query plan analysisPostgreSQL_analyze_trigger_performance- Trigger performancePostgreSQL_monitor_checkpoint_efficiency- Checkpoint efficiencyPostgreSQL_monitor_connection_patterns- Connection patternsPostgreSQL_monitor_wal_generation_rate- WAL generationPostgreSQL_plan_invalidation_analysis- Plan invalidationPostgreSQL_query_pattern_clustering- Query pattern analysisPostgreSQL_statistical_anomaly_detection- Statistical anomaliesPostgreSQL_temporary_objects_usage- Temporary objectsPostgreSQL_vacuum_freeze_age_analysis- Freeze age analysis
"List all indexes in the public schema" "Show detailed structure of the
orderstable"
"What are the slowest queries?" "Analyze unused indexes"
"VACUUM ANALYZE the
productstable" "Find bloated tables in the database"
"List all users and their roles" "Show active connections and privilege levels"
- Full PostgreSQL system health check
- Query and index optimization
- AI-based schema exploration
- SQL query building with NLP
- Role auditing, connection analysis
- Secure query validation and permission check
This project is licensed under the MIT License – see the LICENSE file for details.
If you find this useful, please:
- ⭐ Star the repo
- 🍝 Fork it
- 🛠️ Submit issues or PRs
🧠 Let’s build smarter PostgreSQL automation – one prompt at a time.
#PostgreSQL #MCP #AI_Database_Tools #Claude #Query_Optimizer #Database_Health_Check #Python_PostgreSQL #DevOps_AI #Database_Monitoring #OpenSource