建议使用以下浏览器,以获得最佳体验。 IE 9.0+以上版本 Chrome 31+ 谷歌浏览器 Firefox 30+ 火狐浏览器
请选择 进入手机版 | 继续访问电脑版
设置昵称

在此一键设置昵称,即可参与社区互动!

确定
我再想想
选择版块
直达楼层
标签
您还可以添加5个标签
  • 没有搜索到和“关键字”相关的标签
  • 云产品
  • 解决方案
  • 技术领域
  • 通用技术
  • 平台功能
取消

采纳成功

您已采纳当前回复为最佳回复

鲲小鹏

发帖: 4粉丝: 1

发消息 + 关注

发表于2020年10月23日 10:40:21 779 4
直达本楼层的链接
楼主
显示全部楼层
[优秀实践] 【鲲鹏产业源头创新中心生态伙伴证通电子迁移实践分享】从Oracle迁移到PostgreSQL

Oracle迁移到PostgreSQL

1、迁移前的准备与考虑

近期,公司考虑将项目整体迁移到华为云鲲鹏服务器,由于华为云鲲鹏服务器基于ARM架构,目前只支持开源数据库和国产数据库,开源数据库如MySQLMariaDBPerconaPostgreSQLMongoDB等,国产数据库如GaussDB、达梦等,不支持Oracle,因此寻找替代的数据库便成了重中之重。

2、确定目标数据库

在做数据迁移之前,首先要确定的就是目标数据库,MySQLGuassDBPostgreSql这些数据库都在考虑范围内。但由于项目迁移时间规划为一个月,所以迁移难易度成为重要标准。最终,将目标锁定到了与Oracle数据库相似度较高的PostgreSQL

PostgreSQL 是最先进的开放源码的数据库系统, 它提供了多版本并行控制,支持几乎所有 SQL 构件(包括子查询,事务和用户定义类型和函数),并且可以获得非常广阔范围的(开发)语言绑定 (包括 CC++Javaperltcl,和python)

3、编译ARM版本PostgreSQL

3.1环境

架构:AArch64芯片架构

操作系统:Centos7.6

PostgreSQL版本:11.3

3.2准备工作

安装依赖包:

yum install gcc gcc-c++ readline-devel zlib-devel

3.3下载源码并安装

3.3.1下载源码

源码地址:https://ftp.postgresql.org/pub/source/v11.3/postgresql-11.3.tar.gz

3.3.2解压

tar -xzvf postgresql-11.3.tar.gz

3.3.3编译安装[l1] 

make && make install

3.3.4创建postgres用户

# adduser postgres 

# passwd postgres

3.3.5指定数据库文件存储到/data目录

# mkdir /data/pgsql/data

# chown postgres /data/pgsql/data

3.3.6初始化数据库

# su - postgres

$ /usr/local/pgsql/bin/initdb -D /data/pgsql/data

3.3.7启动数据库

pg_ctl -D /home/app/postgresql/data/ -l logfile start

4、数据库迁移

迁移表结构涉及的东西比较多,可以参考以下的迁移步骤:

1.编写新的CREATE TABLE脚本(按照PostgreSQL的语法);

2.编写新的INDEXSEQUENCE等脚本(索引、序列的各种名称、参数都和Oracle的一致,尽量只调整语法差异)。

4.1 CREATE TABLE脚本

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

基本数据类型差异:

Oracle

PostgreSQL

varchar2

varchar

number

numeric

date

timestamp/date/time

不支持boolean,可通过0/1代替

支持boolean

建表语句例子:

CREATE TABLE "public"."department" (

  "id" numeric(19) NOT NULL,

  "department_key" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,

  "department_value" varchar(200) COLLATE "pg_catalog"."default" NOT NULL,

  "description" varchar(200) COLLATE "pg_catalog"."default",

  "parent_departmentkey" varchar(20) COLLATE "pg_catalog"."default",

  "levels" varchar(200) COLLATE "pg_catalog"."default",

  "create_time" timestamp(6),

  "remarks" varchar(200) COLLATE "pg_catalog"."default",

  "sort" varchar(200) COLLATE "pg_catalog"."default",

  "type" varchar(200) COLLATE "pg_catalog"."default",

  "update_time" timestamp(6),

  "pid" numeric(19),

  "parentdept_id" numeric(19),

  CONSTRAINT "pk_department" PRIMARY KEY ("id"),

  CONSTRAINT "fkgxo19qoorkbn5xn5md80nkt8n" FOREIGN KEY ("pid") REFERENCES "public"."department" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION

);

 

ALTER TABLE "public"."department"  OWNER TO "postgres";

 

COMMENT ON COLUMN "public"."department"."department_key" IS '部门key';

 

COMMENT ON COLUMN "public"."department"."department_value" IS '部门值';

 

COMMENT ON COLUMN "public"."department"."description" IS '描述';

 

COMMENT ON COLUMN "public"."department"."parent_departmentkey" IS '父部门key';

 

COMMENT ON COLUMN "public"."department"."levels" IS '级别';

 

COMMENT ON COLUMN "public"."department"."create_time" IS '创建时间';

 

COMMENT ON COLUMN "public"."department"."remarks" IS '备注';

 

COMMENT ON COLUMN "public"."department"."sort" IS '排序';

 

COMMENT ON COLUMN "public"."department"."type" IS '类型';

 

COMMENT ON COLUMN "public"."department"."update_time" IS '修改时间';

 

COMMENT ON COLUMN "public"."department"."pid" IS 'id';

 

COMMENT ON COLUMN "public"."department"."parentdept_id" IS '父部门id';

 

COMMENT ON TABLE "public"."department" IS '部门表';

4.2 移植SEQUENCE序列

OraclePostgreSQl的序列创建方法大致上一样,但是会有一些细微的差别。需要注意的是,新的序列在创建的时候,高速缓存值最好和原序列的一致,否则可能会发生序列跳跃(Oracle的最低可为0PostgreSQL的最低为1)。

序列创建语句:

CREATE SEQUENCE "public"."attachment_seq"

INCREMENT 1

MINVALUE  1

MAXVALUE 9223372036854775807

START 1

CACHE 1;

 

SELECT setval('"public"."attachment_seq"', 1, false);

 

ALTER SEQUENCE "public"."attachment_seq" OWNER TO "postgres";

4.3导入原有的表数据

导入Oracle数据库的历史数据,采用的方案是写批处理,然后跑批导数据。

5SQL语句改写

PostgreSQL数据库虽然与Oracle数据库具有百分之九十以上的相似度,但PostgreSQl的语法和Oracle的还是有不小的差异,所以必须要进行调整。

调整的内容可以分为2部分:

1.SQL语法;

2.SQL函数。

函数及操作符:

项目

Oracle

PostgreSQL

当前时间

SYSDATE

可全部使用current_timestamp替换

序列

SEQNAME.NEXTVAL

NEXTVAL('SEQNAME')

固定值列

SELECT '1' AS COL1

SELECT CAST('1' AS TEXT) AS COL1

NVL

NVL函数

可用COALESCE函数替换

NVL2

NVL2函数

用标准的CASE WHEN THEN ELSE END语句替换

类型自动转换(例如:String类型转为数字类型)

Oracle某些情况下支持类型自动转换

会出现类型不匹配等错误,需要在Java或者sql中进行类型转换,使类型匹配

INSTR函数

instr('str1','str2')

strpos('str1','str2')

外连接

Oracle可简写为(+)

LEFT JOIN等语句替换

ROWNUM

ROWNUM关键字

两种情况:

1.限制结果集数量,用于翻页等:

SELECT * FROM T LIMIT 5 OFFSET 0

2.生成行号:

ROW_NUMBER() OVER()

DUAL

SELECT 1+1 FROM DUAL

SELECT 1+1或者CREATE VIEW dual AS SELECT current_timestamp

TO_CHAR

TO_CHAR(COL,FMT),格式化字符串可以为空

TO_CHAR(COL1,'FM999999')9的个数为字段长度

TO_NUMBER

TO_NUMBER(COL,FMT),格式化字符串可以为空

TO_NUMBER(COL1,'999999')

NULL''

''等同于NULL

NULL''不同

MONTHS_BETWEEN

MONTHS_BETWEEN(DATE,DATE)

创建函数来解决
  CREATE FUNCTION MONTH_BETWEEN(d1 timestamp,d2 timestamp) RETURNS NUMERIC AS

 'SELECT (extract(year from age(d1,d2))*12 + extract (month   from age(d1,d2)))::integer'

LANGUAGE 'sql';

子查询别名

如果FROM后只有一个子查询,该子查询可以没有别名

必须有别名

()名为关键字

Oracle中比如nametype这样的关键字可以直接作为列的别名,比如:select xx name from t

需要加as,比如select xx as name from t

修改表字段类型

1.如果字段无数据,可直接修改

2.如果有数据且新类型和原类型兼容,也可以直接修改

3.如果不兼容,可通过对原字段改名,然后增加新字段,再通过UPDATE语句对数据进行处理

1.如果新类型和原类型兼容,可直接修改

2.如果不兼容,需要使用USING关键字然后提供一个类型转换的表达式

6、保证数据完整性

数据的完整性,主要依靠的是:

1.一开始系统使用表统计,是否统计的足够完整;

2.导入数据的顺序。

 

测试导入后的数据是否完整也比较简单:

1.观察数据导入时是否有日志异常;

2.将业务从头到尾走一遍,看看数据是否查不到或者流程走不通。

7、总结

1.SQL语句的改造适配涉及点多,需要细致处理,不兼容的语法和函数要全面准确的替换。

2.数据的检验非常重要,校验时如果发现错误,要清理数据重新导入。

3.因为改造适配涉及点多,容易出错,因此可能涉及反复清理数据重新导入,需要有良好的心态。


 [l1]疑问:是否要进入目录编译,编译后二进制文件是否自动到了/usr/local/pgsql

举报
分享

分享文章到朋友圈

分享文章到微博

采纳成功

您已采纳当前回复为最佳回复

lw2019

发帖: 175粉丝: 72

级别 : 版主,版块专家

发消息 + 关注

发表于2020年10月23日 10:45:04
直达本楼层的链接
沙发
显示全部楼层

good

点赞 评论 引用 举报

采纳成功

您已采纳当前回复为最佳回复

Linton林小顿

发帖: 152粉丝: 6

发消息 + 关注

发表于2020年10月24日 23:54:39
直达本楼层的链接
板凳
显示全部楼层

nice

点赞 评论 引用 举报

采纳成功

您已采纳当前回复为最佳回复

火灵

发帖: 172粉丝: 11

发消息 + 关注

发表于2020年10月27日 21:28:40
直达本楼层的链接
地板
显示全部楼层

很赞!

点赞 评论 引用 举报

采纳成功

您已采纳当前回复为最佳回复

重新来过

发帖: 212粉丝: 12

发消息 + 关注

发表于2020年10月27日 23:08:58
直达本楼层的链接
5#
显示全部楼层

哇,Oracle迁移到PG,很难哦,这个居然有案例了,必须点赞

点赞 评论 引用 举报

游客

富文本
Markdown
您需要登录后才可以回帖 登录 | 立即注册

结贴

您对问题的回复是否满意?
满意度
非常满意 满意 一般 不满意
我要反馈
0/200