📌Overview

Purpose: To provide PostgreSQL database management capabilities through analysis, setup guidance, and debugging support.

Overview: PostgreSQL MCP Server is a Model Context Protocol server designed to enhance PostgreSQL database operations. It assists users in analyzing database setups, providing comprehensive implementation instructions, and debugging a variety of common database issues, thereby facilitating optimal database management.

Key Features:

Database Analysis (analyze_database): Performs an in-depth analysis of PostgreSQL configurations and performance metrics, offering insights on security and optimization recommendations to enhance database efficiency.
Setup Instructions (get_setup_instructions): Supplies platform-specific, step-by-step installation and configuration guidance for PostgreSQL, ensuring best practices in security and post-installation tasks are clearly outlined.
Database Debugging (debug_database): Aids users in troubleshooting typical PostgreSQL issues, such as connection problems, performance bottlenecks, and replication status, enabling more effective database operation management.

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides PostgreSQL database management capabilities. This server assists with analyzing existing PostgreSQL setups, providing implementation guidance, and debugging database issues.

Features

1. Database Analysis (`analyze_database`)

Analyzes PostgreSQL database configuration and performance metrics:

Configuration analysis
Performance metrics
Security assessment
Recommendations for optimization

// Example usage
{
  "connectionString": "postgresql://user:password@localhost:5432/dbname",
  "analysisType": "performance" // Optional: "configuration" | "performance" | "security"
}

2. Setup Instructions (`get_setup_instructions`)

Provides step-by-step PostgreSQL installation and configuration guidance:

Platform-specific installation steps
Configuration recommendations
Security best practices
Post-installation tasks

// Example usage
{
  "platform": "linux", // Required: "linux" | "macos" | "windows"
  "version": "15", // Optional: PostgreSQL version
  "useCase": "production" // Optional: "development" | "production"
}

3. Database Debugging (`debug_database`)

Debug common PostgreSQL issues:

Connection problems
Performance bottlenecks
Lock conflicts
Replication status

// Example usage
{
  "connectionString": "postgresql://user:password@localhost:5432/dbname",
  "issue": "performance", // Required: "connection" | "performance" | "locks" | "replication"
  "logLevel": "debug" // Optional: "info" | "debug" | "trace"
}

Prerequisites

Node.js >= 18.0.0
PostgreSQL server (for target database operations)
Network access to target PostgreSQL instances

Installation

Installing via Smithery

To install PostgreSQL MCP Server for Claude Desktop automatically via Smithery:

npx -y @smithery/cli install @nahmanmate/postgresql-mcp-server --client claude

Manual Installation

Clone the repository
Install dependencies:
```
npm install
```
Build the server:
```
npm run build
```

Add to MCP settings file:

{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "node",
      "args": ["/path/to/postgresql-mcp-server/build/index.js"],
      "disabled": false,
      "alwaysAllow": []
    }
  }
}

Development

npm run dev - Start development server with hot reload
npm run lint - Run ESLint
npm test - Run tests

Security Considerations

Connection Security
- Uses connection pooling
- Implements connection timeouts
- Validates connection strings
- Supports SSL/TLS connections
Query Safety
- Validates SQL queries
- Prevents dangerous operations
- Implements query timeouts
- Logs all operations
Authentication
- Supports multiple authentication methods
- Implements role-based access control
- Enforces password policies
- Manages connection credentials securely

Best Practices

Always use secure connection strings with proper credentials
Follow production security recommendations for sensitive environments
Regularly monitor and analyze database performance
Keep PostgreSQL version up to date
Implement proper backup strategies
Use connection pooling for better resource management
Implement proper error handling and logging
Conduct regular security audits and updates

Error Handling

The server implements comprehensive error handling for:

Connection failures
Query timeouts
Authentication errors
Permission issues
Resource constraints

Contributing

Fork the repository
Create a feature branch
Commit your changes
Push to the branch
Create a Pull Request

License

This project is licensed under the AGPLv3 License - see LICENSE file for details.

postgresql-mcp-server