# 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

# 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

# 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

# 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

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

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

# 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

# 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

# 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

# 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

# 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

# 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

# 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 .

# 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 .

# 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

# 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

# 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

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

# 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

{
  "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"
      }
    }
  }
}

# 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