Using PostgreSQL MCP with Upsun Remote Database

​

Introduction to Model Context Protocol (MCP)

1. Access data sources (like databases, APIs, or file systems)
2. Execute functions (like running queries or performing calculations)
3. Interact with external systems (like applications or services)

​

Why Connect Databases to LLMs via MCP?

1. Natural language access to data: Non-technical team members can query complex databases using plain English instead of SQL.
2. Controlled, secure access: MCP servers can enforce read-only access, preventing accidental data corruption or unauthorized changes.
3. Deeper context for AI conversations: The AI can reference your actual data when answering questions, making responses more accurate and relevant to your specific situation.
4. Simplified data exploration: Quickly explore database structures and content without writing SQL queries for every investigation.
5. Enhanced productivity: Developers can focus on higher-level problems while the AI handles routine data retrieval and analysis tasks.
6. Cross-platform compatibility: The same MCP server works with multiple AI assistants (Claude, GitHub Copilot, etc.) that support the protocol.

​

1. Adding a PostgreSQL Database to Upsun

1. Edit your .upsun/config.yaml file to add a PostgreSQL service:

services:
  # The name of the service container - can be any name you choose
  postgresql:
    type: postgresql:17  # Use your preferred PostgreSQL version

2. Create a relationship to this service in your application configuration:

applications:
  myapp:
    # Other configuration...
    relationships:
      database: "postgresql:postgresql"

3. Push your changes to deploy the PostgreSQL service:

git add .upsun/config.yaml
git commit -m "Add PostgreSQL service"
upsun push

​

2. Opening a Tunnel to Your Remote Database

upsun tunnel:open

SSH tunnel opened to postgresql at: pgsql://main:main@127.0.0.1:30000/main

upsun ssh env | grep DATABASE

​

3. Installing the PostgreSQL MCP Server

1. Make sure you have Node.js installed on your system.
2. You don’t need to install anything manually - we’ll use npx to run the server directly.

​

4. Configuring the MCP Server in Your Editor

​

For VSCode

1. Install the GitHub Copilot extension if you haven’t already.
2. Create a .vscode/mcp.json file in your project with the following configuration:

{
  "inputs": [],
  "servers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://main:main@127.0.0.1:30000/main"
      ]
    }
  }
}

​

For Cursor

1. Open Cursor and go to Settings > Features > MCP.
2. Click on ”+ Add New MCP Server”
3. Fill out the form:
   • Name: “postgres”
   • Type: “stdio”
   • Command: “npx”
   • Arguments: -y @modelcontextprotocol/server-postgres postgresql://main:main@127.0.0.1:30000/main

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://main:main@127.0.0.1:30002/main"
      ]
    }
  }
}

​

5. Testing the Setup

1. Make sure your Upsun tunnel is open and running. You can check the tunnel status and credentials with the following command:

upsun tunnel:info -A api
postgresql:
    -
        [...]
        service: postgresql
        rel: postgresql
        scheme: pgsql
        username: main
        password: main
        port: 30000
        path: main
        [...]
        type: 'postgresql:17'
        url: 'pgsql://main:main@127.0.0.1:30000/main'

2. Activate the AI assistant in your editor:
   • In VSCode: Open the Copilot Chat panel and switch to “Agent mode”
   • In Cursor: Open Composer and ensure the MCP server is active
3. Try asking questions about your database:
   • “What tables are in my database?”
   • “Can you show me the schema of the users table?”
   • “How many records are in the orders table?”

​

Troubleshooting

• Connection issues: Ensure your Upsun tunnel is open and the connection string is correct
• Permission errors: Check that your database credentials are matching
• MCP server not responding: Verify the MCP server configuration and ensure the required packages are installed

​

Security Considerations

• The PostgreSQL MCP server provides read-only access to your database
• Always use tunnels for secure connections to remote databases
• For sensitive data, work with non-production environments by creating development branches in Upsun

​

Using Non-Production Databases

1. Create a development branch from your production environment:
   git checkout -b test-db
   git push -u origin test-db
   
2. Upsun automatically creates a new environment with a complete copy of your production database
3. You can then safely connect your MCP server to this development environment:
   upsun tunnel:open -e test-db
   

​

Additional Resources

• Upsun PostgreSQL Documentation
• Model Context Protocol Documentation