Implementing Custom MCP Tools in the MCP-PostgreSQL-Ops Framework: A Complete Guide

To implement custom MCP tools in MCP-PostgreSQL-Ops, create an async function decorated with @mcp.tool() in mcp_main.py, use helper functions from functions.py for database operations, and register the tool in prompt_template.md for LLM discovery.

The MCP-PostgreSQL-Ops framework provides a FastMCP-based server for PostgreSQL monitoring and operations. Implementing custom MCP tools allows you to extend the server's capabilities with organization-specific monitoring queries while maintaining the framework's read-only safety guarantees and version-aware SQL generation.

Understanding the MCP-PostgreSQL-Ops Architecture

Before implementing custom MCP tools, understand the core components that handle tool registration and execution.

Core Architectural Components

The framework relies on several key files:

• src/mcp_postgresql_ops/mcp_main.py: Central registry where all tools are defined and decorated with @mcp.tool().
• src/mcp_postgresql_ops/functions.py: Database connection helpers (get_db_connection, execute_query, execute_single_query) and formatting utilities (format_table_data, format_bytes).
• src/mcp_postgresql_ops/version_compat.py: Version-aware query builders (PostgreSQLVersion, VersionAwareQueries) that adapt SQL to different PostgreSQL versions.
• src/mcp_postgresql_ops/prompt_template.md: Human-readable catalog of available tools shown to the LLM for discovery.
• pyproject.toml: Declares package dependencies including fastmcp and asyncpg.

Step-by-Step Process for Implementing Custom MCP Tools

Follow this workflow to add new monitoring capabilities to the framework.

1. Define the Tool Purpose and Signature

Determine what PostgreSQL metrics your tool will expose. Define an async function with primitive parameters (strings, ints, bools) that can be supplied from LLM prompts.

2. Implement the Tool in mcp_main.py

Add your async function to src/mcp_postgresql_ops/mcp_main.py and decorate it with @mcp.tool():

@mcp.tool()
async def get_large_tables(min_size_mb: int = 100, database_name: str = None) -> str:
    """
    [Tool Purpose]: List tables whose total size exceeds *min_size_mb* megabytes.
    """
    query = """
        SELECT
            schemaname,
            tablename,
            pg_total_relation_size(format('%I.%I', schemaname, tablename)) / 1024 / 1024 AS size_mb
        FROM pg_catalog.pg_tables
        WHERE pg_total_relation_size(format('%I.%I', schemaname, tablename)) >= $1 * 1024 * 1024
        ORDER BY size_mb DESC
    """
    rows = await execute_query(query, [min_size_mb], database=database_name)
    return format_table_data(rows, f"Tables > {min_size_mb} MB")

Key implementation details:

• Use execute_query from functions.py so connection handling and logging remain consistent.
• Return str; FastMCP expects a string payload for LLM consumption.
• Keep the implementation read-only — execute only SELECT statements.

3. Handle Version Compatibility

For features requiring specific PostgreSQL versions, use helpers from version_compat.py:

from .version_compat import PostgreSQLVersion, get_postgresql_version

@mcp.tool()
async def get_replication_stats(database_name: str = None) -> str:
    pg_version = await get_postgresql_version()
    if not pg_version.is_modern:
        return "Error: This tool requires PostgreSQL 12 or newer."
    # Proceed with version-specific query...

4. Update the Prompt Template

Register the tool in src/mcp_postgresql_ops/prompt_template.md so LLMs can discover it:

34. **get_large_tables**: List tables larger than a size threshold (e.g., `get_large_tables(min_size_mb=500)`).

5. Test the Implementation

Start the server to verify tool registration:

python -m mcp_postgresql_ops

Look for the log message "Registering tool: get_large_tables" to confirm FastMCP has loaded your custom tool.

Best Practices for Custom MCP Tool Development

Follow these guidelines to maintain framework integrity when implementing custom MCP tools:

• Maintain read-only semantics: Custom tools must execute only SELECT statements. Never perform DDL or DML operations to preserve safety guarantees when LLMs invoke these tools.
• Use framework helpers: Always route database calls through execute_query or execute_single_query from functions.py to ensure consistent connection pooling, error handling, and logging.
• Validate parameters: FastMCP converts parameters from strings; implement early validation and return clear error messages for out-of-range values.
• Respect version compatibility: Use PostgreSQLVersion checks or VersionAwareQueries when accessing features unavailable in older PostgreSQL releases.
• Optimize performance: Add LIMIT clauses and avoid full-table scans unless necessary; remember that these tools may be invoked frequently by automated LLM workflows.
• Document thoroughly: Include inline docstrings with [Tool Purpose] tags and update prompt_template.md so the LLM understands when to invoke your tool.

Complete Code Example: Building a Custom Monitoring Tool

Here is a production-ready example implementing a version-aware table size inspector:

# src/mcp_postgresql_ops/mcp_main.py

from .functions import execute_query, format_table_data
from .version_compat import PostgreSQLVersion, get_postgresql_version

@mcp.tool()
async def get_large_tables(min_size_mb: int = 100, database_name: str = None) -> str:
    """
    [Tool Purpose]: List tables whose total size exceeds *min_size_mb* megabytes.
    """
    # Ensure the server supports pg_total_relation_size (PostgreSQL 12+)

    pg_version: PostgreSQLVersion = await get_postgresql_version()
    if not pg_version.is_modern:
        return "Error: This tool requires PostgreSQL 12 or newer."

    query = """
        SELECT
            schemaname,
            tablename,
            pg_total_relation_size(format('%I.%I', schemaname, tablename)) / 1024 / 1024 AS size_mb
        FROM pg_catalog.pg_tables
        WHERE pg_total_relation_size(format('%I.%I', schemaname, tablename)) >= $1 * 1024 * 1024
        ORDER BY size_mb DESC
    """
    rows = await execute_query(query, [min_size_mb], database=database_name)
    return format_table_data(rows, f"Tables > {min_size_mb} MB")

After adding this code and updating prompt_template.md, the tool becomes available to any LLM client connected to your MCP-PostgreSQL-Ops server.

Summary

• Implementing custom MCP tools requires creating async functions decorated with @mcp.tool() in src/mcp_postgresql_ops/mcp_main.py.
• Use helper functions from src/mcp_postgresql_ops/functions.py (execute_query, format_table_data) to maintain consistent database access and formatting.
• Respect read-only semantics by executing only SELECT statements and avoiding DDL/DML operations.
• Handle version compatibility using PostgreSQLVersion and VersionAwareQueries from version_compat.py when targeting features unavailable in older PostgreSQL releases.
• Register tools in prompt_template.md so LLMs can discover and invoke them correctly.
• Test implementations by starting the server and verifying the "Registering tool" log message appears.

Frequently Asked Questions

What programming language is used for implementing custom MCP tools in this framework?

The MCP-PostgreSQL-Ops framework is built using Python and leverages the FastMCP library for tool registration. All custom tools must be implemented as Python async coroutines decorated with @mcp.tool() and placed in the appropriate module files.

Can custom MCP tools modify data in PostgreSQL databases?

No. According to the framework's design principles, custom tools must maintain read-only semantics and execute only SELECT statements. The framework explicitly prohibits DDL (Data Definition Language) and DML (Data Manipulation Language) operations to ensure safety when LLMs invoke these tools autonomously.

How does the framework handle different PostgreSQL versions when implementing custom tools?

The framework provides version-aware utilities in src/mcp_postgresql_ops/version_compat.py. You should use the PostgreSQLVersion class and VersionAwareQueries to detect the server version and adapt SQL queries accordingly, ensuring compatibility with both legacy and modern PostgreSQL releases.

Where should I register a new custom tool so that LLMs can discover it?

After implementing the tool in mcp_main.py, you must add a description entry to src/mcp_postgresql_ops/prompt_template.md. This markdown file serves as the human-readable catalog that LLMs use to understand available tools and their invocation patterns, making your custom tool discoverable by AI agents.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}