A Model Communication Protocol (MCP) server for Apache Cloudberry database interaction, providing secure and efficient database management capabilities through AI-ready interfaces.
curl -sSfL https://astral.sh/uv/install.sh | sh
cd mcp-server uv venv source .venv/bin/activate uv sync
uv pip install -e .
uv build
Create a .env file in the project root:
# Database Configuration DB_HOST=localhost DB_PORT=5432 DB_NAME=postgres DB_USER=postgres DB_PASSWORD=your_password # Server Configuration MCP_HOST=localhost MCP_PORT=8000 MCP_DEBUG=false
# Run the MCP server python -m cbmcp.server # Or run with cloudberry-mcp-server cloudberry-mcp-server # Or run with custom configuration MCP_HOST=0.0.0.0 MCP_PORT=8080 python -m cbmcp.server
# Run the test client python -m cbmcp.client
postgres://schemas - List all database schemaspostgres://database/info - Get general database infopostgres://database/summary - Get detailed database summarylist_searchable_columns(schema, table, include_unindexed_text) - Discover embedding columns, tsvector columns, and text columns with a full-text indexvector_search(schema, table, vector_column, query_vector, limit, select_columns, filters, metric, probes, ef_search) - Nearest neighbour search, optionally pre-filteredfulltext_search(schema, table, text_column, query, limit, select_columns, filters, language, query_mode, match_mode) - Keyword search ranked by ts_rankhybrid_search(schema, table, vector_column, text_column, query_vector, query, limit, select_columns, filters, metric, language, query_mode, match_mode, rrf_k, candidates, probes, ef_search) - Both of the above, fused by reciprocal rankexecute_query(query, params, readonly) - Execute a SQL query. params is a list bound to $1, $2, ... in orderexplain_query(query, params) - Get query execution plan. Read-only statements only, because EXPLAIN ANALYZE runs what it is givenget_table_stats(schema, table) - Get table statisticslist_large_tables(limit) - List largest tableslist_users() - List all database userslist_user_permissions(username) - List permissions for a specific userlist_table_privileges(schema, table) - List privileges for a specific tablelist_constraints(schema, table) - List constraints for a tablelist_foreign_keys(schema, table) - List foreign keys for a tablelist_referenced_tables(schema, table) - List tables that reference this tableget_table_ddl(schema, table) - Get DDL statement for a tableget_slow_queries(limit) - List slow queriesget_index_usage() - Analyze index usage statisticsget_table_bloat_info() - Analyze table bloat informationget_database_activity() - Show current database activityget_vacuum_info() - Get vacuum and analyze statisticslist_functions(schema) - List functions in a schemaget_function_definition(schema, function) - Get function definitionlist_triggers(schema, table) - List triggers for a tablelist_materialized_views(schema) - List materialized views in a schemalist_active_connections() - List active database connectionsanalyze_query_performance - Query optimization assistancesuggest_indexes - Index recommendation guidancedatabase_health_check - Database health assessmentThe retrieval tools let an agent search rather than only query. They run over Apache Cloudberry's own storage: embeddings in pgvector columns, documents in text or tsvector columns. Nothing is cached or indexed outside the database.
Vector search covers vector, halfvec and sparsevec columns. A query vector is always supplied as a plain list of numbers; for a sparsevec column it is converted to that type's own text form, which lists only the non-zero entries.
Vector search needs the pgvector extension in the target database:
CREATE EXTENSION IF NOT EXISTS vector;
Full-text search needs no extension. A GIN index over to_tsvector(...) is what makes it fast, and is also what list_searchable_columns looks for when deciding whether a text column is worth reporting.
An agent that cannot see which columns hold embeddings has to guess from names. list_searchable_columns reports the column kind, the declared dimension, and the indexes defined on it:
[ {"schema": "public", "table": "docs", "column": "embedding", "kind": "vector", "type": "vector(8)", "dimension": 8, "full_text_indexed": false, "indexes": [{"name": "docs_embedding_ivf", "method": "ivfflat", "on_expression": false}]}, {"schema": "public", "table": "docs", "column": "content", "kind": "text", "type": "text", "dimension": null, "full_text_indexed": true, "indexes": [{"name": "docs_content_gin", "method": "gin", "on_expression": true}]} ]
filters is a list of conditions applied before ranking, so an approximate nearest neighbour search ranks only rows that already satisfy them:
{"filters": [{"column": "customer_id", "operator": "in", "value": [1, 2, 3]}]}
Operators are eq, ne, lt, lte, gt, gte, in, not_in, like, ilike, is_null, is_not_null. Column names are checked against the catalog and values are bound as query parameters, so a filter cannot inject SQL. For anything these do not express, use execute_query.
PostgreSQL's query constructors require every term to be present, so query latency slow becomes 'queri' & 'latenc' & 'slow'. A document holding the first two but not the third does not match. The longer and more natural the query, the likelier it is that nothing matches at all, and the caller gets an empty result rather than an error. To an agent that reads as “this database holds nothing on the subject”, which is the wrong conclusion when most of the terms did match something.
match_mode decides how the terms are combined:
| Mode | Behaviour |
|---|---|
all | Every term must appear. Precise, and empty when one term is missing |
any | Any term may appear. ts_rank still puts the fuller matches first |
all_then_any | Requires every term, and widens to any of them only if that matched nothing. The default |
A query that carries an explicit operator is never widened. ORing the lexemes of latency -dashboard would return exactly the rows the caller excluded, and flattening a phrase is the opposite of asking for one, so a negation or a phrase is read as an instruction to leave the query alone. The result then reports widening_refused: true alongside an empty row set.
The result always says which reading produced the rows, so widening is never silent:
{"search": {"match_mode": "all_then_any", "matched_with": "any", "widened": true}}
Under all_then_any the existence check is how the decision is made, so it runs on every call, not only when the strict reading fails. With a full-text index in place it is an index probe. Widening cannot rescue a query made entirely of stop words, because the parser produces no lexemes to widen to.
This matters most in hybrid_search. Without it the keyword arm can go empty while the vector arm still returns rows, so the fused result is pure vector search presenting itself as hybrid, and nothing in the response says so.
An IVFFlat index reads ivfflat.probes lists per scan, and the default of 1 can miss the true nearest neighbour outright. probes (IVFFlat) and ef_search (HNSW) trade latency for recall, and the settings that were applied come back in the result so a run can be reproduced:
{"search": {"mode": "vector", "settings": {"ivfflat.probes": 20}}}
The settings are session level and reset once the statement finishes, because Apache Cloudberry does not dispatch SET LOCAL to the segments where the index scan runs. Their upper bounds come from the installed pgvector rather than from this server, so a value it would refuse is rejected before anything is applied: hnsw.ef_search stops at 1000 while ivfflat.probes runs to 32768.
Recall also decides whether a pre-filtered search finds anything at all. The filter is applied inside the index scan, so a selective filter combined with a low probe count can return no rows even though matching rows exist. Raise probes before concluding that a filter matched nothing.
Every retrieval tool returns the rows, the generated SQL, and what it searched:
{ "columns": ["id", "content", "distance"], "rows": [[4138, "...", 0.27]], "row_count": 1, "sql": "SELECT ... ORDER BY \"embedding\" <-> $1::text::\"vector\" LIMIT $2", "search": {"mode": "vector", "metric": "l2", "limit": 1} }
Embedding columns are left out of the default projection because they are large and of no use to a reader; name one in select_columns and it comes back as text.
A table is free to have a column of its own called rank or score. Because rows come back as positional lists, the computed column is renamed rather than duplicated, and search names it: distance_column, rank_column, score_column, vector_rank_column, text_rank_column. Read the name from there rather than assuming it.
hybrid_search adds score, vector_rank and text_rank. A null rank means that arm did not return the row. A row scores 1 / (rrf_k + rank) from each arm it appears in, so a row that only one arm found still places. Ranks are fused rather than raw scores because a distance and a ts_rank share no scale, but their orderings do.
This section shows how to quickly set up and test the Cloudberry MCP Server using a local Cloudberry demo cluster. This is ideal for development and testing purposes.
Assume you already have a running Cloudberry demo cluster and install & build MCP server as described above.
pg_hba.confNote: This configuration is for demo purposes only. Do not use trust authentication in production environments.
[gpadmin@cdw]$ vi ~/cloudberry/gpAux/gpdemo/datadirs/qddir/demoDataDir-1/pg_hba.conf
Add the following lines to the end of the pg_hba.conf:
# IPv4 local connections host all all 127.0.0.1/32 trust # IPv6 local connections host all all ::1/128 trust
After modifying pg_hba.conf, reload the configuration parameters:
[gpadmin@cdw]$ gpstop -u
Create a .env in the project root directory:
# Database Configuration (Demo cluster defaults) DB_HOST=localhost DB_PORT=7000 DB_NAME=postgres DB_USER=gpadmin # No password required for demo cluster # Server Configuration MCP_HOST=localhost MCP_PORT=8000 MCP_DEBUG=false
MCP_HOST=0.0.0.0 MCP_PORT=8000 python -m cbmcp.server
You should see output indicating the server is running:
[09/17/25 14:07:50] INFO Starting MCP server 'Apache Cloudberry MCP Server' with transport server.py:1572
'streamable-http' on http://0.0.0.0:8000/mcp/
Add the following server configuration to your MCP client:
Replace [YOUR_HOST_IP] with your actual host IP address.
Add the following configuration to your Claude Desktop configuration file:
{ "mcpServers": { "cloudberry-mcp-server": { "command": "uvx", "args": [ "--with", "PATH/TO/cbmcp-0.1.0-py3-none-any.whl", "python", "-m", "cbmcp.server", "--mode", "stdio" ], "env": { "DB_HOST": "localhost", "DB_PORT": "5432", "DB_NAME": "dvdrental", "DB_USER": "yangshengwen", "DB_PASSWORD": "" } } } }
{ "mcpServers": { "cloudberry-mcp-server": { "type": "streamable-http", "url": "https://localhost:8000/mcp/", "headers": { "Authorization": "" } } } }
For Cursor IDE, add the configuration to your .cursor/mcp.json file:
{ "mcpServers": { "cloudberry-mcp": { "command": "uvx", "args": ["--with", "cbmcp", "python", "-m", "cbmcp.server", "--mode", "stdio"], "env": { "DB_HOST": "localhost", "DB_PORT": "5432", "DB_NAME": "dvdrental", "DB_USER": "postgres", "DB_PASSWORD": "your_password" } } } }
For Windsurf IDE, configure in your settings:
{ "mcp": { "servers": { "cloudberry-mcp": { "type": "stdio", "command": "uvx", "args": ["--with", "cbmcp", "python", "-m", "cbmcp.server", "--mode", "stdio"], "env": { "DB_HOST": "localhost", "DB_PORT": "5432", "DB_NAME": "dvdrental", "DB_USER": "postgres", "DB_PASSWORD": "your_password" } } } } }
For VS Code with the Cline extension, add to your settings:
{ "cline.mcpServers": { "cloudberry-mcp": { "command": "uvx", "args": ["--with", "cbmcp", "python", "-m", "cbmcp.server", "--mode", "stdio"], "env": { "DB_HOST": "localhost", "DB_PORT": "5432", "DB_NAME": "dvdrental", "DB_USER": "postgres", "DB_PASSWORD": "your_password" } } } }
If you prefer to install the package globally instead of using uvx:
# Install the package pip install cbmcp-0.1.0-py3-none-any.whl # Or using pip install from source pip install -e . # Then use in configuration { "command": "python", "args": ["-m", "cbmcp.server", "--mode", "stdio"] }
All configurations support the following environment variables:
DB_HOST: Database host (default: localhost)DB_PORT: Database port (default: 5432)DB_NAME: Database name (default: postgres)DB_USER: Database usernameDB_PASSWORD: Database passwordMCP_HOST: Server host for HTTP mode (default: localhost)MCP_PORT: Server port for HTTP mode (default: 8000)MCP_DEBUG: Enable debug logging (default: false)Enable debug logging by setting:
export MCP_DEBUG=true
Apache License 2.0