# PostgreSQL Database Migration Runbook

项目：`coding/comi-logic/autumn/server`

目标：将当前旧 PostgreSQL 数据库迁移到新的 PostgreSQL 数据库，并把 Autumn Server 的运行配置切换到新库。

本文不保存数据库密码。执行时用本机 shell 变量或密钥系统注入连接串。

## 结论

默认推荐路径：

```text
预检 -> 备份 -> 目标库清空/确认 -> pg_dump 自定义格式 -> pg_restore -> Drizzle 迁移历史校验 -> 应用配置切换 -> 健康检查 -> 保留旧库只读观察
```

如果业务不能接受最终停写窗口，再使用 PostgreSQL logical replication：

```text
目标库初始化 -> 建 publication/subscription -> 等待追平 -> 短暂停写 -> 最后校验 -> 切换应用 -> 取消订阅
```

当前项目使用 `drizzle-orm`、`postgres.js` 和自定义 `bun db` 迁移 CLI。项目根的 `../scripts/db/README.md` 明确说明生产/预发迁移 CLI 尚未完整接入，迁移后需要确认 `drizzle.__drizzle_migrations` 与 `../shared/drizzle/meta/_journal.json` 一致。

## 预检

先在安全终端中设置变量：

```bash
export OLD_DATABASE_URL='<old-postgres-url>'
export NEW_DATABASE_URL='<new-postgres-url>'
export MIGRATION_DIR="$PWD/.migration-artifacts/postgres-$(date +%Y%m%d-%H%M%S)"
mkdir -p "$MIGRATION_DIR"
```

确认本机客户端版本：

```bash
psql --version
pg_dump --version
pg_restore --version
```

建议使用等于或高于源库主版本的 PostgreSQL 客户端。当前本机已安装 `/opt/homebrew/opt/libpq/bin/psql`、`pg_dump`、`pg_restore`，版本为 PostgreSQL 18.3。

采集源库和目标库状态：

```bash
psql "$OLD_DATABASE_URL" -v ON_ERROR_STOP=1 -c "select version();"
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -c "select version();"

psql "$OLD_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select current_database(), pg_size_pretty(pg_database_size(current_database()));"
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select current_database(), pg_size_pretty(pg_database_size(current_database()));"

psql "$OLD_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select schemaname, count(*) from pg_tables where schemaname not in ('pg_catalog','information_schema') group by 1 order by 1;"
```

检查目标库是否为空或是否允许覆盖：

```bash
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select schemaname || '.' || tablename from pg_tables where schemaname not in ('pg_catalog','information_schema') order by 1 limit 50;"
```

如果目标库已有业务数据，不要直接 restore。先新建目标数据库或做快照。

## 全量迁移

先导出 cluster-level 对象。托管数据库通常不允许恢复所有角色/表空间，但这一步能留下审计材料：

```bash
pg_dumpall --globals-only "$OLD_DATABASE_URL" > "$MIGRATION_DIR/globals.sql"
```

导出业务库，使用 custom archive，便于 `pg_restore` 并行恢复、列出对象、选择性恢复：

```bash
pg_dump "$OLD_DATABASE_URL" \
  --format=custom \
  --blobs \
  --no-owner \
  --no-acl \
  --verbose \
  --file="$MIGRATION_DIR/autumn.dump"
```

生成对象清单：

```bash
pg_restore --list "$MIGRATION_DIR/autumn.dump" > "$MIGRATION_DIR/autumn.dump.list"
```

恢复到新库：

```bash
pg_restore "$MIGRATION_DIR/autumn.dump" \
  --dbname="$NEW_DATABASE_URL" \
  --clean \
  --if-exists \
  --no-owner \
  --no-acl \
  --jobs=4 \
  --verbose
```

如果目标库不是空库，`--clean --if-exists` 会删除 dump 中对应对象。生产迁移前必须先确认目标库可覆盖。

## 校验

基础校验：

```bash
psql "$OLD_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select count(*) from information_schema.tables where table_schema not in ('pg_catalog','information_schema');"
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select count(*) from information_schema.tables where table_schema not in ('pg_catalog','information_schema');"
```

对所有普通表生成行数对比 SQL：

```bash
psql "$OLD_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select format('select %L as table_name, count(*) from %I.%I;', schemaname || '.' || tablename, schemaname, tablename)
   from pg_tables
   where schemaname not in ('pg_catalog','information_schema')
   order by schemaname, tablename;" > "$MIGRATION_DIR/counts.sql"

psql "$OLD_DATABASE_URL" -v ON_ERROR_STOP=1 -f "$MIGRATION_DIR/counts.sql" > "$MIGRATION_DIR/counts-old.txt"
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -f "$MIGRATION_DIR/counts.sql" > "$MIGRATION_DIR/counts-new.txt"
diff -u "$MIGRATION_DIR/counts-old.txt" "$MIGRATION_DIR/counts-new.txt"
```

校验 Drizzle 迁移历史：

```bash
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -Atc \
  "select count(*) from drizzle.__drizzle_migrations;"
```

在 repo 根执行 dry-run，不应有意外 pending migration：

```bash
cd /Volumes/sker/resources/coding/comi-logic/autumn
AUTUMN_DB_DIRECT=1 DATABASE_URL="$NEW_DATABASE_URL" bun db migrate:dry --env=prod
```

如 dry-run 显示需要补跑迁移，先判断是目标库缺少历史记录还是确有新 DDL。不要盲目 mark-applied。

## 应用切换

当前 Autumn Server 的直接 PostgreSQL URL 只使用 `DATABASE_URL`。

同时 `src/db/initDrizzle.ts` 会优先使用 `env.HYPERDRIVE.connectionString`，否则回落到 `env.DATABASE_URL`。因此切换时需要确认 Cloudflare Hyperdrive 绑定和 `DATABASE_URL` 都指向新库。

建议切换顺序：

1. 给新库执行一次只读 smoke query。
2. 更新密钥系统 / Cloudflare vars / Hyperdrive 连接。
3. 部署 Worker。
4. 请求 `/ready`，确认 PostgreSQL ready check 通过。
5. 观察错误率、连接池错误、关键业务 API 和迁移任务队列。

## 低停机备选

如果最终停写窗口不可接受，采用 logical replication：

1. 源库确认 `wal_level=logical`、复制权限、publication 权限。
2. 在目标库先创建 schema，通常来自 `pg_dump --schema-only` 或全量 restore。
3. 源库创建 publication。
4. 目标库创建 subscription，允许初始数据复制。
5. 等待 replication lag 归零。
6. 短暂停写，做最后行数/关键表校验。
7. 切换应用连接到新库。
8. 观察通过后删除 subscription/publication。

logical replication 不会自动复制所有 DDL、角色、权限和某些对象，仍然需要 schema/role/extension 预置和变更冻结。

## 回退

热窗口内优先回退应用配置：

```text
新库异常 -> 把 Worker/Hyperdrive/vars 切回旧库 -> 部署 -> /ready 和关键 API 验证
```

切换后不要马上写入旧库或清理旧库。保留旧库只读观察至少一个业务周期。若新库已接受写入且需要回退，必须先决定是否做反向同步、数据补偿或从备份恢复，不能只改连接串。

## 官方依据

- PostgreSQL `pg_dump`: https://www.postgresql.org/docs/current/app-pgdump.html
- PostgreSQL `pg_restore`: https://www.postgresql.org/docs/current/app-pgrestore.html
- PostgreSQL `pg_dumpall`: https://www.postgresql.org/docs/current/app-pgdumpall.html
- PostgreSQL logical replication: https://www.postgresql.org/docs/current/logical-replication.html
- PostgreSQL libpq connection strings, SSL, channel binding: https://www.postgresql.org/docs/current/libpq-connect.html