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

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

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

注意

自 RDS for PostgreSQL 14.5 起,RDS for PostgreSQL 支持适用于 PostgreSQL 的可信语言扩展。此功能是作为扩展 pg_tle 实现的,您可以将其添加到 RDS for PostgreSQL 数据库实例中。通过使用此扩展,开发人员可以在安全的环境中创建自己的 PostgreSQL 扩展,从而简化设置和配置要求。有关更多信息,请参阅使用适用于 PostgreSQL 的可信语言扩展

在某些情况下,您可以将特定模块添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组中的 shared_preload_libraries 列表中,而不是安装扩展。通常,原定设置的数据库集群参数组仅加载 pg_stat_statements,但还有其他几个模块可供添加到此列表中。例如,您可以通过添加 pg_cron 模块来添加调度功能,详情请见使用 PostgreSQL pg_cron 扩展计划维护。再举一个例子,您可以通过加载 auto_explain 模块来记录查询执行计划。要了解更多信息,请参阅 Amazon 知识中心中的记录查询执行计划

根据您的 RDS for PostgreSQL 版本,安装扩展可能需要 rds_superuser 权限,如下所示:

  • 对于 RDS for PostgreSQL 版本 12 和更早版本,安装扩展需要 rds_superuser 权限。

  • 对于 RDS for PostgreSQL 版本 13 及更高版本,对给定数据库实例具有创建权限的用户(角色)可以安装和使用任何可信扩展。有关可信扩展清单,请参阅PostgreSQL 可信扩展

您还可以通过在 rds.allowed_extensions 参数中列出扩展,来精确指定可以在 RDS for PostgreSQL 数据库实例上安装的扩展。默认情况下,不设置此参数,因此如果用户有添加扩展的权限,则可以添加任何受支持的扩展。通过向此参数添加扩展列表,您可以明确标识 RDS for PostgreSQL 数据库实例可使用的扩展。无法安装任何未列出的扩展。此功能适用于以下版本:

  • RDS for PostgreSQL 14.1 和所有更高版本

  • RDS for PostgreSQL 13.3 和更高次要版本

  • RDS for PostgreSQL 12.7 和更高次要版本

有关更多信息,请参阅限制 PostgreSQL 扩展的安装

要了解有关 rds_superuser 角色的更多信息,请参阅了解 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 记录数据库活动

金融机构、政府机构和许多行业需要保留审计日志以满足监管要求。通过将 PostgreSQL Audit 扩展(pgAudit)与 RDS for PostgreSQL 数据库实例结合使用,您可以捕获审计人员通常需要或满足监管要求的详细记录。例如,您可以设置 pgAudit 扩展来跟踪对特定数据库和表所做的更改,记录进行更改的用户以及许多其他详细信息。

pgAudit 扩展通过更详细地扩展日志消息,进一步构建原生 PostgreSQL 日志记录基础设施的功能。换句话说,您将使用与查看任何日志消息相同的方法来查看审计日志。有关 PostgreSQL 日志记录的更多信息,请参阅 RDS for PostgreSQL 数据库日志文件

pgAudit 扩展会编辑日志中的敏感数据,例如明文密码。如果您的 RDS for PostgreSQL 数据库实例配置为记录数据操作语言(DML)语句(详情请见为您的 RDS for PostgreSQL 数据库实例开启查询日志记录),则可以使用 PostgreSQL Audit 扩展来避免明文密码问题。

您可以在数据库实例上配置具有高度明确性的审计。您可以审计所有数据库和所有用户。或者,您可以选择仅审计某些数据库、用户和其他对象。您也可以明确排除对某些用户和数据库进行审计。有关更多信息,请参阅从审计日志记录中排除用户或数据库

考虑到可以捕获的详细信息量,我们建议您在使用 pgAudit 时监控存储消耗。

所有可用的 RDS for PostgreSQL 版本。有关可用 RDS for PostgreSQL 版本支持的 pgAudit 版本的列表,请参阅《Amazon RDS for PostgreSQL 版本注释》中的 Amazon RDS for PostgreSQL 的扩展版本

设置 pgAudit 扩展

要在 RDS for PostgreSQL 数据库实例 上设置 pgAudit 扩展,首先要将 pgAudit 添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组 上的共享库中。有关创建自定义数据库参数组的信息,请参阅 使用参数组接下来,安装 pgAudit 扩展。最后,指定要审计的数据库和对象。本部分中的过程向您展示如何操作。您可以使用 Amazon Web Services Management Console或 Amazon CLI。

您必须拥有 rds_superuser 角色的权限才能执行所有这些任务。

以下步骤假设您的 RDS for PostgreSQL 数据库实例与自定义 数据库参数组相关联。

设置 pgAudit 扩展
  1. 登录 Amazon Web Services Management Console 并通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  2. 在导航窗格中,选择 RDS for PostgreSQL 数据库实例

  3. 打开 RDS for PostgreSQL 数据库实例的 Configuration(配置)选项卡。在实例详细信息中,找到 Parameter group(参数组)链接。

  4. 选择此链接以打开与您的 RDS for PostgreSQL 数据库实例关联的自定义参数。

  5. Parameters(参数)搜索字段中,键入 shared_pre 以查找 shared_preload_libraries 参数。

  6. 选择 Edit parameters(编辑参数)以访问属性值。

  7. pgaudit 添加到 Values(值)字段的列表中。使用逗号分隔值列表中的项目。

    
                添加了 pgAudit 的 shared_preload_libaries 参数的图像。
  8. 重启 RDS for PostgreSQL 数据库实例,以使对 shared_preload_libraries 参数的更改生效。

  9. 当实例可用时,请验证 pgAudit 是否已初始化。使用 psql 连接到 RDS for PostgreSQL 数据库实例,然后运行以下命令。

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
  10. 初始化 pgAudit 后,您现在可以创建扩展了。您需要在初始化库后创建扩展,因为 pgaudit 扩展会为审计数据定义语言(DDL)语句安装事件触发器。

    CREATE EXTENSION pgaudit;
  11. 关闭 psql 会话。

    labdb=> \q
  12. 登录 Amazon Web Services Management Console 并通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  13. 在列表中找到 pgaudit.log 参数并设置为适合您的使用案例的值。例如,将 pgaudit.log 参数设置为 write(如下图所示),可以捕获对日志的插入、更新、删除和其他一些类型的更改。

    
            带有设置的 pgaudit.log 参数的图像。

    还可以为 pgaudit.log 参数选择以下值之一。

    • none – 这是原定设置值。不记录任何数据库更改。

    • all – 记录所有内容(read、write、function、role、ddl、misc)。

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

    • function – 记录函数调用和 DO 块。

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

    • read –当源为关系(例如表)或查询时记录 SELECTCOPY

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

    • write – 当目标为关系(表)时,记录 INSERTUPDATEDELETETRUNCATECOPY

  14. 选择 Save changes(保存更改)。

  15. 通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  16. 从数据库列表中选择 RDS for PostgreSQL 数据库实例以将其选中,然后从 Actions(操作)菜单中选择 Reboot(重启)。

设置 pgAudit

要使用 Amazon CLI 设置 pgAudit,您可以调用 modify-db-parameter-group 操作来修改自定义参数组中的审计日志参数,如以下过程所示。

  1. 使用以下 Amazon CLI 命令向 shared_preload_libraries 参数中添加 pgaudit

    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. 使用以下 Amazon CLI 命令重启 RDS for PostgreSQL 数据库实例,以便初始化 pgaudit 库。

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  3. 当实例可用时,您可以验证 pgaudit 是否已初始化。使用 psql 连接到 RDS for PostgreSQL 数据库实例,然后运行以下命令。

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)

    初始化 pgAudit 后,您现在可以创建扩展了。

    CREATE EXTENSION pgaudit;
  4. 关闭 psql 会话以便您可以使用 Amazon CLI。

    labdb=> \q
  5. 使用以下 Amazon CLI 命令指定要由会话审计日志记录所记录的语句类别。该示例将 pgaudit.log 参数设置为 write,用于捕获对日志的插入、更新和删除。

    aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \ --region aws-region

    还可以为 pgaudit.log 参数选择以下值之一。

    • none – 这是原定设置值。不记录任何数据库更改。

    • all – 记录所有内容(read、write、function、role、ddl、misc)。

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

    • function – 记录函数调用和 DO 块。

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

    • read –当源为关系(例如表)或查询时记录 SELECTCOPY

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

    • write – 当目标为关系(表)时,记录 INSERTUPDATEDELETETRUNCATECOPY

    使用以下 Amazon CLI 命令重启 RDS for PostgreSQL 数据库实例

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region

审计数据库对象

RDS for PostgreSQL 数据库实例上设置 pgAudit 并根据您的要求进行配置后,将在 PostgreSQL 日志中捕获更多详细信息。例如,虽然原定设置 PostgreSQL 日志记录配置标识数据库表中发生更改的日期和时间,但使用 pgAudit 扩展后,日志条目可以包括模式、进行更改的用户和其他详细信息,具体取决于扩展参数的配置方式。您可以设置审计以通过以下方法跟踪更改。

  • 对于每个会话,按用户进行跟踪。对于会话级别,您可以捕获完全限定的命令文本。

  • 对于每个对象,按用户和数据库进行跟踪。

当您在系统上创建 rds_pgaudit 角色,然后将此角色添加到自定义参数组中的 pgaudit.role 参数时,将激活对象审计功能。原定设置情况下,pgaudit.role 参数处于未设置状态,唯一允许的值是 rds_pgaudit。以下步骤假设 pgaudit 已初始化,并且您已按照设置 pgAudit 扩展中的步骤创建了 pgaudit 扩展。


      设置 pgAudit 后 PostgreSQL 日志文件的图像。

如本示例所示,“LOG: AUDIT: SESSION”行提供了有关表及其架构的信息以及其他详细信息。

设置对象审计
  1. 使用 psql 连接到 RDS for PostgreSQL 数据库实例

    psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
  2. 使用以下命令创建名为 rds_pgaudit 的数据库角色。

    labdb=> CREATE ROLE rds_pgaudit; CREATE ROLE labdb=>
  3. 关闭 psql 会话。

    labdb=> \q

    在接下来的几步中,使用 Amazon CLI 修改自定义参数组中的审计日志参数。

  4. 使用以下 Amazon CLI 命令将 pgaudit.role 参数设置为 rds_pgaudit。原定设置情况下,此参数为空,rds_pgaudit 是唯一允许的值。

    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
  5. 使用以下 Amazon CLI 命令重启 RDS for PostgreSQL 数据库实例,以使对参数的更改生效。

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  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 日志文件

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

从审计日志记录中排除用户或数据库

RDS for PostgreSQL 数据库日志文件中所述,PostgreSQL 日志会消耗存储空间。使用 pgAudit 扩展会在不同程度上增加日志中收集的数据量,具体取决于您跟踪的更改。您可能不需要审计 RDS for PostgreSQL 数据库实例中的每个用户或数据库。

为了最大限度地减少对存储的影响,避免不必要地捕获审计记录,您可以将用户和数据库排除在审计范围之外。您还可以在给定会话中更改日志记录。下面的示例向您演示如何操作。

注意

会话级别的参数设置优先于 RDS for PostgreSQL 数据库实例的自定义数据库参数组中的设置。如果您不希望数据库用户绕过您的审计日志记录配置设置,请务必更改其权限。

假设您的 RDS for PostgreSQL 数据库实例配置为审计所有用户和数据库的相同级别的活动。然后,您决定不想对用户 myuser 进行审计。您可以使用以下 SQL 命令对 myuser 关闭审计功能。

ALTER USER myuser SET pgaudit.log TO 'NONE';

然后,您可以使用以下查询来检查 pgaudit.loguser_specific_settings 列,以确认该参数已设置为 NONE

SELECT usename AS user_name, useconfig AS user_specific_settings FROM pg_user WHERE usename = 'myuser';

您将看到如下输出。

user_name | user_specific_settings -----------+------------------------ myuser | {pgaudit.log=NONE} (1 row)

在给定用户与数据库的会话期间,您可以使用以下命令对此用户关闭日志记录功能。

ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';

使用以下查询,对于特定用户和数据库组合检查 pgaudit.log 的设置列。

SELECT usename AS "user_name", datname AS "database_name", pg_catalog.array_to_string(setconfig, E'\n') AS "settings" FROM pg_catalog.pg_db_role_setting s LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole WHERE usename = 'myuser' AND datname = 'mydatabase' ORDER BY 1, 2;

您将看到类似以下内容的输出。

user_name | database_name | settings -----------+---------------+------------------ myuser | mydatabase | pgaudit.log=none (1 row)

myuser 关闭审计后,您决定不想跟踪对 mydatabase 的更改。您可以使用以下命令对该特定数据库关闭审计。

ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';

然后,使用以下查询检查 database_specific_settings 列,以确认 pgaudit.log 已设置为 NONE。

SELECT a.datname AS database_name, b.setconfig AS database_specific_settings FROM pg_database a FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase WHERE a.datname = 'mydatabase';

您将看到如下输出。

database_name | database_specific_settings ---------------+---------------------------- mydatabase | {pgaudit.log=NONE} (1 row)

要将 myuser 的设置恢复为原定设置,请使用以下命令:

ALTER USER myuser RESET pgaudit.log;

要将数据库的设置恢复为原定设置,请使用以下命令。

ALTER DATABASE mydatabase RESET pgaudit.log;

要将用户和数据库重置为原定设置,请使用以下命令。

ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;

还可以通过将 pgaudit.log 设置为 pgaudit.log 参数的其他允许值之一,将特定事件捕获到日志中。有关更多信息,请参阅pgaudit.log 参数允许的设置列表

ALTER USER myuser SET pgaudit.log TO 'read'; ALTER DATABASE mydatabase SET pgaudit.log TO 'function'; ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'

pgAudit 扩展的参考

您可以通过更改本节中列出的一个或多个参数来为审计日志指定所需的详细级别。

控制 pgAudit 行为

您可以通过更改下表中列出的一个或多个参数来控制审计日志记录。

参数 描述

pgaudit.log

指定会话审计日志记录将记录哪些语句类。允许的值包括 ddl、function、misc、read、role、write、none、all。有关更多信息,请参阅pgaudit.log 参数允许的设置列表

pgaudit.log_catalog

启用(设置为 1)时,如果语句中的所有关系都在 pg_catalog 中,则将语句添加到审计跟踪中。

pgaudit.log_level

指定要用于日志条目的日志级别。允许的值:debug5、debug4、debug3、debug2、debug1、info、notice、warning、log

pgaudit.log_parameter

启用(设置为 1)时,将在审计日志中捕获随语句传递的参数。

pgaudit.log_relation

启用(设置为 1)时,会话的审计日志为 SELECCT 或 DML 语句中引用的每个关系(TABLE、VIEW 等)创建单独的日志条目。

pgaudit.log_statement_once

指定日志记录在语句/子语句组合中的第一个日志条目中包含语句文本和参数,还是在每个条目中都包含。

pgaudit.role

指定用于对象审计日志记录的主角色。唯一允许的条目是 rds_pgaudit

pgaudit.log 参数允许的设置列表

描述

这是默认模式。不记录任何数据库更改。

全部

记录所有内容(read、write、function、role、ddl、misc)。

ddl

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

函数

记录函数调用和 DO 块。

misc

记录其他命令,例如 DISCARDFETCHCHECKPOINTVACUUMSET

read

当源为关系(例如表)或查询时记录 SELECTCOPY

role

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

write

当目标为关系(表)时,记录 INSERTUPDATEDELETETRUNCATECOPY

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

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

使用 pglogical 跨实例同步数据

所有当前可用的 RDS for PostgreSQL 版本都支持 pglogical 扩展。pglogic 扩展早于 PostgreSQL 在版本 10 中引入的功能类似的逻辑复制功能。有关更多信息,请参阅 为 Amazon RDS for PostgreSQL 执行逻辑复制

pglogical 扩展支持在两个或更多 RDS for PostgreSQL 数据库实例之间进行逻辑复制。它还支持在不同的 PostgreSQL 版本之间进行复制,以及在 RDS for PostgreSQL 数据库实例和 Aurora PostgreSQL 数据库集群上运行的数据库之间进行复制。pglogical 扩展使用发布-订阅模型将对表和其他对象(例如序列)的更改从发布者复制到订阅者。它依赖于复制插槽来确保更改从发布者节点同步到订阅者节点,定义如下。

  • 发布者节点是作为要复制到其他节点的数据来源的 RDS for PostgreSQL 数据库实例。发布者节点定义要在发布集中复制的表。

  • 订阅者节点是用于接收来自发布商的 WAL 更新的 RDS for PostgreSQL 数据库实例。订阅者创建订阅以连接到发布者并获取解码后的 WAL 数据。订阅者创建订阅时,将在发布者节点上创建复制插槽。

在下文中,您可以了解有关设置 pglogical 扩展的信息。

pglogical 扩展的要求和限制

所有当前可用的 RDS for PostgreSQL 版本都支持 pglogical 扩展。

发布者节点和订阅者节点都必须设置为进行逻辑复制。

要从订阅者复制到发布者的表必须具有相同的名称和相同的模式。这些表还必须包含相同的列,并且这些列必须使用相同的数据类型。发布者表和订阅者表必须具有相同的主键。我们建议您仅使用 PRIMARY KEY 作为唯一约束。

对于 CHECK 约束和 NOT NULL 约束,订阅者节点上的表可能比发布者节点上的表具有更宽松的约束。

pglogical 扩展提供了诸如双向复制之类的功能,PostgreSQL(版本 10 及更高版本)中内置的逻辑复制功能不支持这些功能。有关更多信息,请参阅使用 pglogic 进行 PostgreSQL 双向复制

设置 pglogical 扩展

要在 RDS for PostgreSQL 数据库实例上设置 pglogical 扩展,首先要将 pglogical 添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组上的共享库中。您还需要将 rds.logical_replication 参数的值设置为 1,以开启逻辑解码。最后,在数据库中创建此扩展。您可以使用 Amazon Web Services Management Console或 Amazon CLI 执行这些任务。

您必须拥有 rds_superuser 角色的权限才能执行这些任务。

以下步骤假设您的 RDS for PostgreSQL 数据库实例与自定义 数据库参数组相关联。有关创建自定义数据库参数组的信息,请参阅 使用参数组

设置 pglogical 扩展
  1. 登录 Amazon Web Services Management Console 并通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  2. 在导航窗格中,选择 RDS for PostgreSQL 数据库实例

  3. 打开 RDS for PostgreSQL 数据库实例的 Configuration(配置)选项卡。在实例详细信息中,找到 Parameter group(参数组)链接。

  4. 选择此链接以打开与您的 RDS for PostgreSQL 数据库实例关联的自定义参数。

  5. Parameters(参数)搜索字段中,键入 shared_pre 以查找 shared_preload_libraries 参数。

  6. 选择 Edit parameters(编辑参数)以访问属性值。

  7. pglogical 添加到 Values(值)字段的列表中。使用逗号分隔值列表中的项目。

    
                添加了 pglogical 的 shared_preload_libraries 参数的图像。
  8. 找到 rds.logical_replication 参数并将其设置为 1,以开启逻辑复制。

  9. 重启 RDS for PostgreSQL 数据库实例,以使更改生效。

  10. 当实例可用时,可以使用 psql(或 pgAdmin)连接到 RDS for PostgreSQL 数据库实例

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
  11. 要验证 pglogical 是否初始化,可以运行以下命令。

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pglogical (1 row)
  12. 验证启用逻辑解码的设置,如下所示。

    SHOW wal_level; wal_level ----------- logical (1 row)
  13. 创建扩展,如下所示。

    CREATE EXTENSION pglogical; EXTENSION CREATED
  14. 选择 Save changes(保存更改)。

  15. 通过以下网址打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

  16. 从数据库列表中选择 RDS for PostgreSQL 数据库实例以将其选中,然后从 Actions(操作)菜单中选择 Reboot(重启)。

设置 pglogical 扩展

要使用 Amazon CLI 设置 pglogical,您可以调用 modify-db-parameter-group 操作来修改自定义参数组中的某些参数,如以下过程所示。

  1. 使用以下 Amazon CLI 命令向 shared_preload_libraries 参数中添加 pglogical

    aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \ --region aws-region
  2. 使用以下 Amazon CLI 命令将 rds.logical_replication 设置为 1,以针对 RDS for PostgreSQL 数据库实例中删除很久之后查看和分析系统数据。

    aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \ --region aws-region
  3. 使用以下 Amazon CLI 命令重启 RDS for PostgreSQL 数据库实例,以便初始化 pglogical 库。

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region
  4. 当实例可用时,使用 psql 连接到 RDS for PostgreSQL 数据库实例

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
  5. 创建扩展,如下所示。

    CREATE EXTENSION pglogical; EXTENSION CREATED
  6. 使用以下 Amazon CLI 命令重启 RDS for PostgreSQL 数据库实例

    aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region

RDS for PostgreSQL 数据库实例设置逻辑复制

以下过程说明如何在两个 RDS for PostgreSQL 数据库实例之间启动逻辑复制。这些步骤假设来源(发布者)和目标(订阅者)都如设置 pglogical 扩展中所述设置了 pglogical 扩展。

创建发布者节点并定义要复制的表

这些步骤假设您的 RDS for PostgreSQL 数据库实例有一个数据库,其中包含一个或多个您要复制到另一个节点的表。您需要在订阅者上根据发布者重新创建表结构,因此,如果需要,首先获取表结构。为此,您可以使用 psq1 元命令 \d tablename,然后在订阅者实例上创建相同的表。以下过程在发布者(来源)上创建示例表以用于演示目的。

  1. 使用 psql 连接到具有要用作订阅者来源的表的实例。

    psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb

    如果没有现有表要复制,可以按如下方式创建示例表。

    1. 使用以下 SQL 语句创建一个示例表。

      CREATE TABLE docs_lab_table (a int PRIMARY KEY);
    2. 使用以下 SQL 语句用生成的数据填充表。

      INSERT INTO docs_lab_table VALUES (generate_series(1,5000)); INSERT 0 5000
    3. 使用以下 SQL 语句验证表中是否存在数据。

      SELECT count(*) FROM docs_lab_table;
  2. 将这一 RDS for PostgreSQL 数据库实例标识为发布者节点,如下所示。

    SELECT pglogical.create_node( node_name := 'docs_lab_provider', dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb'); create_node ------------- 3410995529 (1 row)
  3. 将要复制的表添加到原定设置的复制集。有关复制集的更多信息,请参阅 pglogical 文档中的复制集

    SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL); replication_set_add_table --------------------------- t (1 row)

发布者节点设置已完成。现在,您可以设置订阅者节点以接收来自发布者的更新。

设置订阅者节点并创建订阅以接收更新

这些步骤假设已使用 pglogical 扩展设置了 RDS for PostgreSQL 数据库实例。有关更多信息,请参阅设置 pglogical 扩展

  1. 使用 psql 连接到要从发布者接收更新的实例。

    psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
  2. 在订阅者 RDS for PostgreSQL 数据库实例上,创建与发布者上存在的相同表。在本例中,该表为 docs_lab_table。您可以按如下所示创建表。

    CREATE TABLE docs_lab_table (a int PRIMARY KEY);
  3. 验证此表为空。

    SELECT count(*) FROM docs_lab_table; count ------- 0 (1 row)
  4. 将这一 RDS for PostgreSQL 数据库实例标识为订阅者节点,如下所示。

    SELECT pglogical.create_node( node_name := 'docs_lab_target', dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********'); create_node ------------- 2182738256 (1 row)
  5. 创建订阅。

    SELECT pglogical.create_subscription( subscription_name := 'docs_lab_subscription', provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******', replication_sets := ARRAY['default'], synchronize_data := true, forward_origins := '{}' ); create_subscription --------------------- 1038357190 (1 row)

    完成此步骤后,将在订阅者上的表中创建发布者上表中的数据。您可以使用以下 SQL 查询来验证是否已发生这种情况。

    SELECT count(*) FROM docs_lab_table; count ------- 5000 (1 row)

此后,对发布者上的表所做的更改将复制到订阅者上的表中。

在主要升级后重新建立逻辑复制

对于设置为逻辑复制的发布者节点的 RDS for PostgreSQL 数据库实例,在可以对其执行主要版本升级之前,您必须删除所有复制插槽,即使是不活动的复制插槽也是如此。我们建议您暂时从发布者节点转移数据库事务,删除复制插槽,升级 RDS for PostgreSQL 数据库实例,然后重新建立并重新启动复制。

复制插槽仅托管在发布者节点上。逻辑复制场景中的 RDS for PostgreSQL 订阅者节点没有可删除的插槽,但当它被指定为对发布者具有订阅的订阅者节点时,它无法升级到主要版本。在升级 RDS for PostgreSQL 订阅者节点之前,请删除订阅和节点。有关更多信息,请参阅。管理 RDS for PostgreSQL 的逻辑复制查槽

确定逻辑复制已中断

您可以通过查询发布者节点或订阅者节点来确定复制过程是否已中断,如下所示。

检查发布者节点
  • 使用 psql 连接到发布者节点,然后查询 pg_replication_slots 函数。注意活动列中的值。通常,这将返回 t(true),表明复制处于活动状态。如果查询返回 f(false),则表明向订阅者的复制已停止。

    SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots; slot_name | plugin | slot_type | active -------------------------------------------+------------------+-----------+-------- pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f (1 row)
检查订阅者节点

在订阅者节点上,您可以通过三种不同的方式检查复制的状态。

  • 浏览订阅者节点上的 PostgreSQL 日志,以查找失败消息。该日志使用包含退出代码 1 的消息来标识故障,如下所示。

    2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1 2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
  • 查询 pg_replication_origin 函数。使用 psql 连接到订阅者节点上的数据库并查询 pg_replication_origin 函数,如下所示。

    SELECT * FROM pg_replication_origin; roident | roname ---------+-------- (0 rows)

    结果集为空表示复制已中断。正常情况下,您将看到如下输出。

    roident | roname ---------+---------------------------------------------------- 1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c (1 row)
  • 查询 pglogical.show_subscription_status 函数,如以下示例所示。

    SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status(); subscription_name | status | slot_name ---====----------------+--------+------------------------------------- docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c (1 row)

    此输出显示复制已中断。它的状态为 down。通常,输出将状态显示为 replicating

如果您的逻辑复制过程已中断,则可以按照以下步骤重新建立复制。

在发布者节点和订阅者节点之间重新建立逻辑复制

要重新建立复制,请先断开订阅者与发布者节点的连接,然后重新建立订阅,如这些步骤所述。

  1. 使用 psql 连接到订阅者节点,如下所示。

    psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
  2. 通过使用 pglogical.alter_subscription_disable 函数停用订阅。

    SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true); alter_subscription_disable ---------------------------- t (1 row)
  3. 通过查询 pg_replication_origin 获取发布者节点的标识符,如下所示。

    SELECT * FROM pg_replication_origin; roident | roname ---------+------------------------------------- 1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c (1 row)
  4. 将上一步的响应与 pg_replication_origin_create 命令一起使用,以分配重新建立时订阅可以使用的标识符。

    SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c'); pg_replication_origin_create ------------------------------ 1 (1 row)
  5. 通过传递其状态为 true 的名称来打开订阅,如下面的示例所示。

    SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true); alter_subscription_enable --------------------------- t (1 row)

检查节点的状态。其状态应为 replicating,如本例所示。

SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status(); subscription_name | status | slot_name -------------------------------+-------------+------------------------------------- docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c (1 row)

检查发布者节点上订阅者的复制插槽的状态。插槽的 active 列应返回 t(true),表示已重新建立复制。

SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots; slot_name | plugin | slot_type | active -------------------------------------------+------------------+-----------+-------- pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t (1 row)

管理 RDS for PostgreSQL 的逻辑复制查槽

对于在逻辑复制场景中充当发布者节点的 RDS for PostgreSQL 数据库实例,在对其执行主要版本升级之前,必须删除该实例上的复制插槽。主要版本升级预检查过程会通知您,在插槽被删除之前,升级无法继续。

要从 RDS for PostgreSQL 数据库实例中删除插槽,请先删除订阅,然后删除插槽。

要识别使用 pglogical 扩展创建的复制插槽,请登录到每个数据库并获取节点的名称。当您查询订阅者节点时,您会在输出中得到发布者节点和订阅者节点,如本示例所示。

SELECT * FROM pglogical.node; node_id | node_name ------------+------------------- 2182738256 | docs_lab_target 3410995529 | docs_lab_provider (2 rows)

您可以通过以下查询获取有关订阅的详细信息。

SELECT sub_name,sub_slot_name,sub_target FROM pglogical.subscription; sub_name | sub_slot_name | sub_target ----------+--------------------------------+------------ docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256 (1 row)

现在您可以删除订阅,如下所示。

SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription'); drop_subscription ------------------- 1 (1 row)

删除订阅后,您可以删除该节点。

SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber'); drop_node ----------- t (1 row)

您可以验证该节点是否不再存在,如下所示。

SELECT * FROM pglogical.node; node_id | node_name ---------+----------- (0 rows)

pglogical 扩展的参数参考

在表中,您可以找到与 pglogical 扩展关联的参数。pglogical.conflict_log_levelpglogical.conflict_resolution 等参数用于处理更新冲突。当对订阅来自发布者的更改的相同表进行本地更改时,可能会出现冲突。在不同情况下也可能发生冲突,例如双向复制或当多个订阅者从同一个发布者进行复制时。有关更多信息,请参阅使用 pglogical 进行 PostgreSQL 双向复制

参数 描述

pglogical.batch_inserts

在可能时执行批量插入。原定设置情况下未设置。更改为“1”将打开,更改为“0”将关闭。

pglogical.conflict_log_level

设置用于记录已解决的冲突的日志级别。支持的字符串值为 debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic。

pglogical.conflict_resolution

设置在冲突可以解决时用来解决冲突的方法。支持的字符串值为 error、apply_remote、keep_local、last_update_wins、first_update_wins。

pglogical.extra_connection_options

要添加到所有对等节点连接的连接选项。

pglogical.synchronous_commit

pglogical 专用的同步提交值

pglogical.use_spi

使用 SPI(服务器编程接口)而非低级 API 来应用更改。设置为“1”将打开,设置为“0”将关闭。有关 SPI 的更多信息,请参阅 PostgreSQL 文档中的服务器编程接口

使用 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