将 Oracle ROWID 功能迁移到 AWS 上的 PostgreSQL

由 Rakesh Raghav (AWS) 和 Ramesh Pathuri (AWS) 编写

环境：PoC 或试点	源：Oracle 数据库	目标：AWS 上的 PostgreSQL 数据库
R 类型：更换平台	工作负载：Oracle	技术：迁移；数据库
Amazon Web Services：Amazon Aurora；Amazon RDS；AWS SCT；AWS CLI

Summary

此模式描述了用于将 Oracle 数据库中的 ROWID 伪列功能迁移到适用于 PostgreSQL 的 Amazon Relational Database Service (Amazon RDS)、Amazon Aurora PostgreSQL 兼容版本或 Amazon Elastic Compute Cloud (Amazon EC2) 中的 PostgreSQL 数据库的选项。

在 Oracle 数据库中，ROWID 伪列是表中某一行的物理地址。即使表中不存在主键，该伪列也用于唯一标识行。PostgreSQL 有一个名为 ctid 的类似伪列，但它不能用作 ROWID。正如 PostgreSQL 文档中所述，ctid 可能会在更新后或每次 VACUUM 进程后发生变化。

您可以通过三种方式在 PostgreSQL 中创建 ROWID 伪列功能：

使用主键列代替 ROWID 来标识表中的一行。
在表中使用逻辑主键/唯一键（可能是复合键）。
添加一个带有自动生成值的列，并使其成为模拟 ROWID 的主键/唯一键。

此模式将引导您完成所有三种实现，并描述每个选项的优缺点。

先决条件和限制

先决条件

一个有效的 Amazon Web Services account
程序语言/PostgreSQL（PL/pgSQL）编码专业知识
源 Oracle 数据库
Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 兼容集群，或用于托管 PostgreSQL 数据库的 EC2 实例

限制

此模式为 ROWID 功能提供了解决方法。PostgreSQL 不提供与 Oracle 数据库中的 ROWID 等效的项。

产品版本

PostgreSQL 11.9 或更高版本

架构

源技术堆栈

Oracle Database

目标技术堆栈

Aurora PostgreSQL 兼容、Amazon RDS for PostgreSQL 或具有 PostgreSQL 数据库的 EC2 实例

实施选项

有三个选项可以解决 PostgreSQL 中缺少 ROWID 支持的问题，具体取决于您的表是否具有主键或唯一索引、逻辑主键或标识属性。您的选择取决于您的项目时间表、当前的迁移阶段以及对应用程序和数据库代码的依赖关系。

选项	描述	优点	劣势
主键或唯一索引	如果您的 Oracle 表具有主键，您可以使用该键的属性来唯一标识一行。	不依赖于专有数据库功能。对性能的影响最小，因为主键字段已编制索引。	需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到主键字段。
逻辑主键/唯一键	如果您的 Oracle 表具有逻辑主键，您可以使用该键的属性来唯一标识一行。逻辑主键由可以唯一标识行的一个属性或一组属性组成，但不会通过约束在数据库上强制执行。	不依赖于专有数据库功能。	需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到主键字段。如果未对逻辑主键的属性编制索引，则会对性能产生重大影响。不过，您可以添加唯一索引来防止出现性能问题。
标识属性	如果您的 Oracle 表没有主键，您可以创建一个附加字段作为 `GENERATED ALWAYS AS IDENTITY`。每当将数据插入到表中时，此属性都会生成一个唯一值，因此它可用于唯一标识数据操作语言（DML）操作的行。	不依赖于专有数据库功能。 PostgreSQL 数据库会填充该属性并保持其唯一性。	需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到标识属性。如果未对附加字段编制索引，则会对性能产生重大影响。不过，您可以添加一个索引来防止出现性能问题。

工具

Amazon Relational Database Service（Amazon RDS）for PostgreSQL 可帮助您在 Amazon Web Services Cloud 中设置、操作和扩展 PostgreSQL 关系数据库。
Amazon Aurora PostgreSQL 兼容版是一个完全托管的、与 ACID 兼容的关系数据库引擎，可帮助您建立、运行和扩展 PostgreSQL 部署。
AWS 命令行界面（AWS CLI）是一种开源工具，它可帮助您通过命令行 Shell 中的命令与 Amazon Web Services 交互。在此模式中，您可以使用 AWS CLI 通过 pgAdmin 运行 SQL 命令。
pgAdmin 是一种适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。
AWS Schema Conversion Tool（AWS SCT）通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。

操作说明

任务描述所需技能

任务	描述	所需技能
确定使用该`ROWID`属性的 Oracle 表。	使用 AWS Schema Conversion Tool (AWS SCT) 来识别具有 `ROWID` 功能的 Oracle 表。有关更多信息，请参阅 AWS SCT 文档。 —或者— 在 Oracle 中，使用 `DBA_TAB_COLUMNS` 视图来标识具有 `ROWID` 属性的表。这些字段可用于存储 10 字节的字母数字字符。确定用法，并将其转换为 `VARCHAR` 字段（如果适用）。	数据库管理员或开发人员
标识引用这些表的代码。	使用 AWS SCT 生成迁移评测报告来识别受 `ROWID` 影响的程序。有关更多信息，请参阅 AWS SCT 文档。 —或者— 在源 Oracle 数据库中，使用 `dba_source` 表的文本字段来标识使用 `ROWID` 功能的对象。	数据库管理员或开发人员

确定使用该ROWID属性的 Oracle 表。

使用 AWS Schema Conversion Tool (AWS SCT) 来识别具有 ROWID 功能的 Oracle 表。有关更多信息，请参阅 AWS SCT 文档。

—或者—

在 Oracle 中，使用 DBA_TAB_COLUMNS 视图来标识具有 ROWID 属性的表。这些字段可用于存储 10 字节的字母数字字符。确定用法，并将其转换为 VARCHAR 字段（如果适用）。

数据库管理员或开发人员

标识引用这些表的代码。

使用 AWS SCT 生成迁移评测报告来识别受 ROWID 影响的程序。有关更多信息，请参阅 AWS SCT 文档。

—或者—

在源 Oracle 数据库中，使用 dba_source 表的文本字段来标识使用 ROWID 功能的对象。

数据库管理员或开发人员

任务描述所需技能

任务	描述	所需技能
标识没有主键的表。	在源 Oracle 数据库中，使用 `DBA_CONSTRAINTS` 标识没有主键的表。这些信息将帮助您确定每个表的策略。例如： `select dt.* from dba_tables dt where not exists (select 1 from all_constraints ct where ct.owner = Dt.owner and ct.table_name = Dt.table_name and ct.constraint_type = 'P' ) and dt.owner = '{schema}'`	数据库管理员或开发人员

标识没有主键的表。

在源 Oracle 数据库中，使用 DBA_CONSTRAINTS 标识没有主键的表。这些信息将帮助您确定每个表的策略。例如：


select dt.*
from dba_tables dt
where not exists (select 1
                  from all_constraints ct
                  where ct.owner = Dt.owner
                    and ct.table_name = Dt.table_name
                    and ct.constraint_type = 'P'
                  )
and dt.owner = '{schema}'

数据库管理员或开发人员

任务	描述	所需技能
对具有已定义主键或逻辑主键的表应用更改。	对其他信息部分中显示的应用程序和数据库代码进行更改，以使用唯一主键或逻辑主键来标识表中的行。	数据库管理员或开发人员
向没有定义主键或逻辑主键的表添加附加字段。	添加 `GENERATED ALWAYS AS IDENTITY` 类型的属性。对其他信息部分中显示的应用程序和数据库代码进行更改。	数据库管理员或开发人员
如有必要，请添加索引。	为附加字段或逻辑主键添加索引以提高 SQL 性能。	数据库管理员或开发人员

其他信息

以下各节提供了 Oracle 和 PostgreSQL 代码示例来说明这三种方法。

场景 1：使用主唯一键

在以下示例中，您将创建表 testrowid_s1，并使用 emp_id 作为主键。

Oracle 代码：


create table testrowid_s1 (emp_id integer, name varchar2(10), CONSTRAINT testrowid_pk PRIMARY KEY (emp_id));
INSERT INTO testrowid_s1(emp_id,name) values (1,'empname1');
INSERT INTO testrowid_s1(emp_id,name) values (2,'empname2');
INSERT INTO testrowid_s1(emp_id,name) values (3,'empname3');
INSERT INTO testrowid_s1(emp_id,name) values (4,'empname4');
commit;

SELECT rowid,emp_id,name FROM testrowid_s1;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3pAAAAAAAMOAAA          1 empname1
AAAF3pAAAAAAAMOAAB          2 empname2
AAAF3pAAAAAAAMOAAC          3 empname3
AAAF3pAAAAAAAMOAAD          4 empname4

UPDATE testrowid_s1 SET name = 'Ramesh' WHERE rowid = 'AAAF3pAAAAAAAMOAAB' ;
commit;

SELECT rowid,emp_id,name FROM testrowid_s1;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3pAAAAAAAMOAAA          1 empname1
AAAF3pAAAAAAAMOAAB          2 Ramesh
AAAF3pAAAAAAAMOAAC          3 empname3
AAAF3pAAAAAAAMOAAD          4 empname4

PostgreSQL 代码：


CREATE TABLE public.testrowid_s1
(
    emp_id integer,
    name character varying,
    primary key (emp_id)
);

insert into public.testrowid_s1 (emp_id,name) values 
(1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4');

select emp_id,name from testrowid_s1;
 emp_id |   name   
--------+----------
      1 | empname1
      2 | empname2
      3 | empname3
      4 | empname4

update testrowid_s1 set name = 'Ramesh' where emp_id = 2 ;

select emp_id,name from testrowid_s1;
 emp_id |   name   
--------+----------
      1 | empname1
      3 | empname3
      4 | empname4
      2 | Ramesh

场景 2：使用逻辑主键

在以下示例中，您将创建表 testrowid_s2，并使用 emp_id 作为逻辑主键。

Oracle 代码：


create table testrowid_s2 (emp_id integer, name varchar2(10) );
INSERT INTO testrowid_s2(emp_id,name) values (1,'empname1');
INSERT INTO testrowid_s2(emp_id,name) values (2,'empname2');
INSERT INTO testrowid_s2(emp_id,name) values (3,'empname3');
INSERT INTO testrowid_s2(emp_id,name) values (4,'empname4');
commit;

SELECT rowid,emp_id,name FROM testrowid_s2;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3rAAAAAAAMeAAA          1 empname1
AAAF3rAAAAAAAMeAAB          2 empname2
AAAF3rAAAAAAAMeAAC          3 empname3
AAAF3rAAAAAAAMeAAD          4 empname4

UPDATE testrowid_s2 SET name = 'Ramesh' WHERE rowid = 'AAAF3rAAAAAAAMeAAB' ;
commit;

SELECT rowid,emp_id,name FROM testrowid_s2;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3rAAAAAAAMeAAA          1 empname1
AAAF3rAAAAAAAMeAAB          2 Ramesh
AAAF3rAAAAAAAMeAAC          3 empname3
AAAF3rAAAAAAAMeAAD          4 empname4

PostgreSQL 代码：


CREATE TABLE public.testrowid_s2
(
    emp_id integer,
    name character varying
);

insert into public.testrowid_s2 (emp_id,name) values 
(1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4');

select emp_id,name from testrowid_s2;
 emp_id |   name   
--------+----------
      1 | empname1
      2 | empname2
      3 | empname3
      4 | empname4

update testrowid_s2 set name = 'Ramesh' where emp_id = 2 ;

select emp_id,name from testrowid_s2;
 emp_id |   name   
--------+----------
      1 | empname1
      3 | empname3
      4 | empname4
      2 | Ramesh

场景 3：使用标识属性

在以下示例中，您将使用标识属性创建不带主键的表 testrowid_s3。

Oracle 代码：


create table testrowid_s3 (name varchar2(10));
INSERT INTO testrowid_s3(name) values ('empname1');
INSERT INTO testrowid_s3(name) values ('empname2');
INSERT INTO testrowid_s3(name) values ('empname3');
INSERT INTO testrowid_s3(name) values ('empname4');
commit;

SELECT rowid,name FROM testrowid_s3;
ROWID              NAME
------------------ ----------
AAAF3sAAAAAAAMmAAA empname1
AAAF3sAAAAAAAMmAAB empname2
AAAF3sAAAAAAAMmAAC empname3
AAAF3sAAAAAAAMmAAD empname4

UPDATE testrowid_s3 SET name = 'Ramesh' WHERE rowid = 'AAAF3sAAAAAAAMmAAB' ;
commit;

SELECT rowid,name FROM testrowid_s3;
ROWID              NAME
------------------ ----------
AAAF3sAAAAAAAMmAAA empname1
AAAF3sAAAAAAAMmAAB Ramesh
AAAF3sAAAAAAAMmAAC empname3
AAAF3sAAAAAAAMmAAD empname4

PostgreSQL 代码：


CREATE TABLE public.testrowid_s3
(
    rowid_seq bigint generated always as identity,
    name character varying
);

insert into public.testrowid_s3 (name) values 
('empname1'),('empname2'),('empname3'),('empname4');

select rowid_seq,name from testrowid_s3;
 rowid_seq |   name   
-----------+----------
         1 | empname1
         2 | empname2
         3 | empname3
         4 | empname4

update testrowid_s3 set name = 'Ramesh' where rowid_seq = 2 ;

select rowid_seq,name from testrowid_s3;
 rowid_seq |   name   
-----------+----------
         1 | empname1
         3 | empname3
         4 | empname4
         2 | Ramesh

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

将 Oracle 迁移 PeopleSoft 到亚马逊 RDS 定制版

将 Oracle 错误代码迁移到 Amazon Aurora PostgreSQL-Compatible 数据库

将 Oracle ROWID 功能迁移到 AWS 上的 PostgreSQL

Summary

先决条件和限制

架构

工具

操作说明

相关资源

其他信息