2026/9/13 14:48:22
FEATURED · 精选文章 
最近刚完成一个从PostgreSQL到Oracle的数据迁移项目涉及一套核心业务库数据量在TB级别前后花了一个多月。这种异构数据库迁移在传统行业里特别常见系统重构、机房整合、数据库统一替换都会遇到。这篇文章我把从迁移前的评估、结构迁移、数据迁移、序列处理到验证切换的完整过程复盘一遍偏实践向适合准备接这类活儿的DBA、运维和开发同学参考。先说结论异构数据库迁移工作量的重心不在“搬数据”这一步而在结构转换和业务SQL兼容性评估。PostgreSQL和Oracle虽然都是关系型数据库但数据类型、内置函数、SQL方言、事务细节都有不少差异每一步都可能冒出小坑。文章里我会把关键差异、实操步骤、踩过的坑都列出来尽量让后来的人少走弯路。1. 迁移前先把两边的家底摸清楚1.1 评估哪些对象要迁移哪些可以重建接到迁移需求第一件事不是找工具而是盘点源端PostgreSQL里到底有哪些对象。我通常会把对象分成几类表、索引、约束、序列、视图、物化视图存储过程、函数、包、触发器、同义词、DBLink自定义类型、扩展模块PostGIS、pgcrypto等建一个清单表把每个对象的名称、类型、数量、大小、依赖关系都登记好。对于表还需要按用途分类核心业务表、配置表、日志表、临时表。分类的目的是决定迁移顺序——配置表和小表先迁大表按主键分段并发处理日志表甚至可以跟业务方确认是否需要迁移。数据结构之外还要评估数据量。用SELECT count(*)去数大表会非常慢建议直接查pg_class.reltuples拿估算值再抽样验证。配合pg_total_relation_size看大小能快速摸清哪些是“大块头”。还有一个很容易被忽略的点应用层对PostgreSQL特性的依赖。比如JSONB、数组类型、窗口函数、正则表达式、DISTINCT ON、WITH RECURSIVE这类写法在Oracle里都有对应的实现但语法和语义不完全一样。这个需要拉上开发团队把SQL清单拉出来一条条过一遍。如果业务代码里对PG特性依赖很深迁移成本会直线上升这种项目必须提前规划代码改造。1.2 迁移方案选型三条路线怎么选工具选型决定了后面一个月的工作量。我评估过三条路线方案优势劣势适用场景ORA2PG免费开源能转结构数据支持类型映射定制生成的结构DDL仍需人工校对大表迁移性能一般中小型库、结构迁移为主DataX 手工转换结构迁移性能强支持并发和断点续传对大数据量友好只搬数据DDL还是得自己做大数据量、批量同步商业ETLKettle/Informatica图形化界面功能全面部署重、License成本高性能调优有门槛企业级复杂场景我这次选的是组合方案结构迁移用ORA2PG生成基础DDL再人工比对调整数据迁移用DataX做全量搬运序列、触发器等在迁移完成后用脚本单独处理。为什么这么组合因为DataX的并发能力在大数据量场景下确实稳而ORA2PG能先把结构快速“翻译”一遍省去从零写DDL的时间。2. 结构迁移类型映射和DDL转换是重头戏2.1 PostgreSQL与Oracle常用数据类型映射结构迁移第一步是类型映射。PostgreSQL和Oracle的数据类型名长得像但细节差很多。直接给一张我整理过的映射表PostgreSQL类型Oracle类型说明varchar(n)varchar2(n)长度单位两边都是字符但Oracle按字节还是按字符取决于NLS设置要注意textclob没有长度限制但Oracle的clob操作有限制超过4000字节不能直接做隐式转换char(n)char(n)语义接近注意PG的char会自动补空格booleannumber(1)Oracle表字段没有boolean类型应用层需要用0/1替代smallint/int/integernumber(5)/number(10)PG的int就是4字节Oracle的number可以匹配相应精度bigintnumber(19)注意number默认没有精度建议显式指定serial/bigserialnumber sequencePG的自增列Oracle需要序列加触发器或使用Identity列numeric(p,s)number(p,s)精度定义类似但要注意舍入行为的差异timestamptimestampPG的timestamp不带时区Oracle也是语义基本对应timestamptztimestamp with time zone / with local time zone时区语义有差别建议迁移前确认业务使用的时区datedate两边都有date但PG的date只到天Oracle的date包含时分秒这个差异坑过很多人byteablob二进制数据DataX里用字节流处理uuidvarchar2(32) / raw(16)看业务需要如果只是存储展示用varchar2(32)存字符串最简单json/jsonbclobOracle 21c之前没有原生JSON字段一般用clob存储应用层自行解析数组类型无对应类型需要拆表或序列化存储属于应用改造范围枚举类型varchar2 check约束PG的enum类型Oracle没有改为check约束最省事这里重点说一下两个高频坑第一个是date类型。PostgreSQL的timestamp和date分得很清楚Oracle的date实际上同时存了日期和时间。如果你在PG里用date存了生日迁到Oracle后还是date查询时发现多了时间部分应用层如果依赖trunc()处理还好不处理就会出现日期比较对不上的问题。第二个是boolean。Oracle的表字段没有boolean类型这是PG迁移到Oracle最普遍的问题。我一般改成number(1)然后应用层做一层映射SQL里where is_active true要改成where is_active 1。这块必须提前跟开发确认否则上线后应用报错找半天。2.2 DDL转换的几个细节坑类型映射只是第一步DDL语句本身的转换才是真正花时间的地方。按我这次的经历这几个地方最容易出问题大小写问题。PostgreSQL默认把未加引号的标识符统一转成小写存储Oracle默认转成大写。如果源库建表时规矩都是小写迁移到Oracle后用大写没问题。但如果PG里用了双引号创建混合大小写的表名或字段名迁到Oracle后必须加双引号才能访问否则就报ORA-00942: table or view does not exist。这个在数据字典里排查一遍提前把所有双引号对象清出来能省去后面大量返工。自增列的处理。PG的serial本质是integer sequence default nextval()。迁到Oracle时最简单的做法是去掉default改用Oracle的Identity列12c及以上或者序列加触发器。如果业务逻辑里允许显式插入主键值用Identity会遇到冲突这种情况用序列加触发器更灵活。部分索引Partial Index。PostgreSQL支持where条件的部分索引Oracle原生不支持。如果源库有这种索引要么去掉要么改造成函数索引或普通索引并评估对查询性能的影响。表达式索引和函数索引。PG里很多表达式索引比如lower(email)Oracle支持函数索引但写法略有差异需要把PG的表达式翻译成Oracle的函数调用。分区表。PG的分区语法和Oracle差异很大。PG的分区是基于PARTITION BY RANGE声明式分区Oracle的分区功能强大得多。迁移时先在Oracle里建好分区表再把PG的分区数据按分区边界导入。要注意分区键的选择和分区边界值的一致性否则数据会落到错误的分区。表的COMMENT。PG的注释在COMMENT ON里Oracle也有同名语法这个可以直接转换。但注意字段注释最好在数据迁移前加好很多下游元数据管理系统依赖这些注释生成数据字典漏了会很麻烦。2.3 存储过程、函数与视图的翻译存储过程和函数的转换是整个结构迁移中最费脑子的部分。PostgreSQL用的是PL/pgSQLOracle用的是PL/SQL语法相似但细节差异不少变量声明PG用DECLARE块PL/SQL在IS/AS后直接声明写法上要调整。参数模式PG的IN/OUT/INOUT和Oracle的类似但默认值语法有区别。异常处理两边都有EXCEPTION WHEN ...但异常名称、SQLCODE/SQLERRM的行为不完全一致。游标PL/pgSQL里FOR rec IN SELECT ... LOOP和Oracle的FOR rec IN (SELECT ...) LOOP很像但显式游标属性%FOUND、%ROWCOUNT的处理方式有差异。动态SQLPG的EXECUTE ... USING对应Oracle的EXECUTE IMMEDIATE ... USING基本能对应上但绑定变量的占位符写法要检查。一个比较麻烦的点是PG函数可以返回SETOF、TABLE、JSON等复杂类型Oracle对应的是RETURN SYS_REFCURSOR或者管道函数转换工作量大。如果这种函数多建议单独拉个清单逐个跟开发确认改造方案。视图方面PG里常见的::类型转换在Oracle里要改成CAST。PG的ILIKE要改成LOWER()函数处理。还有DISTINCT ONPG特别常用Oracle没有这个语法必须改写为ROW_NUMBER() OVER (PARTITION BY ...)。这类SQL方言差异光靠工具自动转换是搞不定的必须人工审查。3. 数据迁移全量搬迁的实操过程3.1 数据量摸底与分批思路结构DDL全部在Oracle里建好之后进入数据迁移阶段。先做数据量摸底按pg_total_relation_size排序找出超过10GB的大表。对每张大表查询主键或唯一键的最小值、最大值确定批次切分点。小表和配置表直接一次搬完。迁移顺序上我的习惯是先迁配置表、维度表这类表数据量小、依赖少再迁业务流水表、日志表最后迁超大表和有外键关联的核心表。外键约束建议在数据迁移完成后再启用否则导入顺序一乱就报外键冲突严重影响效率。3.2 用DataX全量迁移的配置实践DataX是阿里开源的数据同步工具支持PostgreSQL Reader和Oracle Writer是我这次数据搬迁的主力。配置一个任务核心是job.json{ job: { setting: { speed: { channel: 8, byte: 1048576 } }, content: [ { reader: { name: postgresqlreader, parameter: { username: pg_user, password: pg_password, column: [id, name, created_at], splitPk: id, connection: [ { table: [source_table], jdbcUrl: [jdbc:postgresql://192.168.1.10:5432/source_db] } ] } }, writer: { name: oraclewriter, parameter: { username: oracle_user, password: oracle_password, column: [id, name, created_at], preSql: [DELETE FROM target_table WHERE 10], connection: [ { table: [target_table], jdbcUrl: [jdbc:oracle:thin://192.168.1.20:1521/ORCLPDB1] } ] } } } ] } }几个关键参数说明一下channel并发数我一般从4开始逐步调到8、16观察源库和目标库的负载。并发太高会把源库的IO打满影响线上业务这个要特别注意。splitPk大表必须指定主键或唯一索引字段作为切分键DataX会按这个字段的区间切分任务。不设置的话多个channel读同一个表容易重复读或者负载不均匀。preSql用来在写入前做清理DELETE FROM target_table WHERE 10是一个空操作不会删数据只是清空表类似的效果需要另外处理。实际如果需要清空目标表用TRUNCATE语句但这个操作会导致整个任务开始前目标表被清空要确认安全后再配。大表迁移时我给每个表单独写一个job配置便于并行执行和单独重跑。比如10张核心大表每个表8个channel分3批跑整体吞吐量比单表跑要稳定得多。3.3 大字段与特殊数据的处理大字段的迁移是容易踩坑的点尤其是text和bytea。text类型在Oracle里对应clob。DataX读写CLOB时走的是流式读取默认会一次性加载到内存。遇到超大字段JVM内存会被撑爆报OutOfMemoryError。调大DataX的JVM堆内存是第一步但更稳妥的做法是对大表单独配置任务channel不要太高必要时把byte参数调小控制单次读取的数据量。bytea对应Oracle的blob处理逻辑类似。DataX里把PG的bytea读出来写进Oracle的blob中间需要转成字节流默认行为没问题但要注意源端的bytea_output格式如果输出的是hex格式DataX的JDBC驱动能直接处理如果输出的是escape格式可能会遇到解析问题建议在PG连接参数里显式指定bytea_outputhex。时间类型方面timestamptz迁移到timestamp with time zone时要注意时区差异。如果应用层统一用北京时区PG端的会话时区和Oracle端的会话时区都要显式设置为同一时区否则数据看起来会差8小时。数值精度方面PG的numeric和Oracle的number都是十进制变长类型精度基本对得上。但要注意PG的numeric如果没指定精度可以存任意精度的数值Oracle的number没指定精度也是变长两者行为相近一般不会丢精度。反观PG的double precision迁移到Oracle的binary_double如果只建了number没有指定精度可能出现小数位被截断的问题这类字段建议提前跟业务确认精度要求。字符集方面源端PG如果是UTF8目标Oracle如果用了ZHS16GBK中文字符在迁移时可能出现乱码或者长度超限。最省心的办法是保持Oracle字符集为AL32UTF8如果目标库已经定死了GBK那在DataX的Reader和Writer里都要显式指定encoding并做好抽样验证。3.4 数据一致性校验数据搬完后验证环节不能省。我有三个层次的校验第一层行数校验。对每张表统计两边的count(*)。注意大表的count可能很慢而且PG和Oracle在并发写入的情况下统计值可能不一致。我的做法是迁移期间暂停源库的写操作或者选择在业务低峰期保证静态数据再对比count才有意义。第二层抽样字段级校验。用主键做关联取若干区间对比每条记录的字段值。最简单的方式是拼一个字段串计算MD5或哈希值做比对-- PostgreSQL端 SELECT md5(string_agg(column_name::text, | ORDER BY id)) FROM table_name WHERE id BETWEEN ? AND ?; -- Oracle端 SELECT LOWER(SUBSTR(STANDARD_HASH(listagg(column_name, |) WITHIN GROUP (ORDER BY id), MD5), 1, 32)) FROM table_name WHERE id BETWEEN ? AND ?;两边算出同样的MD5说明这批数据一致。不一致的区间定位到具体行再人工确认。第三层业务校验。这是最关键的。找业务方抽出几条核心查询在Oracle上跑一遍对比结果集跟PG上的结果是否一致。这一步能发现那些“数据搬对了但SQL语义变了”的问题比如空字符串被当成NULL、日期格式隐式转换出错、布尔值0/1映射不对等等。4. 序列与自增主键的衔接处理数据搬完后序列的处理常常被忽略但一旦漏了应用一插入新数据就会报主键冲突。PG的序列是独立对象Oracle的序列也是独立对象但两者的元数据不通用必须手工重建并设置初始值。做法如下-- 在Oracle中创建序列 CREATE SEQUENCE seq_target_table_id START WITH 1000001 INCREMENT BY 1 NOCACHE NOCYCLE;START WITH的值怎么确定在PG端查该表主键的最大值加一个增量SELECT COALESCE(MAX(id), 0) 1 FROM source_table;要注意的是如果应用代码里曾经显式插入过主键序列的当前值可能落后于表里的最大主键。比如业务手工插入过id20000的记录但序列只跑到了18000此时如果按MAX(id)1设置也可能小于实际业务的预期值。稳妥起见取MAX(sequence_last_value, table_max_id1)也就是序列本身的值和表最大主键值取较大者。如果Oracle目标表用的是12c及以上版本的Identity列可以通过以下语句修改起始值ALTER TABLE target_table MODIFY (id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMITS VALUE));这里的LIMITS VALUE是Oracle的语法表示取当前已有数据的最大值加1比较省事。迁移后还要做一个联动检查所有引用该序列的触发器、存储过程、应用代码确认它们调用的是新建的序列。两边序列名称如果不一样应用层连接串和SQL里的nextval调用也要一并改掉。5. 典型报错与排查实录这一部分把这次迁移过程中实际遇到、以及周围同事经常踩的坑整理成问题速查表按“现象—原因—处理方式”的格式记录。报错信息可能原因处理方式ORA-00942: table or view does not exist表名大小写不一致、schema权限不对、同义词缺失检查Oracle里对象实际名称确认是否需要加双引号授予对应schema的访问权限创建同义词ORA-01400: cannot insert NULL into (字段名)PG空字符串在Oracle里变成NULL目标表该字段有NOT NULL约束数据迁移前把PG的空字符串转成非NULL占位符或调整目标表约束迁移后做一轮空值检测ORA-12899: value too large for column (字段名)字符集从UTF8变GBK导致字节变长或源字段长度大于目标字段核对目标表VARCHAR2长度字符集不匹配时用AL32UTF8重建或调整字段长度ORA-00001: unique constraint violated源库存在重复数据或大小写不一致导致唯一键冲突迁移前在PG端做一次唯一性检查清理重复数据后再迁移ORA-01843: not a valid month日期字符串格式与目标会话NLS参数不匹配设置NLS_DATE_FORMAT或在SQL里显式TO_DATE(yyyy-mm-dd hh24:mi:ss)ORA-06502: PL/SQL numeric or value error存储过程或函数里类型转换溢出检查PL/SQL中变量长度和精度和源PL/pgSQL对齐之后再编译ORA-28547: connection to server failed, probable Oracle Net admin error连接远程Oracle服务时网络配置不对检查tnsnames.ora、listener.ora和防火墙端口监听服务无法启动listener.ora配置错误、主机名解析失败、端口被占用检查lsnrctl status日志确认hosts解析调整端口DataX迁移时报OutOfMemoryError大字段CLOB/BLOB全部加载到内存调大JVM堆内存降低channel数拆小批次处理迁移过程中目标库redo日志暴涨批量写入产生大量重做日志增加redo log大小和组数或控制单批次数据量分多次commit5.1 连接层面的坑连接Oracle的问题很多人一开始都会遇到。最典型的是ORA-28547这个报错经常出现在应用服务器连数据库的时候原因是Oracle Net配置不对。用tnsping命令先通一遍确认能解析到目标实例再看listener.ora里监听的服务名是否匹配。监听服务无法启动多半是主机名解析的问题。我遇到过一台服务器改了hostname之后Oracle监听起不来检查/etc/hosts里没有映射新主机名导致的。加上映射重启监听就好了。还有防火墙1521端口被禁的情况很普遍低版本Oracle的监听默认端口不一定都是1521要看listener.ora里实际配置。5.2 数据加载报错的处理思路数据加载的报错大部分都集中在类型和字符集这两类。ORA-12899 value too large是高频报错。比如说PG的varchar(100)按字符算存100个中文字符没问题迁到Oracle后如果目标库字符集是ZHS16GBKvarchar2(100)默认按字节算100个中文会占200字节直接超限。解决办法是把目标字段长度按字符集扩大比如varchar2(200)或者确保目标库用AL32UTF8注意AL32UTF8中文字符要3字节还要留更大余量。ORA-01400 NOT NULL冲突也经常遇到。前面提过Oracle里空字符串就是NULLPG里不是NULL。如果源表某个字段既有NOT NULL约束又允许空字符串数据迁移到Oracle后就会报冲突。这种字段迁移前要跟业务确认一是把空字符串转成其他占位符比如空格二是去掉目标表的NOT NULL约束三是应用层改为存NULL。5.3 迁移性能问题大数据量迁移时性能瓶颈往往不在工具而在目标库。第一个坑是索引拖慢插入。如果目标表上建了大量索引每次插入都要维护索引数据量大时速度急剧下降。我习惯的做法是在数据迁移前把目标表上的索引尤其是非唯一索引先drop掉等数据全部搬完再重建索引。唯一约束和主键保留因为要靠它们保证数据不重复。第二个坑是Oracle的redo日志。批量insert会产生大量redo如果redo log太小会频繁触发checkpoint严重影响性能。迁移前把redo log调整到合适大小起码保证峰值写入时不至于频繁切换。第三个坑是PG端的垃圾数据。PostgreSQL的MVCC机制会产生dead tuple如果在迁移高峰期表上堆积了大量dead tuple全表扫描的效率非常差。迁移前做一次VACUUM能有效提升读性能。6. 切换前的验证清单6.1 应用兼容性回归数据迁移完成不代表项目结束还要保证应用能跑起来。这个阶段需要开发配合做一轮SQL兼容性回归把应用代码里的SQL扫描一遍重点排查以下几类分页查询PG的LIMIT/OFFSET改成Oracle的ROWNUM或者FETCH FIRST ... ROWS ONLY12c旧版本Oracle只能改写成子查询ROWNUM ?的方式。字符串连接PG的||在Oracle里也支持但如果PG里用了concat_ws、string_agg这类函数要替换成Oracle的LISTAGG或XMLAGG。布尔表达式WHERE is_active这种写法在Oracle里不成立要改成WHERE is_active 1。类型转换::int改成CAST(... AS NUMBER)::text改成TO_CHAR()。空值处理PG里COALESCE、NULLIF在Oracle都有但NVL、NVL2、DECODE是Oracle特有的如果SQL要在两边跑统一用COALESCE最稳。日期函数PG的NOW()在Oracle里对应SYSDATE或CURRENT_TIMESTAMPPG的EXTRACT(EPOCH FROM ...)是取Unix时间戳Oracle用(sysdate - date 1970-01-01) * 86400计算。驱动也要换应用连接PG用的是postgresql-42.x.x.jar连Oracle要用ojdbc8.jar或ojdbc11.jar。连接串从jdbc:postgresql://host:5432/db改成jdbc:oracle:thin://host:1521/service_name。6.2 统计信息、权限与性能基线数据都进去了还要收集统计信息否则Oracle的CBO优化器不知道表的数据分布执行计划可能非常差BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname APP_SCHEMA, cascade TRUE, degree 8); END;然后跑几条核心查询对比执行计划。我遇到过一种情况数据量完全一样但Oracle里走了全表扫描原来PG走的是索引扫描原因是统计信息没收集。收集完之后执行计划恢复正常。权限方面确认目标schema下面所有表的SELECT/INSERT/UPDATE/DELETE权限都授权给了对应业务账号。如果有存储过程还要单独GRANT EXECUTE。同义词也不要忘如果应用用短名访问对象需要在目标库创建对应的私有或公共同义词。6.3 回退方案与灰度切换切换不可能一次成功回退方案必须有。我的建议是切换日之前把PG源库保留至少一个月业务验证稳定后再下线。切换窗口内先切只读流量观察一段时间再切写流量。如果Oracle端出了问题把读写流量切回PG应用配置改回PG连接串即可。前提是PG端写入的数据不能丢所以在切换期间要做好业务写入的规避或同步。增量数据同步是最难的一环。如果业务允许在切换窗口内停写全量迁移后直接切流量即可。如果业务不能停写就需要在切换窗口前建立增量同步机制。选型方面商业OGG、Debezium、自研同步脚本都可以但要提前测试别等到切换日才发现增量链路不通。结尾这次迁移项目做下来我最大的体会是异构数据库迁移真正难的不是“数据搬运”而是“语义对齐”。PostgreSQL和Oracle表面看起来差不多实际上在类型系统、SQL方言、存储引擎、会话管理上差异非常多。数据搬过去很快但搬完之后要让业务跑得一样顺需要花大量精力在结构转换、SQL改写和验证上。最后分享一个小技巧不管用哪个工具搬数据先挑一张小表走通全流程——从结构转换、数据迁移、行数校验到应用查询验证确认整条链路没问题再铺开干。别一上来就并发跑几十张大表等发现类型映射错了返工成本会让你怀疑人生。
PostgreSQL到Oracle迁移实战：从类型映射到数据校验的完整复盘
最近刚完成一个从PostgreSQL到Oracle的数据迁移项目涉及一套核心业务库数据量在TB级别前后花了一个多月。这种异构数据库迁移在传统行业里特别常见系统重构、机房整合、数据库统一替换都会遇到。这篇文章我把从迁移前的评估、结构迁移、数据迁移、序列处理到验证切换的完整过程复盘一遍偏实践向适合准备接这类活儿的DBA、运维和开发同学参考。先说结论异构数据库迁移工作量的重心不在“搬数据”这一步而在结构转换和业务SQL兼容性评估。PostgreSQL和Oracle虽然都是关系型数据库但数据类型、内置函数、SQL方言、事务细节都有不少差异每一步都可能冒出小坑。文章里我会把关键差异、实操步骤、踩过的坑都列出来尽量让后来的人少走弯路。1. 迁移前先把两边的家底摸清楚1.1 评估哪些对象要迁移哪些可以重建接到迁移需求第一件事不是找工具而是盘点源端PostgreSQL里到底有哪些对象。我通常会把对象分成几类表、索引、约束、序列、视图、物化视图存储过程、函数、包、触发器、同义词、DBLink自定义类型、扩展模块PostGIS、pgcrypto等建一个清单表把每个对象的名称、类型、数量、大小、依赖关系都登记好。对于表还需要按用途分类核心业务表、配置表、日志表、临时表。分类的目的是决定迁移顺序——配置表和小表先迁大表按主键分段并发处理日志表甚至可以跟业务方确认是否需要迁移。数据结构之外还要评估数据量。用SELECT count(*)去数大表会非常慢建议直接查pg_class.reltuples拿估算值再抽样验证。配合pg_total_relation_size看大小能快速摸清哪些是“大块头”。还有一个很容易被忽略的点应用层对PostgreSQL特性的依赖。比如JSONB、数组类型、窗口函数、正则表达式、DISTINCT ON、WITH RECURSIVE这类写法在Oracle里都有对应的实现但语法和语义不完全一样。这个需要拉上开发团队把SQL清单拉出来一条条过一遍。如果业务代码里对PG特性依赖很深迁移成本会直线上升这种项目必须提前规划代码改造。1.2 迁移方案选型三条路线怎么选工具选型决定了后面一个月的工作量。我评估过三条路线方案优势劣势适用场景ORA2PG免费开源能转结构数据支持类型映射定制生成的结构DDL仍需人工校对大表迁移性能一般中小型库、结构迁移为主DataX 手工转换结构迁移性能强支持并发和断点续传对大数据量友好只搬数据DDL还是得自己做大数据量、批量同步商业ETLKettle/Informatica图形化界面功能全面部署重、License成本高性能调优有门槛企业级复杂场景我这次选的是组合方案结构迁移用ORA2PG生成基础DDL再人工比对调整数据迁移用DataX做全量搬运序列、触发器等在迁移完成后用脚本单独处理。为什么这么组合因为DataX的并发能力在大数据量场景下确实稳而ORA2PG能先把结构快速“翻译”一遍省去从零写DDL的时间。2. 结构迁移类型映射和DDL转换是重头戏2.1 PostgreSQL与Oracle常用数据类型映射结构迁移第一步是类型映射。PostgreSQL和Oracle的数据类型名长得像但细节差很多。直接给一张我整理过的映射表PostgreSQL类型Oracle类型说明varchar(n)varchar2(n)长度单位两边都是字符但Oracle按字节还是按字符取决于NLS设置要注意textclob没有长度限制但Oracle的clob操作有限制超过4000字节不能直接做隐式转换char(n)char(n)语义接近注意PG的char会自动补空格booleannumber(1)Oracle表字段没有boolean类型应用层需要用0/1替代smallint/int/integernumber(5)/number(10)PG的int就是4字节Oracle的number可以匹配相应精度bigintnumber(19)注意number默认没有精度建议显式指定serial/bigserialnumber sequencePG的自增列Oracle需要序列加触发器或使用Identity列numeric(p,s)number(p,s)精度定义类似但要注意舍入行为的差异timestamptimestampPG的timestamp不带时区Oracle也是语义基本对应timestamptztimestamp with time zone / with local time zone时区语义有差别建议迁移前确认业务使用的时区datedate两边都有date但PG的date只到天Oracle的date包含时分秒这个差异坑过很多人byteablob二进制数据DataX里用字节流处理uuidvarchar2(32) / raw(16)看业务需要如果只是存储展示用varchar2(32)存字符串最简单json/jsonbclobOracle 21c之前没有原生JSON字段一般用clob存储应用层自行解析数组类型无对应类型需要拆表或序列化存储属于应用改造范围枚举类型varchar2 check约束PG的enum类型Oracle没有改为check约束最省事这里重点说一下两个高频坑第一个是date类型。PostgreSQL的timestamp和date分得很清楚Oracle的date实际上同时存了日期和时间。如果你在PG里用date存了生日迁到Oracle后还是date查询时发现多了时间部分应用层如果依赖trunc()处理还好不处理就会出现日期比较对不上的问题。第二个是boolean。Oracle的表字段没有boolean类型这是PG迁移到Oracle最普遍的问题。我一般改成number(1)然后应用层做一层映射SQL里where is_active true要改成where is_active 1。这块必须提前跟开发确认否则上线后应用报错找半天。2.2 DDL转换的几个细节坑类型映射只是第一步DDL语句本身的转换才是真正花时间的地方。按我这次的经历这几个地方最容易出问题大小写问题。PostgreSQL默认把未加引号的标识符统一转成小写存储Oracle默认转成大写。如果源库建表时规矩都是小写迁移到Oracle后用大写没问题。但如果PG里用了双引号创建混合大小写的表名或字段名迁到Oracle后必须加双引号才能访问否则就报ORA-00942: table or view does not exist。这个在数据字典里排查一遍提前把所有双引号对象清出来能省去后面大量返工。自增列的处理。PG的serial本质是integer sequence default nextval()。迁到Oracle时最简单的做法是去掉default改用Oracle的Identity列12c及以上或者序列加触发器。如果业务逻辑里允许显式插入主键值用Identity会遇到冲突这种情况用序列加触发器更灵活。部分索引Partial Index。PostgreSQL支持where条件的部分索引Oracle原生不支持。如果源库有这种索引要么去掉要么改造成函数索引或普通索引并评估对查询性能的影响。表达式索引和函数索引。PG里很多表达式索引比如lower(email)Oracle支持函数索引但写法略有差异需要把PG的表达式翻译成Oracle的函数调用。分区表。PG的分区语法和Oracle差异很大。PG的分区是基于PARTITION BY RANGE声明式分区Oracle的分区功能强大得多。迁移时先在Oracle里建好分区表再把PG的分区数据按分区边界导入。要注意分区键的选择和分区边界值的一致性否则数据会落到错误的分区。表的COMMENT。PG的注释在COMMENT ON里Oracle也有同名语法这个可以直接转换。但注意字段注释最好在数据迁移前加好很多下游元数据管理系统依赖这些注释生成数据字典漏了会很麻烦。2.3 存储过程、函数与视图的翻译存储过程和函数的转换是整个结构迁移中最费脑子的部分。PostgreSQL用的是PL/pgSQLOracle用的是PL/SQL语法相似但细节差异不少变量声明PG用DECLARE块PL/SQL在IS/AS后直接声明写法上要调整。参数模式PG的IN/OUT/INOUT和Oracle的类似但默认值语法有区别。异常处理两边都有EXCEPTION WHEN ...但异常名称、SQLCODE/SQLERRM的行为不完全一致。游标PL/pgSQL里FOR rec IN SELECT ... LOOP和Oracle的FOR rec IN (SELECT ...) LOOP很像但显式游标属性%FOUND、%ROWCOUNT的处理方式有差异。动态SQLPG的EXECUTE ... USING对应Oracle的EXECUTE IMMEDIATE ... USING基本能对应上但绑定变量的占位符写法要检查。一个比较麻烦的点是PG函数可以返回SETOF、TABLE、JSON等复杂类型Oracle对应的是RETURN SYS_REFCURSOR或者管道函数转换工作量大。如果这种函数多建议单独拉个清单逐个跟开发确认改造方案。视图方面PG里常见的::类型转换在Oracle里要改成CAST。PG的ILIKE要改成LOWER()函数处理。还有DISTINCT ONPG特别常用Oracle没有这个语法必须改写为ROW_NUMBER() OVER (PARTITION BY ...)。这类SQL方言差异光靠工具自动转换是搞不定的必须人工审查。3. 数据迁移全量搬迁的实操过程3.1 数据量摸底与分批思路结构DDL全部在Oracle里建好之后进入数据迁移阶段。先做数据量摸底按pg_total_relation_size排序找出超过10GB的大表。对每张大表查询主键或唯一键的最小值、最大值确定批次切分点。小表和配置表直接一次搬完。迁移顺序上我的习惯是先迁配置表、维度表这类表数据量小、依赖少再迁业务流水表、日志表最后迁超大表和有外键关联的核心表。外键约束建议在数据迁移完成后再启用否则导入顺序一乱就报外键冲突严重影响效率。3.2 用DataX全量迁移的配置实践DataX是阿里开源的数据同步工具支持PostgreSQL Reader和Oracle Writer是我这次数据搬迁的主力。配置一个任务核心是job.json{ job: { setting: { speed: { channel: 8, byte: 1048576 } }, content: [ { reader: { name: postgresqlreader, parameter: { username: pg_user, password: pg_password, column: [id, name, created_at], splitPk: id, connection: [ { table: [source_table], jdbcUrl: [jdbc:postgresql://192.168.1.10:5432/source_db] } ] } }, writer: { name: oraclewriter, parameter: { username: oracle_user, password: oracle_password, column: [id, name, created_at], preSql: [DELETE FROM target_table WHERE 10], connection: [ { table: [target_table], jdbcUrl: [jdbc:oracle:thin://192.168.1.20:1521/ORCLPDB1] } ] } } } ] } }几个关键参数说明一下channel并发数我一般从4开始逐步调到8、16观察源库和目标库的负载。并发太高会把源库的IO打满影响线上业务这个要特别注意。splitPk大表必须指定主键或唯一索引字段作为切分键DataX会按这个字段的区间切分任务。不设置的话多个channel读同一个表容易重复读或者负载不均匀。preSql用来在写入前做清理DELETE FROM target_table WHERE 10是一个空操作不会删数据只是清空表类似的效果需要另外处理。实际如果需要清空目标表用TRUNCATE语句但这个操作会导致整个任务开始前目标表被清空要确认安全后再配。大表迁移时我给每个表单独写一个job配置便于并行执行和单独重跑。比如10张核心大表每个表8个channel分3批跑整体吞吐量比单表跑要稳定得多。3.3 大字段与特殊数据的处理大字段的迁移是容易踩坑的点尤其是text和bytea。text类型在Oracle里对应clob。DataX读写CLOB时走的是流式读取默认会一次性加载到内存。遇到超大字段JVM内存会被撑爆报OutOfMemoryError。调大DataX的JVM堆内存是第一步但更稳妥的做法是对大表单独配置任务channel不要太高必要时把byte参数调小控制单次读取的数据量。bytea对应Oracle的blob处理逻辑类似。DataX里把PG的bytea读出来写进Oracle的blob中间需要转成字节流默认行为没问题但要注意源端的bytea_output格式如果输出的是hex格式DataX的JDBC驱动能直接处理如果输出的是escape格式可能会遇到解析问题建议在PG连接参数里显式指定bytea_outputhex。时间类型方面timestamptz迁移到timestamp with time zone时要注意时区差异。如果应用层统一用北京时区PG端的会话时区和Oracle端的会话时区都要显式设置为同一时区否则数据看起来会差8小时。数值精度方面PG的numeric和Oracle的number都是十进制变长类型精度基本对得上。但要注意PG的numeric如果没指定精度可以存任意精度的数值Oracle的number没指定精度也是变长两者行为相近一般不会丢精度。反观PG的double precision迁移到Oracle的binary_double如果只建了number没有指定精度可能出现小数位被截断的问题这类字段建议提前跟业务确认精度要求。字符集方面源端PG如果是UTF8目标Oracle如果用了ZHS16GBK中文字符在迁移时可能出现乱码或者长度超限。最省心的办法是保持Oracle字符集为AL32UTF8如果目标库已经定死了GBK那在DataX的Reader和Writer里都要显式指定encoding并做好抽样验证。3.4 数据一致性校验数据搬完后验证环节不能省。我有三个层次的校验第一层行数校验。对每张表统计两边的count(*)。注意大表的count可能很慢而且PG和Oracle在并发写入的情况下统计值可能不一致。我的做法是迁移期间暂停源库的写操作或者选择在业务低峰期保证静态数据再对比count才有意义。第二层抽样字段级校验。用主键做关联取若干区间对比每条记录的字段值。最简单的方式是拼一个字段串计算MD5或哈希值做比对-- PostgreSQL端 SELECT md5(string_agg(column_name::text, | ORDER BY id)) FROM table_name WHERE id BETWEEN ? AND ?; -- Oracle端 SELECT LOWER(SUBSTR(STANDARD_HASH(listagg(column_name, |) WITHIN GROUP (ORDER BY id), MD5), 1, 32)) FROM table_name WHERE id BETWEEN ? AND ?;两边算出同样的MD5说明这批数据一致。不一致的区间定位到具体行再人工确认。第三层业务校验。这是最关键的。找业务方抽出几条核心查询在Oracle上跑一遍对比结果集跟PG上的结果是否一致。这一步能发现那些“数据搬对了但SQL语义变了”的问题比如空字符串被当成NULL、日期格式隐式转换出错、布尔值0/1映射不对等等。4. 序列与自增主键的衔接处理数据搬完后序列的处理常常被忽略但一旦漏了应用一插入新数据就会报主键冲突。PG的序列是独立对象Oracle的序列也是独立对象但两者的元数据不通用必须手工重建并设置初始值。做法如下-- 在Oracle中创建序列 CREATE SEQUENCE seq_target_table_id START WITH 1000001 INCREMENT BY 1 NOCACHE NOCYCLE;START WITH的值怎么确定在PG端查该表主键的最大值加一个增量SELECT COALESCE(MAX(id), 0) 1 FROM source_table;要注意的是如果应用代码里曾经显式插入过主键序列的当前值可能落后于表里的最大主键。比如业务手工插入过id20000的记录但序列只跑到了18000此时如果按MAX(id)1设置也可能小于实际业务的预期值。稳妥起见取MAX(sequence_last_value, table_max_id1)也就是序列本身的值和表最大主键值取较大者。如果Oracle目标表用的是12c及以上版本的Identity列可以通过以下语句修改起始值ALTER TABLE target_table MODIFY (id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMITS VALUE));这里的LIMITS VALUE是Oracle的语法表示取当前已有数据的最大值加1比较省事。迁移后还要做一个联动检查所有引用该序列的触发器、存储过程、应用代码确认它们调用的是新建的序列。两边序列名称如果不一样应用层连接串和SQL里的nextval调用也要一并改掉。5. 典型报错与排查实录这一部分把这次迁移过程中实际遇到、以及周围同事经常踩的坑整理成问题速查表按“现象—原因—处理方式”的格式记录。报错信息可能原因处理方式ORA-00942: table or view does not exist表名大小写不一致、schema权限不对、同义词缺失检查Oracle里对象实际名称确认是否需要加双引号授予对应schema的访问权限创建同义词ORA-01400: cannot insert NULL into (字段名)PG空字符串在Oracle里变成NULL目标表该字段有NOT NULL约束数据迁移前把PG的空字符串转成非NULL占位符或调整目标表约束迁移后做一轮空值检测ORA-12899: value too large for column (字段名)字符集从UTF8变GBK导致字节变长或源字段长度大于目标字段核对目标表VARCHAR2长度字符集不匹配时用AL32UTF8重建或调整字段长度ORA-00001: unique constraint violated源库存在重复数据或大小写不一致导致唯一键冲突迁移前在PG端做一次唯一性检查清理重复数据后再迁移ORA-01843: not a valid month日期字符串格式与目标会话NLS参数不匹配设置NLS_DATE_FORMAT或在SQL里显式TO_DATE(yyyy-mm-dd hh24:mi:ss)ORA-06502: PL/SQL numeric or value error存储过程或函数里类型转换溢出检查PL/SQL中变量长度和精度和源PL/pgSQL对齐之后再编译ORA-28547: connection to server failed, probable Oracle Net admin error连接远程Oracle服务时网络配置不对检查tnsnames.ora、listener.ora和防火墙端口监听服务无法启动listener.ora配置错误、主机名解析失败、端口被占用检查lsnrctl status日志确认hosts解析调整端口DataX迁移时报OutOfMemoryError大字段CLOB/BLOB全部加载到内存调大JVM堆内存降低channel数拆小批次处理迁移过程中目标库redo日志暴涨批量写入产生大量重做日志增加redo log大小和组数或控制单批次数据量分多次commit5.1 连接层面的坑连接Oracle的问题很多人一开始都会遇到。最典型的是ORA-28547这个报错经常出现在应用服务器连数据库的时候原因是Oracle Net配置不对。用tnsping命令先通一遍确认能解析到目标实例再看listener.ora里监听的服务名是否匹配。监听服务无法启动多半是主机名解析的问题。我遇到过一台服务器改了hostname之后Oracle监听起不来检查/etc/hosts里没有映射新主机名导致的。加上映射重启监听就好了。还有防火墙1521端口被禁的情况很普遍低版本Oracle的监听默认端口不一定都是1521要看listener.ora里实际配置。5.2 数据加载报错的处理思路数据加载的报错大部分都集中在类型和字符集这两类。ORA-12899 value too large是高频报错。比如说PG的varchar(100)按字符算存100个中文字符没问题迁到Oracle后如果目标库字符集是ZHS16GBKvarchar2(100)默认按字节算100个中文会占200字节直接超限。解决办法是把目标字段长度按字符集扩大比如varchar2(200)或者确保目标库用AL32UTF8注意AL32UTF8中文字符要3字节还要留更大余量。ORA-01400 NOT NULL冲突也经常遇到。前面提过Oracle里空字符串就是NULLPG里不是NULL。如果源表某个字段既有NOT NULL约束又允许空字符串数据迁移到Oracle后就会报冲突。这种字段迁移前要跟业务确认一是把空字符串转成其他占位符比如空格二是去掉目标表的NOT NULL约束三是应用层改为存NULL。5.3 迁移性能问题大数据量迁移时性能瓶颈往往不在工具而在目标库。第一个坑是索引拖慢插入。如果目标表上建了大量索引每次插入都要维护索引数据量大时速度急剧下降。我习惯的做法是在数据迁移前把目标表上的索引尤其是非唯一索引先drop掉等数据全部搬完再重建索引。唯一约束和主键保留因为要靠它们保证数据不重复。第二个坑是Oracle的redo日志。批量insert会产生大量redo如果redo log太小会频繁触发checkpoint严重影响性能。迁移前把redo log调整到合适大小起码保证峰值写入时不至于频繁切换。第三个坑是PG端的垃圾数据。PostgreSQL的MVCC机制会产生dead tuple如果在迁移高峰期表上堆积了大量dead tuple全表扫描的效率非常差。迁移前做一次VACUUM能有效提升读性能。6. 切换前的验证清单6.1 应用兼容性回归数据迁移完成不代表项目结束还要保证应用能跑起来。这个阶段需要开发配合做一轮SQL兼容性回归把应用代码里的SQL扫描一遍重点排查以下几类分页查询PG的LIMIT/OFFSET改成Oracle的ROWNUM或者FETCH FIRST ... ROWS ONLY12c旧版本Oracle只能改写成子查询ROWNUM ?的方式。字符串连接PG的||在Oracle里也支持但如果PG里用了concat_ws、string_agg这类函数要替换成Oracle的LISTAGG或XMLAGG。布尔表达式WHERE is_active这种写法在Oracle里不成立要改成WHERE is_active 1。类型转换::int改成CAST(... AS NUMBER)::text改成TO_CHAR()。空值处理PG里COALESCE、NULLIF在Oracle都有但NVL、NVL2、DECODE是Oracle特有的如果SQL要在两边跑统一用COALESCE最稳。日期函数PG的NOW()在Oracle里对应SYSDATE或CURRENT_TIMESTAMPPG的EXTRACT(EPOCH FROM ...)是取Unix时间戳Oracle用(sysdate - date 1970-01-01) * 86400计算。驱动也要换应用连接PG用的是postgresql-42.x.x.jar连Oracle要用ojdbc8.jar或ojdbc11.jar。连接串从jdbc:postgresql://host:5432/db改成jdbc:oracle:thin://host:1521/service_name。6.2 统计信息、权限与性能基线数据都进去了还要收集统计信息否则Oracle的CBO优化器不知道表的数据分布执行计划可能非常差BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname APP_SCHEMA, cascade TRUE, degree 8); END;然后跑几条核心查询对比执行计划。我遇到过一种情况数据量完全一样但Oracle里走了全表扫描原来PG走的是索引扫描原因是统计信息没收集。收集完之后执行计划恢复正常。权限方面确认目标schema下面所有表的SELECT/INSERT/UPDATE/DELETE权限都授权给了对应业务账号。如果有存储过程还要单独GRANT EXECUTE。同义词也不要忘如果应用用短名访问对象需要在目标库创建对应的私有或公共同义词。6.3 回退方案与灰度切换切换不可能一次成功回退方案必须有。我的建议是切换日之前把PG源库保留至少一个月业务验证稳定后再下线。切换窗口内先切只读流量观察一段时间再切写流量。如果Oracle端出了问题把读写流量切回PG应用配置改回PG连接串即可。前提是PG端写入的数据不能丢所以在切换期间要做好业务写入的规避或同步。增量数据同步是最难的一环。如果业务允许在切换窗口内停写全量迁移后直接切流量即可。如果业务不能停写就需要在切换窗口前建立增量同步机制。选型方面商业OGG、Debezium、自研同步脚本都可以但要提前测试别等到切换日才发现增量链路不通。结尾这次迁移项目做下来我最大的体会是异构数据库迁移真正难的不是“数据搬运”而是“语义对齐”。PostgreSQL和Oracle表面看起来差不多实际上在类型系统、SQL方言、存储引擎、会话管理上差异非常多。数据搬过去很快但搬完之后要让业务跑得一样顺需要花大量精力在结构转换、SQL改写和验证上。最后分享一个小技巧不管用哪个工具搬数据先挑一张小表走通全流程——从结构转换、数据迁移、行数校验到应用查询验证确认整条链路没问题再铺开干。别一上来就并发跑几十张大表等发现类型映射错了返工成本会让你怀疑人生。 
