Skip to main content
Glama
postgres-mcp-server

postgres-mcp-server

by irsyadjpp

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.

npm version Tests GitHub issues

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-server

Configuration

The server supports three configuration methods.

Default Database

DB_HOST=127.0.0.1
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=postgres
DB_SSL=true

Additional 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=true

Option 2 — JSON Configuration

Set:

DB_CONFIG_PATH=/path/to/db-config.json

Example:

{
  "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

query

Execute SQL queries with pagination

describe_table

Describe table structure

list_objects

List tables, views, or functions

list_schemas

List schemas

list_indexes

List indexes

search_objects

Search database objects

explain_query

Explain execution plan

get_connections

Show active connections

diagnose_database

Database health check

get_slow_queries

Analyze slow queries


PostgreSQL Advanced Tools

Tool

Description

list_partitions

Partition monitoring

replication_status

Replication monitoring

progress_report

VACUUM / CREATE INDEX progress

wal_monitor

WAL monitoring

extended_stats

Extended statistics

index_dedup

Index deduplication analysis

generated_columns

Generated column analysis

jsonb_analysis

JSONB recommendations

parallel_query

Parallel worker monitoring

autovacuum_advisor

Autovacuum recommendations

huge_pages

Huge pages monitoring

statements_enhanced

Enhanced pg_stat_statements

foreign_key

Foreign key analysis

backup_monitor

Backup monitoring

extensions

Installed extensions


Java Backend Tools

Tool

Description

connection_pool

HikariCP monitoring

jpa_mapping

Validate JPA mappings

orm_performance

ORM performance analysis

transaction_monitor

Transaction monitoring

prepared_statement

Prepared statement analysis

migration_tracking

Flyway/Liquibase monitoring

orm_index_coverage

ORM index recommendations

jsonb_entity

JSONB entity analysis

batch_operation

JDBC batch monitoring

sequence_monitor

Sequence monitoring

timeseries_partition

Partition recommendations

connection_leak

Connection leak detection

deadlock_analysis

Deadlock analysis

jpa_schema_validation

Validate JPA annotations

orm_performance_baseline

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.json

Windows

%APPDATA%\Claude\claude_desktop_config.json

Development

Clone the repository:

git clone https://github.com/irsyadjpp/postgres-mcp-server.git

cd postgres-mcp-server

npm install

Run development server:

npm run dev

Build:

npm run build

Run all tests:

npm test

Run unit tests:

npm run test:unit

Run integration tests:

npm run test:integration

Environment Variables

Variable

Default

Description

DB_HOST

127.0.0.1

PostgreSQL host

DB_PORT

5432

PostgreSQL port

DB_USER

postgres

Database user

DB_PASSWORD

—

Database password

DB_NAME

postgres

Database name

DB_SSL

true

Enable SSL

DB_CONFIG_PATH

—

JSON configuration path

READ_ONLY

true

Restrict to SELECT/EXPLAIN

QUERY_TIMEOUT

30000

Query timeout (ms)

MAX_PAGE_SIZE

500

Maximum page size

DEFAULT_PAGE_SIZE

100

Default page size

Additional databases:

DB_<NAME>_HOST
DB_<NAME>_PORT
DB_<NAME>_USER
DB_<NAME>_PASSWORD
DB_<NAME>_DATABASE
DB_<NAME>_SSL

Migration 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=production

Use it in any MCP tool:

{
  "database_name": "prod"
}

Alternatively, migrate to a JSON configuration by setting:

DB_CONFIG_PATH=/path/to/db-config.json

License

ISC

Available Tools

40 tools
autovacuum_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

    C2.6/5.0

    Scored across 40 tools

    Disambiguation2/5

    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 Consistency2/5

    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.

    Tool Count2/5

    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.

    Completeness3/5

    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

    ActivityInactive
    ResponsivenessOutdated data

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      B
      quality
      D
      maintenance
      A Model Context Protocol server that enables powerful PostgreSQL database management capabilities including analysis, schema management, data migration, and monitoring through natural language interactions.
      18
      10,856 npm
      204
      AGPL 3.0
    • A
      license
      B
      quality
      D
      maintenance
      A TypeScript-based Model Context Protocol server that enables AI assistants to perform secure database operations on PostgreSQL databases through structured tool interfaces.
      8
      MIT
    • F
      license
      Not graded
      quality
      D
      maintenance
      An 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.
      -
    • A
      license
      Not graded
      quality
      D
      maintenance
      A 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