数据迁移步骤清单

以下为从 MySQL 迁移数据到 PostgreSQL 的标准步骤与要点，覆盖评估、架构转换、全量/增量迁移、验证与切换。可按需裁剪为离线一次性迁移或零停机迁移。

一、评估与规划

• 清点与分析
  • 库与表数量、数据量、最大表行数与体积、增长率。
  • 长事务、热点表、触发器/事件、存储过程、视图、外键、全文索引、分区等。
  • 字符集与排序规则（MySQL: utf8mb4/latin1 与 collation；PostgreSQL: cluster 初始化时确定编码/排序）。
• 版本与兼容性
  • MySQL 5.7/8.0 到 PostgreSQL 12+（建议 14+）。
  • 工具支持（pgloader、AWS DMS、pg_chameleon、Debezium + Kafka Connect）。
• 性能与窗口
  • 是否需要零停机（CDC 同步），可接受的只读或停机窗口。
  • 目标库资源与网络带宽评估，批量加载参数规划。
• 安全与网络
  • 凭据、VPC/防火墙、最小权限账户，TLS。

二、目标 PostgreSQL 准备

• 初始化数据库
  • 创建数据库与 schema，编码 UTF8，选择合适的排序规则与 ICU（如需稳定排序/多语言需求）。
  • 建议参数：shared_buffers、work_mem、maintenance_work_mem、wal_compression、checkpoint_timeout 等按数据量调整（大表加载时提高 maintenance_work_mem）。
• 扩展
  • 依据功能启用：uuid-ossp 或 pgcrypto（UUID），citext（不区分大小写文本），postgis（空间数据），pg_trgm（模糊搜索）。
• 账户与权限
  • 迁移专用用户（仅所需权限），业务用户与角色分离。

三、架构转换（DDL 层）

• 数据类型与语义映射（示例）
  • TINYINT(1) → boolean
  • 其他 TINYINT/SMALLINT/INT → smallint/integer/bigint（注意 UNSIGNED → 上推一档或加 CHECK 约束）
  • DECIMAL/NUMERIC → numeric(p,s)
  • FLOAT/DOUBLE → double precision（注意精度差异）
  • CHAR/VARCHAR/TEXT → text/varchar（PostgreSQL 无长度显示宽度语义）
  • BINARY/VARBINARY/BLOB → bytea
  • DATE/DATETIME/TIMESTAMP → date/timestamp/timestamptz（如存储 UTC，建议 timestamptz）
  • JSON → jsonb（功能更强，索引支持更好）
  • ENUM → PostgreSQL enum 类型或 text + CHECK（推荐 enum 或 domain 以维持约束）
  • SET → text[]（数组），或分离多对多关联
  • GEOMETRY → PostGIS 几何类型
• 约束与索引
  • 主键/唯一约束、外键、检查约束迁移；MySQL 隐式索引与前缀索引需要重设策略。
  • FULLTEXT 索引 → tsvector + GIN/GIST；需构造 to_tsvector 列或表达式索引。
• 自增/默认值
  • AUTO_INCREMENT → GENERATED BY DEFAULT AS IDENTITY（或 legacy SERIAL）。
  • MySQL 的 ON UPDATE CURRENT_TIMESTAMP → 在 PostgreSQL 用触发器维护 updated_at（默认值不会在更新时自动刷新）。
• 语义差异处理
  • 0 日期（'0000-00-00'、'0000-00-00 00:00:00'）在 PostgreSQL 非法：需转 NULL 或哨兵值。
  • 区分大小写与保留字：统一小写蛇形命名，避免引用标识符。
  • 排序/比较：MySQL 不同 collation 的大小写比较行为与 PostgreSQL 不同，必要时用 citext 或定义一致 collation。
  • 布尔型：MySQL 用 0/1，迁移为 boolean 后注意应用侧参数化。

建议使用工具自动生成/转换 DDL，并进行人工审校与定制。

四、生成与部署目标 DDL

• 方案 A：使用 pgloader 由源库反射创建目标表
  • 优点：自动化；缺点：复杂场景需自定义 CAST 与后置修正。
• 方案 B：手工整理 DDL
  • 使用脚本/模板统一命名、类型与约束策略，更可控。

建议先在空库执行 DDL，暂不创建外键/次级索引（或以 NOT VALID 形式延后），以便加速后续数据加载。

五、全量数据迁移（初次装载）

• 工具选择
  • pgloader：MySQL → PostgreSQL 一体化（复制时用 COPY，加速，带类型映射与零日期处理）。
  • 或导出为 CSV 并在 PostgreSQL 用 COPY 导入（需自行处理转义与类型转换）。
• pgloader 示例（简化）
  • 配置文件示例： LOAD DATABASE FROM mysql://user:pass@mysql-host:3306/src_db INTO postgresql://user:pass@pg-host:5432/dst_db WITH include drop, create tables, create indexes, reset sequences, workers = 8, batch rows = 50000, prefetch rows = 50000, on error stop SET maintenance_work_mem to '2GB', work_mem to '128MB', synchronous_commit to 'off' CAST type datetime to timestamptz, type date using zero-dates-to-null, type json to jsonb BEFORE LOAD DO $$ SET session_replication_role = 'replica'; $$; AFTER LOAD DO $$ SET session_replication_role = 'origin'; $$;
  • 说明：
    • 根据需要增加对 tinyint(1)→boolean、enum→enum/text 的映射规则。
    • session_replication_role=replica 会禁用用户触发器与外键触发器，加速导入但会跳过外键校验，需在导入后补做一致性校验与再验证。
• CSV + COPY 流程（备选）
  • mysqldump/SELECT ... INTO OUTFILE 导出 CSV（注意字符集统一为 UTF-8，NULL 表示，转义与分隔符）。
  • 在 PostgreSQL 使用 COPY 表加载（单事务，synchronous_commit=off，增大 maintenance_work_mem）。
• 性能建议
  • 暂不创建或临时禁用二级索引与外键，待数据装载后再建。
  • 批量加载完成后 VACUUM ANALYZE 以刷新统计信息。

六、增量同步/变更数据捕获（CDC，零停机可选）

• 方案 A：pg_chameleon
  • 使用 MySQL binlog 行级复制到 PostgreSQL，适合 MySQL→PG 单向迁移。
  • 要求 MySQL 开启 binlog，binlog_format=ROW，binlog_row_image=FULL，设置唯一 server_id。
• 方案 B：Debezium MySQL Connector + Kafka Connect JDBC Sink
  • Debezium 从 MySQL binlog 捕获变更，Kafka Connect 下游写入 PostgreSQL。
  • 适合高吞吐与可观测性需求；需 Kafka 基础设施与 schema/类型映射规则定制。
• 方案 C：AWS DMS
  • 支持全量 + CDC，托管化，适合云上场景。
• CDC 步骤
  1. 在 MySQL 启用 binlog（ROW 模式），创建最小权限复制账号。
  2. 全量装载完成后开启 CDC 订阅，持续追赶增量。
  3. 监控延迟，直至延迟趋近于 0，为切换做准备。
  4. 切换窗口将源库置只读，等待 CDC 清空剩余增量。

七、重建约束与索引、修复序列

• 索引
  • 为大表并发创建索引：CREATE INDEX CONCURRENTLY，减少锁阻塞。
• 外键
  • 可先 ADD CONSTRAINT ... NOT VALID，再 VALIDATE CONSTRAINT，降低写阻塞（注意验证期间仍有共享锁，但不阻塞读）。
• 序列/标识列
  • 如使用 SERIAL：SELECT setval(pg_get_serial_sequence('tbl','id'), (SELECT max(id) FROM tbl), true);
  • 使用 IDENTITY 一般无需手动对齐，但建议验证。
• 触发器
  • 如需模拟 MySQL 的 ON UPDATE 行为，为 updated_at 添加 BEFORE UPDATE 触发器。

八、数据验证与质量校验

• 数量级校验
  • 按库/表/分区比对行数，按条件分片比对。
• 取样与校验和
  • 关键表按主键范围抽样，对比字段哈希/聚合（注意不同类型/时区规范化）。
• 约束与一致性
  • VALIDATE CONSTRAINT 后确认无违例；业务关键唯一性核验。
• 性能基线
  • 收集并比较典型查询的执行计划与耗时，必要时补充索引或调整统计目标（ALTER TABLE ... SET (autovacuum_analyze_scale_factor/targets)）。

九、切换与回滚

• 切换
  • 冻结源库写入（短暂只读）、等待 CDC 清空、停止连接源、切换应用连接到 PostgreSQL。
  • 清理迁移专用权限与参数，开启监控与告警。
• 回滚预案
  • 切换前保留源库只读与快照；在明确验证期内可快速回切。
  • 记录所有变更脚本与参数，确保可重复迁移。

十、常见问题与处理建议

• 0000-00-00 日期：迁移前清洗或在工具层映射为 NULL。
• UNSIGNED 溢出风险：映射到更大整型或添加 CHECK (col >= 0)。
• 字符集/表情符：MySQL utf8 与 utf8mb4 差异，确保源端为 utf8mb4，目标端为 UTF8，并验证四字节字符。
• 全文索引差异：替换为 tsvector + GIN，并按语言选择合适的配置（simple/english/zhparser）。
• JSON 语义：迁移为 jsonb 并替换函数/运算符（->、->>、@>、索引表达式）与应用层查询。
• 时间戳与时区：统一存