Oracle数据库迁移PostgreSQL实践

上一章介绍SQL/MED时提到PostgreSQL支持外部数据源，主要有文件、关系型数据库、非关系型数据库、大数据这几类。通过外部表PostgreSQL能够直接访问外部关系数据库，就像访问本地表- -样，当其他类型数据库迁移到PostgreSQL时这一特性非常给力! 本章将结合生产案例介绍Oracle数据库迁移到PostgreSQL实践。

本文的生产案例是一个Oracle数据库迁移PostgreSQL的项目，利用的核心技术为PostgreSQL的oracle_ fdw 外部表，迁移的Oracle数据库是一个小型数据库，大概30张表，存储过程4个，还包含少量序列和视图，单表数据量最大在30G左右，全库数据量在100GB以内，尽管这个数据库数据量不大，但承载着公司的重要业务，本章将围绕这个案例进行介绍，但不会介绍这个迁移项目的各个细节，主要介绍这个迁移项目的思路。虽然这个迁移项目是基于PostgreSQL9.2或9.3进行，但迁移思路和方法是一样的。

本章介绍的Oracle迁移PostgreSQL方法仅适用于中小型数据库(指全库数据库对象数量、全库数据量在一定范围内)，大型数据库迁移到PostgreSQL需在此方案的基础上进行完善，或考虑其他方案。

1.1 项目准备

对于一个生产系统替换数据库的代价是很大的，涉及大量的改造和测试工作，主要包括以下几个方面：

• :大部分数据库系统主要用到表、索引、序列、存储过程、触发器等对象，不同数据库的数据库对象定义不-样，这部分工作主要涉及表重定义、函数或存储过程代码改造等工作。
• :不同数据库的SQL语法有差异，尽管PostgreSQL的语法和Oracle很相似，在SQL语法和函数方面仍然存在一定差异，因此SQL和应用代码的改写不可避免。
• :当数据库对象迁移工作完成之后，需进行数据迁移测试，具体为迁移Oracle数据库数据到PostgreSQL,同时验证迁移后数据的准确性，例如迁移后数据量是否和Oracle库中的数据量-致?是否存在乱码?中文是否能正常显示?
• :前三步工作完成之后需要对新系统进行功能测试，这块工作主要由测试人员进行，开发人员、DBA配合。
• :前四步工作完成之后需要对新系统进行性能测试，包含业务代码的性能和数据库性能，这块工作主要由测试人员进行，开发人员、DBA配合，性能测试对系统的最高业务吞吐量进行模拟测试。
• :以上步骤完成之后，基本具备生产割接的条件，正式割接前建议至少做两次割接演练，重点记录数据迁移测试时间、停服务时间，以及验证整个迁移步骤是否有问题。

1.2 数据库对象迁移

Oracle 和 PostgreSQL 支持的数据库对象的类型和定义不一样，对于大多数数据库系统，常用的数据库对象为表、索引、序列、视图、函数、存储过程等。首先需要将Oracle 数据库的这些对象的定义迁移到PostgreSQL 数据库中；

1、数据库表定义差异

数据库表是主要的数据库对象，这部分改造工作涉及的脚本量较大，越是复杂的系统，涉及的数据库表越多，改造工作量越大；数据库表的改造主要是数据类型的适配，Oracle 与 PostgreSQL 常见数据类型适配表参考：

 

 

以上只是列出了常见的数据类型，关于Oracle其他数据类型读者可参考Oracle官方手册，关于PostgreSQL数据类型可参考本书第2章。

根据表17-1进行PostgreSQL建表脚本转换，这项工作通常由开发人员或开发DBA完成，管理DBA提供支持。

值得一提的是，Oracle将对象名称默认转换成大写，而PostgreSQL将对象名称转换成小写，PostgreSQL建表时表名不要用双引号，否则将带来使用、维护上的复杂度。

2、存储过程代码差异

有些应用系统会将部分业务用数据库的存储过程实现，尤其是大型数据库系统使用的存储过程可能多达上百个，大型系统迁移将涉及大量的改造工作。PostgreSQL 没有存储过程的概念，可以用函数来实现存储过程中的逻辑，PostgreSQL 函数的语法和Oracle有一定的差异，因此，Oracle的存储过程迁移到PostgreSQL中需要重写存储过程代码，所涉及的工作量还是相当大的。

1.3 应用代码改造

应用改造主要包括两部分，一部分是SQL代码改造，另一部分是应用代码改造，本小节主要介绍SQL代码改造，SQL代码改造主要包含SQL语法、函数两方面；

如果项目使用的SQL大部分为select、update、insert、delect 等标准SQL。SQL代码的改造工作量将大大降低，如果使用了Oracle的一些特殊功能或函数，相应的改造量将大些，以下从SQL语法、函数两方面的差异举例介绍；

1、SQL语法的差异

在标准SQL方面PostgreSQL与Oracle差异并不大，通常情况下大多数据系统不可避免会使用数据库的其他特性，这里仅列举典型的SQL语法差异例子；

Oracle 数据库中可以使用Rownum虚拟列限制返回的结果集记录数，例如限制仅返回一条记录；

PostgreSQL 可以使用 limit 关键字限制返回的记录数：

select * from pg_class limit1；

Oracle中的ROWNUM和PostgreSQL的LIMIT语法虽然在功能上都可以限制返回的结果集，但两者原理不同，ROWNUM是- -个虚拟列，而LIMIT不是虚拟列。

Oracle中的ROWNUM和PostgreSQL的LIMIT常用于分页查询的场景。

SQL方面差异的另- -个例子为序列使用上的差异。Oracle 与PostgreSQL都支持序列，两者使用上存在差异，例如Oracle使用以下SQL获取序列最近返回值:

select SEQ_1.CURRVAL FROM DUAL;

而 PostgreSQL 获取序列最近返回值的语法如下：

select currval（‘seq_1’）；

currval 显示序列最近返回值，nextval 表示获取序列下一个值，从以上代码看出序列的使用上PostgreSQL与Oracle 存在较大语法差异；

另外，Oracle 的子查询和PostgreSQL子查询语法不一样，Oracle 子查询可以不用别名：

select * from （select * from table_1）；

而 PostgreSQL 子查询必须使用别名：

select * from （select * from table_1）as b；

SQL 方面差异的另一个典型的例子为递归查询，Oracle 中常使用START WITH ... CONNECT BY 进行递归查询，而PostgreSQL递归查询的语法完全不一样；

2、函数的差异

原Oracle的SQL代码中会使用到Oracle数据库特有的函数，这些SQL转换成PostgreSQL时需要考虑函数的适配；

下面介绍一个字符串函数的适配，Oracle 使用INSTR 函数查找一个字符中另一个字符串的位置，如果找不到字符串返回为0，INSTR 有四个参数：

1. 1. ：指源字符串；
   2. ：指定要查找的字符或字符串；
   3. ：开始查找的位置，默认值为1，表示从源字字符第一个字符开始查找

1. 1. ：表示第几次出现的值，默认值为一，表示第一次出现时匹配

1.4 数据迁移测试

数据迁移主要有以下几种方式：

• :将Oracle库中表数据按照一定格式落地到文件，文件格式为PostgreSQL可识别的text格式或csv格式，这种方式需要将Oracle数据进行落地。
• :在PostgreSQL库中安装oracle_ fdw外部扩展，部署完成后，PostgreSQL可以访问远端的Oracle库中的数据，通过SQL将远端Oracle表数据插人本地PostgreSQL库，这种方式不需要将Oracle数据进行落地。
• :使用其他ETL数据抽取工具。

方式一由于需要先将Oracle库数据落地，操作起来较为复杂，这里主要介绍方式二：

 

1、pghost1 主机上安装Oracle 11g 客户端

在pghost1 主机上部署oracle_fdw 之前需要部署Oracle 客户端，这里使用RPM 包安装方式，安装 basic 、devel、sqlplus 包即可，所需的RPM 包下载地址为：

 

安装RPM包，如下所示：

rpm -ihv 。。。。。。.rpm

之后设置postgres操作系统用户环境变量，将Oracle相关环境变量加入，如下所示：

 

之后在pghost1 主机上测试是否可以连接远程主机pghost3上的oracle数据库；

2、pghost1 主机上安装oracle_fdw

在https：//api.pgxn.org/dist/oracle_fdw 中下载oracle_fdw 介质；

解压oracle_fdw 包，如下：

unzip oracle_fdw-2.0.0.zip

由于编译安装时需要用到PostgreSQL的pg_config工具，使用root用户编译安装前须载入postgres 操作系统用户的环境变量；

编译并安装 oracle_fdw，如下所示：

 

 

这时 oracle_fdw 已经安装成功，可查看$PGHOME/share/extension 目录进行确认，如下所示：

可见，$PGHOME/share/extension 目录下多了oracle_fdw 相关文件；

3、pghost1 的mydb 库中部署oracle_fdw

在pghost1 上的mydb中创建oracle_fdw 外部扩展：

psql mydb postgres

create extension oracle_fdw；

创建外部表需要使用超级用户权限，普通用户使用oracle_fdw 需要单独赋权，计划以普通用户pguser 使用oracle_fdw 外部扩展，给pguser 用户oracle_fdw 使用权限：

grant usage on foreign data wrapper oracle_fdw to pguser；

4、pghost3 的Oracle 库创建测试表和只读用户

假设community 为 oracle 库中的生产系统账号，以community 用户创建一张业务表：

 

5、pghost1 的 mydb 库中创建外部服务、映射用户

以pguser 用户登录mydb 库创建外部服务

在OPTIONS选项中的dbserver配置远程Oracle库的连接信息，以，上是在CREATESERVER命令中直接配置远程Oracle数据库的连接信息，也可以将远程Oracle库的连接信息配置到本地Oracle客户端的tnsnames.ora文件，之后OPTIONS中的dbserver属性直接配置服务名即可。

6、pghost1 的 mydb 库中创建外部表

1. : Oracle库中的表名，必须和Oracle库中数据字典表名一致，由于Oracle库数据字典中表名存储为大写，因此这里的表名需大写，否则查询外部表时可能报远程表不存在。

1. 远程Oracle库的模式名，设置成大写。

1. 设置外部表是否仅允许读操作，如果设置成yes，则INSERT、UPDATE、DELETE操作将不允许执行，默认为false。

7、将Oracle 库中的表数据迁移至PostgreSQL库中

之后在PostgreSQL 数据库中定义表，和 Oracle 库中表结构保持一致：

数量小的则可以直接通过insert 方式迁移数据

如果数据量较大，迁移时间较长，可考虑分多条INSERT 并行插入，例如根据主键选择迁移的记录，查询表数据进行验证：

 

1.5 功能测试和性能测试

数据库对象迁移、应用代码改造、数据迁移测试完成后，接下来进行功能测试和性能测试，功能测试指对新系统进行功能测试，性能测试指对系统进行压力测试，这两块工作主要为测试人员，开发人员、DBA配合，性能测试理论上可以测出系统的最高业务吞吐量；

功能测试过程中，出现SQL代码异常时DBA需要提供支撑，性能测试过程中，DBA要做好数据库的性能监控工作，查找系统是否存在慢SQL，是否能优化SQL提升系统的业务吞吐量。

对于核心业务涉及的SQL，DBA需要重点关注并优化。

1.6 生产割接

数据库对象迁移、应用代码改造、数据迁移测试、功能测试、性能测试完成之后，基本具备生产割接的条件了，正式割接前建议至少做两次割接演练，重点记录数据迁移测试时间、停服务时间，以及验证整个迁移步骤是否有问题。

如果停服务时间太长，业务方可能不接受，这时DBA要考虑如何减少停服务期间的数据迁移时间，例如，历史数据是否可以提前迁移?

在之前做的Oracle迁移PostgreSQL项目中，由于全库数据加索引总量在100GB以内，全量迁移停服务时间控制在两小时以内，业务方可接受，因此生产割接采取的是全量迁移方式。如果业务对停服务时间非常敏感，这时要考虑最小化数据迁移方案或增量数据迁移方案；

1.7 oracle_fdw 部署过程中的常见错误

常见错误一：创建oracle_fdw 扩展时报相关.so文件找不到

 

常见错误二：外部表可正常创建，但查询外部表时报错

 

常见错误三：查询外部表时报远程Oracle表不存在