1. 迁移自动化为什么CI/CD是MySQL到PostgreSQL迁移的“定心丸”最近在帮一个团队做数据库迁移从MySQL 8.0转到PostgreSQL 15。项目不算小几十张表几百个存储过程还有一堆视图和触发器。一开始大家觉得这活儿就是写个转换脚本跑一遍然后上线。结果第一次试跑就炸了数据类型不匹配、自增序列没处理好、外键约束丢失……光是回滚和排查就花了两天。痛定思痛我们决定把整个迁移过程塞进CI/CD流水线里。结果你猜怎么着后续的十几次迭代迁移从代码提交到验证完成平均耗时不到半小时而且每次都能生成一份详细的差异报告。这就是我想跟你聊的核心把MySQL到PG的迁移MySQL2PG当成一个持续集成、持续交付的过程而不是一次性的、充满未知的“大爆炸”。手动迁移就像蒙着眼睛走钢丝而CI流水线则是给你装上了安全绳、探照灯和实时对讲机。它解决的不仅仅是“能不能迁过去”更是“怎么安全、可控、可验证地迁过去”。对于任何严肃的、有持续迭代需求的项目这套方法的价值远超几个转换脚本。简单说CI流水线能帮你做到三件事一致性每次迁移的环境、步骤完全一致、可观测性每一步都有日志、报告失败立刻知道卡在哪、可回滚性任何一步出错都能快速恢复到已知的安全状态。接下来我就结合我们趟过的坑拆解如何搭建这样一条“平滑迁移”的自动化流水线。2. 迁移流水线的核心架构与工具选型一套高效的MySQL2PG CI流水线其核心是建立一个可重复、可测试的自动化工作流。它不应该只是一个简单的“转换-执行”脚本而应该是一个包含环境管理、转换、测试、验证和部署的完整闭环。下图展示了我们最终采用的流水线核心阶段与关键工具graph TD A[代码/结构变更提交] -- B{CI Pipeline 触发}; B -- C[Stage 1: 环境准备与快照]; C -- D[Stage 2: 结构迁移与转换]; D -- E[Stage 3: 数据迁移与校验]; E -- F[Stage 4: 应用测试与验证]; F -- G{所有验证通过?}; G -- Yes -- H[Stage 5: 生产就绪与报告]; G -- No -- I[失败处理与通知]; I -- J[流水线终止 保留调试环境]; H -- K[生成迁移报告与回滚方案]; subgraph C [Stage 1 工具] C1[Docker] C2[pg_dump / mysqldump] end subgraph D [Stage 2 工具] D1[pgloader] D2[自定义Python脚本] D3[Liquibase/Flyway] end subgraph E [Stage 3 工具] E1[数据校验脚本] E2[行数对比] E3[抽样校验] end subgraph F [Stage 4 工具] F1[应用测试套件] F2[集成测试] F3[性能基准测试] end这个架构的关键在于每个阶段都是独立的、可验证的并且为下一个阶段提供明确的输入和成功标准。下面我们来详细拆解每个阶段的具体实现。2.1 阶段一环境准备与基线捕获——杜绝“我机器上好好的”迁移失败最常见的原因之一就是环境不一致。“在我本地用Python 3.8写的转换脚本在服务器Python 3.6上跑就报错”。我们的解决方案是用Docker容器化所有环境。首先在项目的代码仓库里我们会维护两个关键的Dockerfile一个用于MySQL源库一个用于PostgreSQL目标库。这不是为了运行生产服务而是为了在CI流水线中创建完全干净的、版本固定的数据库实例。# Dockerfile.mysql-source FROM mysql:8.0 # 设置默认字符集为utf8mb4 避免迁移过程中的乱码问题 RUN echo [mysqld]\ncharacter-set-serverutf8mb4\ncollation-serverutf8mb4_unicode_ci /etc/mysql/conf.d/charset.cnf # 复制初始化脚本 用于创建测试所需的特定用户和权限 COPY ./ci/init-mysql.sql /docker-entrypoint-initdb.d/# Dockerfile.pg-target FROM postgres:15-alpine # PostgreSQL 15 默认编码就是UTF-8 通常无需额外配置 # 同样复制初始化脚本 COPY ./ci/init-postgres.sql /docker-entrypoint-initdb.d/注意MySQL的utf8mb4和PostgreSQL的UTF-8虽然都是UTF-8编码但名称不同在流水线脚本中需要明确指定避免混淆。在CI脚本如.gitlab-ci.yml或.github/workflows/migrate.yml中第一步就是启动这两个容器# .github/workflows/migrate.yml 片段 jobs: migrate-test: runs-on: ubuntu-latest services: mysql: image: mysql:8.0 env: MYSQL_ROOT_PASSWORD: ${{ secrets.MYSQL_ROOT_PW }} MYSQL_DATABASE: source_db options: - --health-cmdmysqladmin ping --health-interval10s --health-timeout5s --health-retries3 postgres: image: postgres:15-alpine env: POSTGRES_PASSWORD: ${{ secrets.PG_POSTGRES_PW }} POSTGRES_DB: target_db options: - --health-cmdpg_isready -U postgres --health-interval10s --health-timeout5s --health-retries3环境就绪后下一步是捕获源库的基线。我们不会直接对生产库操作而是从生产库导出一份用于本次迁移测试的结构和数据快照。这里有一个关键技巧使用mysqldump时务必添加--skip-comments和--compact选项并确保使用--set-gtid-purgedOFF如果使用GTID以避免将MySQL特有的元信息带入转储文件这些信息可能会干扰后续的转换逻辑。# 在CI脚本中执行源库快照 mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD \ --single-transaction \ --routines \ --events \ --triggers \ --skip-comments \ --compact \ --set-gtid-purgedOFF \ source_db source_snapshot.sql这份source_snapshot.sql文件会被作为本次流水线运行的“唯一信源”后续所有转换都基于它。这样做的好处是即使生产库在迁移测试期间发生了新的变更也不会影响本次测试的稳定性确保了测试的独立性。2.2 阶段二结构迁移与自动化转换——核心攻坚战场这是技术挑战最集中的部分。我们采用“工具为主脚本为辅分层转换”的策略。首选工具是pgloader。它是一个用Common Lisp写的强大数据迁移工具内置了大量MySQL到PostgreSQL的转换规则。它的优势在于能自动处理很多常见的差异比如将TINYINT(1)转换为boolean将DATETIME转换为TIMESTAMP以及处理基本的索引和约束。我们在项目根目录维护一个pgloader.load配置文件LOAD DATABASE FROM mysql://$MYSQL_USER:$MYSQL_PASSWORDmysql:3306/source_db INTO postgresql://postgres:$PG_POSTGRES_PWpostgres:5432/target_db WITH include drop, create tables, create indexes, reset sequences, workers 4, concurrency 2, batch rows 10000, prefetch rows 50000 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null, type date drop default drop not null using zero-dates-to-null MATERIALIZE VIEWS my_view_1, my_view_2 BEFORE LOAD DO $$ ALTER DATABASE target_db SET search_path TO public; $$;提示zero-dates-to-null这个CAST规则非常重要。MySQL允许‘0000-00-00’这样的日期而PostgreSQL不允许。这个规则能将这些非法日期转换为NULL避免导入失败。这是早期我们踩过的一个大坑。然而pgloader不是万能的。对于复杂的存储过程、自定义函数、特定的触发器逻辑或者它无法完美处理的表结构我们需要辅助转换脚本。我们编写了一个Python脚本库使用sqlglot或sqlparse这类SQL解析库来做精细化转换。例如MySQL的AUTO_INCREMENT需要转为PostgreSQL的SERIAL或GENERATED BY DEFAULT AS IDENTITYPG10推荐。我们写了一个脚本片段来识别并转换# convert_auto_increment.py 片段 import re def convert_create_table(sql): # 将 AUTO_INCREMENT 转换为 GENERATED BY DEFAULT AS IDENTITY # 注意需要更复杂的解析来精确定位列名和类型这里简化示例 pattern r?(\w)?\s(\w\(\d\)|\w)\sAUTO_INCREMENT def replacer(match): col_name match.group(1) col_type match.group(2) # 将某些MySQL类型映射到PG类型 type_map {tinyint(1): boolean, int(11): integer, bigint(20): bigint} pg_type type_map.get(col_type.lower(), col_type.split(()[0]) return f{col_name} {pg_type} GENERATED BY DEFAULT AS IDENTITY converted_sql re.sub(pattern, replacer, sql, flagsre.IGNORECASE) return converted_sql所有转换脚本都必须有对应的单元测试并且转换规则需要版本化。我们在rules/目录下存放不同版本的转换规则集如v1.0-mysql-to-pg.json每次对转换逻辑的修改都对应一个规则版本确保历史迁移的可复现性。2.3 阶段三数据迁移与一致性校验——确保“一个都不能少”结构转换成功后就可以导入数据了。如果使用pgloader它通常会在结构迁移后自动进行数据加载。如果使用自定义流程则可能需要先执行转换后的DDL数据定义语言文件创建表结构再用pg_restore或psql配合COPY命令导入数据。数据校验是此阶段的生命线。绝对不能只相信“导入成功”的日志。我们的流水线包含多层校验行数校验最简单的“ sanity check”。对每个表分别从源库和目标库执行SELECT COUNT(*)比较结果是否一致。这能快速发现因转换错误导致的数据截断或重复。-- 在CI脚本中通过客户端执行 -- MySQL SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema source_db; -- PostgreSQL SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables WHERE schemaname public;然后编写一个脚本对比两个结果集对行数差异超过一定阈值如0.1%的表进行标记和详细检查。抽样校验行数一致不代表数据一致。我们编写校验脚本对每个表随机抽取一定比例如0.5%的行或者针对主键进行哈希校验如MD5(concat(col1, col2, ...))比较源和目标的数据是否完全匹配。对于大表可以按时间范围或主键范围分区进行抽样。约束与关系校验检查外键约束是否生效唯一索引是否被破坏。可以通过尝试插入重复数据或违反外键的数据来测试。-- 示例检查外键约束 -- 在目标库执行 预期应返回0行 SELECT COUNT(*) FROM child_table ct LEFT JOIN parent_table pt ON ct.parent_id pt.id WHERE pt.id IS NULL;这些校验步骤如果全部通过会给流水线打上一个“数据一致性验证通过”的标签为后续的应用测试奠定坚实基础。2.4 阶段四应用测试与功能验证——从数据库到业务数据库迁移的最终目的是让应用能无缝运行。因此在CI流水线中运行应用的全套测试是必不可少的。这包括单元测试连接迁移后的PG数据库运行所有DAO数据访问对象层或Repository层的单元测试。这能快速发现因SQL语法差异如LIMITvsFETCH FIRST、函数差异如DATE_ADDvs INTERVAL导致的问题。集成测试/API测试启动一个连接了目标PG数据库的应用实例运行完整的API测试套件验证所有业务流程是否正常。性能基准测试可选但推荐运行一些核心业务场景的基准测试对比迁移前后在相同数据量下的响应时间。虽然测试环境与生产环境有差异但大幅度的性能劣化如慢查询增加数倍仍然是一个重要的风险信号。为了实现这一点我们需要在CI中动态配置应用连接。通常我们会使用环境变量或配置文件模板在流水线运行时将数据库连接字符串指向刚刚搭建好的PostgreSQL测试容器。# 在CI中启动应用测试的示例步骤 - name: Run Application Tests against PG run: | # 动态生成指向CI中PostgreSQL容器的配置文件 echo DATABASE_URLpostgresql://postgres:${{ secrets.PG_POSTGRES_PW }}postgres:5432/target_db .env.test # 运行测试套件 例如对于Node.js应用 npm test -- --configci env: NODE_ENV: test这个阶段如果出现测试失败流水线会立即停止并保留完整的测试环境和日志供开发者排查。这比在生产环境切换后发现应用报错要安全得多成本也低得多。3. 流水线中的“安全气囊”回滚方案与差异报告即使前面的步骤都成功了在最终切流前我们还需要两个“安全气囊”清晰的回滚方案和详细的差异报告。回滚方案不是简单的“用备份恢复”。在持续迭代的项目中从你开始迁移测试到最终上线生产库可能已经有了新的变更。因此回滚方案需要包含两部分数据回滚基于迁移开始时创建的MySQL快照并结合之后的binlog或变更日志计算出“增量变更”以便在需要时反向同步回MySQL。对于简单的场景可以计划一个短暂的维护窗口直接切换到MySQL的从库或基于快照恢复。应用回滚确保应用代码本身支持快速切换数据库连接。可以通过功能开关Feature Flag或配置热加载来实现避免重新部署。差异报告是每次流水线运行的宝贵产出。它不仅仅是一份“成功/失败”的日志而是一份结构化的、人类可读的迁移摘要。我们的流水线会使用脚本自动生成一份Markdown或HTML报告包含概览迁移表数量、视图数量、存储过程数量、总数据行数。转换详情列出所有被自动转换的数据类型、被重命名的对象如因关键字冲突、被注释掉或需要手动处理的特殊语法。警告与错误按严重等级分类的所有问题。性能对比关键查询在迁移前后的执行计划或耗时对比在测试环境。下一步行动明确列出需要开发人员手动Review和处理的条目。这份报告会被作为CI Artifact保存并可以自动发送到团队的协作工具如Slack、钉钉群或项目管理工具如Jira中成为技术评审和上线决策的依据。4. 从CI到CD构建完整的迁移上线流水线将上述所有阶段串联起来就形成了一条完整的MySQL2PG迁移CI/CD流水线。以下是一个基于GitHub Actions的简化版完整流程示例name: MySQL to PostgreSQL Migration Pipeline on: push: branches: [ main ] pull_request: branches: [ main ] # 也可以手动触发 workflow_dispatch: jobs: full-migration-test: runs-on: ubuntu-latest services: mysql: ... postgres: ... steps: - name: Checkout Code uses: actions/checkoutv4 - name: Setup Environment Capture Baseline run: | # 1. 启动服务容器已在services中定义 # 2. 从生产只读从库获取快照 (模拟) ./scripts/fetch_mysql_snapshot.sh env: ... - name: Convert Schema DDL run: | # 1. 使用pgloader进行初步转换 pgloader ./ci/pgloader.load # 2. 运行自定义精细转换脚本 python ./scripts/refine_schema.py continue-on-error: false # 任何错误都终止流水线 - name: Data Integrity Validation run: | # 运行行数校验和抽样校验脚本 python ./scripts/validate_row_counts.py python ./scripts/validate_data_samples.py continue-on-error: false - name: Run Application Test Suite run: | # 配置应用连接PG测试库并运行测试 ./scripts/run_app_tests_against_pg.sh env: ... - name: Generate Migration Report if: always() # 无论成功失败都生成报告 run: | python ./scripts/generate_migration_report.py env: ... - name: Upload Migration Report if: always() uses: actions/upload-artifactv4 with: name: migration-report-${{ github.run_id }} path: ./migration_report.html - name: Notify Team (on failure) if: failure() uses: 8398a7/action-slackv3 with: status: failure channel: #db-migration-alerts env: ...这条流水线会在每次代码提交到主分支或创建Pull Request时自动运行。对于团队来说它意味着每次提交都是一次迁移演练问题在开发早期就能暴露。迁移过程文档化、代码化新成员也能快速理解并参与。上线信心极大增强因为最终的“上线”操作可能只是将经过数十次CI验证的迁移脚本和配置在低峰期于生产环境再执行一次。5. 实战中的坑与应对策略理论很美好但现实总会给你“惊喜”。分享几个我们实践中遇到的典型问题和解决思路问题一时区处理的陷阱MySQL的TIMESTAMP类型会隐式地将存入的时间转换为UTC存储并根据连接时区返回。而PostgreSQL的TIMESTAMPTZTIMESTAMP WITH TIME ZONE存储的是带时区信息的时间戳显示时根据当前会话时区转换。如果迁移时不做处理业务时间可能全部错乱。应对在转换脚本中明确处理时区。一种方法是在导出MySQL数据时使用CONVERT_TZ()函数将所有TIMESTAMP字段统一转换为UTC时间字符串。在导入PostgreSQL时确保目标字段类型为TIMESTAMPTZ并且数据库会话时区设置为UTC。更稳妥的做法是在应用层就规范使用UTC时间并在连接数据库时显式设置时区。问题二隐式类型转换的副作用MySQL的SQL模式比较宽松允许一些隐式类型转换比如SELECT * FROM table WHERE string_column 123可能会将字符串转换为数字进行比较。PostgreSQL则严格得多这样的语句会直接报错。应对在CI的应用测试阶段必须覆盖全面的查询测试。同时可以在迁移后对PostgreSQL开启更严格的类型检查虽然不是默认设置但可以提醒并在测试阶段使用pg_query_rewrite之类的工具或自定义中间件尝试捕获和记录可能存在的隐式转换供开发人员修复。问题三自增序列AUTO_INCREMENT与缓存将MySQL的AUTO_INCREMENT转换为PostgreSQL的IDENTITY或SERIAL时需要注意序列的当前值。如果迁移过程中表里有数据必须使用SETVAL函数将PostgreSQL序列的当前值设置为MySQL中AUTO_INCREMENT的最大值1否则后续插入可能会主键冲突。应对在数据导入完成后执行一个脚本遍历所有具有IDENTITY列的表查询其最大值并重置序列。-- 示例重置public.user表中id列的序列 SELECT setval(pg_get_serial_sequence(public.user, id), COALESCE(MAX(id), 0) 1, false) FROM public.user;问题四复杂业务逻辑的存储过程/函数这是手动工作量最大的部分。MySQL和PL/pgSQL语法差异显著变量声明、循环、游标、异常处理等。完全自动化转换几乎不可能。应对我们的策略是“分而治之”。在CI流水线的转换阶段使用脚本将这些对象原样提取出来但标记为“待手动转换”并放入一个单独的目录如/manual_review/stored_procedures/。在生成的差异报告中会重点列出这些对象。团队需要安排专人根据业务逻辑用PL/pgSQL重写。重写后的函数可以放入代码库并在后续的CI流水线中直接部署到测试PG库进行验证。将MySQL到PostgreSQL的迁移工程化、流水线化本质上是一种研发理念的转变把一次高风险、高不确定性的“黑盒”操作转变为一个可观测、可重复、可测试的标准化研发流程。它带来的最大收益不是迁移速度的提升而是风险的显性化和控制力的增强。每一次代码提交触发的流水线都是一次小规模、低成本的预演。当这条流水线在测试环境稳定运行数十上百次后你对最终的生产迁移所拥有的信心是任何手动检查都无法比拟的。