本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。 # 将 Oracle ROWID 功能迁移到 AWS 上的 PostgreSQL *Rakesh Raghav 和 Ramesh Pathuri，Amazon Web Services* ## Summary 此模式描述了将甲骨文数据库中的`ROWID`伪列功能迁移到适用于 PostgreSQL 的亚马逊关系数据库服务（亚马逊 RDS）、兼容亚马逊 Aurora PostgreSQL 的版本或亚马逊弹性计算云（亚马逊）中的 PostgreSQL 数据库的选项。 EC2 在 Oracle 数据库中，`ROWID` 伪列是表中某一行的物理地址。即使表中不存在主键，该伪列也用于唯一标识行。PostgreSQL 有一个名为 `ctid` 的类似伪列，但它不能用作 `ROWID`。正如 [PostgreSQL 文档](https://www.postgresql.org/docs/current/ddl-system-columns.html)中所述，`ctid` 可能会在更新后或每次 `VACUUM` 进程后发生变化。 您可以通过三种方式在 PostgreSQL 中创建 `ROWID` 伪列功能： + 使用主键列代替 `ROWID` 来标识表中的一行。 + 在表中使用逻辑 primary/unique 键（可能是复合键）。  + 添加一个包含自动生成值的列，并使其成为模仿`ROWID`的 primary/unique 关键。 此模式将引导您完成所有三种实现，并描述每个选项的优缺点。 ## 先决条件和限制 **先决条件** + 一个有效的 Amazon Web Services account + 程序 Language/PostgreSQL (pl/pgSQL) 编码专业知识 + 源 Oracle 数据库 + 兼容 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 的集群，或者托管 PostgreSQL 数据库的实 EC2 例 **限制** + 此模式为 `ROWID` 功能提供了解决方法。PostgreSQL 不提供与 Oracle 数据库中的 `ROWID` 等效的项。 **产品版本** + PostgreSQL 11.9 或更高版本 ## 架构 **源技术堆栈** + Oracle Database **目标技术堆栈** + 兼容 Aurora PostgreSQL、适用于 PostgreSQL 的亚马逊 RDS 或者带有 PostgreSQL 数据库的实例 EC2 ![在 AWS 上将 Oracle 数据库转换为 PostgreSQL](http://docs.aws.amazon.com/zh_cn/prescriptive-guidance/latest/patterns/images/pattern-img/9a2ce994-4f68-4975-aab2-796cc20a3c82/images/6e7c2ef6-f440-476a-9003-f1f166718e15.png) **实施选项** 有三个选项可以解决 PostgreSQL 中缺少 `ROWID` 支持的问题，具体取决于您的表是否具有主键或唯一索引、逻辑主键或标识属性。您的选择取决于您的项目时间表、当前的迁移阶段以及对应用程序和数据库代码的依赖关系。 | | | 选项 | 描述 | 优点 | 缺点 | | --- |--- |--- |--- | | **主键或唯一索引** | 如果您的 Oracle 表具有主键，您可以使用该键的属性来唯一标识一行。  | 不依赖于专有数据库功能。对性能的影响最小，因为主键字段已编制索引。 | 需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到主键字段。  | | **逻辑 primary/unique 密钥** | 如果您的 Oracle 表具有逻辑主键，您可以使用该键的属性来唯一标识一行。逻辑主键由可以唯一标识行的一个属性或一组属性组成，但不会通过约束在数据库上强制执行。 | 不依赖于专有数据库功能。 | 需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到主键字段。如果未对逻辑主键的属性编制索引，则会对性能产生重大影响。不过，您可以添加唯一索引来防止出现性能问题。 | | **标识属性** | 如果您的 Oracle 表没有主键，您可以创建一个附加字段作为 `GENERATED ALWAYS AS IDENTITY`。每当将数据插入到表中时，此属性都会生成一个唯一值，因此它可用于唯一标识数据操作语言 （DML） 操作的行。 | 不依赖于专有数据库功能。PostgreSQL 数据库会填充该属性并保持其唯一性。 | 需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到标识属性。如果未对附加字段编制索引，则会对性能产生重大影响。不过，您可以添加一个索引来防止出现性能问题。 | ## 工具 + [Amazon Relational Database Service（Amazon RDS）for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) 可帮助您在 Amazon Web Services Cloud 中设置、操作和扩展 PostgreSQL 关系数据库。 + [Amazon Aurora PostgreSQL 兼容版](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)是一个完全托管的、与 ACID 兼容的关系数据库引擎，可帮助您建立、运行和扩展 PostgreSQL 部署。 + [AWS 命令行界面（AWS CLI）](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)是一种开源工具，它可帮助您通过命令行 Shell 中的命令与 Amazon Web Services 交互。在此模式中，您可以使用 AWS CLI 通过 **pgAdmin** 运行 SQL 命令。 + [pgAdmin](https://www.pgadmin.org/) 是一种适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。 + [AWS Schema Conversion Tool（AWS SCT）](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。 ## 操作说明 ### 标识源表 | Task | 说明 | 所需技能 | | --- | --- | --- | | 识别使用 `ROWID` 属性的 Oracle 表。 | 使用 AWS Schema Conversion Tool (AWS SCT) 来识别具有 `ROWID` 功能的 Oracle 表。有关更多信息，请参阅 [AWS SCT 文档](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.ToPostgreSQL.html#CHAP_Source.Oracle.ToPostgreSQL.ConvertRowID)。
—或者—
在 Oracle 中，使用 `DBA_TAB_COLUMNS` 视图来标识具有 `ROWID` 属性的表。这些字段可用于存储 10 字节的字母数字字符。确定用法，并将其转换为 `VARCHAR` 字段（如果适用）。 | 数据库管理员或开发人员 | | 标识引用这些表的代码。 | 使用 AWS SCT 生成迁移评测报告来识别受 `ROWID` 影响的程序。有关更多信息，请参阅 [AWS SCT 文档](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.html)。
—或者—
在源 Oracle 数据库中，使用 `dba_source` 表的文本字段来标识使用 `ROWID` 功能的对象。 | 数据库管理员或开发人员 | ### 确定主键用法 | Task | 说明 | 所需技能 | | --- | --- | --- | | 标识没有主键的表。 | 在源 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}'
| 数据库管理员或开发人员 | ### 确定并应用解决方案 | Task | 说明 | 所需技能 | | --- | --- | --- | | 对具有已定义主键或逻辑主键的表应用更改。 | 对[其他信息](#migrate-oracle-rowid-functionality-to-postgresql-on-aws-additional)部分中显示的应用程序和数据库代码进行更改，以使用唯一主键或逻辑主键来标识表中的行。 | 数据库管理员或开发人员 | | 向没有定义主键或逻辑主键的表添加附加字段。 | 添加 `GENERATED ALWAYS AS IDENTITY` 类型的属性。对[其他信息](#migrate-oracle-rowid-functionality-to-postgresql-on-aws-additional)部分中显示的应用程序和数据库代码进行更改。 | 数据库管理员或开发人员 | | 如有必要，请添加索引。 | 为附加字段或逻辑主键添加索引以提高 SQL 性能。 | 数据库管理员或开发人员 | ## 相关资源 + [PostgreSQL CTID](https://www.postgresql.org/docs/current/ddl-system-columns.html)（PostgreSQL 文档） + [生成的列](https://www.postgresql.org/docs/current/ddl-generated-columns.html)（PostgreSQL 文档） + [ROWID 伪列](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWID-Pseudocolumn.html#GUID-F6E0FBD2-983C-495D-9856-5E113A17FAF1)（Oracle 文档） ## 附加信息 以下各节提供了 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 ```