pgsql

This MCP server provides an interface for AI assistants to interact with a PostgreSQL database via the MCP protocol. It exposes a set of tools that map directly to common SQL operations: read_query for SELECT statements, write_query for INSERT/UPDATE/DELETE statements, create_table for creating new tables, list_tables to enumerate user-created tables, and explain_query to fetch query execution plans. The server also supports multiple transport modes, including stdio for direct command-line use, SSE for event-driven clients, and streamableHttp for HTTP-based MCP clients. Configure the database connection with environment variables and run the binary to start serving MCP requests. When using MCP-enabled assistants, you’ll reference the available tools by name and provide the required parameters shown in the tool descriptions.

Prerequisites:

• Go 1.18+ (per project requirements)
• PostgreSQL database server accessible from the host where the MCP server runs

Installation steps:

1. Clone the repository: git clone https://github.com/leixiaotian1/pgsql-mcp-server.git cd pgsql-mcp-server

2. Build the MCP server: go mod download go build -o pgsql-mcp-server

3. Configure environment variables (example in a .env file): DB_HOST=localhost # PostgreSQL server host DB_PORT=5432 # PostgreSQL server port DB_NAME=postgres # Database name DB_USER=your_username # Database user DB_PASSWORD=your_password # Database password DB_SSLMODE=disable # SSL mode (disable, require, verify-ca, verify-full) SERVER_MODE=stdio # Server mode (stdio, sse, streamableHttp)

4. Run the server: ./pgsql-mcp-server

Optional: Run with a specific environment file, or export variables in your shell before starting.

Docker (optional): see the Docker deployment guide in the README for container-based deployment steps.

Tips and notes:

• The server uses environment variables to configure the PostgreSQL connection; ensure DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD, DB_SSLMODE are set correctly.
• SERVER_MODE controls transport: stdio for local testing, sse for HTTP SSE endpoints, and streamableHttp for modern HTTP MCP transport.
• Common issues often relate to database connectivity (firewall, incorrect credentials, or SSL mode mismatches). Check logs for messages like 'Successfully connected to database'.
• If using Docker, ensure the DB_HOST matches the PostgreSQL container name in your network, and expose/bridge ports as needed.
• The MCP configuration block in your assistant’s config should reference the binary path and environment variables exactly as shown, replacing placeholders with real values where appropriate.