n8500x/mcp-postgresql repository overview

⁠mcp-postgresql

A PostgreSQL root-cause-analysis MCP server. 59 read-only diagnostic tools, 4 composite RCA reports and 3 investigation prompts — enough for an agent to take "the database is slow" from symptom to a specific, evidenced root cause.

• 🔒 Read-only, enforced by PostgreSQL — every statement runs inside BEGIN TRANSACTION READ ONLY, so a write is rejected by the server, not by a regular expression. Writes, session control and EXPLAIN ANALYZE are separate, default-off switches.
• 🎯 Version-aware — PostgreSQL 13–17. Capabilities are probed per target, so a tool needing pg_stat_io (16+) or pg_stat_statements explains what to install rather than failing with a missing-relation error.
• ⏱️ Bounded — statement timeout, lock timeout, row caps, result budget. A diagnostic tool must never become the incident.
• 📦 Self-contained — sources, tests, dev stack, k8s manifests and docs are all baked in, so npm run selftest works on an air-gapped host with no registry and no checkout. ~459 MB, non-root. A lean build target (~191 MB) drops the dev tooling for production deployments.
• 🔌 No libpq coupling — the server speaks the PostgreSQL wire protocol through node-postgres and never shells out. (psql is included for humans debugging inside the container, not used by the server.)

⁠Run it

stdio (an MCP host spawns it):

docker run --rm -i \
  -e PG_URL="postgres://readonly_user:[email protected]:5432/appdb" \
  n8500x/mcp-postgresql

HTTP (one shared service for many agents; stateless, so replicas need no session affinity):

docker run -d -p 3000:3000 \
  -e MCP_TRANSPORT=http \
  -e PG_URL="postgres://readonly_user:[email protected]:5432/appdb" \
  n8500x/mcp-postgresql
# endpoint  http://localhost:3000/mcp
# readiness http://localhost:3000/healthz   (actually probes the database)

MCP host configuration:

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": ["run", "--rm", "-i", "-e", "PG_URL", "n8500x/mcp-postgresql"],
      "env": { "PG_URL": "postgres://readonly_user:[email protected]:5432/appdb" }
    }
  }
}

List the catalogue without connecting to anything:

docker run --rm n8500x/mcp-postgresql --list-tools

⁠Kubernetes — and getting a shell in the pod

Use MCP_TRANSPORT=http for any long-running deployment. The stdio default expects an MCP host to own the process and speak JSON-RPC over the pipe. A Kubernetes container has nothing on stdin, so the server hits EOF, exits 0, and the pod CrashLoopBackOffs with a successful exit code — leaving nothing to exec into. The server logs exactly that if it happens, rather than exiting silently.

Manifests are in k8s/⁠ (Deployment + Service, non-root UID 10001, read-only rootfs, readiness probe that actually queries the database):

kubectl create namespace mcp
kubectl -n mcp create secret generic mcp-postgresql \
  --from-literal=PG_URL='postgres://mcp_readonly:[email protected]:5432/appdb'
kubectl -n mcp apply -f k8s/

kubectl -n mcp exec -it deploy/mcp-postgresql -- bash

The image ships bash — alpine has only busybox sh, so --entrypoint=/bin/bash and kubectl exec … -- bash would otherwise fail with a runc "no such file or directory" that reads like a broken image. Also included: curl, both CLIs on PATH, and the full documentation baked in at /app/docs.

Once inside the pod:

mcp-harness --http http://127.0.0.1:3000/mcp doctor   # test the live server
mcp-harness call pg_server_info                       # or spawn a private one
curl -s localhost:3000/healthz
cat /app/docs/README.md

Locally, the same:

docker run --rm -it --entrypoint=/bin/bash n8500x/mcp-postgresql

⁠The database role

Full diagnostics need pg_monitor — without it PostgreSQL hides other sessions' query text and much of the statistics.

CREATE ROLE mcp_readonly LOGIN PASSWORD '…';
GRANT pg_monitor TO mcp_readonly;
GRANT CONNECT ON DATABASE appdb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
-- pg_monitor does NOT cover sequences; without this, pg_sequence_exhaustion
-- cannot read last_value and would report "not visible" rather than a false all-clear.
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO mcp_readonly;

Also install pg_stat_statements — it is the single most valuable input to any query-performance investigation:

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'  (needs a restart)
CREATE EXTENSION pg_stat_statements;

⁠How an investigation goes

"checkout API times out since 14:20"
   │
   ├─▶ pg_rca_snapshot            one call, twelve probes, whole board
   │     └─ 14 sessions blocked, root blocker idle in transaction
   │
   ├─▶ pg_blocking_tree           find the ROOT blocker (depth 0), not a victim
   │     └─ pid 4412, idle in transaction 22 min, holding 3 locks
   │
   ├─▶ pg_idle_in_transaction     confirm: which app, which last statement
   │     └─ application_name=checkout-worker
   │
   └─▶ root cause: the worker opens a transaction around an external HTTP call.
       Mitigation: terminate pid 4412. Fix: move the call outside the
       transaction; set idle_in_transaction_session_timeout as a backstop.

Three prompts — pg_rca_triage, pg_slow_query_analysis, pg_health_review — encode that method: the order to work in, what each signal means, and the failure mode to avoid (latching onto the first plausible thread).

⁠Tools

⚠️ marks tools that can change state; all are disabled unless explicitly enabled.

⁠Composite RCA — start here

⁠Inventory & orientation

⁠Live activity

⁠Locks & blocking

⁠Query statistics

⁠Execution plans

⁠Index health

⁠Storage & bloat

⁠Vacuum & wraparound

⁠Replication & WAL

⁠I/O, cache & checkpoints

⁠Configuration

⁠Ad-hoc SQL

⁠Administrative (gated)

⁠Configuration

⁠Connection

⁠Limits

⁠Safety switches — all default to off

Diagnosis never needs any of these.

⁠Transport, logging, output

⁠Trimming the tool surface

-e MCP_TOOLS_CATEGORIES=rca,activity,locks   # only these categories
-e MCP_TOOLS_ENABLE='pg_rca_*,pg_list_*'     # glob allow-list
-e MCP_TOOLS_DISABLE='pg_run_*'              # glob deny-list
-e MCP_TOOLS_READONLY_ONLY=true              # drop anything that can change state

⁠What is inside

A TypeScript monorepo, deliberately layered so a second backend is cheap:

Tags are YYYYMMDD-HHMMSS plus latest.

⁠Local development and testing

Clone the source⁠, then:

npm install
npm run build

⁠Bring up a database with real problems to find

dev/ starts PostgreSQL 17 with pg_stat_statements, seeded deliberately badly so every diagnostic tool has a known-positive case: 78 MB of bloat, an unindexed foreign key, duplicate/unused/invalid indexes, an int4 sequence at 92%, and stale statistics.

npm run dev:up          # start + seed (~30s)
npm run dev:workload    # generate query statistics for pg_top_queries
npm run dev:down        # stop and drop the volume

export PG_URL="postgres://mcp_readonly:[email protected]:55432/rcademo"

⁠Drive the server by hand

mcp-harness is a plain MCP client. Testing an MCP server through a chat client is ambiguous — a failure could be the server, the model's tool choice, or the host. Here every call is explicit.

npm run harness -- tools                      # list all 59 (filter: tools rca)
npm run harness -- describe pg_top_queries    # schema + annotations
npm run harness -- call pg_rca_snapshot       # the composite triage report
npm run harness -- call pg_top_queries limit=5 order_by=mean_time
npm run harness -- prompts
npm run harness -- prompt pg_rca_triage symptom="checkout is slow"
npm run harness -- repl                       # interactive session
npm run harness -- info                       # server identity + instructions

# against an HTTP deployment instead of spawning one:
npm run harness -- --http http://localhost:3000/mcp doctor

⁠The test commands

doctor is the one that earns its keep. Unit tests cannot tell you that pg_stat_checkpointer lost a column in PostgreSQL 17, or that an expression came out double precision where round() needs numeric. It found six such bugs on its first run against a real server — including a bloat estimator reading relpages, which is zero on a never-vacuumed table, so the most bloated table in the database reported "0 bytes".

To exercise the lock tools, open a real blocking transaction:

docker exec -it mcp-pg-dev psql -U postgres -d rcademo -f /dev/stdin < dev/make-blocking.sql
# then, from another terminal:
docker exec -it mcp-pg-dev psql -U postgres -d rcademo -c "UPDATE orders SET status='paid' WHERE id=1;"
npm run harness -- call pg_blocking_tree

⁠Testing on-prem / air-gapped, from inside the image

The image is self-contained: sources, tests, dev/, k8s/, scripts/, docs and the dev dependencies are all baked in, plus bash, psql, git, jq, curl and the docker CLI. An air-gapped host has no npm registry and no git checkout, so everything has to work from inside the artefact.

docker run --rm \
  -e PG_URL="postgres://mcp_readonly:[email protected]:5432/appdb" \
  --entrypoint npm n8500x/mcp-postgresql run selftest

Five stages, cheapest and most diagnostic first, degrading sensibly when there is no database to point at:

1. Unit tests (no database required)     ✓ 120 passed
2. Tool catalogue (no database required) ✓ 59 tools
3. Connectivity                          ✓ connected — PostgreSQL 17.10
4. Doctor — every read-only tool         ✓ 47 ok, 1 unavailable, 0 failed
5. Smoke scenario                        ✓ all 33 steps passed

Anything individually:

docker run --rm --entrypoint npm  n8500x/mcp-postgresql test
docker run --rm --entrypoint npm  n8500x/mcp-postgresql run lint
docker run --rm --entrypoint bash n8500x/mcp-postgresql -c 'mcp-harness doctor'

Podman is CLI-compatible throughout — substitute the binary name. The differences that bite:

podman run --rm -e PG_URL=... --entrypoint npm n8500x/mcp-postgresql run selftest
podman save n8500x/mcp-postgresql:latest -o mcp-postgresql.tar
podman-compose -f dev/docker-compose.yml up -d        # or: podman compose

# the k8s manifests, with no cluster:
podman kube play k8s/secret.example.yaml k8s/deployment.yaml
podman exec -it mcp-postgresql-pod-mcp-postgresql bash

SELinux: on RHEL-family hosts a bind mount without a relabel is unreadable by the container — initdb then runs none of the seed scripts and you get an empty database with nothing in the log to explain it. The dev compose file already carries :ro,z; do the same for any mount you add.

Rootless: the image runs as UID 10001 and writes nothing outside /tmp, so no --userns tricks are needed, and both published ports are above 1024.

npm run dev:up does not work from inside the container. Its compose file bind-mounts ./init, and those paths are resolved by the Docker daemon on the host — so even with /var/run/docker.sock mounted it would mount empty directories and seed nothing. Either point PG_URL at a PostgreSQL you already have, or extract the dev stack and run it on the host:

docker run --rm --entrypoint tar n8500x/mcp-postgresql -cf - dev | tar -xf -
docker compose -f dev/docker-compose.yml up -d

A lean target exists for production deployments that will never be tested in place — compiled output and production dependencies only:

docker build --target lean -t mcp-postgresql:lean .

⁠Air-gapped install

docker save n8500x/mcp-postgresql:latest -o mcp-postgresql.tar   # ~182 MB
docker load -i mcp-postgresql.tar                                # on the target

To extract just the app tree (~23 MB: compiled output plus production dependencies), on Linux — the tree contains npm workspace symlinks, which Windows cannot create without Developer Mode:

docker run --rm --entrypoint tar n8500x/mcp-postgresql:latest -cf - -C / app > app.tar
tar -xf app.tar -C /opt
PG_URL=... node /opt/app/packages/postgres/dist/main.js       # any Node 20+

Source: https://github.com/fdf3d186-88d5/docker/tree/main/mcp-postgresql⁠ Licence: MIT