{"id":9191,"library":"postgres-mcp","title":"PostgreSQL Multi-Cluster Performance Tool (postgres-mcp)","description":"PostgreSQL Tuning and Analysis Tool (Multi-Cluster Performance), currently at version 0.3.0. It's a command-line utility and a Python library designed to help analyze and optimize PostgreSQL database performance by providing recommendations and insights. Its release cadence is irregular, typical for a community-driven tool in its early stages.","status":"active","version":"0.3.0","language":"en","source_language":"en","source_url":"https://github.com/pgtune/postgres-mcp","tags":["postgresql","database","tuning","analysis","performance"],"install":[{"cmd":"pip install postgres-mcp","lang":"bash","label":"Standard installation"}],"dependencies":[{"reason":"PostgreSQL database adapter for connecting to the database.","package":"psycopg2-binary","optional":false},{"reason":"ORM and SQL toolkit used for database interaction.","package":"SQLAlchemy","optional":false}],"imports":[{"note":"Classes are in specific submodules, not directly under the top-level package.","wrong":"import postgres_mcp; cm = postgres_mcp.ConnectionManager()","symbol":"ConnectionManager","correct":"from postgres_mcp.connections import ConnectionManager"},{"note":"Classes are in specific submodules, not directly under the top-level package.","wrong":"from postgres_mcp import Recommender","symbol":"Recommender","correct":"from postgres_mcp.recommender import Recommender"},{"note":"The main CLI script 'mcp.py' is not the source of these classes.","wrong":"from postgres_mcp.mcp import Analyzer","symbol":"Analyzer","correct":"from postgres_mcp.analysis import Analyzer"}],"quickstart":{"code":"import os\nfrom postgres_mcp.connections import ConnectionManager\nfrom postgres_mcp.recommender import Recommender\n\n# Ensure these environment variables are set or provide default values\nDB_HOST = os.environ.get('PG_MCP_DB_HOST', 'localhost')\nDB_PORT = int(os.environ.get('PG_MCP_DB_PORT', 5432))\nDB_USER = os.environ.get('PG_MCP_DB_USER', 'postgres')\nDB_PASS = os.environ.get('PG_MCP_DB_PASS', '') # Consider using an actual password or no password if allowed\nDB_NAME = os.environ.get('PG_MCP_DB_NAME', 'postgres')\n\nif not DB_PASS and DB_USER != 'postgres': # Simple check for non-default user requiring password\n    print(\"Warning: DB_PASS environment variable not set. Connection might fail if user requires a password.\")\n\ntry:\n    # Initialize connection manager\n    conn_manager = ConnectionManager(\n        host=DB_HOST,\n        port=DB_PORT,\n        user=DB_USER,\n        password=DB_PASS,\n        dbname=DB_NAME\n    )\n    \n    # Initialize the recommender with the connection manager\n    recommender = Recommender(conn_manager)\n    \n    # Get recommendations\n    print(f\"Fetching recommendations for {DB_USER}@{DB_HOST}:{DB_PORT}/{DB_NAME}...\")\n    recommendations = recommender.get_recommendations()\n    \n    print(\"\\nPostgreSQL Recommendations:\")\n    if recommendations:\n        for category, recs in recommendations.items():\n            print(f\"[{category.upper()}]\")\n            for key, value in recs.items():\n                print(f\"  - {key}: {value}\")\n    else:\n        print(\"No specific recommendations found (this might be normal for a healthy DB or small workload).\")\n\nexcept Exception as e:\n    print(f\"\\nAn error occurred during recommendation generation: {e}\")\n    print(\"Please ensure:\")\n    print(\"1. Your PostgreSQL server is running and accessible from this machine.\")\n    print(\"2. The provided database connection details (host, port, user, password, dbname) are correct.\")\n    print(\"3. The database user has sufficient permissions to query system catalog views (e.g., pg_stat_activity, pg_settings).\")\n","lang":"python","description":"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)."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":"<1.0.0"},{"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.","message":"`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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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).","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Run `pip install postgres-mcp` to install the package in your active Python environment.","cause":"The `postgres-mcp` package is not installed or the Python environment where you are running the script does not have it installed.","error":"ModuleNotFoundError: No module named 'postgres_mcp'"},{"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.","cause":"The PostgreSQL server is not running, is not accessible from the machine running `postgres-mcp`, or the connection details (host, port) are incorrect.","error":"psycopg2.OperationalError: could not connect to server: Connection refused"},{"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.","cause":"The provided username or password for connecting to the PostgreSQL database is incorrect.","error":"psycopg2.OperationalError: password authentication failed for user \"your_user\""}]}