瀚高数据库数据抽取实战：从pg_dump到逻辑复制

简介一款面向Oracle至瀚高HGDB数据库迁移与同步场景的专业抽取工具帮助DBA、运维人员及数据架构师在异构数据库更换或升级时保障数据一致性与完整性。压缩包共660个文件约55.29MB核心类型包括jar运行库、dll动态链接库、exe可执行程序及properties配置文件并内置完整JRE7时区数据工具针对Oracle 10g Release 3优化可较好兼容PL/SQL存储过程、触发器和索引等特性。已有771人学习/下载。通过该资源可获得开箱即用的数据库迁移组件理解ETL抽取、转换、装载全流程并借助工具内置的同步机制、数据加密与错误恢复能力降低人工迁移风险。适合正在推进国产数据库替代、双库并行或主备数据同步的企业项目参考也可作为学习Oracle与HGDB兼容性处理的实操素材。1. 瀚高数据库抽取工具到底在抽什么先搞清楚再动手接触过瀚高数据库的人应该都有同感这库的内核和 PostgreSQL 高度同源平时查数据、写 SQL 都顺手可一到「把数据抽出来」这个环节就容易卡壳。所谓瀚高数据库抽取工具指的是把数据从瀚高数据库导出、迁移到文件或其他数据库的一整套手段不是某一个固定软件。有人要整库备份有人要把几张业务表交给下游数据分析团队有人要把生产库同步到实时数仓不同诉求对应完全不同的工具组合。这篇文章把我自己常用的抽取方案按场景拆开讲覆盖导出命令、参数调优、跨库增量同步和排障目标是让新手照着命令能跑通让熟手绕开我在生产环境里踩过的坑。瀚高数据库社区版自带的 PostgreSQL 系工具是免费的企业版的高级组件才涉及授权费用这点在选型时要先有数。先把场景分清后面就顺了。2. 抽取工具选型先分清四种场景再决定用哪个命令2.1 四种典型抽取场景整库、单表、跨库、增量抽取工具的选择不是越强大越好而是看你的抽取目标。我习惯把需求拆成四类。整库抽取通常出现在数据库迁移、灾备恢复和版本升级场景要的是把全部 schema、表结构、数据、序列、函数、触发器一起带走。单表抽取最常出现在数据分析和报表需求里业务方说「把订单表给我一份」你只需要导出几张表。跨库抽取是数据中台和数仓项目的常规动作要把瀚高里的数据抽到 PostgreSQL、MySQL 或其他国产数据库。增量抽取则是持续同步的场景比如生产库和报表库之间的准实时同步需要的是捕获变化数据而不是反复全量。这四类场景对应工具箱里的不同工具。用错了不是不能跑而是你会发现在数据量大了之后某个方案根本撑不住。2.2 瀚高自带的 PostgreSQL 系抽取工具pg_dump 家族瀚高数据库的内核基于 PostgreSQL因此 PostgreSQL 自带的 pg_dump、pg_dumpall、pg_restore 在瀚高上完全可用。这套工具的最大优势是零成本社区版自带不用额外安装任何插件语法和参数与官方 PostgreSQL 完全一致。pg_dump 负责导出单个数据库可以选整库导出也可以指定表pg_dumpall 负责导出集群级别的全局对象包括用户、角色、表空间和所有数据库pg_restore 负责把 pg_dump 生成的归档文件恢复到目标库。三者配合使用基本能覆盖一整条迁移链路。需要注意的是工具版本与数据库版本的匹配问题。瀚高数据库有多条产品线V2.1、V3、V4 等版本的内核基准不同pg_dump 的版本最好与数据库版本保持一致至少不要相差太多。我用 V4 的库配 V3 的 pg_dump 导过数据结果是部分语法报错后来换成同版本工具才顺利通过。2.3 瀚高数据泵 gx_dump面向瀚高内核的专有导出工具除了 PostgreSQL 系的 pg_dump瀚高还提供了自己的数据泵工具命令行形态是 gx_dump 和 gx_dumpall。这套工具针对瀚高内核做了适配在导出瀚高特有对象、处理中文字符集和兼容旧版本数据格式上有一些优化。说直白一点如果目标库还是瀚高用自带的 gx_dump 是更省心的选择如果目标库是标准 PostgreSQL 或者其他数据库pg_dump 和逻辑复制反而更通用。我看到一个常见误用是拿 gx_dump 的导出文件去标准 PostgreSQL 里恢复遇到方言 SQL 就报错。工具选型的第一原则是导出文件和目标端的 SQL 方言要兼容。2.4 工具选择对照表抽取场景推荐工具输出格式注意事项整库备份与迁移pg_dumpall / gx_dumpallSQL 文本全局对象要另外处理单库全量导出pg_dump / gx_dumpSQL 或归档格式归档格式恢复更灵活单表或多表导出pg_dump -t / COPYSQL 或 CSVCOPY 更利于下游加工跨库迁移到 PostgreSQLpg_dump pg_restore归档格式版本一致才少踩坑增量同步逻辑复制 / 第三方同步工具实时数据流需要开启 wal_levellogical这张表是我做选型时的一张速查卡。新增场景拿不准时优先查表然后在小数据量环境里先跑通再上生产。3. 用 pg_dump 把瀚高数据抽到本地文件最稳的一种做法3.1 抽数前确认的三件事权限、字符集、磁盘空间在敲任何导出命令之前先花几分钟确认环境能省掉后面一整天的排障时间。权限方面执行导出的用户需要目标库的 CONNECT 权限和所有被导出对象的访问权限。最省事的做法是用超级用户导出但生产环境通常不给你超级用户口令那就让 DBA 创建一个专用的导出账号并授予相应 schema 的 USAGE 和 SELECT 权限。字符集方面先查源库的编码再决定导出文件的客户端编码。瀚高数据库安装时默认可能是 UTF-8但如果兼容过旧业务也常见 GBK 编码。磁盘空间方面要知道导出文件的大小预期预留两倍空间比较稳妥因为归档格式加压缩之后体积变化大不确定时先做一次小范围导出估算。这三件事确认完导出本身的成功率会高很多。跳过这些直接跑命令的人十个里有七个中途回来查权限报错。3.2 全库导出的最小命令与关键参数首先登录任意一台能连到瀚高数据库的机器确认客户端工具可用。以下是全库导出的最小命令pg_dump -h 192.168.1.10 -p 5866 -U hg_dump_user -d mydb \ -F c -Z 5 -f /backup/mydb_$(date %Y%m%d).dump命令执行后会提示输入密码输入对应账号的密码等待完成即可。参数含义如下-h指定数据库主机瀚高默认端口通常是 5866和 PostgreSQL 的 5432 不一样这一点经常有人搞混-U指定导出账号-d指定要导出的数据库名-F c指定输出格式为自定义归档格式这种格式支持压缩和选择性恢复-Z 5是压缩级别5 是速度和压缩率的中间档-f指定输出文件路径。文件名里的$(date %Y%m%d)是 Linux 下的当前日期也可以按你的习惯写固定文件名。用自定义归档格式而不是纯 SQL 文本是我做整库导出时的固定习惯。归档格式体积小、恢复时可以选表恢复比纯 SQL 灵活得多。如果下游需要直接执行 SQL 脚本再改用-F p输出纯文本。3.3 只抽业务表用 -t 参数和 COPY 命令各做一遍业务方只要几张表时跑整库导出既慢又占空间。指定单表导出用-t参数pg_dump -h 192.168.1.10 -p 5866 -U hg_dump_user -d mydb \ -t public.orders -t public.order_items \ -F c -f /backup/orders_only.dump注意每个-t只能跟一个表名要导多张表就写多个-t。表名要带上 schema 前缀不带的时候默认找 public。这个命令会把表的定义和数据一起导出表之间的外键约束也会保留。如果下游团队要的是 CSV 文件而不是 SQL用\copy更直接\copy (SELECT id, order_no, amount, create_time FROM orders WHERE create_time 2024-01-01) TO /data/orders_2024.csv WITH CSV HEADER\copy是 psql 客户端命令在 psql 交互环境里执行导出的是服务端查询结果文件落在执行客户端的那台机器上。它和 SQL 里的COPY命令的区别在于COPY在服务端执行文件写在数据库服务器本地客户端机器上拿不到\copy是先查数据再在客户端本地落盘。连接远程数据库时用\copy是避免文件找不着的稳妥选择。3.4 恢复进新库的三个步骤导出只是前半程把数据恢复进目标库才是完整闭环。恢复分三步。第一步在目标库服务器上创建空数据库createdb -h 192.168.2.20 -p 5866 -U hg_admin -O hg_app_owner newdb第二步用 pg_restore 把归档格式的导出文件恢复进去pg_restore -h 192.168.2.20 -p 5866 -U hg_app_owner -d newdb \ --no-owner --no-privileges /backup/mydb_20240101.dump第三步检查关键对象是否存在SELECT schemaname, tablename FROM pg_tables WHERE schemaname public LIMIT 20;恢复命令中的--no-owner和--no-privileges建议加上。生产环境的导出文件里带着原库的对象属主和权限目标库未必有同名用户不加这两个参数大概率报角色不存在。3.5 瀚高与社区版 PostgreSQL 版本不一致时的处理做跨版本恢复时最常见的报错是「ERROR: syntax error at or near ...」原因是源库导出的 SQL 里用了目标库不认识的语法或数据类型。处理思路有三个把源库的导出工具换成与目标库相近的版本导出时加上--no-tablespaces去掉表空间语句恢复时按错误逐条跳过有问题的对象。处理步骤是先在测试环境恢复一次记录报错位置再回导出端调整参数。跨版本迁移不是一条命令能包办的我一般预留一天时间专门处理这类问题。4. 跨库抽取与增量同步让数据自己流向目标端4.1 从瀚高抽到标准 PostgreSQL用逻辑复制还是物理复制瀚高和 PostgreSQL 同源数据迁移到标准 PostgreSQL 比迁移到其他数据库容易得多。两种常见做法。物理复制层面瀚高支持 PostgreSQL 的流复制协议可以做基于 WAL 的物理主备同步。但物理复制要求主备库的内核版本严格一致瀚高官方版本和社区 PostgreSQL 对不上时基本走不通所以物理复制更适合瀚高到瀚高的场景。逻辑复制层面瀚高从较早版本开始支持wal_levellogical可以用 PostgreSQL 的逻辑复制机制把数据同步到标准 PostgreSQL。这个方案不要求两端小版本完全一致只要发布端和订阅端的主要版本差距在可接受范围内。做跨厂商迁移时逻辑复制是我首选的同步方案。4.2 用 COPY 做文件级抽取大表导出比 pg_dump 快当需要导出一张大表给数据处理团队比如几亿行的流水表再用 pg_dump 导 SQL 就太笨重了这时候用 COPY 直接出文件更合适。在 psql 里执行COPY (SELECT * FROM trade_flow WHERE biz_date 2024-01-01) TO /data/trade_flow.csv WITH CSV;这是在服务端执行文件写在数据库服务器本地。如果只想在客户端拿到文件用\copy。注意 COPY 导出的是纯数据不含表结构建表语句要单独提供。这里有一个容易被忽略的点CSV 格式对大字段和特殊字符处理不彻底。字段里如果含换行、逗号和引号用WITH CSV能处理引号包裹但导入端必须同样按 CSV 解析。下游如果用 Excel 打开某些转义规则会走样。要求严格的下游我会建议导出成管道分隔或用FORMAT csv后配合FORCE_QUOTE控制。4.3 增量抽取用逻辑复制实现准实时同步定时任务全量抽数在海量数据场景下越来越吃紧增量同步是更省资源的方案。瀚高上的逻辑复制配置方式和 PostgreSQL 一致步骤如下。先在源库修改配置并重启# postgresql.conf 中确认以下参数 wal_level logical max_replication_slots 10 max_wal_senders 10然后在源库创建逻辑复制槽SELECT * FROM pg_create_logical_replication_slot(slot_trade, pgoutput);接着在目标 PostgreSQL 库上创建订阅CREATE SUBSCRIPTION sub_trade CONNECTION host192.168.1.10 port5866 userrepl_user passwordxxx dbnamemydb PUBLICATION pub_trade;执行前需要在源库先创建发布CREATE PUBLICATION pub_trade FOR TABLE trade_flow;这套配置完成后源库 trade_flow 表的增删改会自动流到目标库。架构上是发布订阅模式源库是发布端目标库是订阅端。4.4 定时全量抽取与断点续抽的取舍不是所有场景都需要逻辑复制。数据量不大、对实时性要求不高的报表库定时全量抽取反而更好维护。用 cron 做定时任务30 2 * * * pg_dump -h 192.168.1.10 -p 5866 -U hg_dump_user -d mydb -F c -Z 5 -f /backup/mydb_$(date \%Y\%m\%d).dump /var/log/hg_export.log 21逻辑复制的实时性好但存在两端 schema 必须兼容、DDL 操作会中断同步的约束定时全量抽取实现简单但抽取窗口内数据库压力大数据量大了之后耗时会越来越长。小数据量用定时全量大数据量或实时场景用逻辑复制这是我定的分界线。5. 瀚高抽取工具的避坑指南导出失败的 5 个真实问题5.1 导出文件打开全是乱码GBK 源库与 UTF-8 客户端不匹配现象是导出命令执行成功但生成的 SQL 或 CSV 文件在编辑器里打开中文全是乱码部分字符直接变成问号。原因是源库的编码是 GBK而导出客户端的 client_encoding 默认是 UTF-8两边没有对齐数据在传输过程中被错误转码。解决方法是导出前先查编码再显式指定客户端编码执行SHOW server_encoding;确认服务端编码然后在导出命令前设置export PGCLIENTENCODINGGBK或在 psql 里执行\encoding GBK。导 CSV 时同样要在 COPY 之前设置客户端编码。这个坑在兼容过旧系统的瀚高库上非常常见。5.2 导出过程提示权限不足但账号明明有 DBA 角色现象是用一个有 DBA 角色的账号执行 pg_dump中途报错permission denied for table xxx然后整个导出失败。原因是 DBA 角色并不等于拥有所有对象的访问权。瀚高里的普通表、视图、物化视图的权限是独立授予的部分业务表做了行级安全策略时即使角色有 SELECT 权限也会被行级安全过滤。解决方法是不要依赖角色权限明确为用户授予目标 schema 的权限再到具体表上授权GRANT USAGE ON SCHEMA public TO hg_dump_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO hg_dump_user;如果还是报权限不足检查该表是否开启了行级安全策略必要时由表属主临时关闭策略后导出。生产环境遇到这个坑多半是之前迁移过的表属主混乱导致。5.3 大表导出到一半连接断开没有断点续传现象是导出千万级以上的大表时执行到一半 SSH 终端卡住随后命令中断已导出的数据全部作废。原因是导出的会话连接超时或网络闪断pg_dump 没有断点续传机制中断后必须从头再来。如果是用交互式终端执行终端关闭也会带走进程。解决方案是用 nohup 把导出进程挂到后台加上压缩参数减小传输体积nohup pg_dump -h 192.168.1.10 -p 5866 -U hg_dump_user -d mydb \ -F c -Z 9 -f /backup/mydb_20240101.dump /var/log/hg_dump.log 21 加-Z 9把压缩级别提到最高虽然耗 CPU但传输的数据量明显下降中断概率也随之降低。对超大表我更建议拆成按时间范围分段导出每段一个文件哪段失败就只重抽哪段。5.4 从瀚高导出的 SQL 在旧版瀚高上恢复时报语法错误现象是同一套导出文件在新版本库上恢复一切正常换到旧版本库就报syntax error at or near ...通常是分区表或生成列相关语法。原因是导出工具版本比目标库版本高导出的 SQL 里带了目标库不认识的新语法。解决方法是导出时加兼容参数pg_dump --no-comments --no-publications --no-subscriptions \ -h 192.168.1.10 -p 5866 -U hg_dump_user -d mydb \ -F c -f /backup/mydb_legacy.dump同时用低版本的客户端工具执行导出。稳妥的做法是服务器的 pg_dump 版本号和目标库版本一致或更低不要反过来。5.5 恢复后序列断档新增数据主键冲突现象是数据恢复成功后业务系统插入新记录时报主键重复检查发现自增主键从 1 开始重新计算和已恢复数据的最大值撞上。原因是恢复表数据时没有同步序列当前值。pg_dump 导出时会带序列定义但序列的当前值恢复到默认值没有对齐表的实际最大值。解决方法是恢复完成后手工校准序列把序列值跳到表的最大 ID 之后SELECT setval(orders_id_seq, (SELECT MAX(id) FROM orders));批量处理多个表时写一段 PL/pgSQL 自动扫描所有序列并校准更省事但手工处理一两张关键表也用不了几分钟。我在每次恢复操作后都会例行执行这一步避免上线前才发现主键冲突。6. 抽数之后的验证与进阶数据不只要抽得出来还要对得上6.1 行数校验最简单的对账方式导出完成不等于抽取成功。我每次导完数据都会第一时间核对行数在源库和目标库分别执行SELECT COUNT(*) FROM orders;两侧数字一致是最低标准。更进一步对大表增加最大值、最小值、去重行数的核对这些统计值也能快速暴露数据缺失或重复。6.2 对字段级数据做抽样比对行数一致仍有可能是整段数据错位。对关键表做一个字段级抽样用 md5 函数比对两端的聚合校验值SELECT md5(string_agg(id::text || order_no, , ORDER BY id)) FROM orders;源库和目标库各跑一遍哈希值一致说明这批数据完全一致。抽样比对比单纯数行数可靠得多。6.3 增量抽取的验证方式逻辑复制的场景验证的是数据延迟和目标端数据连续性。在目标库执行SELECT MAX(biz_date) FROM trade_flow;拿这个值和源库对比如果时间差在预期范围内同步链路基本健康。另一条经验是订阅端偶尔出现复制中断事后重连会自动追平事务但 DDL 操作需要手动处理。因此生产环境里对同步表做结构变更前必须先暂停订阅。6.4 我给自己定的一条铁律做了这么多年数据库抽取我总结出一条习惯任何导出操作不管多简单都要把源库、目标库、导出工具三方的版本记下来连同导出命令一起写进操作文档里。等到半年后同一个库要再做一次抽取翻出当时的记录参数和环境一目了然能省掉大量重新摸索的时间。数据抽取这种事翻车一次的成本足够做十次规范操作所以花在验证和记录上的时间从来不亏。希望帮到你。本文还有配套的精品资源点击获取