MCP-PostgreSQL-Ops copilot-instructions.md

MCP PostgreSQL Operations Server - AI Coding Agent Instructions

Architecture Overview

This is a Model Context Protocol (MCP) server built with FastMCP that provides PostgreSQL database monitoring and operations through natural language queries. The server acts as a safe, read-only bridge between AI assistants and PostgreSQL databases.

Core Components

• mcp_main.py: Main MCP server with 24+ @mcp.tool() decorated functions
• functions.py: Database connection layer using asyncpg with multi-database support
• version_compat.py: PostgreSQL 12-17 version detection and adaptive feature handling
• prompt_template.md: Comprehensive prompt definitions loaded via @mcp.prompt() decorators
• Docker stack: PostgreSQL + MCP server + MCPO proxy + Open WebUI integration

Key Patterns

Multi-Database Architecture: All tools accept optional database_name parameter to target specific databases while maintaining a default connection database from POSTGRES_DB env var.

Extension Dependencies: Core functionality requires pg_stat_statements extension; pg_stat_monitor is optional. Always check extension availability with check_extension_exists() before using related tools.

Version-Aware Tools: Use version_compat.py for PostgreSQL 12-17 compatibility. Tools auto-adapt features based on detected version. PostgreSQL 18 is beta and not yet supported - will be added once stable.

Tool Structure: Each MCP tool follows this pattern:

@mcp.tool()
async def get_something(limit: int = 20, database_name: str = None) -> str:
    """Detailed docstring with [Tool Purpose], [Exact Functionality], [Required Use Cases], [Strictly Prohibited Use Cases]"""
    try:
        # Validate inputs (limit constraints: max 1-100)
        # Check extension dependencies if needed
        # Execute queries via functions.py
        # Return formatted table data
    except Exception as e:
        logger.error(f"Failed to...: {e}")
        return f"Error: {str(e)}"