将 Oracle® 数据库用户和架构迁移到 Cloud SQL for PostgreSQL

本文档是系列文章中的一篇，该系列提供了关键的信息和指导来帮助规划和执行 Oracle® 11g/12c 数据库到 Cloud SQL for PostgreSQL 12 版的迁移。本文档介绍 Oracle® 与 Cloud SQL for PostgreSQL 的基本差异，因为它们与创建用户、架构、表、索引和视图有关。

除了设置简介部分之外，本系列文章还包括以下部分：

将 Oracle 用户迁移到 Cloud SQL for PostgreSQL：术语和功能
将 Oracle 用户迁移到 Cloud SQL for PostgreSQL：数据类型、用户和表
将 Oracle 用户迁移到 Cloud SQL for PostgreSQL：查询、存储过程、函数和触发器
将 Oracle 用户迁移到 Cloud SQL for PostgreSQL：安全、操作、监控和日志记录
将 Oracle 数据库用户和架构迁移到 Cloud SQL for PostgreSQL（本文档）

Oracle 与 Cloud SQL for PostgreSQL 之间的术语差异

Oracle 和 Cloud SQL for PostgreSQL 具有实例、数据库、用户和架构的不同架构和术语。有关这些差异的摘要，请参阅本系列的术语部分。

导出 Oracle 配置

计划迁移到 Cloud SQL for PostgreSQL 的首要步骤之一是查看源 Oracle 数据库上的现有参数设置。有关内存分配、字符集和存储参数的设置特别有用，因为它们可以通知 Cloud SQL for PostgreSQL 目标环境的初始配置和大小调整。有多种方法可用于提取 Oracle 参数设置。以下是几种常见的方法：

自动工作负载代码库 (AWR) 报告包含资源分配数据（CPU、RAM）、实例参数配置和最大活跃会话数。
DBA_HIST、V$OSSTAT 和 V$LICENSE，用于 CPU 使用率详情。
V$PARAMETER 视图，用于数据库配置参数。
V$NLS_PARAMETERS 视图，用于数据库语言参数。
DBA_DATA_FILES 视图，用于计算数据库存储空间大小。
Oracle SPFILE，用于数据库实例配置。
作业调度器工具（例如 crontab），用于识别应考虑的日常备份或维护期。

在 Cloud SQL for PostgreSQL 中导入和配置用户

大体上来说，每个 Oracle 架构都应在 PostgreSQL 中创建为自己的架构。在 Oracle 数据库中，用户与架构同义。这意味着，您在创建用户时会创建架构。用户和架构之间总是存在 1:1 的关系。在 PostgreSQL 中，用户和架构是单独创建的。可以在不创建相应架构的情况下创建用户。如需在 PostgreSQL 中保持相同的 Oracle 用户或架构结构，您可为每个用户创建架构。

下表提供了转换示例：

操作类型	数据库类型	命令比较
创建用户和架构	`Oracle`	`CREATE USER username IDENTIFIED BY password;`
	`PostgreSQL`	用户和架构是 PostgreSQL 中的不同概念，因此需要两个单独的 `CREATE` 语句 `CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;`
分配角色	`Oracle`	`GRANT CONNECT TO username;`
	`PostgreSQL`	`GRANT pg_monitor TO username;`
授予权限	`Oracle`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
	`PostgreSQL`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
撤消权限	`Oracle`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
	`PostgreSQL`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
授予 DBA/超级用户	`Oracle`	`GRANT DBA TO username;`
	`PostgreSQL`	`GRANT cloudsqlsuperuser TO username;`
删除用户	`Oracle`	`DROP USER username CASCADE;`
	`PostgreSQL`	用户和架构是 PostgreSQL 中的不同概念，因此需要两个单独的 `DROP` 语句 `DROP USER username; DROP SCHEMA schema_name CASCADE;`
用户元数据	`Oracle`	`DBA_USERS`
	`PostgreSQL`	`pg_catalog.pg_user`
权限元数据	`Oracle`	`DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS`
	`PostgreSQL`	`pg_catalog.pg_roles`
CLI 连接字符串	`Oracle`	`sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid`
	`PostgreSQL`	不使用密码提示： `PGPASSWORD=password psql -h hostname -U username -d database_name` 使用密码提示： `psql -h hostname -U username -W -d database_name`

Oracle 12c 数据库用户：

Oracle 12c 中有两种类型的用户：普通用户和本地用户。普通用户是在根 CDB（包括 PDB）中创建的。它们通过用户名中的 C## 前缀识别。本地用户仅在特定的 PDB 中创建。您可以在多个 PDB 中创建具有相同用户名的不同数据库用户。从 Oracle 12c 迁移到 PostgreSQL 时，请修改用户和权限以适合 PostgreSQL 的架构。下面提供两个常见的示例以说明它们之间的差异：

# Oracle local user
SQL> ALTER SESSION SET CONTAINER=pdb;
SQL> CREATE USER username IDENTIFIED BY password QUOTA 50M ON USERS;

# PostgreSQL user for a single database and schema
postgres=> CREATE USER username WITH PASSWORD 'password';
postgres=> GRANT CONNECT TO DATABASE database_name TO username;
postgres=> GRANT USAGE ON SCHEMA schema_name TO username;
postgres=> -- Optionally, grant object privileges in the schema
postgres=> GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL PROCEDURES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL ROUTINES IN SCHEMA schema_name TO username;

# Oracle common user
SQL> CREATE USER c##username IDENTIFIED BY password CONTAINER=ALL;

# PostgreSQL user with permissions for all database (use the local user script above and repeat it for each database and schema)

通过 Google Cloud 控制台管理用户

如需查看 Cloud SQL for PostgreSQL 当前配置的用户，请转到 Google Cloud 控制台中的以下页面：

Google Cloud > 存储 > SQL > 实例 > 用户

“用户”页面的屏幕截图。

导入表和视图定义

Oracle 和 PostgreSQL 在区分大小写方面有所不同。Oracle 名称不区分大小写。PostgreSQL 名称不区分大小写，用双引号括起时除外。许多适用于 Oracle 的架构导出和 SQL 生成工具（例如 DBMS_METADATA.GET_DDL）都会自动向对象名称添加英文双引号。这些引号可能导致迁移后的各种问题。我们建议您先从数据定义语言 (DDL) 语句中移除对象名称前后的所有英文引号，然后才在 PostgreSQL 中创建对象。

创建表的语法

将表从 Oracle 转换为 PostgreSQL 数据类型时，第一步是从来源数据库中提取 Oracle 创建表语句。以下示例查询从 HR 架构中提取位置表的 DDL：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS') FROM DUAL;

CREATE TABLE "HR"."LOCATIONS"
   (  "LOCATION_ID" NUMBER(4,0),
  "STREET_ADDRESS" VARCHAR2(40),
  "POSTAL_CODE" VARCHAR2(12),
  "CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE,
  "STATE_PROVINCE" VARCHAR2(25),
  "COUNTRY_ID" CHAR(2),
  CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID")
...
      CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE

完整输出包括存储元素、索引和表空间信息，由于 PostgreSQL CREATE TABLE 语句不支持这些附加元素，因此予以忽略。

提取 DDL 后，移除英文引号括住的名称，并根据 Oracle 到 PostgreSQL 数据类型的转换表执行表转换。检查每一列数据类型以了解它是否可以按原样转换，或者如果不受支持，则根据转换表选择不同的数据类型。例如，以下所示为位置表的转换后 DDL。

CREATE TABLE HR.LOCATIONS (
  LOCATION_ID NUMERIC(4,0),
  STREET_ADDRESS VARCHAR(40),
  POSTAL_CODE VARCHAR(12),
  CITY VARCHAR(30) CONSTRAINT LOC_CITY_NN NOT NULL,
  STATE_PROVINCE VARCHAR(25),
  COUNTRY_ID CHAR(2),
  CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID),
  CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID)
REFERENCES HR.COUNTRIES (COUNTRY_ID)
)

Create Table As Select (CTAS)

CREATE TABLE AS SELECT (CTAS) 语句用于基于现有表创建新表。请注意，只会复制列名称和列数据类型，不会复制限制条件和索引。PostgreSQL 支持适用于 CTAS 功能的 ANSI SQL 标准，并且与 Oracle CTAS 语句兼容。

Oracle 12c 不可见列

PostgreSQL 不支持不可见列。如需解决方法，请创建一个仅包含可见列的视图。

表限制条件

Oracle 提供六种类型的表限制条件，您可以在创建表时定义它们，也可以在创建表之后使用 ALTER TABLE 命令定义它们。Oracle 限制条件类型为 PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL 和 REF。此外，Oracle 允许用户通过以下选项来控制限制条件的状态：

INITIALLY IMMEDIATE：在每个后续 SQL 语句（即默认状态）结束时检查限制条件。
DEFERRABLE/NOT DEFERRABLE：在提交 COMMIT 语句之前，允许在后续事务中使用 SET CONSTRAINT 子句。
INITIALLY DEFERRED：在后续事务结束时检查限制条件。
VALIDATE/NO VALIDATE：检查（或故意不检查）新行或修改过的行是否存在错误。这些参数取决于限制条件是 ENABLED 还是 DISABLED。
ENABLED/DISABLED：指定是否应在创建后强制执行限制条件（默认为 ENABLED）

PostgreSQL 还支持六种类型的表限制条件：PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL 和 EXCLUDE。但是，Oracle 和 PostgreSQL 限制条件类型之间存在一些显著差异，其中包括：

PostgreSQL 不支持 Oracle 的 REF 限制条件。
PostgreSQL 不会在外键限制条件的引用列上自动创建索引。如果需要索引，则需要对引用列单独执行 CREATE INDEX 语句。
PostgreSQL 不支持 Oracle 的 ON DELETE SET NULL 子句。当删除父表中的记录时，该子句会指示 Oracle 将子表中的任何依赖值设置为 NULL。
不支持针对 VIEWS 的限制条件，但 CHECK OPTION 除外。
PostgreSQL 不支持停用限制条件。使用 ALTER TABLE 语句添加新的外键或检查限制条件时，PostgreSQL 支持 NOT VALID 选项。此选项指示 PostgreSQL 跳过子表中现有记录的参照完整性检查。

下表概括了 Oracle 与 PostgreSQL 的限制条件类型之间的主要差异：

Oracle 限制条件类型	Cloud SQL for PostgreSQL 支持	Cloud SQL for PostgreSQL 等效项
`PRIMARY KEY`	是	`PRIMARY KEY`
`FOREIGN KEY`	是	使用与 Oracle 相同的 ANSI SQL 语法。使用 `ON DELETE` 子句处理 `FOREIGN KEY` 父记录删除的情况。PostgreSQL 提供了三个选项来处理从由 `FOREIGN KEY` 限制条件引用的父表和子表中删除数据的情况： `ON DELETE CASCADE` `ON DELETE RESTRICT` `ON DELETE NO ACTION` PostgreSQL 不支持 Oracle 的 `ON DELETE SET NULL` 子句。使用 `ON UPDATE` 子句处理 `FOREIGN KEY` 父记录更新的情况。 PostgreSQL 提供了三个选项来处理 `FOREIGN KEY` 限制条件更新事件： `ON UPDATE CASCADE` `ON UPDATE RESTRICT` `ON UPDATE NO ACTION` PostgreSQL 不会在外键限制条件的引用列上自动创建索引。
`UNIQUE`	是	默认创建 `UNIQUE` 索引。
`CHECK`	是	`CHECK`
`NOT NULL`	是	`NOT NULL`
`REF`	否	不受支持。
`DEFERRABLE/NOT DEFERRABLE`	是	`DEFERRABLE/NOT DEFERRABLE`
`INITIALLY IMMEDIATE`	是	`INITIALLY IMMEDIATE`
`INITIALLY DEFERRED`	是	`INITIALLY DEFERRED`
`VALIDATE/NO VALIDATE`	否	不受支持。
`ENABLE/DISABLE`	否	默认处于启用状态。使用 `ALTER TABLE` 语句向表添加新的外键或检查约束以跳过对现有记录的参照完整性检查时，使用 `NOT VALID` 选项。
对视图的限制条件	否	不受支持，但 `VIEW WITH CHECK OPTION` 除外。
限制条件元数据	Oracle	`DBA_CONSTRAINTS`
	PostgreSQL	`INFORMATION_SCHEMA.TABLE_CONSTRAINTS`

虚拟列和生成的列

Oracle 的虚拟列基于其他列的计算结果。它们显示为常规列，但它们的值由 Oracle 数据库引擎实时计算所得，而不是存储在数据库中。虚拟列可与限制条件、索引、表分区和外键一起使用，但无法通过数据操纵语言 (DML) 操作操纵。

PostgreSQL 生成的列在功能方面与 Oracle 的虚拟列功能相当。但与 Oracle 不同的是，PostgreSQL 中生成的列会予以存储并且您必须为每个生成的列指定数据类型，这意味着它们会占用存储空间，就如它们是正常列一样。

Oracle 中的虚拟列的示例：

SQL> CREATE TABLE PRODUCTS (
        PRODUCT_ID     INT PRIMARY KEY,
        PRODUCT_TYPE   VARCHAR2(100) NOT NULL,
        PRODUCT_PRICE  NUMBER(6,2) NOT NULL,
        PRICE_WITH_TAX AS (ROUND(PRODUCT_PRICE * 1.01, 2))
);

SQL> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE)
     VALUES(1, 'A', 99.99);

SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID PRODUCT_TYPE         PRODUCT_PRICE PRICE_WITH_TAX
---------- -------------------- ------------- --------------
         1 A                            99.99         100.99

PostgreSQL 中的等效示例：

postgres=> CREATE TABLE PRODUCTS (
postgres(>         PRODUCT_ID     INT PRIMARY KEY,
postgres(>         PRODUCT_TYPE   VARCHAR(100) NOT NULL,
postgres(>         PRODUCT_PRICE  NUMERIC(6,2) NOT NULL,
postgres(>         PRICE_WITH_TAX NUMERIC GENERATED ALWAYS AS (ROUND(PRODUCT_PRICE * 1.01, 2)) STORED
postgres(> );

postgres=> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE) VALUES(1, 'A', 99.99);

postgres=> SELECT * FROM PRODUCTS;

 product_id | product_type | product_price | price_with_tax
------------+--------------+---------------+----------------
          1 | A            |         99.99 |         100.99
(1 row)

表索引

Oracle 和 PostgreSQL 提供各种索引算法和索引类型，可用于多种应用。下面列出了 PostgreSQL 中的可用索引算法：

索引算法	说明
B 树	PostgreSQL 的默认索引类型，用于加快相等和范围查询的速度。支持所有基本数据类型，可用于检索 `NULL` 值默认情况下，索引值按升序排序，但也可以配置为按降序排序。
哈希	用于加快相等搜索的速度效率比 B 树索引高，但仅限于处理相等搜索
GIN	反向树索引在处理包含多个组件值的列（例如数组和文本）时，效率比 B 树索引高
GiST	不是单个索引类型，而是用于定义索引的基础架构，可支持比常规 B 树索引多的比较运算符。在需要优化“最邻近”搜索时，对于几何图形数据非常有用
SP-GiST	与 GiST 类似，SP-GiST 是用户定义的索引策略的基础架构允许使用各种不同的非均衡数据结构，例如四叉树在 Cloud SQL for PostgreSQL 中不可用
BRIN	块范围索引存储表的物理块范围摘要对于采用线性排序顺序的列适用于大型表的范围查询

下表比较了 Oracle 和 PostgreSQL 的索引类型：

Oracle 索引	说明	受 PostgreSQL 支持	PostgreSQL 等效项
位图索引	为每个索引键存储位图，最适合用于为 OLAP 工作负载提供快速数据检索。	否	不适用
B 树索引	最常见的索引类型，非常适合各种工作负载，可以在 `ASC\|DESC` 排序中配置。	是	B 树索引
复合索引	创建两个或更多个列，以提高数据检索的性能。索引内的列排序决定了访问路径。	是	多列索引创建多列索引时，最多可以指定 32 列。
基于函数的索引	存储应用于表列值的函数的输出。	是	表达式上的索引
唯一索引	逐列针对索引值强制执行 `UNIQUE` 限制条件的 B 树索引。	是	唯一索引
应用领域索引	适用于将非关系型数据（如音频/视频数据、LOB 数据和其他非文本类型）编入索引。	否	不适用
不可见索引	此 Oracle 功能可让您在管理、维护和测试索引时，不会影响优化人员决策。	否	对于替代解决方案，您可以在读取副本上另外创建一个索引以用于测试，而不会影响正在进行的活动。
按索引组织的表	一种索引类型，用于控制在表中和索引级层存储数据的方式。	否	PostgreSQL 不支持按索引组织的表。`CLUSTER` 语句指示 PostgreSQL 根据指定的索引组织表存储。它的用途与 Oracle 的按索引组织的表类似。但是，聚簇是一次性操作，PostgreSQL 不会在后续更新中维护表的结构。需要手动执行定期聚类。
局部索引和全局索引	用于将 Oracle 数据库中分区表编入索引。每个索引都定义为 `LOCAL` 或 `GLOBAL`。	否	PostgreSQL 分区工作索引具有与 Oracle 本地索引相同的功能（即，索引是在分区级别定义的，不支持全局级别）。
分区表的部分索引 (Oracle 12c)	针对表分区的子集创建索引。支持 `LOCAL` 和 `GLOBAL`。	有	PostgreSQL 中的分区通过将子表附加到父表来实现。您只能在子表的子集上创建索引。
`CREATE/DROP INDEX`	用于创建和删除索引的命令。	是	PostgreSQL 支持 `CREATE INDEX` 命令。它还支持 `ALTER TABLE tableName ADD INDEX indexName columnName`
`ALTER INDEX ... REBUILD`	重建索引，这可能会导致独占锁定索引表。	需要不同的语法	PostgreSQL 支持使用 `REINDEX` 语句重建索引。在执行此操作期间，表处于锁定状态，无法写入，并且只允许读取。
`ALTER INDEX ... REBUILD ONLINE`	重建索引而不针对表创建独占锁定。	需要不同的语法	PostgreSQL 支持使用 `REINDEX TABLE CONCURRENTLY` 语句进行并发索引重建。在此模式下，PostgreSQL 会尝试使用最小锁定来重新构建索引，但可能需要权衡更多时间和资源来完成重建。
索引压缩	用于减小索引物理大小的功能。	否	不适用
分配索引给表空间	创建一个索引表空间，其可以存储在与表数据不同的磁盘上，以减少磁盘 I/O 瓶颈。	否	虽然 PostgreSQL 允许在用户定义的表空间中创建索引，但您无法在 Cloud SQL for PostgreSQL 中创建表空间，并且必须在默认表空间中构建索引。
索引元数据（表/视图）	Oracle	`DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS`
	PostgreSQL	`pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class`

索引转换注意事项

在大多数情况下，Oracle 索引可以轻松转换为 PostgreSQL 的 B 树索引，因为此类索引是最常用的索引类型。与 Oracle 数据库一样，系统会在表的 PRIMARY KEY 字段中自动创建索引。同样，系统会自动为具有 UNIQUE 限制条件的字段创建 UNIQUE 索引。此外，二级索引也是使用标准 CREATE INDEX 语句创建的。

以下示例说明了如何将具有多个编入索引的字段的 Oracle 表转换为 PostgreSQL：

SQL> CREATE TABLE ORA_IDX_TO_PG (
        col1 INT PRIMARY KEY,
        col2 VARCHAR2(60),
        col3 DATE,
        col4 CLOB,
        col5 VARCHAR2(20)
      );

-- Single-field index
SQL> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index
SQL> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index
SQL> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(col3);

-- Function-based index
SQL> CREATE INDEX idx_func_col3 ON
        ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB index
SQL> CREATE INDEX idx_col4 ON
       ora_idx_to_pg(col4) INDEXTYPE IS CTXSYS.CONTEXT;

-- Invisible index
SQL> CREATE INDEX idx_col5_inv ON
        ora_idx_to_pg(col5) INVISIBLE;

-- Drop index
SQL> DROP INDEX idx_col5_inv;

postgres=> CREATE TABLE ORA_IDX_TO_PG (
postgres(> col1 INT PRIMARY KEY,
postgres(> col2 VARCHAR(60),
postgres(> col3 DATE,
postgres(> col4 TEXT,
postgres(> col5 VARCHAR(20)
postgres(> );

-- Single index (supported)
postgres=> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index (supported)
postgres=> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index (supported)
postgres=> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(COL3);

-- Function-based index (supported)
postgres=> CREATE INDEX idx_func_col3 ON
postgres->         ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB (Supported, but requires different syntax. See Full Text Search for details)
postgres=> CREATE INDEX idx_col4 ON ora_idx_to_pg
postgres->         USING GIN (to_tsvector('english', col4));

-- Invisible index (not supported)
-- Optional - create the index as a B-tree index
postgres=> CREATE INDEX idx_col5 ON ora_idx_to_pg(col5);

-- Drop index
postgres=> DROP INDEX idx_col2;

SQL> SELECT ui.table_name,
            ui.index_name,
            ui.index_type,
            ic.column_name
     FROM user_indexes ui JOIN user_ind_columns ic
     ON ui.index_name = ic.index_name
     WHERE ui.table_name = 'ORA_IDX_TO_PG'
     ORDER BY 4;

postgres=> select distinct
postgres->     t.relname as table_name,
postgres->     i.relname as index_name,
postgres-> pg_get_indexdef(ix.indexrelid) index_definition
postgres-> from
postgres->     pg_class t,
postgres->     pg_class i,
postgres->     pg_index ix
postgres-> where
postgres->     t.oid = ix.indrelid
postgres->     and i.oid = ix.indexrelid
postgres->     and t.relname = 'ora_idx_to_pg'
postgres-> order by
postgres->     t.relname,
postgres->     i.relname;

-- OR Use psql \d command:
postgres=> \d ora_idx_to_pg
                  Table "public.ora_idx_to_pg"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 col1   | integer               |           | not null |
 col2   | character varying(60) |           |          |
 col3   | date                  |           |          |
 col4   | text                  |           |          |
 col5   | character varying(20) |           |          |
Indexes:
    "ora_idx_to_pg_pkey" PRIMARY KEY, btree (col1)
    "idx_col2" btree (col2)
    "idx_col4" gin (to_tsvector('english'::regconfig, col4))
    "idx_col5" btree (col5)
    "idx_cols3_2" btree (col3 DESC, col2)
    "idx_func_col3" btree (date_part('month'::text, col3))

postgres=>

表分区

Oracle 和 PostgreSQL 均提供拆分大型表的分区功能。这是通过将表物理细分为更小的部分来实现的，其中每个部分包含行的水平子集。此分区表称为父表，它的行物理存储在其分区中。虽然 PostgreSQL 不支持所有 Oracle 分区类型，但 PostgreSQL 支持最常见的类型。

以下部分介绍了 PostgreSQL 支持的分区类型，并通过一个示例说明如何创建与该类型对应的分区。

RANGE 分区

这种类型的分区根据给定范围内的列值将行分配给分区。每个分区都包含分区表达式值在给定范围内的行。请务必注意，范围不会跨分区重叠。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (store_id);

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (MINVALUE) TO (6);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (6) TO (11);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES FROM (11) TO (16);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES FROM (16) TO (21);

LIST 分区

与 RANGE 分区类似，LIST 分区根据一组预定义值内的列值将行分配给分区。系统会针对 LIST 分区明确列出每个分区中显示的键值。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY LIST (store_id);

CREATE TABLE employees_pNorth PARTITION OF employees
 FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
 FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
 FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
 FOR VALUES IN (7,8,15);

HASH 分区

如果目标是要在所有分区之间平均分配数据，则 HASH 分区最合适目。列值（或基于要哈希的列值的表达式）和行值分配给与该哈希值对应的分区。哈希值必须唯一分配给分区，并且所有插入的值必须映射到一个分区。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY HASH (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

多级层分区

多级层分区是一种为单个表创建分区层次结构的方法。每个分区可进一步分为许多不同的分区。子分区的数量可能因分区而异。

示例

CREATE TABLE sales (
 Saleid    INT,
 sale_date DATE,
 cust_code VARCHAR(15),
 income    DECIMAL(8,2))
PARTITION BY RANGE(date_part('year', sale_date));

CREATE TABLE sales_2019 PARTITION OF sales
 FOR VALUES FROM (2019) TO (2020)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2019_q1 PARTITION OF sales_2019
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2019_q2 PARTITION OF sales_2019
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2019_q3 PARTITION OF sales_2019
 FOR VALUES FROM (7) TO (10);
CREATE TABLE sales_2019_q4 PARTITION OF sales_2019
 FOR VALUES FROM (10) TO (13);

CREATE TABLE sales_2020 PARTITION OF sales
 FOR VALUES FROM (2020) TO (2021)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2020_q1 PARTITION OF sales_2020
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2020_q2 PARTITION OF sales_2020
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2020_h2 PARTITION OF sales_2020
 FOR VALUES FROM (7) TO (13);

附加或分离分区

在 PostgreSQL 中，您可以在父表中添加或移除分区。已分离的分区稍后可以重新附加到同一个表。此外，您可以在重新附加分区时指定新的分区条件，这样就可以调整分区边界。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (2010) TO (2015);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (2015) TO (2020);

-- changing partition boundaries
BEGIN TRANSACTION;
ALTER TABLE employees DETACH PARTITION employees_p1;
ALTER TABLE employees ATTACH PARTITION employees_p1 FOR VALUES FROM (2015) TO (2022);
COMMIT TRANSACTION;

下表介绍了 Oracle 和 Cloud SQL for PostgreSQL 分区类型等效之处以及建议转换之处：

Oracle 分区类型	受 PostgreSQL 支持	PostgreSQL 实现
`RANGE` 分区	是	`PARTITION BY RANGE`
`LIST` 分区	是	`PARTITION BY LIST`
`HASH` 分区	是	`PARTITION BY HASH`
`SUB-PARTITIONING`	是	多级层分区
间隔分区	否	不支持
分区顾问	否	不支持
偏好设置	否	不支持
基于列的虚拟分区	否	如需解决此问题，请考虑直接使用虚拟列表达式进行分区： `CREATE TABLE users ( id INT, username VARCHAR(20), first_letter VARCHAR(1) GENERATED ALWAYS AS ( UPPER(SUBSTR(TRIM(username), 1, 1)) ) STORED ) PARTITION BY LIST (UPPER(SUBSTR(TRIM(username), 1, 1)));`
自动列表分区	否	不支持
拆分t 分区	否	如需解决此问题，请考虑分离或附加表分区以调整分区边界
交换分区	是	`DETACH / ATTACH PARTITION`
多类型分区（复合分区）	是	多级层分区
分区元数据	Oracle	`DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS`
	PostgreSQL	`pg_catalog.pg_class pg_catalog.pg_partitioned_table`

下面的示例并排比较了在两个平台上创建表分区的情况。请注意，PostgreSQL 不支持在 CREATE TABLE 命令的 PARTITIONS 子句中引用表空间。

Oracle 实现

CREATE TABLE employees ( empid NUMBER, fname VARCHAR2(30), lname VARCHAR2(30), hired DATE, separated DATE, job_code NUMBER, store_id NUMBER) PARTITION BY LIST (store_id) ( PARTITION employees_pNorth VALUES (3,5,6) TABLESPACE users, PARTITION employees_pEast VALUES (1,2,10) TABLESPACE users, PARTITION employees_pWest VALUES (4,12,13) TABLESPACE users, PARTITION employees_pCnrl VALUES (7,8,15) TABLESPACE users );

PostgreSQL 实现

CREATE TABLE employees ( empid INT, fname VARCHAR(30), lname VARCHAR(30), hired DATE, separated DATE, job_code INT, store_id INT) PARTITION BY LIST (store_id); CREATE TABLE employees_pNorth PARTITION OF employees FOR VALUES IN (3,5,6); CREATE TABLE employees_pEast PARTITION OF employees FOR VALUES IN (1,2,10); CREATE TABLE employees_pWest PARTITION OF employees FOR VALUES IN (4,12,13); CREATE TABLE employees_pCnrl PARTITION OF employees FOR VALUES IN (7,8,15);

临时表

在 Oracle 数据库中，临时表称为 GLOBAL TEMPORARY TABLES，而在 PostgreSQL 中，它们简称为临时表。在这两个平台上，临时表的基本功能是相同的。不过，两者之间存在一些显著差异：

即使在数据库重启之后，Oracle 也会存储临时表结构供重复使用，而 PostgreSQL 仅在会话期间存储临时表。
具有相应权限的其他用户可以访问 Oracle 数据库中的临时表。相比之下，PostgreSQL 中的临时表只能在创建它的会话期间访问，除非临时表使用架构限定名称引用。
在 Oracle 数据库中，在指定表的内容是全局表还是会话特定表方面，GLOBAL 和 LOCAL 临时表之间有区别。在 PostgreSQL 中，GLOBAL 和 LOCAL 关键字出于兼容性原因受支持，但它们对数据的可见性没有影响。
如果在创建临时表时省略了 ON COMMIT 子句，则 Oracle 数据库中的默认行为是 ON COMMIT DELETE ROWS，这意味着 Oracle 会在每次提交后截断临时表。相比之下，在 PostgreSQL 中，默认行为是在每次提交后保留临时表中的行。

下表突出显示了 Oracle 和 Cloud SQL for PostgreSQL 的临时表之间的差异。

临时表功能	Oracle 实现	PostgreSQL 实现
语法	`CREATE GLOBAL TEMPORARY TABLE`	`CREATE TEMPORARY TABLE`
无障碍	可从多个会话中访问	仅可通过创建者的会话访问，除非使用架构限定的名称引用
索引支持	是	是
外键支持	是	是
保留 DDL	是	否
`ON COMMIT` 默认操作	删除记录	保留记录
`ON COMMIT PRESERVE ROWS`	是	是
`ON COMMIT DELETE ROWS`	是	是
`ON COMMIT DROP`	否	是
`ALTER TABLE` 支持	是	是
收集统计信息	`DBMS_STATS.GATHER_TABLE_STATS`	`ANALYZE`
Oracle 12c `GLOBAL_TEMP_` `TABLE_STATS`	`DBMS_STATS.SET_TABLE_PREFS`	`ANALYZE`

未使用的列

Oracle 将特定列标记为 UNUSED 的功能通常用于从表中移除列，而无需实际移除列数据。这是为了防止从大型表删除列时发生潜在的高负载。

在 PostgreSQL 中，删除大型列不会从物理存储空间中移除列数据，因此即使是针对大型表执行操作，速度也很快。无需像在 Oracle 数据库中那样将列标记为 UNUSED。被删除的列占用的空间可由新 DML 语句或在后续 VACUUM 操作期间回收。

只读表

只读表是一项 Oracle 功能，可以使用 ALTER TABLE 命令将表标记为只读。在 Oracle 12c R2 中，此功能也可用于包含分区和子分区的表。PostgreSQL 不提供等效功能，但有两种可能的解决方法：

向特定用户授予对表的 SELECT 权限。请注意，这不会阻止表所有者对其表执行 DML 操作。
创建 Cloud SQL for PostgreSQL 读取副本，并引导用户访问只读的读取表。此解决方案需要将读取副本实例添加到现有 Cloud SQL for PostgreSQL 实例。

创建引发 DML 语句异常的数据库触发器 - 例如：

-- Define trigger function
CREATE OR REPLACE FUNCTION raise_readonly_exception() RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Table is readonly!';
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

-- Fire trigger when DML statements is executed on read only table
CREATE TRIGGER myTable_readonly_trigger
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON myTable FOR EACH STATEMENT
EXECUTE PROCEDURE raise_readonly_exception();

-- Testing the trigger
postgres=> INSERT INTO myTable (id) VALUES (1);
ERROR:  Table is readonly!
CONTEXT:  PL/pgSQL function raise_readonly_exception() line 3 at RAISE
postgres=>

字符集

Oracle 和 PostgreSQL 都支持多种字符集、排序规则和 Unicode，包括支持单字节和多字节语言。此外，驻留在同一个实例上的 PostgreSQL 数据库可以使用不同的字符集进行配置。请参阅 PostgreSQL 中支持的字符集列表。

在 Oracle 数据库中，字符集是在数据库级层（Oracle 12g R1 或更低版本）或可插入数据库级层（Oracle 12g R2 或更高版本）指定的。在 PostgreSQL 中，系统会在创建新的 Cloud SQL for PostgreSQL 实例时指定默认字符集。在该实例中创建的每个数据库可以使用不同的字符集创建。可以按表列指定排序顺序和字符分类。

示例

-- Create a database using UTF-8 character set and ja_JP.UTF collation
postgres=> CREATE DATABASE jpdb WITH ENCODING 'UTF8' LC_COLLATE='ja_JP.UTF8' LC_CTYPE='ja_JP.UTF8' TEMPLATE=template0;

-- Query the character set and collation settings of all databases
postgres=> SELECT datname AS DATABASE_NAME, datcollate AS LC_COLLATE, datctype AS LC_CTYPE from pg_database;
 database_name | lc_collate |  lc_ctype
---------------+------------+------------
 cloudsqladmin | en_US.UTF8 | en_US.UTF8
 template0     | en_US.UTF8 | en_US.UTF8
 template1     | en_US.UTF8 | en_US.UTF8
 postgres      | en_US.UTF8 | en_US.UTF8
 jpdb          | ja_JP.UTF8 | ja_JP.UTF8
(5 rows)

-- Alternatively, use psql \l command to query the database settings
postgres=> \l
                                                List of databases
     Name      |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges
---------------+-------------------+----------+------------+------------+-----------------------------------------
 cloudsqladmin | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 |
 postgres      | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser                  +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
               |                   |          |            |            | testuser=CTc/cloudsqlsuperuser
 template0     | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin                       +
               |                   |          |            |            | cloudsqladmin=CTc/cloudsqladmin
 template1     | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser                   +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser
-- Specifying column level collation
postgres=> CREATE TABLE test1 (
postgres(>     a text COLLATE "de_DE",
postgres(>     b text COLLATE "es_ES"
postgres(> );

视图

PostgreSQL 支持简单视图和复杂视图。对于视图创建选项，Oracle 和 PostgreSQL 之间存在一些差异。下表着重说明了这些差异。

Oracle 视图功能	说明	Cloud SQL for PostgreSQL 支持	转换注意事项
`FORCE`	创建视图，而不验证来源表/视图是否存在。	否	无等效选项可用。
`CREATE OR REPLACE`	创建不存在的视图或覆盖现有的视图。	是	PostgreSQL 支持 `CREATE OR REPLACE` 命令视图。
`WITH CHECK OPTION`	指定针对视图执行 DML 操作时的强制执行级别。	是	默认值为 `CASCADED`，其会导致同时评估引用的视图。 `LOCAL` 关键字导致仅评估当前视图。
`WITH READ-ONLY`	只允许针对该视图的读取操作。禁止执行 DML 操作。	否	解决方法是向所有用户授予对视图的 SELECT 权限。
`VISIBLE \| INVISIBLE` (Oracle 12c)	指定基于视图的列对用户可见还是不可见。	否	仅使用所需的列创建 `VIEW`。

以下转换示例演示了如何将视图从 Oracle 转换为 Cloud SQL PostgreSQL。

-- Create view to retrieve employees from department 100 using the WITH CHECK -- OPTION option
SQL> CREATE OR REPLACE FORCE VIEW vw_emp_dept100
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
SQL> UPDATE vw_emp_dept100
     SET salary=salary+1000;

postgres=> CREATE OR REPLACE VIEW vw_emp_dept100
postgres-> AS
postgres-> SELECT EMPLOYEE_ID,
postgres->        FIRST_NAME,
postgres->        LAST_NAME,
postgres->        SALARY,
postgres->        DEPARTMENT_ID
postgres-> FROM EMPLOYEES
postgres-> WHERE DEPARTMENT_ID=100
postgres-> WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
postgres=> UPDATE vw_emp_dept100
postgres-> SET salary=salary+1000;

-- Update one employee department id to 60
postgres=> UPDATE vw_emp_dept100
postgres-> SET DEPARTMENT_ID=60
postgres-> WHERE EMPLOYEE_ID=110;

ERROR:  new row violates check option for view "vw_emp_dept100"
DETAIL:  Failing row contains (110, John, Chen, JCHEN, 515.124.4269, 1997-09-28, FI_ACCOUNT, 9200.00, null, 108, 60).

查看访问权限管理：

视图的所有者必须具有基表的权限才能创建视图。视图的用户需要具有对视图的适当 SELECT 权限。在通过视图执行 DML 操作时，他们还需要对视图具有适当的 INSERT、UPDATE、DELETE 权限。无论哪种情况，用户都不需要针对底层表的权限。

后续步骤

详细了解 PostgreSQL 用户账号。
探索有关 Google Cloud 的参考架构、图表和最佳实践。查看我们的 Cloud Architecture Center。