<aside> ✅

唯一事实源：全教程统一采用 PostgreSQL 18.4 + pgvector 0.8.2。以下镜像、表结构和种子数据替代各章零散的初始化片段。

</aside>

版本矩阵

目录结构

pg-lab/
├── compose.yaml
├── secrets/postgres_password.txt
└── sql/
    ├── 00_setup.sql
    ├── 01_seed.sql
    └── 99_reset.sql

compose.yaml

services:
  postgres:
    image: pgvector/pgvector:0.8.2-pg18
    container_name: pg-lab
    environment:
      POSTGRES_DB: pg_lab
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password
    secrets:
      - postgres_password
    ports:
      - "5433:5432"
    volumes:
      - pg18_data:/var/lib/postgresql/data
      - ./sql:/docker-entrypoint-initdb.d:ro
    command:
      - postgres
      - -c
      - shared_preload_libraries=pg_stat_statements
      - -c
      - compute_query_id=on
      - -c
      - track_io_timing=on
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d pg_lab"]
      interval: 5s
      timeout: 3s
      retries: 20

secrets:
  postgres_password:
    file: ./secrets/postgres_password.txt

volumes:
  pg18_data:

<aside> 🔐

实验密码文件不得提交到 Git。生产环境应使用密钥管理服务、TLS、最小权限和密码轮换；不要暴露 5432/5433 到公网。

</aside>

00_setup.sql：结构与约束

\set ON_ERROR_STOP on
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE SCHEMA IF NOT EXISTS sales;
CREATE SCHEMA IF NOT EXISTS kb;

CREATE TABLE IF NOT EXISTS sales.customer (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL UNIQUE,
  tenant_id bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS sales.product (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  name text NOT NULL,
  price numeric(12,2) NOT NULL CHECK (price >= 0),
  attrs jsonb NOT NULL DEFAULT '{}'::jsonb,
  tags text[] NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS sales.orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES sales.customer,
  status text NOT NULL CHECK (status IN ('pending','paid','cancelled')),
  ordered_at timestamptz NOT NULL DEFAULT now(),
  total_amount numeric(14,2) NOT NULL DEFAULT 0 CHECK (total_amount >= 0)
);

CREATE TABLE IF NOT EXISTS sales.order_item (
  order_id bigint NOT NULL REFERENCES sales.orders ON DELETE CASCADE,
  line_no integer NOT NULL CHECK (line_no > 0),
  product_id bigint NOT NULL REFERENCES sales.product,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, line_no)
);

CREATE TABLE IF NOT EXISTS kb.document (
  document_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  title text NOT NULL,
  source_uri text,
  model_version text NOT NULL DEFAULT 'deterministic-384-v1',
  content_hash text NOT NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active','deleted')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, content_hash)
);

CREATE TABLE IF NOT EXISTS kb.chunk (
  chunk_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL
    REFERENCES kb.document(document_id) ON DELETE CASCADE,
  chunk_no integer NOT NULL,
  content text NOT NULL,
  embedding vector(384) NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS
    (to_tsvector('simple', content)) STORED,
  UNIQUE (document_id, chunk_no)
);

CREATE INDEX IF NOT EXISTS idx_orders_customer_time
  ON sales.orders (customer_id, ordered_at DESC);
CREATE INDEX IF NOT EXISTS idx_product_attrs_gin
  ON sales.product USING gin (attrs);
CREATE INDEX IF NOT EXISTS idx_chunk_fts
  ON kb.chunk USING gin (search_vector);

01_seed.sql：确定性数据

\set ON_ERROR_STOP on
INSERT INTO sales.customer(name,email,tenant_id) VALUES
  ('Alice','[email protected]',1),
  ('Bob','[email protected]',2)
ON CONFLICT (email) DO NOTHING;

INSERT INTO sales.product(sku,name,price,attrs,tags) VALUES
  ('PG-BOOK','PostgreSQL 实战',99.00,'{"level":"advanced"}','{database,postgresql}'),
  ('AI-BOOK','向量检索实战',129.00,'{"level":"advanced"}','{ai,vector}')
ON CONFLICT (sku) DO NOTHING;

WITH c AS (
  SELECT customer_id FROM sales.customer WHERE email='[email protected]'
)
INSERT INTO sales.orders(customer_id,status,total_amount)
SELECT customer_id,'paid',228.00 FROM c
WHERE NOT EXISTS (
  SELECT 1 FROM sales.orders o
  WHERE o.customer_id=c.customer_id
    AND o.status='paid' AND o.total_amount=228.00
);

WITH target_order AS (
  SELECT min(o.order_id) AS order_id
  FROM sales.orders o
  JOIN sales.customer c USING(customer_id)
  WHERE c.email='[email protected]'
    AND o.status='paid' AND o.total_amount=228.00
), lines(line_no,sku) AS (
  VALUES (1,'PG-BOOK'),(2,'AI-BOOK')
)
INSERT INTO sales.order_item(order_id,line_no,product_id,quantity,unit_price)
SELECT o.order_id,l.line_no,p.product_id,1,p.price
FROM target_order o CROSS JOIN lines l
JOIN sales.product p ON p.sku=l.sku
ON CONFLICT DO NOTHING;

INSERT INTO kb.document(tenant_id,title,source_uri,content_hash)
VALUES (1,'PostgreSQL 18 实验手册','local://pg18-lab','sha256:pg18-lab-v1')
ON CONFLICT (tenant_id,content_hash) DO NOTHING;

WITH d AS (
  SELECT document_id FROM kb.document
  WHERE tenant_id=1 AND content_hash='sha256:pg18-lab-v1'
)
INSERT INTO kb.chunk(document_id,chunk_no,content,embedding)
SELECT d.document_id, x.chunk_no, x.content,
       ARRAY(
         SELECT CASE WHEN i=x.hot_dim THEN 1.0 ELSE 0.0 END
         FROM generate_series(1,384) AS i
       )::vector
FROM d
CROSS JOIN (VALUES
  (1,'PostgreSQL 使用 MVCC 提供一致性读',1),
  (2,'HNSW 适合低延迟近似向量检索',2),
  (3,'WAL 支持崩溃恢复与物理复制',3)
) AS x(chunk_no,content,hot_dim)
ON CONFLICT (document_id,chunk_no) DO NOTHING;

启动、验证、停止与彻底复位

docker compose up -d
docker compose ps
docker compose exec postgres psql -U postgres -d pg_lab -c   "SELECT version(), extversion FROM pg_extension WHERE extname='vector';"

# 保留数据停止
docker compose stop
docker compose start

# 删除容器但保留命名卷
docker compose down

# 彻底删除实验数据；不可恢复
docker compose down -v

99_reset.sql：逻辑复位

\set ON_ERROR_STOP on
DROP SCHEMA IF EXISTS sales CASCADE;
DROP SCHEMA IF EXISTS kb CASCADE;
\ir 00_setup.sql
\ir 01_seed.sql

运行：

docker compose exec -T postgres psql -U postgres -d pg_lab   -f /docker-entrypoint-initdb.d/99_reset.sql

验收查询