mcp-postgresql-ops 1.0.6

Add your description here

MCP PostgreSQL Operations Server

A professional MCP server for PostgreSQL database server operations, monitoring, and management. Most features work independently, but advanced performance analysis capabilities are available when the pg_stat_statements and (optionally) pg_stat_monitor extensions are installed.

Features

• ✅ PostgreSQL Monitoring: Performance analysis based on pg_stat_statements and pg_stat_monitor

• ✅ Structure Exploration: Database, table, and user listing

• ✅ Performance Analysis: Slow query identification and index usage analysis

• ✅ Capacity Management: Database and table size analysis

• ✅ Configuration Retrieval: PostgreSQL configuration parameter verification

• ✅ Safe Read-Only: All operations are read-only and safe

• 🛠️ Easy Customization: Simple and clean codebase makes it very easy to add new tools or customize existing ones

Example Usage

---

Quick start

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.

1. Environment Setup

### Check and modify .env file
cp .env.example .env

### If you use other postgresql server, configure connection information:
# POSTGRES_HOST=your-address
# POSTGRES_PORT=your-listen-port
# POSTGRES_USER=your-username
# POSTGRES_PASSWORD=your-password
# POSTGRES_DB=your-database

2. Install Dependencies

docker-compose up -d

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

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.

---

Usage Examples

Claude Desktop Integration (Examples)

Add to your Claude Desktop configuration file:

{
  "mcpServers": {
    "postgresql-ops": {
      "command": "uvx",
      "args": ["--python", "3.11", "mcp-postgresql-ops"],
      "env": {
        "POSTGRES_HOST": "127.0.0.1",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "passwd",
        "POSTGRES_DB": "testdb"
      }
    }
  }
}

Options: Run with Local Source:

{
  "mcpServers": {
    "postgresql-ops": {
      "command": "uv",
      "args": ["run", "python", "-m", "src.mcp_postgresql_ops.mcp_main"],
      "cwd": "/path/to/MCP-PostgreSQL-Ops",
      "env": {
        "POSTGRES_HOST": "127.0.0.1",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "passwd",
        "POSTGRES_DB": "testdb"
      }
    }
  }
}

Command Line Usage

/w Local Source

# Stdio mode
python -m src.mcp_postgresql_ops.mcp_main \
  --type stdio

# HTTP mode
python -m src.mcp_postgresql_ops.mcp_main \
  --type streamable-http \
  --host 127.0.0.1 \
  --port 8080 \
  --log-level DEBUG

/w Pypi and uvx

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

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

---

Environment Variables

Variable	Description	Default	Project Default
PYTHONPATH	Python module search path for MCP server imports	/app/src	/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	8080	8080
PGSQL_VERSION	PostgreSQL major version for Docker image selection	16	15
POSTGRES_HOST	PostgreSQL server hostname or IP address	localhost	127.0.0.1
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
POSTGRES_DB	Default database name for connections	postgres	mcp_postgres_ops
POSTGRES_MAX_CONNECTIONS	PostgreSQL max_connections configuration parameter	100	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

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.

---

Prerequisites

Required PostgreSQL Extensions

⚠️ Note: Most MCP tools work without any PostgreSQL extensions. However, 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 16)
• Python 3.11
• Network access to PostgreSQL server
• Read permissions on system catalogs

---

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 current database"
  • "Show table sizes in the public schema"
• 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"

🟡 Extension-Dependent Tools

• get_pg_stat_statements_top_queries (Requires pg_stat_statements)
  • "Show top 10 slowest queries"
  • "Analyze slow queries in the sales database"
• 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"

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

📖 More Useful Example Queries →

---

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

Performance Issues

1. Use limit parameters to reduce result size
2. Run monitoring during off-peak hours
3. Check database load before running analysis

---

Development

Testing & Development

# Test with MCP Inspector
./scripts/run-mcp-inspector-local.sh

# Direct execution for debugging
python -m src.mcp_postgresql_ops.mcp_main --log-level DEBUG

# Run tests (if you add any)
uv run pytest

---

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