198 lines
7.1 KiB
Markdown
198 lines
7.1 KiB
Markdown
# 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
|