2020年7月31日星期五

Oracle数据库迁移至PostgreSQL数据库问题及解决

Oracle数据库迁移PostgreSQL数据库问题及解决


目录

  • 如何计划迁移数据库(现状及问题分析)
  • 统计系统表及表功能
  • 解耦公共表
  • 建立数据库
  • 迁移表结构
  • 导入表数据
  • 改SQL语法
  • 保证数据时效性和完整性
  • 其他(优化SQL等)

1.如何计划迁移数据库

将数据库从Oracle迁移至PostgreSQL数据库,需要考虑的有很多。
但是有一点是不变的,或者说是目的:要保证数据库迁移后,系统功能能够正常使用,或对业务逻辑尽可能少的修改(修改业务逻辑可能会出现意想不到的连锁问题)
那么,就需要想办法将原有的Oracle数据库整体结构,尽可能的在PostgreSQL上还原,这样才能保证不必大量调整业务逻辑。


明确目的之后,可以具体分析系统现状了:
该系统所使用的是Oracle数据库,但Orale数据库是一个总库,其他系统使用的表也在该库中,使用属主区分表。所以该系统除了自身使用的表之外,还使用了一部分公共表。
这样一来,在迁移数据库之前,除了统计系统本身的表外,还要进行公共表解耦
其次,如果要迁移到PostgreSQL数据库,那么建表语句就必须按照PostgreSQL的语法写,即调整建表脚本
除了建表脚本,原Oracle数据库还有大量的序列、索引、存储过程等,这些也需要移植过去
然后就是导入原有的表数据,进行本地系统功能的SQL语句调整。
最后需要解决的,就是在生产环境切换数据库,那么如何保证数据的完整性和时效性?


总结以上的问题要点:
1.迁移数据库之前,统计系统本身的表清单和表功能清单等;
2.迁移数据库之前,进行公共表解耦;
3.调整建表脚本;
4.移植序列、索引、存储过程等;
5.导入原有的表数据;
6.调整系统原本的SQL语句;
7.生产环境保证数据的完整性和时效性;
8.其他问题及解决;


2.统计系统表及表功能

统计系统的使用的表和表功能,主要是为了明确需要迁移的表,以及表所负责的功能(方便数据迁移时,确定数据导入的先后顺序)
这一步很简单,但是比较耗费时间,需要耐心和细心
最好能够一起统计表使用到的序列和索引等,方便之后的迁移操作。

3.解耦公共表

之所以需要解耦公共表,原因有二:
1.如果把使用到的公共表(表属主并不是该迁移的系统)迁移到PostgreSQL数据库,那么其他使用该表的系统就无法正常使用
2.公司规定,不是自己系统的表,一般是只进行读操作,不会进行写入或修改操作,更不会迁移其他系统的表;


根据以上原因,最终采用的方案是:
由表属主的系统,提供给我们这个迁移系统所需要的接口,我们由原来的直接查询其他系统的表,转为调用其他系统提供的接口来查询需要的数据;
这么做的好处就是,将原来的公共表与本系统进行解耦,同时当该系统进行数据库迁移后(其他系统仍是Oracle数据库),还能够查询到需要的数据,不会影响其他系统运行。


需要注意的是:
在进行表解耦之前,需要先统计该系统使用的外部表,包括本系统使用外部表的哪些关联字段、外部表在本系统中的作用等信息。
然后根据这些信息,与其他系统负责人沟通并开发接口、联调接口等。



4.建立数据库

在各环境的数据库正式迁移之前,可以先在本地的PostgreSQL数据库,建立一个新的系统数据库用于本地系统功能和业务测试。
当本地测试的差不多了,就可以把脚本提供给DBA,交由DBA去创建各环境的PostgreSQL数据库。
至于怎么在本地安装PostgreSQL数据库,这里就不赘述了。


5.迁移表结构(包括序列、索引等)

迁移表结构涉及的东西比较多,可以参考以下的迁移步骤:
1.编写新的CREATE TABLE脚本(按照PostgreSQL的语法);
2.编写新的INDEX、SEQUENCE等脚本(索引、序列的各种名称、参数都和Oracle的一致,尽量只调整语法差异)

5.1 CREATE TABLE脚本

编写CREATE TABLE脚本的时候,除了需要注意建表的语法之外,还有Oracle和PostgreSQL两种数据库不同字段类型的替换。

例如:

varchar类型和character varying类型
Oracle中,定长的char类型最大长度是2000,变长的varchar类型长度最大是2000、varchar2类型最大长度是4000。

字段类型长度是否可变最大长度补充
char2000存储长度为指定的长度,即char(20)存'abc',占20字节;比varchar占空间多,但效率稍高;
varchar2000存储长度为实际长度,即varchar(20)存'abc',占3字节;比char省空间,但效率稍低;存中文2字节,英文1字节;对空串不处理;
varchar24000Oracle特有,存储特点和varchar一致,但varchar2存中英文都是2字节;最大长度限制为varcahr的2倍;会将空串处理为null;

PostgreSQL中,定长的char类型和变长的varchar类型的最大长度都是1G(10485760),变长的text类型则没有长度限制。

字段类型长度是否可变最大长度补充
text无限制性能和varchar几乎相同,但存储结构不同;
character,char1G(10485760)定长;默认长度为1G;不足部分会补空白;
character varying,varchar1G(10485760)变长;默认长度为1;占用字节为实际存储长度;

在PostgreSQL中,text、char和varcahr的性能是没有区别的,大多数情况下使用text和varchar比较好;
character是全称,char是别名(简称);character varying是全称,varchar是别名(简称);Oracle中较长的字段,可以用PostgreSQL中的character varying替代,长度是没问题的


number类型和numeric类型
Oracle中,使用number类型来保存对精度要求高的数值,比如货币、金额等
对精度要求不高或没有要求的尽量不要用numeric类型,因为它的效率很低
字段类型范围说明例子
number(precision,scale)percision:1到38;scale:-78到124不指定percision,默认为38;number(8,3)

PostgreSQL中,与number类型对应的是numeric类型

字段类型范围说明例子
numeric(precision,scale)percision:最大131072个数字;scale:最大16383个数字变量;percision为总精度,scale为小数位位数;
若小数位超过指定位数,则进行四舍五入;
两参数同时省略时,可存任何不超上限精度的数字;
可存特殊值NaN(NaN与任何值都不等,包括自身);
numeric(8,3)
decimal(precision,scale)同上同上(只是语法支持,因为其底层就是numeric类型)decimal(8,3)或decimal(8,-2)

date类型和timestamp类型
Oracle中,使用date类型来保存时间数据,如创建时间、修改时间等
字段类型格式说明
date标准:DD-MON-YY存储的时间格式可定义,如"yyyy-mm-dd"或"yyyy-mm-dd hh24:mi:ss"

PostgreSQL中,与date类型对应的是timestamp类型

字段类型格式说明
timestamp存时间日期
time存时间
date存日期
timestampz存timestamp和时区
interval存一段时间

其他数据类型不再一一介绍,可以通过查阅PostgreSQl轻松学来获取相关知识。

建表语句例子:

-- 删减了一部分关键字段,只保留了基本的字段,同表一起创建的还有该表使用的索引CREATE TABLE cp_opr.user_role( id varchar(20) COLLATE pg."default" NOT NULL, role_id varchar(50) COLLATE pg."default" NOT NULL, department_chinese_name varchar(255) COLLATE pg."default" NOT NULL, user_name varchar(30) COLLATE pg."default" NOT NULL, department_code varchar(30) COLLATE pg."default", user_id varchar(30) COLLATE pg."default" NOT NULL, state varchar(2) COLLATE pg."default" NOT NULL, flag varchar(2) COLLATE pg."default" DEFAULT '1'::varchar, manager varchar(30) COLLATE pg."default", create_by varchar(100) COLLATE pg."default" DEFAULT 'complaint'::varchar, create_date timestamp(0) without time zone NOT NULL DEFAULT now(), update_by varchar(100) COLLATE pg."default", update_date timestamp(0) without time zone, CONSTRAINT user_role PRIMARY KEY (id));COMMENT ON TABLE cp_opr.user_role IS '用户角色表';COMMENT ON COLUMN cp_opr.user_role.id IS 'ID';COMMENT ON COLUMN cp_opr.user_role.role_id IS '角色ID';COMMENT ON COLUMN cp_opr.user_role.department_chinese_name IS '机构名称';COMMENT ON COLUMN cp_opr.user_role.user_name IS '用户名称';COMMENT ON COLUMN cp_opr.user_role.department_code IS '机构';COMMENT ON COLUMN cp_opr.user_role.user_id IS '用户ID';COMMENT ON COLUMN cp_opr.user_role.state IS '状态';COMMENT ON COLUMN cp_opr.user_role.flag IS '有效标志';COMMENT ON COLUMN cp_opr.user_role.manager IS '角色的管理员';	COMMENT ON COLUMN cp_opr.user_role.create_by IS '创建人';COMMENT ON COLUMN cp_opr.user_role.create_date IS '创建日期';COMMENT ON COLUMN cp_opr.user_role.update_by IS '修改人';COMMENT ON COLUMN cp_opr.user_role.update_date IS '修改日期';	-- 不能创建与表同名的索引-- CREATE INDEXCREATE INDEX index_role_dep_code ON cp_opr.user_role USING btree(department_code) ;CREATE INDEX index_role_id ON cp_opr.user_role USING btree(role_id) ;CREATE INDEX index_role_user_id ON cp_opr.user_role USING btree(user_id) ;

5.2 移植SEQUENCE序列

Oracle和PostgreSQl的序列创建方法大致上一样,但是会有一些细微的差别。

需要注意的是,新的序列在创建的时候,高速缓存值最好和原序列的一致
否则可能会发生序列跳跃(Oracle的最低可为0,PostgreSQl的最低为1)。

-- 以下是三个序列的创建语句,当前设置的初始值为1,当迁移后需要查询原序列的当前值,并修改现在的初始值1-- CREATE SEQUENCE create sequence cp_opr.sq_tauditdetail_idminvalue 1maxvalue 9223372036854775807start with 1increment by 1cache 10;-- CREATE SEQUENCE create sequence cp_opr.seq_complaint_email_log_idminvalue 1maxvalue 9223372036854775807start with 1increment by 1cache 10;-- CREATE SEQUENCE create sequence cp_opr.seq_t_complaint_mobilesms_logminvalue 1maxvalue 9223372036854775807start with 1increment by 1cache 10;

6.导入原有的表数据

导入Oracle数据库的历史数据,我们采用的方案是写批处理,然后跑批导数据
因为是本地和sit环境,所以数据量大的表可以适当删掉些无用的旧数据,以提升导入速度,方便测试,这里就不上批处理的代码了。

导入数据的时候,需要注意
数据导入的先后顺序,因为有的表数据通过逻辑外键关联,这些逻辑外键不允许为空。如果顺序乱了,则可能导入数据的时候报错

7.改SQL语法

这一步主要改的是系统中的SQL语句,因为PostgreSQl的语法和Oracle的差异还是不小的,所以必须要进行调整。
调整的内容可以分为2部分:
1.SQL语法();
2.SQL函数;

7.1 SQL语法

语法需要注意的地方,主要是字段类型的转换,这是因为PostgreSQL的语法要求的。

字段类型的指定(转换)需要用"::"符号,例如:

-- 不仅可以指定WHERE条件中的子类类型,还可以指定SELECT查询字段的类型SELECT T.BRANCH_ID::VARCHARFROM T_xxx TWHERE T.BRANCH_ID <> 1 ]]>	AND T.PARENT_ID = 1	AND T.BRANCH_ID::VARCHAR		<iterate prepend="in" conjunction="," open="(" close=")">		#deplist[]#	</iterate>-- INSERT语句用的最为频繁INSERT INTO T_C_C	(CREATED_BY,	CREATED_DATE,	UPDATED_BY,	UPDATED_DATE,	CALENDAR_ID,	CALENDAR_DATE,	IS_WORKING_DATE,	YEAR,	MONTH,	DAY,	QUARTER,	WEEK)VALUES(#createdBy:VARCHAR#,  now(),  #updatedBy:VARCHAR#,	now(),	#calendarId:VARCHAR#,	#calendarDate#::timestamp,	#isWorkingDate:VARCHAR#,	substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),1,4),	substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),6,2),	substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),8,2),	substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),5,1),	to_char(#calendarDate#::timestamp,'ww'));-- UPDATE语句中,不能够使用表别名-- 特殊的时间加减法运算select CURRENT_TIMESTAMP::TIMESTAMP + INTERVAL '5 day';-- 分页查询语法SELECT * FROM T_xxx LIMIT 20 OFFSET 0;-- 左右连接查询 需要使用LEFT OUTER JOIN 或 RIGHT OUTER JOINSELECT * FROM T_A a LEFT OUTER JOIN T_B b WHERE a.id=b.id;-- 子查询必须要有别名SELECT a.id,a.name,a.phone FROM(SELECT * FROM T_XXX WHERE ID in('1','2','5'))a;-- 插入空值NULL的时候,必须明确指定为NULL,而不能和Oracle一样是''

7.2 SQL函数

以下列举一部分,更多的函数可以自行百度

Oracle函数PostgreSQL函数
Decodecase xxx when
NvlCoalesce
InstrStrops
Nlssortconvert_to
Truncdate_trunc

8.保证数据时效性和完整性

8.1 数据完整性

数据的完整性,主要依靠的是:
1.一开始系统使用表统计,是否统计的足够完整;
2.导入数据的时候顺序;

测试导入后的数据是否完整也比较简单:
1.观察数据导入时是否有日志异常;
2.将业务从头到尾走一遍,看看数据是否查不到或者流程走不通;

8.2 数据时效性

这个主要涉及的是,当生产环境数据库由Oracle切换到PostgreSQL的时候,需要一定的时间去导入原有的数据。
那么导入数据的这段时间中,产生的新数据该如何导入新数据库?
如果直接导入,会不会与原有的数据关联不上?除此之外,还会有什么意想不到的问题?

比较遗憾的是,因为本次迁移的系统属于比较边缘的系统,所以最终采用的方案是
生产数据库切换的时候,将服务器关闭一段时间,阻止新数据的产生,直到旧数据迁移完毕再重启服务器。

除了本次迁移的系统,还有很多系统也需要迁移,如果那些系统迁移的时候遇到了这个问题,并有了更好的方案,再来更新。

9.其他

暂无,如果发现了再来补充~Oracle数据库迁移至PostgreSQL数据库问题及解决亚马逊之Facebook与YouTube双壁结合-走向海外推广之路亚马逊规则安全必备解读"Google+Facebook"双核流量引擎打造闭门集训营中国将自今日起暂停所有加拿大肉类对华出口?亚马逊常见问题 第一弹DHL推出"全球贸易晴雨表",评估预测全球贸易动态2017年端午节深圳欢乐谷会不会很多人?建议去吗?2017年端午深圳世界之窗有什么活动吗?2017年端午节去深圳西冲玩怎么样?去西冲玩什么?

没有评论:

发表评论