一、迁移前的核心准备工作

1.1 环境评估与架构设计

在启动迁移前需完成三项关键评估：数据量级分析（单表超过500GB需考虑分片策略）、业务耦合度检测（识别存储过程、触发器等复杂逻辑）、高可用需求确认（PostgreSQL原生支持流复制与逻辑解码）。建议采用pg_dump和pg_restore进行基准测试，预估迁移耗时公式为：总时间=导出时间+传输时间+导入时间×并行因子。

1.2 迁移工具矩阵分析

工具类型	代表工具	适用场景	限制条件
命令行工具	pg_dump/pg_restore	结构+数据全量迁移	不支持存储过程转换
图形化工具	pgAdmin/DBeaver	小规模数据迁移	缺乏自动化转换能力
专用迁移工具	AWS Schema Conversion	复杂Schema转换	依赖云环境
开源转换框架	ora2pg（适配MySQL版）	自定义规则转换	需要二次开发

推荐组合方案：使用mysqldump --no-data导出结构后，通过ora2pg -t TABLE进行类型转换，最后用\copy命令实现高效数据加载。

二、数据类型转换规范

2.1 基础类型映射表

MySQL类型	PostgreSQL等效类型	转换注意事项
TINYINT	SMALLINT	需处理BOOL类型冲突
ENUM	TEXT+CHECK约束	需重建枚举值约束
DATETIME	TIMESTAMPTZ	注意时区处理差异
JSON	JSONB	索引方式不同

2.2 特殊类型处理方案

• 自增字段：MySQL的AUTO_INCREMENT需改为PostgreSQL的SERIAL或IDENTITY语法

  -- MySQL
  CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY);
  -- PostgreSQL
  CREATE TABLE users (id SERIAL PRIMARY KEY);
  -- 或PostgreSQL 10+版本
  CREATE TABLE users (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

• 全文索引：需将MySQL的FULLTEXT索引转换为PostgreSQL的GIN/GIST索引

  -- MySQL
  ALTER TABLE articles ADD FULLTEXT(content);
  -- PostgreSQL
  CREATE EXTENSION pg_trgm;
  CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));

三、SQL语法转换指南

3.1 常用语句转换对照

MySQL语法	PostgreSQL等效语法	差异说明
LIMIT offset,size	LIMIT size OFFSET offset	参数顺序不同
GROUP_CONCAT()	STRING_AGG(column, ‘,’)	需显式指定分隔符
IFNULL()/COALESCE()	COALESCE()（统一语法）	PostgreSQL更严格类型检查
DATE_FORMAT()	TO_CHAR()	格式字符串语法不同

3.2 存储过程重构策略

1. 流程控制转换：

   -- MySQL CASE语句
   CASE 
     WHEN score > 90 THEN 'A'
     ELSE 'B'
   END;
   -- PostgreSQL CASE（语法兼容但推荐使用IF）
   DO $$
   BEGIN
     IF score > 90 THEN
       RAISE NOTICE 'A';
     ELSE
       RAISE NOTICE 'B';
     END IF;
   END $$;

2. 异常处理机制：
   PostgreSQL使用BEGIN...EXCEPTION...END块替代MySQL的DECLARE CONTINUE HANDLER

   DO $$
   DECLARE
     div_zero EXCEPTION;
   BEGIN
     IF divisor = 0 THEN
       RAISE div_zero;
     END IF;
   EXCEPTION WHEN div_zero THEN
     RAISE NOTICE 'Division by zero';
   END $$;

四、性能优化实践

4.1 批量导入加速方案

1. COPY命令优化：

   # 生成CSV文件（注意NULL值处理）
   mysqldump -t -T /tmp db table --fields-terminated-by=',' --fields-enclosed-by='"' --null-string='\\N'
   # PostgreSQL高效导入
   COPY table FROM '/tmp/table.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

2. 并行加载策略：

   -- 创建多个会话同时导入不同分区
   SESSION 1: COPY table_part1 FROM '...';
   SESSION 2: COPY table_part2 FROM '...';

4.2 查询性能调优

1. 索引优化：

   • 为外键列创建索引
   • 对高频查询条件创建部分索引

     CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

2. 统计信息更新：

   ANALYZE verbose table_name;  -- 收集详细统计信息
   VACUUM FULL table_name;      -- 回收空间并重建表

五、迁移后验证体系

5.1 数据一致性校验

1. 行数核对：

   SELECT 'mysql' AS source, COUNT(*) FROM mysql.table
   UNION ALL
   SELECT 'postgres' AS source, COUNT(*) FROM postgres.table;

2. 抽样校验：

   -- 生成随机样本
   SELECT * FROM (
     SELECT *, ROW_NUMBER() OVER () AS rn 
     FROM postgres.table
   ) t 
   WHERE rn IN (SELECT FLOOR(RANDOM() * (SELECT MAX(rn) FROM t)) FROM generate_series(1,100));

5.2 性能基准测试

1. TPS对比测试：

   # 使用pgbench进行标准化测试
   pgbench -i -s 100 postgres_db  # 初始化100倍数据
   pgbench -c 50 -j 4 -T 60 postgres_db  # 50并发4线程运行60秒

2. 慢查询分析：

   -- 启用慢查询日志
   ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 记录超过1s的查询
   SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

六、持续维护方案

1. 模式演进管理：

   • 使用Liquibase或Flyway进行版本控制
   • 示例变更集：

     <changeSet id="1" author="dev">
       <addColumn tableName="users">
         <column name="phone" type="varchar(20)"/>
       </addColumn>
     </changeSet>

2. 连接池配置优化：

   # PgBouncer典型配置
   [databases]
   main = host=localhost dbname=postgres
   [pgbouncer]
   pool_mode = transaction
   max_client_conn = 100
   default_pool_size = 20

通过上述系统化方案，可实现MySQL到PostgreSQL的平滑迁移。实际案例显示，采用分阶段迁移策略（先结构后数据、先非核心后核心系统）可将业务中断时间控制在2小时以内。建议组建包含DBA、开发、测试的专项小组，通过Jira等工具进行任务跟踪，确保每个迁移阶段都经过严格验证。