如何从 Oracle 迁移到 PostgreSQL

本白皮书介绍了 Intelligent Converters 为一家大型货运公司制作的数据库从 Oracle 迁移到 PostgreSQL 的经验。迁移的主要原因是为了降低数据库管理系统的成本，并转向具有足够扩展性、安全性和定制能力的开源系统。

以下是源数据库的简要说明：

• 数据库服务器 Oracle 12g

• 数据库包含 190 个表，50 GB 数据

• 存储过程、函数和触发器中的 120000 行 PL/SQL

  
  

项目工期为 2 个月：半个月的评估和规划，一个月的迁移和半个月的测试。迁移涉及 3 名专家：2 名开发人员和 1 名数据库管理员。

Oracle 到 PostgreSQL 数据库的迁移包括六个基本阶段：

1. 查找所有Oracle特有的在源数据库中存储和处理数据的方法和使用范围（调查和计划阶段）
2. 选择合适的模式迁移工具并实施
3. 选择最合适的数据迁移方法以减少 Oracle 系统停机时间
4. 运行数据迁移处理 PostgreSQL DBMS 所需的所有转换
5. 将所有 PL/SQL 代码转换为 PostgreSQL 格式（使用部分自动机和手动后处理工具）
6. 运行性能和功能测试，微调结果数据库

表定义的迁移

Oracle 中的某些数据类型在 PostgreSQL 中没有直接等效项。其中一种类型是包含日期和时间部分的 DATE。

PostgreSQL 支持：

• 日期——没有时间部分的纯日期
• time – 没有日期部分的纯时间，带有时区可选规范
• timestamp – 带时区可选规范的日期和时间

将 Oracle 日期映射到 PostgreSQL 有两种选择：使用 TIMESTAMP 或设置orafce扩展以使用 Oracle 风格的日期类型 oracle.date。

空间类型也需要特别注意。 Oracle 具有内置类型 SDO_GEOMETRY，而 PostgreSQL 需要安装 PostGIS 才能使用空间数据类型。

下表说明了 Oracle 到 PostgreSQL 的安全类型映射：

对于数字类型，了解在数据库中的使用范围很重要。如果注重准确性，Oracle 数字类型必须映射到 PostgreSQL NUMERIC。如果最优先考虑的是计算速度，那么最好的映射是 REAL 或 DOUBLE PRECISION。

我们使用Oracle-to-PostgreSQL 转换器来自动迁移该项目的表定义、索引和约束。它将 Oracle 类型映射到最合适的 PostgreSQL 等价物，并允许自定义特定类型映射。

数据迁移

由于数据迁移对于大型数据库可能会消耗大量时间，因此选择正确的策略和工具对于这一步非常重要。

常见的数据迁移方式有以下三种：

• 快照——一步迁移所有数据
• 分段快照——在并行线程或进程中按块迁移数据
• 更改数据复制——通过跟踪增量更改来持续加载数据

快照方法要求源数据库在整个读取数据期间必须停机，以避免迁移过程中数据丢失或损坏。分段快照方法的停机时间要少得多，但仍然是必需的。在这个迁移项目中，我们使用分段快照方法来迁移包含数百万行的大表。

那么，变更数据复制 (CDR) 技术又如何呢？

它有两个主要的实现，每个都有其弱点。第一个被称为基于触发的 CDR。它需要为源数据库中复制的每个表创建插入、更新和删除触发器。这些触发器通过将有关更改事件的信息存储到特殊的“历史”表中来跟踪所有更改。基于此数据，CDR 工具将所有更改复制到目标数据库。基于触发器的 CDR 会导致源数据库的额外开销，因为每次数据更新都会写入“历史”表。

第二种方法称为事务日志 CDR。它使用 Oracle DBMS 创建的事务日志来跟踪更改并将更改复制到目标 PostgreSQL 数据库中。与基于触发器的 CDR 相比，这种方法的优势在于它不修改源数据库。

但是，事务日志 CDR 也有一些弱点：

• Oracle 不断归档事务日志文件，因此如果在归档之前不读取事务日志，CDC 工具可能会丢失一些更改。
• 如果在从事务日志复制更改时丢失与目标数据库的连接（或任何其他类型的错误），数据可能会由于缺乏对事务日志的控制而丢失或损坏。

从 Oracle 到 PostgreSQL 的数据迁移最具挑战性的方面是：源 DBMS 的特定数据格式在目标和外部数据中没有直接等效项。

在本文的前一节中，BYTEA 被描述为最适合二进制数据的 PostgreSQL 数据类型。但是，在迁移大型二进制数据（平均字段大小不小于 10MB）时，不建议使用 BYTEA。原因在于读取BYTEA数据的特殊性——只能分片提取；分段阅读是不可能的。这可能会导致必要的 RAM 开销。 PostgreSQL 类型 LARGE OBJECT 可用作解决此问题的方法。 LARGE OBJECT 类型的所有值都存储在系统表“pg_largeobject”中，该表是每个数据库的一部分。表“pg_largeobject”中最多可以有 40 亿行。 LARGE OBJECT 的最大大小为 4TB，并且可以分段读取。

此外，正确迁移 ROWID 很重要，它是标识表中记录物理地址的伪列。 PostgreSQL 有一个名为 ctid 的类似服务字段，但它直接等同于 ROWID。 PostgreSQL 文档说 ctid 可能会因清理过程而更改。

在 PostgreSQL 中有三种模拟 ROWID 功能的基本方法：

• 使用现有主键（或创建新主键）来标识行而不是 ROWID
• 添加一个带有自动生成值的 serial 或 bigserial 列，并使其成为替换 ROWID 功能的主键/唯一键
• 当无法定义单列主键时，使用建立在多列上的唯一索引（为此目的确定每一行的最小唯一字段集）

Oracle 可能会链接存储在数据库外部的外部数据。但是，可以使用 DBMS 的“外部表”功能将其作为常规表进行处理。 PostgreSQL 出于同样的目的使用 Foreign Data Wrapper 库。例如，它提供扩展名“file_fdw”以将外部 CSV 文件作为常规表使用。

PL/SQL 迁移的挑战

本节探讨在将 Oracle 包、存储过程、函数和触发器迁移到 PostgreSQL 期间解决的问题。

包裹。 PostgreSQL 没有包。可以通过将属于同名 PostgreSQL 模式中的一个包的所有条目分组来模拟这一缺失的功能，全局变量可以存储在服务表中。

空字符串。与 Oracle 不同，在 PostgreSQL 中，空字符串不等于 NULL。例如，在 Oracle 中：

 SQL> select NVL('','This is null') as null_test from dual; NULL_TEST ------------ This is null

在 PostgreSQL 中也是如此：

 testdb=# select coalesce('','This is null') as null_test; null_test ----------- (1 row)

此外，在 Oracle 和 PostgreSQL 中，字符串与 NULL 的连接行为不同。在甲骨文中：

 SQL> Select 'String' || NULL as cs from dual; CS -- String

在 PostgreSQL 中：

 synctest=# Select 'String' || NULL as concat_str; concat_str ------------ (1 row)

类型铸造。 PostgreSQL 在调用函数、运算符或当 INSERT 和 UPDATE 语句将表达式的结果放入表中时需要严格的类型转换。

解决方法是使用伪类型“anyelement”：

 create or replace function my_concat(str1 anyelement, str2 anyelement) returns varchar language plpgsql as $$ begin return str1::varchar || str2::varchar; end; $$;

注意：您可以在存储过程/函数中使用一个 anyelement 参数，也可以使用同一类型 anyelement 的所有参数。

序列。 Oracle 和 PostgreSQL 的序列声明语法相似。例如：

 CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE;

但是，引用序列的下一个值是不同的。在 Oracle 中它是 'sequence.nextval' 而在 PostgreSQL 中它是 nextval('sequence')。

触发器。在 Oracle 中，触发器主体的源代码包含在 CREATE TRIGGER 语句中。 PostgreSQL 要求触发器源代码作为独立函数与 CREATE TRIGGER 语句的引用组合在一起：

 CREATE OR REPLACE FUNCTION store_changes() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF (NEW.first_name <> OLD.first_name) OR (NEW.last_name <> OLD.last_name) OR (NEW.email <> OLD.email) THEN INSERT INTO changes_log(id,changed_on) VALUES(OLD.id,now()); END IF; RETURN NEW; END; $$ CREATE TRIGGER make_changes BEFORE UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE store_changes();

自主交易。 Oracle 中的自治事务允许子程序在不提交或回滚主事务的情况下提交或回滚 SQL 操作。例如，一些数据作为 insert-trigger 的一部分插入到表中。

即使 insert-trigger 的主事务失败，此插入操作也必须成功。在这种情况下，相应的 INSERT 语句必须包含在自治事务中：

 CREATE OR REPLACE PROCEDURE insert_critical_data(v_data varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO info_table (data) VALUES (v_data); commit; END;

PostgreSQL 不直接支持自治事务。一种可能的解决方法是重构代码，因此它不需要自治事务。

否则，您可以使用 PostgreSQL 的模块“dblink”。使用 dblink，建立一个新的连接，使用这个连接执行查询并立即提交，与主事务无关。例如，下面的函数向表中插入一行，即使回滚调用事务，插入操作也会被提交：

 CREATE OR REPLACE FUNCTION insert_critical_data(v_data TEXT) RETURNS void AS $BODY$ DECLARE v_sql text; BEGIN PERFORM dblink_connect('myconn', 'dbname=mydbname user=… password=… host=… port=…'); v_sql := format('INSERT INTO info_table (data) VALUES (%L)', v_data); PERFORM dblink_exec('myconn', v_sql); PERFORM dblink_disconnect('myconn'); END; $BODY$ LANGUAGE plpgsql;

内置函数。 Oracle 和 PostgreSQL 提供相似但不相同的内置函数集。下表包括需要移植到 PostgreSQL 等效项中的 Oracle 函数：

*可以在以下位置找到 Oracle INSTR 函数到 PostgreSQL 的复杂移植：https: //www.postgresql.org/docs/9.0/static/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX

Oracle 条件 'where rownum < N' 必须在 PostgreSQL 中转换为 'limit N' 我们使用 Oracle-to-PostgreSQL Code Converter 来部分自动化触发器、存储过程和函数的转换。它支持将 Oracle 内置函数转换为 PostgreSQL 等效函数，并处理 PL/SQL 的大多数语法模式。但是，复杂的源代码可能需要对输出进行手动后处理。

Oracle 到 PostgreSQL 迁移的有用模块

本节包含有助于从 Oracle 迁移到 PostgreSQL 并测试迁移结果的 PostgreSQL 模块的简要说明。

从 Oracle 迁移时最重要的 PostgreSQL 模块之一是orafce 。它模拟了Oracle数据库管理系统的特定类型、功能和操作符。

扩展“pgTAP”和“pg_prove”可用于 PostgreSQL 函数的功能测试。安装“pgTAP”后，它会创建许多用于编写测试的存储函数。测试函数必须声明为“returns setof text”。然后它可以从 psql 控制台客户端运行：

 psql -h $db_host -p $db_port -U $db_user $db_name -f tests/name_of_test.sql

关于 pgTAP 的详细信息可以在这里找到： http ://pgtap.org/documentation.html

PostgreSQL 扩展plpgsql_check有助于验证 PL/pgSQL 代码。例如，如果 PostgreSQL 函数中缺少变量声明或拼写错误的列名，则在执行过程中肯定会失败。在投入生产之前使用“plpgsql_check”的功能来诊断此类问题：

 select * from plpgsql_check_function_tb('{name of function}');

模块plprofiler有助于分析 PL/pgSQL 代码在函数和存储过程中的性能。

PostgreSQL 扩展oracle_fdw通过 DBMS 的外部数据包装器功能提供与 Oracle 数据库的连接。例如，如果 Oracle 数据库“OCRL”在服务器“server.mydomain.com”上运行，则 oracle_fdw 必须配置如下：

 CREATE EXTENSION oracle_fdw; CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//server.mydomain.com:1521/ORCL'); GRANT USAGE ON FOREIGN SERVER oraserver TO pguser; CREATE USER MAPPING FOR pguser SERVER oraserver OPTIONS (user 'oracle user', password 'oracle password'); CREATE FOREIGN TABLE oratable ( { column definitions } ) SERVER oraserver OPTIONS (schema 'ORAUSER', table 'ORATAB');

然后，您可以将“ oratable ”用作常规 PostgreSQL 表。