Skip to content

postgres-to-d1-data-import.md

Latest commit

 

History

History
405 lines (290 loc) · 17.2 KB

postgres-to-d1-data-import.md

File metadata and controls

405 lines (290 loc) · 17.2 KB
Raw

PostgreSQL → Cloudflare D1 数据导入手册

最后更新：2026-08-08

本文是 AAEasy 从旧 PostgreSQL 搬迁业务数据到 Cloudflare D1 的操作依据。它覆盖迁移演练、正式切换、验证、失败恢复，以及以后修改 schema 时需要同步维护的知识。

1. 先记住这些结论

  1. PostgreSQL dump 不能直接导入 D1。D1 使用 SQLite 类型和 SQL 语法，必须通过仓库的 scripts/export-postgres-to-d1.ts 转换。
  2. 目标 D1 必须先应用仓库 migrations，且除 users 外的业务表必须为空。已有 users/sessions 只有在用户 ID 与身份映射完全一致时才能保留；导出器只对 users 做受控 upsert，其余业务表仍是不可重复执行的严格 INSERT。
  3. 正式切换必须停止旧 PostgreSQL 的业务写入。导出器使用只读、REPEATABLE READ 快照保证单次导出内部一致，但无法包含快照建立后继续发生的写入。
  4. 不要用 drizzle-kit migrate 应用 D1 migrations。Drizzle Kit 只生成 SQL，实际迁移由 Wrangler 管理。
  5. 导入失败后不要在同一个目标库上直接重试。将它视为可能已被部分写入，恢复到导入前的 Time Travel bookmark，或删除并重建演练库。
  6. 正式导入前至少完成一次远程演练，并保存行数、文件哈希、迁移版本和验证结果。

Cloudflare 官方说明了 PostgreSQL 与 D1 不可直接互导、d1 execute --file 的导入方式及当前限制：D1 Import and export data。

2. 仓库导出器的契约

执行入口：

pnpm exec tsx scripts/export-postgres-to-d1.ts

导出器读取 DIRECT_DATABASE_URL 和 AAEASY_USER_IDENTITY_MAP_FILE，把 SQL 写到标准输出。它不会修改 PostgreSQL。生成 SQL 文件时必须直接执行 tsx；不要重定向 pnpm db:export:postgres 的标准输出，因为 pnpm 的运行横幅也会被写进 SQL。

身份映射文件是一个受保护的 JSON 对象：key 为 Neon user ID，value 包含对应的 KeyForge id、email 和 picture。正式导出前必须按 Neon username 与 KeyForge alias 做唯一匹配，并验证旧超级管理员属于 KeyForge admins 组。映射文件包含身份数据，权限必须为 0600，并与导出 SQL 一起清理。

2.1 会导出的表

按外键依赖顺序导出：

  1. users
  2. groups
  3. members
  4. group_memberships
  5. group_invitations
  6. share_links
  7. settlements
  8. expenses
  9. expense_splits
  10. fx_rate_cache
  11. settlement_entries
  12. audit_logs

2.2 有意不导出的表

  • sessions
  • share_sessions

这两张表包含旧系统的登录或分享解锁状态。它们不会从 PostgreSQL 导入；分享访问者必须重新解锁。若生产 D1 已有映射正确的 OIDC 用户和 session，users 的受控 upsert 不会删除该 session。不要为了减少重新登录而临时把旧 session 加入导出器。

2.3 类型转换

PostgreSQL 数据 D1 表示 处理方式
timestamp / timestamptz INTEGER 转成 Unix epoch 毫秒
json / jsonb TEXT JSON.stringify 后写入
expenses.tags 不导入 标签功能已移除，旧标签直接丢弃
boolean INTEGER true → 1，false → 0
金额 bigint 十进制 TEXT 使用带引号的十进制字符串，避免 JavaScript 数字精度损失
旧 user ID KeyForge user ID 所有用户外键按已验证身份映射改写；users 按 KeyForge ID 受控 upsert
旧密码/超级管理员字段 不导入 密码由 KeyForge 管理；超级管理员来自 KeyForge admins 组
groups.revision INTEGER 缺失时初始化为 0；已有值必须是非负 JavaScript safe integer
expenses.isDraft 不导入 仅允许旧值全为 false；费用 version 初始化为 1
mutation token TEXT 旧数据使用实体 ID 初始化 mutationId

导出器拒绝非有限数字、无效时间、包含 NUL 字符的文本，以及无法安全表示的 revision。遇到这些错误时应修复或明确转换源数据，不能静默截断。

2.4 已归档账本

D1 触发器禁止向已归档账本插入费用。导出器会：

  1. 暂时把源数据中的 ARCHIVED 账本写为 ACTIVE；
  2. 导入结算、费用和分摊；
  3. 在文件末尾恢复 ARCHIVED 状态；
  4. 让 D1 触发器验证结算快照和锁定费用是否完整。

如果最后一步报 SETTLEMENT_INCOMPLETE 或 SETTLEMENT_EXPENSE_SET_CHANGED，说明旧 PostgreSQL 中的归档数据不符合当前业务约束。应修复源数据并重新导出，不要绕过触发器。

2.5 完成标记

成功生成的文件开头包含：

-- aaeasy-d1-data-export-version: 5
-- aaeasy-user-identity-mappings: 5

每张表包含一条源行数注释，文件末尾必须包含：

-- aaeasy-source-export-complete: tables=12

没有完成标记的文件是中断或失败的导出，禁止导入。

3. 前置检查

在仓库根目录执行：

pnpm install --frozen-lockfile
pnpm exec wrangler --version
pnpm exec wrangler whoami
pnpm check

确认：

  • 当前代码版本与即将部署的 Worker 完全相同；
  • wrangler.jsonc 中 production D1 的 database_name 和 database_id 正确；
  • pnpm db:migrate:remote 指向 aaeasy-production；
  • PostgreSQL 迁移账号对 12 张源表都有 SELECT 权限；
  • 本机有足够的受保护临时磁盘空间；
  • 已安排写入冻结和回滚窗口。

D1 migration 文件按顺序记录在 d1_migrations 表中，详见 D1 migrations。

4. 远程演练

不要把第一次导入尝试放在生产 D1。

4.1 创建一次性演练库

数据库名应包含日期或工单号，避免误认成生产库：

umask 077
AAEASY_MIGRATION_DIR="$(mktemp -d)"
AAEASY_REHEARSAL_DB="aaeasy-import-rehearsal-$(date +%Y%m%d)"
AAEASY_REHEARSAL_CONFIG="$AAEASY_MIGRATION_DIR/wrangler.rehearsal.json"
pnpm exec wrangler d1 create "$AAEASY_REHEARSAL_DB"

把创建命令返回的 database_id 写入临时 AAEASY_REHEARSAL_CONFIG，binding 使用 DB，migrations_dir 指向仓库的绝对 migrations/ 路径。不要把演练 binding 加入生产 wrangler.jsonc。随后执行：

pnpm exec wrangler d1 migrations apply DB --remote --config "$AAEASY_REHEARSAL_CONFIG"

确认 migration 状态：

pnpm exec wrangler d1 migrations list DB --remote --config "$AAEASY_REHEARSAL_CONFIG"

4.2 安全生成转换后的 SQL

不要把数据库 URL 直接写进命令历史。下面的交互输入不会回显密码：

AAEASY_EXPORT_SQL="$AAEASY_MIGRATION_DIR/aaeasy-d1-data.sql"
AAEASY_IDENTITY_MAP="$AAEASY_MIGRATION_DIR/identity-map.json"

printf 'PostgreSQL URL: ' >&2
IFS= read -r -s AAEASY_SOURCE_POSTGRES_URL
printf '\n' >&2

DIRECT_DATABASE_URL="$AAEASY_SOURCE_POSTGRES_URL" \
AAEASY_USER_IDENTITY_MAP_FILE="$AAEASY_IDENTITY_MAP" \
  pnpm exec tsx scripts/export-postgres-to-d1.ts > "$AAEASY_EXPORT_SQL"
unset AAEASY_SOURCE_POSTGRES_URL

在自动化环境中应通过 CI secret 注入 DIRECT_DATABASE_URL，不能提交 .env、SQL 文件或数据库 URL。

4.3 验证导出文件

head -n 1 "$AAEASY_EXPORT_SQL" | rg '^-- aaeasy-d1-data-export-version: 5$'
rg '^-- aaeasy-user-identity-mappings:' "$AAEASY_EXPORT_SQL"
rg '^-- aaeasy-source-row-count:' "$AAEASY_EXPORT_SQL"
rg -q '^-- aaeasy-source-export-complete: tables=12$' "$AAEASY_EXPORT_SQL"
shasum -a 256 "$AAEASY_EXPORT_SQL"
wc -c < "$AAEASY_EXPORT_SQL"

把表行数、SHA-256 和文件字节数记入迁移记录。当前 D1 对 d1 execute 文件大小和单条 SQL 长度有限制，执行前应核对 D1 limits。导出器每行生成一条 INSERT；如果单个数据行生成的 statement 超过当前限制，需要先缩小对应 JSON/文本数据或编写专用分片导入器。

4.4 导入演练库

pnpm exec wrangler d1 execute DB --remote \
  --config "$AAEASY_REHEARSAL_CONFIG" --file "$AAEASY_EXPORT_SQL"

导出文件使用 PRAGMA defer_foreign_keys 处理导入期间的依赖顺序。D1 的外键始终受保护，延迟检查只在当前导入事务内有效：D1 foreign keys。

5. 数据验证

5.1 比较所有业务表行数

从导出文件读取 PostgreSQL 快照行数：

rg '^-- aaeasy-source-row-count:' "$AAEASY_EXPORT_SQL"

查询 D1：

pnpm exec wrangler d1 execute DB --remote --config "$AAEASY_REHEARSAL_CONFIG" --command "
SELECT 'users' AS table_name, count(*) AS row_count FROM users
UNION ALL SELECT 'groups', count(*) FROM groups
UNION ALL SELECT 'members', count(*) FROM members
UNION ALL SELECT 'group_memberships', count(*) FROM group_memberships
UNION ALL SELECT 'group_invitations', count(*) FROM group_invitations
UNION ALL SELECT 'share_links', count(*) FROM share_links
UNION ALL SELECT 'settlements', count(*) FROM settlements
UNION ALL SELECT 'expenses', count(*) FROM expenses
UNION ALL SELECT 'expense_splits', count(*) FROM expense_splits
UNION ALL SELECT 'fx_rate_cache', count(*) FROM fx_rate_cache
UNION ALL SELECT 'settlement_entries', count(*) FROM settlement_entries
UNION ALL SELECT 'audit_logs', count(*) FROM audit_logs;
"

12 张表的行数必须逐一相同。干净演练库的 session 表必须为空；生产库若预先存在已验证 OIDC session，可以保留，但其 userId 必须属于身份映射后的用户：

pnpm exec wrangler d1 execute DB --remote --config "$AAEASY_REHEARSAL_CONFIG" \
  --command "SELECT (SELECT count(*) FROM sessions) AS sessions, (SELECT count(*) FROM share_sessions) AS share_sessions;"

5.2 检查结构约束

外键检查必须返回零行：

pnpm exec wrangler d1 execute DB --remote --config "$AAEASY_REHEARSAL_CONFIG" \
  --command 'PRAGMA foreign_key_check;'

确认所有归档账本已恢复，并且没有停留在临时状态：

rg '^-- aaeasy-archived-groups-restored:' "$AAEASY_EXPORT_SQL"

pnpm exec wrangler d1 execute DB --remote --config "$AAEASY_REHEARSAL_CONFIG" --command "
SELECT status, count(*) AS row_count FROM groups GROUP BY status ORDER BY status;
SELECT count(*) AS active_settlements FROM settlements WHERE reopenedAt IS NULL;
SELECT count(*) AS locked_expenses FROM expenses WHERE lockedBySettlementId IS NOT NULL;
"

把 D1 的 group status 计数与 PostgreSQL 源库比较。最终的归档恢复语句会触发当前 D1 结算完整性约束，因此导入成功本身也验证了每个归档账本。

5.3 应用层抽查

使用连接演练库的临时 Worker，至少验证：

  • 登录后能看到预期账本和成员；
  • 任选 3 个活跃账本，费用数量、总额、币种和分摊结果与 PostgreSQL 版本一致；
  • 任选 1 个已归档账本，结算快照、锁定费用和结算建议一致；
  • 删除费用、邀请、分享链接和审计日志能正常读取；
  • 新建一笔费用后 revision、split、audit log 同时更新。

只有行数、约束检查和应用抽查都通过，演练才算成功。

6. 正式切换步骤

以下步骤应在维护窗口内按顺序执行。

6.1 冻结和备份

  1. 停止旧应用写流量，或将旧应用切换到只读维护状态。
  2. 等待所有后台任务和在途请求完成。
  3. 使用 PostgreSQL 托管平台快照或 pg_dump 创建独立备份。
  4. 记录写入冻结的 UTC 时间。
  5. 在冻结后重新生成 SQL；演练文件不能直接作为正式文件使用。

6.2 准备生产 D1

pnpm db:migrate:remote
pnpm exec wrangler d1 migrations list aaeasy-production --remote --env production
pnpm exec wrangler d1 time-travel info aaeasy-production

保存导入前的 Time Travel bookmark。Time Travel 默认启用；恢复会原地覆盖数据库并取消在途查询，因此只能作为明确确认后的恢复操作：D1 Time Travel。

确认所有目标业务表为空。不要以“看起来是新库”为依据，必须执行行数查询。

6.3 导出、导入和验证

按照第 4.2 节重新导出冻结后的 PostgreSQL，再执行第 4.3 节检查。然后：

pnpm exec wrangler d1 execute aaeasy-production \
  --remote --env production --file "$AAEASY_EXPORT_SQL"

完整执行第 5 节验证并保存结果。验证期间不要开放新 Worker 的写流量。

6.4 开放流量

  1. 部署与本次 schema 对应的 Worker 版本。
  2. 检查 /api/health。
  3. 完成登录、账本读取、费用写入和结算 smoke test。
  4. 切换正式流量。
  5. 保持旧 PostgreSQL 只读和可恢复，直到观察窗口结束。
  6. 监控 D1 query errors、Worker 5xx、写入冲突和数据量变化。

7. 失败恢复

7.1 演练库失败

删除一次性演练库，创建新库并重新执行 migrations 和导入。不要清空若干表后继续，因为遗漏的表、trigger 或 migration 状态会使下一次结果不可复现。

pnpm exec wrangler d1 delete "$AAEASY_REHEARSAL_DB"

删除 D1 是不可恢复操作；执行前再次输出并确认变量值确实是演练库，而不是 production。

7.2 生产导入失败且尚未开放流量

使用导入前保存的 bookmark 恢复：

pnpm exec wrangler d1 time-travel restore aaeasy-production \
  --bookmark '<IMPORT_BEFORE_BOOKMARK>'

这是破坏性操作，会原地覆盖生产 D1。必须由操作者二次确认数据库名和 bookmark，并保存 restore 返回的 previous bookmark，以便撤销误恢复。

7.3 已开放流量后发现问题

不要直接恢复到导入前，否则会丢失切换后的 D1 写入。先重新冻结写流量，导出当前 D1、确定受影响范围，再决定：

  • 修复性 SQL；
  • 回放切换后的业务写入；
  • 恢复 D1 bookmark 并把增量写入重新应用；
  • 回切旧 PostgreSQL。

这属于事故处理，不再是普通导入重试。

8. 常见错误

错误 常见原因 处理
UNIQUE constraint failed 目标库不为空，或源库已有违反新唯一约束的数据 放弃当前目标；修复源数据或重建空库后重导
GROUP_ARCHIVED 使用旧版导出文件，或手工把 groups/expenses 重排 确认 export version 为 5，用当前脚本重新导出
SETTLEMENT_INCOMPLETE 已归档账本没有且仅有一个有效结算 修复 PostgreSQL 业务数据后重导
SETTLEMENT_EXPENSE_SET_CHANGED 结算 snapshot 与锁定费用不一致 修复源数据，不能禁用 trigger 绕过
FOREIGN KEY constraint failed 数据缺失、顺序错误或源库已有孤儿引用 执行源库关系检查，修复后重新导出
cannot start a transaction within a transaction 使用了包含 BEGIN / COMMIT 的通用 dump 只使用仓库导出器；不要把 PostgreSQL/SQLite dump 直接导入
Statement too long 单个 JSON 或文本行生成的 SQL 超过 D1 当前限制 找到对应行并缩小数据，或实现专用分片/绑定参数导入器
revision safe integer 错误 PostgreSQL revision 超出 JS 安全整数范围 先确定新的编码方案，不能强制 Number() 截断
NUL character 错误 TEXT 中包含 D1 导出器不接受的 \0 清洗源字段并记录业务影响
Wrangler authentication 错误 登录账户或 API token 不属于目标账户 运行 wrangler whoami，修正身份后重新确认目标 D1

9. Schema 变化后的维护清单

任何数据库 schema 变更都必须审查 scripts/export-postgres-to-d1.ts：

  1. 新表是否加入 tables，且顺序满足外键和 trigger；
  2. 新的 timestamp、JSON、数组、boolean、bigint 是否需要转换；
  3. 新增必填 D1 字段是否能从 PostgreSQL 补齐；
  4. 被排除的 session/token 表是否仍应排除；
  5. 新 trigger 是否会阻止历史数据导入；
  6. 是否需要提高 aaeasy-d1-data-export-version；
  7. 行数验证 SQL 是否覆盖了新表；
  8. 是否重新完成一次远程演练。

如果不完成这份清单，schema 变更不能视为兼容旧 PostgreSQL 数据迁移。

10. 迁移记录模板

每次正式切换保存以下信息，但不要保存数据库密码或明文导出数据：

代码 commit:
操作者:
开始/结束 UTC 时间:
PostgreSQL 来源实例:
写入冻结 UTC 时间:
PostgreSQL 备份/快照 ID:
D1 database name / ID:
D1 migrations:
导出器版本:
导出文件 SHA-256:
12 张表源行数:
12 张表 D1 行数:
归档账本数量:
PRAGMA foreign_key_check 结果:
导入前 Time Travel bookmark:
应用 smoke test:
开放流量时间:
观察窗口结论:

11. 敏感数据清理

转换后的 SQL 包含用户资料、账本、费用、分享链接哈希和审计记录。迁移完成并保存必要的非敏感证据后，删除临时 SQL：

rm -f -- "$AAEASY_EXPORT_SQL" "$AAEASY_IDENTITY_MAP" "$AAEASY_REHEARSAL_CONFIG"
rmdir -- "$AAEASY_MIGRATION_DIR"
unset AAEASY_EXPORT_SQL AAEASY_IDENTITY_MAP AAEASY_REHEARSAL_CONFIG AAEASY_MIGRATION_DIR AAEASY_REHEARSAL_DB

执行前确认变量指向本次 mktemp -d 创建的专用目录。普通删除不一定能保证 SSD 或快照中的数据不可恢复；如有合规要求，应使用组织批准的加密临时存储和销毁流程。