将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用 - Amazon Relational Database Service
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用

您可以安装和配置各种 PostgreSQL 扩展。例如,要处理空间数据,您可以安装和使用 PostGIS 扩展。有关更多信息,请参阅 使用 PostGIS 扩展管理空间数据。作为另一个例子,如果要改进非常大的表的数据输入,您可以考虑使用 pg_partman 扩展对数据进行分区。要了解更多信息,请参阅“使用 pg_partman 扩展管理 PostgreSQL 分区”。

以下是部分 PostgreSQL 扩展,您可以安装这些扩展并与 Amazon RDS for PostgreSQL 数据库实例结合使用。

使用 orafce 扩展中的函数

orafce 扩展提供了模拟 Oracle 数据库中的函数和软件包子集的函数和运算符。orafce 扩展使您能够更轻松地将 Oracle 应用程序移植到 PostgreSQL。RDS for PostgreSQL 版本 9.6.6 及更高版本支持该扩展。有关 orafce 的更多信息,请参阅 GitHub 上的 orafce

注意

RDS for PostgreSQL 不支持属于 orafce 扩展一部分的 utl_file 软件包。这是因为 utl_file 架构函数可提供对操作系统文本文件的读取和写入操作,这需要对底层主机拥有超级用户访问权限。作为一项托管服务,RDS for PostgreSQL 不提供主机访问权限。

使用 orafce 扩展

  1. 使用用于创建数据库实例的主用户名连接到数据库实例。

    如果要为同一数据库实例中的不同数据库启用 orafce,请使用 /c dbname psql 命令。使用此命令,您可以在启动连接后从主数据库进行更改。

  2. 使用 CREATE EXTENSION 语句启用 orafce 扩展。

    CREATE EXTENSION orafce;
  3. 使用 ALTER SCHEMA 语句将 oracle 架构的所有权移交给 rds_superuser 角色。

    ALTER SCHEMA oracle OWNER TO rds_superuser;

    如果要查看 oracle 架构的所有者列表,请使用 \dn psql 命令。

使用 pgaudit 扩展在会话和对象级别进行日志记录

您可以通过在 RDS for PostgreSQL 数据库实例上安装 pgaudit 扩展在会话级别或在对象级别记录活动。所有可用的 RDS for PostgreSQL 版本均支持该扩展。它使用底层本机 PostgreSQL 日志记录机制。

要了解关于 pgaudit 扩展的更多信息,请参阅 GitHub 上的 pgAudit

通过会话审计,您可以记录来自各种来源的审核事件,并在完全限定的命令文本可用时包括该文本。修改与数据库实例关联的自定义参数组以使 shared_preload_libraries 包含 pgaudit。然后,将 pgaudit.log 参数设置为记录以下任意类型的事件:

  • READ – 记录 SELECTCOPY(当源为关系或查询时)。

  • WRITE - 记录 INSERTUPDATEDELETETRUNCATE, 和 COPY(当目标为关系时)。

  • FUNCTION - 记录函数调用和 DO 块。

  • ROLE - 记录与角色和权限相关的语句,例如 GRANTREVOKECREATE ROLEALTER ROLEDROP ROLE

  • DDL - 记录所有数据定义语言 (DDL) 语句(不包括在 ROLE 类中)。

  • MISC – 记录其他命令,例如 DISCARDFETCHCHECKPOINTVACUUMSET

要使用会话审计记录多种事件类型,请使用逗号分隔的列表。要记录所有事件类型,请将 pgaudit.log 设置为 ALL。重启数据库实例以应用更改。

通过对象审计,您可以细化审计日志记录以使用特定的关系。例如,您可以指定要审计特定数量表上的 READ 操作的日志记录。

要使用 pgaudit 扩展,请将其添加到 RDS for PostgreSQL 数据库实例上的 shared_preload_libraries 参数。您无法编辑默认数据库参数组中的值,因此这意味着您需要为数据库实例使用自定义数据库参数组。有关参数组的更多信息,请参阅 使用参数组

将对象审计与 pgaudit 扩展结合使用

  1. 使用以下命令创建名为 rds_pgaudit 的数据库角色。

    CREATE ROLE rds_pgaudit;
  2. 修改与数据库实例关联的自定义参数组,如下所示:

    1. 将 pgaudit 添加到 shared_preload_libraries 参数列表。使用 Amazon CLI,运行以下命令。

      aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \ --region aws-region
    2. pgaudit.role 设置为角色 rds_pgaudit。使用 Amazon CLI,运行以下命令。

      aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \ --region aws-region
  3. 重启数据库实例以使参数组的更改生效。

    aws rds reboot-db-instance \ --db-instance-identifier your-RDS-db-instance \ --region aws-region
  4. 运行以下命令确认 pgaudit 已初始化。

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
  5. 运行以下命令创建 pgaudit 扩展。

    CREATE EXTENSION pgaudit;
  6. 运行以下命令确认 pgaudit.role 设置为 rds_pgaudit

    SHOW pgaudit.role; pgaudit.role ------------------ rds_pgaudit

要测试 pgaudit 日志记录,您可以运行几条要审计的示例命令。例如,您可以运行以下命令。

CREATE TABLE t1 (id int); GRANT SELECT ON t1 TO rds_pgaudit; SELECT * FROM t1; id ---- (0 rows)

数据库日志应包含类似于以下内容的条目。

... 2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1; ...

有关查看日志的信息,请参阅监控 Amazon RDS 日志文件

使用 pg_repack 扩展减少表和索引的膨胀

您可以使用 pg_repack 扩展从表和索引中删除多余内容。RDS for PostgreSQL 版本 9.6.3 及更高版本支持该扩展。有关 pg_repack 扩展的更多信息,请参阅 GitHub 项目文档

若要使用 pg_repack 扩展

  1. 通过运行以下命令在 RDS for PostgreSQL 数据库实例上安装 pg_repack 扩展。

    CREATE EXTENSION pg_repack;
  2. 运行以下命令以授予重新打包由 pg_repack 创建的临时日志表的写入访问权限。

    ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
  3. 使用 pg_repack 客户端实用程序连接到数据库。使用具有 rds_superuser 特权的账户。例如,假设 rds_test 角色有 rds_superuser 特权。命令语法如下所示。

    pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres

    使用 -k 选项连接。不支持 -a 选项。

  4. 来自 pg_repack 客户端的响应提供有关重新打包的数据库实例上的表的信息。

    INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"

升级和使用 PLV8 扩展

PLV8 是适用于 PostgreSQL 的可信 Javascript 语言扩展。您可以将其用于存储过程、触发条件和其他可从 SQL 调用的过程代码。PostgreSQL 的所有当前版本都支持此语言扩展。

如果使用 PLV8 并将 PostgreSQL 升级到新的 PLV8 版本,您可以立即利用新扩展。按下列步骤将目录元数据与新版本的 PLV8 进行同步。这些步骤是可选的,但我们强烈建议您完成这些步骤以避免元数据错配警告。

升级过程会丢弃所有现有的 PLV8 功能。因此,建议您在升级之前创建 RDS for PostgreSQL 数据库实例的快照。有关更多信息,请参阅 创建数据库快照

将目录元数据与新版本的 PLV8 同步

  1. 确认您需要更新。为此,请在连接到实例的情况下运行以下命令。

    SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

    如果您的结果包含的已安装版本的值低于默认版本,请继续该过程以更新扩展。例如,以下结果集指示您应更新。

    name | default_version | installed_version | comment --------+-----------------+-------------------+-------------------------------------------------- plls | 2.1.0 | 1.5.3 | PL/LiveScript (v8) trusted procedural language plcoffee| 2.1.0 | 1.5.3 | PL/CoffeeScript (v8) trusted procedural language plv8 | 2.1.0 | 1.5.3 | PL/JavaScript (v8) trusted procedural language (3 rows)
  2. 如果尚未创建 RDS for PostgreSQL 数据库实例的快照,请先创建一个。创建快照时,您可以继续下列步骤。

  3. 获取数据库实例中 PLV8 函数的数量,以便在升级后验证是否遗漏函数。例如,以下 SQL 查询会返回用 plv8、plcoffee 和 plls 编写的函数数量。

    SELECT proname, nspname, lanname FROM pg_proc p, pg_language l, pg_namespace n WHERE p.prolang = l.oid AND n.oid = p.pronamespace AND lanname IN ('plv8','plcoffee','plls');
  4. 使用 pg_dump 创建仅架构转储文件。例如,在客户端计算机上的 /tmp 目录中创建文件。

    ./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp

    该示例使用以下选项:

    • -Fc - 自定义格式

    • --仅架构 - 仅转储创建架构所需的命令(在本例中为功能)

    • -U - RDS 主用户名

    • database - 数据库实例的数据库名称

    有关 pg_dump 的更多信息,请参阅 PostgreSQL 文档中的 pg_dump

  5. 提取位于转储文件中的“CREATE FUNCTION”DDL 语句。以下示例使用 grep 命令提取创建函数的 DDL 语句并将函数保存到文件中。将在后续步骤中使用该语句重新创建函数。

    ./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list/

    有关 pg_restore 的更多信息,请参阅 PostgreSQL 文档中的 pg_restore

  6. 删掉函数和扩展。以下示例将删除基于 PLV8 的任何对象。级联选项确保删除任何依赖项。

    DROP EXTENSION pvl8 CASCADE;

    如果您的 PostgreSQL 实例包含基于 plcoffee 或 plls 的对象,请对这些扩展重复此步骤。

  7. 创建扩展。以下示例将创建 plv8、plcoffee 和 plls 扩展。

    CREATE EXTENSION plv8; CREATE EXTENSION plcoffee; CREATE EXTENSION plls;
  8. 使用转储文件和“驱动程序”文件创建函数。

    以下示例将重新创建之前提取的函数。

    ./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp
  9. 使用以下查询验证是否已重新创建所有函数。

    SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

    PLV8 版本 2 在结果集中添加以下额外行:

    proname | nspname | lanname ---------------+------------+---------- plv8_version | pg_catalog | plv8