先说个题外话我身边不少同学提到 Navicat 就觉得它是数据库工具箱顶配日常连库、看数据、跑个查询确实舒服。可一旦到了把这张几千万行的表挪到另一台服务器或者把 MySQL 数据同步到 PostgreSQL的场景Navicat 的导出导入功能基本就成了一场煎熬。进度条转半天内存越吃越高最后还可能直接崩给你看。这篇文章不贩卖焦虑也不把 Navicat 说得一无是处我只想分享一个我实际踩坑踩出来的结论大批量 PostgreSQL 数据同步和跨库迁移应该交给更底层的开源工具。它们用 COPY 协议、并行分片和断点续传把同步这件事彻底提速——在我试过的场景里从 MySQL 迁 500 万行到 PostgreSQLNavicat 折腾 20 多分钟pgloader 跑完两分半快的不止 10 倍。而且这些工具完全开源支持跨库迁移也有日志和进度视图做可视化。这篇文章会从原理、选型、实操到排障完整走一遍适合所有被数据迁移折磨过的运维、后端和数据工程师。1. 先搞清楚为什么 Navicat 做数据迁移会那么慢1.1 慢的根本原因不在 SQL而在架构很多人以为 Navicat 同步慢是因为它生成的 SQL 不够高效其实不完全是。最核心的问题出在它的执行路径它是一个图形化客户端数据要经过源库 → 客户端内存 → 目标库这样一条链路。你可以把 Navicat 想象成一个搬运工它每次从源库 SELECT 一批数据塞进自己的内存再拼成 INSERT 语句一条条往目标库插。这个链条上有三处天然瓶颈。第一客户端中转。源库和目标库之间所有数据都要先流经你本地电脑的内存和网卡如果你的机器性能一般或者网络链路有损耗整体吞吐瞬间就下来了。第二INSERT 语句逐条执行。每一条 INSERT 都要走完整的解析、权限检查、生成事务日志、写入数据页这条链路。单条插入和批量导入的开销完全不是一个量级。第三单线程串行。Navicat 的表导入导出基本是逐表进行的表内部也是顺序读取、顺序写入完全没利用上 PostgreSQL 的多核并行能力。这不是说 Navicat 做得不好而是这个产品天生就不是为了大数据量搬迁设计的。它更擅长的是交互式管理、写个小查询、导出一份报表。把它当成数据迁移工具属于用错工具。提示判断一个工具适不适合做大规模同步先问三个问题数据流是否经过客户端中转写入是否走批量 COPY 而不是单条 INSERT是否支持并行和断点续传Navicat 在这三项上都不占优势。1.2 三种常见龟速现场与真实体验我自己踩过的坑大致能归成三类。第一类是跨服务器拷贝大表。一张 2 亿行的业务日志表从生产 PostgreSQL 库同步到分析库Navicat 导出的 SQL 文件有好几十 GB中途还经常因为网络闪断导致导出失败。重新来一遍的时间成本非常高最后只能硬着头皮用命令行 psql 分片导入那体验完全不一样。第二类是异构数据库迁移。把 MySQL 里的用户表、订单表逻辑迁移到 PostgreSQLNavicat 虽然有表结构和数据的转换模板但批量插入一次只能处理少量行跑了一整夜还没跑完第二天一看还有几个大表没处理。这个场景下Navicat 的转储 SQL功能还会因为语法兼容性问题报错你得手工改 SQL越改越崩溃。第三类是后续增量同步。Navicat 本质上只是一次性搬家工具如果你需要把源库实时的更新持续同步到目标库它根本做不了。真正的增量同步依赖的是 PostgreSQL 的逻辑复制机制或者专门的数据同步工具而不是图形客户端。这些痛点叠加起来我基本得出结论图形客户端适合看数据库不适合搬数据库。想要高效搬迁就得把目光投向那些直接和数据库底层协议打交道的开源工具。2. 开源工具阵营盘点这几个方案我实测能打先说结论目前开源工具里能干净利落处理 PostgreSQL 数据同步和跨库迁移的主力就三个pgloader、pgcopydb、pgsync。它们定位不同适合的场景也完全不同。2.1 pgloader全能型混源迁移选手pgloader 是我个人最喜欢的一个工具。它由 PostgreSQL 社区的资深开发者 Dimitri Fontaine 用 Common Lisp 编写虽然这个语言听起来有点冷门但工具本身非常成熟。它最强的地方在于异构迁移可以把 MySQL、SQLite、MS SQL Server、MongoDB 的数据迁移到 PostgreSQLPostgreSQL 到 PostgreSQL 的迁移也支持。它的底层逻辑很聪明读取源库数据后直接使用 PostgreSQL 的 COPY 协议批量装载数据同时通过多个 worker 并行读取。更重要的是它会自动迁移表结构、索引、外键、序列还可以在迁移过程中做数据清洗和类型转换。整个迁移过程会在终端输出实时进度和详细统计信息这样迁移过程中的可视化基本是开箱即用。适合场景MySQL / SQLite / SQL Server 迁移到 PostgreSQL以及轻量级的 PostgreSQL 互迁。我自己用的最多的就是 MySQL 搬迁到 PG。2.2 pgcopydbPostgreSQL 到 PostgreSQL 的事实标准pgcopydb 是 Dimitri Fontaine 的另一个作品是专为 PostgreSQL 到 PostgreSQL 迁移设计的。它在社区里口碑极好很多 PostgreSQL 大版本升级和高可用架构改造都在用它。它的核心思路是把 PostgreSQL 的物理备份和逻辑复制结合起来先用并行导出导入的方式把全量数据搬过去然后借助逻辑复制槽把搬迁期间产生的新增数据持续追平。这意味着它天然支持跨版本迁移比如从 PostgreSQL 13 迁到 16支持表空间、序列、权限、发布订阅等高级对象最重要的是它支持低停机时间迁移。你在业务运行期间就能把数据提前搬到新库最后切换的时候只需要让应用停机几秒钟让它把最后一点增量同步过去。适合场景PostgreSQL 大版本升级、跨机搬迁、异地容灾、低停机迁移。凡是源库和目标库都是 PostgreSQL 的严肃场景我基本都用 pgcopydb。2.3 pgsync开发环境数据刷新的小快灵pgsync 是 Andrew Kane 写的工具定位是开发者的贴心伴侣。它主要解决的是把生产库数据同步到本地开发库这类需求。你可以一条命令把生产环境某个表甚至整个库拉回本地它同样基于 COPY 协议也支持只同步部分数据、脱敏字段、指定并发度。pgsync 和上面两个工具不是一个赛道。它不是为几十 TB 的大库迁移准备的但如果你每周都要刷新开发环境或者需要把生产数据脱敏后同步到测试环境用 pgsync 能省下大量时间。它支持 PostgreSQL 之间的同步也支持 MySQL 作为源库。2.4 选型建议什么时候用哪个我整理了一个选型表方便大家按场景直接对号入座。场景首选工具备选原因MySQL/MSSQL/SQLite 迁到 PostgreSQLpgloaderpgsync仅部分场景异构支持最强自动转换类型和结构PostgreSQL 到 PostgreSQL 全量增量pgcopydbpgloader支持复制槽低停机对象覆盖全PostgreSQL 大版本升级pgcopydbpg_dump/restore物理快照逻辑复制迁移风险低生产库刷新到开发环境pgsyncpgloader轻量、快速、可做数据脱敏临时导出一张小表pgloader命令行 psql一条命令搞定实操心得工具不是选越先进的越好而是要匹配你的源库类型、目标库类型、停机窗口、数据量、对象复杂度这五个维度。我自己吃过亏为了图方便在 PG 到 PG 的迁移任务里用 pgloader 而不是 pgcopydb结果遇到物化视图和发布订阅等对象时要手动补很折腾。3. 上手实操pgloader 从 MySQL 迁移到 PostgreSQL 的完整流程跨库迁移是标题里最核心的关键词也是我日常用 pgloader 最多的场景。下面我以MySQL 业务库迁移到 PostgreSQL为例把完整流程拆开讲包括安装、配置、调优和核对。3.1 安装与前置检查pgloader 的安装非常友好。macOS 上执行brew install pgloaderDebian/Ubuntu 系执行apt install pgloaderCentOS 系的 EPEL 源里也有。如果你喜欢容器化直接跑docker pull dimitri/pgloader也行。这里有一个细节pgloader 是 Common Lisp 写的源码编译依赖 SBCL 和一堆 Lisp 库比较折腾我个人不建议源码编译除非你有特殊需求。安装完之后先别急着跑做几个前置检查确认源库和目标库都能从你的机器访问网络连通性没问题确认源库账号至少有读取权限目标库账号有建表、写数据、建索引的权限确认目标库的字符集和源库兼容尤其是源库是 latin1 或者 gbk 这种编码时提前想好转换规则大表迁移前先在目标库建一个空库别直接和现有数据混在一起。我习惯先跑一次pgloader --dry-run它会只分析对象和数据大小、打印迁移计划而不实际执行非常有用。3.2 写一个最小可用的 load 配置文件pgloader 支持命令行直接传连接串比如pgloader mysql://root:password192.168.1.10:3306/app_db postgresql://postgres:password192.168.1.20:5432/app_db_pg但真实项目里参数太多我推荐用.load配置文件。下面是一个我反复使用的最小配置LOAD DATABASE FROM mysql://root:password192.168.1.10:3306/app_db INTO postgresql://postgres:password192.168.1.20:5432/app_db_pg WITH workers 8, concurrency 1, batch rows 50000, batch size 64MB, prefetch rows 100000, create tables, create indexes, reset sequences, include drop SET PostgreSQL PARAMETERS maintenance_work_mem 1GB work_mem 128MB ;这个配置的含义并不复杂。workers 8是让 pgloader 用 8 个并发进程读取源库batch rows 50000和batch size 64MB控制每次 COPY 写入的数据量create tables和create indexes表示自动迁移表结构并在迁移后创建索引reset sequences是把自增序列重置到当前最大值。include drop则是如果目标库里已有同名表先删掉再重建。这里有个经验batch size不是越大越好。如果单批数据超过 64MB目标端的 WAL 写入压力会很大而且一旦某个批次失败回滚代价也高。我一般根据表大小调整几十 GB 的大表用 64MB小表用 16MB 就够。3.3 并行参数与 Batch 大小的调优很多人第一次用 pgloader 就直接默认参数跑结果发现速度一般以为工具不行。其实关键在参数调优。pgloader 的并行模型是源端多 worker 读取 目标端 COPY 写入你可以在配置里同时控制读取并发和写入并发。workers控制源端读取并发concurrency控制目标端写入并发。我自己的经验是源库是 SSD、目标库是 SSD 时workers 8, concurrency 2能跑满网络带宽源库是机械盘或者目标端 CPU 核数较少时workers 4, concurrency 1更稳定避免把目标库拖垮如果源库是 MySQL注意 MySQL 的max_allowed_packet限制批量读取时不要超过这个值否则会报错。另外一个容易被忽略的点是prefetch rows。这个参数控制 pgloader 在内存里预取的记录数相当于给管道加了个缓冲。如果这个值太小worker 经常要停下来等数据如果太大内存会飙高。我通常按总行数 / workers / 10估算一个合理的预取值。3.4 迁移完成后的核对迁移跑完终端会输出一张表级统计报表包含每个表的读行数、错误数、耗时和数据速率。但依赖报表还不够我还会做四个额外核对行数核对分别对源库和目标库执行 count(*)抽样几张表对比序列核对确认自增主键的序列当前值比表内最大 id 大否则插入会报主键冲突索引核对确认目标库的索引和源库一致尤其注意唯一索引外键核对把create indexes跑出来的索引、约束列出来和源库对比。如果迁移过程中有错误pgloader 会在工作目录下生成.reject文件里面记录每条失败记录的原因。这个文件非常有用我通常在排查个别记录为什么没进去时直接翻它省去写复杂 SQL 去比对。注意pgloader 的默认事务粒度是一批记录一个事务所以中途失败不会导致前面全部回滚。但这意味着失败后你需要认真看 reject 文件而不是直接重跑。重跑命令会先include drop把已有表删掉重建如果没有include drop则可能报表已存在。4. pgcopydb 实战跨库迁移 增量同步一步到位如果你的源库和目标库都是 PostgreSQL并且追求更低的停机时间和更高的对象兼容性pgcopydb 才是正主。这个工具的设计思路值得好好理解一遍理解了你就知道为什么它比 Navicat 快那么多。4.1 pgcopydb 的核心思路pgcopydb 本质上是一个编排器它把 PostgreSQL 生态里原本零散的能力组合成一条流水线。全量数据搬迁阶段它使用 pg_dump / pg_restore 的并行模式把表数据直接以 COPY 格式传输这比 Navicat 的逐条 INSERT 快一个数量级。增量追平阶段它利用 PostgreSQL 的逻辑复制功能。逻辑复制是 PostgreSQL 原生机制源库会在 WAL 日志里记录每一条数据变更逻辑复制槽负责把这些变更以流式方式推给目标端目标端再按顺序应用。pgcopydb 做的事情就是自动帮你配置好这一切省去手工执行CREATE SUBSCRIPTION等一堆命令的麻烦。你可以把 pgcopydb 理解成一个带增量功能的搬家队全量搬家的时候业务还在持续写入新数据搬家队一边搬旧家具一边盯着新搬进来的家具全部搬完之后再把新家具补齐最后让你无缝切换到新家。4.2 clone 一把梭全量 增量pgcopydb 的使用方式特别简洁它通过环境变量传递连接信息。我的标准操作流程是这样的export PGCOPYDB_SOURCE_PGURIpostgres://postgres:password192.168.1.10:5432/app_db export PGCOPYDB_TARGET_PGURIpostgres://postgres:password192.168.1.20:5432/app_db export PGCOPYDB_TARGET_DATADIR/tmp/pgcopydb pgcopydb clone --follow这里有三个环境变量PGCOPYDB_SOURCE_PGURI是源库连接串PGCOPYDB_TARGET_PGURI是目标库连接串PGCOPYDB_TARGET_DATADIR是 pgcopydb 存放快照和状态信息的目录。--follow参数表示全量迁移完成后继续跟随源库的增量变更。在执行之前我有几个习惯目标库先用createdb建好避免 pgcopydb 在目标库不存在时中途报错确认源库的wal_level为logical否则逻辑复制槽无法建立增量同步会失败如果目标库里已经有数据建议先备份pgcopydb 默认会重建表结构。执行过程中pgcopydb 会在PGCOPYDB_TARGET_DATADIR下生成一个.log文件。你可以用tail -f实时看迁移进度tail -f /tmp/pgcopydb/pgcopydb.log日志里会按表输出行数、耗时、速率信息密度比 Navicat 的进度条高得多。4.3 迁移进度与可视化怎么看标题里提到可视化这里我想多说一句数据同步类的可视化不一定非要有炫酷的 Web 界面。真正有用的可视化是能实时看到进度、延迟和异常。pgcopydb 和 pgloader 都遵循这个原则。pgloader 终端里的动态进度报表就是一种可视化它会每秒钟刷新一次显示当前表、已处理行数、失败数、当前速率。pgcopydb 的日志同样记录了每个表的处理情况。如果你还想看得更细可以直接查询 PostgreSQL 的进度视图。PostgreSQL 14 起有一个pg_stat_progress_copy视图专门展示正在进行中的 COPY 导入进度SELECT datname, command, phase, tuples_done, tuples_total FROM pg_stat_progress_copy;在 pgcopydb 或 pgloader 执行期间你能实时看到已经 COPY 了多少行总行数大概多少这对大表迁移非常有用。增量同步阶段关注两个东西复制槽的restart_lsn和目标库的pg_stat_subscription视图PostgreSQL 10 以后都有。复制槽的延迟增长说明目标端跟不上源端的写入速度这时候需要排查目标库的磁盘 I/O 或 CPU。如果你确实需要一个面向领导的、看得见摸得着的大屏也简单把pg_stat_replication和pg_stat_subscription的数据采集到 Prometheus再接 Grafana 做个迁移动态面板延迟、速率、WAL 堆积量一目了然。这个后文我会再细说。5. 跑数加速背后的原理COPY、并行、断点续传把工具用熟之后我建议花点时间理解它们为什么快。理解了原理遇到问题时你就知道从哪个方向排查。5.1 COPY 协议为什么比 INSERT 快Navicat 的默认导出行径是生成INSERT INTO ... VALUES (...)语句而开源工具用的是 PostgreSQL 的 COPY 协议。COPY 这条路径是完全绕开标准 SQL 解析的它直接把数据按二进制或文本格式写入表跳过查询计划器也不需要逐条维护对触发器的支持除非你显式开启。有人做过对比同样插入 100 万行数据单条 INSERT 可能需要几十秒到几分钟而 COPY 一般只要几秒。COPY 更快的原因很简单——它把100 万次零散操作变成100 万行数据一次到手数据库自己批量写。这个和你在文件系统里逐个创建 1 万个小文件和打包成一个 tar 包再解压是类似的逻辑。开源工具选择 COPY 作为核心装载方式等于选择了数据库厂商提供的最快写入通道。5.2 并行分片如何正确设置光有 COPY 还不够单个连接读数据还是有上限。pgloader 和 pgcopydb 都采用多 worker 并行读取源库的方式。pgloader 会自动把一张大表按主键范围切成多个分片由不同 worker 分别读取最后目标端再用 COPY 批量写入。这样源端多个连接同时读目标端也有多个 COPY 流在写系统资源的利用率才真正上来。这里就引出两个经验第一并行度不是越高越好。workers 数量超过源库 CPU 核数源库的查询解析就会成为瓶颈超过目标库 CPU 核数目标端的索引维护和 WAL 写入会成为瓶颈。我一般从 4 起步压测后逐步往上加找到一个吞吐峰值而不是盲目往 32、64 加。第二大表是否有主键直接影响分片效率。没有主键的表pgloader 没法高效切片只能退化为单线程顺序读取速度会明显下降。所以在迁移前我会给源库那些没有主键的大表临时加一个自增主键或者选择一个唯一列作为分片键。5.3 断点续传与异常恢复Navicat 的导入导出最让人恼火的一点是跑到 80% 网络断了整个活儿白干。开源工具对这种情况的处理就好得多。pgsync 从设计上就内置了先创建临时表全部导入完成后替换正式表的机制失败后重跑不会产生脏数据。pgloader 的每个批次是独立事务失败后你只要处理 reject 文件里的错误记录或者调整参数重跑即可。pgcopydb 更彻底它支持断点续传迁移过程中会在本地数据目录里保存每一步的状态重启后可以接着跑不用从头再来。这一特性在几十 GB 甚至几 TB 的库上价值巨大。我见过有人用传统方式迁移 2TB 的库中途断了几次硬生生折腾了一个星期后来改用支持断点续传的工具一个通宵全部搞定。实操心得如果你的迁移任务非常重我强烈建议全程守着日志而不是撒手不管。启动命令不要用nohup简单丢后台建议配合tmux或screen会话这样你可以随时回到终端看进度、断掉重连也不会把命令搞丢。6. 常见问题与排查技巧实录工具用久了各种怪问题都见过。我把最常见的几类问题和排查思路整理如下希望帮你少走一些弯路。6.1 内存与带宽问题高并发迁移时最常见的是内存被吃满。pgloader 的预取行数和批次大小都挺吃内存如果超过机器物理内存进程会被 OOM Killer 干掉。遇到这种情况先别急着加机器配置检查一下你的prefetch rows是不是设得太大。比如一张表 1 亿行prefetch rows 100000再乘上 8 个 worker内存瞬间就爆了。我通常把预取值控制在总行数 / 1000左右。网络带宽问题则比较隐蔽工具在本地跑得飞快一旦源库和目标库跨机房速度就断崖式下跌。你需要先用量带宽工具跑一下源库到目标库的网速如果带宽本身只有 20Mbps那工具再快也没有用瓶颈在链路上。这种情况下只能考虑在源库所在机房先做一次本地同步再用物理方式搬运快照或者提高链路带宽。6.2 字符集与编码踩坑异构迁移最容易出错的就是字符集。源库是 MySQL 的utf8mb4目标库是 PostgreSQL 的UTF8还好但如果源库是gbk或latin1目标库里就可能出现乱码或者非法字节序列错误。我的处理方式是在 pgloader 的配置里显式指定源端编码转换规则比如在FROM mysql://...的 MySQL 连接串里加上?charsetgbk参数让 pgloader 按正确编码读取。目标端 PostgreSQL 则必须保证数据库本身是 UTF8 编码这样字符串可以在写入时统一转换到 UTF8。还有一个隐蔽问题MySQL 里的DATETIME默认不带时区迁移到 PostgreSQL 的timestamp without time zone时看似正常但如果你后续要做跨时区分析一定要在迁移前想清楚是否要转成timestamptz。这类逻辑问题工具不会帮你区分必须自己提前调研。6.3 权限与参数检查清单权限这种东西平时风平浪静迁移时刻突然跳出来卡你一下。我把容易踩的坑整理成了一张清单建议执行前逐项核对检查项源库目标库说明连接权限SELECT, SHOWCREATE, INSERT大表迁移前的权限预检wal_levellogicalpgcopydb 增量需要用不改源库要支持逻辑复制max_replication_slots至少 1 个空闲不需要源库要能建立复制槽磁盘空间需预留 WAL建议 1.2~1.5 倍数据量pgcopydb 快照和数据双份maintenance_work_mem无大一点影响索引创建速度目标库版本无建议与源库相同或更高高版本迁低版本容易遇到兼容问题另外一个常见坑是目标库的 PostgreSQL 版本低于源库导致 pg_dump 导出的对象定义在高版本上才能解析。所以我一贯的建议是目标库版本尽量不低于源库最好比源库高一到两个大版本。跨版本升级本来就是 pgcopydb 的强项别浪费这个能力。6.4 快速排查速查表最后给一个排查速查表都是我在实际操作中反复用到的症状可能原因快速解决办法迁移刚开始就报连接失败连接串写错、防火墙封端口、密码带特殊字符未转义先用 psql 手动测通连接迁移中途进程被杀内存不足、batch 太大降低 prefetch 和 batch size或者分表迁移目标库索引创建极慢maintenance_work_mem 太小在配置里SET maintenance_work_mem to 2GB大表迁移速率不稳定网络抖动、源库负载波动降低并发、增加超时容忍、避开业务高峰期增量同步不生效源库 wal_level 不是 logical修改wal_levellogical后重启源库主键冲突目标库已有残留数据清理目标库或用include drop重建乱码源库字符集和目标库不一致在连接串中指定 charset转换到 UTF8我自己在实测中还有一个体会很多问题是因为没看日志造成的。pgloader 的 reject 文件、pgcopydb 的 log 文件里问题原因写得明明白白。有时候你只要打开日志看一眼问题就解决一半了。不要凭感觉猜日志不会骗你。最后再分享一个小技巧。大数据量迁移之前我总会先拿一张中等大小的表比如几十万行完整跑一遍流程从配置、执行到核对都过一遍。这样能把大部分权限问题、编码问题、网络问题暴露在小范围内真正跑全库的时候就心里有底了。这套先小后大、先局部后全量的思路比任何工具技巧都管用。