pg-mcp
Read-only PostgreSQL metadata and data exploration server for an MCP agent.
Check connection status, including database, role, profile, and limits.
List readable tables, views, and foreign tables with row estimates and comments, optionally by schema.
Search tables, views, and columns by name or comment text.
Describe a table’s columns, types, constraints, keys, referencing tables, and indexes.
Sample up to 50 rows from a table or view, optionally limited to specific columns.
Explain a read-only SQL query without running it.
Cannot modify data, structure, or settings.
Provides read-only access to PostgreSQL databases, enabling agents to inspect schemas, tables, views, columns, keys, indexes, sample rows, and query plans; run SELECT queries in the analyst profile; and compare schemas across multiple stands.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@pg-mcp describe the orders table and show a few sample rows"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
pg-explorer-mcp
English | Русский
Called pg-mcp until 0.8.0: what to do after an update is in Updating and removing.
An MCP server that gives an agent read-only access to a PostgreSQL database, or through one entry to
dozens of stands. The agent sees the database structure, can look at sample rows and
query plans, and analysts can run their own SELECTs.
Nothing can change data, structure or settings through this server.
Works with any MCP client that starts servers over stdio, such as Claude Code or Qwen Code. Runs on Python 3.9+ with nothing to install: the pg8000 driver ships inside the repository. Speaks English or Russian.
Contents
Related MCP server: PostgreSQL MCP Server
What it does
Tool | What it does | Profiles |
| which database, as which role, which profile and language, which limits; on failure, what to fix | all |
| with many stands: the databases of a stand and their schemas, those the server has logins for or all that a single login reads | all |
| tables, views and foreign tables the role can read, with estimated row counts and comments | all |
| tables, views and columns whose name or comment contains a piece of text, in every readable schema; for large databases | all |
| columns with types, | all |
| the data model as a Mermaid diagram the agent shows as a picture: the tables of a schema with their keys and foreign keys, or one table with its neighbours | all |
| with many stands: how one schema differs between two stands: tables and views, columns, constraints, indexes | all |
| catalog diagnostics: buffers, maintenance, index candidates, sequence headroom and sizes; no monitoring roles |
|
| a few rows of a table, optionally only some columns |
|
| a query plan without running the query; in the |
|
| one read-only statement: |
|
The group's administrator picks the profile at installation: details.
Quick start
You need Python 3.9+, Qwen Code (or another MCP client) and a database role from your DBA.
git clone https://github.com/dbelokursky/pg-explorer-mcp.git
cd pg-explorer-mcp
export PGHOST=db.example.com PGDATABASE=app PGUSER=mcp_agent # where the database is, which role
python3 install.py --client qwen --lang en # register and check
qwen extensions link "$(pwd)" # context and the /pg:setup commandPut the password into ~/.pgpass (how), restart
Qwen Code and ask the agent: "Check the database connection".
There is a shorter way too: link the extension (qwen extensions link), restart Qwen Code and run
/pg:setup in it. The agent finds Python, runs install.py and works through the hints itself.
Already have the stand connections in DBeaver, DataGrip or pg_service.conf? python3 install.py import dbeaver
(or import datagrip) shows what it would bring in, and /pg:import does the same through the agent:
many stands.
Try it on the demo database
The answers shown in Usage come from a small demo database: a shop the agent may read and an
hr schema it may not. scripts/demo.py makes it on any PostgreSQL where your PG* variables may
create a database and a role (a fresh local PostgreSQL will do):
python3 scripts/demo.py # pg_mcp_demo and the role mcp_demo_agent, password demo
echo "localhost:5432:pg_mcp_demo:mcp_demo_agent:demo" >> ~/.pgpass && chmod 600 ~/.pgpass
PGDATABASE=pg_mcp_demo PGUSER=mcp_demo_agent python3 install.py --profile analystRestart the agent and ask it: "How many orders are there per status?", "Describe the orders table",
"What is in the hr schema?". The data is the same on every copy (docs/demo.sql builds it with
arithmetic, not random()), so the agent's answers match the examples in this README.
python3 scripts/demo.py --drop removes the database and the role.
Installation step by step
1. The code
git clone https://github.com/dbelokursky/pg-explorer-mcp.gitWithout git, take pg-explorer-mcp-<version>.zip from the latest release on the
Releases page and unpack it anywhere: inside is a pg-explorer-mcp folder
with everything the server needs, and SHA256SUMS next to the archive holds its checksum. The archive
carries a provenance attestation: gh attestation verify pg-explorer-mcp-<version>.zip --repo dbelokursky/pg-explorer-mcp
confirms that GitHub built it from this repository's tag. Do not move the folder afterwards: its path
goes into the agent settings. If you do move it, repeat step 5.
2. Python 3.9 or newer
System | How to check | Where to get it |
macOS |
| comes with the Command Line Tools (installing |
Linux |
| your distribution's |
Windows |
| the installer from python.org, or the "embeddable package" zip, which unpacks without admin rights |
Nothing needs to be installed with pip.
3. Where the database is
The server reads the same environment variables as psql. Set them in the terminal you run
install.py from, and the script copies them into the server settings.
macOS and Linux:
export PGHOST=db.example.com PGPORT=5432 PGDATABASE=app PGUSER=mcp_agentWindows (PowerShell):
$env:PGHOST = "db.example.com"; $env:PGDATABASE = "app"; $env:PGUSER = "mcp_agent"For TLS with certificate checks add PGSSLMODE=verify-full and PGSSLROOTCERT=<path to the CA>.
All variables are listed under settings.
4. The password goes into the password file
The password lives only in PostgreSQL's password file, the same one psql reads. Never put it into
the agent settings and never send it in the chat.
macOS and Linux use ~/.pgpass:
nano ~/.pgpass # or any other editor
chmod 600 ~/.pgpass # otherwise the file is ignored: others could read itWindows uses %APPDATA%\postgresql\pgpass.conf:
mkdir "$env:APPDATA\postgresql" -Force
notepad "$env:APPDATA\postgresql\pgpass.conf"One line per connection: host:port:database:role:password. An asterisk matches any value; escape
a colon or a backslash inside a value as \: and \\.
db.example.com:5432:app:mcp_agent:password-from-the-DBA5. Register the server with the agent
python3 install.py --client qwen # Qwen Code, profile explore, language from the system locale
python3 install.py --client qwen --profile analyst --lang en # profile and language spelled out
py -3 install.py --client qwen # WindowsWhat the script does:
backs up the agent settings (
~/.qwen/settings.json.bak-<date>);adds the server
pgtomcpServers: the Python that runs the script, the path torun.py, thePG*variables from the terminal (exceptPGPASSWORD), the profile and the language;starts the server the way the agent will, calls
statusand prints the answer. Exit code 0 means the database is reachable.
python3 install.py --check only starts the server and calls status; it changes nothing.
This is the entry in ~/.qwen/settings.json after install.py --client qwen --profile analyst --lang en:
{
"mcpServers": {
"pg": {
"command": "/usr/bin/python3",
"args": ["-I", "/Users/me/pg-explorer-mcp/run.py"],
"timeout": 30000,
"env": {
"PGHOST": "db.example.com",
"PGDATABASE": "app",
"PGUSER": "mcp_agent",
"PG_EXPLORER_MCP_PROFILE": "analyst",
"PG_EXPLORER_MCP_LANG": "en"
}
}
}
}If settings.json contains comments, the script leaves it alone and prints the snippet to add by hand.
Several databases
One clone serves any number of databases: register each further one under its own name. Its entry becomes
pg-<name> next to pg, with its own connection, profile, language and query log:
PGDATABASE=sales python3 install.py --name sales
PGDATABASE=hr PGUSER=mcp_hr python3 install.py --name hr --profile schemaThe agent then gets the tools of every entry, and the status of each names its server and database.
Other agents and editors
--client registers the server with other MCP clients as well, in each one's own file and format:
| Where the entry goes |
|
|
| the user |
|
|
| Windsurf, now Devin Desktop: |
|
|
| the settings file of the Roo Code extension in VS Code (the extension is no longer developed) |
| a block file |
|
|
| the app's |
|
|
| nothing is written: AI Assistant keeps its servers in Settings, so the entry is printed to paste |
python3 install.py --client cursor # write into Cursor's file (with a backup) and check
python3 install.py --client vscode --print # only show the entry and where it goes--print suits a client this list lacks, or a file you would rather edit yourself. A file with comments
is never rewritten: install.py prints what to add instead. Run at a terminal, install.py notices the
other clients installed on this machine once it has registered the server and offers to add it there
too. The extension (the context and the /pg: commands) exists for Qwen Code only.
6. The extension
qwen extensions link /path/to/pg-explorer-mcpThe extension gives the agent a short context (where to start, never to ask for passwords in the chat,
to answer in the user's language), the /pg:setup command for installing and repairing the server,
/pg:import for bringing stand connections in from DBeaver or pg_service.conf, and /pg:diagram for
drawing a data model and saving it next to the project.
7. Check
Restart the agent so it starts the server with the new settings, and ask: "Check the database
connection". The agent calls status and tells you what it says.
Usage
Talk to the agent in plain words; it picks the tools.
You ask | The agent calls |
"Check the database connection" |
|
"Which tables are in the shop schema?" |
|
"Where are the customers stored?" |
|
"Describe the orders table" |
|
"Draw the data model of the shop" |
|
"Show me a few orders" |
|
"Why is this query slow: …" |
|
"How many orders are there per status?" |
|
Below are real answers of the tools on a demo database. The model reads them, and you see them in the agent's interface.
status:
pg-explorer-mcp 0.8.0 on Python 3.9.6, installed at /Users/me/pg-explorer-mcp
server name: pg
access: read only. Nothing can be inserted, changed or deleted through this server: decline such requests instead of asking the user to confirm them.
target: mcp_demo_agent@localhost:5432/pg_mcp_demo (sslmode=prefer)
profile: analyst (status, list_tables, search_schema, describe_table, schema_diagram, db_health, sample_rows, explain_query, run_select)
language: en
query log: off
database: connected to pg_mcp_demo as mcp_demo_agent, PostgreSQL 16.15
limits: read only = on, statement timeout = 5s, list_tables returns at most 200 entries, answers are cut at 8000 characterslist_tables: table comments help the model see what lives where.
shop.customers | table | ~50 rows | Покупатели
shop.order_items | table | ~2.2k rows | Позиции заказов: цена фиксируется на момент заказа
shop.orders | table | ~500 rows | Заказы
shop.products | table | ~20 rows | Каталог товаровsearch_schema with text: "customer" finds the table and a column of another table:
shop.customers | table | ~50 rows | Покупатели
shop.orders.customer_id integer NOT NULLdescribe_table for shop.orders:
shop.orders | table | ~500 rows | Заказы
columns:
id integer NOT NULL
customer_id integer NOT NULL
status text NOT NULL -- new, paid, shipped или cancelled
created_at timestamp with time zone NOT NULL
constraints:
orders_pkey: PRIMARY KEY (id)
orders_customer_id_fkey: FOREIGN KEY (customer_id) REFERENCES shop.customers(id)
orders_status_check: CHECK (status = ANY (ARRAY['new'::text, 'paid'::text, 'shipped'::text, 'cancelled'::text]))
referenced by:
shop.order_items: FOREIGN KEY (order_id) REFERENCES shop.orders(id)
indexes:
CREATE INDEX orders_created_idx ON shop.orders USING btree (created_at)
CREATE INDEX orders_customer_idx ON shop.orders USING btree (customer_id)
CREATE UNIQUE INDEX orders_pkey ON shop.orders USING btree (id)The comments come from the database itself, so they stay in whatever language they were written in.
schema_diagram with schema: shop answers with the source of a Mermaid diagram, which the agent shows
as a picture; columns: all adds every column with its comment, and table with depth draws one
table with its neighbours by foreign keys:
erDiagram
%% schema shop: 4 tables, 3 relationships
customers {
integer id PK
text email UK
}
order_items {
integer order_id PK, FK
integer product_id PK, FK
}
orders {
integer id PK
integer customer_id FK
}
products {
integer id PK
}
order_items }o--|| orders : order_id
order_items }o--|| products : product_id
orders }o--|| customers : customer_idsample_rows with columns: "id, status, created_at" and limit: 3:
shop.orders:
id | status | created_at
1 | shipped | 2026-01-01 09:00:00+02:00
2 | paid | 2026-01-01 16:00:00+02:00
3 | cancelled | 2026-01-01 23:00:00+02:00
(3 rows shown, there are more: narrow the query with WHERE, ORDER BY or LIMIT)An error is a hint as well: the model sees what to fix and tries again.
No column 'total' in shop.orders. Columns: id, customer_id, status, created_at.
DELETE is not allowed. Only read-only statements are: SELECT, WITH ... SELECT, VALUES, TABLE, EXPLAIN and SHOW.
No readable table or view named 'salaries' in schema 'hr'. Call list_tables to see what exists.The last line is about the hr schema, which the role has no rights to: the tools show none of its tables.
Catalog diagnostics
Ask "Check the database health" to call db_health. It takes an optional schema; with many stands,
stand is required and database selects one database. Without it, the tool checks the stand's databases
within one query budget. It is available in explore and analyst.
How to call it. After updating, restart the MCP server (or the client that starts it). Ask the
agent in plain language; it selects db_health and passes the arguments below. The stand, database
and schema names are examples: substitute names from your configuration.
Request | Connection mode |
|
"Check the health of the connected database" | Single database |
|
"Check the shop schema in the connected database" | Single database |
|
"Check database health on dev1" | Many stands |
|
"Check the orders database on dev1" | Many stands |
|
"Check the billing schema in the orders database on dev1" | Many stands |
|
For an MCP client that lets you call tools directly, select db_health and supply the JSON arguments
from the table. For example, the tool name and arguments of one call are:
{
"name": "db_health",
"arguments": {"stand": "dev1", "database": "orders", "schema": "billing"}
}On a single-database connection, only schema is accepted. On a stand connection, stand is required;
database and schema narrow the report. Use status and list_schemas to find configured stands and
schemas. If the tool is missing, check the installed version (0.9.0 or newer) and profile in status:
the schema profile does not offer it. Ask the administrator to select explore or analyst and restart
the MCP server after changing its configuration.
How to read the result. The first line records collection status for each check:
Status | Meaning |
| The check ran; this does not mean the database is healthy. |
| More than five entries matched; the section shows only the first five. |
| The check failed; the code identifies the database error, for example |
| The database connection was unavailable. |
| The check did not finish, or was not started after the time budget or connection failure. |
Sequence entries include unknown_values_in_scope: the count of sequences with unknown current values
across the selected scope, including entries outside the first five. state: no_sequence_privilege
means the current role cannot read the sequence value. If the report is truncated or a section says
limited, repeat the call with a narrower schema or database.
The report includes collection time, server version, primary/replica, track_counts and the database
statistics reset time; heap/index buffer counters and hit ratios; estimated live/dead rows, changes
since analyze and vacuum/analyze timestamps; indexes with no observed scans and equivalent index
candidates; sequence headroom; and table/index sizes. Physical partition leaves are listed separately,
without parent totals. Sizes include TOAST in the table component, not the table's indexes.
These are leads for investigation, not proof of the cause of a slowdown or instructions to drop indexes. Counters may cover an unknown period, can be reset per object and describe local replica activity; uptime is not their age. A buffer miss can still hit the OS cache. NULL means unknown, not zero. The report does not estimate bloat, read query texts, scan application rows or perform repairs.
Only readable relations are included; with schema-specific stand logins, the registry determines the
schema scope, as for the other catalog tools. Sequence checks also include sequences owned by visible
table columns. docs/role.sql does not grant sequence access: missing privileges appear explicitly.
A DBA can optionally run GRANT SELECT ON SEQUENCE app.orders_id_seq TO mcp_agent for chosen sequences.
Do not grant pg_monitor or pg_read_all_stats. Cached sequence values give a conservative headroom
estimate; descending sequences and CYCLE are handled separately. Unused sequences have unknown values.
Each section shows at most five objects and says when more exist. Sequence entries prioritize the
least known headroom and report how many values are unknown in the selected scope. A compact status
line precedes the details so truncation does not hide failed or unfinished checks. A denied or failed
check leaves the other sections available. Queries share a 15-second budget (also capped by the configured per-statement
timeout); connection and transport timeouts still apply. The normal response-size limit can further
truncate a report. Narrow schema or database for detail. See ADR 0009.
Data model diagrams
schema_diagram (and the /pg:diagram command of the extension) draws the structure of the database as a
Mermaid erDiagram. The answer is text, and GitHub, GitLab, Obsidian, VS Code and agent chats show it as a
picture.
What it can draw:
A whole schema (
schema): its tables, partitioned and foreign tables, the foreign keys among them, and the tables of other schemas that this schema refers to. Tables of other schemas that refer to this one are left out, so a table everybody refers to does not pull in the whole database.One table with its neighbours (
tableanddepthfrom 0 to 3, 1 by default): the tables within that many foreign keys of it, in either direction.The same on a stand: with many stands pass
standandschema; the first line of the diagram is a%%comment saying stand / database / schema.
How much of each table:
| What a table shows |
| the key columns: |
| every column, with its comment cut to 60 characters |
| only the table name; the relationships stay |
What a relationship line means:
Line | Foreign key |
| many to one, the key columns are |
| many to one, the key may be empty |
| one to one: the key is on the primary key or on unique columns |
The relationship is labelled with the key columns.
When it does not fit. An answer has a budget of 8000 characters (PG_EXPLORER_MCP_MAX_RESPONSE_CHARS).
A diagram either fits whole or is not sent: first the columns are left out, then the keys, and a
%% comment at the top says so. If even the tables and relationships do not fit, the answer says to draw a
part of it with table and depth.
Names. A table name with a dot, Cyrillic or a leading digit is put in quotes ("hr.employees",
"заказы"). A column name keeps letters, digits, _ and -, and gets a _ before a leading digit. A
type is one word: varchar(50), timestamptz, numeric without (10,2). Cyrillic column names need
Mermaid 11 or newer.
What it does not draw: views (they have no keys), data (no row is read), defaults and indexes. For a
column's exact type and the indexes of a table, ask for describe_table.
To keep the picture, ask the agent to save it: /pg:diagram writes the block into <schema>.md (or the
bare source into <schema>.mmd) next to the project, and mmdc -i <schema>.mmd -o <schema>.svg from
mermaid-cli turns it into an SVG. The reasoning is in ADR 0008.
Limits
What | How much |
Entries from | at most 200; pass |
Matches from | at most 100 |
Rows from | 10 by default, at most 50 |
Rows from | at most 200 |
Plan lines | at most 500 |
One value in an answer | at most 200 characters |
A whole tool answer | 8000 characters ( |
One query | 5 seconds ( |
Open connections of an entry for stands | at most 8 ( |
Stands in one call ( | at most 100, asked at the same time; the rows of |
A longer answer is cut at whole lines, and its last line says how much is shown. Local models run with small context windows, and an answer that is too long can end the agent's session.
Many stands
When there are dozens of stands (dev1 to dev50, ift1 to ift3), each with several databases and a
login per schema, an entry per connection would cost the model thousands of characters of tool
descriptions in every request. One entry serves them all instead: the tools are described once, and
each call names the stand and the schema.
1. The registry. Connections are kept in the pg_service.conf format of libpq, a section per schema
named stand.database.schema:
[dev7.demo.shop]
host=dev7-db.example.com
[dev7.demo.hr]
host=dev7-db.example.com
[ift2.demo.shop]
host=ift2-db.example.com
port=6432
user=shop_readeronly
hostis required;port(5432),dbname(the database part of the name),user(the schema name),sslmode,sslrootcertandconnect_timeout(10 seconds) are optional;hostmay list several hosts separated by commas, as libpq takes them:host=h1,h2with oneportfor both, orport=6544,6545with one per host. The server tries them in order and keeps the first that lets the login in, so a stand with a standby answers while its primary is down; when none does, the error names every host;a database whose sections all name one login (a single section is one) is read as a whole through that login:
schematakes any schema the database holds, andlist_schemasandlist_tablesshow what the login can read, as for one database; a database with a login per schema keeps to the schemas of the registry;passwords go into the password file, a line per login, just as for one database;
the server reads
~/.pg-explorer-mcp/stands.conf(on Windows%APPDATA%\pg-explorer-mcp\stands.conf, or the filePG_EXPLORER_MCP_STANDS_FILEnames) and your own~/.pg_service.conf; with equal names the first file wins. While the new folder has nostands.conf, the one pg-mcp 0.7.0 wrote to~/.pg-mcp/is read, and the import and the exports use that folder too. Sections named otherwise are ordinary psql services and stay out of it;psql reads the same sections:
psql service=dev7.demo.shop.
2. The entry.
PG_EXPLORER_MCP_STANDS='*' python3 install.py --name standsThis registers pg-stands next to pg. PG_EXPLORER_MCP_STANDS switches stands on: * takes every stand,
patterns such as dev*,ift2 take some. The check succeeds once the stands are found: a stand behind a
VPN is reached only when a tool needs it.
Importing connections. When the connections already live in DBeaver, in DataGrip or another
JetBrains IDE with database tools, or in a pg_service.conf file, let install.py bring them in:
python3 install.py import dbeaver # a preview: what would go where; nothing is written
python3 install.py import dbeaver --write # write the registry and the password file, register pg-stands
python3 install.py import datagrip # DataGrip, IntelliJ IDEA, PyCharm and the other JetBrains IDEs
python3 install.py import datagrip --project ~/IdeaProjects/shop # and the data sources of a project
python3 install.py import pg-service ~/.pg_service.conf --writeThe stand comes from the folder (DBeaver's folder, or the group of a JetBrains data source), then the
connection name, then the host, matched with the regular expression (dev|ift)[-_ ]?\d+
(--stand-pattern changes it); a separator between the letters and the number is dropped, so
DEV-27 is the stand dev27. A connection the pattern matches nowhere
takes its own name as the stand, IFT-Caldera becoming ift-caldera, and the preview says so. The
database is the connection's, from its URL first, and the section is named after its login: which
schemas that login reads the import cannot know, so the server reads such a database as a whole through
it, and a stand with a login per schema gets a section per login, as before. The preview lists
what would be written and what is left out and why; passwords are never shown. --write rewrites
~/.pg-explorer-mcp/stands.conf (adding to what is there, or replacing it with --prune), adds the passwords
to the password file without touching its other lines, keeps a dated backup of both files and
registers pg-stands; when anything fails, nothing is changed. DBeaver's saved passwords are read
from its own files while DBeaver keeps them under its default key; with its secure storage switched
on they stay unknown, and the preview says which logins need a line in the password file by hand.
The agent does the same with /pg:import, writing only after you confirm. A running server notices
a changed registry on its next call, so an import needs no restart; a registry file that holds a
password= line and is readable by other users gets a warning in status. A DBeaver connection with
several hosts (h1:6544,h2 with the port 6544, as DBeaver writes it, or a URL
jdbc:postgresql://h1:6544,h2:6544/db) keeps them all, and the password file gets a line per host,
since libpq matches a line against one host.
import datagrip reads the global data sources of each JetBrains IDE, from its newest version, and
DataGrip's projects in ~/DataGripProjects; the other IDEs keep data sources in the projects of your
code, and --project <folder> brings one of those in. A URL with several hosts
(jdbc:postgresql://h1:6544,h2:6545/db) keeps them all, and a host without a port of its own gets 5432,
as in the JDBC driver. The IDEs keep passwords in the system keychain or in KeePass, which the import
never reads: the preview marks those logins, and their lines go into the password file by hand. A
password, user or sslmode written in the URL itself is taken from there. Data sources that
install.py export datagrip wrote are not brought back.
A connection that DBeaver or a JetBrains IDE makes through an SSH tunnel is left out, and the preview
says so, naming the SSH server when the tool's files do (for the IDEs, the SSH configuration in the
project's .idea/sshConfigs.xml or in the IDE's settings): its host is the database's as the SSH server
sees it, often localhost, and pg-explorer-mcp opens no tunnels. To query such a stand, open the tunnel
yourself, say ssh -N -L 15432:<database host>:5432 <SSH server>, then add its section by hand with
host=localhost and port=15432, and its line to the password file with the same host and port. A
connection whose tunnel is switched off in the tool is brought in as usual.
Exporting connections to database tools. The other way round, install.py export puts the stands
into the tools you query them with yourself: a folder or group per stand, every connection logging in
with the password file. No password is copied anywhere.
python3 install.py export dbeaver # a preview: what would go where; nothing is written
python3 install.py export dbeaver --write
python3 install.py export pgadmin --write --stands 'dev*,ift2'Tool |
| What it writes | Then |
DBeaver |
|
| Restart DBeaver |
DataGrip and the JetBrains IDEs with database tools |
|
| Open the folder as a project |
pgAdmin 4 |
|
| Tools > Import/Export Servers > Import |
psql, pgcli |
| Sections of |
|
PostgreSQL extension of VS Code (Microsoft) |
|
| Reload the window |
SQLTools |
|
| Reload the window |
A stand with several hosts goes to psql, DBeaver and DataGrip with all of them. pgAdmin and the PostgreSQL extension have one port field, so they get the list when the hosts share a port and the first host otherwise; SQLTools connects to one host and gets the first.
An export replaces only the connections it brings, found by a stable id or by name, and keeps the
rest: what you made in the tool, and what an earlier export brought for other stands. Each changed
file keeps a dated copy and its permissions, and a file that is a link stays a link. Connections
DBeaver already has with the same host, port, database and user are left out, since the stands most
often come from DBeaver in the first place. In ~/.pg_service.conf every exported section sits under
the line # pg-explorer-mcp export: the next export rewrites only such sections, the server does not take them
for stands, and a section of the same name that you wrote yourself stays as it is while its stand is
left out. The TLS settings of a stand, sslmode and sslrootcert, go along. A settings file with
comments is left alone, and the export prints what to add to it by hand. The PostgreSQL extension of
Microsoft cannot read the password file: it asks for each password once and keeps it in the system
keychain. Importing the file into pgAdmin again adds the servers again, so remove the old ones first.
After import --write, and after registering an entry while stands exist, install.py at a terminal
looks for these tools on the machine and offers to export into them.
3. Use. Every tool takes stand. Reading data also takes schema: the server connects with that
schema's login and looks for unqualified names in it first. database is needed only when the schema
exists in several databases of the stand.
You ask | The agent calls |
"Which stands are there?" |
|
"Is dev7 reachable?" |
|
"Which schemas are on dev7?" |
|
"Where are the orders on dev7?" |
|
"Draw the data model of shop on dev7" |
|
"How many orders per status are in shop on dev7?" |
|
"On which dev stand is there an order 123?" |
|
"Which stands have a discount column in shop?" |
|
"How does shop on dev7 differ from dev12?" |
|
"Which dev stands differ from dev7 in shop?" |
|
Every answer starts with where it came from. Real answers on the demo database:
list_schemas with stand: dev7:
dev7 / demo: hr, shoprun_select with stand: dev7 and schema: shop:
dev7 / demo / shop
status | count
shipped | 174
paid | 162
cancelled | 90
new | 74
(4 rows)compare_schema with stand_a: dev7, stand_b: dev12 and schema: shop, where dev12 is a copy of the demo
database in which the price gained two digits, orders gained a note and a table of returns, and an index
was dropped:
dev7 / demo / shop vs dev12 / demo / shop
only on dev12:
shop.returns | table
differ:
shop.order_items.price: dev7 numeric(10,2) NOT NULL | dev12 numeric(12,2) NOT NULL
shop.orders.note only on dev12: text
shop.orders: index orders_customer_idx only on dev7: CREATE INDEX orders_customer_idx ON shop.orders USING btree (customer_id)compare_schema with stand_a: dev7, stands: dev* and schema: shop — the stands that match, each
against dev7; up to three stands get the full differences, more stands get one line of counts each:
dev7 / demo / shop vs 3 stands:
dev8 / demo / shop: same as dev7
dev9 / demo / shop: same as dev7
dev12 / demo / shop
only on dev12:
shop.returns | table
differ:
shop.order_items.price: dev7 numeric(10,2) NOT NULL | dev12 numeric(12,2) NOT NULL
shop.orders.note only on dev12: text
shop.orders: index orders_customer_idx only on dev7: CREATE INDEX orders_customer_idx ON shop.orders USING btree (customer_id)
same as dev7: 2; different: 1; not checked: 0The structure comes from the system catalog of each stand, read with the schema's own login in three
queries however many tables there are; the data is not read. A schema with no differences gets a single
line, such as identical: 1 table, 3 columns, and a schema the registry lists but one stand's database
lacks is an error, not a stand full of differences.
Without schema, list_tables, search_schema and describe_table cover the whole stand: the names
come from the system catalog, which every login can see, through one login per database, and only the
schemas of the registry are shown; a database with one login shows whatever that login can read. The data
stays behind each schema's own privileges: a login that cannot read a neighbouring schema gets
permission denied there.
4. Several stands at once. search_schema and run_select take stands instead of stand: stand
names separated by commas or a pattern, dev1,dev2,dev3 or dev*, at most 100 stands in one call. The
stands are asked all at the same time, so the call takes as long as the slowest stand, not the sum, and
the answer lists them in the order status does, a section per stand under its stand / database / schema line. A stand that is unreachable, has no login for the schema or rejects the query gets one line
saying so under its header, and the other stands still answer. Stands that answered with no rows, or no
matches, are folded into one line naming them, so the stand that has what was asked for stands out. The
200 rows of run_select and the 100 matches of search_schema are shared among the stands, at least 10
per stand, so the whole answer fits the budget. The call keeps to 25 of the client's 30 seconds: a stand
that has not answered by then is named as such, and the agent asks about it in another call. If the stand is
still running the query then, that call waits for it to end, no longer than the query timeout plus 5 seconds.
run_select with stands: dev*, schema: shop and SELECT count(*) FROM orders WHERE id = 123:
dev1 / demo / shop
count
1
(1 row)
dev2 / demo / shop
count
0
(1 row)
dev3 / demo / shop
Connecting to dev3-db.example.com:5432 timed out. The host may be behind a firewall or a VPN.A failure stays small. An unreachable stand or a broken login affects only the answers that need it,
and status with stand tries a second login when the server turned the first one down. Without
PG_EXPLORER_MCP_STANDS, or when no stand is found, the entry works with the PG* variables as before, and
status says why. At most 8 connections stay open (PG_EXPLORER_MCP_MAX_CONNECTIONS). A host that does not
answer is given up after 10 seconds and not tried again for the rest of that call, whatever else the
stand has on it; a second login of a database is tried only when the server answered and turned the
first one down. The reasoning is in ADR 0006.
Group profiles
The profile decides which tools the agent sees:
Profile | Tools | For |
|
| people who need the structure only: the agent sees no data rows at all |
| plus | developers; the default |
| plus | analysts who need their own SQL |
Set it at registration (python3 install.py --profile schema) or with PG_EXPLORER_MCP_PROFILE in the server's
env block. An unknown value turns on the strictest profile, schema, and status warns about it.
A profile is a convenience, not an access boundary: what can be read is decided by the role's
privileges in the database.
Language
The server speaks English (en) or Russian (ru): tool descriptions, answers, hints, refusals and
the output of install.py. The language is chosen like this:
the
PG_EXPLORER_MCP_LANGvariable (install.py --langwrites it);otherwise the system locale: the
LANGUAGE,LC_ALL,LC_MESSAGESandLANGvariables, and the regional settings on Windows;otherwise English.
status shows the current language. To change it, run python3 install.py --lang ru or edit
PG_EXPLORER_MCP_LANG in the settings, then restart the agent.
Tool and argument names, JSON-RPC protocol errors, the server log and PostgreSQL's own messages are not
translated; the language of the latter is set by the database's lc_messages.
Either way, the model is asked to answer in the user's language. If your group's model picks tools less
reliably from Russian descriptions, set PG_EXPLORER_MCP_LANG=en: it will still answer in Russian. The reasons
behind this design are in ADR 0004.
Security
Any change to data, structure, privileges, settings or other sessions is refused. There are three layers of defence, and each one holds even if the others miss something:
The PostgreSQL role is the real boundary: it has only
SELECTon the chosen schemas (a script for DBAs).A
READ ONLYtransaction for every query, always rolled back, with the statement timeout and the schema's search path set for that transaction alone: a connection pooler in transaction mode between the server and PostgreSQL (pgbouncer, Odyssey) changes nothing. A query goes out as a single statement, so PostgreSQL rejects a string such asSELECT 1; COMMIT; DROP ….A check of the query text lets only read-only statements through and refuses
INSERT,UPDATE,DELETE,MERGE,SELECT … INTO, row locks and functions with side effects (pg_terminate_backend,set_config,dblink,pg_sleepand others), telling the model why.
The full policy is in ADR 0002.
Everything the tools return goes into the model's context. With a local model inside your organisation
the data stays there, but pick the profile by the task: schema shows no data at all.
The server takes passwords from the password file, as
psql does, and only from the line for its own login.
Query log
To keep a journal of what the agent asked the database, set PG_EXPLORER_MCP_QUERY_LOG to an absolute file path
(~ is fine) before running install.py, or add it to the server's env block later:
export PG_EXPLORER_MCP_QUERY_LOG=~/pg-explorer-mcp-queries.jsonl
python3 install.pyEvery tool call adds one JSON line. These two are real, from the demo database:
{"time": "2026-10-22T13:54:20.570Z", "os_user": "anna", "pid": 95715, "python": "3.9.6", "role": "mcp_demo_agent", "database": "pg_mcp_demo", "tool": "run_select", "arguments": {"query": "SELECT status, count(*) FROM shop.orders GROUP BY status"}, "outcome": "ok", "rows": 4, "duration_ms": 12.6}
{"time": "2026-10-22T13:54:20.571Z", "os_user": "anna", "pid": 95715, "python": "3.9.6", "role": "mcp_demo_agent", "database": "pg_mcp_demo", "tool": "run_select", "arguments": {"query": "DELETE FROM shop.orders WHERE status = 'cancelled'"}, "outcome": "refused", "message": "DELETE is not allowed. Only read-only statements are: SELECT, WITH ... SELECT, VALUES, TABLE, EXPLAIN and SHOW.", "duration_ms": 0.0}outcomeisok,refused(by the SQL check),error(bad arguments or a PostgreSQL error),unavailable(no connection) orfailed(a bug in the server);rowsis how many rows the agent's own statement returned (sample_rows,run_select,explain_query);pythonis the version of Python the server runs on: the report below counts who runs which, which is what raising the minimum version would cost;a call over several stands (
stands) is still one line:standsmaps each stand to its outcome (ok,unavailable,error, orskippedwhen the call had no time left for it),rowsis the total, androleanddatabaseare empty, since every stand has its own;the rows themselves and the tools' answers are never written.
The file is created readable by its owner only, since literals in SQL may carry personal data, and a
symlink in its place is refused. It is opened anew for every line, so several agent sessions can share
it and logrotate can move it away. If the file cannot be written, tool calls keep working and
status says why.
To sum the log up for whoever looks after the group, run scripts/querylog_report.py with the files,
rotated .1 and .gz ones too, and, if needed, a UTC window of --since and --until (a date or a
date-time; a bare date in --until covers that whole day):
python3 scripts/querylog_report.py ~/pg-explorer-mcp-queries.jsonl ~/pg-explorer-mcp-queries.jsonl.1.gz --since 2026-10-20The report counts calls by tool, by OS user and, in stands mode, by stand (a call over several stands
counts on each), and the users on each version of Python; it gives the median and 95th percentile of duration_ms, the ten longest calls and every
call the SQL check refused, which is what an attempt to write looks like. Arguments go on one line cut to
80 characters, values of keys that look like a password are hidden, and lines that are not complete log
lines, say cut by a crash or by rotation, are skipped and counted:
Files: /home/anna/pg-explorer-mcp-queries.jsonl (13 lines, 1 broken)
Calls: 12 ok 8 error 1 refused 2 unavailable 1 failed 0
Period: 2026-10-22 09:12:03 .. 2026-10-22 16:41:05 UTC
Users: 2 Tools: 7
Duration: median 11.2 ms, 95th percentile 2003.5 ms
By tool
tool calls ok error refused unavailable failed problems
run_select 5 2 1 2 0 0 60.0%
list_tables 2 1 0 0 1 0 50.0%
describe_table 1 1 0 0 0 0 0.0%
explain_query 1 1 0 0 0 0 0.0%
sample_rows 1 1 0 0 0 0 0.0%
search_schema 1 1 0 0 0 0 0.0%
status 1 1 0 0 0 0 0.0%
By user
user calls queries rows refused
anna 7 1 4 2
boris 5 3 18 0
Longest calls (top 10)
duration_ms time user tool arguments
2003.5 2026-10-22 16:30:00 anna list_tables
48.3 2026-10-22 14:05:11 boris run_select query=SELECT c.name, sum(o.total) AS total FROM shop.orders o JOIN shop.custome…
35.0 2026-10-22 14:02:15 boris search_schema pattern=customer
…
Refused by the SQL check (2)
time user tool arguments
2026-10-22 13:54:20 anna run_select query=DELETE FROM shop.orders WHERE status = 'cancelled'
2026-10-22 16:41:05 anna run_select query=UPDATE shop.products SET price = price * 1.1--format json gives the same figures to scripts, --format csv one table, the breakdown that
--by user|tool|stand|day chooses.
The file lives on the user's machine, so it is a journal, not a tamper-proof audit. To audit data access,
log on the PostgreSQL server (pgaudit or log_statement); the server's connections carry
application_name = 'pg-explorer-mcp'. The reasoning is in ADR 0005.
Settings
All settings are environment variables in the env block of the pg server in the agent settings.
install.py copies them there from the terminal. Settings written by pg-mcp 0.7.0 and earlier name them
PG_MCP_*: those names keep working, the PG_EXPLORER_MCP_* one wins when both are set, and install.py
writes only the new ones.
Variable | Default | Meaning |
|
| database host, or several separated by commas, tried in turn; or the Unix socket directory (starts with |
|
| port; with several hosts, one for all of them or one per host, separated by commas |
| same as | database |
| the OS user name | role |
|
|
|
| — | CA certificate for |
|
| password file |
| — | works, but |
|
|
|
| from the locale |
|
|
| budget of one answer in characters, at least 500 |
|
| timeout of one query in milliseconds |
|
| detail of the stderr log: |
| — (off) | absolute path of the query log file |
|
| the entry's name that |
|
| seconds to wait for a new connection; once connected, the socket waits at least the query timeout plus 5 seconds |
| — (off) |
|
|
| the stands registry |
|
| your psql services; stands are read from it too |
|
| connections an entry for stands keeps open |
The budget counts characters. Russian text takes more tokens per character than English, so if the model's context is small, lower the budget rather than raise it.
For DBAs: a role for the agent
The docs/role.sql script creates a role that can only read the chosen schemas. Run it as a superuser or as the owner of the schemas, in the database the agent will read:
psql -d app -v role=mcp_agent -v password='…' -v schemas=sales,reports -v owner=app_owner -f docs/role.sqlParameter | Meaning |
| name of the new role; PostgreSQL reserves names that start with |
| its password; users put it into their password file |
| comma-separated schemas the agent may read |
| the role that creates tables in those schemas, so future tables become readable too |
The role gets LOGIN with no other attributes, at most 5 connections, default_transaction_read_only,
statement (5 s) and idle-in-transaction (60 s) timeouts, CONNECT on the database, USAGE on the
schemas, SELECT on all their tables, and default privileges on the owner's future tables.
Never grant this role pg_read_all_stats or pg_monitor (they show other sessions' query texts),
pg_read_server_files, pg_write_server_files, pg_execute_server_program, pg_signal_backend, or
membership in a role that owns tables.
Give each group its own role and its own set of schemas. For connections from outside, turn on TLS and
ask users to set PGSSLMODE=verify-full.
Troubleshooting
Start with python3 install.py --check, or ask the agent to run /pg:setup: status nearly always
says what is wrong.
What you see | What it means | What to do |
The agent has no | the agent was not restarted, or the entry is missing | restart the agent; check that |
"Nothing accepts connections at …" | wrong host or port, the database is down, no VPN | check |
"Host … cannot be resolved" | a typo in | fix |
"Connecting to … timed out" | a firewall or a VPN | check that the port is reachable from this machine |
"… is unreachable from this machine" | no route to the host: the VPN is down | connect the VPN |
"… accepted the connection, then closed it during the login" | the server refused the login, but its message was lost (Windows drops it on a reset); usually the password or | check the password file, then ask the DBA |
"Login as … failed" or "the server asks for a password" | no password in the password file, a wrong line, or file permissions | check the path, the line format and |
"Database … does not exist" | wrong database name | fix |
"The server refused the login for …" |
| ask the DBA |
"TLS with … failed" | the TLS mode does not match the server | check |
"The current role cannot read any table" | the role has no | ask the DBA: a role for the agent |
| the settings file has comments | add the printed snippet by hand |
An answer ends with "… answer cut to" | the answer budget kicked in | narrow the question, or raise |
| Python is missing or has another name |
|
"query log: …, not written: …" | the folder does not exist, no permission, a relative path, or a symlink in place of the file | fix |
"No stand … in the registry" | a typo, or the stand is missing from the registry or from |
|
"PG_EXPLORER_MCP_STANDS is set, but no stands were found" | no registry file, or its sections are not named | check the files the warning names |
The server logs to stderr; PG_EXPLORER_MCP_LOG_LEVEL=DEBUG adds detail. If the server does not answer the
check, install.py --check prints that log.
Updating and removing
To update a clone, run git pull in its folder and restart the agent. To update an unpacked archive, take
the new pg-explorer-mcp-<version>.zip from the
Releases page, delete the old folder, unpack the new
one in its place and restart the agent. Run install.py again only if the folder's path changed or
you changed Python, the profile or the language.
From pg-mcp 0.7.0 and earlier.
A clone needs only
git pulland a restart. The agent settings point at itsrun.py, the extension shows up under the new name by itself, and the oldPG_MCP_*variables, the stands registry in~/.pg-mcp/and earlier exports keep working.An archive now unpacks into
pg-explorer-mcp/rather thanpg-mcp/. Runinstall.pyfrom the new folder once: it rewrites the paths in the agent settings. Then link the extension again.Qwen Code keeps an extension linked before 0.8.0 in
~/.qwen/extensions/pg-mcpand refuses a new link while that folder is there. Delete it first;install.pynames it when it finds it.The server's connections now carry
application_name = 'pg-explorer-mcp': a DBA's filter onpg-mcpneeds the new name.
To remove, delete pg (and any pg-<name> entries) from mcpServers in the agent settings (backups of earlier settings sit next to
the file), run qwen extensions uninstall pg-explorer-mcp and delete the folder. An extension linked
before 0.8.0 is removed by deleting ~/.qwen/extensions/pg-mcp.
FAQ
Does it need internet access, pip or admin rights? No. Everything the server needs is in the repository; it only needs a network path to the database.
Is the pg-mcp package on PyPI this server? No, that is another project; this server was called
pg-mcp too until 0.8.0. It is not on PyPI and installs only from GitHub, as a clone or a release archive
(step 1): pip install pg-mcp and uvx pg-mcp install the other project.
Can the agent change anything in the database? No. Even if text inside the data tricks the model, the role's privileges, the read-only transaction and the text check stop the query.
Which PostgreSQL versions does it work with? CI tests it against PostgreSQL 13 to 18. Before 14,
a table PostgreSQL has never analyzed looks exactly like an empty one, so list_tables calls both
"empty or never analyzed".
Which MCP protocol versions does it speak? Revisions 2024-11-05 to 2025-11-25 (with the
initialize handshake) and 2026-07-28 (without it).
Which clients is it checked with? CI starts Qwen Code with the server registered by install.py: the
client must connect to the server and see the extension.
Will another MCP client do? Yes, any client that starts servers over stdio. install.py --client
writes the entry for VS Code, Cursor, Windsurf, Cline, Roo Code, Continue, LM Studio, Claude Desktop and
JetBrains (other agents and editors); for any other client,
install.py --client cursor --print shows the command, the arguments and the PG* variables to give it.
Why does the agent answer in the wrong language? The model chooses the language of its answers;
the server asks it to use the user's language. PG_EXPLORER_MCP_LANG sets the language of the tools themselves.
Development
Environment, checks, code layout and rules are in CONTRIBUTING.md. Decisions and their reasons are in docs/adr/, the version history is in CHANGELOG.md. The decisions are in English; CONTRIBUTING.md and the changelog are in Russian. How to report a vulnerability is in SECURITY.md.
License
pg-explorer-mcp is under the MIT license (LICENSE). The packages it ships unchanged in
src/pg_explorer_mcp/_vendor keep their own licenses, listed in THIRD_PARTY_NOTICES.md.
Available Tools
6 toolsdescribe_tableARead-only
Columns with types, NOT NULL, defaults and comments; keys, referencing tables, indexes.
Inputs: table (required), schema.
explain_queryARead-only
PostgreSQL's plan for one read-only query passed without EXPLAIN; the query is not run.
Inputs: query (required).
list_tablesARead-only
Tables, views and foreign tables the role can read, with row estimates and comments. At most 200; pass schema to narrow.
Inputs: schema.
sample_rowsARead-only
A few rows of a table or view, optionally some columns. Default 10, at most 50, in no order.
Inputs: table (required), limit, schema, columns.
search_schemaARead-only
Find tables, views and columns by a piece of name or comment; use it instead of paging list_tables. At most 100 matches.
Inputs: text (required), schema.
statusARead-only
Read-only server: database, role, profile and limits. Call it first, and when a tool reports a connection problem: it says what to fix.
Inputs: no named parameters.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 6 tools
The majority of tools are distinct, separating schema listing, searching, detailing, data sampling, and query planning. However, `list_tables` and `search_schema` share a similar purpose of object discovery, which could occasionally cause agent confusion.
The tool names exhibit a very consistent naming convention, generally adhering to the action_object format using snake_case. The naming pattern is immediately recognizable and predictable across the entire tool set.
With 6 tools, the surface is well-scoped for a read-only PostgreSQL introspection server. Each tool performs a distinct function, contributing meaningfully to database inspection without being overly verbose or incomplete.
The tool set provides comprehensive coverage for read-only operations, including health checks, schema discovery, structural inspection, data sampling, and query planning. Minor gaps remain but do not prevent an agent from performing core database tasks.
Maintenance
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Related MCP Servers
- AlicenseNot gradedqualityDmaintenanceProvides read-only access to PostgreSQL databases, enabling LLMs to inspect database schemas and execute read-only SQL queries within a secure transaction context.1MIT
- AlicenseNot gradedqualityDmaintenanceProvides read-only access to PostgreSQL databases, enabling LLMs to inspect database schemas and execute read-only SQL queries.MIT
- AlicenseNot gradedqualityBmaintenanceEnables secure read-only access to PostgreSQL databases, allowing users to list tables, query schemas, execute SELECT statements, and inspect table structures through natural language interactions.650 npm4MIT
- AlicenseNot gradedqualityDmaintenanceEnables read-only PostgreSQL database access with query, table listing, and schema inspection tools.ISC