将虚拟生成的列从 Oracle 迁移至 PostgreSQL - AWS Prescriptive Guidance
Summary先决条件和限制工具操作说明相关资源

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

将虚拟生成的列从 Oracle 迁移至 PostgreSQL

由 Veeranjaneyulu Grandhi (AWS)、Rajesh Madiwale (AWS) 和 Ramesh Pathuri (AWS) 编写

环境：生产

源：Oracle 数据库

目标：Amazon RDS for PostgreSQL 或 Aurora PostgreSQL-Compatible

R 类型：重构

工作负载：Oracle

技术：迁移；数据库

Amazon Web Services：Amazon Aurora；Amazon RDS；AWS DMS

Summary

在版本 11 及以前版本中，PostgreSQL 不提供直接等同于 Oracle 虚拟列的功能。从 Oracle 数据库迁移到 PostgreSQL 版本 11 或以前版本时，处理虚拟生成的列很困难，原因有两个： 

  • 迁移时虚拟列不可见。

  • PostgreSQL 不支持版本 12 之前的 generate 表达式。

但是，也有一些变通方法可模拟类似的功能。当您使用 AWS Database Migration Service (AWS DMS) 将数据从 Oracle 数据库迁移至 PostgreSQL 版本 11 及以前版本时，您可以使用触发函数在虚拟生成的列中填充值。此模式提供了可用于此目的 Oracle 数据库和 PostgreSQL 代码的示例。在 AWS 上，对于您的 PostgreSQL 数据库，您可以使用 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 或 Amazon Aurora PostgreSQL-Compatible Edition。

从 PostgreSQL 版本 12 开始支持生成的列。生成的列可以按其他列值即时计算，也可以计算和存储。PostgreSQL 生成列与 Oracle 虚拟列类似。

先决条件和限制

先决条件

  • 一个有效的 Amazon Web Services account

  • 源 Oracle 数据库

  • 目标 PostgreSQL 数据库（在 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL-Compatible 上）

  • PL/pgSQL 编码专业知识

限制

  • 仅适用于 12 之前的 PostgreSQL 版本。 

  • 适用于 Oracle 数据库版本 11g 或更高版本。

  • 数据迁移工具不支持虚拟列。

  • 仅适用于同一表中定义的列。

  • 如果虚拟生成的列引用确定性的用户定义函数，则其不能将其用作分区键列。

  • 表达式输出必须是标量值。它无法返回 Oracle 提供的数据类型、用户定义的类型 LOB 或 LONG RAW。

  • 针对虚拟列定义的索引，等同于 PostgreSQL 中基于函数的索引。

  • 必须收集表格的统计信息。

工具

  • pgAdmin 4 是一种适用于 PostgreSQL 的开源管理工具。该工具提供了图形界面，可简化数据库对象的创建、维护和使用。

  • Oracle SQL Developer 是免费的集成开发环境，用于在传统部署和云部署中在 Oracle 数据库中使用 SQL。 

操作说明

任务描述所需技能

创建源 Oracle 数据库表。

在 Oracle 数据库，使用以下语句创建包含虚拟生成的列的表。

CREATE TABLE test.generated_column
( CODE NUMBER,
STATUS VARCHAR2(12) DEFAULT 'PreOpen',
FLAG CHAR(1) GENERATED ALWAYS AS (CASE UPPER(STATUS) WHEN 'OPEN' THEN 'N' ELSE 'Y' END) VIRTUAL VISIBLE
);

在此源表，STATUS 列中的数据通过 AWS DMS 迁移到目标数据库。但是，FLAG 列是使用 generate by 功能填充的，因此 AWS DMS 在迁移期间看不到此列。要实现 generated by 功能，您必须使用目标数据库中的触发器和函数填充 FLAG 列中的值，如下一个操作说明所示。

数据库管理员，应用程序开发人员

在 AWS 创建目标 PostgreSQL 表。

使用以下语句在 AWS 上创建一个 PostgreSQL 表。

CREATE TABLE test.generated_column
(
    code integer not null,
    status character varying(12) not null ,
    flag character(1)
);

在此表中，status 列是标准列。flag 列将是根据该列中的数据生成的 status 列。

数据库管理员，应用程序开发人员
任务描述所需技能

创建 PostgreSQL 触发器。

在 PostgreSQL 中创建触发器。

CREATE TRIGGER tgr_gen_column
AFTER INSERT OR UPDATE OF status ON test.generated_column
FOR EACH ROW 
EXECUTE FUNCTION test.tgf_gen_column();
数据库管理员，应用程序开发人员

创建 PostgreSQL 触发器函数。

在 PostgreSQL 中，为触发器创建函数。此函数填充由应用程序或 AWS DMS 插入或更新虚拟列，并验证数据。

CREATE OR REPLACE FUNCTION test.tgf_gen_column() RETURNS trigger AS $VIRTUAL_COL$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW.flag IS NOT NULL) THEN
RAISE EXCEPTION 'ERROR: cannot insert into column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (NEW.flag::VARCHAR != OLD.flag::varchar) THEN
RAISE EXCEPTION 'ERROR: cannot update column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
IF (old.flag is NULL) OR (coalesce(old.status,'') != coalesce(new.status,'')) THEN
UPDATE test.generated_column
SET flag = (CASE UPPER(status) WHEN 'OPEN' THEN 'N' ELSE 'Y' END)
WHERE code = new.code;
END IF;
END IF;
RETURN NEW;
END
$VIRTUAL_COL$ LANGUAGE plpgsql;
数据库管理员，应用程序开发人员
任务描述所需技能

创建复制实例。

要创建复制实例，请按照 AWS DMS 文档中的说明进行操作。复制实例应与源数据库和目标数据库位于同一虚拟私有云（VPC）中。

数据库管理员，应用程序开发人员

创建源和目标端点。

要创建端点，请按照 AWS DMS 文档中的说明进行操作。

数据库管理员，应用程序开发人员

测试端点连接。

您可以通过指定 VPC 和复制实例并选择运行测试来测试端点连接。

数据库管理员，应用程序开发人员

创建和启动满载任务。

有关说明，请参阅 AWS DMS 文档中的创建任务和满载任务设置。

数据库管理员，应用程序开发人员

验证虚拟列数据。

比较源数据库和目标数据库中虚拟列数据。您可手动验证数据，也可以为此步骤编写脚本。

数据库管理员，应用程序开发人员

相关资源