跨MySQL与PostgreSQL服务器高效数据迁移方案咨询

哇，45分钟的同步耗时确实有点折磨人，咱们来聊聊几个能大幅提速的靠谱方案，都是生产环境里验证过的实用办法：

一、用数据库原生工具代替自定义代码（最快的全量同步方式） #

自己写PHP数组+implode()的方式本质是逐条/批量执行INSERT，效率远不如数据库原生的批量导入工具。推荐两种工具：

1. mysqldump + PostgreSQL COPY命令 #

这是最经典的组合，利用MySQL的导出工具生成标准CSV，再用PostgreSQL的COPY命令（这是PostgreSQL最快的导入方式，比INSERT快10-100倍）完成导入：

• 第一步：从MySQL导出CSV数据

# 导出指定表的数据为CSV，不含建表语句
mysqldump -u mysql_username -p --tab=/tmp --fields-terminated-by=',' --no-create-info your_mysql_database your_target_table

• 第二步：在PostgreSQL中导入CSV
  先确保PostgreSQL用户有权限读取导出的文件，然后执行：

-- 先截断目标表（如果需要全量覆盖）
TRUNCATE TABLE your_pg_target_table;
-- 用COPY导入数据
COPY your_pg_target_table FROM '/tmp/your_target_table.txt' WITH (FORMAT csv, DELIMITER ',', HEADER);

如果文件不在PostgreSQL服务器上，可以用COPY FROM STDIN结合管道，比如：

mysqldump -u mysql_user -p --skip-add-drop-table --no-create-info --tab - your_mysql_db your_table | psql -U pg_user -d your_pg_db -c "COPY your_pg_table FROM STDIN WITH (FORMAT csv)"

2. pgloader（异构数据库迁移神器） #

pgloader是专门为异构数据库迁移设计的工具，能直接连接MySQL和PostgreSQL，自动处理数据类型映射、编码转换，全程无需手动生成中间文件：

pgloader mysql://mysql_user:mysql_pass@mysql_host/mysql_db postgresql://pg_user:pg_pass@pg_host/pg_db

它默认会做全量同步，还支持增量同步，性能比自定义代码高几个数量级，配置也简单。

二、优化自定义代码的插入逻辑 #

如果必须用代码实现同步，那可以抛弃implode()生成单条INSERT的方式，改用以下两种更高效的方法：

1. 使用PostgreSQL的批量INSERT语法 #

不要每次插入1条数据，而是一次性插入多条（建议每次批量1000条左右，根据数据大小调整）：

// 假设$data是从MySQL获取的二维数组
$values = [];
foreach ($data as $row) {
    // 注意转义特殊字符，用参数绑定更安全
    $escaped = array_map(function($val) use ($pg_conn) {
        return pg_escape_string($pg_conn, $val);
    }, $row);
    $values[] = "('" . implode("', '", $escaped) . "')";
}

// 开启事务，减少事务提交开销
pg_query($pg_conn, "BEGIN");
$batchSize = 1000;
for ($i = 0; $i < count($values); $i += $batchSize) {
    $batch = array_slice($values, $i, $batchSize);
    $sql = "INSERT INTO your_pg_table VALUES " . implode(', ', $batch);
    pg_query($pg_conn, $sql);
}
pg_query($pg_conn, "COMMIT");

2. 使用PHP的pg_copy_from()函数 #

这个函数底层调用PostgreSQL的COPY协议，效率比批量INSERT还要高，直接把PHP数组导入到PostgreSQL表中：

// $data是从MySQL获取的二维数组，每个子数组对应一行数据
pg_query($pg_conn, "BEGIN");
// 临时关闭触发器提速，导入完成后再开启
pg_query($pg_conn, "ALTER TABLE your_pg_target_table DISABLE TRIGGER ALL");
pg_copy_from($pg_conn, 'your_pg_target_table', $data);
pg_query($pg_conn, "ALTER TABLE your_pg_target_table ENABLE TRIGGER ALL");
pg_query($pg_conn, "COMMIT");

三、增量同步（避免全量截断+导入） #

如果你的业务允许，完全可以不用每次全量同步，只同步新增或修改的数据，这能把耗时从几十分钟降到几秒：

1. 给MySQL的源表添加一个updated_at字段（或者用created_at+modified_at），记录每条数据的最后更新时间；
2. 第一次同步时全量导入，并记录最后同步的时间戳；
3. 后续每次同步时，只从MySQL查询WHERE updated_at > '上次同步时间'的数据；
4. 在PostgreSQL中用UPSERT语法（ON CONFLICT DO UPDATE）来处理新增和更新，不用截断表：

INSERT INTO your_pg_table (col1, col2, ...) VALUES (...)
ON CONFLICT (primary_key_col) DO UPDATE SET
col1 = EXCLUDED.col1,
col2 = EXCLUDED.col2,
...;

四、其他通用优化点 #

• 用TRUNCATE代替DELETE来清空表：TRUNCATE是DDL操作，直接释放存储空间，比逐行删除的DELETE快太多；
• 临时调整PostgreSQL的wal_buffers参数，增大事务日志缓冲区，减少磁盘写入次数；
• 确保MySQL和PostgreSQL服务器在同一个局域网内，减少网络传输延迟；

内容的提问来源于stack exchange，提问作者Sach jot