7个工具！让Oracle 迁移至 PostgreSQL成为可能！

作者：Oded Valin

本文来源：EverSQL

* 本文由IvorySQL社区翻译。

📕前言

 

作为加州一家大型能源基础设施公司的Oracle DBA，我花了数年时间掌握Oracle数据库。然而，我们的高管团队最近的一个决定促使我们过渡到PostgreSQL。这种转变将我从一个经验丰富的Oracle DBA转变为PostgreSQL领域的新人。在经历了这次迁移之后，我觉得有必要分享我在此过程中获得的见解。本文详细介绍了我的旅程、遇到的挑战以及促进这一转变的七个不可或缺的工具。我希望分享这些经验将使你的PostgreSQL之旅更加顺利。

 

🐘迁移过程概述

从Oracle迁移到PostgreSQL不仅仅是按下一个开关这么简单。这是一个包含一系列步骤的旅程，例如架构转换、数据迁移、应用程序迁移和性能调优。每个阶段都有自己的问题，我需要一个解决方案的工具箱来处理它们。

 

01

# 工具 # Ora2Pg

 

Ora2Pg是我的第一个盟友。它是一个开源工具，可将Oracle数据库模式转换为PostgreSQL格式。

 

由于Ora2Pg是一个开源项目，你可以看到该工具的受欢迎程度：

优点：

• 可以处理大量的甲骨文对象

• 可通过配置文件进行配置

 

局限性：

• 复杂的 PL/SQL 转换可能需要手动干预

• 大型数据库可能需要很长时间才能转换

 

对我们来说，我们有一个巨大的甲骨文表，它是我们日常运营的支柱。手动转换它就像攀登珠穆朗玛峰一样难，但是使用 Ora2Pg 就像运行命令一样简单：

ora2pg -t TABLE -o table.sql -b /output/directory -c /path/to/config/file 

 

我们的Oracle数据库中也有一堆序列。Ora2Pg也像冠军一样处理它们：

ora2pg -t SEQUENCE -o sequence.sql -b /output/directory -c /path/to/config/file 

 

就这样，我已经准备好了PostgreSQL表和序列。

 

02

# 工具 # AWS数据库迁移服务（DMS）

 

接下来是 AWS DMS，这是一项云服务，它帮助我以最短的停机时间迁移数据。在每一分钟的停机时间都可能造成一大笔损失的行业中，这是一个巨大的胜利。

 

我们之前有一个数据库对我们的运营至关重要。使用 DMS，我设置了连续复制，这意味着数据库在迁移期间仍然可用。

 

我还发现DMS的任务监控功能非常方便。使用一个简单的 AWS CLI 命令，我能够密切关注迁移状态：

aws dms describe-replication-tasks --filters Name=replication-task-id,Values=task1 

 

我们从本地迁移到AWS，但如果您要迁移到Google Cloud，那么您可以使用Google Cloud Database Migration Service（DMS），该服务提供类似的功能，并且已经与Ora2Pg集成。

 

优点：

• 支持同构和异构迁移

• 允许连续复制，减少停机时间

 

局限性：

• 这是一项付费服务，除非您拥有 AWS 的积分或资金。

• 并非所有源数据库和目标数据库都支持所有数据类型

 

03

# 工具 # pgLoader

 

pgLoader是PostgreSQL的数据加载工具，它使用COPY命令来超快地加载数据。它就像一辆移动的卡车，帮助将我们的数据从Oracle传输到PostgreSQL。

 

当我第一次尝试移动我们的数据时，进展缓慢。但是使用 pgLoader，我只是运行了以下命令：

pgloader oracle://user@localhost/dbname postgresql:///dbname 

 

我们还有一堆CSV文件需要加载到PostgreSQL中。pgLoader也处理了这些：

pgloader --type csv --field 'column1,column2,column3' --with 'header=true' csv_file.csv postgresql:///dbname 

 

优点：

• 快速数据加载

• 可以从各种来源加载数据，包括平面文件和其他数据库

 

局限性：

• 不提供在迁移期间转换数据的选项

• 仅限于数据加载，不处理架构或代码迁移

 

04

# 工具 # 外部数据包装器 （FDW）

 

外部数据包装器（FDW）是PostgreSQL的简洁功能，它们可让您管理其他数据库中的数据，就好像它们是本地PostgreSQL表一样。

 

例如，有时我需要直接从PostgreSQL查询Oracle数据。对于 FDW，我能够设置一个外部表并且像对本地数据一样运行查询：

CREATE EXTENSION oracle_fdw; CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//hostname/dbname'); 

 

后来，当我需要从外部 MySQL 数据库中提取数据时，我使用了mysql_fdw：

CREATE EXTENSION mysql_fdw; CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'mysqlhost', port '3306'); 

 

优点：

• 简化数据集成

• 支持不同的数据源

 

局限性：

• 一些外部数据包装器是只读的

• 性能可能比本地查询慢

 

05

# 工具 # pg_dump、pg_restore和其他内置 PG 功能

 

PostgreSQL带有自己的工具箱。我发现两个非常有用的工具是用于数据备份和还原的“pg_dump”和“pg_restore”。

 

在我们的迁移过程中，一旦我们所有的数据都在PostgreSQL中，我想确保我们有可靠的备份。所以我使用这些命令来转储和恢复我们的数据库：

pg_dump -U username -W -F t dbname > dbname.tar pg_restore -U username -W -F t -d dbname dbname.tar 

 

我们还有几张需要单独备份的大表。“pg_dump”工具再次派上用场：

pg_dump -U username -W -F t -t large_table dbname > large_table.tar 

 

优点：

• 可以处理完整备份和部分备份

• 允许并行备份和恢复

 

局限性：

• 从转储还原可能比初始备份慢

• 不是为大规模数据迁移而设计的

 

06

# 工具 # EverSQL，用于迁移后调优

 

在我们完成迁移后，我们的查询需要进行一些调整才能在PostgreSQL上高效运行。这就是EverSQL的用武之地。这个工具采用了我们的SQL查询，并针对PostgreSQL进行了优化。

 

我们的一个Oracle查询在PostgreSQL上运行得比较慢。我把它弹出到EverSQL中，它给出了一个优化版本，运行速度提高了19倍。我还使用EverSQL的Index Advisor功能来获取有关添加哪些索引的建议。

 

优点：

• 自动 SQL 查询优化，具有自动重写功能。

• 提供索引优化建议

 

局限性：

大多数功能都在免费层上，但高级功能需要付费订阅。

 

07

# 工具 # Npgsql

 

最后，还有Npgsql。它是PostgreSQL的.NET数据提供程序，让我们的.NET应用程序与PostgreSQL通信。

 

我们有一个.NET应用程序需要连接到我们新的PostgreSQL数据库。使用 Npgsql，它就像：

var connString = "Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase"; using (var conn = new NpgsqlConnection(connString)) { conn.Open(); // Perform database operations } 

 

还有一次，我不得不从我们的.NET应用程序调用PostgreSQL存储过程。Npgsql 使它变得简单：

using (var conn = new NpgsqlConnection(connString)) { conn.Open(); using (var command = new NpgsqlCommand("stored_procedure_name", conn)) { command.CommandType = CommandType.StoredProcedure; var result = command.ExecuteNonQuery(); } } 

 

优点：

• 用于PostgreSQL的完全托管的.NET数据提供程序

• 支持新式 .NET 功能，如异步和实体框架核心

 

局限性：

• 仅适用于 .NET 应用程序

• 可能需要更改应用程序代码才能从 Oracle 数据提供程序切换

 

⚒️从 Oracle 迁移到 PostgreSQL 的常见陷阱

 

从Oracle迁移到PostgreSQL并非没有障碍。以下是您在迁移过程中可能遇到的一些常见问题，以及我自己的经验中的真实示例。

 

1.SQL 语法和功能的差异

Oracle和PostgreSQL SQL语法和功能并不总是一对一的匹配。这可能会导致迁移过程中出现问题，尤其是对于复杂的查询和存储过程。

 

例如：在 Oracle 中，我曾经严重依赖“CONNECT BY”子句进行分层查询。PostgreSQL不支持“CONNECT BY”。我不得不使用递归公用表表达式（CTE）重写这些查询，这是PostgreSQL处理分层数据的方式。

 

2.交易行为

Oracle和PostgreSQL处理事务的方式不同。在 Oracle 中，DDL 语句被视为自治事务，并立即提交。相比之下，PostgreSQL将DDL语句视为常规事务。

 

例如：我曾经尝试在PostgreSQL的单个事务中执行一系列DDL和DML语句，期望它们的行为类似于Oracle。这导致了一些意外行为，因为更改没有立即提交。

 

3. 区分大小写

默认情况下，Oracle 对列名和表名不区分大小写，而 PostgreSQL 区分大小写。如果您的 Oracle 架构使用混合大小写标识符，这可能会导致出现问题。

 

例如：我在 Oracle 中有一个包含混合大小写列的表。当我将其迁移到PostgreSQL时，我遇到了问题，因为PostgreSQL将“myColumn”和“mycolumn”视为两个不同的列。

 

4. 序列和自动递增

在 Oracle 中，您可以创建一个序列，然后使用触发器自动递增列。PostgreSQL有一个内置的功能，可以使用“SERIAL”或“IDENTITY”自动递增列，但是将Oracle序列和触发器迁移到这种新范式可能会很棘手。

 

例如：* 在 Oracle 中，我有一个带有序列和用于自动递增主键的触发器的表。迁移到PostgreSQL时，我不得不用“SERIAL”主键替换它们。

 

5.数据类型

Oracle和PostgreSQL有不同的数据类型集。虽然其中许多是直接映射的，但某些Oracle数据类型在PostgreSQL中没有等效项。

 

例如：我在 Oracle 中有一个使用“NUMBER”数据类型的表。PostgreSQL没有“NUMBER”类型，所以我不得不在PostgreSQL中仔细地将其映射到适当的数字类型。

 

6.空和空字符串

Oracle 以相同的方式处理 NULL 和空字符串，这与 PostgreSQL 不同，后者的 NULL 和空字符串是不同的。如果 Oracle 数据库或应用程序依赖于此行为，则需要在迁移过程中仔细处理此问题。

 

例如：迁移后，我的应用程序中曾经遇到过一个错误，因为它依赖于 Oracle 将空字符串视为 NULL。我不得不更新应用程序代码以在PostgreSQL中分别处理NULL 和空字符串。

 

7. 日期和时间类型

Oracle 有一个包含日期和时间的 DATE 类型，这与 PostgreSQL 不同，PostgreSQL 将日期和时间分为 DATE、TIME 和 TIMESTAMP 类型。如果 Oracle 数据库使用 DATE 类型来存储时间，这可能会导致混淆。

 

例如：我们在 Oracle 中有一个表，其中包含用于存储日期和时间的 DATE 列。当我使用 DATE 类型将其迁移到 PostgreSQL 时，我们丢失了时间信息。我不得不返回并将其更改为PostgreSQL中的TIMESTAMP类型。

 

🏆结论

 

从Oracle迁移到PostgreSQL是一段疯狂的旅程，但这些工具使它变得容易得多。无论您是经验丰富的Oracle DBA还是PostgreSQL的新手，我都希望我的旅程和这些工具可以帮助您的迁移更加顺利。祝您好运，祝您迁移愉快！

 

---

 

IvorySQL

一款开源的兼容Oracle的PostgreSQL

 

官方网址：

https://www.ivorysql.org/zh-cn/

社区仓库：

https://github.com/IvorySQL/IvorySQL

IvorySQL社区欢迎并赞赏所有类型的贡献，期待您的加入！

记得在GitHub给我们一个 ⭐奥~