Oracle迁移PostgreSQL之开发篇

保险技术黑板报 2021-09-13

2819

简述

因为各种原因有不少公司都有过数据库转换迁移的需求，如Access迁移到SQLServer， Oracle迁移到MySQL。但是因为应用系统的稳定性，数据库转换迁移对于大部分开发人员来说却并不常见，笔者有幸参与了一个Java应用系统数据库由Oracle迁移PostgreSQL的项目，便从开发角度整理了一篇迁移过程分享给大家。如有遗漏或错误欢迎评论区补充指正。

一、PostgreSQL简介

简介

PostgreSQL（PGSQL）是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。

优点

• 支持主流操作系统（Windows、Linux、Unix、Mac OS X、BSD）；

• 负载接近极限后，性能会保持在顶峰不会下降；

• 拥有丰富的数据类型（大量字典、数组、几何类型、bitmap等）；

• 拥有强悍的SQL编程能力（支持丰富的统计函数和统计语法、支持递归、内置正则等）；

• “无锁定”特性突出；

• 多版本并发控制；

• 数据库性能调优灵活（可使用函数和条件索引）；

• 拥有多种集群框架可选择（Plproxy、Slony、Stand-by等）；

• 支持异步、半同步、同步复制（基于Wal）；

对比

安装

PostgreSQL官网：https://www.postgresql.org

或直接访问下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

PostgreSQL数据库可视化操作工具：pgAdmin4，支持主流操作系统，如Linux、Windows、Mac OS X等。数据库自带的为网页版，也可以自行安装PC客户端。

pgAdmin4客户端下载地址：https://www.pgadmin.org/download

二、Oracle迁移PostgreSQL

迁移宗旨：将数据库从Oracle迁移至PostgreSQL数据库，需要考虑的有很多。但是有一点是不变的，即：要保证数据库迁移后，系统功能能够正常使用，且对业务逻辑尽可能少的修改，因为修改业务逻辑可能会出现意想不到的连锁问题。

前期系统梳理

因为Oralce和PostgreSQL语法的不同，所以我们需要调整建表语句脚本，包括索引、函数、存储过程等。但是在整理脚本前，需要先统计好系统中的表信息。

1.1 统计系统表及表功能

简述：统计系统的使用的表和表功能，主要是为了明确需要迁移的表，以及表所负责的功能（方便数据迁移时，确定数据导入的先后顺序）。好能够一起统计表使用到的序列和索引等，方便之后的迁移操作。部分系统的表，在测试环境和生产环境的字段可能有所不同，也需要统计进来。并注意表结构、约束条件、主外键设置均以生产为准

操作：提供系统使用的表清单、字段差异清单、表解耦清单、剔除系统未使用的表等。

注意：无难点，但耗费时间，需要足够耐心、细心。

1.2 公共表解耦

简述：若本系统有提供公共表给其他系统使用，则迁移到PostgreSQL数据库，那么其他使用该表的系统就无法正常使用；或本系统有使用其他系统的公共表，本系统迁移后则也无法正常使用。

操作：由表属主的系统提供对外的表接口，即由原来的直接查询其他系统的表，转为调用其他系统提供的接口来查询需要的数据；反之，若其他系统有用到本系统的表，改为调用本系统提供的接口完成业务操作。

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

1.3 编写执行脚本

简述：Oralce和PostgreSQL的语法不同，所以会先在本地自己搭建的数据库环境中做模拟测试，等脚本无误后才会在SIT环境执行。

操作：统计系统中的存储过程、函数、索引等信息，然后更改语法并写成脚本，最后在本地进行验证。

注意：PostgreSQL的语法与Oracle细节差异还是很多的。

特别分享

常规的SQL脚本改造可以分成两部分：SQL语法和函数。下面是以学生课程表为模型分享一些常用的例子：

-- 不仅可以指定WHERE条件中的子类类型，还可以指定SELECT查询字段的类型
SELECT T.STUDENT_ID::VARCHAR
  FROM T_STUDENT T
 WHERE T.STUDENT_ID <> 1 ]]>
   AND T.STATUS = 1
   AND T.CLASS_CODE::VARCHAR    
    <iterate prepend="in" conjunction="," open="(" close=")">
        #classCodeList[]#
    </iterate>
-- INSERT语句用的最为频繁
INSERT INTO T_STUDENT ( CREATED_BY,
                        CREATED_DATE,
                        UPDATED_BY,
                        WEEK)
VALUES( #createdBy:VARCHAR#,
        now(),
        #updatedBy:VARCHAR#,
        to_char(#calendarDate#::timestamp,'ww'));
-- 特殊的时间加减法运算
select CURRENT_TIMESTAMP::TIMESTAMP + INTERVAL '5 day';
-- 分页查询语法
SELECT * FROM T_STUDENT LIMIT 20 OFFSET 0;
-- 左右连接查询 需要使用LEFT OUTER JOIN 或 RIGHT OUTER JOIN
SELECT * FROM T_STUDENT a LEFT OUTER JOIN T_CLASS_INFO b WHERE a.CLASS_ID=b.CLASS_ID;
复制

本地代码调整

2.1 建立本地数据库

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

特别分享

PostgreSQL重置密码步骤：

1. 停服务（pg_ctl stop -D

D:/app/pgsql/12/data -m immediate）

2. 修改 pg_hba.conf 修改加密方法

3. sql alert 修改 postgre 的密码（alter user postgres with password 'Password@123';）

4. pg_hba.conf 加密方法改回去

5. 重新登录

2.2 应用系统集成PostgreSQL

数据库依赖：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.18</version>
 </dependency>
复制

系统中数据源配置参考：

dataSourceClassName=驱动
url=PostgreSQL数据库地址
username=用户名
password=密码
minimumIdle=空闲连接最小数
maximumPoolSize=最大允许线程数
maxLifetime=存活时间
leakDetectionThreshold=泄露检测
connectionTimeout=连接超时时间
复制