作者 Iris （PG深度爱好者）
一、项目描述
前段时间刚完成一个应用ORACLE改造的项目，数据体量比较大，业务改造的周期计划比较短，再加上自研分布式MYSQL对事务、存储过程等限制，所以应用方放弃了改造分布式数据库MYSQL的想法。很多研发人员对PG的了解不深，产生一个误区，认为PG近乎等于ORACLE，并无多大改造体量。然后现实很快就被打脸了。现实就是，该做的改造工作一个都不能少，毕竟是异构数据库，字段和语法的差异都是项目改造的基础。
二、需求分析
为了完成Oracle到PG的改造工作，将改造的工作分为以下两类：
-
数据迁移：表、视图、数据、序列、存过（及函数）、JOB定时任务等，按照需要迁移到Postgre中。
-
程序改造：后台所有的微服务工程，需要修改数据库连接配置及所有Mybatis的SQL语句需要改造适应Postgre
三、改造说明
Oracle和Postgre数据库的差异方面很多，现在列举此项目改造中常用到的一些差异如下：
3.1 数据类型差异
|
Oracle |
Postgre |
|
Varchar2 |
Varchar |
|
Date |
Date/time/timestamp |
|
Clob |
Text |
|
Blob |
Bytea |
|
Number |
Smallint/integer/bigint/numeric/real/double precision |
3.2 函数或语法差异
|
项目 |
Oracle |
Postgre |
|
字符串连接 |
|| |
||或者使用concat() |
|
'A'||NULL |
'A' |
NULL |
|
null和’’ |
没区别 |
有区别 |
|
Trunc(时间) |
Trunc(date) |
Date_trunc(date) |
|
当前系统时间 |
Sysdate |
Now()或localtimestamp |
|
TO_CHAR,TO_NUMBER,TO_DATE |
自动格式转换 |
需指定格式 |
|
空值转换函数 |
NVL |
COALESCE |
|
转义函数 |
DECODE |
没有，只能CASE WHEN 实现 |
|
Outer join |
(+) |
Left(righ) join |
|
序列值获取 |
SEQ_NAME.NEXTVAL(新值) SEQ_NAME.CURRVAL(会话当前值) |
NEXTVAL('SEQ_NAME') (新值) CURRVAL('SEQ_NAME') (会话当前值) |
|
虚表DUAL |
DUAL |
自建视图dual |
|
函数继承或承载 |
不支持 |
支持 |
|
行号 |
ROWNUM |
没有，可以通过ROW_NUMBER() OVER ()代替 |
|
分页查询 |
ROWNUM BETWEEN 开始位置 AND 结束位置 |
LIMIT 数据量 OFFSET 开始位置 |
|
类型自动转换 |
支持 |
不支持，需要显示转换，或者添加CAST, |
|
子查询别名 |
可以不需要 |
必须指定别名 |
|
递归查询 |
start with … connect by… |
with recusive |
|
更新字段别名 |
更新字段可以使用表别名引用字段 |
更新字段时SET后面的字段不能出现别名 |
|
日期加减 |
天数直接用+-，月份用ADD_MONTHS(date,int) |
+ INTERVAL '1 YEAR 1 MONTH -1 DAY' |
|
别名 |
可以省略AS关键字 |
特殊关键字名字的别名必须加AS |
|
查找字符串位置 |
INSTR('12345', '3') |
POSITION('3' IN '12345') |
|
数据记录ID |
Rowid |
Ctid |
3.3 应用程序改造
所有微服务都需要进行改造，需要数据库连接修改及SQL配置文件的改造。
现有微服务工程如下：
|
工程 |
备注 |
|
config-client |
|
|
config-server |
|
|
eureka-server-1.5.2 |
|
|
fault-service |
|
|
ggzs-service |
对接公共助手 |
|
it.wisdom |
|
|
jeesite.api |
|
|
upedc-service |
|
|
wisdom-api-app-menu |
app菜单 |
|
wisdom-api-event |
|
|
wisdom-api-gateway |
网关 |
|
wisdom-auth-service |
认证服务 |
|
wisdom-bo-service |
|
|
wisdom-eareka-server |
eareka服务 |
|
wisdom-interface |
|
|
wisdom-order |
|
|
wisdom-report |
报表 |
|
wisdom-schedule |
调度任务 |
|
wisdom-service |
|
|
wisdom-storm-service |
|
|
wisdom-web-assessment-service |
员工评估等数据后台数据查询处理等 |
|
wisdom-web-bo-service |
端到端数据查询 |
|
wisdom-web-operator-service |
|
|
wisdom-web-operator-sms-service |
|
|
wisdom-web-operator-useranalysis-service |
|
|
wisdom-web-sys-service |
系统管理 |
|
wisdom-web-user-service |
用户管理 |
|
wisdom-web-vendor-assessment-service |
|
|
wisdom-web-ws-service |
对外提供WS接口 |
四、数据迁移
数据迁移工具用的是Ora2pg，数据迁移对象包含有323张表，32个序列，21个视图，8个函数，43个存储过程，以及16个JOB。具体的迁移过程略。可以参考Ora2pg官方帮助文档：http://ora2pg.darold.net/documentation.html
迁移问题处理
1、BLOB==>bytea 读出数据导出为文件时，不能直接做为BLOB数据类型导出
2、NUMBER==>NUMERIC 通过mybatis写入数据时，如果数据本身是VARCHAR，不能通过#{item.callfailnum,jdbcType=DECIMAL}转为DECIMAL写入数据，需要改成：#{item.callfailnum,jdbcType=VARCHAR}::DECIMAL
3、数据类型严格检查，ORACLE中，数据类型可以自动进行转换，而POSTGRE需要严格根据数据类型编写SQL，如果字段XXX为NUMBER类型，在ORACLE中可以写为XXX='3'，而在POSTGRE中必须写为XXX=3
4、navicat 11的版本过低，无法图形化导出脚本。建议提供navicat 15的版本。当然最好是使用PGADMIN工具。
文章评论