Skip to main content
Glama
Sign In
deenesjakoruzh
MCP PostgreSQL Operations

MCP PostgreSQL Operations

by call518
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Hybrid

MCP Server for PostgreSQL Operations and Monitoring

MCPVault: claimed

MCP Toplist

License: MIT Python Docker Pulls PostgreSQL BuyMeACoffee

Deploy to PyPI with tag PyPI PyPI - Downloads

Architecture & Internal (DeepWiki)

Ask DeepWiki

Related MCP server: MCP-PostgreSQL-Ops

Overview

MCP-PostgreSQL-Ops is a professional MCP server for PostgreSQL database operations, monitoring, and management. Supports PostgreSQL 12-18 with comprehensive database analysis, performance monitoring, and intelligent maintenance recommendations through natural language queries. Most features work independently, but advanced query analysis capabilities are enhanced when pg_stat_statements and (optionally) pg_stat_monitor extensions are installed.

Features

  • ✅ Zero Configuration: Works with PostgreSQL 12-18 out-of-the-box with automatic version detection.

  • ✅ Natural Language: Ask questions like "Show me slow queries" or "Analyze table bloat."

  • ✅ Production Safe: Read-only operations, RDS/Aurora compatible with regular user permissions.

  • ✅ Extension Enhanced: Optional pg_stat_statements and pg_stat_monitor for advanced query analytics.

  • ✅ Comprehensive Database Monitoring: Performance analysis, bloat detection, and maintenance recommendations.

  • ✅ Smart Query Analysis: Slow query identification with pg_stat_statements and pg_stat_monitor integration.

  • ✅ Schema & Relationship Discovery: Database structure exploration with detailed relationship mapping.

  • ✅ VACUUM & Autovacuum Intelligence: Real-time maintenance monitoring and effectiveness analysis.

  • ✅ Multi-Database Operations: Seamless cross-database analysis and monitoring.

  • ✅ Enterprise-Ready: Safe read-only operations with RDS/Aurora compatibility.

  • ✅ Developer-Friendly: Simple codebase for easy customization and tool extension.

🔧 Advanced Capabilities

  • Version-aware I/O statistics (enhanced on PostgreSQL 16+, byte columns on PG 18+).

  • Real-time connection and lock monitoring.

  • Background process and checkpoint analysis.

  • Replication status and WAL monitoring.

  • Database capacity and bloat analysis.

  • Wait event catalog with descriptions (PG 17+).

  • WAL summarizer monitoring for incremental backups (PG 17+).

  • Async I/O subsystem monitoring (PG 18+).

  • Per-backend I/O and WAL statistics (PG 18+).

Tool Usage Examples

📸 More Examples with Screenshots →

MCP-PostgreSQL-Ops Usage Screenshot

MCP-PostgreSQL-Ops Usage Screenshot

⭐ Quickstart (5 minutes)

Note: The postgresql container included in docker-compose.yml is intended for quickstart testing purposes only. You can connect to your own PostgreSQL instance by adjusting the environment variables as needed.

If you want to use your own PostgreSQL instance instead of the built-in test container:

  • Update the target PostgreSQL connection information in your .env file (see POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB).

  • In docker-compose.yml, comment out (disable) the postgres and postgres-init-extensions containers to avoid starting the built-in test database.

Flow Diagram of Quickstart/Tutorial

Flow Diagram of Quickstart/Tutorial

1. Environment Setup

Note: While superuser privileges provide access to all databases and system information, the MCP server also works with regular user permissions for basic monitoring tasks.

git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops

### Check and modify .env file
cp .env.example .env
vim .env
### No need to modify defaults, but if using your own PostgreSQL server, edit below:
POSTGRES_HOST=host.docker.internal
POSTGRES_PORT=15432  # External port for host access (mapped to internal 5432)
POSTGRES_USER=postgres
POSTGRES_PASSWORD=changeme!@34
POSTGRES_DB=ecommerce # Default connection DB. Superusers can access all DBs.

Note: PGDATA=/data/db is preconfigured for the Percona PostgreSQL Docker image, which requires this specific path for proper write permissions.

2. Start Demo Containers

# Start all containers including built-in PostgreSQL for testing
docker-compose up -d

# Alternative: If using your own PostgreSQL instance
# Comment out postgres and postgres-init-extensions services in docker-compose.yml
# Then use the custom configuration:
# docker-compose -f docker-compose.custom-db.yml up -d

⏰ Wait for Environment Setup: The initial environment setup takes a few minutes as containers are started in sequence:

  1. PostgreSQL container starts first with database initialization

  2. PostgreSQL Extensions container installs extensions and creates comprehensive test data (~83K records)

  3. MCP Server and MCPO Proxy containers start after PostgreSQL is ready

  4. OpenWebUI container starts last and may take additional time to load the web interface

💡 Tip: Wait 2-3 minutes after running docker-compose up -d before accessing OpenWebUI to ensure all services are fully initialized.

🔍 Check Container Status (Optional):

# Monitor container startup progress
docker-compose logs -f

# Check if all containers are running
docker-compose ps

# Verify PostgreSQL is ready
docker compose exec -T postgres sh -c \
  'pg_isready -h 127.0.0.1 -p 5432 -U "$POSTGRES_USER" -d "$POSTGRES_DB"'

3. Access to OpenWebUI

http://localhost:3003/

  • The list of MCP tool features provided by swagger can be found in the MCPO API Docs URL.

    • e.g: http://localhost:8003/docs

4. Registering the Tool in OpenWebUI

📌 Note: Web-UI configuration instructions are based on OpenWebUI v0.6.22. Menu locations and settings may differ in newer versions.

  1. logging in to OpenWebUI with an admin account

  2. go to "Settings" → "Tools" from the top menu.

  3. Enter the postgresql-ops Tool address (e.g., http://localhost:8003/postgresql-ops) to connect MCP Tools.

  4. Setup Ollama or OpenAI.

5. Complete!

Congratulations! Your MCP PostgreSQL Operations server is now ready for use. You can start exploring your databases with natural language queries.

🚀 Try These Example Queries:

  • "Show me the current active connections"

  • "What are the slowest queries in the system?"

  • "Analyze table bloat across all databases"

  • "Show me database size information"

  • "What tables need VACUUM maintenance?"

📖 Next Steps:

(NOTE) Sample Test Data Overview

The create-test-data.sql script is executed by the postgres-init-extensions container (defined in docker-compose.yml) on first startup, automatically generating comprehensive test databases for MCP tool testing:

Database

Purpose

Schema & Tables

Scale

ecommerce

E-commerce system

public: categories, products, customers, orders, order_items

10 categories, 500 products, 100 customers, 200 orders, 400 order items

analytics

Analytics & reporting

public: page_views, sales_summary

1,000 page views, 30 sales summaries

inventory

Warehouse management

public: suppliers, inventory_items, purchase_orders

10 suppliers, 100 items, 50 purchase orders

hr_system

HR management

public: departments, employees, payroll

5 departments, 50 employees, 150 payroll records

Test users created: app_readonly, app_readwrite, analytics_user, backup_user

Optimized for testing: Intentional table bloat, various indexes (used/unused), time-series data, complex relationships

Tool Compatibility Matrix

Automatic Adaptation: All tools work transparently across supported versions - no configuration needed!

🟢 Extension-Independent Tools (No Extensions Required)

Tool Name

Extensions Required

PG 12

PG 13

PG 14

PG 15

PG 16

PG 17

PG 18

System Views/Tables Used

get_server_info

❌ None

✅

✅

✅

✅

✅

✅

✅

version(), pg_extension

get_active_connections

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_activity

get_postgresql_config

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_settings

get_database_list

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_database

get_table_list

❌ None

✅

✅

✅

✅

✅

✅

✅

information_schema.tables

get_table_schema_info

❌ None

✅

✅

✅

✅

✅

✅

✅

information_schema.*, pg_indexes

get_database_schema_info

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_namespace, pg_class, pg_proc

get_table_relationships

❌ None

✅

✅

✅

✅

✅

✅

✅

information_schema.* (constraints)

get_user_list

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_user, pg_roles

get_index_usage_stats

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_user_indexes

get_database_size_info

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_database_size()

get_table_size_info

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_total_relation_size()

get_vacuum_analyze_stats

❌ None

✅

✅

✅

✅

✅

✅

✅ Enhanced

pg_stat_user_tables

get_current_database_info

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_database, current_database()

get_table_bloat_analysis

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_user_tables

get_database_bloat_overview

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_user_tables

get_autovacuum_status

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_user_tables

get_autovacuum_activity

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_user_tables

get_running_vacuum_operations

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_activity

get_vacuum_effectiveness_analysis

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_user_tables

get_lock_monitoring

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_locks, pg_stat_activity

get_wal_status

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_current_wal_lsn()

get_database_stats

❌ None

✅

✅

✅

✅

✅

✅

✅ Enhanced

pg_stat_database

get_table_io_stats

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_statio_user_tables

get_index_io_stats

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_statio_user_indexes

get_database_conflicts_stats

❌ None

✅

✅

✅

✅

✅

✅

✅

pg_stat_database_conflicts

🚀 Version-Aware Tools (Auto-Adapting)

Tool Name

Extensions Required

PG 12

PG 13

PG 14

PG 15

PG 16

PG 17

PG 18

Special Features

get_io_stats

❌ None

✅ Basic

✅ Basic

✅ Basic

✅ Basic

✅ Enhanced

✅ Enhanced

✅ Enhanced

PG16+: pg_stat_io support; PG18+: byte columns

get_bgwriter_stats

❌ None

✅

✅

✅

✅

✅

✅ Special

✅ Enhanced

PG17: Separate checkpointer stats; PG18+: num_done, slru_written

get_replication_status

❌ None

✅ Compatible

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

PG13+: wal_status, safe_wal_size; PG16+: enhanced WAL receiver; PG17+: invalidation_reason, inactive_since

get_all_tables_stats

❌ None

✅ Compatible

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

PG13+: n_ins_since_vacuum tracking for vacuum maintenance optimization

get_user_functions_stats

⚙️ Config Required

✅

✅

✅

✅

✅

✅

✅

Requires track_functions=pl

get_wait_events

❌ None

✅ Fallback

✅ Fallback

✅ Fallback

✅ Fallback

✅ Fallback

✅ Native

✅ Native

PG17+: pg_wait_events catalog; PG12-16: fallback to pg_stat_activity current waits

get_wal_summarizer_status

❌ None

❌

❌

❌

❌

❌

✅

✅

PG17+: WAL summarizer monitoring for incremental backups

get_async_io_status

❌ None

❌

❌

❌

❌

❌

❌

✅

PG18+: pg_aios async I/O subsystem monitoring

get_per_backend_io_stats

❌ None

❌

❌

❌

❌

❌

❌

✅

PG18+: Per-backend I/O and WAL statistics

🟡 Extension-Dependent Tools (Extensions Required)

Tool Name

Required Extension

PG 12

PG 13

PG 14

PG 15

PG 16

PG 17

PG 18

Notes

get_pg_stat_statements_top_queries

pg_stat_statements

✅ Compatible

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

PG12: total_time → total_exec_time; PG13+: native total_exec_time; PG17+: stats_since

get_pg_stat_monitor_recent_queries

pg_stat_monitor

✅ Compatible

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

✅ Enhanced

PG12: total_time → total_exec_time; PG13+: native total_exec_time

🆕 Version-Specific Features

PostgreSQL 17

  • pg_wait_events view: Native wait event catalog with descriptions (used by get_wait_events)

  • WAL summarizer: Monitoring for incremental backup support (used by get_wal_summarizer_status)

  • Replication slot enhancements: invalidation_reason and inactive_since columns (used by get_replication_status)

  • pg_stat_statements stats_since: Track when statistics were last reset (used by get_pg_stat_statements_top_queries)

  • VACUUM progress: Index vacuum tracking in progress views (future enhancement for get_running_vacuum_operations)

PostgreSQL 18

  • pg_aios view: Async I/O subsystem monitoring (used by get_async_io_status)

  • Per-backend I/O stats: Individual backend I/O and WAL statistics (used by get_per_backend_io_stats)

  • VACUUM/ANALYZE time columns: total_vacuum_time, total_autovacuum_time, total_analyze_time, total_autoanalyze_time cumulative timing (used by get_vacuum_analyze_stats)

  • pg_stat_io byte columns: read_bytes, write_bytes, extend_bytes (used by get_io_stats)

  • Parallel worker stats: parallel_workers_launched, parallel_workers_to_launch (used by get_database_stats)

  • Checkpointer enhancements: num_done, slru_written columns (used by get_bgwriter_stats)

Usage Examples

Claude Desktop Integration

(Recommended) Add to your Claude Desktop configuration file:

{
  "mcpServers": {
    "mcp-postgresql-ops": {
      "command": "uvx",
      "args": ["--python", "3.12", "mcp-postgresql-ops"],
      "env": {
        "POSTGRES_HOST": "127.0.0.1",
        "POSTGRES_PORT": "15432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "changeme!@34",
        "POSTGRES_DB": "ecommerce"
      }
    }
  }
}

"Show all active connections in a clear and readable html table format." Claude Desktop Integration

"Show all relationships for customers table in ecommerce database as a Mermaid diagram." Claude Desktop Integration

Installation

From PyPI (Recommended)

# Install the package
pip install mcp-postgresql-ops

# Or with uv (faster)
uv add mcp-postgresql-ops

# Verify installation
mcp-postgresql-ops --help

From Source

# Clone the repository
git clone https://github.com/call518/MCP-PostgreSQL-Ops.git
cd MCP-PostgreSQL-Ops

# Install with uv (recommended)
uv sync
uv run mcp-postgresql-ops --help

# Or with pip
pip install -e .
mcp-postgresql-ops --help

MCP Configuration

Claude Desktop Configuration

(Optional) Run with Local Source:

{
  "mcpServers": {
    "mcp-postgresql-ops": {
      "command": "uv",
      "args": ["run", "python", "-m", "mcp_postgresql_ops"],
      "env": {
        "POSTGRES_HOST": "127.0.0.1",
        "POSTGRES_PORT": "15432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "changeme!@34",
        "POSTGRES_DB": "ecommerce"
      }
    }
  }
}

Run MCP-Server as Standalon

/w Pypi and uvx (Recommended)

# Stdio mode
uvx --python 3.12 mcp-postgresql-ops \
  --type stdio

# HTTP mode
uvx --python 3.12 mcp-postgresql-ops
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000 \
  --log-level DEBUG

(Option) Configure Multiple PostgreSQL Instances

{
  "mcpServers": {
    "Postgresql-A": {
      "command": "uvx",
      "args": ["--python", "3.12", "mcp-postgresql-ops"],
      "env": {
        "POSTGRES_HOST": "a.foo.com",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "postgres",
        "POSTGRES_DB": "postgres"
      }
    },
    "Postgresql-B": {
      "command": "uvx",
      "args": ["--python", "3.12", "mcp-postgresql-ops"],
      "env": {
        "POSTGRES_HOST": "b.bar.com",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "postgres",
        "POSTGRES_DB": "postgres"
      }
    }
  }
}

/w Local Source

# Method 1: Module execution (for development, requires PYTHONPATH)
PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src
python -m mcp_postgresql_ops \
  --type stdio

# Method 2: Direct script (after uv installation in project directory)
uv run mcp-postgresql-ops \
  --type stdio

# Method 3: Installed package script (after pip/uv install)
mcp-postgresql-ops \
  --type stdio

# HTTP mode examples:
# Development mode
PYTHONPATH=/path/to/MCP-PostgreSQL-Ops/src
python -m mcp_postgresql_ops \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000 \
  --log-level DEBUG

# Production mode (after installation)
mcp-postgresql-ops \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8000 \
  --log-level DEBUG

CLI Arguments

  • --type: Transport type (stdio or streamable-http) - Default: stdio

  • --host: Host address for HTTP transport - Default: 127.0.0.1

  • --port: Port number for HTTP transport - Default: 8000

  • --auth-enable: Enable Bearer token authentication for streamable-http mode - Default: false

  • --secret-key: Secret key for Bearer token authentication (required when auth enabled)

  • --log-level: Logging level (DEBUG, INFO, WARNING, ERROR, CRITICAL) - Default: INFO

Environment Variables

Variable

Description

Default

Project Default

PYTHONPATH

Python module search path (only needed for development mode)

-

/app/src

MCP_LOG_LEVEL

Server logging verbosity (DEBUG, INFO, WARNING, ERROR)

INFO

INFO

FASTMCP_TYPE

MCP transport protocol (stdio for CLI, streamable-http for web)

stdio

streamable-http

FASTMCP_HOST

HTTP server bind address (0.0.0.0 for all interfaces)

127.0.0.1

0.0.0.0

FASTMCP_PORT

HTTP server port for MCP communication

8000

8000

REMOTE_AUTH_ENABLE

Enable Bearer token authentication for streamable-http mode (Default: false if undefined/null/empty)

false

false

REMOTE_SECRET_KEY

Secret key for Bearer token authentication (required when auth enabled)

-

your-secret-key-here

PGSQL_VERSION

PostgreSQL major version for Docker image selection

17

17

PGDATA

PostgreSQL data directory inside Docker container (Do not modify)

/var/lib/postgresql/data

/data/db

POSTGRES_HOST

PostgreSQL server hostname or IP address

127.0.0.1

host.docker.internal

POSTGRES_PORT

PostgreSQL server port number

5432

15432

POSTGRES_USER

PostgreSQL connection username (needs read permissions)

postgres

postgres

POSTGRES_PASSWORD

PostgreSQL user password (supports special characters)

changeme!@34

changeme!@34

POSTGRES_DB

Default database name for connections

testdb

ecommerce

POSTGRES_MAX_CONNECTIONS

PostgreSQL max_connections configuration parameter

200

200

DOCKER_EXTERNAL_PORT_OPENWEBUI

Host port mapping for Open WebUI container

8080

3003

DOCKER_EXTERNAL_PORT_MCP_SERVER

Host port mapping for MCP server container

8080

18003

DOCKER_EXTERNAL_PORT_MCPO_PROXY

Host port mapping for MCPO proxy container

8000

8003

DOCKER_INTERNAL_PORT_POSTGRESQL

PostgreSQL container internal port

5432

5432

Note: POSTGRES_DB serves as the default target database for operations when no specific database is specified. In Docker environments, if set to a non-default name, this database will be automatically created during initial PostgreSQL startup.

Port Configuration: The built-in PostgreSQL container uses port mapping 15432:5432 where:

  • POSTGRES_PORT=15432: External port for host access and MCP server connections

  • DOCKER_INTERNAL_PORT_POSTGRESQL=5432: Internal container port (PostgreSQL default)

  • When using external PostgreSQL servers, set POSTGRES_PORT to match your server's actual port

Prerequisites

Required PostgreSQL Extensions

For more details, see the ## Tool Compatibility Matrix

Note: Most MCP tools work without any PostgreSQL extensions. section below. Some advanced performance analysis tools require the following extensions:

-- Query performance statistics (required only for get_pg_stat_statements_top_queries)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Advanced monitoring (optional, used by get_pg_stat_monitor_recent_queries)
CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;

Quick Setup: For new PostgreSQL installations, add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Then restart PostgreSQL and run the CREATE EXTENSION commands above.

  • pg_stat_statements is required only for slow query analysis tools.

  • pg_stat_monitor is optional and used for real-time query monitoring.

  • All other tools work without these extensions.

Minimum Requirements

  • PostgreSQL 12+ (tested with PostgreSQL 17 and 18)

  • Python 3.12

  • Network access to PostgreSQL server

  • Read permissions on system catalogs

Required PostgreSQL Configuration

⚠️ Statistics Collection Settings: Some MCP tools require specific PostgreSQL configuration parameters to collect statistics. Choose one of the following configuration methods:

Tools affected by these settings:

  • get_user_functions_stats: Requires track_functions = pl or track_functions = all

  • get_table_io_stats & get_index_io_stats: More accurate timing with track_io_timing = on

  • get_database_stats: Enhanced I/O timing with track_io_timing = on

Verification: After applying any method, verify the settings:

SELECT name, setting, context FROM pg_settings WHERE name IN ('track_activities', 'track_counts', 'track_io_timing', 'track_functions') ORDER BY name;

       name       | setting |  context  
------------------+---------+-----------
 track_activities | on      | superuser
 track_counts     | on      | superuser
 track_functions  | pl      | superuser
 track_io_timing  | on      | superuser
(4 rows)

Method 1: postgresql.conf (Recommended for Self-Managed PostgreSQL)

Add the following to your postgresql.conf:

# 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:

# 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:

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

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

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

    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

    SHOW track_functions;  -- Should be 'pl' or 'all'

    Quick fix for managed services (AWS RDS, etc.):

    ALTER SYSTEM SET track_functions = 'pl';
SELECT pg_reload_conf();

  2. Missing I/O timing data: Enable timing collection

    SHOW track_io_timing;  -- Should be 'on'

    Quick fix:

    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

  1. Run compatibility check first:

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

# 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

The following test suites are available:

Suite

File

Requires Docker

Unit tests (version compatibility logic)

tests/test_version_compat.py

No

Unit tests (null-default tool argument schemas)

tests/test_tool_schema_null_defaults.py

No

Integration tests (all tools × PG 12–18)

tests/test_tools_integration.py

Yes

uv run pytest automatically starts the Docker test containers (PG 12–18), waits for them to be fully initialized, runs all tests, then tears everything down.

# Run all tests (unit + integration) — Docker is managed automatically
uv run pytest -v

# Unit tests only (no Docker or PostgreSQL needed)
uv run pytest tests/test_version_compat.py tests/test_tool_schema_null_defaults.py -v

# Tool argument schema tests only (no Docker or PostgreSQL needed)
uv run pytest tests/test_tool_schema_null_defaults.py -v

# Integration tests only
uv run pytest tests/test_tools_integration.py -v

Note: Docker must be running for integration tests. The test stack uses ports 5412–5418 (PG 12–18).

Version Compatibility Testing

The MCP server automatically adapts to PostgreSQL versions 12-18. To test across versions:

  1. Set up test databases: Different PostgreSQL versions (12, 14, 15, 16, 17, 18)

  2. Run compatibility tests: Point to each version and verify tool behavior

  3. Check feature detection: Ensure proper version detection and feature availability

  4. Verify fallback behavior: Confirm graceful degradation on older versions

Security Notes

  • All tools are read-only - no data modification capabilities

  • Sensitive information (passwords) are masked in outputs

  • No direct SQL execution - only predefined queries

  • Follows principle of least privilege

Contributing

🤝 Got ideas? Found bugs? Want to add cool features?

We're always excited to welcome new contributors! Whether you're fixing a typo, adding a new monitoring tool, or improving documentation - every contribution makes this project better.

Ways to contribute:

  • 🐛 Report issues or bugs

  • 💡 Suggest new PostgreSQL monitoring features

  • 📝 Improve documentation

  • 🚀 Submit pull requests

  • ⭐ Star the repo if you find it useful!

Pro tip: The codebase is designed to be super friendly for adding new tools. Check out the existing @mcp.tool() functions in mcp_main.py.

MCPO Swagger Docs

[MCPO Swagger URL] http://localhost:8003/postgresql-ops/docs

MCPO Swagger APIs

🔐 Security & Authentication

Bearer Token Authentication

For streamable-http mode, this MCP server supports Bearer token authentication to secure remote access. This is especially important when running the server in production environments.

Default Policy: REMOTE_AUTH_ENABLE defaults to false if undefined, null, or empty. This ensures backward compatibility and prevents startup errors when the variable is not set.

Configuration

Enable Authentication:

# In .env file
REMOTE_AUTH_ENABLE=true
REMOTE_SECRET_KEY=my-test-secret-key-12345

Or via CLI:

# Module method
python -m mcp_postgresql_ops --type streamable-http --auth-enable --secret-key my-test-secret-key-12345

# Script method
mcp-postgresql-ops --type streamable-http --auth-enable --secret-key my-test-secret-key-12345

Security Levels

  1. stdio mode (Default): Local-only access, no authentication needed

  2. streamable-http + REMOTE_AUTH_ENABLE=false: Remote access without authentication ⚠️ NOT RECOMMENDED for production

  3. streamable-http + REMOTE_AUTH_ENABLE=true: Remote access with Bearer token authentication ✅ RECOMMENDED for production

Client Configuration

When authentication is enabled, MCP clients must include the Bearer token in the Authorization header:

{
  "mcpServers": {
    "mcp-postgresql-ops": {
      "type": "streamable-http",
      "url": "http://your-server:8000/mcp",
      "headers": {
        "Authorization": "Bearer my-test-secret-key-12345"
      }
    }
  }
}

Security Best Practices

  • Always enable authentication when using streamable-http mode in production

  • Use strong, randomly generated secret keys (32+ characters recommended)

  • Use HTTPS when possible (configure reverse proxy with SSL/TLS)

  • Restrict network access using firewalls or network policies

  • Rotate secret keys regularly for enhanced security

  • Monitor access logs for unauthorized access attempts

Error Handling

When authentication fails, the server returns:

  • 401 Unauthorized for missing or invalid tokens

  • Detailed error messages in JSON format for debugging

🚀 Adding Custom Tools

This MCP server is designed for easy extensibility. Follow these 4 simple steps to add your own custom tools:

Step-by-Step Guide

1. Add Helper Functions (Optional)

Add reusable data functions to src/mcp_postgresql_ops/functions.py:

async def get_your_custom_data(target_database: str = None, limit: int = 20) -> List[Dict[str, Any]]:
    """Your custom data retrieval function."""
    try:
        # Example implementation - adapt to your PostgreSQL needs
        query = """
        SELECT 
            schemaname,
            tablename,
            attname as column_name,
            n_distinct,
            most_common_vals,
            most_common_freqs
        FROM pg_stats 
        WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
        ORDER BY schemaname, tablename, attname
        LIMIT $1
        """
        
        results = await execute_query(query, [limit], database=target_database)
        return results
        
    except Exception as e:
        logger.error(f"Failed to get custom data: {e}")
        raise

2. Create Your MCP Tool

Add your tool function to src/mcp_postgresql_ops/mcp_main.py:

@mcp.tool()
async def get_your_custom_analysis(limit: int = 50, database_name: Optional[str] = None) -> str:
    """
    [Tool Purpose]: Brief description of what your tool does
    
    [Exact Functionality]:
    - Feature 1: Data aggregation and analysis
    - Feature 2: Database monitoring and insights
    - Feature 3: Performance metrics and reporting
    
    [Required Use Cases]:
    - When user asks "your specific analysis request"
    - Your PostgreSQL-specific monitoring needs
    
    Args:
        limit: Maximum results (1-100)
        database_name: Target database name (optional, uses default if not specified)
    
    Returns:
        Formatted analysis results
    """
    try:
        # Always validate input limits
        limit = max(1, min(limit, 100))
        
        # Get your custom data
        results = await get_your_custom_data(target_database=database_name, limit=limit)
        
        if not results:
            return "No data found for custom analysis."
        
        # Format and return results
        return format_table_data(results, f"Custom Analysis Results (Top {len(results)})")
        
    except Exception as e:
        logger.error(f"Failed to get custom analysis: {e}")
        return f"Error: {str(e)}"

3. Update Imports

Add your helper function to the imports section in src/mcp_postgresql_ops/mcp_main.py (around line 30):

from .functions import (
    execute_query,
    execute_single_query,
    format_table_data,
    format_bytes,
    format_duration,
    get_server_version,
    check_extension_exists,
    get_pg_stat_statements_data,
    get_pg_stat_monitor_data,
    sanitize_connection_info,
    read_prompt_template,
    parse_prompt_sections,
    get_current_database_name,
    POSTGRES_CONFIG,
    get_your_custom_data,  # Add your new function here
)

4. Update Prompt Template (Recommended)

Add your tool description to src/mcp_postgresql_ops/prompt_template.md for better natural language recognition:

### **Your Custom Analysis Tool**

### X. **get_your_custom_analysis**
**Purpose**: Brief description of what your tool does
**Usage**: "Show me your custom analysis" or "Get custom analysis for database_name"
**Features**: Data aggregation, database monitoring, performance metrics
**Optional**: `database_name` parameter for specific database analysis
**Limit**: Results limited to 1-100 records for performance

5. Test Your Tool

# Local testing with MCP Inspector
./run-mcp-inspector-local.sh

# Or test with Docker stack
docker-compose up -d
docker-compose logs -f mcp-server

# Test with natural language queries:
# "Show me your custom analysis"
# "Get custom analysis for ecommerce database"
# "Analyze custom data with limit 25"

Important Notes

  • Multi-Database Support: All tools support the optional database_name parameter to target specific databases

  • Input Validation: Always validate limit parameters with max(1, min(limit, 100))

  • Error Handling: Return user-friendly error messages instead of raising exceptions

  • Logging: Use logger.error() for debugging while returning clean error messages to users

  • PostgreSQL Compatibility: Your custom queries should work across PostgreSQL 12-18

  • Extension Dependencies: If your tool requires specific extensions, check availability with check_extension_exists()

Advanced Patterns

For version-aware queries or extension-dependent features, see existing tools like get_pg_stat_statements_top_queries for reference patterns.

That's it! Your custom tool is ready to use with natural language queries through any MCP client.

License

Freely use, modify, and distribute under the MIT License.

⭐ Other Projects

Other MCP servers by the same author:

Available Tools

38 tools
get_active_connectionsA

[Tool Purpose]: Retrieve all active connections and session information on current PostgreSQL server

[Exact Functionality]:

  • Retrieve list of all currently active connected sessions

  • Display user, database, and client address for each connection

  • Include session status and currently executing query information

[Required Use Cases]:

  • When user requests "active connections", "current sessions", "connection status", etc.

  • When server load or performance problem diagnosis is needed

  • When checking connection status of specific users or applications

[Strictly Prohibited Use Cases]:

  • Requests for forceful connection termination or session management

  • Requests for detailed query history of specific sessions

  • Requests for connection security or authentication-related changes

Returns: Information including PID, username, database name, client address, status, and current query

Inputs: no named parameters. Returns: result

get_all_tables_statsA

[Tool Purpose]: Get comprehensive statistics for all tables (including system tables if requested)

[Exact Functionality]:

  • Show detailed access statistics for all tables in database

  • Include sequential scans, index scans, and tuple operations

  • Provide live/dead tuple estimates and maintenance history

  • Option to include system catalog tables

[Required Use Cases]:

  • When user requests "all tables stats", "complete table statistics", etc.

  • When analyzing overall table usage patterns

  • When investigating table maintenance needs across the database

  • When getting comprehensive database activity overview

[Strictly Prohibited Use Cases]:

  • Requests for table maintenance operations (VACUUM, ANALYZE)

  • Requests for statistics reset or modification

  • Requests for table optimization actions

Inputs: database_name, include_system. Returns: result

get_async_io_statusA

[Tool Purpose]: Monitor asynchronous I/O subsystem status (PostgreSQL 18+)

[Exact Functionality]:

  • Show active async I/O operations from pg_aios view

  • Display I/O method configuration and concurrency settings

  • Report async I/O file handle usage

  • Falls back to I/O configuration info on PG < 18

[Required Use Cases]:

  • When user requests "async I/O status", "AIO monitoring", "I/O subsystem"

  • When investigating I/O performance and concurrency

  • When checking async I/O configuration

[Strictly Prohibited Use Cases]:

  • Requests for I/O configuration changes

  • Requests for storage modifications

Inputs: database_name. Returns: result

get_autovacuum_activityA

[Tool Purpose]: Monitor recent autovacuum and autoanalyze activity patterns and execution history

[Exact Functionality]:

  • Track recent autovacuum and autoanalyze execution patterns

  • Analyze autovacuum frequency and timing intervals

  • Show tables with most/least autovacuum activity

  • Calculate average time between autovacuum executions

  • Identify tables with irregular autovacuum patterns

[Required Use Cases]:

  • When user requests "autovacuum activity", "autovacuum history", "vacuum patterns", etc.

  • When monitoring autovacuum performance and effectiveness

  • When troubleshooting autovacuum scheduling issues

  • When analyzing autovacuum workload distribution

[Strictly Prohibited Use Cases]:

  • Requests for autovacuum process control or restart

  • Requests for autovacuum configuration modifications

  • Requests for manual vacuum scheduling

Inputs: limit, hours_back, schema_name, database_name. Returns: result

get_autovacuum_statusA

[Tool Purpose]: Analyze autovacuum configuration and current maintenance status for tables

[Exact Functionality]:

  • Analyze autovacuum trigger conditions based on dead tuple thresholds

  • Calculate current dead tuple ratios vs autovacuum trigger points

  • Show autovacuum configuration settings per table

  • Identify tables requiring immediate autovacuum attention

  • Estimate next autovacuum execution likelihood

[Required Use Cases]:

  • When user requests "autovacuum status", "autovacuum configuration", "vacuum trigger analysis", etc.

  • When planning autovacuum optimization and tuning

  • When troubleshooting autovacuum performance issues

  • When identifying tables with autovacuum problems

[Strictly Prohibited Use Cases]:

  • Requests for autovacuum configuration changes

  • Requests for manual VACUUM execution

  • Requests for autovacuum process restart or control

Inputs: limit, schema_name, database_name, table_pattern. Returns: result

get_bgwriter_statsA

[Tool Purpose]: Analyze background writer and checkpoint performance statistics with version compatibility

[Exact Functionality]:

  • Show checkpoint execution statistics (timed vs requested)

  • Display checkpoint timing information (write and sync times)

  • Provide buffer writing statistics by different processes

  • Analyze background writer performance and efficiency

  • Automatically adapts to PostgreSQL version (17+ uses separate checkpointer view)

[Required Use Cases]:

  • When user requests "checkpoint stats", "bgwriter performance", "buffer stats", etc.

  • When analyzing I/O performance and checkpoint impact

  • When investigating background writer efficiency

  • When troubleshooting checkpoint-related performance issues

[Strictly Prohibited Use Cases]:

  • Requests for checkpoint execution or configuration changes

  • Requests for background writer parameter modifications

  • Requests for statistics reset

Returns: Background writer and checkpoint performance statistics with version-appropriate data

Inputs: no named parameters. Returns: result

get_current_database_infoA

[Tool Purpose]: Get information about the current database connection

[Exact Functionality]:

  • Show the name of the currently connected database

  • Display database-specific information like encoding, locale, and size

  • Provide connection context for clarity in multi-database environments

[Required Use Cases]:

  • When user asks "what database am I connected to?", "current database", etc.

  • When clarifying database context for analysis operations

  • When troubleshooting connection issues or confirming target database

[Strictly Prohibited Use Cases]:

  • Requests for database structure changes or creation/deletion

  • Requests for user authentication or permission changes

  • Requests for configuration modifications

Inputs: database_name. Returns: result

get_database_bloat_overviewA

[Tool Purpose]: Provide database-wide bloat overview and summary statistics

[Exact Functionality]:

  • Summarize bloat statistics across all schemas

  • Identify schemas and tables with highest bloat ratios

  • Calculate total estimated bloat size per schema

  • Show aggregate dead tuple counts and maintenance status

[Required Use Cases]:

  • When user requests "database bloat overview", "bloat summary", etc.

  • When getting high-level view of database storage efficiency

  • When planning database maintenance activities

  • When investigating overall database performance issues

[Strictly Prohibited Use Cases]:

  • Requests for automatic maintenance operations

  • Requests for bloat cleanup or removal

  • Requests for schema or database restructuring

Inputs: limit, database_name. Returns: result

get_database_conflicts_statsA

[Tool Purpose]: Analyze query conflicts in standby/replica database environments

[Exact Functionality]:

  • Show conflict statistics for standby servers (only relevant on replicas)

  • Display conflicts by type (tablespace, lock, snapshot, bufferpin, deadlock)

  • Help diagnose replication-related performance issues

  • Provide conflict resolution statistics

[Required Use Cases]:

  • When user requests "replication conflicts", "standby conflicts", etc.

  • When analyzing replica server performance issues

  • When troubleshooting replication lag or conflicts

  • When monitoring standby server health

[Strictly Prohibited Use Cases]:

  • Requests for conflict resolution actions

  • Requests for replication configuration changes

  • Requests for statistics reset

Inputs: database_name. Returns: result

get_database_listA

[Tool Purpose]: Retrieve list of all databases and their basic information on PostgreSQL server

[Exact Functionality]:

  • Retrieve list of all databases on the server

  • Display owner, encoding, and size information for each database

  • Include database connection limit information

[Required Use Cases]:

  • When user requests "database list", "DB list", "database info", etc.

  • When need to check what databases exist on the server

  • When database size or owner information is needed

[Strictly Prohibited Use Cases]:

  • Requests for tables or schemas inside specific databases

  • Requests for database creation or deletion

  • Requests related to user permissions or security

Returns: Table-format information including database name, owner, encoding, size, and connection limit

Inputs: no named parameters. Returns: result

get_database_schema_infoA

[Tool Purpose]: Retrieve detailed information about database schemas (namespaces) and their contents

[Exact Functionality]:

  • Show all schemas in a database with their owners and permissions

  • Display schema-level statistics including table count and total size

  • List all objects (tables, views, functions) within specific schema

  • Show schema access privileges and usage patterns

[Required Use Cases]:

  • When user requests "database schema info", "schema overview", "namespace structure", etc.

  • When analyzing database organization and schema-level permissions

  • When exploring multi-schema database architecture

[Strictly Prohibited Use Cases]:

  • Requests for actual data inside tables

  • Requests for schema structure changes or DDL operations

  • Requests for individual table details (use get_table_schema_info for that)

Inputs: database_name (required), schema_name. Returns: result

get_database_size_infoA

[Tool Purpose]: Analyze size information and storage usage status of all databases in PostgreSQL server

[Exact Functionality]:

  • Retrieve disk usage for each database

  • Analyze overall server storage usage status

  • Provide database list sorted by size

[Required Use Cases]:

  • When user requests "database size", "disk usage", "storage space", etc.

  • When capacity management or cleanup is needed

  • When resource usage status by database needs to be identified

[Strictly Prohibited Use Cases]:

  • Requests for data deletion or cleanup operations

  • Requests for storage configuration changes

  • Requests related to backup or restore

Returns: Table-format information with database names and size information sorted by size

Inputs: no named parameters. Returns: result

get_database_statsA

[Tool Purpose]: Get comprehensive database-wide statistics and performance metrics

[Exact Functionality]:

  • Show database-wide transaction statistics (commits, rollbacks)

  • Display block I/O statistics (disk reads vs buffer hits)

  • Provide tuple operation statistics (returned, fetched, inserted, updated, deleted)

  • Show temporary file usage and deadlock counts

  • Include checksum failure information and I/O timing data

[Required Use Cases]:

  • When user requests "database statistics", "database performance", "transaction stats", etc.

  • When analyzing overall database performance and activity

  • When investigating I/O performance or buffer cache efficiency

  • When checking for deadlocks or temporary file usage

[Strictly Prohibited Use Cases]:

  • Requests for statistics reset or modification

  • Requests for database configuration changes

  • Requests for performance tuning actions

Returns: Comprehensive database statistics including transactions, I/O, tuples, and performance metrics

Inputs: no named parameters. Returns: result

get_index_io_statsA

[Tool Purpose]: Analyze I/O performance statistics for indexes (disk reads vs buffer cache hits)

[Exact Functionality]:

  • Show index-level I/O statistics and buffer hit ratios

  • Identify indexes with poor buffer cache performance

  • Provide detailed I/O performance metrics per index

  • Help optimize index and buffer cache usage

[Required Use Cases]:

  • When user requests "index I/O stats", "index buffer performance", etc.

  • When analyzing index-level I/O performance

  • When identifying indexes causing excessive disk I/O

  • When optimizing index buffer cache efficiency

[Strictly Prohibited Use Cases]:

  • Requests for index optimization actions

  • Requests for buffer cache configuration changes

  • Requests for statistics reset

Inputs: schema_name, database_name. Returns: result

get_index_usage_statsA

[Tool Purpose]: Analyze usage rate and performance statistics of all indexes in database

[Exact Functionality]:

  • Analyze usage frequency and efficiency of all indexes

  • Identify unused indexes

  • Provide scan count and tuple return statistics per index

[Required Use Cases]:

  • When user requests "index usage rate", "index performance", "unnecessary indexes", etc.

  • When database performance optimization is needed

  • When index cleanup or reorganization is required

[Strictly Prohibited Use Cases]:

  • Requests for index creation or deletion

  • Requests for index reorganization or REINDEX execution

  • Requests for statistics reset

Inputs: database_name. Returns: result

get_io_statsA

[Tool Purpose]: Analyze comprehensive I/O statistics across all database operations with version compatibility

[Exact Functionality]:

  • PostgreSQL 16+: Shows detailed I/O statistics from pg_stat_io (reads, writes, hits, timing)

  • PostgreSQL 12-15: Falls back to pg_statio_* views with basic I/O information

  • Provides buffer cache efficiency analysis and I/O timing when available

  • Identifies I/O patterns and performance bottlenecks

[Required Use Cases]:

  • When user requests "I/O stats", "I/O performance", "buffer cache analysis", etc.

  • When analyzing storage performance and buffer efficiency

  • When identifying I/O bottlenecks across different backend types

  • When comparing I/O patterns between relation types

[Strictly Prohibited Use Cases]:

  • Requests for I/O configuration changes or buffer tuning

  • Requests for storage or filesystem modifications

  • Requests for I/O statistics reset

Inputs: limit, database_name. Returns: result

get_lock_monitoringA

[Tool Purpose]: Monitor current locks and potential deadlocks in PostgreSQL

[Exact Functionality]:

  • List all current locks held and waited for by sessions

  • Show blocked and blocking sessions, lock types, and wait status

  • Help diagnose lock contention and deadlock risk

  • Filter results by granted status, state, mode, lock type, or username

[Required Use Cases]:

  • When user requests "lock monitoring", "deadlock check", "blocked sessions", etc.

  • When diagnosing performance issues due to locking

  • When checking for blocked or waiting queries

  • When filtering specific types of locks or users

[Strictly Prohibited Use Cases]:

  • Requests for killing sessions or force-unlocking

  • Requests for lock configuration changes

  • Requests for historical lock data (only current state is shown)

Inputs: mode, state, granted, locktype, +2 more. Returns: result

get_per_backend_io_statsA

[Tool Purpose]: Analyze per-backend I/O and WAL statistics (PostgreSQL 18+)

Note: Uses pg_stat_get_backend_io() and pg_stat_get_backend_wal() which are new in PG 18.

[Exact Functionality]:

  • Show I/O statistics broken down by individual backend process

  • Display per-backend WAL generation statistics

  • Identify backends with highest I/O activity

  • Falls back to version notice on PG < 18

[Required Use Cases]:

  • When user requests "per-backend I/O", "backend I/O stats", "which backend is doing most I/O"

  • When diagnosing I/O hotspots at the process level

  • When analyzing per-connection I/O patterns

[Strictly Prohibited Use Cases]:

  • Requests for backend termination

  • Requests for I/O configuration changes

Inputs: limit, database_name. Returns: result

get_pg_stat_monitor_recent_queriesA

[Tool Purpose]: Analyze recently executed queries and detailed monitoring information using pg_stat_monitor extension

[Exact Functionality]:

  • Retrieve detailed performance information of recently executed queries

  • Display client IP and time bucket information by execution period

  • Provide more detailed monitoring data than pg_stat_statements

[Required Use Cases]:

  • When user requests "recent queries", "detailed monitoring", "pg_stat_monitor", etc.

  • When real-time query performance monitoring is needed

  • When client-specific or time-based query analysis is required

[Strictly Prohibited Use Cases]:

  • When pg_stat_monitor extension is not installed

  • Requests for query execution or data modification

  • Requests for monitoring configuration changes or data reset

Inputs: limit, database_name. Returns: result

get_pg_stat_statements_top_queriesA

[Tool Purpose]: Analyze top queries that consumed the most time using pg_stat_statements extension

[Exact Functionality]:

  • Retrieve top query list based on total execution time

  • Display call count, average execution time, and cache hit rate for each query

  • Support identification of queries requiring performance optimization

[Required Use Cases]:

  • When user requests "slow queries", "performance analysis", "top queries", etc.

  • When database performance optimization is needed

  • When query performance monitoring or tuning is required

[Strictly Prohibited Use Cases]:

  • When pg_stat_statements extension is not installed

  • Requests for query execution or data modification

  • Requests for statistics data reset or configuration changes

Inputs: limit, database_name. Returns: result

get_postgresql_configA

[Tool Purpose]: Retrieve and analyze PostgreSQL server configuration parameter values

[Exact Functionality]:

  • Retrieve all PostgreSQL configuration parameters (when config_name is not specified)

  • Retrieve current value and description of specific configuration parameter

  • Filter configurations by text pattern (when filter_text is specified)

  • Display whether configuration can be changed and if restart is required

[Required Use Cases]:

  • When user requests "PostgreSQL config", "config", "parameters", etc.

  • When checking specific configuration values is needed

  • When searching for configurations containing specific text

  • When configuration status identification is needed for performance tuning

[Strictly Prohibited Use Cases]:

  • Requests for configuration value changes or modifications

  • Requests for PostgreSQL restart or reload

  • Requests for system-level configuration changes

Inputs: config_name, filter_text. Returns: result

get_prompt_templateC

Returns the MCP prompt template (full, headings, or specific section). Args: section: Section number or keyword (optional) mode: 'full', 'headings', or None (optional)

Inputs: mode, section. Returns: result

get_replication_statusA

[Tool Purpose]: Monitor PostgreSQL replication status and statistics

[Exact Functionality]:

  • Show current replication connections and their status

  • Display replication lag information for standbys

  • Monitor WAL sender and receiver processes

  • Provide replication slot information and statistics

[Required Use Cases]:

  • When user requests "replication status", "standby lag", "replication monitoring", etc.

  • When diagnosing replication issues or performance problems

  • When checking replication slot usage and lag

[Strictly Prohibited Use Cases]:

  • Requests for replication configuration changes

  • Requests for replication slot creation or deletion

  • Requests for failover or switchover operations

Returns: Replication status including connections, lag information, slots, and statistics

Inputs: no named parameters. Returns: result

get_running_vacuum_operationsA

[Tool Purpose]: Monitor currently running VACUUM and ANALYZE operations in real-time

[Exact Functionality]:

  • Show all currently active VACUUM, ANALYZE, and REINDEX operations

  • Display operation progress, elapsed time, and process details

  • Identify blocking or long-running maintenance operations

  • Provide process IDs for operation tracking

[Required Use Cases]:

  • When user requests "running VACUUM", "active maintenance", "current VACUUM status", etc.

  • When troubleshooting slow or stuck VACUUM operations

  • When monitoring maintenance operation progress

  • When identifying maintenance operations that may be affecting performance

[Strictly Prohibited Use Cases]:

  • Requests for terminating or controlling VACUUM processes

  • Requests for starting new VACUUM operations

  • Requests for changing VACUUM parameters or configuration

Inputs: database_name. Returns: result

get_server_infoA

[Tool Purpose]: Check basic information and connection status of PostgreSQL server

[Exact Functionality]:

  • Retrieve PostgreSQL server version information

  • Display connection settings (with password masking)

  • Verify server accessibility

  • Check installation status of extensions (pg_stat_statements, pg_stat_monitor)

[Required Use Cases]:

  • When user requests "server info", "PostgreSQL status", "connection check", etc.

  • When basic database server information is needed

  • When preliminary check is needed before using monitoring tools

[Strictly Prohibited Use Cases]:

  • Requests for specific data or table information

  • Requests for performance statistics or monitoring data

  • Requests for configuration changes or administrative tasks

Returns: Comprehensive information including server version, connection info, and extension status

Inputs: no named parameters. Returns: result

get_table_bloat_analysisA

[Tool Purpose]: Analyze table bloat based on dead tuple statistics and size information

[Exact Functionality]:

  • Calculate bloat ratio based on dead tuples vs total tuples

  • Estimate bloat size in bytes and human-readable format

  • Show last VACUUM/AUTOVACUUM timestamps for maintenance tracking

  • Identify tables requiring VACUUM maintenance

  • Filter tables by name pattern using SQL LIKE or ILIKE matching

  • Sort results by bloat severity (dead tuple ratio and count)

[Required Use Cases]:

  • When user requests "table bloat", "bloat analysis", "dead tuples", etc.

  • When identifying tables that need VACUUM maintenance

  • When investigating database storage efficiency and space usage

  • When troubleshooting performance issues related to table bloat

  • When analyzing specific table groups (e.g., tables with "user", "log", "temp" in names)

[Strictly Prohibited Use Cases]:

  • Requests for automatic VACUUM execution

  • Requests for bloat removal or cleanup operations

  • Requests for table restructuring or data modification

Inputs: limit, schema_name, database_name, table_pattern, +1 more. Returns: result

get_table_io_statsA

[Tool Purpose]: Analyze I/O performance statistics for tables (disk reads vs buffer cache hits)

[Exact Functionality]:

  • Show heap, index, and TOAST table I/O statistics

  • Calculate buffer hit ratios for performance analysis

  • Identify tables with poor buffer cache performance

  • Provide detailed I/O breakdown by table component

[Required Use Cases]:

  • When user requests "table I/O stats", "buffer performance", "disk vs cache", etc.

  • When analyzing table-level I/O performance

  • When identifying tables causing excessive disk I/O

  • When optimizing buffer cache efficiency

[Strictly Prohibited Use Cases]:

  • Requests for I/O optimization actions

  • Requests for buffer cache configuration changes

  • Requests for statistics reset

Inputs: schema_name, database_name. Returns: result

get_table_listA

[Tool Purpose]: Retrieve list of all tables and their information from specified database (or current DB)

[Exact Functionality]:

  • Retrieve list of all tables in specified database

  • Display schema, owner, and size information for each table

  • Distinguish table types (regular tables, views, etc.)

[Required Use Cases]:

  • When user requests "table list", "table listing", "schema info", etc.

  • When need to understand structure of specific database

  • When table size or owner information is needed

[Strictly Prohibited Use Cases]:

  • Requests for data inside tables

  • Requests for table structure changes or creation/deletion

  • Requests for detailed column information of specific tables

Inputs: database_name. Returns: result

get_table_relationshipsA

[Tool Purpose]: Analyze table relationships including foreign keys, dependencies, and inheritance

[Exact Functionality]:

  • Show foreign key relationships (inbound and outbound)

  • Display view dependencies and table references

  • Analyze inheritance and partition relationships

  • Identify orphaned tables and relationship patterns

[Required Use Cases]:

  • When user requests "table relationships", "foreign keys", "dependencies", etc.

  • When analyzing database schema design and data model

  • When planning data migration or schema changes

[Strictly Prohibited Use Cases]:

  • Requests for actual data inside tables

  • Requests for relationship modifications or DDL operations

  • Requests for performance statistics (use other tools for that)

Inputs: database_name (required), table_name, schema_name, relationship_type. Returns: result

get_table_schema_infoA

[Tool Purpose]: Retrieve detailed schema information for specific table or all tables in a database

[Exact Functionality]:

  • Retrieve detailed column information including data types, constraints, defaults

  • Display primary keys, foreign keys, indexes, and other table constraints

  • Show table-level metadata such as size, row count estimates

[Required Use Cases]:

  • When user requests "table schema", "column info", "table structure", etc.

  • When detailed table design information is needed for development

  • When analyzing database structure and relationships

[Strictly Prohibited Use Cases]:

  • Requests for actual data inside tables

  • Requests for table structure changes or DDL operations

  • Requests for performance statistics (use other tools for that)

Inputs: database_name (required), table_name, schema_name. Returns: result

get_table_size_infoA

[Tool Purpose]: Analyze size information and index usage of all tables in specified schema

[Exact Functionality]:

  • Retrieve size information of all tables within schema

  • Analyze index size and total size per table

  • Provide table list sorted by size

[Required Use Cases]:

  • When user requests "table size", "schema capacity", "index usage", etc.

  • When storage analysis of specific schema is needed

  • When resource usage status per table needs to be identified

[Strictly Prohibited Use Cases]:

  • Requests for table data deletion or cleanup operations

  • Requests for index creation or deletion

  • Requests for table structure changes

Inputs: schema_name, database_name. Returns: result

get_user_functions_statsA

[Tool Purpose]: Analyze performance statistics for user-defined functions

[Exact Functionality]:

  • Show execution count and timing statistics for user functions

  • Calculate average execution time per function call

  • Identify performance bottlenecks in user-defined functions

  • Provide total and self execution time breakdown

[Required Use Cases]:

  • When user requests "function stats", "function performance", etc.

  • When analyzing user-defined function performance

  • When identifying slow or frequently called functions

  • When optimizing application function usage

[Strictly Prohibited Use Cases]:

  • Requests for function modification or optimization

  • Requests for statistics reset

  • Requests for function execution or testing

Inputs: database_name. Returns: result

get_user_listA

[Tool Purpose]: Retrieve list of all user accounts and permission information on PostgreSQL server

[Exact Functionality]:

  • Retrieve list of all database user accounts

  • Display permission information for each user (superuser, database creation rights, etc.)

  • Include account creation date and expiration date information

[Required Use Cases]:

  • When user requests "user list", "account info", "permission check", etc.

  • When user permission management or security inspection is needed

  • When account status overview is needed

[Strictly Prohibited Use Cases]:

  • Requests for user password information

  • Requests for user creation, deletion, or permission changes

  • Requests for specific user sessions or activity history

Returns: Table-format information including username, superuser status, permissions, and account status

Inputs: no named parameters. Returns: result

get_vacuum_analyze_statsA

[Tool Purpose]: Analyze VACUUM and ANALYZE execution history and statistics per table

[Exact Functionality]:

  • Retrieve last VACUUM/ANALYZE execution time for each table

  • Provide Auto VACUUM/ANALYZE execution count statistics

  • Analyze table activity with tuple insert/update/delete statistics

[Required Use Cases]:

  • When user requests "VACUUM status", "ANALYZE history", "table statistics", etc.

  • When database maintenance status overview is needed

  • When performance issues or statistics update status verification is required

[Strictly Prohibited Use Cases]:

  • Requests for VACUUM or ANALYZE execution

  • Requests for Auto VACUUM configuration changes

  • Requests for forced statistics update

Inputs: database_name. Returns: result

get_vacuum_effectiveness_analysisA

[Tool Purpose]: Analyze VACUUM effectiveness and maintenance patterns using existing statistics

[Exact Functionality]:

  • Compare manual VACUUM vs autovacuum effectiveness patterns

  • Analyze VACUUM frequency vs table activity (DML operations)

  • Identify tables with suboptimal VACUUM patterns

  • Calculate maintenance efficiency ratios without performance impact

  • Show VACUUM coverage analysis across all tables

[Required Use Cases]:

  • When user requests "VACUUM effectiveness", "maintenance efficiency", "VACUUM analysis", etc.

  • When planning manual VACUUM schedules or autovacuum tuning

  • When identifying tables with poor maintenance patterns

  • When analyzing overall database maintenance health

[Strictly Prohibited Use Cases]:

  • Requests for VACUUM execution or scheduling

  • Requests for autovacuum configuration changes

  • Requests for maintenance operation control

Inputs: limit, schema_name, database_name. Returns: result

get_wait_eventsA

[Tool Purpose]: List available wait event types and their descriptions (PostgreSQL 17+)

[Exact Functionality]:

  • Show all wait event types with human-readable descriptions from pg_wait_events catalog

  • Correlate with current active sessions waiting on events

  • Filter by specific wait event type

  • Falls back to basic pg_stat_activity wait info on PG < 17

[Required Use Cases]:

  • When user requests "wait events", "wait event descriptions", "what are sessions waiting on"

  • When diagnosing session waits and understanding wait event meanings

  • When analyzing wait patterns across the database

[Strictly Prohibited Use Cases]:

  • Requests for session termination or wait resolution

  • Requests for configuration changes

Inputs: database_name, wait_event_type. Returns: result

get_wal_statusA

[Tool Purpose]: Monitor WAL (Write Ahead Log) status and statistics

[Exact Functionality]:

  • Show current WAL location and LSN information

  • Display WAL file generation rate and size statistics

  • Monitor WAL archiving status and lag

  • Provide WAL-related configuration and activity metrics

[Required Use Cases]:

  • When user requests "WAL status", "WAL monitoring", "log shipping status", etc.

  • When diagnosing replication lag or WAL archiving issues

  • When monitoring database write activity and WAL generation

[Strictly Prohibited Use Cases]:

  • Requests for WAL configuration changes

  • Requests for manual WAL switching or archiving

  • Requests for WAL file manipulation or cleanup

Returns: WAL status information including current LSN, WAL files, archiving status, and statistics

Inputs: no named parameters. Returns: result

get_wal_summarizer_statusA

[Tool Purpose]: Monitor WAL summarizer status for incremental backup support (PostgreSQL 17+)

[Exact Functionality]:

  • Show WAL summarizer process state and progress

  • Display available WAL summaries for incremental backups

  • Report summarize_wal configuration status

  • Falls back to version notice on PG < 17

[Required Use Cases]:

  • When user requests "WAL summarizer", "incremental backup status", "WAL summary"

  • When checking incremental backup readiness

  • When monitoring WAL summarization progress

[Strictly Prohibited Use Cases]:

  • Requests for WAL summarizer configuration changes

  • Requests for backup execution

Inputs: database_name. Returns: result

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    Deploy Server
    One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

    TDQS

    A3.5/5.0

    Scored across 38 tools

    Disambiguation4/5

    Most tools are cleanly scoped by object+scope (table vs index vs database vs per-backend I/O; table vs database bloat), and the detailed required/prohibited use cases help agents choose correctly. However, the five vacuum tools (get_vacuum_analyze_stats, get_vacuum_effectiveness_analysis, get_autovacuum_status, get_autovacuum_activity, get_running_vacuum_operations) overlap enough that misselection is likely for 'vacuum status'-style requests.

    Naming Consistency4/5

    Nearly all 37 monitoring tools follow a consistent get_<noun>_<qualifier> snake_case pattern that is easy to predict. The lone outlier get_prompt_template breaks both the prefix and the domain convention, and a few names are verbose but still readable.

    Tool Count2/5

    At 38 tools the surface is well beyond a comfortable range for a monitoring server and includes a crowded maintenance cluster that could be consolidated into a single parameterized tool. Several version-specific tools (async I/O, per-backend I/O, wait events, WAL summarizer) further inflate the count for narrow PG 17/18 use cases.

    Completeness4/5

    For a read-only monitoring server the coverage is strong: schema objects, tables, indexes, functions, bloat, vacuum/autovacuum, I/O, WAL, replication, locks, connections, users, and configuration are all reachable. Minor gaps remain, such as no index listing tool and no ability to inspect a query plan or run/diagnose an actual query, and get_prompt_template is unrelated dead weight.

    Maintenance

    ActivityActive
    ResponsivenessOutdated data

    Resources

    Related MCP Connectors

    Related MCP Servers

    • MCP-PostgreSQL-Ops

      tmitsotakis66
      F
      license
      Not graded
      quality
      A
      maintenance
      Enables AI assistants to inspect and monitor PostgreSQL databases using natural language, covering performance analysis, bloat detection, lock/deadlock monitoring, autovacuum, schema inspection, and maintenance recommendations across PostgreSQL 12–18 without requiring extensions.
      -

    • MCP-PostgreSQL-Ops

      IsabelaRiccardi
      F
      license
      Not graded
      quality
      A
      maintenance
      Enables natural-language monitoring and administration of PostgreSQL databases, covering performance analysis, bloat detection, lock and deadlock monitoring, autovacuum intelligence, and schema inspection. It works with PostgreSQL 12-18 and can run without extensions, with optional extensions for advanced query analytics.
      -

    • MCP-PostgreSQL-Ops

      Ngoc7541
      F
      license
      Not graded
      quality
      A
      maintenance
      Enables AI assistants to perform PostgreSQL database operations, monitoring, and management through natural language, including performance analysis, bloat detection, lock/deadlock monitoring, autovacuum and schema inspection, and maintenance recommendations across PostgreSQL 12-18.
      -

    • MCP-PostgreSQL-Ops

      diegororato5559
      F
      license
      Not graded
      quality
      A
      maintenance
      Enables AI assistants to monitor and administer PostgreSQL 12-18 databases through natural language, covering performance and slow-query analysis, table bloat detection, connection and lock monitoring, autovacuum and maintenance recommendations, and schema discovery. All operations are read-only and work with regular user permissions, requiring no extensions unless optional query-analytics features are desired.
      -

    Appeared in Searches