Postgresql Database Management Server

🚀 PostgreSQL MCP Server

PostgreSQL MCP (Model Context Protocol) Server is a tool for managing and operating PostgreSQL databases. It offers various functions, including database analysis, debugging, schema management, data migration, and real-time monitoring.

🚀 Quick Start

The PostgreSQL MCP Server is designed to simplify the management and operation of PostgreSQL databases. With a range of powerful features, it helps users analyze, debug, and optimize their databases effectively.

✨ Features

Database Analysis

• Description: Provides comprehensive analysis of PostgreSQL databases, including performance metrics and health checks.
• Example:

// Analyze database performance
const analysis = await postgresqlMcpServer.analyzeDatabase(connectionString);

Database Debugging

• Description: Helps identify and resolve issues in the database, including query optimization and error troubleshooting.
• Example:

// Debug a specific query
const debugResult = await postgresqlMcpServer.debugQuery(connectionString, query);

Schema Management

• Description: Offers management and operation functions for database schemas, including tables, indexes, and views.
• Example:

// Get the database schema
const schema = await postgresqlMcpServer.getSchema(connectionString);

Data Migration

• Description: Supports data migration between different PostgreSQL databases, including filtering and cleaning operations.
• Example:

// Migrate data
await postgresqlMcpServer.migrateData(sourceConnectionString, targetConnectionString);

Real-time Monitoring

• Description: Provides real-time monitoring of PostgreSQL databases, including performance metrics, query activities, and lock information.
• Example:

// Monitor the database status
const monitoringResult = await postgresqlMcpServer.monitorDatabase(connectionString);

📦 Installation

Prerequisites

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

Steps

1. Clone the repository.
2. Install dependencies:

npm install

3. Build the server:

npm run build

4. Add the following content to the MCP settings file:

{
  "mcpServers": {
    "postgresql-mcp": {
      "command": "node",
      "args": ["/path/to/postgresql-mcp-server/build/index.js"],
      "disabled": false,
      "alwaysAllow": [],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://username:password@server:port/dbname"
      }
    }
  }
}

📚 Documentation

Development

• npm run dev - Start the development server with hot reloading.
• npm run lint - Run ESLint.
• npm test - Run tests.

Security Considerations

Connection Security

• Use connection pooling.
• Implement connection timeouts.
• Validate connection strings.
• Support SSL/TLS connections.

Query Security

• Validate SQL queries.
• Prevent dangerous operations.
• Implement query timeouts.
• Log all operations.

Authentication

• Support multiple authentication methods.
• Implement role-based access control.
• Enforce password policies.
• Securely manage connection credentials.

Best Practices

1. Always use secure connection strings with appropriate credentials.
2. Follow security recommendations for production environments.
3. Regularly monitor and analyze database performance.
4. Keep the PostgreSQL version up to date.
5. Implement appropriate data backup strategies.
6. Use connection pooling to optimize resource management.
7. Implement appropriate error handling and logging.
8. Conduct regular security audits and updates.

Error Handling

The server implements comprehensive error handling for:

• Connection failures
• Query timeouts
• Authentication errors
• Locking issues

Contribution Guidelines

Please refer to the contribution guidelines in the project repository to learn how to contribute to the PostgreSQL MCP Server.

📄 License

This project is licensed under the LICENSE.