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, debugging database issues, managing schemas, migrating data, and monitoring database performance.
Features
Database Analysis and Debugging
1. Database Analysis (`analyze_database`)
Analyzes PostgreSQL database configuration and performance metrics:
- Configuration analysis
- Performance metrics
- Security assessment
- Recommendations for optimization
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
3. Database Debugging (`debug_database`)
Debug common PostgreSQL issues:
- Connection problems
- Performance bottlenecks
- Lock conflicts
- Replication status
Schema Management
4. Schema Information (`get_schema_info`)
Get detailed schema information for a database or specific table:
- List of tables in a database
- Column definitions
- Constraints (primary keys, foreign keys, etc.)
- Indexes
5. Create Table (`create_table`)
Create a new table with specified columns:
- Define column names and types
- Set nullable constraints
- Set default values
6. Alter Table (`alter_table`)
Modify existing tables:
- Add new columns
- Modify column types or constraints
- Drop columns
Data Migration
7. Export Table Data (`export_table_data`)
Export table data to JSON or CSV format:
- Filter data with WHERE clause
- Limit number of rows
- Choose output format
8. Import Table Data (`import_table_data`)
Import data from JSON or CSV files:
- Optionally truncate table before import
- Support for different formats
- Custom CSV delimiters
9. Copy Between Databases (`copy_between_databases`)
Copy data between two PostgreSQL databases:
- Filter data with WHERE clause
- Optionally truncate target table
Monitoring
10. Monitor Database (`monitor_database`)
Real-time monitoring of PostgreSQL database:
- Database metrics (connections, cache hit ratio, etc.)
- Table metrics (size, row counts, dead tuples)
- Active query information
- Lock information
- Replication status
- Configurable alerts
Prerequisites
- Node.js >= 18.0.0
- PostgreSQL server (for target database operations)
- Network access to target PostgreSQL instances
Installation
- Clone the repository
- Install dependencies:
- Build the server:
- Add to MCP settings file:
Development
npm run dev- Start development server with hot reload
npm run lint- Run ESLint
npm test- Run tests
Security Considerations
- Connection Security
- Query Safety
- Authentication
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
- Regular security audits and updates
Error Handling
The server implements comprehensive error handling:
- 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
A Model Context Protocol (MCP) server that provides PostgreSQL database management capabilities. This server assists with analyzing existing PostgreSQL setups, providing implementation guidance, debugging database issues, managing schemas, migrating data, and monitoring database performance.
Features
Database Analysis and Debugging
1. Database Analysis (`analyze_database`)
Analyzes PostgreSQL database configuration and performance metrics:
- Configuration analysis
- Performance metrics
- Security assessment
- Recommendations for optimization
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
3. Database Debugging (`debug_database`)
Debug common PostgreSQL issues:
- Connection problems
- Performance bottlenecks
- Lock conflicts
- Replication status
Schema Management
4. Schema Information (`get_schema_info`)
Get detailed schema information for a database or specific table:
- List of tables in a database
- Column definitions
- Constraints (primary keys, foreign keys, etc.)
- Indexes
5. Create Table (`create_table`)
Create a new table with specified columns:
- Define column names and types
- Set nullable constraints
- Set default values
6. Alter Table (`alter_table`)
Modify existing tables:
- Add new columns
- Modify column types or constraints
- Drop columns
Data Migration
7. Export Table Data (`export_table_data`)
Export table data to JSON or CSV format:
- Filter data with WHERE clause
- Limit number of rows
- Choose output format
8. Import Table Data (`import_table_data`)
Import data from JSON or CSV files:
- Optionally truncate table before import
- Support for different formats
- Custom CSV delimiters
9. Copy Between Databases (`copy_between_databases`)
Copy data between two PostgreSQL databases:
- Filter data with WHERE clause
- Optionally truncate target table
Monitoring
10. Monitor Database (`monitor_database`)
Real-time monitoring of PostgreSQL database:
- Database metrics (connections, cache hit ratio, etc.)
- Table metrics (size, row counts, dead tuples)
- Active query information
- Lock information
- Replication status
- Configurable alerts
Prerequisites
- Node.js >= 18.0.0
- PostgreSQL server (for target database operations)
- Network access to target PostgreSQL instances
Installation
- Clone the repository
- Install dependencies:
- Build the server:
- Add to MCP settings file:
Development
npm run dev- Start development server with hot reload
npm run lint- Run ESLint
npm test- Run tests
Security Considerations
- Connection Security
- Query Safety
- Authentication
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
- Regular security audits and updates
Error Handling
The server implements comprehensive error handling:
- 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.