MySQL迁移到PostgreSQL实战：数据迁移工具与运维调优

MySQL与PostgreSQL对比：迁移评估与语法差异要点

MySQL迁移到PostgreSQL是国产化和架构升级中常见动作。两者都是关系型数据库，但语法、类型体系、事务与索引行为存在一批差异，直接复制SQL会出现大量报错。迁移前先把评估清单列出来：字符串类型映射、自增主键、limit语法、布尔值、日期时间类型、索引语法。

-- MySQL 常见写法
CREATE TABLE user (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO user (name) VALUES ('alice') ON DUPLICATE KEY UPDATE name = 'alice';

-- PostgreSQL 等价写法
CREATE TABLE user (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO user (name) VALUES ('alice')
ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name;

常见映射：TINYINT映射SMALLINT，DATETIME映射TIMESTAMPTZ，TEXT在两边都支持但行为略有不同。存储引擎概念在PostgreSQL中不存在，每张表都是堆表，不再有InnoDB与MyISAM之分。

数据迁移实战：pgloader与mysqldump转换方案对比

数据迁移有两条主流路径：pgloader工具和mysqldump导出后手工转换。pgloader支持直接读取MySQL连接，自动完成类型映射与在线写入；mysqldump方案适合小库，导出SQL后做正则替换和结构改造，可控性强但繁琐。

# pgloader 迁移配置
LOAD DATABASE
  FROM mysql://user:pass@mysql-host:3306/appdb
  INTO postgresql://user:pass@pg-host:5432/appdb

WITH include drop, create tables, create indexes, reset sequences
SET maintenance_work_mem TO '128MB'
CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type tinyint to smallint;

pgloader执行前先小表试迁移，核对类型映射和行数；大数据量表分批迁移，监控WAL写入。迁移期间应用保持只读，用复制或双写缓冲切换流量。

PostgreSQL迁移后验证：数据一致性校验与性能对比

数据迁移完成后需要做双维度验证：行数、哈希、关键字段抽样对比；性能基线要重新压测，因为PostgreSQL的统计信息和查询计划生成逻辑与MySQL不同，原索引不一定最优。

-- 行数校验
SELECT 'orders' AS tbl, count(*) FROM orders
UNION ALL SELECT 'users', count(*) FROM users;

-- 抽样字段级校验（可重复执行用于对比）
SELECT id, status, amount FROM orders
ORDER BY id LIMIT 100;

-- 分析统计信息，让规划器拿到准确基数
ANALYZE orders;
ANALYZE users;

迁移后先用EXPLAIN (ANALYZE, BUFFERS)复查热查询，确认走了正确索引；性能下降的SQL优先检查Join条件两端的类型是否一致，跨类型比较往往引发全表扫描。

PostgreSQL运维优化：work_mem、maintenance_work_mem与checkpoint调优

数据库迁移后调参集中在四个维度：shared_buffers、work_mem、maintenance_work_mem、checkpoint相关参数。shared_buffers通常设为物理内存25%，work_mem按并发连接数反推，排序和哈希操作的临时结果会占用这个内存。

-- 初始建议（总内存32GB，约80%分配给PG）
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET effective_cache_size = '24GB';
SELECT pg_reload_conf();

参数生效不需要重启，但shared_buffers等少数参数需要重启。调优依赖基线：先看pg_stat_statements里耗时Top SQL，再针对热点查询索引。checkpoint_warning与max_wal_senders配合监控WAL增长，防止迁移后的高频写入撑满磁盘。

MySQL迁移PostgreSQL常见坑：JSONB、事务隔离与约束差异

最容易踩的坑是JSON类型差异：MySQL JSON字段保留原始文本，PostgreSQL推荐JSONB，查询走GIN索引；但JSONB会对键排序和值去空格，业务侧若依赖原文输出需在应用层还原。事务隔离默认级别也不同：MySQL可重复读、PostgreSQL读已提交，长事务行为会有变化。

-- PostgreSQL 下 JSONB 查询示例
CREATE INDEX idx_meta ON orders USING GIN (metadata jsonb_path_ops);
SELECT * FROM orders WHERE metadata @> '{"region": "south"}';

约束方面，PostgreSQL的CHECK约束执行更严格，主键唯一约束处理重复的方式与MySQL不同，ON DUPLICATE KEY UPDATE要改写为ON CONFLICT。外键、级联删除和延迟约束都需要在迁移SQL中显式声明，不要依赖默认行为。

MySQL迁移到PostgreSQL并不是把DDL翻译一遍，而是类型、事务、索引、约束四个维度整体重构。先小表试点，再全量迁移，每一层校验通过后再切换流量，这套流程能把迁移风险控制在可接受范围。