postgres-mcp-server
Tracks Flyway database migrations, detects schema drift, and validates checksums.
Validates database schemas against JPA/Hibernate entity mappings and analyzes ORM query performance to detect issues like N+1 problems.
Tracks Liquibase database migrations, detects schema drift, and validates checksums.
Provides secure database access to PostgreSQL, enabling SQL queries, schema inspection, table management, and advanced monitoring and diagnostics across multiple databases.
Offers tools for monitoring Spring Boot applications, including connection pool metrics, ORM performance analysis, transaction monitoring, and migration tracking.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@postgres-mcp-server list all tables in the public schema"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
PostgreSQL MCP Server
A secure Model Context Protocol (MCP) Server for PostgreSQL built with Kysely ORM. It enables AI assistants such as Claude Desktop, GitHub Copilot, Cline, and others to interact with PostgreSQL databases using natural language.
A secure Model Context Protocol (MCP) server for PostgreSQL built with Kysely ORM. It provides AI assistants with secure, read-only (by default) access to PostgreSQL databases using natural language.
Table of Contents
Features
Database Features
✅ PostgreSQL 12–18 support
✅ Multi-database support
✅ Lazy database initialization
✅ Connection pooling
✅ Query timeout configuration
✅ Read-only mode by default
✅ Parameterized queries (SQL Injection protection)
✅ Type-safe validation using Zod
Related MCP server: PostgreSQL MCP Server
MCP Capabilities
More than 40 MCP tools covering:
SQL Query Execution
Schema Inspection
Table Explorer
Index Explorer
Query Explain Plan
Database Diagnostics
Performance Monitoring
PostgreSQL DBA Utilities
Java Backend Monitoring
PostgreSQL Advanced Monitoring
Supports PostgreSQL modern features including:
Partitioning
WAL Monitoring
Replication
Parallel Query
JSONB Analysis
Generated Columns
Extended Statistics
Autovacuum Advisor
Backup Monitoring
Huge Pages
Extensions
Foreign Key Analysis
Java Backend Support
Built specifically for Java backend engineers.
Supports monitoring for:
Spring Boot
Quarkus
Hibernate
Spring Data JPA
HikariCP
Flyway
Liquibase
JDBC Batch
Transaction Monitoring
Connection Leak Detection
Installation
Run directly with NPX:
npx @irsyadjpp/postgres-mcp-serverConfiguration
The server supports three configuration methods.
Option 1 — Environment Variables (Recommended)
Default Database
DB_HOST=127.0.0.1
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=postgres
DB_SSL=trueAdditional Databases
DB_PROD_HOST=prod.example.com
DB_PROD_PORT=5432
DB_PROD_USER=app_user
DB_PROD_PASSWORD=secret
DB_PROD_DATABASE=production
DB_PROD_SSL=true
DB_STAGING_HOST=staging.example.com
DB_STAGING_PORT=5432
DB_STAGING_USER=app_user
DB_STAGING_PASSWORD=secret
DB_STAGING_DATABASE=staging
DB_STAGING_SSL=trueOption 2 — JSON Configuration
Set:
DB_CONFIG_PATH=/path/to/db-config.jsonExample:
{
"databases": [
{
"name": "default",
"host": "127.0.0.1",
"port": 5432,
"user": "postgres",
"password": "password",
"database": "postgres",
"maxConnections": 5,
"ssl": true
},
{
"name": "production",
"host": "prod.example.com",
"port": 5432,
"user": "app_user",
"password": "secret",
"database": "production",
"maxConnections": 10,
"ssl": true
}
]
}Option 3 — Dynamic Database Selection
Every MCP tool accepts an optional parameter:
{
"database_name": "production"
}Example:
{
"sql": "SELECT * FROM users",
"database_name": "production"
}Available Tools
Core Tools
Tool | Description |
| Execute SQL queries with pagination |
| Describe table structure |
| List tables, views, or functions |
| List schemas |
| List indexes |
| Search database objects |
| Explain execution plan |
| Show active connections |
| Database health check |
| Analyze slow queries |
PostgreSQL Advanced Tools
Tool | Description |
| Partition monitoring |
| Replication monitoring |
| VACUUM / CREATE INDEX progress |
| WAL monitoring |
| Extended statistics |
| Index deduplication analysis |
| Generated column analysis |
| JSONB recommendations |
| Parallel worker monitoring |
| Autovacuum recommendations |
| Huge pages monitoring |
| Enhanced pg_stat_statements |
| Foreign key analysis |
| Backup monitoring |
| Installed extensions |
Java Backend Tools
Tool | Description |
| HikariCP monitoring |
| Validate JPA mappings |
| ORM performance analysis |
| Transaction monitoring |
| Prepared statement analysis |
| Flyway/Liquibase monitoring |
| ORM index recommendations |
| JSONB entity analysis |
| JDBC batch monitoring |
| Sequence monitoring |
| Partition recommendations |
| Connection leak detection |
| Deadlock analysis |
| Validate JPA annotations |
| CRUD performance baseline |
Key Capabilities
Multi-database support
Automatic pagination
SQL Injection protection
PostgreSQL diagnostics
Query performance analysis
Database health monitoring
DBA utilities
Spring Boot optimization
Quarkus optimization
Hibernate optimization
Claude Desktop Configuration
Example:
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": [
"@irsyadjpp/postgres-mcp-server@latest"
],
"env": {
"DB_HOST": "127.0.0.1",
"DB_PORT": "5432",
"DB_USER": "postgres",
"DB_PASSWORD": "password",
"DB_NAME": "postgres",
"DB_SSL": "true"
}
}
}
}Other Supported AI Assistants
Configuration examples are included for:
Claude Desktop
GitHub Copilot Chat
Cline
Antigravity IDE
See CONFIG-GUIDES.md for detailed setup instructions.
Claude Configuration Locations
macOS
~/Library/Application Support/Claude/claude_desktop_config.jsonWindows
%APPDATA%\Claude\claude_desktop_config.jsonDevelopment
Clone the repository:
git clone https://github.com/irsyadjpp/postgres-mcp-server.git
cd postgres-mcp-server
npm installRun development server:
npm run devBuild:
npm run buildRun all tests:
npm testRun unit tests:
npm run test:unitRun integration tests:
npm run test:integrationEnvironment Variables
Variable | Default | Description |
|
| PostgreSQL host |
|
| PostgreSQL port |
|
| Database user |
| — | Database password |
|
| Database name |
|
| Enable SSL |
| — | JSON configuration path |
|
| Restrict to SELECT/EXPLAIN |
|
| Query timeout (ms) |
|
| Maximum page size |
|
| Default page size |
Additional databases:
DB_<NAME>_HOST
DB_<NAME>_PORT
DB_<NAME>_USER
DB_<NAME>_PASSWORD
DB_<NAME>_DATABASE
DB_<NAME>_SSLMigration Guide
Existing users require no configuration changes.
The server maintains full backward compatibility.
To add another database:
DB_PROD_HOST=prod.example.com
DB_PROD_PORT=5432
DB_PROD_USER=app_user
DB_PROD_PASSWORD=secret
DB_PROD_DATABASE=productionUse it in any MCP tool:
{
"database_name": "prod"
}Alternatively, migrate to a JSON configuration by setting:
DB_CONFIG_PATH=/path/to/db-config.jsonLicense
ISC
Available Tools
40 toolsautovacuum_advisorA
Analyze autovacuum effectiveness and recommend tuning settings
Inputs: table, schema, database_name.
backup_monitorB
Monitor pg_basebackup progress and PITR recovery timeline
Inputs: database_name.
batch_operationC
Monitor JDBC batch operation performance and efficiency
Inputs: database_name.
connection_leakC
Detect connection leaks and analyze connection acquisition patterns
Inputs: database_name.
connection_poolB
Monitor HikariCP connection pool metrics, detect connection leaks, and track wait times
Inputs: database_name.
deadlock_analysisC
Analyze deadlock patterns and lock wait information
Inputs: database_name.
describe_tableB
Get table structure including columns, constraints, and size statistics
Inputs: table (required), schema (required), database_name.
diagnose_databaseB
Composite database health check: cache, connections, vacuum, indexes, sequences
Inputs: database_name, include_queries, include_connections.
explain_queryC
Get query execution plan (EXPLAIN)
Inputs: sql (required), costs, format, analyze, +2 more.
extended_statsB
List extended statistics and provide recommendations for multi-column correlations
Inputs: table, schema, database_name.
extensionsB
List installed extensions, check compatibility, and view dependencies
Inputs: database_name.
foreign_keyB
Monitor foreign key performance and identify missing indexes
Inputs: table, schema, database_name.
generated_columnsB
List generated columns and monitor their dependencies (PostgreSQL 12+)
Inputs: table, schema, database_name.
get_connectionsB
Show active database connections, utilization, and idle-in-transaction warnings
Inputs: group_by, database_name, include_queries.
get_slow_queriesC
Analyze slow queries via pg_stat_statements with filtering and sorting
Inputs: limit, sort_by, min_calls, database_name, +2 more.
huge_pagesB
Monitor huge pages usage and provide configuration recommendations
Inputs: database_name.
index_dedupB
Identify B-tree indexes that can benefit from deduplication (PostgreSQL 13+)
Inputs: table, schema, database_name.
jpa_mappingC
Validate database schema against JPA/Hibernate entity mappings
Inputs: schema, database_name.
jpa_schema_validationB
Validate Spring Data JPA annotations against database schema
Inputs: schema, database_name.
jsonb_analysisB
Analyze JSONB columns and recommend GIN indexes for better performance
Inputs: table, schema, database_name.
jsonb_entityB
Analyze JSONB columns for @Convert entity attributes and recommend GIN indexes
Inputs: schema, database_name.
list_indexesB
List indexes for a table or schema
Inputs: schema (required), table, database_name.
list_objectsB
List tables, views, or functions in a schema
Inputs: type (required), schema, database_name.
list_partitionsC
List partitioned tables and monitor partition pruning efficiency (PostgreSQL 12+)
Inputs: table, schema, database_name.
list_schemasB
List all schemas in the database
Inputs: database_name, includeSystemSchemas.
migration_trackingB
Track Flyway/Liquibase migrations, detect schema drift, and validate checksums
Inputs: database_name.
orm_index_coverageB
Analyze index coverage for ORM @Query annotations and recommend missing indexes
Inputs: schema, database_name.
orm_performanceB
Analyze ORM query performance, detect N+1 problems, and identify lazy loading issues
Inputs: database_name.
orm_performance_baselineC
Establish and monitor performance baselines for ORM CRUD operations
Inputs: database_name.
parallel_queryC
Monitor parallel query worker usage and performance
Inputs: database_name.
prepared_statementC
Analyze prepared statement cache hit ratio and query patterns
Inputs: database_name.
progress_reportB
Monitor progress of VACUUM, ANALYZE, CLUSTER, and CREATE INDEX operations
Inputs: database_name.
queryC
Execute SQL with pagination and parameterization
Inputs: sql (required), offset, pageSize, parameters, +1 more.
replication_statusA
Monitor logical replication: publications, subscriptions, and lag (PostgreSQL 13+)
Inputs: database_name.
search_objectsB
Find tables, columns, functions, views by name pattern across schemas
Inputs: pattern (required), limit, schemas, object_types, +1 more.
sequence_monitorB
Monitor sequences for JPA @GeneratedValue and detect exhaustion risks
Inputs: schema, database_name.
statements_enhancedC
Enhanced pg_stat_statements analysis with parallel workers and WAL tracking
Inputs: limit, database_name.
timeseries_partitionC
Recommend partitioning strategies for time-series entity tables
Inputs: schema, database_name.
transaction_monitorB
Monitor active transactions, isolation levels, and detect long-running transactions
Inputs: database_name.
wal_monitorC
Monitor WAL size, growth, archive status, and replication slots
Inputs: database_name.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 40 tools
Several tools have overlapping purposes, such as query, explain_query, get_slow_queries, and statements_enhanced all dealing with query execution and analysis. Similarly, connection_pool, connection_leak, and get_connections all monitor connections, and multiple ORM performance tools blur boundaries despite detailed descriptions.
Naming conventions are mixed: some follow verb_noun (list_objects, describe_table), others are noun_phrases (replication_status, extends_stats), and some use compound adjectives (autovacuum_advisor, statements_enhanced). Long, inconsistent ORM-related names further reduce predictability.
40 tools is excessive for a PostgreSQL monitoring server. Many niche tools (huge_pages, extended_stats, generated_columns) are very specific and could be consolidated into broader diagnostic tools, making the set feel overweighted and harder to navigate.
The tool set covers a wide range of database monitoring and health checks, including queries, schema inspection, index analysis, and replication. However, it lacks administrative actions like vacuum, index creation/dropping, or even EXPLAIN ANALYZE, leaving operational gaps for a server that purports to manage PostgreSQL.
Maintenance
Related MCP Connectors
Hosted PostgreSQL MCP server for Claude, ChatGPT and Cursor, with each action switched per account.
Text-to-SQL MCP server: read-only queries on PostgreSQL, MySQL and SQL Server with your real schema
Hosted MCP server for PostgreSQL diagnostics: slow queries, missing indexes, connection pressure.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Related MCP Servers
- AlicenseBqualityDmaintenanceA Model Context Protocol server that enables powerful PostgreSQL database management capabilities including analysis, schema management, data migration, and monitoring through natural language interactions.1810,856 npm204AGPL 3.0
- AlicenseBqualityDmaintenanceA TypeScript-based Model Context Protocol server that enables AI assistants to perform secure database operations on PostgreSQL databases through structured tool interfaces.8MIT
- FlicenseNot gradedqualityDmaintenanceAn MCP server that enables AI assistants to interact with PostgreSQL databases by executing SQL queries and inspecting database schemas. It provides tools for standardized database exploration and management through the Model Context Protocol.-
- AlicenseNot gradedqualityDmaintenanceA Model Context Protocol (MCP) Server that allows AI models to securely interact with data hosted in Azure Database for PostgreSQL. It enables natural language querying, schema exploration, and data management through MCP clients like Claude Desktop and Visual Studio Code.MIT