Oracle 到 Postgres 转换

来自 PostgreSQL 维基
跳转到导航跳转到搜索

注意：此页面最初是在 OpenACS 网站上发布的，但在漫长的岁月里似乎丢失了，因此现在在此重新发布。作者：James Shannon、Ben Adida 和 Don Baccus

开始之前你需要知道的

你应该对 SQL 有相当的了解。了解 Oracle SQL 和 Postgres SQL 的细节显然是巨大的优势，但本文中的提示应该可以让你快速了解它们之间的差异。

如果你要将 Oracle SQL 移植到 Postgres SQL 用于 ACS/pg，你也应该熟悉 AOLserver Tcl，特别是 AOLserver 数据库 API。

在本文中，我们讨论的是

  • Oracle 10g 到 11g（大多数内容适用于 8i）
  • Oracle 12c 在某些方面有所不同（多租户数据库），但迁移可能更容易
  • Postgres 8.4，有时也适用于早期版本。
  • 企业版与 Oracle RAC 迁移

Oracle 企业版和 RAC 注意事项

在某些情况下，Oracle 企业版的迁移到 PostgreSQL 比 Oracle 实时应用集群 (RAC) 更直接。在 RAC 中，你可能有多个独立的、性能强劲的 DML 应用程序，通常是 OLTP 类型，它们连接到同一个 RAC 集群，而 RAC 充当一种应用程序池。从 Oracle RAC 池迁移到 PostgreSQL 的一个常见错误是将所有池应用程序与一个 PostgreSQL 实例关联。这里错过了的大局是 ACTIVE-ACTIVE（Oracle RAC）和 ACTIVE-PASSIVE（PG）。虽然 Oracle RAC 可以将应用程序划分并跨集群中的节点进行负载平衡，但在 PostgreSQL 中不存在这种机制。因此，“正确”的解决方案，如果没有进行一些重新架构或使用第三方工具和扩展，就是一次将应用程序从 Oracle RAC 迁移到独立的 PostgreSQL 实例，每个性能强劲的应用程序对应一个 PostgreSQL 实例。接下来的两个部分阐述了在 PostgreSQL 承载 2 个或更多个性能强劲的应用程序时出现的 CPU 和内存因素。

CPU 争用

一个连接了多个性能强劲的 DML 应用程序的 PostgreSQL 实例可能会出现 CPU 负载问题，因为多个应用程序的并发活动事务堆积或累积。PostgreSQL 的一个一般规则是，当活动并发事务数量（pg_stat_activity.state = 'active'）超过 CPU 数量的两倍时，我们就会开始遇到 CPU 负载饱和。在多个性能强劲的应用程序针对同一个 PostgreSQL 实例进行操作时，这种情况是预料之中的。

内存争用

当多个独立的、性能强劲的 DML 应用程序同时访问同一个 PostgreSQL 实例时，我们开始看到固有的问题，即不相关的 SQL 工作负载（独立的数据库、模式和表）争用相同的内存资源。通常，对于单个应用程序来说，一个好处是，很多磁盘到内存的活动都与相同的高频使用表相关。因此，你通常可以获得 95% 到 99% 的缓存命中率。但是，当多个独立的 SQL 工作负载在一个 PostgreSQL 实例中同时工作，并拥有自己的表集时，你可能会开始看到这些独立的 SQL 工作负载之间争用驻留在内存中的页面。在这种情况下，你必须确保你有足够的 OS 内存和 shared_buffers 内存来处理多个 SQL 工作负载争用相同分页资源时的激增情况。预测在任何时刻不同 SQL 工作负载之间的不同负载活动程度，使得调整 shared_buffers 变得更加困难，甚至可能无法同时调整两个工作负载的 shared_buffers，除非显著增加预留给 shared_buffers 的内存，即使它可能大部分时间都不需要那么多内存。

事务

虽然在 Oracle RDBMS 中，COMMIT 后第一个语句会开始一个新的多语句事务，但 Postgres 采用的是自动提交模式。每段进行 DML 操作且不需要立即提交的代码都必须使用 BEGIN 语句开始一个事务。ROLLBACK 和 COMMIT 在两个系统中具有相同的语义含义；SAVEPOINTS 也是如此。Postgres 了解 Oracle 了解的所有隔离级别（以及更多）。在大多数情况下，Postgres 的默认隔离级别（Read Committed）就足够了。

语法差异

在 Postgres 中，对于实际上相同的功能，有一些语法差异。ACS/pg 试图自动执行这些更改，只留下主要的功能差异需要手动移植。这是通过 db_sql_prep 来实现的，它对一段 SQL 代码进行了一些正则表达式替换。

函数

Oracle 有超过 250 个内置的单行函数和超过 50 个聚合函数。请参见 Oracle 函数。

Sysdate

Oracle 使用函数 sysdate 获取服务器时区的当前日期和时间。Postgres 使用 'now'::timestamp 获取会话时区的当前事务开始日期和时间，ACS/pg 方便地将其封装在一个名为 sysdate() 的函数中。

ACS/pg 还包含一个名为 db_sysdate 的 Tcl 过程，该过程应该在 sysdate 术语出现时使用。因此

set now [database_to_tcl_string $db "select sysdate from dual"]

应该变为

set now [database_to_tcl_string $db "select [db_sysdate] from dual"]

Dual 表

Oracle 使用表 DUAL 用于 select 语句，实际上不需要表名，因为 Oracle 中的 FROM 子句是必需的。在 PostgreSQL 中，我们可以完全省略 FROM 子句。这个表可以作为视图在 postgres 中创建，以方便移植问题。这使得代码可以在一定程度上与 Oracle SQL 保持兼容，而不会让 Postgres 解析器感到厌烦。

无论如何，在迁移时，如果可能的话，请从语句中删除 "FROM DUAL" 子句。与 dual 的连接非常少见，而且很奇怪。

ROWNUM 和 ROWID

Oracle 的伪列 ROWNUM 返回一个在读取数据时分配的数字，但在执行 ORDER BY 之前分配。你可能会考虑用 ROW_NUMBER() OVER (ORDER BY ...) 来替换它，这在很多情况下都能奏效。使用序列的模拟可能比较慢。

Oracle 的伪列 ROWID 返回表行的物理地址，以 base64 编码。在应用程序中，它可以用于（暂时）缓存行的地址，以便更容易地第二次定位它们。Postgres 有 ctid，它具有相同的功能。

序列

Oracle 的序列语法是 sequence_name.nextval。

Postgres 的序列语法是 nextval('sequence_name')。

在 Tcl 中，获取下一个序列值可以通过调用 [db_sequence_nextval $db sequence_name] 来抽象。如果你需要将序列的值包含在一个更复杂的 SQL 语句中，你可以使用 [db_sequence_nextval_sql sequence_name]，它将返回相应的语法。

Decode

Oracle 的便捷 decode 函数的工作原理如下

decode(expr, search, result [, search, result...] [, default])

为了评估此表达式，Oracle 将 expr 与每个 search 值逐一进行比较。如果 expr 等于某个 search，Oracle 将返回相应的 result。如果没有找到匹配项，Oracle 将返回 default，或者，如果省略 default，则返回 null。

Postgres 没有相同的构造。它可以用以下方法复制

CASE WHEN expr THEN expr [...] ELSE expr END

它返回与第一个真谓词相对应的表达式。例如

CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END

在 DECODE 和它的 CASE 模拟之间存在一个细微的差异

DECODE (x,NULL,'null','else')

如果 x 为 NULL，它将返回 'null'，而

CASE x WHEN NULL THEN 'null' ELSE 'else' END

将返回 'else'。这在 Oracle 中也是适用的。

NVL

Oracle 还有另一个便捷的函数：NVL。如果 NVL 的第一个参数不为 null，则返回第一个参数，否则返回第二个参数。

start_date := NVL(hire_date, SYSDATE);

上面的语句将返回 SYSDATE，如果 hire_date 为 null。Postgres（和 Oracle）有一个函数以更通用的方式执行相同的事情：coalesce(expr1, expr2, expr3,....) 返回传递给它的第一个非 null 表达式。

FROM 中的子查询

PostgreSQL 要求用圆括号括住子 SELECT 语句，并且必须为其提供一个别名。在 Oracle 中，别名不是必需的。

Oracle 中的查询

 SELECT * FROM (SELECT * FROM table_a)

在 PostgreSQL 中将类似于

 SELECT * FROM (SELECT * FROM table_a) AS foo

功能差异

Postgres 没有 Oracle 的所有功能。ACS/pg 必须使用特定的解决方法来处理这些限制。几乎所有的事情都可以在 Postgres 下完成，但有些功能正在等待新版本的开源数据库。

外连接

旧版（版本 9i 之前）Oracle 中的外连接的工作原理如下

SELECT a.field1, b.field2
FROM a, b
WHERE a.item_id = b.item_id(+)

其中 (+) 表示，如果表 b 中没有与正确的 item_id 相匹配的行，则匹配仍然应该发生，并返回表 b 中的一行空行。例如，在这种情况下，对于表 a 中所有在表 b 中没有匹配行的行，仍然将返回一行，其中 a.field1 是正确的，但 b.field2 为 null。

在 Postgresql 和 Oracle 9i 及更高版本中

SELECT a.field1, b.field2
FROM a
LEFT OUTER JOIN b
ON a.item_id = b.item_id;

在某些其他情况下，如果只从外连接表中提取聚合值，则可以完全不使用连接。如果原始查询是

SELECT a.field1, sum (b.field2)
FROM a, b
WHERE a.item_id = b.item_id (+)
GROUP BY a.field1

那么 Postgres 查询可以类似于

SELECT a.field1, b_sum_field2_by_item_id (a.item_id)
FROM a

其中你已经定义了函数

CREATE FUNCTION b_sum_field2_by_item_id (integer)
RETURNS integer
AS '
DECLARE
     v_item_id alias for $1;
BEGIN
     RETURN sum(field2) FROM b WHERE item_id = v_item_id;
END;
' language 'plpgsql';

从 9i 开始，Oracle 也支持 SQL 99 外连接语法。不幸的是，一些程序员仍然坚持使用旧语法（即使在它被取代近 20 年后）。这就是为什么本章仍然有必要。

CONNECT BY

Postgres 没有 connect by 语句。但是，它有 WITH RECURSIVE。由于 WITH RECURSIVE 是图灵完备的，因此将 CONNECT BY 语句转换为 WITH RECURSIVE 语句很简单。

有时 CONNECT BY 被用作一个简单的迭代器

SELECT ... FROM DUAL CONNECT BY rownum <=10

可能等同于 Postgres 查询

SELECT ... FROM generate_series(...)

NO_DATA_FOUND 和 TOO_MANY_ROWS

默认情况下，这些异常在 PLpgSQL 中的选择语句中被禁用。如果您需要在存储的 PLpgSQL 代码中保留单行检查，则需要在所有选择语句中的任何 INTO 关键字后添加 STRICT 关键字。参见 文档.

数据类型

Postgres 主要遵循 SQL 标准，而 Oracle 在历史上则选择自己的方式，尤其是在数据类型方面。

空字符串和 NULL 值

在 Oracle 中，空字符串 ('') 和字符串上下文中的 NULL 值在最新版本中是相同的。您可以将 NULL 结果与字符串连接，并获得该字符串作为结果。在 PostgreSQL 中，您将获得 null。在 Oracle 中，您需要使用 IS NULL 运算符来检查字符串是否为空。在 PostgreSQL 中，您将获得空字符串的 FALSE 和 NULL 的 TRUE。

从 Oracle 转换为 PostgreSQL 时，您需要仔细分析所有字符串处理代码并区分 NULL 和空字符串。

数字类型

Oracle 程序主要使用 NUMBER。Postgres 中的适当数据类型是decimal或numeric，两者都等效。Postgres 中数字的限制（小数点前最多 131072 位数字；小数点后最多 16383 位数字）远高于 Oracle，并且在内部以类似的方式存储。

Oracle 允许您使用 INTEGER、REAL、DOUBLE PRECISION 和 FLOAT，但它会将它们映射到 NUMBER。

Oracle 的后期版本有 BINARY_FLOAT 和 BINARY_DOUBLE，它们对应于 PostgreSQL 的real和double precision.

日期和时间

Oracle 主要使用 DATE 数据类型，它结合了日期 + 时间。在大多数情况下，用 Postgres 的 TIMESTAMP 替换它应该足够了。在某些情况下，它不会产生完全正确的结果，因为日期仅包含秒、分钟、小时、日、月和年。没有秒的小数部分，没有夏令时，没有时区。

Oracle 的 TIMEZONE 与 Postgres 的非常相似。

Oracle 的 TIMESTAMP WITH TIME ZONE 与 PostgreSQL 的不同之处在于，它将时区与值一起存储。PostgreSQL 将值存储在 UTC 中，并在显示时将其转换为当前会话时区。如果您需要保留原始时区信息，则必须单独存储。

Oracle 仅知道 INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND，因此也可以在 Postgres 中直接使用。

CLOB

Postgres 以 TEXT 的形式提供了良好的 CLOB 支持。

BLOB

（请注意，本段内容已过时：PostgreSQL 现在不仅支持下面描述的大对象，还支持bytea类型，它是 BLOB 的良好替代品，只是它不能进行流传输——为此，您仍然需要使用大对象。）

Postgres 中对二进制大对象的支持非常差，不适合在 24/7 环境中使用，因为您无法使用 pg_dump 转储它们。备份使用 Postgres 大对象的数据库需要您关闭 RDBMS 并转储数据库目录中的文件。

Don Baccus 编写了一个扩展 AOLserver postgres 驱动程序以提供 BLOB 类支持的黑客，方法是在将二进制文件填充到数据库或从数据库中提取二进制文件之前对其进行 uuencode/解码。生成的物体可以被“pg_dump”一致地转储，而 RDBMS 正在运行。在进行备份时无需中断服务。

为了解决 Postgres 强加于元组大小的一块限制，驱动程序将编码后的数据分成 8K 块。

Postgres 大对象计划在 2000 年夏季进行重大改进。因此，仅实现了 ACS 使用的 BLOB 功能。

要使用 BLOB 驱动程序扩展，您必须首先在将存储 BLOB 的表中创建一个名为“lob”的“integer”类型列，并在其上创建一个调用“on_lob_ref”的触发器。您必须使用“lob”这个名字。以下是一个示例

create table my_table (
    my_key integer primary key,
    lob integer references lobs,
    my_other_data some_type -- etc
);

create trigger my_table_lob_trig before insert or delete or update on my_table for each row execute procedure on_lob_ref();

将二进制文件放入“my_table”

set lob [database_to_tcl_string $db "select empty_lob()"]

ns_db dml $db "begin"
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"
ns_pg blob_dml_file $db $lob $tmp_filename
ns_db dml $db "end"

请注意，即使您没有同时更新任何其他表，将文件填充到数据库的 ns_pg 调用也必须包含在事务中。如果您没有这样做，驱动程序将返回错误。

将存储在“my_table”中的大对象返回给用户

set lob [database_to_tcl_string $db "select lob from my_table
                                     where my_key = $my_key"]
ns_pg blob_write $db $lob


请注意，您无需将 blob_write 的调用包装在事务中，因为数据库没有被修改。

大对象在不再使用时会自动删除。要替换存储在现有记录中的大对象，只需通过调用“empty_lob()”分配一个新的大对象，并将返回的键分配给表中的“lob”列。

外部工具

Oracle 迁移有几个扩展工具可用。

Ispirer 工具包
Ispirer Toolkit 自动迁移整个数据库架构（表、视图、包、存储过程、函数、触发器等），并将数据从 Oracle 传输到 PostgreSQL。
SQLWays 工具包
SQLWays Toolkit 是一款易于使用的数据库迁移工具，用于在最流行的数据库之间迁移整个数据库架构、SQL 对象、表和数据。该工具以免费在线转换器的方式提供，并且
提供免费的演示许可证。
Convertum.ru
Convertum 是一款强大的工具，可自动迁移 25 多个数据库，包括从 Oracle 到 PostgreSQL 的迁移。30 天试用期免费提供。
CYBERTEC 迁移器
用于从 Oracle 迁移到 PostgreSQL 的企业级软件。GUI 驱动，高性能，停机时间短。
Full Convert
在 Oracle（以及 40 多个其他数据库引擎）和 PostgreSQL 之间进行数据库转换。使用最多 16 个并行工作器，每秒可处理超过一百万条记录。
DBConvert - 数据转换和同步
Oracle 和 PostgreSQL 数据库之间的无缝数据迁移和（双向）同步。
ESF 数据库迁移工具包
该工具包使用向导将 Oracle 数据库迁移到 PostgreSQL。它直接连接到 Oracle 和 PostgreSQL 数据库，并迁移其表结构、数据、索引、主键、外键、注释等。
orafce
该项目的目的是实现 Oracle 数据库中的某些函数。现在已经实现了某些日期函数（next_day、last_day、trunc、round 等）、字符串函数和某些模块（DBMS_ALERT、DBMS_OUTPUT、UTL_FILE、DBMS_PIPE 等）。功能已在 Oracle 10g 上验证，并且该模块对生产工作很有用。
ora2pg
Ora2Pg 是一个 Perl 模块，用于将 Oracle 数据库架构导出到与 PostgreSQL 兼容的架构。它连接您的 Oracle 数据库，提取其结构，并生成一个 SQL 脚本，您可以将其加载到 PostgreSQL 数据库中。
Oracle 到 Postgres
将 Oracle 数据库迁移到 PostgreSQL 服务器的程序。它不使用 ODBC 或任何其他中间件软件。表结构、数据、索引、主键和外键都会被转换。命令行支持允许脚本化、自动化和计划转换过程。
ora_migrator
使用 PL/pgSQL 编写的 PostgreSQL 扩展，提供一个利用 Oracle 外部数据包装器的数据库迁移工具包。
Splendid Data - Cortex
Cortex 是市场上最先进的产品，用于将 Oracle 数据库自动迁移到原生 PostgreSQL。这可以防止供应商锁定，并提供最大程度的部署自由度，无论是在本地还是在云中。由于 Cortex 建立了数据和代码对象之间所有必要的依赖关系，并且还自动迁移了平均 80% 的代码对象，因此它是独一无二的。
检索自 "https://wiki.postgresql.ac.cn/index.php?title=Oracle_to_Postgres_Conversion&oldid=39600"