这两年数据库迁移的项目特别多Oracle迁PostgreSQL已经成为很多企业级技术栈调整的标配动作。ora2pg作为这个领域最主流的开源迁移工具几乎每个Oracle到PostgreSQL的迁移项目都会碰到它。这篇文章就把我在多个迁移项目里使用ora2pg的完整实践拆开来讲从工具选型、环境准备、参数配置到结构迁移、数据迁移、SQL改写和校验调优把能直接复用的经验和绕过的坑都整理出来。无论你是刚接手迁移任务的新手还是已经在迁移路上踩过坑的工程师这篇文章都能给你一份可落地的工作清单。1. 迁移项目整体设计与思路拆解1.1 为什么现在都在做Oracle到PostgreSQL的迁移先说个我自己的观察。前些年提到数据库选型很多企业闭眼就是Oracle稳定、生态成熟、DBA熟手多。但这些年情况变了Oracle的授权费用逐年走高尤其是核心系统要扩容的时候那个报价单拿出来能把预算吓一跳。PostgreSQL这边呢功能上越来越能打复杂查询、窗口函数、JSON处理、分区表全都齐了事务机制扎实开源社区活跃还不用被商业授权卡脖子。一进一出很多企业开始认真考虑把非核心甚至核心系统从Oracle迁到PostgreSQL。还有一个隐性驱动力是人才结构。现在年轻一点的开发者和DBA很多人的第一套数据库是MySQL或者PostgreSQL懂Oracle的反而越来越稀缺。企业里老DBA退休一个就少一个与其硬撑着Oracle的人力缺口不如把系统迁到更主流的开源生态里招人也好招内部培养也快。再加上容器化、云原生这套东西铺开之后PostgreSQL在云上的部署方案非常成熟很多团队本来就在用云数据库迁移反而是一个顺势而为的动作。不过我得说句实话Oracle迁PostgreSQL不是简单的“换个数据库连一下”它是一次涉及结构、数据、SQL、存储过程、应用连接方式的全链路改造。如果只是拿工具导个表结构、灌个数据后面应用跑起来各种SQL报错那才是真正的灾难。所以迁移项目第一步不是急着装工具而是把整体思路理清楚要迁哪些库、哪些对象、哪些应用允许停机多久谁来验证怎么回退。这些问题想不清楚后面每一步都可能返工。1.2 为什么选ora2pg而不是其他工具Oracle到PostgreSQL的迁移工具市面上有不少最常见的是ora2pg、pgloader、AWS DMS还有企业级商业工具如Ispirer、Full Convert以及一些基于自研脚本的“土办法”。我做一个横向对比方便你结合自己项目的情况判断。工具方案开源结构迁移能力数据迁移能力SQL改写能力适配复杂对象上手难度ora2pg是GPL强支持表、索引、约束、视图、函数、存储过程、包、触发器等强支持COPY批量、并行、断点续传强内置大量Oracle语法转PostgreSQL规则强支持物化视图、序列、同义词等中等配置项多pgloader是MIT弱主要用于表结构和数据强基于COPY的性能极高弱基本不改写SQL一般带类型映射但复杂对象支持差低AWS DMS否商业云服务中等结构迁移支持有限强支持持续同步弱复杂对象需手工处理一般中商业迁移工具否强强强强低但是贵我自己在项目里默认首选是ora2pg原因有三个。第一它是专门为“Oracle到PostgreSQL”这一条路线设计的连名字都是Oracle To PG的缩写对Oracle对象的覆盖度远超通用ETL工具。第二它不只是一个数据搬运工它能把Oracle的PL/SQL转换成PL/pgSQL还能导出评估报告提前告诉你这个库在PG里会遇到哪些兼容性麻烦。第三完全开源部署不依赖外部商业服务在内网环境里也能跑这对很多政企和金融客户来说是硬性要求。当然pgloader在单纯导数据的时候性能非常出色如果你只需要把数据搬过去、结构在PG里手工建那pgloader也挺好。但真实项目里一个Oracle库往往带几十上百个存储过程、视图、序列、触发器和各种约束这些用pgloader基本搞不定。所以我的推荐是结构复杂、对象多、PL/SQL重用ora2pg结构简单、只搬数据、时间紧可以考虑pgloader。大部分场景下ora2pg是真命天子。1.3 迁移项目的通用流程与阶段划分一个完整的迁移项目我习惯切成六个阶段评估、环境准备、结构迁移、数据迁移、应用改造与验证、切换割接。这六个阶段不是线性的实际执行中会有很多来回但阶段划分一定要清晰否则进度没法管理。评估阶段的核心不是“能不能迁”而是“迁过去要改多少东西”。用ora2pg的评估模式跑一遍得到兼容性报告看看哪些对象能自动转换、哪些需要人工改写、哪些根本无法转换。这个阶段决定了整个项目的工作量。我见过一个项目评估报告显示400个存储过程只有60个能自动转换剩下340个全要手工改工作量立刻翻了三倍。所以评估报告一定是一开始就要出的东西而不是迁完了才看。环境准备阶段要做的事很杂装好PostgreSQL和ora2pg建好数据库、用户、表空间设置好源端Oracle的访问权限确认字符集统一方案还有网络、磁盘、内存这些基础资源的规划。结构迁移阶段是用ora2pg导出表结构、序列、函数、存储过程、视图、触发器等然后在PG里执行重建。数据迁移阶段用COPY模式灌数据大表考虑并行和分批。应用改造与验证阶段是工作量最大的部分要把应用里的SQL全部回归一遍结合ora2pg的改写结果修正语法和方言差异。切换割接阶段就是停机窗口内完成最后一次增量同步、应用切库、回退预案试跑。这套流程看起来平淡无奇但每一阶段都有技术细节和坑接下来我从第二阶段开始逐个拆解。2. 迁移前准备工作与环境搭建2.1 版本选择与兼容性评估ora2pg的版本迭代比较快我在项目里常用的是23.x和24.x系列这两个版本对Oracle 19c和PostgreSQL 14到17的配合都比较好。理论上ora2pg支持Oracle 9i到19c、21c以及PostgreSQL 9.4到17但老版本Oracle的字典视图差异可能会导致部分对象识别不全。我的建议是源端Oracle尽量在11g以上目标端PostgreSQL直接用16或者17。PostgreSQL 16和17在并行查询、逻辑复制、vacuum性能上的改进非常明显迁移完后的运维负担小很多。版本选择上还有一个容易忽略的点ora2pg是用Perl写的它对Perl版本和依赖模块有要求。Linux上最好用系统自带的Perl 5.26以上再用包管理器安装DBD::Oracle和DBD::Pg。DBD::Oracle这个模块需要Oracle Instant Client的配合所以你的迁移服务器上得装一个Oracle客户端环境。这一步很多人会在配置Oracle客户端时卡住实际上下载对应架构的instantclient-basic和instantclient-sdk包解压配置好LD_LIBRARY_PATH就行了不需要安装完整的Oracle软件。如果你不想在迁移服务器上装Oracle客户端还有一个替代思路用Docker起一个装了ora2pg和Oracle客户端的容器把迁移工具链封装好。我在自动化迁移平台里就是这么做的既避免了污染宿主机环境也方便多项目复用。不过容器方案要特别注意网络能连通源库和目标库很多内网环境的容器网络策略比宿主机严格得多跑不通的情况我遇到不止一次。2.2 ora2pg安装配置实战我以Linux环境为例给你一个完整的安装步骤。首先安装操作系统层面的依赖Debian/Ubuntu系用aptCentOS/RHEL系用yum或dnf。# Ubuntu/Debian apt-get update apt-get install -y perl cpanminus libdbi-perl libdbd-pg-perl build-essential unzip # CentOS/RHEL yum install -y perl perl-CPAN perl-DBI perl-DBD-Pg gcc make unzip然后安装Oracle Instant Client。这里注意Instant Client的版本要和你源库Oracle大版本匹配比如源库是19c就下载19.x的instantclient-basic和instantclient-sdk。解压后放到一个固定目录比如/opt/oracle/instantclient_19_19然后设置环境变量。export ORACLE_HOME/opt/oracle/instantclient_19_19 export LD_LIBRARY_PATH$ORACLE_HOME:$LD_LIBRARY_PATH export PATH$PATH:$ORACLE_HOME接下来安装Perl的Oracle驱动模块DBD::Oracle。先下载对应版本的源码包或者直接用cpanm安装。cpanm --force DBD::Oracle这里加--force是因为DBD::Oracle在某些Perl版本下编译告警比较多不加的话很可能中途失败。装完验证一下perl -e use DBD::Oracle; print $DBD::Oracle::VERSION最后安装ora2pg本体。从GitHub的darold/ora2pg仓库下载源码包解压后执行标准的perl安装流程。wget https://github.com/darold/ora2pg/archive/refs/tags/v24.5.tar.gz tar zxvf v24.5.tar.gz cd ora2pg-24.5 perl Makefile.PL make make install安装完成后验证ora2pg --version至于Windows环境ora2pg也支持但配置过程更折腾建议你在Windows上做评估分析可以大规模迁移还是放在Linux服务器上跑性能和稳定性都更好。Windows下要安装Strawberry Perl再装DBD::Pg和DBD::Oracle难度主要在Oracle客户端的DLL依赖上新手不建议在Windows上抗这个罪。2.3 数据库侧准备工作迁移前的数据库侧准备很多人会漏掉结果跑到一半才回头补权限。源端Oracle这边用于迁移的账号需要能够读取数据字典和所有目标对象的定义建议授予DBA角色或者按需授予SELECT_CATALOG_ROLE、SELECT ANY DICTIONARY、SELECT ANY TABLE等权限。不要用SYS或者SYSTEM直接连DBA账号配合独立迁移专用账号是更安全的做法。目标端PostgreSQL这边需要提前建好数据库和用户。我的习惯是给迁移业务单独建一个schema不要什么都塞进public里后面管理起来会非常痛苦。字符集统一用UTF8除非你确认整个数据链路都是纯英文或者兼容性强。Oracle侧的字符集如果是ZHS16GBK或AL32UTF8迁移到PG的UTF8库时中文一般没有问题但是要注意那些在GBK下合法、在UTF8下非法的特殊字符后面数据校验阶段我会专门说。PG侧的关键参数也要提前调整特别是涉及到大批量数据写入的我在实际项目里会把maintenance_work_mem调到256MB以上max_wal_size调大到4GB以上checkpoint_timeout适当延长。这些参数能让copy数据的速度提升一个档次否则默认配置下大量写入会频繁触发checkpoint性能明显下降。还有wal_level如果后面要用逻辑复制做增量同步提前要设成logical不然后面改参数还得重启数据库。另外说一个很多文档不会提的细节Oracle的数据字典里表和字段名默认是大写很多老业务建表时没用双引号所以全部是大写。ora2pg导出到PG时会做大小写转换处理但某些特殊对象名可能保留原样。这会导致应用里如果写了带引号的小写表名在PG里查不到。这个坑我后面在常见问题里会细讲准备阶段你只需要知道和应用团队提前约定好对象命名的规则能省很多扯皮。3. 核心迁移流程与参数配置细节3.1 ora2pg配置文件深度解读ora2pg的配置是通过一个ora2pg.conf文件来驱动的它默认会读取/etc/ora2pg/ora2pg.conf但我在实际项目里习惯每个迁移任务单独建一份配置文件避免多个任务的配置互相干扰。配置文件的格式是keyvalue注释以#开头结构很清晰你可以用命令生成一份默认配置作为参考。ora2pg --init_project my_migration # 或直接生成配置模板 ora2pg --print_config my_ora2pg.conf用到最多的几个配置项我整理成了表格你可以直接对着设置。配置项作用我的建议ORACLE_HOMEOracle客户端主目录指向Instant Client解压目录ORACLE_DSN源库连接信息格式dbi:Oracle:host;port;sid或service_name尽量用service_name连接别用SIDORACLE_USER / ORACLE_PWD源库用户名密码单独迁移账号不共享日常账号PG_DSN目标库连接信息格式dbi:Pg:host;port;dbname对应待迁移目标库PG_USER / PG_PWD目标库用户名密码具备建表、索引等DDL权限SCHEMA要迁移的Oracle schema逗号分隔一次迁一个schema避免依赖混乱TYPE导出类型TABLE/VIEW/SEQUENCE/FUNCTION/TRIGGER/PROCEDURE等按需组合用逗号分隔EXPORT_SCHEMA是否导出结构DDL1COPY_DATA是否迁移数据1表示数据随结构一起导出DATA_LIMIT每个表导出多少数据行数0表示全量DATA_TYPEPG的目标数据类型映射一般用默认特殊类型自定义映射PARSE_BAD_FILE解析失败的SQL记录文件路径设置后方便排查LOG_FILEora2pg运行日志路径必须设置否则出错难定位REPORT_FILE评估报告输出路径迁移前必跑重点解释一下ORACLE_DSN的写法。ora2pg用的是Perl DBI连接串格式和Oracle SQL*Plus里的连接串不一样不能拿tnsnames.ora的写法直接填。正确的是ORACLE_DSNdbi:Oracle:host192.168.1.10;port1521;service_nameORCLPDB如果你只有SID那就用sidORCL这种写法。连接串里不要带用户名密码那是由ORACLE_USER和ORACLE_PWD单独提供的。这个连接串是最容易配错的地方尤其是PDB模式的Oracle 12c以上很多老DBA还在用SID连接结果连到了CDB而不是PDB导出来的对象根本不是业务库的这种低级错误一旦发生整个评估报告就废了。3.2 迁移评估与导出准备拿到配置后第一件事不是马上导数据而是跑评估模式。ora2pg的评估报告能告诉你每个对象类型的数量、可自动转换的比例、需要手工处理的工作量是所有后续排期的依据。跑评估很简单ora2pg -c my_ora2pg.conf -t SHOW_REPORT如果只想快速看一个对象类型比如存储过程可以用SHOW_PROCEDURE或者SHOW_TABLE、SHOW_VIEW等。我前端时间做一个核心交易系统迁移的时候就是先用SHOW_REPORT发现这个库里有大概1200个存储过程其中ora2pg能自动转换的只有700多个剩下的要在PL/pgSQL里手工重写这样才逼着项目组提前多招了两个开发不然排期铁定崩。还有一种非常实用的评估手段是SHOW_COLUMN和SHOW_TABLE可以快速检查数据类型的映射情况。我习惯用下面这组命令逐个确认# 查看所有表及其行数 ora2pg -c my_ora2pg.conf -t SHOW_TABLE # 查看所有列的类型映射评估 ora2pg -c my_ora2pg.conf -t SHOW_COLUMN # 查看存储过程和函数的评估 ora2pg -c my_ora2pg.conf -t SHOW_PROCEDURE跑完评估后ora2pg会给出一个报告里面明确标注了哪些对象在PostgreSQL中有对应的自动转换策略哪些需要review哪些是完全无法转换的。这个报告要发给应用开发团队逐条分析因为有些“自动转换”出来的SQL可能在语义上变了不是简单搬过去就完事的。3.3 结构迁移与数据迁移的具体执行评估通过后可以开始正式导出。我一般把结构迁移和数据迁移分成两步执行这样中间可以人工介入检查DDL。第一步导结构把TYPE配置成需要迁移的对象类型组合COPY_DATA设为0。# 只导出结构 ora2pg -c my_ora2pg.conf -t TABLE --copy-data 0 ora2pg -c my_ora2pg.conf -t VIEW --copy-data 0 ora2pg -c my_ora2pg.conf -t SEQUENCE --copy-data 0 ora2pg -c my_ora2pg.conf -t TRIGGER --copy-data 0 ora2pg -c my_ora2pg.conf -t FUNCTION --copy-data 0 ora2pg -c my_ora2pg.conf -t PROCEDURE --copy-data 0每个命令会生成对应的SQL文件比如TABLE会生成table.sqlPROCEDURE会生成procedure.sql。这里有个重要细节ora2pg生成的SQL文件中外键约束默认可能是追加在表定义之后的你要自己在PG里按顺序执行先建表再建序列再灌数据最后加索引和外键约束。如果一上来就把整个DDL文件全部执行外键和索引可能因为表数据还没迁移就报错或者导致后面数据灌入性能大幅下降。正确顺序是建表、建序列、导数据、建索引、建约束、建视图、建触发器、建函数存储过程。第二步导数据把COPY_DATA打开通常用COPY模式而不是INSERT模式速度能差出一个数量级。一个千万级的表INSERT模式可能要跑半小时COPY模式可能几分钟就结束了。# 导出所有表的数据 ora2pg -c my_ora2pg.conf -t TABLE --copy-data 1 -o data.sqlora2pg的数据导出默认会写到文件里然后在PG端执行文件来完成导入。如果想直接从Oracle读到PG不走中间文件可以使用--direct模式但这个模式对两边数据库的网络延迟比较敏感内网环境下问题不大跨机房容易超时。我为了稳妥还是习惯先落文件再导入这样数据文件还能留档后面出问题可以重新导入不用再连源库拉一遍。大数据量的表建议拆开来单独导。先看评估报告里哪些表超过百万行单独为这些大表配置导出参数比如再加并行度参数JOBS_NUM让多张表并行导出。我做过一个表有2亿行单线程COPY跑了快40分钟拆成4个并行后总耗时降到12分钟收益非常明显。4. 从Oracle语法到PostgreSQL语法的改造实践4.1 数据类型映射与处理数据迁移过程中最基础但也最关键的环节是数据类型的映射。ora2pg内置了一张Oracle到PostgreSQL的类型映射表默认规则大致如下。Oracle类型PostgreSQL类型注意事项NUMBER(p,s)numeric(p,s)精度超过18时用numeric否则也可用bigintNUMBER(1)smallint实际是布尔语义的字段要注意应用代码NUMBERnumeric无限精度但性能比integer差能定长度尽量定VARCHAR2(n)varchar(n)Oracle中VARCHAR2(10)按字节计PG按字符计中文字段要小心CHAR(n)char(n)PG的char会补空格和Oracle行为基本一致DATEtimestamp(0)Oracle的DATE包含时分秒PG的date只有日期必须映射成timestampTIMESTAMPtimestamp无时区PG默认timestamp是without time zoneTIMESTAMP WITH TIME ZONEtimestamptz时区语义注意转换CLOBtext无长度限制BLOBbytea二进制大对象RAW(n)bytea二进制流FLOATdouble precision浮点映射为双精度这里有一个高频头疼点Oracle的DATE类型。很多老业务把DATE当时间戳用里面存了完整的年月日时分秒到了PG里你要是直接映射成date类型秒和时分全被截断。ora2pg默认会映射成timestamp(0)这个方向是对的但你要是在PG里手工建表时用了date那数据就悄悄丢了。我每次做迁移时都会让SQL开发在应用里全面排查DATE字段的用法看看有没有人把它当字符串拼。还有一个我反复跟团队强调的点VARCHAR2的字节与字符问题。Oracle的VARCHAR2(n)里n的单位是字节比如VARCHAR2(20)可以存20个英文字母但只能存6个中文汉字如果UTF8编码下3字节一字。PostgreSQL的varchar(n)里n是字符数同样声明varchar(20)能存20个中文。所以迁移时如果原表是VARCHAR2(20)直接映射成varchar(20)看起来没问题但实际存储能力变了。对既有数据通常没有影响因为你不需要更长但如果你是反向从PG往Oracle迁或者两边做双向同步这个语义差异会直接导致写入失败。正确的做法是按字段内容的最大字符数来重新设计目标列的长度不要无脑沿用原来的长度定义。4.2 存储过程、函数与触发器的改造存储过程和函数的迁移是整个项目里最耗人力的部分。ora2pg对PL/SQL到PL/pgSQL的转换已经内置了很多规则能处理基本的IF/ELSE、LOOP、CURSOR、EXCEPTION等结构但遇到复杂的包PACKAGE、动态SQL、隐式游标、%ROWTYPE、%TYPE以及很多Oracle特有的内置函数还是需要人工介入。先看最基本也最常见的差异点。Oracle的SELECT INTO在PG里写法和语义略有不同。Oracle中直接SELECT ... INTO变量 FROM dualPG里需要SELECT ... INTO变量或者更推荐用PERFORM或者RETURNING。其次Oracle的函数调用允许不带括号的函数名比如SYSDATE、USER在PG里很多要写成CURRENT_TIMESTAMP、CURRENT_USER或者NOALIAS之后才能宽松处理。再次NULL的判断和空字符串的处理Oracle认为空字符串就是NULLPG里空字符串和NULL是不同概念这对数据一致性影响极大很多迁移后应用行为变化都源于这里。还有一个大坑是序列。Oracle里的序列用法是seq_name.NEXTVAL和seq_name.CURRVALPG里对应的是nextval(seq_name)和currval(seq_name)。ora2pg会自动把Oracle的序列定义转换成PG的sequence并且把SQL里的NEXTVAL写法改写成nextval(...)。但是如果你在Oracle里是用触发器序列来实现自增PG这边更推荐直接用IDENTITY列或者SERIAL不然你还要在PG里保留一个触发器来调nextval纯属给自己加戏。迁移时我建议把“序列加触发器产生主键”的模式直接改良成PG的GENERATED BY DEFAULT AS IDENTITY这一步顺手做了后面应用插入数据时和Oracle行为基本一致。包PACKAGE是另一个棘手对象。Oracle的包把一组函数、存储过程和全局变量封装在一起PG没有直接对应的包结构。ora2pg会把包里的函数和过程拆成独立的顶层函数同时把包里的全局变量处理成特殊的配置表或者会话变量。这个拆分过程在实际项目中几乎不可能一次成功主要问题在于包内函数互相调用时包名被剥掉后函数名可能冲突或者私有函数被暴露出来。我的经验是迁移后让开发把包内部调用关系重新梳理一遍转成PG的schema组织方式每个schema对应一个业务模块而不是硬凑一个和Oracle包一一对应的结构。触发器迁移也要重视。Oracle触发器语法里大量使用:NEW和:OLD伪记录PG里对应的是NEW和OLD少了冒号。Oracle里BEFORE INSERT触发器可以修改:NEW的值来改变最终插入的数据PG里也有类似能力但语法细节不同。还有触发器函数在Oracle里是独立于表的PG里必须先写一个返回TRIGGER的函数再CREATE TRIGGER绑定到表上这一步ora2pg可以自动生成但生成的函数名可读性不好后续维护起来很痛苦我一般会让人工过一遍命名规则。4.3 分页、函数与特殊SQL改写Oracle分页用的是ROWNUM这是大部分开发人员首先遇到的语法迁移难点。经典的Oracle分页写法SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY hiredate DESC ) t WHERE ROWNUM 50 ) WHERE rn 40;PG里的等价写法就简单得多SELECT * FROM emp ORDER BY hiredate DESC OFFSET 40 LIMIT 10;ora2pg能识别简单的ROWNUM分页并改写但遇到ROWNUM和其他条件混合、或者ROWNUM用在UPDATE场景就很容易漏网。比如Oracle里UPDATE ... WHERE ROWNUM 1这种写法PG里没有直接等价物需要用子查询或者CTE先取主键再回表更新。这类SQL在评估报告里通常会被标记为无法转换或需要review你要专门安排一批人工排查。常用的Oracle函数到PG的替换关系我也整理了一份速查表Oracle函数PostgreSQL等价备注NVL(a, b)COALESCE(a, b) 或 ISNULL语义等价DECODE(a, b, c, d)CASE WHEN ab THEN c ELSE d END也可用CASE表达式SYSDATECURRENT_TIMESTAMP / now()注意隐式转换TO_CHAR(date, YYYY-MM-DD HH24:MI:SS)TO_CHAR(date, YYYY-MM-DD HH24:MI:SS)格式基本一致但部分格式符不同ADD_MONTHS(date, n)date (n * interval 1 month)语义有细微差异月底边界要小心TRUNC(date)date_trunc(day, date)语义有差异LISTAGG(col, ,)STRING_AGG(col, ,)PG的STRING_AGG更强WM_CONCATSTRING_AGG(DISTINCT col, ,)WM_CONCAT是Oracle隐藏函数官方不推荐ROWNUMLIMIT/OFFSET需要改写CONNECT BY PRIORWITH RECURSIVE层级查询改写最头疼连接查询方面Oracle的()外连接写法必须改写成标准的LEFT/RIGHT JOIN。ora2pg能处理一部分但老SQL里()写多了容易出现ambiguity我强烈建议在迁移前做一个静态扫描把所有()写法先统一改写成ANSI JOIN再进行迁移。层级查询CONNECT BY在PG里用WITH RECURSIVE改写这不是简单的关键字替换涉及递归逻辑的重构遇到过特别复杂的树形查询我和开发一起在会议室白板上演算了两个小时才算清楚。PG还有一种Oracle没有的写法ON CONFLICT。这在做增量更新时特别好用能实现UPSERT语义。从Oracle迁移到PG的应用很多原本要写MERGE INTO的地方在PG里可以用INSERT ... ON CONFLICT DO UPDATE大大简化。这是迁移后顺手做的优化不算必须但值得做。5. 数据一致性校验与性能调优5.1 校验方案设计与实施数据搬过去不等于数据是对的。我做过不止一个项目表面上看表行数都对结果字段层面一堆差异。所以校验必须分三个层次行数校验、字段级抽样校验、业务逻辑验证。行数校验最简单两边分别执行count(*)对比结果。大表count很慢Oracle可以用all_tables里的num_rows做预评估但那个不精确最终还是要跑真实count。-- Oracle SELECT table_name, num_rows FROM all_tables WHERE owner SCOTT; -- PostgreSQL SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname public;这两个值可以作为快速筛查但不能作为最终结论。真正确认一致性我会用哈希聚合的方式-- Oracle SELECT SUM(dbms_crypto.hash(rawtohex(column_data), 3)) FROM table_name; -- PostgreSQL SELECT SUM(hashtextextended(column_data::text, 0)) FROM table_name;这里我只是举个例子实际操作中你可以将所有关键字段拼成一个字符串再做哈希聚合两边对比聚合值。这个方案能快速发现某一列的数据是否有差异但不能定位到具体行。定位具体行需要再缩小范围做分块或者抽样对比。字段级抽样校验是更细的工作。我会写一个脚本从两张表各自按主键抽样10000行逐字段比对。还有一种更可靠但工程量大的方式是按主键范围分批把两边数据都导出成CSV然后用diff工具对比。这个方案在数据量不大时最稳但一旦表上亿就不推荐了还是用哈希分块处理更实际。最后一步是业务逻辑验证。让测试团队拿真实的查询脚本和应用功能在PG环境上跑一遍重点关注金额精度、日期显示、字符串排序、NULL处理这些最容易出差异的场景。这一步不可跳过因为我见过太多纯数据校验通过、一上业务就开始报bug的项目。5.2 迁移后的性能调优实战数据迁完性能往往不理想原因是Oracle里原有的统计信息、执行计划习惯、索引设计在PG里全部失效。首先要做的第一件事就是收集统计信息让PG知道表有多大、数据分布什么样。ANALYZE VERBOSE; -- 或者针对大表 ANALYZE table_name;PG默认的autovacuum会开启自动analyze但刚迁移完的大量数据插入会让统计信息陈旧手工analyze一遍是必须的。然后检查所有表是否在迁移后正确创建了主键和索引。ora2pg默认会导出Oracle的索引定义但索引名在PG里可能因为长度超限被截断或改名你要核对这些索引是否真实存在。性能调优里一个容易忽略的点PG对多列条件的查询优化器处理方式和Oracle差异很大。Oracle里你习惯建复合索引的顺序是等值列在前、范围列在后PG也差不多但PG的索引扫描成本估算受统计信息影响更明显。迁移后最好把应用里的慢SQL收集起来针对每条慢SQL重新调整索引不要直接照搬Oracle时代的索引设计。我见过一个项目Oracle里五分钟的报表查询在PG里跑成了半小时后来只加了一个复合索引直接降回三分钟。大表的vacuum策略也要关注。刚迁移完的数据量巨大如果不做一次手动VACUUM (ANALYZE)表的bloat会很严重。更保险的做法是迁移完成后立即执行VACUUM FULL把表的物理空间重新整理一遍然后再打开autovacuum的常规节奏。VACUUM FULL会锁表这个操作必须在业务验证阶段的低峰期做不能等试运行期间做。5.3 切换窗口与回退预案迁移项目最紧张的时刻就是切换割接。停机窗口通常很短几小时甚至几十分钟所以需要在正式切换前把流程反复演练。我的做法是做一份切换SOP从停止应用写入、最后一次增量同步、切换数据库连接、启动应用、执行冒烟验证、到宣布切换成功或启动回退每一步都写明命令、预期结果、负责人员。最后一次增量同步是切换的核心。如果迁移前没有实施持续同步那停机窗口内的操作就是停应用、导增量数据、再校验、再起应用。如果数据库变更量大这个窗口根本不够用。所以有条件的话建议提前用逻辑复制或者基于时间戳的自研增量同步方案把Oracle到PG的增量数据在后台持续同步切换时只同步最后中断的那十几分钟数据压力小很多。回退预案是必须写但不能用的东西。如果切换后发现严重问题要回退最关键的是源Oracle环境不能动。很多团队在迁移时会顺手把Oracle资源释放掉结果回退无路。我的底线是Oracle库至少保留到PG试运行稳定两到四周之后再谈下线的事情。期间数据还可以通过反向同步或者重新导出的方式支持业务回切。6. 常见问题与排查技巧实录6.1 高频问题速查表这里把我在多个项目中反复遇到的ora2pg迁移问题整理成一张速查表希望能帮你少走弯路。现象可能原因解决办法ora2pg连接Oracle报ORA-12154ORACLE_DSN写错或LD_LIBRARY_PATH没配好检查连接串格式确认instantclient路径生效导出时报ORA-00942: table or view does not exist迁移账号没有对应表的权限给账号授权SELECT ANY TABLE或DBA角色生成的PG表结构没有主键Oracle主键依赖索引ora2pg默认可能不导出在配置里启用CONSTRAINTS相关选项或人工核对DDLCLOB数据迁移到PG是乱码字符集不一致或客户端NLS_LANG配置不对统一UTF8设置NLS_LANGAL32UTF8存储过程转换后报语法错误PL/SQL和PL/pgSQL差异未完全处理人工重写重点检查动态SQL、包、隐藏游标数据迁移速度极慢默认INSERT模式没有用COPY模式设置COPY_DATA1及COPY_MODE大表迁移中途失败网络超时或内存不足启用JOBS_NUM并行分批迁移增大PG的maintenance_work_mem中文数据导出后在PG端列数错位特殊分隔符冲突调整COPY的DELIMITER避免数据中含有该分隔符迁移后日期字段丢失时分秒Oracle DATE被映射为PG date类型手工改成timestamp迁移后null与空字符串行为不一致Oracle和PG对空串处理不同修改应用逻辑或迁移时统一转换这个问题表只是一个起点实际项目里还会有更奇葩的情况但排查思路是一致的先缩小范围到是结构问题还是数据问题再对照两边数据库的日志和ora2pg的日志文件定位到具体对象和SQL。6.2 我踩过的坑和独家心得第一个坑是我刚开始做迁移时踩的表名大小写问题。Oracle里如果建表语句没有加双引号所有对象名都会变成大写存储。PG恰恰相反不带引号的对象名会自动变成小写。ora2pg在导出时会把Oracle的大写对象名转换成PG里未加引号的形式看起来一切都正常。但应用里如果写了Emp这种带双引号的混合大小写表名在PG里就会因为大小写不匹配而查不到。碰上这种历史SQL债唯一的办法就是全量扫描应用代码里的表名引用统一规则。我后来都会在配置里加上MODIFY_NAMES选项让ora2pg自动做名称处理但还是要人工过一遍。第二个坑和空字符串有关。Oracle把空字符串当作NULL来处理但PG严格区分和NULL。很多老系统的表里存的是应用代码也是按判断的。迁移到PG后这些被原样保留但应用里的WHERE col IS NULL就再也查不到这些行了。这个坑在业务验证阶段才暴露排查成本特别高。我的建议是迁移前先和业务方确认历史数据中的含义如果业务语义上和NULL等价就统一转换成NULL如果不等价就要同步修改应用判断逻辑。第三个坑是ora2pg在导出超大对象时的内存占用。ora2pg是Perl单进程程序对数千万行的表进行COPY导出时内存占用可能冲到几个GB。我之前在8G内存的迁移服务器上跑一个大表直接把OOM Killer激怒了进程被杀。解决办法是设置COPY_FROM_ORACLE等参数让ora2pg使用Oracle的fetch分批机制同时降低DATA_LIMIT控制每次处理的行数。还可以把大表单独拎出来拆成多个小分片任务跑避免一个进程吃满所有内存。第四个坑是外键和触发器导致的导数据顺序问题。Oracle的外键约束在导数据时可能没有按顺序禁用导致合规性报错。ora2pg会把外键约束和索引生成在表结构文件中我踩过直接执行全部DDL文件后数据导入时因为外键顺序混乱而大量失败。解决方法是导入数据前先禁用约束导入完成后重新启用并验证。在PG里可以用SET session_replication_role replica暂时禁用外键数据导完再SET session_replication_role DEFAULT恢复这个技巧非常好用。6.3 迁移后的巡检清单迁移收尾不只是交一份报告我习惯用一张巡检清单把所有工作项过一遍避免遗漏。清单大致包括对象数比对、数据量比对、索引数量与去重检查、序列当前值核对、外键约束状态确认、存储过程和函数编译状态、触发器是否生效、字符集与客户端连接编码统一检查、应用连接池配置调整、慢SQL日志抽样分析、备份与恢复演练、监控指标接入。对象数比对很容易做Oracle和PG的数据字典里分别统计表、视图、序列、函数、存储过程、触发器的数量先看差值。数量一致不代表内容正确但数量不一致一定有问题。序列当前值核对往往被忽略如果Oracle的sequence已经跑到了100万而PG里迁移后的序列还在1应用插入下一行就可能主键冲突。ora2pg导出序列时会带上当前值但如果你手工重建过序列很容易丢失这个信息。最后我想讲一个实际体会迁移项目最容易出问题的地方其实不在工具而在应用层。ora2pg可以把表和存储过程搬过去但它无法感知应用代码里写死的Oracle方言、特殊函数、隐式转换、连接池参数。所以整个迁移项目一定要把应用开发团队的改造投入纳入排期而且要给足测试回归时间。我在正式切换前至少会让业务系统在PG环境上集成测试跑两周把能暴露的问题尽量暴露在割接之前。如果你现在正要开始一个Oracle到PostgreSQL的迁移我的建议很简单先把评估跑起来把报告当成项目风险清单来管理先拿一个非核心业务库练手跑通全流程之后再碰核心系统所有迁移操作的步骤和命令都沉淀成文档或者自动化脚本这样后面遇到同类项目才不会一遍遍从头踩坑。工具只是手段流程和经验才是迁移项目能顺利落地的基础。 ora2pg迁移实践：Oracle到PostgreSQL全流程指南
这两年数据库迁移的项目特别多Oracle迁PostgreSQL已经成为很多企业级技术栈调整的标配动作。ora2pg作为这个领域最主流的开源迁移工具几乎每个Oracle到PostgreSQL的迁移项目都会碰到它。这篇文章就把我在多个迁移项目里使用ora2pg的完整实践拆开来讲从工具选型、环境准备、参数配置到结构迁移、数据迁移、SQL改写和校验调优把能直接复用的经验和绕过的坑都整理出来。无论你是刚接手迁移任务的新手还是已经在迁移路上踩过坑的工程师这篇文章都能给你一份可落地的工作清单。1. 迁移项目整体设计与思路拆解1.1 为什么现在都在做Oracle到PostgreSQL的迁移先说个我自己的观察。前些年提到数据库选型很多企业闭眼就是Oracle稳定、生态成熟、DBA熟手多。但这些年情况变了Oracle的授权费用逐年走高尤其是核心系统要扩容的时候那个报价单拿出来能把预算吓一跳。PostgreSQL这边呢功能上越来越能打复杂查询、窗口函数、JSON处理、分区表全都齐了事务机制扎实开源社区活跃还不用被商业授权卡脖子。一进一出很多企业开始认真考虑把非核心甚至核心系统从Oracle迁到PostgreSQL。还有一个隐性驱动力是人才结构。现在年轻一点的开发者和DBA很多人的第一套数据库是MySQL或者PostgreSQL懂Oracle的反而越来越稀缺。企业里老DBA退休一个就少一个与其硬撑着Oracle的人力缺口不如把系统迁到更主流的开源生态里招人也好招内部培养也快。再加上容器化、云原生这套东西铺开之后PostgreSQL在云上的部署方案非常成熟很多团队本来就在用云数据库迁移反而是一个顺势而为的动作。不过我得说句实话Oracle迁PostgreSQL不是简单的“换个数据库连一下”它是一次涉及结构、数据、SQL、存储过程、应用连接方式的全链路改造。如果只是拿工具导个表结构、灌个数据后面应用跑起来各种SQL报错那才是真正的灾难。所以迁移项目第一步不是急着装工具而是把整体思路理清楚要迁哪些库、哪些对象、哪些应用允许停机多久谁来验证怎么回退。这些问题想不清楚后面每一步都可能返工。1.2 为什么选ora2pg而不是其他工具Oracle到PostgreSQL的迁移工具市面上有不少最常见的是ora2pg、pgloader、AWS DMS还有企业级商业工具如Ispirer、Full Convert以及一些基于自研脚本的“土办法”。我做一个横向对比方便你结合自己项目的情况判断。工具方案开源结构迁移能力数据迁移能力SQL改写能力适配复杂对象上手难度ora2pg是GPL强支持表、索引、约束、视图、函数、存储过程、包、触发器等强支持COPY批量、并行、断点续传强内置大量Oracle语法转PostgreSQL规则强支持物化视图、序列、同义词等中等配置项多pgloader是MIT弱主要用于表结构和数据强基于COPY的性能极高弱基本不改写SQL一般带类型映射但复杂对象支持差低AWS DMS否商业云服务中等结构迁移支持有限强支持持续同步弱复杂对象需手工处理一般中商业迁移工具否强强强强低但是贵我自己在项目里默认首选是ora2pg原因有三个。第一它是专门为“Oracle到PostgreSQL”这一条路线设计的连名字都是Oracle To PG的缩写对Oracle对象的覆盖度远超通用ETL工具。第二它不只是一个数据搬运工它能把Oracle的PL/SQL转换成PL/pgSQL还能导出评估报告提前告诉你这个库在PG里会遇到哪些兼容性麻烦。第三完全开源部署不依赖外部商业服务在内网环境里也能跑这对很多政企和金融客户来说是硬性要求。当然pgloader在单纯导数据的时候性能非常出色如果你只需要把数据搬过去、结构在PG里手工建那pgloader也挺好。但真实项目里一个Oracle库往往带几十上百个存储过程、视图、序列、触发器和各种约束这些用pgloader基本搞不定。所以我的推荐是结构复杂、对象多、PL/SQL重用ora2pg结构简单、只搬数据、时间紧可以考虑pgloader。大部分场景下ora2pg是真命天子。1.3 迁移项目的通用流程与阶段划分一个完整的迁移项目我习惯切成六个阶段评估、环境准备、结构迁移、数据迁移、应用改造与验证、切换割接。这六个阶段不是线性的实际执行中会有很多来回但阶段划分一定要清晰否则进度没法管理。评估阶段的核心不是“能不能迁”而是“迁过去要改多少东西”。用ora2pg的评估模式跑一遍得到兼容性报告看看哪些对象能自动转换、哪些需要人工改写、哪些根本无法转换。这个阶段决定了整个项目的工作量。我见过一个项目评估报告显示400个存储过程只有60个能自动转换剩下340个全要手工改工作量立刻翻了三倍。所以评估报告一定是一开始就要出的东西而不是迁完了才看。环境准备阶段要做的事很杂装好PostgreSQL和ora2pg建好数据库、用户、表空间设置好源端Oracle的访问权限确认字符集统一方案还有网络、磁盘、内存这些基础资源的规划。结构迁移阶段是用ora2pg导出表结构、序列、函数、存储过程、视图、触发器等然后在PG里执行重建。数据迁移阶段用COPY模式灌数据大表考虑并行和分批。应用改造与验证阶段是工作量最大的部分要把应用里的SQL全部回归一遍结合ora2pg的改写结果修正语法和方言差异。切换割接阶段就是停机窗口内完成最后一次增量同步、应用切库、回退预案试跑。这套流程看起来平淡无奇但每一阶段都有技术细节和坑接下来我从第二阶段开始逐个拆解。2. 迁移前准备工作与环境搭建2.1 版本选择与兼容性评估ora2pg的版本迭代比较快我在项目里常用的是23.x和24.x系列这两个版本对Oracle 19c和PostgreSQL 14到17的配合都比较好。理论上ora2pg支持Oracle 9i到19c、21c以及PostgreSQL 9.4到17但老版本Oracle的字典视图差异可能会导致部分对象识别不全。我的建议是源端Oracle尽量在11g以上目标端PostgreSQL直接用16或者17。PostgreSQL 16和17在并行查询、逻辑复制、vacuum性能上的改进非常明显迁移完后的运维负担小很多。版本选择上还有一个容易忽略的点ora2pg是用Perl写的它对Perl版本和依赖模块有要求。Linux上最好用系统自带的Perl 5.26以上再用包管理器安装DBD::Oracle和DBD::Pg。DBD::Oracle这个模块需要Oracle Instant Client的配合所以你的迁移服务器上得装一个Oracle客户端环境。这一步很多人会在配置Oracle客户端时卡住实际上下载对应架构的instantclient-basic和instantclient-sdk包解压配置好LD_LIBRARY_PATH就行了不需要安装完整的Oracle软件。如果你不想在迁移服务器上装Oracle客户端还有一个替代思路用Docker起一个装了ora2pg和Oracle客户端的容器把迁移工具链封装好。我在自动化迁移平台里就是这么做的既避免了污染宿主机环境也方便多项目复用。不过容器方案要特别注意网络能连通源库和目标库很多内网环境的容器网络策略比宿主机严格得多跑不通的情况我遇到不止一次。2.2 ora2pg安装配置实战我以Linux环境为例给你一个完整的安装步骤。首先安装操作系统层面的依赖Debian/Ubuntu系用aptCentOS/RHEL系用yum或dnf。# Ubuntu/Debian apt-get update apt-get install -y perl cpanminus libdbi-perl libdbd-pg-perl build-essential unzip # CentOS/RHEL yum install -y perl perl-CPAN perl-DBI perl-DBD-Pg gcc make unzip然后安装Oracle Instant Client。这里注意Instant Client的版本要和你源库Oracle大版本匹配比如源库是19c就下载19.x的instantclient-basic和instantclient-sdk。解压后放到一个固定目录比如/opt/oracle/instantclient_19_19然后设置环境变量。export ORACLE_HOME/opt/oracle/instantclient_19_19 export LD_LIBRARY_PATH$ORACLE_HOME:$LD_LIBRARY_PATH export PATH$PATH:$ORACLE_HOME接下来安装Perl的Oracle驱动模块DBD::Oracle。先下载对应版本的源码包或者直接用cpanm安装。cpanm --force DBD::Oracle这里加--force是因为DBD::Oracle在某些Perl版本下编译告警比较多不加的话很可能中途失败。装完验证一下perl -e use DBD::Oracle; print $DBD::Oracle::VERSION最后安装ora2pg本体。从GitHub的darold/ora2pg仓库下载源码包解压后执行标准的perl安装流程。wget https://github.com/darold/ora2pg/archive/refs/tags/v24.5.tar.gz tar zxvf v24.5.tar.gz cd ora2pg-24.5 perl Makefile.PL make make install安装完成后验证ora2pg --version至于Windows环境ora2pg也支持但配置过程更折腾建议你在Windows上做评估分析可以大规模迁移还是放在Linux服务器上跑性能和稳定性都更好。Windows下要安装Strawberry Perl再装DBD::Pg和DBD::Oracle难度主要在Oracle客户端的DLL依赖上新手不建议在Windows上抗这个罪。2.3 数据库侧准备工作迁移前的数据库侧准备很多人会漏掉结果跑到一半才回头补权限。源端Oracle这边用于迁移的账号需要能够读取数据字典和所有目标对象的定义建议授予DBA角色或者按需授予SELECT_CATALOG_ROLE、SELECT ANY DICTIONARY、SELECT ANY TABLE等权限。不要用SYS或者SYSTEM直接连DBA账号配合独立迁移专用账号是更安全的做法。目标端PostgreSQL这边需要提前建好数据库和用户。我的习惯是给迁移业务单独建一个schema不要什么都塞进public里后面管理起来会非常痛苦。字符集统一用UTF8除非你确认整个数据链路都是纯英文或者兼容性强。Oracle侧的字符集如果是ZHS16GBK或AL32UTF8迁移到PG的UTF8库时中文一般没有问题但是要注意那些在GBK下合法、在UTF8下非法的特殊字符后面数据校验阶段我会专门说。PG侧的关键参数也要提前调整特别是涉及到大批量数据写入的我在实际项目里会把maintenance_work_mem调到256MB以上max_wal_size调大到4GB以上checkpoint_timeout适当延长。这些参数能让copy数据的速度提升一个档次否则默认配置下大量写入会频繁触发checkpoint性能明显下降。还有wal_level如果后面要用逻辑复制做增量同步提前要设成logical不然后面改参数还得重启数据库。另外说一个很多文档不会提的细节Oracle的数据字典里表和字段名默认是大写很多老业务建表时没用双引号所以全部是大写。ora2pg导出到PG时会做大小写转换处理但某些特殊对象名可能保留原样。这会导致应用里如果写了带引号的小写表名在PG里查不到。这个坑我后面在常见问题里会细讲准备阶段你只需要知道和应用团队提前约定好对象命名的规则能省很多扯皮。3. 核心迁移流程与参数配置细节3.1 ora2pg配置文件深度解读ora2pg的配置是通过一个ora2pg.conf文件来驱动的它默认会读取/etc/ora2pg/ora2pg.conf但我在实际项目里习惯每个迁移任务单独建一份配置文件避免多个任务的配置互相干扰。配置文件的格式是keyvalue注释以#开头结构很清晰你可以用命令生成一份默认配置作为参考。ora2pg --init_project my_migration # 或直接生成配置模板 ora2pg --print_config my_ora2pg.conf用到最多的几个配置项我整理成了表格你可以直接对着设置。配置项作用我的建议ORACLE_HOMEOracle客户端主目录指向Instant Client解压目录ORACLE_DSN源库连接信息格式dbi:Oracle:host;port;sid或service_name尽量用service_name连接别用SIDORACLE_USER / ORACLE_PWD源库用户名密码单独迁移账号不共享日常账号PG_DSN目标库连接信息格式dbi:Pg:host;port;dbname对应待迁移目标库PG_USER / PG_PWD目标库用户名密码具备建表、索引等DDL权限SCHEMA要迁移的Oracle schema逗号分隔一次迁一个schema避免依赖混乱TYPE导出类型TABLE/VIEW/SEQUENCE/FUNCTION/TRIGGER/PROCEDURE等按需组合用逗号分隔EXPORT_SCHEMA是否导出结构DDL1COPY_DATA是否迁移数据1表示数据随结构一起导出DATA_LIMIT每个表导出多少数据行数0表示全量DATA_TYPEPG的目标数据类型映射一般用默认特殊类型自定义映射PARSE_BAD_FILE解析失败的SQL记录文件路径设置后方便排查LOG_FILEora2pg运行日志路径必须设置否则出错难定位REPORT_FILE评估报告输出路径迁移前必跑重点解释一下ORACLE_DSN的写法。ora2pg用的是Perl DBI连接串格式和Oracle SQL*Plus里的连接串不一样不能拿tnsnames.ora的写法直接填。正确的是ORACLE_DSNdbi:Oracle:host192.168.1.10;port1521;service_nameORCLPDB如果你只有SID那就用sidORCL这种写法。连接串里不要带用户名密码那是由ORACLE_USER和ORACLE_PWD单独提供的。这个连接串是最容易配错的地方尤其是PDB模式的Oracle 12c以上很多老DBA还在用SID连接结果连到了CDB而不是PDB导出来的对象根本不是业务库的这种低级错误一旦发生整个评估报告就废了。3.2 迁移评估与导出准备拿到配置后第一件事不是马上导数据而是跑评估模式。ora2pg的评估报告能告诉你每个对象类型的数量、可自动转换的比例、需要手工处理的工作量是所有后续排期的依据。跑评估很简单ora2pg -c my_ora2pg.conf -t SHOW_REPORT如果只想快速看一个对象类型比如存储过程可以用SHOW_PROCEDURE或者SHOW_TABLE、SHOW_VIEW等。我前端时间做一个核心交易系统迁移的时候就是先用SHOW_REPORT发现这个库里有大概1200个存储过程其中ora2pg能自动转换的只有700多个剩下的要在PL/pgSQL里手工重写这样才逼着项目组提前多招了两个开发不然排期铁定崩。还有一种非常实用的评估手段是SHOW_COLUMN和SHOW_TABLE可以快速检查数据类型的映射情况。我习惯用下面这组命令逐个确认# 查看所有表及其行数 ora2pg -c my_ora2pg.conf -t SHOW_TABLE # 查看所有列的类型映射评估 ora2pg -c my_ora2pg.conf -t SHOW_COLUMN # 查看存储过程和函数的评估 ora2pg -c my_ora2pg.conf -t SHOW_PROCEDURE跑完评估后ora2pg会给出一个报告里面明确标注了哪些对象在PostgreSQL中有对应的自动转换策略哪些需要review哪些是完全无法转换的。这个报告要发给应用开发团队逐条分析因为有些“自动转换”出来的SQL可能在语义上变了不是简单搬过去就完事的。3.3 结构迁移与数据迁移的具体执行评估通过后可以开始正式导出。我一般把结构迁移和数据迁移分成两步执行这样中间可以人工介入检查DDL。第一步导结构把TYPE配置成需要迁移的对象类型组合COPY_DATA设为0。# 只导出结构 ora2pg -c my_ora2pg.conf -t TABLE --copy-data 0 ora2pg -c my_ora2pg.conf -t VIEW --copy-data 0 ora2pg -c my_ora2pg.conf -t SEQUENCE --copy-data 0 ora2pg -c my_ora2pg.conf -t TRIGGER --copy-data 0 ora2pg -c my_ora2pg.conf -t FUNCTION --copy-data 0 ora2pg -c my_ora2pg.conf -t PROCEDURE --copy-data 0每个命令会生成对应的SQL文件比如TABLE会生成table.sqlPROCEDURE会生成procedure.sql。这里有个重要细节ora2pg生成的SQL文件中外键约束默认可能是追加在表定义之后的你要自己在PG里按顺序执行先建表再建序列再灌数据最后加索引和外键约束。如果一上来就把整个DDL文件全部执行外键和索引可能因为表数据还没迁移就报错或者导致后面数据灌入性能大幅下降。正确顺序是建表、建序列、导数据、建索引、建约束、建视图、建触发器、建函数存储过程。第二步导数据把COPY_DATA打开通常用COPY模式而不是INSERT模式速度能差出一个数量级。一个千万级的表INSERT模式可能要跑半小时COPY模式可能几分钟就结束了。# 导出所有表的数据 ora2pg -c my_ora2pg.conf -t TABLE --copy-data 1 -o data.sqlora2pg的数据导出默认会写到文件里然后在PG端执行文件来完成导入。如果想直接从Oracle读到PG不走中间文件可以使用--direct模式但这个模式对两边数据库的网络延迟比较敏感内网环境下问题不大跨机房容易超时。我为了稳妥还是习惯先落文件再导入这样数据文件还能留档后面出问题可以重新导入不用再连源库拉一遍。大数据量的表建议拆开来单独导。先看评估报告里哪些表超过百万行单独为这些大表配置导出参数比如再加并行度参数JOBS_NUM让多张表并行导出。我做过一个表有2亿行单线程COPY跑了快40分钟拆成4个并行后总耗时降到12分钟收益非常明显。4. 从Oracle语法到PostgreSQL语法的改造实践4.1 数据类型映射与处理数据迁移过程中最基础但也最关键的环节是数据类型的映射。ora2pg内置了一张Oracle到PostgreSQL的类型映射表默认规则大致如下。Oracle类型PostgreSQL类型注意事项NUMBER(p,s)numeric(p,s)精度超过18时用numeric否则也可用bigintNUMBER(1)smallint实际是布尔语义的字段要注意应用代码NUMBERnumeric无限精度但性能比integer差能定长度尽量定VARCHAR2(n)varchar(n)Oracle中VARCHAR2(10)按字节计PG按字符计中文字段要小心CHAR(n)char(n)PG的char会补空格和Oracle行为基本一致DATEtimestamp(0)Oracle的DATE包含时分秒PG的date只有日期必须映射成timestampTIMESTAMPtimestamp无时区PG默认timestamp是without time zoneTIMESTAMP WITH TIME ZONEtimestamptz时区语义注意转换CLOBtext无长度限制BLOBbytea二进制大对象RAW(n)bytea二进制流FLOATdouble precision浮点映射为双精度这里有一个高频头疼点Oracle的DATE类型。很多老业务把DATE当时间戳用里面存了完整的年月日时分秒到了PG里你要是直接映射成date类型秒和时分全被截断。ora2pg默认会映射成timestamp(0)这个方向是对的但你要是在PG里手工建表时用了date那数据就悄悄丢了。我每次做迁移时都会让SQL开发在应用里全面排查DATE字段的用法看看有没有人把它当字符串拼。还有一个我反复跟团队强调的点VARCHAR2的字节与字符问题。Oracle的VARCHAR2(n)里n的单位是字节比如VARCHAR2(20)可以存20个英文字母但只能存6个中文汉字如果UTF8编码下3字节一字。PostgreSQL的varchar(n)里n是字符数同样声明varchar(20)能存20个中文。所以迁移时如果原表是VARCHAR2(20)直接映射成varchar(20)看起来没问题但实际存储能力变了。对既有数据通常没有影响因为你不需要更长但如果你是反向从PG往Oracle迁或者两边做双向同步这个语义差异会直接导致写入失败。正确的做法是按字段内容的最大字符数来重新设计目标列的长度不要无脑沿用原来的长度定义。4.2 存储过程、函数与触发器的改造存储过程和函数的迁移是整个项目里最耗人力的部分。ora2pg对PL/SQL到PL/pgSQL的转换已经内置了很多规则能处理基本的IF/ELSE、LOOP、CURSOR、EXCEPTION等结构但遇到复杂的包PACKAGE、动态SQL、隐式游标、%ROWTYPE、%TYPE以及很多Oracle特有的内置函数还是需要人工介入。先看最基本也最常见的差异点。Oracle的SELECT INTO在PG里写法和语义略有不同。Oracle中直接SELECT ... INTO变量 FROM dualPG里需要SELECT ... INTO变量或者更推荐用PERFORM或者RETURNING。其次Oracle的函数调用允许不带括号的函数名比如SYSDATE、USER在PG里很多要写成CURRENT_TIMESTAMP、CURRENT_USER或者NOALIAS之后才能宽松处理。再次NULL的判断和空字符串的处理Oracle认为空字符串就是NULLPG里空字符串和NULL是不同概念这对数据一致性影响极大很多迁移后应用行为变化都源于这里。还有一个大坑是序列。Oracle里的序列用法是seq_name.NEXTVAL和seq_name.CURRVALPG里对应的是nextval(seq_name)和currval(seq_name)。ora2pg会自动把Oracle的序列定义转换成PG的sequence并且把SQL里的NEXTVAL写法改写成nextval(...)。但是如果你在Oracle里是用触发器序列来实现自增PG这边更推荐直接用IDENTITY列或者SERIAL不然你还要在PG里保留一个触发器来调nextval纯属给自己加戏。迁移时我建议把“序列加触发器产生主键”的模式直接改良成PG的GENERATED BY DEFAULT AS IDENTITY这一步顺手做了后面应用插入数据时和Oracle行为基本一致。包PACKAGE是另一个棘手对象。Oracle的包把一组函数、存储过程和全局变量封装在一起PG没有直接对应的包结构。ora2pg会把包里的函数和过程拆成独立的顶层函数同时把包里的全局变量处理成特殊的配置表或者会话变量。这个拆分过程在实际项目中几乎不可能一次成功主要问题在于包内函数互相调用时包名被剥掉后函数名可能冲突或者私有函数被暴露出来。我的经验是迁移后让开发把包内部调用关系重新梳理一遍转成PG的schema组织方式每个schema对应一个业务模块而不是硬凑一个和Oracle包一一对应的结构。触发器迁移也要重视。Oracle触发器语法里大量使用:NEW和:OLD伪记录PG里对应的是NEW和OLD少了冒号。Oracle里BEFORE INSERT触发器可以修改:NEW的值来改变最终插入的数据PG里也有类似能力但语法细节不同。还有触发器函数在Oracle里是独立于表的PG里必须先写一个返回TRIGGER的函数再CREATE TRIGGER绑定到表上这一步ora2pg可以自动生成但生成的函数名可读性不好后续维护起来很痛苦我一般会让人工过一遍命名规则。4.3 分页、函数与特殊SQL改写Oracle分页用的是ROWNUM这是大部分开发人员首先遇到的语法迁移难点。经典的Oracle分页写法SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY hiredate DESC ) t WHERE ROWNUM 50 ) WHERE rn 40;PG里的等价写法就简单得多SELECT * FROM emp ORDER BY hiredate DESC OFFSET 40 LIMIT 10;ora2pg能识别简单的ROWNUM分页并改写但遇到ROWNUM和其他条件混合、或者ROWNUM用在UPDATE场景就很容易漏网。比如Oracle里UPDATE ... WHERE ROWNUM 1这种写法PG里没有直接等价物需要用子查询或者CTE先取主键再回表更新。这类SQL在评估报告里通常会被标记为无法转换或需要review你要专门安排一批人工排查。常用的Oracle函数到PG的替换关系我也整理了一份速查表Oracle函数PostgreSQL等价备注NVL(a, b)COALESCE(a, b) 或 ISNULL语义等价DECODE(a, b, c, d)CASE WHEN ab THEN c ELSE d END也可用CASE表达式SYSDATECURRENT_TIMESTAMP / now()注意隐式转换TO_CHAR(date, YYYY-MM-DD HH24:MI:SS)TO_CHAR(date, YYYY-MM-DD HH24:MI:SS)格式基本一致但部分格式符不同ADD_MONTHS(date, n)date (n * interval 1 month)语义有细微差异月底边界要小心TRUNC(date)date_trunc(day, date)语义有差异LISTAGG(col, ,)STRING_AGG(col, ,)PG的STRING_AGG更强WM_CONCATSTRING_AGG(DISTINCT col, ,)WM_CONCAT是Oracle隐藏函数官方不推荐ROWNUMLIMIT/OFFSET需要改写CONNECT BY PRIORWITH RECURSIVE层级查询改写最头疼连接查询方面Oracle的()外连接写法必须改写成标准的LEFT/RIGHT JOIN。ora2pg能处理一部分但老SQL里()写多了容易出现ambiguity我强烈建议在迁移前做一个静态扫描把所有()写法先统一改写成ANSI JOIN再进行迁移。层级查询CONNECT BY在PG里用WITH RECURSIVE改写这不是简单的关键字替换涉及递归逻辑的重构遇到过特别复杂的树形查询我和开发一起在会议室白板上演算了两个小时才算清楚。PG还有一种Oracle没有的写法ON CONFLICT。这在做增量更新时特别好用能实现UPSERT语义。从Oracle迁移到PG的应用很多原本要写MERGE INTO的地方在PG里可以用INSERT ... ON CONFLICT DO UPDATE大大简化。这是迁移后顺手做的优化不算必须但值得做。5. 数据一致性校验与性能调优5.1 校验方案设计与实施数据搬过去不等于数据是对的。我做过不止一个项目表面上看表行数都对结果字段层面一堆差异。所以校验必须分三个层次行数校验、字段级抽样校验、业务逻辑验证。行数校验最简单两边分别执行count(*)对比结果。大表count很慢Oracle可以用all_tables里的num_rows做预评估但那个不精确最终还是要跑真实count。-- Oracle SELECT table_name, num_rows FROM all_tables WHERE owner SCOTT; -- PostgreSQL SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname public;这两个值可以作为快速筛查但不能作为最终结论。真正确认一致性我会用哈希聚合的方式-- Oracle SELECT SUM(dbms_crypto.hash(rawtohex(column_data), 3)) FROM table_name; -- PostgreSQL SELECT SUM(hashtextextended(column_data::text, 0)) FROM table_name;这里我只是举个例子实际操作中你可以将所有关键字段拼成一个字符串再做哈希聚合两边对比聚合值。这个方案能快速发现某一列的数据是否有差异但不能定位到具体行。定位具体行需要再缩小范围做分块或者抽样对比。字段级抽样校验是更细的工作。我会写一个脚本从两张表各自按主键抽样10000行逐字段比对。还有一种更可靠但工程量大的方式是按主键范围分批把两边数据都导出成CSV然后用diff工具对比。这个方案在数据量不大时最稳但一旦表上亿就不推荐了还是用哈希分块处理更实际。最后一步是业务逻辑验证。让测试团队拿真实的查询脚本和应用功能在PG环境上跑一遍重点关注金额精度、日期显示、字符串排序、NULL处理这些最容易出差异的场景。这一步不可跳过因为我见过太多纯数据校验通过、一上业务就开始报bug的项目。5.2 迁移后的性能调优实战数据迁完性能往往不理想原因是Oracle里原有的统计信息、执行计划习惯、索引设计在PG里全部失效。首先要做的第一件事就是收集统计信息让PG知道表有多大、数据分布什么样。ANALYZE VERBOSE; -- 或者针对大表 ANALYZE table_name;PG默认的autovacuum会开启自动analyze但刚迁移完的大量数据插入会让统计信息陈旧手工analyze一遍是必须的。然后检查所有表是否在迁移后正确创建了主键和索引。ora2pg默认会导出Oracle的索引定义但索引名在PG里可能因为长度超限被截断或改名你要核对这些索引是否真实存在。性能调优里一个容易忽略的点PG对多列条件的查询优化器处理方式和Oracle差异很大。Oracle里你习惯建复合索引的顺序是等值列在前、范围列在后PG也差不多但PG的索引扫描成本估算受统计信息影响更明显。迁移后最好把应用里的慢SQL收集起来针对每条慢SQL重新调整索引不要直接照搬Oracle时代的索引设计。我见过一个项目Oracle里五分钟的报表查询在PG里跑成了半小时后来只加了一个复合索引直接降回三分钟。大表的vacuum策略也要关注。刚迁移完的数据量巨大如果不做一次手动VACUUM (ANALYZE)表的bloat会很严重。更保险的做法是迁移完成后立即执行VACUUM FULL把表的物理空间重新整理一遍然后再打开autovacuum的常规节奏。VACUUM FULL会锁表这个操作必须在业务验证阶段的低峰期做不能等试运行期间做。5.3 切换窗口与回退预案迁移项目最紧张的时刻就是切换割接。停机窗口通常很短几小时甚至几十分钟所以需要在正式切换前把流程反复演练。我的做法是做一份切换SOP从停止应用写入、最后一次增量同步、切换数据库连接、启动应用、执行冒烟验证、到宣布切换成功或启动回退每一步都写明命令、预期结果、负责人员。最后一次增量同步是切换的核心。如果迁移前没有实施持续同步那停机窗口内的操作就是停应用、导增量数据、再校验、再起应用。如果数据库变更量大这个窗口根本不够用。所以有条件的话建议提前用逻辑复制或者基于时间戳的自研增量同步方案把Oracle到PG的增量数据在后台持续同步切换时只同步最后中断的那十几分钟数据压力小很多。回退预案是必须写但不能用的东西。如果切换后发现严重问题要回退最关键的是源Oracle环境不能动。很多团队在迁移时会顺手把Oracle资源释放掉结果回退无路。我的底线是Oracle库至少保留到PG试运行稳定两到四周之后再谈下线的事情。期间数据还可以通过反向同步或者重新导出的方式支持业务回切。6. 常见问题与排查技巧实录6.1 高频问题速查表这里把我在多个项目中反复遇到的ora2pg迁移问题整理成一张速查表希望能帮你少走弯路。现象可能原因解决办法ora2pg连接Oracle报ORA-12154ORACLE_DSN写错或LD_LIBRARY_PATH没配好检查连接串格式确认instantclient路径生效导出时报ORA-00942: table or view does not exist迁移账号没有对应表的权限给账号授权SELECT ANY TABLE或DBA角色生成的PG表结构没有主键Oracle主键依赖索引ora2pg默认可能不导出在配置里启用CONSTRAINTS相关选项或人工核对DDLCLOB数据迁移到PG是乱码字符集不一致或客户端NLS_LANG配置不对统一UTF8设置NLS_LANGAL32UTF8存储过程转换后报语法错误PL/SQL和PL/pgSQL差异未完全处理人工重写重点检查动态SQL、包、隐藏游标数据迁移速度极慢默认INSERT模式没有用COPY模式设置COPY_DATA1及COPY_MODE大表迁移中途失败网络超时或内存不足启用JOBS_NUM并行分批迁移增大PG的maintenance_work_mem中文数据导出后在PG端列数错位特殊分隔符冲突调整COPY的DELIMITER避免数据中含有该分隔符迁移后日期字段丢失时分秒Oracle DATE被映射为PG date类型手工改成timestamp迁移后null与空字符串行为不一致Oracle和PG对空串处理不同修改应用逻辑或迁移时统一转换这个问题表只是一个起点实际项目里还会有更奇葩的情况但排查思路是一致的先缩小范围到是结构问题还是数据问题再对照两边数据库的日志和ora2pg的日志文件定位到具体对象和SQL。6.2 我踩过的坑和独家心得第一个坑是我刚开始做迁移时踩的表名大小写问题。Oracle里如果建表语句没有加双引号所有对象名都会变成大写存储。PG恰恰相反不带引号的对象名会自动变成小写。ora2pg在导出时会把Oracle的大写对象名转换成PG里未加引号的形式看起来一切都正常。但应用里如果写了Emp这种带双引号的混合大小写表名在PG里就会因为大小写不匹配而查不到。碰上这种历史SQL债唯一的办法就是全量扫描应用代码里的表名引用统一规则。我后来都会在配置里加上MODIFY_NAMES选项让ora2pg自动做名称处理但还是要人工过一遍。第二个坑和空字符串有关。Oracle把空字符串当作NULL来处理但PG严格区分和NULL。很多老系统的表里存的是应用代码也是按判断的。迁移到PG后这些被原样保留但应用里的WHERE col IS NULL就再也查不到这些行了。这个坑在业务验证阶段才暴露排查成本特别高。我的建议是迁移前先和业务方确认历史数据中的含义如果业务语义上和NULL等价就统一转换成NULL如果不等价就要同步修改应用判断逻辑。第三个坑是ora2pg在导出超大对象时的内存占用。ora2pg是Perl单进程程序对数千万行的表进行COPY导出时内存占用可能冲到几个GB。我之前在8G内存的迁移服务器上跑一个大表直接把OOM Killer激怒了进程被杀。解决办法是设置COPY_FROM_ORACLE等参数让ora2pg使用Oracle的fetch分批机制同时降低DATA_LIMIT控制每次处理的行数。还可以把大表单独拎出来拆成多个小分片任务跑避免一个进程吃满所有内存。第四个坑是外键和触发器导致的导数据顺序问题。Oracle的外键约束在导数据时可能没有按顺序禁用导致合规性报错。ora2pg会把外键约束和索引生成在表结构文件中我踩过直接执行全部DDL文件后数据导入时因为外键顺序混乱而大量失败。解决方法是导入数据前先禁用约束导入完成后重新启用并验证。在PG里可以用SET session_replication_role replica暂时禁用外键数据导完再SET session_replication_role DEFAULT恢复这个技巧非常好用。6.3 迁移后的巡检清单迁移收尾不只是交一份报告我习惯用一张巡检清单把所有工作项过一遍避免遗漏。清单大致包括对象数比对、数据量比对、索引数量与去重检查、序列当前值核对、外键约束状态确认、存储过程和函数编译状态、触发器是否生效、字符集与客户端连接编码统一检查、应用连接池配置调整、慢SQL日志抽样分析、备份与恢复演练、监控指标接入。对象数比对很容易做Oracle和PG的数据字典里分别统计表、视图、序列、函数、存储过程、触发器的数量先看差值。数量一致不代表内容正确但数量不一致一定有问题。序列当前值核对往往被忽略如果Oracle的sequence已经跑到了100万而PG里迁移后的序列还在1应用插入下一行就可能主键冲突。ora2pg导出序列时会带上当前值但如果你手工重建过序列很容易丢失这个信息。最后我想讲一个实际体会迁移项目最容易出问题的地方其实不在工具而在应用层。ora2pg可以把表和存储过程搬过去但它无法感知应用代码里写死的Oracle方言、特殊函数、隐式转换、连接池参数。所以整个迁移项目一定要把应用开发团队的改造投入纳入排期而且要给足测试回归时间。我在正式切换前至少会让业务系统在PG环境上集成测试跑两周把能暴露的问题尽量暴露在割接之前。如果你现在正要开始一个Oracle到PostgreSQL的迁移我的建议很简单先把评估跑起来把报告当成项目风险清单来管理先拿一个非核心业务库练手跑通全流程之后再碰核心系统所有迁移操作的步骤和命令都沉淀成文档或者自动化脚本这样后面遇到同类项目才不会一遍遍从头踩坑。工具只是手段流程和经验才是迁移项目能顺利落地的基础。