pgvector – AWS CLOUD DBA

PostgreSQL is much more than a conventional relational database. With the right extensions, the same core engine can become a vector store, a time-series platform, a geospatial database, an analytical engine, an extension laboratory, or the data layer behind an AI agent.

In this hands-on lab, I run several PostgreSQL personalities side by side with Docker, then connect one of them to a secured PostgreSQL Model Context Protocol (MCP) server. The aim is not to declare one image “best.” It is to demonstrate just how broad the PostgreSQL ecosystem has become, and to show the Docker details that make a multi-image lab reliable.

Lab, not production blueprint: these examples prioritize learning and isolation. Before production use, add backups, monitoring, resource limits, TLS, a secrets manager, tested upgrades, and a deliberately designed high-availability architecture.

What we are building

Container	Image	Capability	Host port	Persistent volume
`postgres18_server`	`postgres:18`	Vanilla PostgreSQL baseline	5432	`pg18_vanilla_data`
`postgres_pgvector18`	`pgvector/pgvector:0.8.4-pg18-trixie`	Vector similarity search	5433	`pgvector18_data`
`postgres_timescale18_node1`	`timescale/timescaledb-ha:pg18`	Time-series plus vector extensions	5434	`timescale18_node1_data`
`postgres_timescale18_node2`	`timescale/timescaledb-ha:pg18`	Second independent TimescaleDB instance	5435	`timescale18_node2_data`
`postgres_pglayers18`	`ghcr.io/pglayers/pglayers-full:18`	Large extension catalogue	5436	`pglayers18_data`
`postgres_ai_exts17`	`postgresai/extended-postgres:17-0.7.0`	PostgresAI/DBLab extension set	5437	`postgresai17_data`
`postgres-mcp`	`postgres-mcp-server:latest`	Agent-safe database discovery and diagnostics	8899	None

Every database receives a unique host port and a unique volume. Inside the Docker network, however, every PostgreSQL container still listens on its normal container port, 5432.

Prerequisites and safety

Docker Desktop, or Docker Engine plus the Compose plugin on Linux.
Enough disk space for six independent database clusters.
At least 8 GB of RAM if several extension-heavy images will run together; stop containers you are not actively testing.
psql, pgAdmin, or DBeaver if you want to connect from the host.
Node.js 18 or newer, including npm/npx, on the computer where Claude Desktop runs the mcp-remote bridge.
Node.js 20 or newer if you rebuild or test the PostgreSQL MCP server source outside Docker.

All passwords and tokens below are placeholders. Generate new values for your own lab. Never paste a real MCP bearer token into a blog post, source repository, screenshot, or shared configuration file.

Set reusable lab variables (Add it to .bash_profile)

			
# Choose the bootstrap administrator used by the standard PostgreSQL images.
export POSTGRES_ADMIN_USER='my_admin_user'
# Replace this value before running the lab. Use a long, unique password.
export POSTGRES_ADMIN_PASSWORD='replace-with-a-long-random-password'
# This is the application database created during first initialization.
export POSTGRES_DATABASE='my_database'
# Wait up to two minutes for a container to accept PostgreSQL connections.
# Arguments: container name, database role, and database name.
wait_for_postgres() {
  container_name="$1"
  role_name="$2"
  database_name="$3"
  for attempt in $(seq 1 60); do
    if docker exec "$container_name" \
      pg_isready -U "$role_name" -d "$database_name" >/dev/null 2>&1; then
      return 0
    fi
    sleep 2
  done
  docker logs --tail 100 "$container_name"
  return 1
}
# Confirm that Docker is available before creating anything.
docker version
docker ps

		

Environment variables are convenient for a lab, but they remain visible to processes in the shell and can appear in container metadata. Use Docker secrets or your platform’s secret manager for production.

The Docker foundation that prevents most PostgreSQL problems

Create one private network and one volume per server

			
# Create a user-defined bridge network if it does not already exist.
docker network inspect postgres-lab >/dev/null 2>&1 || \
docker network create postgres-lab
# Create independent persistent storage for each PostgreSQL personality.
docker volume create pg18_vanilla_data
docker volume create pgvector18_data
docker volume create timescale18_node1_data
docker volume create timescale18_node2_data
docker volume create pglayers18_data
docker volume create postgresai17_data

		

Never share PGDATA. Mounting one data directory into two PostgreSQL containers can corrupt the cluster. A different image or major version does not make the files interchangeable.

Understand the three image-specific storage paths

Image family	Correct container mount target	Why
PostgreSQL 18, pgvector PG18, pglayers PG18	`/var/lib/postgresql`	PostgreSQL 18 stores the cluster below a version-specific subdirectory such as `/var/lib/postgresql/18/docker`.
TimescaleDB HA PG18	`/home/postgres/pgdata`	The HA image defines `PGDATA=/home/postgres/pgdata/data`. Mounting the parent preserves the version’s complete packaged data area.
PostgresAI Extended PostgreSQL 17	`/var/lib/postgresql/data`	This is the image’s declared PGDATA/VOLUME. The lab uses a fresh named volume with `volume-nocopy` so initialization begins in an empty target.

Common `docker run` parameters

Parameter	Purpose
`--detach`	Runs the container in the background and prints its container ID.
`--name NAME`	Assigns a stable name used by `docker exec`, logs, health checks, and Docker DNS.
`--network postgres-lab`	Places the container on the private user-defined network. Other containers can reach it by name.
`--publish 127.0.0.1:H:C`	Maps host port `H` to container port `C`, but only on host loopback. Use an SSH tunnel for remote access.
`--volume VOLUME:PATH`	Persists database files outside the writable container layer.
`--mount type=volume,source=V,target=PATH,volume-nocopy`	Uses the explicit mount syntax and prevents Docker from pre-populating a new volume with files already present at the image path.
`--env NAME=value`	Supplies initialization or runtime settings. `POSTGRES_*` initialization settings only apply when PGDATA is empty.
`--shm-size=1g`	Raises shared memory above Docker’s small default, useful for parallel queries and index builds.
`--health-cmd`	Defines the command Docker uses to test database readiness.
`--health-interval`	Controls how often Docker runs the health check.
`--health-timeout`	Limits how long one health check may run.
`--health-retries`	Sets how many consecutive failures make the container unhealthy.
`IMAGE`	Selects the exact PostgreSQL distribution and tag.
`postgres -c name=value`	Overrides the image command and passes a startup-only PostgreSQL setting directly to the server.

Related command-line flags and shell syntax

Flag or syntax	Purpose
`docker network inspect NAME`	Checks whether a named network already exists and displays its metadata.
`docker network create NAME`	Creates a user-defined network with built-in container-name DNS.
`docker volume create NAME`	Creates a Docker-managed persistent volume.
`docker exec --interactive`	Keeps standard input open so `psql` can read a heredoc or accept input.
`docker exec --tty`	Allocates a terminal for a human-driven interactive `psql` session.
`docker logs --follow`	Streams new log records until you press Ctrl+C.
`docker logs --tail N`	Shows only the newest `N` log lines.
`docker logs --since 2m`	Shows logs produced during the last two minutes.
`docker inspect --format TEMPLATE`	Extracts a selected value, such as health status, from Docker metadata.
`docker update --restart=unless-stopped`	Adds a restart policy to a verified container without recreating it.
`docker restart NAME`	Stops and starts an existing container, preserving its command, environment, mounts, and published ports.
`docker manifest inspect --verbose`	Displays the platforms and detailed manifest data published for an image tag.
`docker build --tag NAME .`	Builds the Dockerfile in the current directory and assigns the resulting image a name and tag.
`docker compose config`	Resolves variables and validates the Compose model before deployment.
`docker compose up --detach`	Creates or reconciles the Compose services and leaves them running in the background.
`psql -h HOST`	Selects the database host; omitting it normally uses a local Unix socket.
`psql -p PORT`	Selects the PostgreSQL TCP port.
`psql -U ROLE`	Selects the PostgreSQL login role.
`psql -d DATABASE`	Selects the database to connect to.
`psql -W`	Forces a password prompt before connecting.
`psql -c SQL`	Runs one SQL command and exits.
`psql -v ON_ERROR_STOP=1`	Makes scripted `psql` stop immediately when any statement fails.
`ssh -N`	Creates forwarding only and does not run a remote shell command.
`ssh -L LPORT:HOST:RPORT`	Forwards a local port through SSH to a host and port visible from the remote machine.
`ssh -i KEY`	Selects the private key used to authenticate to the remote host.
`openssl rand -hex N`	Generates `N` random bytes and encodes them as twice as many hexadecimal characters.
`[ -z "${VAR:-}" ]`	Tests safely whether a shell variable is unset or empty.
`${VAR:?MESSAGE}`	Stops the current command with `MESSAGE` when a required variable is unset or empty.
`chmod 600 FILE`	Allows only the file owner to read or write a secret-bearing configuration file on POSIX systems.
`>/dev/null 2>&1`	Suppresses both normal and error output from the idempotent network-existence check.
`COMMAND_A \|\| COMMAND_B`	Runs the second command only if the first command fails.
`wait_for_postgres CONTAINER ROLE DATABASE`	Calls the helper defined above, retrying `pg_isready` for up to two minutes and printing recent logs if startup fails.
`<<'SQL'`	Feeds a literal heredoc into `psql`; the quoted marker prevents shell expansion inside the SQL body.

I deliberately omit a restart policy during the first boot. Once a database is healthy, enable unless-stopped. This makes startup errors visible instead of hiding them inside a rapid restart loop.

Lab 1: Vanilla PostgreSQL 18—the baseline

The official PostgreSQL image is the control group for the lab: no third-party extensions, no custom process supervisor, and the standard PostgreSQL 18 data layout.

			
# Start vanilla PostgreSQL 18 on host port 5432.
# The database is reachable by other lab containers as postgres18_server:5432.
docker run --detach \
  --name postgres18_server \
  --network postgres-lab \
  --publish 127.0.0.1:5432:5432 \
  --volume pg18_vanilla_data:/var/lib/postgresql \
  --env POSTGRES_USER="$POSTGRES_ADMIN_USER" \
  --env POSTGRES_PASSWORD="$POSTGRES_ADMIN_PASSWORD" \
  --env POSTGRES_DB="$POSTGRES_DATABASE" \
  --shm-size=1g \
  --health-cmd='pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=12 \
  postgres:18
# Do not continue until the server accepts connections.
wait_for_postgres postgres18_server \
  "$POSTGRES_ADMIN_USER" "$POSTGRES_DATABASE"

		

			
# Review startup logs; press Ctrl+C to leave follow mode.
docker logs --follow postgres18_server
# In another terminal, inspect Docker's health result.
docker inspect --format '{{.State.Health.Status}}' postgres18_server
# Connect from inside the container; no host port is required here.
docker exec --interactive --tty postgres18_server \
  psql -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE"
# After the first healthy boot, enable automatic restart after host reboots.
docker update --restart=unless-stopped postgres18_server

		

Docker Compose alternative

Use this instead of the preceding docker run, not at the same time. Save it as compose.yaml.

			
# Compose specification for the vanilla PostgreSQL service.
services:
  db:
    image: postgres:18
    container_name: postgres18_server
    networks:
      - postgres-lab
    ports:
      - "127.0.0.1:5432:5432"
    environment:
      POSTGRES_USER: ${POSTGRES_ADMIN_USER}
      POSTGRES_PASSWORD: ${POSTGRES_ADMIN_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DATABASE}
    shm_size: 1gb
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"]
      interval: 10s
      timeout: 5s
      retries: 12
    volumes:
      - pg18_vanilla_data:/var/lib/postgresql
# Reuse the network created earlier.
networks:
  postgres-lab:
    external: true
# Reuse the volume created earlier instead of making a project-prefixed volume.
volumes:
  pg18_vanilla_data:
    external: true

		

Compose key	Purpose
`services` / `db`	Defines the application services and gives this PostgreSQL service its Compose-local name.
`image`	Selects the container image and tag.
`container_name`	Assigns the same stable Docker name used by the `docker run` example.
`networks`	Attaches the service to `postgres-lab`.
`ports`	Publishes host-loopback port 5432 to container port 5432.
`environment`	Passes the three shell variables into the container. Compose resolves the single-dollar expressions.
`shm_size`	Allocates 1 GB for the container’s `/dev/shm`.
`healthcheck.test`	Runs `pg_isready` through a container shell. Double dollar signs defer variable expansion to that shell.
`interval`, `timeout`, `retries`	Set the health-check cadence, per-check limit, and failure threshold.
`volumes`	Mounts the persistent volume at the PostgreSQL 18 parent data directory.
`external: true`	Tells Compose to reuse the pre-created network and volume rather than make project-prefixed replacements.

			
# Validate the Compose file, then start it in detached mode.
docker compose config
docker compose up --detach

After the service is healthy, add restart: unless-stopped beneath container_name, then apply the edited Compose model:

			
# Reconcile the service after adding the verified restart policy.
docker compose up --detach

Lab 2: pgvector—PostgreSQL as a vector database

The pgvector image extends the official PostgreSQL image with the vector data type, exact distance operations, and approximate indexes such as HNSW and IVFFlat. The pinned tag below provides pgvector 0.8.4 on PostgreSQL 18 and Debian Trixie.

			
# Start an independent pgvector cluster on host port 5433.
docker run --detach \
  --name postgres_pgvector18 \
  --network postgres-lab \
  --publish 127.0.0.1:5433:5432 \
  --volume pgvector18_data:/var/lib/postgresql \
  --env POSTGRES_USER="$POSTGRES_ADMIN_USER" \
  --env POSTGRES_PASSWORD="$POSTGRES_ADMIN_PASSWORD" \
  --env POSTGRES_DB="$POSTGRES_DATABASE" \
  --shm-size=1g \
  --health-cmd='pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=12 \
  pgvector/pgvector:0.8.4-pg18-trixie
# Wait for initialization before running extension SQL.
wait_for_postgres postgres_pgvector18 \
  "$POSTGRES_ADMIN_USER" "$POSTGRES_DATABASE"

		

			
# Create and exercise the extension in my_database. (Run as a single block command)
docker exec --interactive postgres_pgvector18 \
  psql -v ON_ERROR_STOP=1 \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" <<'SQL'
-- Extensions are installed per database, not once per server.
CREATE EXTENSION IF NOT EXISTS vector;
-- Create a tiny three-dimensional vector table.
CREATE TABLE IF NOT EXISTS vector_demo (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label text NOT NULL UNIQUE,
  embedding vector(3) NOT NULL
);
-- Insert the sample embeddings once; reruns update the existing labels.
INSERT INTO vector_demo (label, embedding)
VALUES
  ('alpha', '[1,0,0]'),
  ('beta',  '[0,1,0]'),
  ('gamma', '[0.8,0.2,0]')
ON CONFLICT (label) DO UPDATE
SET embedding = EXCLUDED.embedding;
-- The <-> operator returns Euclidean/L2 distance; smaller is closer.
SELECT label, embedding <-> '[1,0,0]' AS distance
FROM vector_demo
ORDER BY distance
LIMIT 3;
SQL
# Enable restart only after the health check succeeds.
docker update --restart=unless-stopped postgres_pgvector18

		

Lab 3: TimescaleDB—time-series and high-performance vectors

The TimescaleDB HA image combines PostgreSQL with TimescaleDB and other packaged extensions. It uses PGDATA=/home/postgres/pgdata/data, so the named volume is mounted at its parent, /home/postgres/pgdata, rather than the official image’s /var/lib/postgresql path.

Two containers do not automatically form a highly available cluster. The commands below create two independent instances for comparison and failover experiments. Patroni, a distributed configuration store, replication, and a routing layer require separate configuration.

			
# Start independent TimescaleDB instance 1 on host port 5434.
docker run --detach \
  --name postgres_timescale18_node1 \
  --network postgres-lab \
  --publish 127.0.0.1:5434:5432 \
  --volume timescale18_node1_data:/home/postgres/pgdata \
  --env POSTGRES_USER=postgres \
  --env POSTGRES_PASSWORD="$POSTGRES_ADMIN_PASSWORD" \
  --env POSTGRES_DB="$POSTGRES_DATABASE" \
  --shm-size=1g \
  --health-cmd='pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=12 \
  timescale/timescaledb-ha:pg18
# Start independent TimescaleDB instance 2 with a different port and volume.
docker run --detach \
  --name postgres_timescale18_node2 \
  --network postgres-lab \
  --publish 127.0.0.1:5435:5432 \
  --volume timescale18_node2_data:/home/postgres/pgdata \
  --env POSTGRES_USER=postgres \
  --env POSTGRES_PASSWORD="$POSTGRES_ADMIN_PASSWORD" \
  --env POSTGRES_DB="$POSTGRES_DATABASE" \
  --shm-size=1g \
  --health-cmd='pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=12 \
  timescale/timescaledb-ha:pg18
# Wait for both independent instances before executing SQL or enabling restarts.
wait_for_postgres postgres_timescale18_node1 postgres "$POSTGRES_DATABASE"
wait_for_postgres postgres_timescale18_node2 postgres "$POSTGRES_DATABASE"

		

			
# Verify and enable the TimescaleDB and pgvectorscale extensions on node 1.
docker exec --interactive postgres_timescale18_node1 \
  psql -v ON_ERROR_STOP=1 \
  -U postgres -d "$POSTGRES_DATABASE" <<'SQL'
-- Confirm that the required extension packages are available.
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('timescaledb', 'vector', 'vectorscale')
ORDER BY name;
-- Enable TimescaleDB in this database.
CREATE EXTENSION IF NOT EXISTS timescaledb;
-- CASCADE also enables pgvector when vectorscale requires it.
CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;
-- Create a simple time-series table.
CREATE TABLE IF NOT EXISTS sensor_readings (
  observed_at timestamptz NOT NULL,
  sensor_id text NOT NULL,
  temperature_c double precision NOT NULL
);
-- Convert the table to a TimescaleDB hypertable.
SELECT create_hypertable(
  'sensor_readings',
  by_range('observed_at'),
  if_not_exists => TRUE
);
SQL
# Enable restart policies after both instances report healthy.
docker update --restart=unless-stopped postgres_timescale18_node1
docker update --restart=unless-stopped postgres_timescale18_node2

		

Lab 4: pglayers Full—an extension-rich PostgreSQL 18

pglayers publishes PostgreSQL extensions as composable image layers and also provides a full profile containing more than fifty extensions. Although the project documents multi-architecture layer support, the live pglayers-full:18 tag resolved to Linux AMD64 only when this article was reviewed. Check the manifest again before using it on ARM64.

This image preloads many libraries that register background workers. PostgreSQL’s default worker limit is eight, while the pglayers test suite uses 64. The full profile also configures components around the canonical postgres role, so this lab intentionally keeps POSTGRES_USER=postgres. Because this walkthrough does not initialize DocumentDB, its internal PostgreSQL background worker is disabled to suppress the missing-role warning. This setting does not disable the separately preloaded MongoDB wire-gateway library.

			
# Start pglayers Full on host port 5436.
# max_worker_processes=64 prevents the extension workers exhausting the default pool.
# The DocumentDB worker is disabled until that extension is deliberately installed.
docker run --detach \
  --name postgres_pglayers18 \
  --network postgres-lab \
  --publish 127.0.0.1:5436:5432 \
  --volume pglayers18_data:/var/lib/postgresql \
  --env POSTGRES_USER=postgres \
  --env POSTGRES_PASSWORD="$POSTGRES_ADMIN_PASSWORD" \
  --env POSTGRES_DB="$POSTGRES_DATABASE" \
  --shm-size=1g \
  --health-cmd='pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=12 \
  ghcr.io/pglayers/pglayers-full:18 \
  postgres \
    -c max_worker_processes=64 \
    -c documentdb.enableBackgroundWorker=off
# Extension-rich images can take longer to initialize.
wait_for_postgres postgres_pglayers18 postgres "$POSTGRES_DATABASE"

		

			
# Check the live image architecture and database health.
docker manifest inspect --verbose \
  ghcr.io/pglayers/pglayers-full:18
docker inspect --format '{{.State.Health.Status}}' postgres_pglayers18
# Run the inspection SQL as one fail-fast script.
docker exec --interactive postgres_pglayers18 \
  psql -v ON_ERROR_STOP=1 \
  -U postgres -d "$POSTGRES_DATABASE" <<'SQL'
-- Confirm the expanded worker pool.
SHOW max_worker_processes;
-- Count and inspect the extension packages available in this image.
SELECT count(*) AS available_extensions
FROM pg_available_extensions;
SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;
SQL

		

			
# After verification, enable the restart policy from the shell.
docker update --restart=unless-stopped postgres_pglayers18

The full image makes extensions available; it does not mean every extension should be created in every database. Some extensions have background workers, database-role requirements, or mutual conflicts. Enable only what your experiment needs. To test DocumentDB, stop and recreate this container against the same named volume without the disabling -c option; command arguments cannot be changed by a simple restart. Then follow the project’s documented DocumentDB installation sequence in the configured postgres database.

Lab 5: PostgresAI Extended PostgreSQL 17

The postgresai/extended-postgres image is primarily designed for PostgresAI Database Lab workflows. Its default startup script expects an existing cluster and deliberately keeps the container alive if PostgreSQL stops. For a fresh standalone lab, appending postgres activates the inherited official initialization path. Mounting a brand-new named volume at the image’s declared PGDATA path with volume-nocopy guarantees an empty initialization target.

			
# Start the AMD64 PostgresAI PostgreSQL 17 image on host port 5437.
# Mount the image's declared PGDATA and prevent Docker from copying image-layer files.
# The final "postgres" argument is essential for first-run initialization.
docker run --detach \
  --name postgres_ai_exts17 \
  --network postgres-lab \
  --publish 127.0.0.1:5437:5432 \
  --mount type=volume,source=postgresai17_data,target=/var/lib/postgresql/data,volume-nocopy \
  --env POSTGRES_USER="$POSTGRES_ADMIN_USER" \
  --env POSTGRES_PASSWORD="$POSTGRES_ADMIN_PASSWORD" \
  --env POSTGRES_DB="$POSTGRES_DATABASE" \
  --shm-size=1g \
  --health-cmd='pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  --health-interval=10s \
  --health-timeout=5s \
  --health-retries=12 \
  postgresai/extended-postgres:17-0.7.0 \
  postgres
# Wait for the inherited PostgreSQL entrypoint to finish initialization.
wait_for_postgres postgres_ai_exts17 \
  "$POSTGRES_ADMIN_USER" "$POSTGRES_DATABASE"

		

			
# Inspect initialization before trying to use psql.
docker logs --tail 100 postgres_ai_exts17
docker inspect --format '{{.State.Health.Status}}' postgres_ai_exts17
# List a few useful extensions supplied by the image.
docker exec --interactive postgres_ai_exts17 \
  psql -v ON_ERROR_STOP=1 \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" <<'SQL'
-- See whether selected extension packages are available.
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('vector', 'hypopg', 'pg_stat_statements', 'timescaledb')
ORDER BY name;
-- Enable only the extensions needed by this database.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS hypopg;
SQL
# Enable automatic restart after a successful first boot.
docker update --restart=unless-stopped postgres_ai_exts17

		

If initdb reports that PGDATA “exists but is not empty,” do not delete files until you know what they are. Stop the container, inspect the volume, and use a new empty volume for a disposable lab. PostgreSQL will not initialize over unrelated or partial files.

Comparing the PostgreSQL personalities

Stack	Best suited to	Initialization	Architecture note	Main caution
Official PostgreSQL 18	Baseline relational and JSON workloads	Automatic on empty volume	Multi-architecture	Add extensions yourself
pgvector PG18	Embeddings and similarity search	`CREATE EXTENSION vector`	AMD64 and ARM64 tags available	One extension-focused image, not an AI platform by itself
TimescaleDB HA PG18	Time-series, telemetry, PostGIS, and vectorscale	Create/verify extensions per database	AMD64 and ARM64	Two containers are not automatically HA
pglayers Full PG18	Discovering and testing a wide extension catalogue	Create selected extensions per database	Current full tag: AMD64; verify the live manifest	Many preloaded workers; keep the `postgres` role and raise worker slots
PostgresAI Extended PG17	Database Lab and advanced extension experiments	Override the command with `postgres` for a fresh standalone cluster	Published tag is AMD64	Default startup assumes existing PGDATA

Connect with psql, pgAdmin, or DBeaver

From inside a database container, use the container’s normal port 5432—or omit the port entirely. From the host, use the mapped port from the lab table.

			
# From inside the vanilla container: local socket, no host port mapping involved.
docker exec --interactive --tty postgres18_server \
  psql -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE"
# From the Docker host: connect to the vanilla instance on host port 5432.
psql -h 127.0.0.1 -p 5432 \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" -W
# From the Docker host: connect to pgvector on its unique host port 5433.
psql -h 127.0.0.1 -p 5433 \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" -W
# From the Docker host: connect to pglayers on host port 5436.
psql -h 127.0.0.1 -p 5436 \
  -U postgres -d "$POSTGRES_DATABASE" -W

		

For pgAdmin or DBeaver, use host 127.0.0.1, the mapped host port, the configured database, and the matching user. On an EC2 host, keep Docker bound to loopback and use an SSH tunnel instead of opening every database port to the internet.

			
# Forward local laptop port 5432 securely to the EC2 host's loopback port 5432.
ssh -N \
  -L 5432:127.0.0.1:5432 \
  -i /absolute/path/to/key.pem \
  ec2-user@YOUR_EC2_HOST

		

Add the PostgreSQL MCP server

The Postgres MCP Server exposes schema discovery, object inspection, bounded SQL execution, query-plan diagnostics, index recommendations, workload analysis, database monitoring, and optional Prometheus metrics. One MCP process connects to one PostgreSQL database URI. For simultaneous targets, each additional MCP instance needs its own Docker container name, host port, database role, Claude configuration key and URL, plus an allowed-origin entry matching that URL.

Build the MCP image

			
# Clone the MCP server and enter its repository before building.
git clone https://github.com/shadabshaukat/postgres-mcp-server.git
cd postgres-mcp-server
# Option A: build an unchanged checkout from its tracked build output.
docker build --tag postgres-mcp-server:latest .

		

If you modify the TypeScript source, use the following validation-and-build path instead of the final build command above.

			
# Option B: install the locked dependencies, validate the source, and rebuild.
# If you edit TypeScript source, rebuild and test before rebuilding the image.
# These commands require Node.js 20 or newer on the host.
npm ci
npm run check
npm run test:unit
npm run build
docker build --tag postgres-mcp-server:latest .

		

Create a least-privilege database role

MCP_DB_MODE=restricted adds application-level safeguards, but PostgreSQL privileges remain the real security boundary. Do not connect the MCP service as a superuser.

			
# Generate a fresh URL-safe database password and keep it in this private shell.
# Hexadecimal output contains no URI delimiter characters.
export MCP_DB_PASSWORD="$(openssl rand -hex 24)"
# Stop before creating the role if OpenSSL failed.
: "${MCP_DB_PASSWORD:?OpenSSL did not generate an MCP database password}"
# Create the MCP role and grants as one fail-fast script.
# psql safely quotes the password and database-name variables in the SQL below.
docker exec --interactive postgres18_server \
  psql -v ON_ERROR_STOP=1 \
  -v mcp_password="$MCP_DB_PASSWORD" \
  -v target_db="$POSTGRES_DATABASE" \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" <<'SQL'
-- Create a login dedicated to MCP read access.
CREATE ROLE mcp_reader
  WITH LOGIN
  PASSWORD :'mcp_password';
-- Allow the role to connect to this database and inspect the public schema.
GRANT CONNECT ON DATABASE :"target_db" TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
-- Grant read access to current tables.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
-- Grant read access to future tables created by this administrator.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_reader;
SQL

		

Roles are local to a PostgreSQL cluster. Before pointing MCP at pgvector, TimescaleDB, pglayers, or PostgresAI, repeat the dedicated-role and grant step in that target cluster with its administrator and database name. Do not merely change the hostname in the URI.

Optional: grant deeper MCP observability

The least-privilege role above can inspect schemas and selected data, but some workload and monitoring tools will return partial results. The vanilla image does not preload pg_stat_statements, and ordinary roles cannot see every session’s query text. If that wider visibility is acceptable in your lab, enable it explicitly:

			
# Configure the bundled statistics library; this setting needs a restart.
docker exec --interactive postgres18_server \
  psql -v ON_ERROR_STOP=1 \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" <<'SQL'
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SQL
# Restart so PostgreSQL can preload the library.
docker restart postgres18_server
# Wait until PostgreSQL accepts connections before running the next SQL script.
wait_for_postgres postgres18_server \
  "$POSTGRES_ADMIN_USER" "$POSTGRES_DATABASE"
# Create the extension and grant the broad built-in monitoring role.
docker exec --interactive postgres18_server \
  psql -v ON_ERROR_STOP=1 \
  -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_DATABASE" <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT pg_monitor TO mcp_reader;
SQL

		

pg_monitor exposes cluster-wide monitoring information, so grant it only after reviewing that visibility. HypoPG is not packaged in the vanilla image; MCP can still recommend indexes there, but hypothetical-index validation remains unavailable unless you choose an image that supplies HypoPG.

Run the MCP server on the private Docker network

			
# Generate a fresh 64-character bearer token for this MCP instance.
# Keep it out of shell history, recordings, screenshots, and source control.
export POSTGRES_MCP_TOKEN="$(openssl rand -hex 32)"
# Stop immediately if OpenSSL failed and left the token empty.
: "${POSTGRES_MCP_TOKEN:?OpenSSL did not generate an MCP token}"
# Start a restricted, bearer-authenticated Streamable HTTP MCP endpoint.
# Docker DNS resolves postgres18_server directly on the private network.
docker run --detach \
  --name postgres-mcp \
  --network postgres-lab \
  --publish 127.0.0.1:8899:8899 \
  --read-only \
  --tmpfs /tmp \
  --security-opt no-new-privileges:true \
  --env "DATABASE_URI=postgresql://mcp_reader:${MCP_DB_PASSWORD:?MCP_DB_PASSWORD is not set}@postgres18_server:5432/${POSTGRES_DATABASE:?POSTGRES_DATABASE is not set}?sslmode=disable" \
  --env PGSSLMODE=disable \
  --env MCP_TRANSPORT=http \
  --env MCP_HTTP_HOST=0.0.0.0 \
  --env MCP_HTTP_PORT=8899 \
  --env MCP_HTTP_PATH=/mcp \
  --env MCP_DB_MODE=restricted \
  --env "MCP_AUTH_TOKEN=${POSTGRES_MCP_TOKEN:?POSTGRES_MCP_TOKEN is not set}" \
  --env 'MCP_ALLOWED_HOSTS=localhost,127.0.0.1' \
  --env 'MCP_ALLOWED_ORIGINS=http://localhost:8899,http://127.0.0.1:8899' \
  postgres-mcp-server:latest

		

Claude needs the same bearer token. While this private shell or SSH session is still open, transfer it directly into your password manager or secure clipboard. If you must display it, do so once in a private, non-recorded terminal and clear the terminal scrollback afterward:

			
# Display the token only in a private terminal so it can be copied to Claude.
printf '%s\n' "$POSTGRES_MCP_TOKEN"

MCP parameter reference

Parameter	Purpose
`--network postgres-lab`	Lets the MCP container reach the selected database by container name and internal port 5432.
`--publish 127.0.0.1:8899:8899`	Exposes MCP only on host loopback. It is not directly reachable from the network.
`--read-only`	Makes the MCP container filesystem read-only.
`--tmpfs /tmp`	Provides a temporary writable in-memory directory required by some runtime operations.
`--security-opt no-new-privileges:true`	Prevents processes from gaining additional Linux privileges.
`DATABASE_URI`	Selects exactly one PostgreSQL target. Use container DNS and port 5432 on the shared network.
`PGSSLMODE=disable`	Disables TLS only for this trusted, private container network. Use certificate verification for remote databases.
`MCP_TRANSPORT=http`	Enables Streamable HTTP. The legacy value `sse` is only an alias; legacy SSE endpoints require a separate opt-in.
`MCP_HTTP_HOST=0.0.0.0`	Listens on all interfaces inside the container. The host-side publish remains safely bound to 127.0.0.1.
`MCP_HTTP_PORT=8899`	Sets the HTTP listener port inside the container.
`MCP_HTTP_PATH=/mcp`	Sets the Streamable HTTP MCP endpoint path.
`MCP_DB_MODE=restricted`	Enables read-oriented SQL inspection, read-only transactions, row limits, and timeouts.
`MCP_AUTH_TOKEN`	Sets the static Bearer token. The server requires at least 16 characters.
`MCP_ALLOWED_HOSTS`	Restricts accepted HTTP Host values when the internal listener is non-loopback.
`MCP_ALLOWED_ORIGINS`	Restricts browser-style Origin values when an Origin header is present.

			
# Confirm the MCP process, database connection, and readiness endpoint.
docker logs postgres-mcp
curl http://127.0.0.1:8899/healthz
curl http://127.0.0.1:8899/readyz
# Enable automatic restart only after readiness succeeds.
docker update --restart=unless-stopped postgres-mcp

		

To point MCP at another lab server, first create mcp_reader and its grants in that cluster, then recreate the MCP container with that target’s hostname, database, and generated password in DATABASE_URI. The private-network port remains 5432. A simultaneous second MCP target also needs a unique --name, a different host-side published port, a matching allowed origin, and a distinct client configuration entry.

Configure Claude Desktop

Claude Desktop starts the community mcp-remote bridge as a local process and forwards it to the loopback-only Streamable HTTP endpoint. The example pins version 0.1.38, verified when this article was reviewed, instead of downloading an unspecified future release. Replace the placeholder with the token generated above; the value must include the Bearer prefix.

			
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote@0.1.38",
        "http://127.0.0.1:8899/mcp",
        "--allow-http",
        "--transport",
        "http-only",
        "--header",
        "Authorization:${AUTH_HEADER}"
      ],
      "env": {
        "AUTH_HEADER": "Bearer REPLACE_WITH_THE_GENERATED_TOKEN"
      }
    }
  }
}

		

-y allows npx to install/run the pinned bridge without an interactive confirmation.
--allow-http is acceptable here only because the endpoint is local loopback.
--transport http-only selects Streamable HTTP.
--header adds the required Authorization header.
If Claude cannot locate npx, replace it with the absolute path returned by command -v npx.

Restrict the Claude configuration file to your account where the operating system supports POSIX permissions—for example, chmod 600 "$HOME/Library/Application Support/Claude/claude_desktop_config.json" on macOS. The token is still plaintext in that file, so do not share it. Completely quit and reopen Claude Desktop after changing the configuration. A cloud-hosted connector cannot reach 127.0.0.1 on your computer; this is a local Claude Desktop configuration.

MCP on EC2: tunnel it instead of publishing it

			
# Run this on the laptop that hosts Claude Desktop.
# It maps laptop port 8899 to the EC2 host's loopback-only MCP endpoint.
ssh -N \
  -L 8899:127.0.0.1:8899 \
  -i /absolute/path/to/key.pem \
  ec2-user@YOUR_EC2_HOST

		

Claude still connects to http://127.0.0.1:8899/mcp. Do not open port 8899 in the EC2 security group merely to make the demo reachable.

Troubleshooting playbook

Symptom	Likely cause	Fix
`port is already allocated`	Two containers publish the same host port.	Use the unique host-port map in this article. Container port 5432 remains unchanged.
`initdb: directory exists but is not empty`	The mounted PGDATA contains files, perhaps from an earlier or incorrect mount.	Inspect it first. For a disposable lab, create a new empty volume.
Missing `.s.PGSQL.5432` socket	PostgreSQL did not finish starting.	Run `docker logs --tail 200 CONTAINER`; do not treat a running container as proof of a running database.
Host port is listening but PostgreSQL refuses connections	Docker published the port even though the database process failed.	Check container health, logs, and `pg_isready`.
pglayers repeatedly says to increase `max_worker_processes`	The full profile exhausted PostgreSQL’s default worker pool.	Start it with `postgres -c max_worker_processes=64`.
pglayers reports `role "postgres" does not exist`	A custom `POSTGRES_USER` replaced the canonical bootstrap role while bundled workers expect `postgres`.	For a fresh full-profile lab, initialize with `POSTGRES_USER=postgres`.
DocumentDB worker role warning	The DocumentDB library is preloaded but its extension has not created the required role.	Create DocumentDB in its configured database or disable that background worker if DocumentDB is not part of the test.
PostgresAI container is up but PostgreSQL is not	The default DBLab-oriented script expects initialized PGDATA.	Use an empty PG17 volume and append `postgres` to the image command.
Changed `POSTGRES_USER` or `POSTGRES_DB` has no effect	The volume already contains an initialized cluster.	Change roles/databases with SQL, or initialize a new empty volume.
MCP cannot reach PostgreSQL	The URI uses `localhost` inside the MCP container.	Use the shared Docker network and the PostgreSQL container name.
MCP returns HTTP 401	The bearer token is missing, stale, or lacks the `Bearer` prefix.	Use the same generated token in the container and client header.
Claude rejects its configuration	Malformed JSON, missing closing brace, or unavailable `npx`.	Validate the JSON and use an absolute `npx` path if necessary.

Production hardening checklist

Pin immutable image tags or digests and test upgrades before deployment.
Use a secret manager rather than plaintext environment variables.
Bind database and MCP ports to private interfaces; prefer SSH tunnels, private networks, or VPN access.
Use TLS with hostname and certificate verification for remote PostgreSQL endpoints.
Give MCP a dedicated least-privilege PostgreSQL role; keep restricted mode enabled.
Do not enable EXPLAIN ANALYZE or unrestricted MCP mode without understanding that queries or writes will execute.
Add tested backups, restore drills, monitoring, WAL management, disk alerts, and capacity limits.
Do not call two standalone TimescaleDB containers “HA” until replication, leader election, routing, and failover have been configured and tested.
Prefer a deliberately composed extension image over an everything-enabled bundle for production.

Conclusion

This lab demonstrates why PostgreSQL earns the “Swiss Army knife” description. The core database remains familiar, while extensions change the workload it can address: pgvector adds similarity search, TimescaleDB adds time-series behavior, pglayers turns extension discovery into a composable workflow, PostgresAI packages a broad Database Lab toolset, and MCP makes PostgreSQL safely inspectable by AI clients.

The real lesson is not only PostgreSQL’s flexibility. It is that Docker isolation matters: unique ports, unique volumes, image-correct PGDATA paths, explicit health checks, canonical roles where an image expects them, and least-privilege connections. Get those foundations right and the PostgreSQL ecosystem becomes an unusually capable platform for experimentation.