如果你维护过几十个PostgreSQL实例你一定体会过用Navicat做跨库同步时那种“看着进度条发呆”的感觉。我之前因为每天要同步几十个表和几万行数据用Navicat导一个500MB的库少则十几分钟多则半小时中途网络稍微抖一下还得从头再来。后来我换了思路自己写了一个开源的PostgreSQL数据同步工具底层用原生COPY协议代替逐条Insert把同步时间从30分钟压缩到了3分钟以内说“速度快10倍”真不是标题党。这篇文章就把这个开源工具的核心设计、实现思路、实测数据和踩坑经验完整拆开讲全是在生产环境跑过的东西。如果你也正在用Navicat做PostgreSQL跨库迁移或者在找靠谱的开源数据同步方案看完这篇至少能少踩一半的坑。1. Navicat 做数据同步到底慢在哪1.1 为什么 Navicat 拿 50 万行没办法很多人对Navicat的数据传输功能有误解以为它是“在数据库内部搬数据”速度应该很快。实际上Navicat的同步逻辑很朴素先从源库执行SELECT把结果集加载到内存中再在目标库一条一条执行INSERT或者UPDATE最后提交事务。这种模式在处理几千行小表的时候没什么问题但数据量一上来性能就一落千丈。我用Navicat同步一个50万行的业务表时观察过它的执行状态源端的SELECT跑得很快但目标端的INSERT是逐条执行的每执行一条还有一次网络RTT。在普通千兆内网环境下即使一次INSERT耗时只有1毫秒50万行也要500秒这还不算索引维护、约束检查、事务日志刷新带来的额外开销。所以Navicat做跨库同步慢不是服务器不行而是它的工作方式就决定了它不可能快。另一个容易被忽视的问题是Navicat在目标库每次INSERT要么不带事务要么一次性提交一个大事务。不带事务时每条INSERT都要fsync一次把WAL写入磁盘性能更差带大事务时比如50万行一旦中途失败整个事务要回滚所有工作全部白费。Navicat的超时机制又常常导致长事务被数据库杀掉很多人在同步大表时遇到“connection reset”就是这个问题。1.2 真正的性能瓶颈逐行读取和逐行插入做PostgreSQL数据同步其实就是要解决两个核心问题从源库把数据读出来再把数据写进目标库。Navicat选择的是“逐行读取、逐行插入”的通用方案因为它需要兼容MySQL、SQL Server、Oracle等不同数据库只能采用最通用的JDBC/ODBC接口。而PostgreSQL本身有一个远没有被充分利用的能力——COPY协议。COPY TO可以一次性把整张表的数据以文本或二进制格式输出COPY FROM可以批量导数据底层走的是PostgreSQL原生协议数据不经过ORM、不需要拼INSERT、不需要反复解析SQL。同样是写10万行数据逐条INSERT可能需要1000秒而COPY FROM只需要10秒钟差距就是100倍。Navicat虽然也提供了“数据传输”功能但我实测下来它的实现并没有走COPY而是仍然走逐行Insert的方式所以速度一直上不去。这里需要解释一下COPY为什么快它会把数据打包进若干个缓冲区一次网络交互传输大量行然后在目标端由数据库服务进程直接写入堆表并维护索引。更关键的是PostgreSQL的COPY FROM支持在导入期间合并索引更新、延迟约束检查、减少WAL记录在配置了wal_levelminimal时这些都是逐条INSERT永远做不到的。1.3 10倍速度是怎么算出来的我在做性能对比时用同一台交换机下的两台PostgreSQL 16实例数据量是1.2GB约800万行表结构带2个索引和3个外键。Navicat的数据传输功能花了28分36秒才跑完中途还失败了一次重试后累计超过30分钟。换成基于COPY并发同步的开源工具全量导入部分只花了2分54秒加上结构比对和索引重建总共3分20秒左右。28分36秒对比3分20秒正好是8到10倍的差距。可能有朋友会说Navicat也可以分批插入、调整批量大小但即使我把批量调整为2000行一批观察到的耗时也只下降到了20分钟左右离“分钟级”相差很远。因为瓶颈不只是批量还有它要为每一批拼接JDBC SQL、反复解析和绑定参数。这个开销在数据量大时是逃不掉的。所以“速度快10倍”不是单纯靠调参数而是换了一条完全不同的技术路线。2. 开源工具的设计思路与技术选型2.1 为什么我抛弃 Navicat 转投开源方案当时我面临的需求比较简单把生产库的某几个schema每日同步到分析库不能停源库要支持跨主机最好还能在浏览器里看到同步进度。用Navicat手动操作有两个痛点一是慢二是无法自动化。每天的定时同步如果靠人半夜爬起来用鼠标点太痛苦了。一开始我也考虑过直接用pg_dump加pg_restore全量恢复的效率很高但它是实例级或库级粒度没法灵活选择表也没法做增量同步。后来看了pglogical、pgcopydb这些成熟项目确实强大但依赖关系复杂有些需要安装扩展有些还需要专门的运维知识。对于只想“把这几张表同步过去”的团队来说学习成本偏高。最后我决定基于PostgreSQL自身已有的能力开发一个轻量工具利用COPY协议做全量搬运配合wal2json或自带的逻辑解码做增量同步。这套方案不依赖任何商业软件只依赖PostgreSQL原生协议而且开发起来很顺手。我把它开源出去后不少同行反馈说配置比预期简单这也是我写这篇文章的原因。2.2 工具的架构与功能拆解这个开源工具整体分三层命令行入口、同步执行器、Web可视化面板。命令行入口负责解析同步配置比如源库连接串、目标库连接串、要同步的表列表、并发数、是否开启增量同步等。同步执行器是核心负责创建连接、执行元数据比对、启动全量COPY、监听WAL增量事件、把增量应用到目标库。Web面板则是一个独立的HTTP服务提供任务创建、进度百分比、同步延迟、日志查询等页面。核心设计思路是“全量增量”分离。全量同步阶段工具会在源库对每个表执行COPY (SELECT * FROM table) TO STDOUT然后在目标库用COPY FROM STDIN接收数据。为了实现并行工具会对每张表分配一个独立的goroutine同时使用连接池管理多个数据库连接。大表还可以按主键范围拆分为多个分片每个分片独立搬运类似于pgcopydb的做法。增量同步阶段工具会先在源库创建一个发布Publication然后连接数据库的逻辑复制槽持续读取变更事件。这里有个设计取舍如果目标库是另一个PostgreSQL实例最优雅的方式其实是直接用PostgreSQL原生的Subscription但为了让工具也支持“异构目标”和“跨大版本迁移”我还是自己实现了WAL消费端把decode出来的行变更按目标库SQL方言重放。虽然多写了一些代码但灵活性大大提升。2.3 依赖组件选型COPY、逻辑解码和并行 Worker选型的时候我想尽可能少引入外部组件。底层的数据库驱动用的是pgx它是Go生态里最成熟的PostgreSQL客户端原生支持COPY协议的读写接口。用pgx可以直接拿到CopyTo和CopyFrom方法不需要自己拼COPY命令的二进制格式省了很多事。如果你用Java技术栈则可以考虑CopyManager但整体思路是通用的。增量同步上我选择了PostgreSQL自带的逻辑解码机制。PostgreSQL 10以后内置了pgoutput插件pg_create_logical_replication_slot可以直接开启一个逻辑复制槽不需要额外安装系统库。当然要拿到JSON格式的变更事件还是需要wal2json插件。考虑到很多云数据库不允许安装插件我的工具自动检测如果数据库支持pgoutput就用pgoutput解析如果不支持就在提示中说明要用wal2json。实测中wal2json的事件解析要更直观一点但pgoutput更标准。并行Worker这块我采用的是动态并发。不是固定起多少个worker而是先按表数量分配再根据表体积调整分片数量。比如一张5GB的大表默认切成4到8个分片每个分片一个worker一堆小表则直接每个表一个worker避免频繁切换上下文。并发度可以通过--workers参数控制我建议不超过CPU核心数的2倍。3. 核心实现细节我用三个关键步骤完成跨库迁移3.1 全量同步第一步元数据预检和结构比对很多人做数据同步时只搬运数据忽略了表结构结果目标库没有对应字段或者字段类型不兼容搬过去之后应用直接报错。所以我的工具在全量开始之前会先连接源库和目标库拉取所有表的字段名、数据类型、主键、索引、约束、注释等信息然后生成结构差异报告。这个报告会明确告诉你有哪张表在目标库不存在哪个字段长度不够哪个数据类型在PostgreSQL 15和PostgreSQL 16之间发生了变化。对于在目标库不存在的表工具会执行一次CREATE TABLE语句并把源库的索引定义转换过去。对于存在的表默认只做数据追加不做结构变更这样避免误删目标表的字段。预检通过后工具会进入锁定状态对于每一张要同步的表先获取一个ACCESS SHARE锁然后读取表大小、估算行数。这一步不是必须的但有助于排序大表。我的策略是按表大小从大到小排序大表优先分配更多分片小表放在后面。这样在同步过程中总能看到进度条明显增长不会因为前面全是小表而卡住不动。结构比对的一个实现细节不要直接比对information_schema.columns因为它的视图层开销比较大而且对权限要求高。我使用的是系统目录表pg_attribute和pg_class查询效率高很多。字段类型判断也要用atttypid关联pg_type而不是依赖format_type因为后者在跨大版本时返回的格式可能有细微差异。3.2 数据搬运基于 COPY 的并行导入导出怎么实现核心的COPY流程可以这样理解源端执行COPY (SELECT...) TO STDOUT目标端执行COPY ... FROM STDIN中间直接把字节流从一个连接转发到另一个连接。在Go里用pgx实现代码短得让人意外srcConn.CopyTo(ctx, ioStream, fmt.Sprintf(SELECT * FROM %s, tableName)) dstConn.CopyFrom(ctx, ioStream, tableName, columns)这里的io.Reader和io.Writer连接在一起后数据就像水管一样从源库流向目标库。这个方案最核心的优势是流式处理不需要在本地落盘也不需要把整个表读进内存。如果直接调用pg_dump | psql虽然也走COPY但中间多了一层shell管道和进程切换而且很难做分片并行。为了支持分片并行我对SELECT语句做了动态处理如果表有主键就按主键范围拆分比如主键是id分成id 1000000、id between 1000001 and 2000000等区间每个区间单独执行一个COPY。如果表没有主键只能整表搬运但会通过tid物理行ID做粗粒度分片避免太大表在源库产生长事务。这里有个使用心得分片数量不宜过多否则源库需要同时增加很多读连接快熊期会造成源库CPU飙升。建议单表分片数控制在4以内。目标端导入时还有一个容易踩坑的细节外键约束。对带外键的表逐个导入会频繁触发约束检查影响速度。我的做法是在同步开始时对目标库先执行SET session_replication_role replica相当于临时禁用外键触发数据全部导入完成后再执行SET session_replication_role origin恢复。这样能省去大量约束校验的性能开销。需要注意这个操作需要超级用户权限而且导入期间不要并发写入目标表否则数据完整性无法保证。3.3 增量同步用 WAL 逻辑解码把变化“流式”搬过来全量同步做完之后源库生产环境还在继续写入如果只做全量目标库的数据会很快过期。增量同步的做法是在源库创建一个逻辑复制槽让数据库把每次INSERT、UPDATE、DELETE的变更写入WAL工具作为消费端订阅这些变更并应用到目标库。开启增量同步前需要修改源库配置。有两个参数必须确认wal_level要设为logicalmax_replication_slots至少为2。很多云数据库默认不允许手动改wal_level但你可以在控制台找到参数组修改。修改配置后需要重启实例这一步绕不过去。工具启动时流程如下源库执行CREATE PUBLICATION my_pub FOR TABLE t1, t2...。在源库创建逻辑复制槽pg_create_logical_replication_slot(sub_slot, pgoutput)。全量同步完成后从复制槽记录的LSN位置继续读取变更数据。对每个变更事件解析出表名、主键、新值、旧值翻译成对应的INSERT/UPDATE/DELETE语句在目标库执行。这里有一个关键点全量同步过程中如果源库同时有写入可能会造成数据不一致。解决办法是“先建立复制槽再开始全量同步”。这样从全量开始时刻起所有变更都会积压在复制槽里全量结束之后再追放积压的增量事件最终达到一致状态。我的工具在初始化时就会先建槽再开全量从机制上规避了数据差异。增量重放的时候为了提升速度我采用批量提交策略。比如从WAL读到1000条变更组装成批量事务再执行而不是一条一条提交同时只对目标表的索引做必要的增量维护。实测在每秒写入几百条的生产环境增量同步延迟能稳定在1秒以内。3.4 可视化看板如何实时跟踪任务进度与延迟开源项目如果只有命令行很多非DBA会觉得门槛高。所以我加了一个轻量Web面板用Go的net/http加嵌入式静态文件实现没有引入大框架。面板上能看到正在执行的同步任务列表、每张表的行数、已同步行数、当前速率、剩余时间以及增量同步的延迟秒数。实现进度跟踪的思路是每个同步worker会周期性地向一个全局状态管理器上报自己处理的偏移量。面板通过HTTP接口查询状态管理器再渲染到页面上。对于增量同步延迟是一个核心指标工具会记录源库当前WAL的LSN以及最近一次已消费的LSN两者之差除以wal_wal_rates估算延迟时间。初步版本用轮询刷新每2秒更新一次数据已经足够实时。可视化面板的价值不只是好看它能帮助你在同步卡住时快速定位问题。比如某张表的进度长时间不动多半是源库的COPY卡在等锁延迟持续增长说明目标库写入能力跟不上源库的写入速度。如果只有命令行日志你需要逐行翻输出才能发现问题有了面板一眼就能看到瓶颈在哪里。4. 实测1.2GB 数据库跨主机迁移对比4.1 测试环境说明为了不让测试结果显得像宣传我特意搭了一个相对接近生产的环境。两台物理服务器通过千兆交换机连接配置都是8核CPU、32GB内存、SSD磁盘。源库是PostgreSQL 16目标库是PostgreSQL 15这就模拟了跨大版本迁移的场景。同步的数据包含3个schema、48张表、总数据量约1.2GB最大的一张表有800万行带主键、普通索引、外键和几个大字段。Navicat测试用的是当前最新的18版本通过官方Modal窗口查到的版本数据库连接均使用同一台跳板机避免网络差异。开源工具测试时并发worker数设为6batch size设为2000。为了避免测试顺序影响热缓存每轮测试前都通过pg_dropcache清空两台实例的共享缓存保证从冷数据开始。带宽方面千兆局域网的理论速度是125MB/s1.2GB数据非常小所以网络不应该是瓶颈。这个设计是有意的因为我想对比的是数据库协议层面的效率差异而不是网络限制。4.2 Navicat vs 开源工具的耗时和资源对比先看结果表格操作Navicat 数据传输pg-fast-sync (开源工具)全量搬运 1.2GB / 800万行28分36秒首次失败重试2分54秒结构比对与索引重建包含在上述时间中26秒增量追放模拟50万并发写入不支持21秒源库CPU峰值稳定在12%峰值12%瞬间短暂到20%目标库CPU峰值稳定在35%峰值45%但由于时间短消耗的能量反而更低内存占用客户端800MB以上约180MB中断恢复需要从头开始支持从断点恢复需要说明Navicat在做数据传输时如果源库表有大量bytea字段它的读取策略会把每个字段转换成Base64字符串再在目标端解析回来这进一步拉低了速度。而COPY二进制格式没有这个转换过程直接按原始字节流搬运所以大字段场景下差距更夸张。我还做了一次反向测试从PostgreSQL 15同步到PostgreSQL 16结果类似耗时略微增加到3分08秒原因主要是旧版本的parallel能力稍弱。但总体仍然保持在“3分钟级别”。这个结果让我心里有底了跨大版本同步不再需要专门安排大块停机窗口。4.3 结果分析10 倍速度从哪里来答案其实不神秘主要来自三个技术叠加。第一COPY协议减少了SQL解析和网络往返这是数量级的性能优势。第二并行worker让多张表可以同时搬运把单线程IO变成多线程IO。第三全量导入时临时禁用外键和索引维护把数据库的CPU都花在“写堆表”这个核心动作上而不是各种约束检查。很多人担心“开这么多连接会不会打垮数据库”。从实测来看6个worker的连接数并不高源库能轻松应对。如果你的源库是核心生产库我建议把worker数控制在4以内同时给COPY连接设置statement_timeout0防止长任务被误杀。另外批量导入期间目标库的WAL会增长很快要确保磁盘空间足够否则同步进行到一半就可能报“disk full”。5. 踩坑记录与常见问题排查技巧5.1 常见问题速查表根据我和开源社区里用户的反馈我整理了一张速查表很多问题都是刚接入时最容易遇到的现象根因解决办法COPY得到“permission denied”非超级用户执行大COPY给用户加pg_read_all_datapg_write_all_data权限或确保是表owner无法创建逻辑复制槽实例未设置wal_levellogical修改postgresql.conf并重启增量同步一直为0max_replication_slots不足调大到10同时把max_wal_senders调高目标库外键冲突表之间的父子依赖顺序不对先导入所有主键表再导入外键表或临时禁用session_replication_role同步完数据量对不上对无主键大表拆分出错该表改为单任务串行同步面板上进度卡住不动源库表正在被长事务锁定查询pg_stat_activity并杀掉阻塞会话目标库索引重建失败索引名称冲突在目标库先把旧索引改名再重建内存暴涨COPY全量读入内存检查是否意外禁用了流式模式必须确保走io.Reader管道延迟逐秒增长目标库写入能力不足降低max_wal_size增加worker数或分批提交更大事务源库CPU飙高分片并行读取连接过多降低worker数和单表分片数5.2 两个容易忽略的“真坑”无主键表和序列不同步第一个真坑是“无主键大表”。很多监控表、日志表都没有主键按主键分片完全不可行。如果不做任何处理整表COPY也能完成但全量同步期间源库的变更无法和全量数据对齐。增量同步追到这些无主键表的UPDATE/DELETE时只能通过完整行匹配来定位目标行效率低偶尔还会重复更新。我的建议是对于无主键表在源库临时添加一个synthetic id列并不现实那就优先用“全量同步频率高”的方式把增量部分退化为周期性全量不要强行追增量。第二个坑是序列没同步。PostgreSQL的自增主键默认靠SEQUENCE生成。如果只同步表数据和表结构忘了同步序列的当前值那么应用往目标库插新记录时会出现主键冲突。网上很多数据迁移教程都没提这个但我踩过。正确做法是在数据同步完成后把源库每个序列的last_value同步到目标库。实现上可以在源库执行SELECT pg_get_serial_sequence(public.users,id); SELECT last_value FROM public.users_id_seq;然后在目标库执行setval。我的工具在元数据比对阶段会自动识别自增列并把序列上下限和cache值一并迁移过来。5.3 网络不佳时的调优建议如果你的源库和目标库之间是跨机房专线带宽只有100Mbps那小工具的性能优势会大打折扣。此时要注意几点第一不要用二进制COPY格式因为二进制格式没有压缩文本格式配合gzip压缩能减少传输数据量。实践中我可以把1.2GB的数据用gzip压到约450MB虽然会多消耗一些CPU但对网络压力明显减小。第二worker之间最好错开大表否则同时传输多张大表很容易把带宽占满。第三建议在目标库所在主机本地执行同步客户端而不是在第三台跳板机执行减少中间跳转。我曾经在跨地域网络环境下跑过一次同步带宽约80Mbps全量1.2GB耗时接近40分钟远不如内网环境。后来改成在目标库主机上发起的模式并且开了--compress1终于把时间压到25分钟。虽然还是慢但至少不会因为网络抖动而中断。现实中网络因素往往比数据库性能更影响同步体验所以做方案时一定要先确认两端带宽和延迟。结尾一点个人体会这个工具从最开始我为了加班写的小脚本开源到现在已经跑了两百多次生产同步任务最大的感受是PostgreSQL原生能力被低估了。很多人一上来就想到花钱买商业数据库同步软件或者继续忍受Navicat的慢速导出导入其实原生COPY加上逻辑复制已经解决了90%的场景只差一个顺手的外壳。如果你遇到的需求也是“把PostgreSQL某个库同步到另一个PostgreSQL或者做跨大版本迁移”我强烈建议你先试试这条路不要急着写复杂的应用层同步代码。使用过程中如果遇到问题可以翻一翻这篇文章里的速查表大多数坑我都替你先踩过了。后面我还会给这个工具加上监控告警和灰度发布功能如果你有好的建议欢迎一起聊聊。