明确迁移目标

• 降低成本：摆脱

  MySQL

  Enterprise）

• 功能增强：利用

  JSONB、GIS（PostGIS）、全文检索、窗口函数、物化视图等高级特性

• 架构统一：已有

  技术栈，希望统一数据库平台

• 云战略：迁移到

  AWS

  模式，识别潜在问题：

  差异点	MySQL	PostgreSQL	迁移影响
  标识符大小写	默认不区分（Linux 区分）	严格区分（"Name"≠name）	需统一命名规范
  字符串比较	忽略尾部空格	严格比较	可能导致 JOIN/UNIQUE 失败
  布尔类型	无原生 BOOLEAN，用 BOOLEAN	需转换字段类型
  自增主键	AUTO_INCREMENT	SERIAL/IDENTITY	DDL 需重写
  LIMIT/OFFSET	LIMIT offset, count	LIMIT count 需调整
  字符串连接	CONCAT()或 `	`（需开启）
  日期时间	DATETIME无时区	TIMESTAMP/TIMESTAMPTZ	时区处理需谨慎
  默认值函数	NOW(),CURDATE()	CURRENT_TIMESTAMP,CURRENT_DATE	函数替换
  反引号	`table`	双引号"table"或无引号	需转义或移除

  推荐工具：Ora2Pg（虽名含

  Ora，但支持

  模式解析）、pgloader内置检查、自定义脚本。

  1.3

  数据量与停机窗口评估

  • 小数据量（<

    100

    GB）：可接受短暂停机（分钟级）

  • 中大数据量（100

    TB）：需采用在线迁移

    +

    增量同步，最小化停机时间

  • 超大数据量（>

    TB）：考虑分库分表迁移、并行导出导入

  主流工具概览

  工具	类型	优点	缺点	适用场景
  pgloader	开源 ETL	自动转换 DDL/DML，支持在线迁移，速度快	复杂函数/存储过程不支持	中小规模，结构简单
  AWS DMS	托管服务	支持 CDC（变更数据捕获），零停机	费用高，需 AWS 环境	云上迁移，大企业
  MySQL PostgreSQL Converters)	商业软件	图形界面，支持触发器/存储过程	闭源，收费	无开发资源团队
  自定义脚本（mysqldump + psql）	手动	完全可控	易出错，维护成本高	特殊定制需求
  Debezium + Connector	流式架构	实时同步，高可用	架构复杂	超大规模，高可用要求

  推荐组合：

  • 中小项目：pgloader（主力）

    +

    手动修正

  • 大型项目：AWS

    DMS或Debezium（增量）

    +pgloader（全量）

  />

  三、使用

  进行自动化迁移（推荐方案）

  3.1

  pgloader

  #
  Ubuntu/Debiansudoaptinstallpgloader#
  macOSbrewinstallpgloader#
  源码编译（推荐最新版）gitclone
  https://github.com/dimitri/pgloader.gitcdpgloadermakepgloader

  3.2

  编写迁移配置文件（migrate.load）

  LOAD
  DATABASE
  mysql://user:pass@mysql-host:3306/source_db
  INTO
  postgresql://pguser:pgpass@pg-host:5432/target_db
  WITH
  like'users','orders',
  create
  NOW();CREATE
  INDEX
  orders(user_id);$$;

  3.3

  关键配置说明

  • include

    only

    names：按需迁移表，避免无关数据

  • create

    tables：自动建表（基于转换后

    DDL）

  • reset

    序列值

  • CAST：类型映射规则（核心！）
    • datetime

      timestamptz：保留时区信息

    • tinyint(1)

      0/1

      true/false

    • varchar(N)

      varchar：移除长度限制（PG

      支持无限长）

  • AFTER

    LOAD

    SQL（如重建索引、设默认值）

  3.4

  migrate.load

  输出包含错误日志、性能统计、未处理对象列表，便于排查。

  />

  四、数据类型与函数转换详解

  4.1

  常见数据类型映射

  MySQL 推荐类型	注意事项
  INT,BIGINT	INT4,INT8	直接映射
  TINYINT(1)	BOOLEAN	仅当表示真假时
  TINYINT(n>1)	SMALLINT	避免误转 BOOLEAN
  VARCHAR(N)	VARCHAR或TEXT	PG 无性能差异，建议用 TEXT
  TEXT,MEDIUMTEXT	TEXT	统一为 TEXT
  DATETIME	TIMESTAMP WITHOUT ZONE	若无时区需求
  DATETIME	TIMESTAMPTZ	若需时区转换（推荐）
  DATE	DATE	直接映射
  DECIMAL(M,D)	NUMERIC(M,D)	精确数值
  FLOAT,DOUBLE	REAL,DOUBLE PRECISION	直接映射
  ENUM	TEXT+ CHECK ENUM
  JSON	JSONB	推荐 JSONB（支持索引、更快）
  BLOB	BYTEA	二进制数据

  4.2

  函数与操作符转换

  MySQL 等效写法
  NOW()	CURRENT_TIMESTAMP
  CURDATE()	CURRENT_DATE
  IFNULL(a, b)	COALESCE(a, b)
  ISNULL(a)	a NULL
  CONCAT(a, b)	`a
  SUBSTRING(str, pos, len)	SUBSTR(str, pos, 开始）
  RAND()	RANDOM()
  LIMIT 10, 10
  `column`	"column"或直接column（若无关键字冲突）

  建议：在应用层统一替换，或通过

  PostgreSQL

  的自定义函数兼容：

  CREATEFUNCTIONifnull(anyelement,anyelement)RETURNSanyelementAS$$SELECTcoalesce($1,$2);$$LANGUAGEsql;

  MySQL

  AUTO_INCREMENT

  • 每张表独立计数器
  • 插入时可显式指定

    ID（需开启NO_AUTO_VALUE_ON_ZERO）

  5.2

  PostgreSQL

  的实现方式

  方式一：SERIAL（旧版）

  CREATETABLEusers(idSERIALPRIMARYKEY,--
  自动创建
  users_id_seqnameTEXT);

  方式二：IDENTITY（SQL

  标准，PG

  推荐）

  CREATETABLEusers(idINTGENERATEDBYDEFAULTASIDENTITYPRIMARYKEY,nameTEXT);

  5.3

  迁移后修复序列值

  若数据已导入但序列未更新，会导致主键冲突：

  --
  方法1：pgloader
  sequences）--
  方法2：手动重置SELECTsetval('users_id_seq',(SELECTMAX(id)FROMusers));

  注意：IDENTITY列的序列名可通过pg_get_serial_sequence('users',

  'id')获取。

  索引转换

  • 普通索引：CREATE

    INDEX直接迁移

  • 唯一索引：UNIQUE约束自动创建索引
  • 全文索引：MySQL

    GIN/GiST

    索引

    ALTERTABLEarticlesADDCOLUMNtitle_ts
    tsvector;UPDATEarticlesSETtitle_ts=to_tsvector('english',title);CREATEINDEXidx_title_ftsONarticlesUSINGGIN(title_ts);

  • 前缀索引：MySQL

    支持INDEX(col(10))，PG

    不支持，需用表达式索引：

    CREATEINDEXONusers((left(email,10)));

  6.2

  外键与约束

  • PG

    默认启用外键约束，确保参照完整性

  • 若

    MySQL

    未启用外键，迁移后需评估是否添加

  • 使用NOT

    VALID延迟验证大表外键：

    ALTERTABLEordersADDCONSTRAINTfk_userFOREIGNKEY(user_id)REFERENCESusers(id)NOTVALID;ALTERTABLEorders
    VALIDATECONSTRAINTfk_user;--
    后台验证

  6.3

  性能调优建议

  • 批量插入：关闭

    autovacuum、增大maintenance_work_mem

  • 并行迁移：pgloader

    的workers和concurrency参数

  • 目标库配置：

    shared_buffers
    =
    适配
    Sequelize
    TypeORM
    为postgres，调整连接字符串
    MyBatis：修改
    XML
    中的函数（如NOW()→CURRENT_TIMESTAMP）
    Laravel
    Eloquent：基本兼容，注意分页语法（skip/takevsoffset/limit）
    7.2
    SQL
    改造点
    场景
    MySQL
    PostgreSQL
    分页
    LIMIT
    OFFSET
    不能为负）
    插入返回
    ...;
    LAST_INSERT_ID();
    INSERT
    ...
    id;
    字符串转日期
    STR_TO_DATE('20250101','%Y%m%d')
    TO_DATE('20250101','YYYYMMDD')
    随机排序
    ORDER
    RAND()
    ORDER
    RANDOM()
    获取当前数据库
    DATABASE()
    current_database()
    7.3
    事务与锁行为差异
    默认隔离级别：MySQL（RR），PG（Read
    Committed）
    幻读处理：PG
    下无幻读（MVCC
    实现不同）
    锁粒度：PG
    行锁更精细，死锁检测更主动
    建议：测试高并发场景，必要时显式设置隔离级别：
    BEGINISOLATIONLEVELREPEATABLEREAD;
    MySQLSELECTCOUNT(*)FROMusers;--
    PostgreSQLSELECTCOUNT(*)FROMusers;

    8.2

    校验和比对（关键表）

    --
    MySQLSELECTMD5(GROUP_CONCAT(CONCAT(id,name,email)ORDERBYid))FROMusers;--
    PostgreSQLSELECTMD5(STRING_AGG(CONCAT(id,name,email),''ORDERBYid))FROMusers;

    注意：需处理

    NULL（CONCAT在

    NULL

    NULL，可用COALESCE）

    8.3

    1000

    行，逐字段比对

  • 重点验证：时间戳、浮点数、布尔值、JSON

    内容

  8.4

  应用端冒烟测试

  • 核心业务流程（注册、下单、查询）端到端测试
  • 监控错误日志、慢查询

  停机迁移（小系统）

  1. 停写

     MySQL

  2. 执行最终增量同步（如有）
  3. 切换应用连接字符串至

     PG

  4. 验证后恢复服务

  9.2

  +

  切读（中大型系统）

  1. 应用同时写

     MySQL

     PG（双写）

  2. 数据校验工具持续比对
  3. 逐步切读流量至

     PG（如

     实时同步（零停机）

     • 使用Debezium捕获

       MySQL

       PG

     • 切换瞬间仅需处理秒级延迟数据

     工具链：MySQL

     Debezium

     字符集与排序规则

     • MySQL

       默认：utf8mb4+utf8mb4_general_ci（不区分大小写）

     • PostgreSQL

       默认：UTF8+en_US.UTF-8（区分大小写）

     • 解决方案：
       • 应用层统一转小写比较
       • 或创建不区分大小写的

         collation（PG

         12+）：

         CREATECOLLATION
         case_insensitive(provider=icu,locale='und-u-ks-level2');CREATETABLEusers(nameTEXTCOLLATE"case_insensitive");

     10.2

     0000-00-00

     允许0000-00-00，PG

     不允许

  4. 迁移前清洗：

     UPDATEtableSETdate_col=NULLWHEREdate_col='0000-00-00';

10.3

用反引号包裹关键字（如`order`）

PG

用双引号（"order"），但建议重命名字段避免冲突

10.4

大对象（BLOB）迁移

• pgloader

  支持BYTEA，但超大文件（>1GB）建议单独迁移

• 或存储为文件路径，数据库仅存

  URL

的迁移是一项系统工程，成功的关键在于：

• 充分评估：识别语法、类型、行为差异
• 工具赋能：以pgloader为核心，自动化

  DDL/DML

  转换

• 渐进切换：通过双写、CDC

  实现零停机

• 全面验证：行数、校验和、业务逻辑三层保障
• 长期优化：迁移后利用

  特性（JSONB、分区表、扩展）提升系统能力

PostgreSQL

以其标准兼容性、功能丰富性、社区活跃度，已成为

MySQL

的强大替代者。

通过科学规划与严谨执行，迁移不仅是一次技术栈升级，更是系统架构现代化的重要契机。