从Oracle到PG 该做的改造工作一个都不能少!

2020年7月18日 316点热度 0人点赞 0条评论

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

图片

21090从Oracle到PG 该做的改造工作一个都不能少!

root

这个人很懒,什么都没留下

文章评论