如何从 Oracle 迁移到 PostgreSQL

本行业报告介紹了 Intelligent Converters 为一间大一些的货物运输机构自制的动态sql语句从 Oracle 转化到 PostgreSQL 的技术。转化的其主要因素是为降低动态sql语句处理系統化的费用，并转向机兼有足够扩张性、的保密性和开发力量的开源系統系統化。

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

• 统计资料库服务管理器 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 或设施初始化以适用 Oracle 的风格的年月日时间型 oracle.date。

余地类行也所需特点注意力。 Oracle 兼备内部自带类行 SDO_GEOMETRY，而 PostgreSQL 所需装设 PostGIS 才行用到余地数据统计类行。

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

甲骨文	数据库
BINARY_FLOAT	真的
BINARY_INTEGER	整数
BINARY_DOUBLE	双精密度较
BLOB、RAW(n)、LONG RAW	BYTEA（1GB 控制）
CLOB，长	文章（1GB 局限性）
日期时间	时光戳
数值5，数值5（*）	DOUBLE PRECISION 或 BIGINT（只要它是主键的一台分）
数据（n，0），数据（n）	n<5 – SMALLINT5<=n<9 – INT9<=n<19 – BIGINTn>=19 – DECIMAL(n)
小数（p，s）	小数（p，s）
最真实的	双的精密度

而言数值8类，理解在数据源表中的采用时间范围较重要。若是 注意最准性，Oracle 数值8类一定要镜像到 PostgreSQL NUMERIC。若是 最后先满足的是计算公式极限速度，特别最后的镜像是 REAL 或 DOUBLE PRECISION。

人们安全使用来自功转入此项作用表定议、目录和自律。它将 Oracle 形式地址地址映射到最适当的 PostgreSQL 等价物，并同意自定议独特形式地址地址映射。

数据迁移

根据动态数据报告迁徙对于那些那些门头动态数据报告库将会会耗用很大时期，因而选泽有效的原则和产品对于那些那些这这一步比较注重。

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

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

浏览更新工艺想要源数剧资料资料库在整体的显示数剧资料资料期内一定要断电，以禁止转迁具体步骤中数剧资料资料流失或磨损。逐段浏览更新工艺的断电日期要少得多，但依旧是有必须的。在这一转迁创业项目中，公司操作逐段浏览更新工艺来转迁是指数十万万行的大表。

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

它有这个最主要的的实行，每这个会有其缺点。第1个被称呼对于闪避的 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 越重要，它是LOGO表里登记数学位置的伪列。 PostgreSQL 还有1个个名里 ctid 的看起来像服务于字符串，但它马上相等于 ROWID。 说 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 函数：

甲骨文	数据库
ADD_MONTH($日期时间,$n_month)	$date + $n_month * 接连 '1 月'
编解码($exp, $when, $then, ...)	CASE $exp WHEN $when THEN $then ... 结束后
INSTR($str1, $str2)	*地段（$str2 在 $str1 中）
行号	**row_number() 在 ()
控制系统日期时间	现在日期时间
SYS_GUID()	uuid_generate_v1()

*可不可以在低于选址找 Oracle INSTR 函数值到 PostgreSQL 的较为复杂冻胚移植：https:

Oracle 生活条件 'where rownum < N' 不得不在 PostgreSQL 中转地换为 'limit N' 我们公司采用 Oracle-to-PostgreSQL Code Converter 来部位自功化引起器、存贮工作和方程的转型。它不支持将 Oracle 内装方程转型为 PostgreSQL 等效方程，没收违法所得理 PL/SQL 的基本上数语法结构玩法。如果，较为复杂的源二维码应该须要对工作输出去一键后治理 。

Oracle 到 PostgreSQL 迁移的有用模块

抛锚式教学是指能够促进从 Oracle 搬迁到 PostgreSQL 并测试仪搬迁后果的 PostgreSQL 模组的大概这说明。

从 Oracle 迁徙时最猛要的 PostgreSQL 模组的一个是 。它模仿了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 的详细分析信息内容是可以将在里找寻到： ://pgtap.org/documentation.html

PostgreSQL 延伸这样有利于证实 PL/pgSQL 编码。如，如果 PostgreSQL 变量类型中不兼容变量类型申明或发音不正确的列名，则在实施具体步骤中当然会不了。在投放生产加工前几天用“plpgsql_check”的模块来程度某些情况：

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

传感器能有效的研究分析 PL/pgSQL 编码在数学函数和存贮进程中的的性能。

PostgreSQL 扩张依据 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 表。