Just as USB-C provides a standardized way to connect your devices to various peripherals and accessories, MCP provides a standardized way to connect AI models to different data sources and tools.
MCP is an open protocol that standardizes how applications provide context to LLMs. MCP helps you build agents and complex workflows on top of LLMs. LLMs frequently need to integrate with data and tools, and MCP provides:
- A growing list of pre-built integrations that your LLM can directly plug into
- The flexibility to switch between LLM providers and vendors
- Best practices for securing your data within your infrastructure
This guide walks you through setting up PostgreSQL locally on a MacBook, configuring a database, and integrating it with Claude Desktop for efficient data querying and management. Follow along to get started!
Refer this GitHub Repository for MCP Postgres
Getting Started
Prereq
- MacBook
- Install Docker Desktop
- Install Claude Desktop
- Install Postgres (without Docker)
- Install Postgres(with Docker)
Method 1: Setting up Postgres locally (without Docker)
Install Postgres locally
To install PostgreSQL on your MacBook, use the following Homebrew command:
Copied!brew install postgresql
Start Postgres Service
After installation, start the PostgreSQL service:
Copied!brew services start postgresql
Result:
Copied!brew services start postgresql ==> Successfully started `postgresql@14` (lab
Create database
Create a sample database using the command:
Copied!createdb sampledb
Connect to the database
Run this command to connect to the database you just created.
Copied!psql sampledb psql (15.10 (Homebrew), server 14.15 (Homebrew)) Type "help" for help. sampledb=#
Create Table
Once connected, create a table in your database:
Copied!sampledb=# CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Installing Claude Desktop
Claude Desktop is a desktop application developed by Anthropic that brings the capabilities of their AI chatbot, Claude, directly to your computer. Available for both macOS and Windows, the app offers a seamless integration of Claude’s features into your daily workflow, enhancing productivity and accessibility.
Key Features:
- Instant Access: Launch Claude directly from your desktop without the need to navigate to a web browser, streamlining your workflow.
- Enhanced Performance: Designed for deep work, the desktop app provides a faster and more focused experience compared to the web interface.
- Voice Interaction: Anthropic has introduced dictation support in its mobile apps, allowing users to interact with Claude using voice commands. This feature enhances accessibility and user experience.
- Cross-Platform Availability: Claude Desktop is available for both macOS and Windows, ensuring compatibility with a wide range of systems.
Download and install Claude Desktop using this link
Configure Claude Desktop Configuration
Once installed, follow these steps to configure Claude Desktop for PostgreSQL.
- Open Applications > Claude Desktop.
- Click on “Edit” Config.
- Add the following entry for PostgreSQL in the configuration:
Once downloaded, open Applications > Claude Desktop
Click “Edit Config” and add the following entry for Postgres
Copied!{ "mcpServers": { "postgres": { "command": "npx", "args": [ "-y", "@modelcontextprotocol/server-postgres", "postgresql://localhost/sampledb" ] } } }
Restart the Claude Desktop and click on Hammer sign shown under the UI.
You will find that it successfully detected your Postgres along with the table name from your local system.
Querying the Database
With the setup complete, you can now start querying your PostgreSQL database directly through Claude Desktop. Use the interface to execute SQL commands and manage your data effectively.
With these steps, your PostgreSQL database and Claude Desktop are ready to handle your projects seamlessly.
Setting up Postgres using Docker Desktop
Step 1. Run a Postgres Container
Copied!docker run -d --name postgres2 -e POSTGRES_PASSWORD=dev -p 5433:5432 postgres:13
Open Docker Dashboard and run the following commands:
Copied!# psql -d postgres -U postgres -W Password: psql (17.2 (Debian 17.2-1.pgdg120+1)) Type "help" for help. postgres=#
The above command includes three flags:
- -d – specifies the name of the database to connect to
- -U – specifies the name of the user to connect as
- -W – forces psql to ask for the user password before connecting to the database
Listing all the databases – \l
Copied!List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges -----------+----------+----------+-----------------+------------+------------+--------+-----------+----------------------- postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/po stgres + | | | | | | | | postg res=CTc/postgres template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/po stgres + | | | | | | | | postg res=CTc/postgres (3 rows)
List all schemas
The \dn psql command lists all the database schemas.
Copied!postgres=# \dn List of schemas Name | Owner --------+------------------- public | pg_database_owner (1 row) postgres=#
Run the following command to show the database activity:
Copied!SELECT * from pg_stat_activity; <--- DONT FORGET ";"
Result:
Copied!datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_host name | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type -------+----------+-----+------------+----------+----------+------------------+-------------+------------ -----+-------------+-------------------------------+-------------------------------+--------------------- ----------+-------------------------------+-----------------+---------------------+--------+------------- +--------------+----------+---------------------------------+------------------------------ 5 | postgres | 85 | | 10 | postgres | psql | | | -1 | 2025-01-08 11:40:22.778949+00 | | 2025-01-08 11:40:56. 590114+00 | 2025-01-08 11:41:26.598462+00 | Client | ClientRead | idle | | | | SELECT pg_sleep(30); | client backend 5 | postgres | 92 | | 10 | postgres | psql | | | -1 | 2025-01-08 11:41:14.359414+00 | 2025-01-08 11:43:52.615603+00 | 2025-01-08 11:43:52. 615603+00 | 2025-01-08 11:43:52.61561+00 | | | active | | 750 | | SELECT * FROM pg_stat_activity; | client backend | | 64 | | | | | | | | 2025-01-08 11:38:28.74611+00 | | | | Activity | AutovacuumMain | | | | | | autovacuum launcher | | 65 | | 10 | postgres | | | :
Create some dummy tables
Copied!-- Create a table for Users CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create a table for Orders CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create a table for Products CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0 );
Query the list of tables
Copied!SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Using Claude Desktop
Edit and add the following JSON file. Refer https://github.com/modelcontextprotocol/servers/tree/main/src/postgres
Copied!{ "mcpServers": { "postgres": { "command": "docker", "args": [ "run", "-i", "--rm", "mcp/postgres", "postgresql://postgres:dev@host.docker.internal:5432/postgres"] } } }
In this blog, you learned how to run Postgres MCP server packaged in the form of Docker container with and able to perform operations using Claude Desktop.
Leave a Reply