作者 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工具。