PostgreSQL Multi-Cluster Performance Tool (postgres-mcp)

Common errors

• ModuleNotFoundError: No module named 'postgres_mcp'

  cause The `postgres-mcp` package is not installed or the Python environment where you are running the script does not have it installed.
  fix Run `pip install postgres-mcp` to install the package in your active Python environment.

• psycopg2.OperationalError: could not connect to server: Connection refused

  cause The PostgreSQL server is not running, is not accessible from the machine running `postgres-mcp`, or the connection details (host, port) are incorrect.
  fix Verify that your PostgreSQL server is running and listening on the specified host and port. Check firewall rules. Ensure `PG_MCP_DB_HOST` and `PG_MCP_DB_PORT` environment variables (or hardcoded values) are correct.

• psycopg2.OperationalError: password authentication failed for user "your_user"

  cause The provided username or password for connecting to the PostgreSQL database is incorrect.
  fix Double-check your `PG_MCP_DB_USER` and `PG_MCP_DB_PASS` environment variables (or hardcoded values) for accuracy. Ensure the user exists and the password matches for the specified database.

Warnings

• breaking As a pre-1.0 library (version 0.x.x), `postgres-mcp`'s API is subject to change without strict backward compatibility guarantees. Expect potential breaking changes in minor releases.
  Fix: Always pin the exact version in your `requirements.txt` or `pyproject.toml` (e.g., `postgres-mcp==0.3.0`) and review release notes carefully before upgrading.
• gotcha `postgres-mcp` primarily operates as a command-line tool. While programmatic access to its components (like `ConnectionManager`, `Recommender`, `Analyzer`) is possible, the documentation often focuses on CLI usage. Direct programmatic usage requires familiarity with its internal module structure.
  Fix: Refer to the source code (especially `src/postgres_mcp/mcp.py` for how the CLI uses the library) to understand the intended usage of internal classes and functions.
• gotcha For `postgres-mcp` to provide comprehensive analysis and recommendations, the connecting PostgreSQL user typically requires more than just basic read-only access. It needs permissions to query system views and potentially execute functions like `pg_stat_statements` (if enabled).
  Fix: Ensure your database user has sufficient privileges, e.g., membership in the `pg_monitor` role or specific `SELECT` grants on views like `pg_stat_activity`, `pg_settings`, `pg_stat_bgwriter`, etc.

Install

• pip install postgres-mcp Standard installation

Imports

• ConnectionManager
  wrong:

  import postgres_mcp; cm = postgres_mcp.ConnectionManager()

  correct:

  from postgres_mcp.connections import ConnectionManager

  Classes are in specific submodules, not directly under the top-level package.
• Recommender
  wrong:

  from postgres_mcp import Recommender

  correct:

  from postgres_mcp.recommender import Recommender

  Classes are in specific submodules, not directly under the top-level package.
• Analyzer
  wrong:

  from postgres_mcp.mcp import Analyzer

  correct:

  from postgres_mcp.analysis import Analyzer

  The main CLI script 'mcp.py' is not the source of these classes.

Quickstart

This quickstart demonstrates how to programmatically use `postgres-mcp` to connect to a PostgreSQL database and fetch performance recommendations using the `ConnectionManager` and `Recommender` classes. It requires setting database connection details via environment variables or hardcoding them (not recommended for production).

import os
from postgres_mcp.connections import ConnectionManager
from postgres_mcp.recommender import Recommender

# Ensure these environment variables are set or provide default values
DB_HOST = os.environ.get('PG_MCP_DB_HOST', 'localhost')
DB_PORT = int(os.environ.get('PG_MCP_DB_PORT', 5432))
DB_USER = os.environ.get('PG_MCP_DB_USER', 'postgres')
DB_PASS = os.environ.get('PG_MCP_DB_PASS', '') # Consider using an actual password or no password if allowed
DB_NAME = os.environ.get('PG_MCP_DB_NAME', 'postgres')

if not DB_PASS and DB_USER != 'postgres': # Simple check for non-default user requiring password
    print("Warning: DB_PASS environment variable not set. Connection might fail if user requires a password.")

try:
    # Initialize connection manager
    conn_manager = ConnectionManager(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASS,
        dbname=DB_NAME
    )
    
    # Initialize the recommender with the connection manager
    recommender = Recommender(conn_manager)
    
    # Get recommendations
    print(f"Fetching recommendations for {DB_USER}@{DB_HOST}:{DB_PORT}/{DB_NAME}...")
    recommendations = recommender.get_recommendations()
    
    print("\nPostgreSQL Recommendations:")
    if recommendations:
        for category, recs in recommendations.items():
            print(f"[{category.upper()}]")
            for key, value in recs.items():
                print(f"  - {key}: {value}")
    else:
        print("No specific recommendations found (this might be normal for a healthy DB or small workload).")

except Exception as e:
    print(f"\nAn error occurred during recommendation generation: {e}")
    print("Please ensure:")
    print("1. Your PostgreSQL server is running and accessible from this machine.")
    print("2. The provided database connection details (host, port, user, password, dbname) are correct.")
    print("3. The database user has sufficient permissions to query system catalog views (e.g., pg_stat_activity, pg_settings).")