postgresql-mcp-server by vpro1032 - MCP Server

PostgreSQL MCP Server

A Docker-based Model Context Protocol (MCP) server that provides comprehensive PostgreSQL database interaction capabilities. This server enables AI assistants and other MCP clients to query, inspect, and manage PostgreSQL databases through a standardized interface.

Features

🔍 Query Execution

Execute raw SQL queries with parameter binding
Run multiple queries in transactions
Get query execution plans with EXPLAIN/EXPLAIN ANALYZE

📊 Schema Inspection

List databases, schemas, tables, views, and functions
Describe table structure with columns, types, and constraints
View indexes and their definitions
Get table size and row count statistics

📝 Data Manipulation

Insert single or multiple rows
Update and delete with WHERE clauses
Bulk insert from JSON arrays
Export table data as JSON

⚙️ Database Administration

View database statistics and connection info
List and terminate active connections
Run VACUUM and ANALYZE operations
Check for table bloat

Quick Start

Prerequisites

Docker and Docker Compose
An MCP client (e.g., Claude Desktop, VS Code with MCP extension)

Installation

Clone the repository

git clone <repository-url>
cd postgresql-mcp-server

Configure environment variables

cp .env.example .env
# Edit .env with your preferred settings

Start the services
```
docker-compose up -d
```
Verify the server is running
```
docker-compose logs mcp-server
```

Configuration

The server is configured via environment variables. See for all available options:

Variable	Description	Default
`POSTGRES_HOST`	PostgreSQL host	`postgres`
`POSTGRES_PORT`	PostgreSQL port	`5432`
`POSTGRES_DB`	Database name	`postgres`
`POSTGRES_USER`	Database user	`postgres`
`POSTGRES_PASSWORD`	Database password	`postgres`
`POSTGRES_MAX_CONNECTIONS`	Max connection pool size	`20`

Using with MCP Clients

Claude Desktop

Add to your Claude Desktop configuration (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "postgresql": {
      "command": "docker",
      "args": ["exec", "-i", "postgresql-mcp-server", "node", "dist/index.js"]
    }
  }
}

VS Code MCP Extension

Add to your VS Code MCP settings:

{
  "mcp.servers": {
    "postgresql": {
      "command": "docker",
      "args": ["exec", "-i", "postgresql-mcp-server", "node", "dist/index.js"]
    }
  }
}

Available Commands

The server provides 20+ commands across 4 categories. See for detailed documentation.

Query Tools

execute_query - Execute SQL queries with parameter binding
execute_transaction - Run multiple queries in a transaction
explain_query - Get query execution plans

Schema Tools

list_databases - List all databases
list_schemas - List schemas in current database
list_tables - List tables in a schema
describe_table - Get table structure and constraints
list_indexes - List indexes for a table
list_views - List views in a schema
list_functions - List stored functions/procedures
get_table_size - Get table size and row count

Data Tools

insert_data - Insert a single row
update_data - Update rows with WHERE clause
delete_data - Delete rows with WHERE clause
bulk_insert - Insert multiple rows from JSON
export_table - Export table data as JSON

Admin Tools

get_database_stats - Get database statistics
list_active_connections - Show active connections
terminate_connection - Terminate a connection by PID
vacuum_table - Run VACUUM on a table
analyze_table - Run ANALYZE on a table
check_table_bloat - Check for table bloat

Development

Building from Source

# Install dependencies
npm install

# Build TypeScript
npm run build

# Run locally (requires PostgreSQL)
npm start

Project Structure

postgresql-mcp-server/
├── src/
│   ├── index.ts           # Main server entry point
│   ├── database.ts        # Database connection manager
│   └── tools/
│       ├── query.ts       # Query execution tools
│       ├── schema.ts      # Schema inspection tools
│       ├── data.ts        # Data manipulation tools
│       └── admin.ts       # Administrative tools
├── Dockerfile             # Multi-stage Docker build
├── docker-compose.yml     # Docker Compose configuration
└── package.json           # Node.js dependencies

Docker Commands

# Start services
docker-compose up -d

# Stop services
docker-compose down

# View logs
docker-compose logs -f mcp-server

# Rebuild after code changes
docker-compose up -d --build

# Access PostgreSQL directly
docker-compose exec postgres psql -U postgres

Security Considerations

Credentials: Never commit .env files with real credentials
Network: The MCP server and PostgreSQL communicate over a private Docker network
Permissions: The server runs as a non-root user in the container
SQL Injection: Always use parameterized queries (the tools support parameter binding)

Troubleshooting

Server won't connect to PostgreSQL

Check that PostgreSQL is healthy: docker-compose ps
Verify environment variables in .env
Check logs: docker-compose logs postgres

MCP client can't connect to server

Ensure the container is running: docker ps
Verify the command in your MCP client configuration
Check server logs: docker-compose logs mcp-server

Permission denied errors

Ensure your database user has appropriate permissions
Check PostgreSQL logs for authentication errors

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Support This Project

If you find this MSSQL MCP Server useful for your projects, please consider supporting its development!

☕ Become a Patron

This project is maintained by independent developers. Your support helps us:

🚀 Add new features and improvements
🐛 Fix bugs and improve stability
📚 Create better documentation and tutorials
🔒 Enhance security features
🎯 Provide faster support and updates

Support us on Patreon

Supporter Benefits

Patrons get access to:

🎯 Priority Support - Get help faster when you need it
📝 Early Access - Try new features before public release
💬 Direct Communication - Influence the project roadmap
🎓 Exclusive Tutorials - Advanced usage guides and examples
🏆 Recognition - Your name in our SUPPORTERS.md file

vpro1032/postgresql-mcp-server