跨数据库迁移实战用Kettle实现MySQL到PostgreSQL的高效数据同步当你需要将业务系统从MySQL迁移到PostgreSQL时最头疼的问题莫过于如何安全高效地转移海量数据。传统的手工导出导入不仅耗时费力还容易出错。本文将带你用Kettle的ETL工具链构建一个自动化、可复用的数据迁移流水线。1. 环境准备与连接配置在开始数据迁移前我们需要确保两端数据库环境就绪。PostgreSQL建议安装与MySQL字符集兼容的扩展如citext模块避免后续出现大小写敏感问题。数据库连接配置步骤启动Kettle后右键点击数据库连接选择新建MySQL连接参数示例连接名称: src_mysql 主机名: 127.0.0.1 端口: 3306 数据库名: order_db 用户名: etl_user 密码: ******PostgreSQL连接需要特别注意SSL模式配置连接名称: dst_pg 主机名: pg.example.com 端口: 5432 数据库名: analytics 用户名: loader 密码: ****** SSL模式: require提示测试连接时若失败检查防火墙规则是否放行了Kettle所在主机的IP地址2. 构建基础迁移转换流新建转换ktr文件后从核心对象面板拖入以下组件构建基础流程表输入 - 字段选择 - 表输出表输入组件的关键配置SQL查询建议使用完全限定列名避免字段歧义SELECT orders.id AS order_id, customers.name AS customer_name, orders.total_amount FROM orders JOIN customers ON orders.customer_id customers.id勾选替换SQL语句里的变量以便动态传参记录数限制先设为1000进行测试迁移字段映射的典型问题处理MySQL的datetime映射到PostgreSQL的timestamptinyint(1)转为boolean类型文本字段注意编码转换utf8mb4 - utf83. 高级优化技巧当迁移数据量超过百万级时需要采用分片策略提升性能批量提交优化参数参数项测试值1测试值2生产推荐值提交记录数1000500010000批量插入大小1005001000并行线程数248在表输出组件中启用高级配置使用批量插入: 是 批量插入大小: 1000 压缩数据传输: 是性能对比测试结果单线程默认配置12,000行/分钟优化后多线程85,000行/分钟4. 异常处理与数据校验迁移过程中最常见的三类问题及解决方案数据类型不兼容PostgreSQL的日期范围更严格需预处理非法日期使用字段选择组件添加类型转换规则字符集问题在字段选择中添加编码转换步骤典型转换对latin1 - utf8、gbk - utf8外键约束冲突迁移前禁用目标表约束按依赖顺序迁移表先主表后子表数据校验SQL示例-- 数量校验 SELECT (SELECT COUNT(*) FROM mysql.orders) AS src_count, (SELECT COUNT(*) FROM pg.orders) AS dst_count; -- 抽样校验 SELECT md5(array_agg(t::text)::text) AS hash_value FROM ( SELECT id, customer_id, amount FROM pg.orders ORDER BY random() LIMIT 10000 ) t;5. 自动化调度与监控将转换保存后可以通过作业kjb实现自动化创建每日增量迁移作业流开始 - 检查依赖文件 - 执行转换 - 发送通知 - 结束配置增量查询条件WHERE update_time ${LAST_RUN_TIME}添加错误处理分支失败时重试3次最终失败发送告警邮件在资源库中创建migration_log表记录每次运行情况CREATE TABLE migration_log ( job_name VARCHAR(100), start_time TIMESTAMP, end_time TIMESTAMP, rows_processed INT, status VARCHAR(20) );实战经验分享在一次电商系统迁移中我们发现订单表的JSON字段在PostgreSQL中解析失败。解决方案是在字段选择中添加JavaScript步骤进行预处理// 处理JSON字段转换 function cleanJson(input) { try { return JSON.stringify(JSON.parse(input)); } catch (e) { return null; } }另一个教训是关于大字段迁移——将LONGTEXT直接映射到TEXT导致性能骤降。后来改为分批提取大字段吞吐量提升了8倍。