# 将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用 您可以通过安装各种扩展和模块来扩展 PostgreSQL 的功能。例如，要处理空间数据，您可以安装和使用 PostGIS 扩展。有关更多信息，请参阅 [使用 PostGIS 扩展管理空间数据](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)。作为另一个例子，如果要改进非常大的表的数据输入，您可以考虑使用 `pg_partman` 扩展对数据进行分区。要了解更多信息，请参阅[使用 pg\$1partman 扩展管理 PostgreSQL 分区](PostgreSQL_Partitions.md)。 **注意** RDS for PostgreSQL 通过 `pg_tle` 扩展支持 PostgreSQL 的可信语言扩展，您可以将该扩展添加到数据库实例中。通过使用此扩展，开发人员可以在安全的环境中创建自己的 PostgreSQL 扩展，从而简化设置和配置要求。要了解支持 `pg_tle` 扩展的 RDS for PostgreSQL 版本以及更多信息，请参阅[使用适用于 PostgreSQL 的可信语言扩展](PostgreSQL_trusted_language_extension.md)。 在某些情况下，您可以将特定模块添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组中的 `shared_preload_libraries` 列表中，而不是安装扩展。通常，默认的数据库集群参数组仅加载 `pg_stat_statements`，但还有其他几个模块可供添加到此列表中。例如，您可以通过添加 `pg_cron` 模块来添加调度功能，详情请见[使用 PostgreSQL pg\$1cron 扩展计划维护](PostgreSQL_pg_cron.md)。再举一个例子，您可以通过加载 `auto_explain` 模块来记录查询执行计划。要了解更多信息，请参阅 AWS 知识中心中的[记录查询执行计划](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#)。 根据您的 RDS for PostgreSQL 版本，安装扩展可能需要 `rds_superuser` 权限，如下所示： + 对于 RDS for PostgreSQL 版本 12 和更早版本，安装扩展需要 `rds_superuser` 权限。 + 对于 RDS for PostgreSQL 版本 13 及更高版本，对给定数据库实例具有创建权限的用户（角色）可以安装和使用任何*可信扩展*。有关可信扩展清单，请参阅[PostgreSQL 可信扩展](PostgreSQL.Concepts.General.FeatureSupport.Extensions.md#PostgreSQL.Concepts.General.Extensions.Trusted)。 您还可以通过在 `rds.allowed_extensions` 参数中列出扩展，来精确指定可以在 RDS for PostgreSQL 数据库实例上安装的扩展。有关更多信息，请参阅 [限制 PostgreSQL 扩展的安装](PostgreSQL.Concepts.General.FeatureSupport.Extensions.md#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction)。 要了解有关 `rds_superuser` 角色的更多信息，请参阅[了解 PostgreSQL 角色和权限](Appendix.PostgreSQL.CommonDBATasks.Roles.md)。 **Topics** + [ # 使用 orafce 扩展中的函数 ](Appendix.PostgreSQL.CommonDBATasks.orafce.md) + [ # 使用适用于 PostgreSQL 的 Amazon RDS 委派扩展支持 ](RDS_delegated_ext.md) + [ # 使用 pg\$1partman 扩展管理 PostgreSQL 分区 ](PostgreSQL_Partitions.md) + [ # 使用 pgAudit 记录数据库活动 ](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md) + [ # 使用 PostgreSQL pg\$1cron 扩展计划维护 ](PostgreSQL_pg_cron.md) + [ # 使用 pglogical 跨实例同步数据 ](Appendix.PostgreSQL.CommonDBATasks.pglogical.md) + [ # 使用 pgactive 支持主动-主动复制 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.md) + [ # 使用 pg\$1repack 扩展减少表和索引的膨胀 ](Appendix.PostgreSQL.CommonDBATasks.pg_repack.md) + [ # 升级和使用 PLV8 扩展 ](PostgreSQL.Concepts.General.UpgradingPLv8.md) + [ # 使用 PL/Rust 通过 Rust 语言编写 PostgreSQL 函数 ](PostgreSQL.Concepts.General.Using.PL_Rust.md) + [ # 使用 PostGIS 扩展管理空间数据 ](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md) # 使用 orafce 扩展中的函数 orafce 扩展提供了模拟 Oracle 数据库中的函数和软件包子集的函数和运算符。orafce 扩展使您能够更轻松地将 Oracle 应用程序移植到 PostgreSQL。RDS for PostgreSQL 版本 9.6.6 及更高版本支持该扩展。有关 orafce 的更多信息，请参阅 GitHub 上的 [orafce](https://github.com/orafce/orafce)。 **注意** RDS for PostgreSQL 不支持属于 orafce 扩展一部分的 `utl_file` 软件包。这是因为 `utl_file` 架构函数可提供对操作系统文本文件的读取和写入操作，这需要对底层主机拥有超级用户访问权限。作为一项托管服务，RDS for PostgreSQL 不提供主机访问权限。 **使用 orafce 扩展** 1. 使用用于创建数据库实例的主用户名连接到数据库实例。 如果要为同一数据库实例中的不同数据库启用 orafce，请使用 `/c dbname` psql 命令。使用此命令，您可以在启动连接后从主数据库进行更改。 1. 使用 `CREATE EXTENSION` 语句启用 orafce 扩展。 ``` CREATE EXTENSION orafce; ``` 1. 使用 `ALTER SCHEMA` 语句将 oracle 架构的所有权移交给 rds\$1superuser 角色。 ``` ALTER SCHEMA oracle OWNER TO rds_superuser; ``` 如果要查看 oracle 架构的所有者列表，请使用 `\dn` psql 命令。 # 使用适用于 PostgreSQL 的 Amazon RDS 委派扩展支持 使用适用于 PostgreSQL 的 Amazon RDS 委派扩展支持，您可以将扩展管理委派给其它用户，而且不必让该用户成为 `rds_superuser`。通过这种委派扩展支持，将创建一个名为 `rds_extension` 的新角色，您必须将其分配给用户才能管理其他扩展。此角色可以创建、更新和删除扩展。 可以通过在 `rds.allowed_extensions` 参数中列出扩展，指定可以在 RDS 数据库实例上安装哪些扩展。有关更多信息，请参阅[将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html)。 您可以通过 `rds.allowed_delegated_extensions` 参数来限制具有 `rds_extension` 角色的用户可以管理的可用扩展列表。 委派扩展支持在以下版本中可用： + 所有更高版本 + 16.4 及更高的 16 版本 + 15.8 及更高的 15 版本 + 14.13 及更高的 14 版本 + 13.16 及更高的 13 版本 + 12.20 及更高的 12 版本 **Topics** + [ ## 为用户开启委派扩展支持 ](#RDSPostgreSQL.delegated_ext_mgmt) + [ ## 在适用于 PostgreSQL 的 RDS 委派扩展支持中使用的配置 ](#RDSPostgreSQL.delegated_ext_config) + [ ## 关闭对委派扩展的支持 ](#RDSPostgreSQL.delegated_ext_disable) + [ ## 使用 Amazon RDS 委派扩展支持的优势 ](#RDSPostgreSQL.delegated_ext_benefits) + [ ## 适用于 PostgreSQL 的 Amazon RDS 委派扩展支持的限制 ](#RDSPostgreSQL.delegated_ext_limit) + [ ## 某些扩展所需的权限 ](#RDSPostgreSQL.delegated_ext_perm) + [ ## 安全考虑因素 ](#RDSPostgreSQL.delegated_ext_sec) + [ ## 已禁用删除扩展级联 ](#RDSPostgreSQL.delegated_ext_drop) + [ ## 可以使用委派扩展支持添加的扩展示例 ](#RDSPostgreSQL.delegated_ext_support) ## 为用户开启委派扩展支持 必须执行以下操作才能为用户启用委派扩展支持： 1. **向用户授予 `rds_extension` 角色**：以 `rds_superuser` 身份连接到数据库并执行以下命令： ``` Postgres => grant rds_extension to user_name; ``` 1. **设置可供委派用户管理的扩展列表**：`rds.allowed_delegated_extensions` 允许您使用 `rds.allowed_extensions` 在数据库集群参数中指定可用扩展的子集。您可以在以下级别之一执行此操作： + 在集群或实例参数组中，通过 AWS 管理控制台或 API。有关更多信息，请参阅 [Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。 + 在数据库级别使用以下命令： ``` alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1, extension_name_2,...extension_name_n'; ``` + 在用户级别使用以下命令： ``` alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1, extension_name_2,...extension_name_n'; ``` **注意** 更改 `rds.allowed_delegated_extensions` 动态参数后，无需重启数据库。 1. **允许委派用户访问在扩展创建过程中创建的对象**：某些扩展创建的对象要求先授予其它权限，然后具有 `rds_extension` 角色的用户才能访问它们。`rds_superuser` 必须向委派用户授予对这些对象的访问权限。其中一个选项是使用事件触发器自动向委派用户授予权限。 **事件触发器示例** 如果您希望允许具有 `rds_extension` 的委派用户使用扩展，而此类扩展要求对通过扩展创建过程所创建的对象设置权限，则可以自定义以下事件触发器示例，并仅添加您希望委派用户有权访问其全部功能的扩展。此事件触发器可以在 template1（默认模板）上创建，因此所有从 template1 创建的数据库都将具有该事件触发器。当委派用户安装扩展时，此触发器将自动授予对扩展创建的对象的所有权。 ``` CREATE OR REPLACE FUNCTION create_ext() RETURNS event_trigger AS $$ DECLARE schemaname TEXT; databaseowner TEXT; r RECORD; BEGIN IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN RAISE NOTICE 'SECURITY INVOKER'; RAISE NOTICE 'user: %', current_user; FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands() LOOP CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension'; schemaname = ( SELECT n.nspname FROM pg_catalog.pg_extension AS e INNER JOIN pg_catalog.pg_namespace AS n ON e.extnamespace = n.oid WHERE e.oid = r.objid ); databaseowner = ( SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = current_database() ); RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner; IF r.object_identity = 'address_standardizer_data_us' THEN EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner); EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner); EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner); ELSIF r.object_identity = 'dict_int' THEN EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner); ELSIF r.object_identity = 'pg_partman' THEN EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner); EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner); EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner); ELSIF r.object_identity = 'postgis_topology' THEN EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner); EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner); EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner); EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner); END IF; END LOOP; END IF; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext(); ``` ## 在适用于 PostgreSQL 的 RDS 委派扩展支持中使用的配置 | 配置名称 | 说明 | 默认值 | 备注 | 谁可以修改或授予权限 | | --- | --- | --- | --- | --- | | `rds.allowed_delegated_extensions` | 此参数限制 rds\$1extension 角色可以在数据库中管理的扩展。它必须是 rds.allowed\$1extensions 的子集。 | 空字符串 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/RDS_delegated_ext.html) 要了解有关设置此参数的更多信息，请参阅[为用户开启委派扩展支持](#RDSPostgreSQL.delegated_ext_mgmt)。 | rds\$1superuser | | `rds.allowed_extensions` | 此参数可让客户限制可以在 RDS 数据库实例中安装的扩展。有关更多信息，请参阅[限制 PostgreSQL 扩展的安装](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction)。 | "\$1" | 默认情况下，此参数设置为“\$1”，这意味着具有必要权限的用户能够创建 RDS for PostgreSQL 和 Aurora PostgreSQL 支持的所有扩展。 空表示无法在 RDS 数据库实例中安装任何扩展。 | 管理员 | | `rds-delegated_extension_allow_drop_cascade` | 此参数控制拥有 `rds_extension` 的用户使用级联选项删除扩展的能力。 | off | 默认情况下，将 `rds-delegated_extension_allow_drop_cascade` 设置为 `off`。这意味着不允许拥有 `rds_extension` 的用户使用级联选项删除扩展。 要授予该能力，`rds.delegated_extension_allow_drop_cascade` 参数应设置为 `on`。 | rds\$1superuser | ## 关闭对委派扩展的支持 **部分关闭** 委派用户无法创建新的扩展，但仍然可以更新现有的扩展。 + 在数据库集群参数组中将 `rds.allowed_delegated_extensions` 重置为默认值。 + 在数据库级别使用以下命令： ``` alter database database_name reset rds.allowed_delegated_extensions; ``` + 在用户级别使用以下命令： ``` alter user user_name reset rds.allowed_delegated_extensions; ``` **完全关闭** 撤销用户的 `rds_extension` 角色会将该用户恢复为标准权限。用户无法再创建、更新或删除扩展。 ``` postgres => revoke rds_extension from user_name; ``` ## 使用 Amazon RDS 委派扩展支持的优势 通过使用适用于 PostgreSQL 的 Amazon RDS 委派扩展支持，您可以安全地将扩展管理委派给没有 `rds_superuser` 角色的用户。该功能具有以下优势： + 您可以轻松地将扩展管理委派给您选择的用户。 + 这不需要 `rds_superuser` 角色。 + 支持为同一个数据库集群中的不同数据库提供不同的扩展集。 ## 适用于 PostgreSQL 的 Amazon RDS 委派扩展支持的限制 + 在扩展创建过程中创建的对象可能需要额外的权限才能使扩展正常运行。 + 默认情况下，委派扩展用户无法管理某些扩展，包括以下各项：`log_fdw`、`pg_cron`、`pg_tle`、`pgactive`、`pglogical`、`postgis_raster`、`postgis_tiger_geocoder`、`postgis_topology`。 ## 某些扩展所需的权限 要创建、使用或更新以下扩展，委派用户应具有对以下函数、表和架构的必要权限。 | 需要所有权或权限的扩展 | 函数 | 表 | 架构 | 文本搜索词典 | Comment | | --- | --- | --- | --- | --- | --- | | address\$1standardizer\$1data\$1us | none | us\$1gaz、us\$1lex、us\$1lex、I.us\$1rules | none | none | none | | amcheck | bt\$1index\$1check、bt\$1index\$1parent\$1check | none | none | none | none | | dict\$1int | none | none | none | intdict | none | | pg\$1partman | none | custom\$1time\$1partitions、part\$1config、part\$1config\$1sub | none | none | none | | pg\$1stat\$1statements | none | none | none | none | none | | PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | none | none | none | | postgis\$1raster | none | none | none | none | none | | postgis\$1topology | none | topology、layer | topology | none | 委派用户必须是数据库所有者 | | log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | none | none | none | none | | rds\$1tools | role\$1password\$1encryption\$1type | none | none | none | none | | postgis\$1tiger\$1geocoder | none | geocode\$1settings\$1default、geocode\$1settings | tiger | none | none | | pg\$1freespacemap | pg\$1freespace | none | none | none | none | | pg\$1visibility | pg\$1visibility | none | none | none | none | ## 安全考虑因素 请记住，具有 `rds_extension` 角色的用户将能够管理他们拥有连接权限的所有数据库上的扩展。如果打算让委派用户管理单个数据库上的扩展，那么一个好的做法是撤销每个数据库上的公有权限，然后向委派用户显式授予对该特定数据库的连接权限。 有几个扩展可以允许用户访问多个数据库中的信息。在向 `rds.allowed_delegated_extensions` 添加这些扩展之前，请确保您授予 `rds_extension` 的用户具有跨数据库功能。例如，`postgres_fdw` 和 `dblink` 提供在同一实例或远程实例上跨数据库进行查询的功能。`log_fdw` 读取 postgres 引擎日志文件，这些文件适用于实例中的所有数据库，可能包含来自多个数据库的慢速查询或错误消息。`pg_cron` 允许在数据库实例上运行计划的后台任务，并且可以将任务配置为在不同的数据库中运行。 ## 已禁用删除扩展级联 具有 `rds_extension` 角色的用户使用级联选项删除扩展的能力由 `rds.delegated_extension_allow_drop_cascade` 参数控制。默认情况下，将 `rds-delegated_extension_allow_drop_cascade` 设置为 `off`。这意味着不允许具有 `rds_extension` 角色的用户使用级联选项删除扩展，如以下查询所示。 ``` DROP EXTENSION CASCADE; ``` 因为这将自动删除依赖于扩展的对象，进而删除依赖于这些对象的所有对象。尝试使用级联选项将会导致错误。 要授予该能力，`rds.delegated_extension_allow_drop_cascade` 参数应设置为 `on`。 更改 `rds.delegated_extension_allow_drop_cascade` 动态参数不需要重启数据库。您可以在以下级别之一执行此操作： + 在集群或实例参数组中，通过 AWS 管理控制台或 API。 + 在数据库级别使用以下命令： ``` alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on'; ``` + 在用户级别使用以下命令： ``` alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on'; ``` ## 可以使用委派扩展支持添加的扩展示例 + `rds_tools` ``` extension_test_db=> create extension rds_tools; CREATE EXTENSION extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files'; ERROR: permission denied for function role_password_encryption_type ``` + `amcheck` ``` extension_test_db=> CREATE TABLE amcheck_test (id int); CREATE TABLE extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000)); INSERT 0 100000 extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id); CREATE INDEX extension_test_db=> create extension amcheck; CREATE EXTENSION extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass); ERROR: permission denied for function bt_index_check extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass); ERROR: permission denied for function bt_index_parent_check ``` + `pg_freespacemap` ``` extension_test_db=> create extension pg_freespacemap; CREATE EXTENSION extension_test_db=> SELECT * FROM pg_freespace('pg_authid'); ERROR: permission denied for function pg_freespace extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0); ERROR: permission denied for function pg_freespace ``` + `pg_visibility` ``` extension_test_db=> create extension pg_visibility; CREATE EXTENSION extension_test_db=> select * from pg_visibility('pg_database'::regclass); ERROR: permission denied for function pg_visibility ``` + `postgres_fdw` ``` extension_test_db=> create extension postgres_fdw; CREATE EXTENSION extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432'); ERROR: permission denied for foreign-data wrapper postgres_fdw ``` # 使用 pg\$1partman 扩展管理 PostgreSQL 分区 PostgreSQL 表分区为数据输入和报告的高性能处理提供了框架。对于需要非常快速地输入大量数据的数据库，请使用分区。分区还可以更快地查询大型表。分区有助于在不影响数据库实例的情况下维护数据，因为它需要的 I/O 资源较少。 通过使用分区，您可以将数据拆分为自定义大小的块进行处理。例如，您可以将时间序列数据分区为范围，例如每小时、每日、每周、每月、每季度、每年、自定义或这些范围的任意组合。对于时间序列数据示例，如果您按小时对表进行分区，则每个分区会包含一小时的数据。如果您按天对时间序列表进行分区，则分区会保存一天的数据，以此类推。分区键控制分区的大小。 在分区表上使用 `INSERT` 或 `UPDATE` SQL 命令时，数据库引擎会将数据路由到相应的分区。存储数据的 PostgreSQL 表分区是主表的子表。 在数据库查询读取期间，PostgreSQL 优化程序会检查查询的 `WHERE` 子句，如果可能的话，将数据库扫描定向到仅相关分区。 从版本 10 开始，PostgreSQL 使用声明性分区来实现表分区，这也称为本机 PostgreSQL 分区。在 PostgreSQL 版本 10 之前，使用触发器来实现分区。 PostgreSQL 表分区提供了以下功能： + 随时创建新分区。 + 可变分区范围。 + 使用数据定义语言 (DDL) 语句可分离和可重新连接的分区。 例如，可分离的分区对于从主分区中删除历史数据但保留历史数据以供分析很有用。 + 新分区继承了父数据库表的属性，包括以下属性： + 索引 + 主键，其必须包括分区键列 + 外键 + 检查约束 + 参考 + 为完整表或每个特定分区创建索引。 您不能更改单个分区的架构。但是，您可以更改传播到分区的父表（例如添加新列）。 **Topics** + [ ## PostgreSQL pg\$1partman 扩展概述 ](#PostgreSQL_Partitions.pg_partman) + [ ## 启用 pg\$1partman 扩展 ](#PostgreSQL_Partitions.enable) + [ ## 使用 create\$1parent 函数配置分区 ](#PostgreSQL_Partitions.create_parent) + [ ## 使用 run\$1maintenance\$1proc 函数配置分区维护 ](#PostgreSQL_Partitions.run_maintenance_proc) ## PostgreSQL pg\$1partman 扩展概述 您可以使用 PostgreSQL `pg_partman` 扩展自动创建和维护表分区。有关更多一般信息，请参阅 `pg_partman` 文档中的 [PG 分区管理器](https://github.com/pgpartman/pg_partman)。 **注意** RDS for PostgreSQL 版本 12.5 及更高版本支持该 `pg_partman` 扩展。 您可以使用以下设置来配置 `pg_partman`，而不必手动创建每个分区： + 要分区的表 + 分区类型 + 分区键 + 分区粒度 + 分区预创建和管理选项 创建 PostgreSQL 分区表后，您可以通过调用 `create_parent` 函数向 `pg_partman` 注册该表。此举会根据传递给函数的参数创建必要的分区。 该 `pg_partman` 扩展还提供了 `run_maintenance_proc` 函数，您可以按计划调用该函数来自动管理分区。为确保根据需要创建正确的分区，请将此函数计划为定期运行（例如每小时）。您还可以确保自动删除分区。 ## 启用 pg\$1partman 扩展 如果要管理分区的同一 PostgreSQL 数据库实例中有多个数据库，请为每个数据库分别启用 `pg_partman` 扩展。要为特定数据库启用 `pg_partman` 扩展，请创建分区维护架构，然后按如下所示创建 `pg_partman` 扩展。 ``` CREATE SCHEMA partman; CREATE EXTENSION pg_partman WITH SCHEMA partman; ``` **注意** 要创建 `pg_partman` 扩展，请确保您具有 `rds_superuser` 权限。 如果您收到以下错误，请向该账户授予 `rds_superuser` 权限或使用您的超级用户帐户。 ``` ERROR: permission denied to create extension "pg_partman" HINT: Must be superuser to create this extension. ``` 要授予 `rds_superuser` 权限，请连接您的超级用户账户并运行以下命令。 ``` GRANT rds_superuser TO user-or-role; ``` 对于显示使用 pg\$1partman 扩展的示例，我们使用以下示例数据库表和分区。此数据库使用基于时间戳的分区表。架构 `data_mart` 包含名为 `events` 的表，当中包含名为 `created_at` 的列。`events` 表中包含以下设置： + 主键 `event_id` 和 `created_at`，其必须具有用于指导分区的列。 + 用于强制 `ck_valid_operation` 表列值的检查约束 `operation`。 + 两个外键，其中一个 (`fk_orga_membership)`) 指向外部表 `organization`，另一个 (`fk_parent_event_id`) 是自引用的外键。 + 两个索引，其中一个 (`idx_org_id`) 用于外键，另一个 (`idx_event_type`) 用于事件类型。 以下 DDL 语句创建这些对象，这些对象自动包含在每个分区中。 ``` CREATE SCHEMA data_mart; CREATE TABLE data_mart.organization ( org_id BIGSERIAL, org_name TEXT, CONSTRAINT pk_organization PRIMARY KEY (org_id) ); CREATE TABLE data_mart.events( event_id BIGSERIAL, operation CHAR(1), value FLOAT(24), parent_event_id BIGINT, event_type VARCHAR(25), org_id BIGSERIAL, created_at timestamp, CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), CONSTRAINT fk_orga_membership FOREIGN KEY(org_id) REFERENCES data_mart.organization (org_id), CONSTRAINT fk_parent_event_id FOREIGN KEY(parent_event_id, created_at) REFERENCES data_mart.events (event_id,created_at) ) PARTITION BY RANGE (created_at); CREATE INDEX idx_org_id ON data_mart.events(org_id); CREATE INDEX idx_event_type ON data_mart.events(event_type); ``` ## 使用 create\$1parent 函数配置分区 启用 `pg_partman` 扩展后，使用 `create_parent` 函数在分区维护架构中配置分区。以下示例使用在 `events` 中创建的 [启用 pg\$1partman 扩展使用 run\$1maintenance\$1proc 函数配置分区维护](#PostgreSQL_Partitions.enable) 表示例。按如下方式调用 `create_parent` 函数。 ``` SELECT partman.create_parent( p_parent_table => 'data_mart.events', p_control => 'created_at', p_type => 'range', p_interval => '1 day', p_premake => 30); ``` 参数如下所示： + `p_parent_table` – 父分区表。此表必须已存在并完全限定（包括架构在内）。 + `p_control` – 分区所依据的列。数据类型必须是整数或基于时间的。 + `p_type` – 该类型是 `'range'` 或者 `'list'`。 + `p_interval` – 每个分区的时间间隔或整数范围。示例值包括 `1 day`、`1 hour` 等。 + `p_premake` – 为支持新插入而提前创建的分区数量。 有关 `create_parent` 函数的完整说明，请参阅 `pg_partman` 文档中的[创建函数](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions)。 ## 使用 run\$1maintenance\$1proc 函数配置分区维护 您可以运行分区维护操作来自动创建新分区、分离分区或删除旧分区。分区维护依赖于 `pg_partman` 扩展和 `pg_cron` 扩展的 `run_maintenance_proc` 函数，其将启动内部调度程序。调度程序 `pg_cron` 自动执行数据库中定义的 SQL 语句、函数和程序。 以下示例使用在 `events` 中创建的 [启用 pg\$1partman 扩展使用 run\$1maintenance\$1proc 函数配置分区维护](#PostgreSQL_Partitions.enable) 表示例将分区维护操作设置为自动运行。作为先决条件，请将 `pg_cron` 添加到数据库实例的参数组中的 `shared_preload_libraries` 参数。 ``` CREATE EXTENSION pg_cron; UPDATE partman.part_config SET infinite_time_partitions = true, retention = '3 months', retention_keep_table=true WHERE parent_table = 'data_mart.events'; SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$); ``` 以下是前述示例的分步说明： 1. 修改与数据库实例关联的参数组并将 `pg_cron` 添加到 `shared_preload_libraries` 参数值中。此更改需要重启数据库实例才能生效。有关更多信息，请参阅“[在 Amazon RDS 中修改数据库参数组中的参数](USER_WorkingWithParamGroups.Modifying.md)”。 1. `CREATE EXTENSION pg_cron;` 使用具有 `rds_superuser` 权限的账户运行此命令。这将启用 `pg_cron` 扩展。有关更多信息，请参阅“[使用 PostgreSQL pg\$1cron 扩展计划维护](PostgreSQL_pg_cron.md)”。 1. 运行命令 `UPDATE partman.part_config` 以调整 `data_mart.events` 表的 `pg_partman` 设置。 1. 运行命令 `SET`… 配置 `data_mart.events` 表，其中包含以下子句： 1. `infinite_time_partitions = true,` – 将表配置为能够在没有任何限制的情况下自动创建新分区。 1. `retention = '3 months',` – 将表配置为最长保留三个月。 1. `retention_keep_table=true ` – 对表进行配置，以便在保留期到期时表不会自动删除。相反，早于保留期的分区只能从父表中分离。 1. 运行命令 `SELECT cron.schedule`… 创建一个 `pg_cron` 函数调用。此调用定义了计划程序运行 `pg_partman` 维护程序的频率，`partman.run_maintenance_proc`。对于此示例，该程序每小时运行一次。 有关 `run_maintenance_proc` 函数的完整说明，请参阅 `pg_partman` 文档中的[维护函数](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions)。 # 使用 pgAudit 记录数据库活动 金融机构、政府机构和许多行业需要保留*审计日志*以满足监管要求。通过将 PostgreSQL Audit 扩展（pgAudit）与 RDS for PostgreSQL 数据库实例结合使用，您可以捕获审计人员通常需要或满足监管要求的详细记录。例如，您可以设置 pgAudit 扩展来跟踪对特定数据库和表所做的更改，记录进行更改的用户以及许多其他详细信息。 pgAudit 扩展通过更详细地扩展日志消息，进一步构建原生 PostgreSQL 日志记录基础设施的功能。换句话说，您将使用与查看任何日志消息相同的方法来查看审计日志。有关 PostgreSQL 日志记录的更多信息，请参阅 [RDS for PostgreSQL 数据库日志文件](USER_LogAccess.Concepts.PostgreSQL.md)。 pgAudit 扩展会编辑日志中的敏感数据，例如明文密码。如果您的 RDS for PostgreSQL 数据库实例配置为记录数据操作语言（DML）语句（详情请见[为您的 RDS for PostgreSQL 数据库实例开启查询日志记录](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md)），则可以使用 PostgreSQL Audit 扩展来避免明文密码问题。 您可以在数据库实例上配置具有高度明确性的审计。您可以审计所有数据库和所有用户。或者，您可以选择仅审计某些数据库、用户和其他对象。您也可以明确排除对某些用户和数据库进行审计。有关更多信息，请参阅 [从审计日志记录中排除用户或数据库](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)。 考虑到可以捕获的详细信息量，我们建议您在使用 pgAudit 时监控存储消耗。 所有可用的 RDS for PostgreSQL 版本。有关可用 RDS for PostgreSQL 版本支持的 pgAudit 版本的列表，请参阅《Amazon RDS for PostgreSQL 版本注释》**中的 [Amazon RDS for PostgreSQL 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html)。 **Topics** + [ # 设置 pgAudit 扩展 ](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md) + [ # 审计数据库对象 ](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md) + [ # 从审计日志记录中排除用户或数据库 ](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md) + [ # pgAudit 扩展的参考 ](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md) # 设置 pgAudit 扩展 要在 RDS for PostgreSQL 数据库实例 上设置 pgAudit 扩展，首先要将 pgAudit 添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组 上的共享库中。有关创建自定义数据库参数组的信息，请参阅 [Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。接下来，安装 pgAudit 扩展。最后，指定要审计的数据库和对象。本部分中的过程向您展示如何操作。您可以使用 AWS 管理控制台或 AWS CLI。 您必须拥有 `rds_superuser` 角色的权限才能执行所有这些任务。 以下步骤假设您的 RDS for PostgreSQL 数据库实例与自定义 数据库参数组相关联。 ## 控制台 **设置 pgAudit 扩展** 1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 在导航窗格中，选择 RDS for PostgreSQL 数据库实例。 1. 打开 的**配置**选项卡。RDS for PostgreSQL 数据库实例的 Configuration（配置）选项卡。在实例详细信息中，找到 **Parameter group**（参数组）链接。 1. 选择此链接以打开与您的 RDS for PostgreSQL 数据库实例关联的自定义参数。 1. 在 **Parameters**（参数）搜索字段中，键入 `shared_pre` 以查找 `shared_preload_libraries` 参数。 1. 选择 **Edit parameters**（编辑参数）以访问属性值。 1. 将 `pgaudit` 添加到 **Values**（值）字段的列表中。使用逗号分隔值列表中的项目。 ![\[添加了 pgAudit 的 shared_preload_libaries 参数的图像。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/apg_rpg_shared_preload_pgaudit.png) 1. 重启 RDS for PostgreSQL 数据库实例，以使对 `shared_preload_libraries` 参数的更改生效。 1. 当实例可用时，请验证 pgAudit 是否已初始化。使用 `psql` 连接到 RDS for PostgreSQL 数据库实例，然后运行以下命令。 ``` SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row) ``` 1. 初始化 pgAudit 后，您现在可以创建扩展了。您需要在初始化库后创建扩展，因为 `pgaudit` 扩展会为审计数据定义语言（DDL）语句安装事件触发器。 ``` CREATE EXTENSION pgaudit; ``` 1. 关闭 `psql` 会话。 ``` labdb=> \q ``` 1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 在列表中找到 `pgaudit.log` 参数并设置为适合您的使用案例的值。例如，将 `pgaudit.log` 参数设置为 `write`（如下图所示），可以捕获对日志的插入、更新、删除和其他一些类型的更改。 ![\[带有设置的 pgaudit.log 参数的图像。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/rpg_set_pgaudit-log-level.png) 还可以为 `pgaudit.log` 参数选择以下值之一。 + none – 这是默认值。不记录任何数据库更改。 + all – 记录所有内容（read、write、function、role、ddl、misc）。 + ddl – 记录所有数据定义语言（DDL）语句（不包括在 `ROLE` 类中）。 + function – 记录函数调用和 `DO` 块。 + misc – 记录其他命令，例如 `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM` 和 `SET`。 + read –当源为关系（例如表）或查询时记录 `SELECT` 和 `COPY`。 + role – 记录与角色和权限相关的语句，例如 `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE` 和 `DROP ROLE`。 + write – 当目标为关系（表）时，记录 `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE` 和 `COPY`。 1. 选择 **Save changes**（保存更改）。 1. 通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 从“数据库”列表中选择 RDS for PostgreSQL 数据库实例。 ## AWS CLI **设置 pgAudit** 要使用 AWS CLI 设置 pgAudit，您可以调用 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 操作来修改自定义参数组中的审计日志参数，如以下过程所示。 1. 使用以下 AWS 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 ``` 1. 使用以下 AWS CLI 命令重启 RDS for PostgreSQL 数据库实例，以便初始化 pgaudit 库。 ``` aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region ``` 1. 当实例可用时，您可以验证 `pgaudit` 是否已初始化。使用 `psql` 连接到 RDS for PostgreSQL 数据库实例，然后运行以下命令。 ``` SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row) ``` 初始化 pgAudit 后，您现在可以创建扩展了。 ``` CREATE EXTENSION pgaudit; ``` 1. 关闭 `psql` 会话以便您可以使用 AWS CLI。 ``` labdb=> \q ``` 1. 使用以下 AWS 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 – 记录其他命令，例如 `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM` 和 `SET`。 + read –当源为关系（例如表）或查询时记录 `SELECT` 和 `COPY`。 + role – 记录与角色和权限相关的语句，例如 `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE` 和 `DROP ROLE`。 + write – 当目标为关系（表）时，记录 `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE` 和 `COPY`。 使用以下 AWS 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 扩展](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)中的步骤创建了 `pgaudit` 扩展。 ![\[设置 pgAudit 后 PostgreSQL 日志文件的图像。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/pgaudit-log-example.png) 如本示例所示，“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 ``` 1. 使用以下命令创建名为 `rds_pgaudit` 的数据库角色。 ``` labdb=> CREATE ROLE rds_pgaudit; CREATE ROLE labdb=> ``` 1. 关闭 `psql` 会话。 ``` labdb=> \q ``` 在接下来的几步中，使用 AWS CLI 修改自定义参数组中的审计日志参数。 1. 使用以下 AWS 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 ``` 1. 使用以下 AWS CLI 命令重启 RDS for PostgreSQL 数据库实例，以使对参数的更改生效。 ``` aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region ``` 1. 运行以下命令确认 `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 日志文件](USER_LogAccess.md)。 要了解关于 pgAudit 扩展的更多信息，请参阅 GitHub 上的 [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md)。 # 从审计日志记录中排除用户或数据库 如 [RDS for PostgreSQL 数据库日志文件](USER_LogAccess.Concepts.PostgreSQL.md)中所述，PostgreSQL 日志会消耗存储空间。使用 pgAudit 扩展会在不同程度上增加日志中收集的数据量，具体取决于您跟踪的更改。您可能不需要审计 RDS for PostgreSQL 数据库实例中的每个用户或数据库。 为了最大限度地减少对存储的影响，避免不必要地捕获审计记录，您可以将用户和数据库排除在审计范围之外。您还可以在给定会话中更改日志记录。下面的示例向您演示如何操作。 **注意** 会话级别的参数设置优先于 RDS for PostgreSQL 数据库实例的自定义数据库参数组中的设置。如果您不希望数据库用户绕过您的审计日志记录配置设置，请务必更改其权限。 假设您的 RDS for PostgreSQL 数据库实例配置为审计所有用户和数据库的相同级别的活动。然后，您决定不想对用户 `myuser` 进行审计。您可以使用以下 SQL 命令对 `myuser` 关闭审计功能。 ``` ALTER USER myuser SET pgaudit.log TO 'NONE'; ``` 然后，您可以使用以下查询来检查 `pgaudit.log` 的 `user_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\$1specific\$1settings 列，以确认 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` 参数允许的设置列表](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings)。 ``` 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` 参数允许的设置列表](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings)。 | | `pgaudit.log_catalog` | 启用（设置为 1）时，如果语句中的所有关系都在 pg\$1catalog 中，则将语句添加到审计跟踪中。 | | `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` 参数允许的设置列表 | 值 | 说明 | | --- | --- | | none | 这是默认值。不记录任何数据库更改。 | | 全部 | 记录所有内容（read、write、function、role、ddl、misc）。 | | ddl | 记录所有数据定义语言（DDL）语句（不包括在 `ROLE` 类中）。 | | 函数 | 记录函数调用和 `DO` 块。 | | misc | 记录其他命令，例如 `DISCARD`、`FETCH`、`CHECKPOINT`、`VACUUM` 和 `SET`。 | | read | 当源为关系（例如表）或查询时记录 `SELECT` 和 `COPY`。 | | 角色 | 记录与角色和权限相关的语句，例如 `GRANT`、`REVOKE`、`CREATE ROLE`、`ALTER ROLE` 和 `DROP ROLE`。 | | 写入 | 当目标为关系（表）时，记录 `INSERT`、`UPDATE`、`DELETE`、`TRUNCATE` 和 `COPY`。 | 要使用会话审计记录多种事件类型，请使用逗号分隔的列表。要记录所有事件类型，请将 `pgaudit.log` 设置为 `ALL`。重启数据库实例以应用更改。 通过对象审计，您可以细化审计日志记录以使用特定的关系。例如，您可以指定要对一个或多个表上的 `READ` 操作进行审计日志记录。 # 使用 PostgreSQL pg\$1cron 扩展计划维护 您可以使用 PostgreSQL `pg_cron` 扩展来计划 PostgreSQL 数据库中的维护命令。有关扩展的更多信息，请参阅 pg\$1cron 文档中的[什么是 pg\$1cron？](https://github.com/citusdata/pg_cron) RDS for PostgreSQL 引擎版本 12.5 及更高版本支持 `pg_cron` 扩展。 要了解有关使用 `pg_cron` 的更多信息，请参阅[在 RDS for PostgreSQL 或 Aurora PostgreSQL 兼容版数据库上使用 pg\$1cron 计划任务](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/)。 **注意** 在 pg\$1available\$1extensions 视图中，`pg_cron` 扩展版本显示为两位数的版本，例如 1.6。虽然在某些情况下您可能会看到三位数的版本，例如 1.6.4 或 1.6.5，但在执行扩展升级时必须指定两位数的版本。 **Topics** + [ ## 设置 pg\$1cron 扩展 ](#PostgreSQL_pg_cron.enable) + [ ## 授予数据库用户使用 pg\$1cron 的权限 ](#PostgreSQL_pg_cron.permissions) + [ ## 计划 pg\$1cron 作业 ](#PostgreSQL_pg_cron.examples) + [ ## pg\$1cron 扩展的参考 ](#PostgreSQL_pg_cron.reference) ## 设置 pg\$1cron 扩展 按如下方式设置 `pg_cron` 扩展： 1. 通过向 `shared_preload_libraries` 参数值添加 `pg_cron`，修改与 PostgreSQL 数据库实例关联的自定义参数组。 + 如果 RDS for PostgreSQL 数据库实例使用 `rds.allowed_extensions` 参数显式列出可安装的扩展，则需要将 `pg_cron` 扩展添加到列表中。只有某些版本的 RDS for PostgreSQL 支持 `rds.allowed_extensions` 参数。原定设置情况下，允许使用所有可用的扩展。有关更多信息，请参阅 [限制 PostgreSQL 扩展的安装](PostgreSQL.Concepts.General.FeatureSupport.Extensions.md#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction)。 重新启动 PostgreSQL 数据库实例，以使对参数组的更改生效。要了解有关使用参数组的更多信息，请参阅[在 Amazon RDS 中修改数据库参数组中的参数](USER_WorkingWithParamGroups.Modifying.md)。 1. 重新启动 PostgreSQL 数据库实例后，使用具有 `rds_superuser` 权限的账户运行以下命令。例如，如果在创建 RDS for PostgreSQL 数据库实例时使用默认设置，请以用户 `postgres` 身份进行连接，然后创建扩展。 ``` CREATE EXTENSION pg_cron; ``` `pg_cron` 调度程序是在名为 `postgres` 的默认 PostgreSQL 数据库中设置的。这些 `pg_cron` 对象是在此 `postgres` 数据库中创建的，所有调度操作都在此数据库中运行。 1. 您可以使用默认设置，也可以计划作业在 PostgreSQL 数据库实例的其他数据库中运行。要为 PostgreSQL 数据库实例中的其他数据库计划作业，请参阅 [为原定设置数据库以外的数据库计划 cron 任务](#PostgreSQL_pg_cron.otherDB) 中的示例。 ## 授予数据库用户使用 pg\$1cron 的权限 安装 `pg_cron` 扩展需要 `rds_superuser` 权限。但是，可以（由 `pg_cron` 组/角色的成员）将使用 `rds_superuser` 的权限授予其他数据库用户，以便他们可以计划自己的任务。我们建议您仅在需要时才授予对 `cron` 架构的权限，前提是它可以改进生产环境中的操作。 要在 `cron` 架构中授予数据库用户权限，请运行以下命令： ``` postgres=> GRANT USAGE ON SCHEMA cron TO db-user; ``` 这向 *db-user* 授予访问 `cron` 架构的权限，以便为他们有权限访问的对象计划 cron 任务。如果数据库用户没有权限，则在将错误消息发布到 `postgresql.log` 文件后，任务会失败，如下所示： ``` 2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name 2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1 ``` 换言之，请确保被授予对 `cron` 架构的权限的数据库用户也对他们计划安排的对象（表、架构等）拥有权限。 cron 任务的详细信息及其成功或失败情况也在 `cron.job_run_details` 表中捕获。有关更多信息，请参阅 [用于计划任务和捕获状态的表](#PostgreSQL_pg_cron.tables)。 ## 计划 pg\$1cron 作业 以下各节介绍了如何使用 `pg_cron` 作业安排各种管理任务。 **注意** 创建 `pg_cron` 任务时，请检查 `max_worker_processes` 设置是否大于 `cron.max_running_jobs` 的数量。如果 `pg_cron` 任务耗尽后台工作进程，它将失败。原定设置的 `pg_cron` 任务数量为 `5`。有关更多信息，请参阅 [用于管理 pg\$1cron 扩展的参数](#PostgreSQL_pg_cron.parameters)。 **Topics** + [ ### 对表执行清理操作 ](#PostgreSQL_pg_cron.vacuum) + [ ### 清除 pg\$1cron 历史记录表 ](#PostgreSQL_pg_cron.job_run_details) + [ ### 仅将错误记录到 postgresql.log 文件中 ](#PostgreSQL_pg_cron.log_run) + [ ### 为原定设置数据库以外的数据库计划 cron 任务 ](#PostgreSQL_pg_cron.otherDB) ### 对表执行清理操作 Autovacuum 在大多数情况下处理清理维护。但是，您可能希望在选择的时间计划对特定表执行清理操作。 另请参阅[在 Amazon RDS for PostgreSQL 上使用 PostgreSQL autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md)。 以下示例介绍了使用 `cron.schedule` 函数设置作业，以便每天 22:00 (GMT) 在特定表上使用 `VACUUM FREEZE`。 ``` SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts'); schedule ---------- 1 (1 row) ``` 运行上述示例之后，您可以按如下方式检查 `cron.job_run_details` 表中的历史记录。 ``` postgres=> SELECT * FROM cron.job_run_details; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time -------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+------------------------------- 1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00 (1 row) ``` 下面的内容说明如何查询 `cron.job_run_details` 表以查看失败的任务。 ``` postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed'; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time ------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------ 5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00 (1 row) ``` 有关更多信息，请参阅 [用于计划任务和捕获状态的表](#PostgreSQL_pg_cron.tables)。 ### 清除 pg\$1cron 历史记录表 `cron.job_run_details` 表包含 cron 作业的历史记录，随着时间的推移，这些历史记录可能会变得非常大。我们建议您计划清除此表的作业。例如，保留一周的条目可能足以进行故障排除。 以下示例使用 [cron.schedule](#PostgreSQL_pg_cron.schedule) 函数计划每天午夜运行以清除 `cron.job_run_details` 表的作业。这项工作只保留了过去七天的历史记录。使用您的 `rds_superuser` 账户计划作业，如下所示。 ``` SELECT cron.schedule('0 0 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$); ``` 有关更多信息，请参阅 [用于计划任务和捕获状态的表](#PostgreSQL_pg_cron.tables)。 ### 仅将错误记录到 postgresql.log 文件中 要防止向 `cron.job_run_details` 表中进行写入，请修改与 PostgreSQL 数据库实例关联的参数组，然后将 `cron.log_run` 参数设置为 off。`pg_cron` 扩展不再写入表，只会将错误捕获到 `postgresql.log` 文件中。有关更多信息，请参阅 [在 Amazon RDS 中修改数据库参数组中的参数](USER_WorkingWithParamGroups.Modifying.md)。 使用以下命令检查 `cron.log_run` 参数的值。 ``` postgres=> SHOW cron.log_run; ``` 有关更多信息，请参阅 [用于管理 pg\$1cron 扩展的参数](#PostgreSQL_pg_cron.parameters)。 ### 为原定设置数据库以外的数据库计划 cron 任务 `pg_cron` 的元数据全部保存在名为 `postgres` 的 PostgreSQL 默认数据库中。由于后台工件用于运行维护 cron 作业，因此您可以在 PostgreSQL 数据库实例中的任何数据库中计划作业。 **注意** 只有具有 `rds_superuser` 角色或 `rds_superuser` 权限的用户才能列出数据库中的所有 cron 作业。其他用户只能在 `cron.job` 表中查看其自己的作业。 1. 在 cron 数据库中，以与平常使用 [cron.schedule](#PostgreSQL_pg_cron.schedule) 相同的方式计划作业。 ``` postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table'); ``` 1. 作为具有 `rds_superuser` 角色的用户，请更新刚创建的作业的数据库列，使其在 PostgreSQL 数据库实例中的另一个数据库中运行。 ``` postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106; ``` 1. 通过查询 `cron.job` 表进行验证。 ``` postgres=> SELECT * FROM cron.job; jobid | schedule | command | nodename | nodeport | database | username | active | jobname ------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+------------------------- 106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum 1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum (2 rows) ``` **注意** 在某些情况下，您可以添加打算在其他数据库上运行的 cron 作业。在这些情况下，在您更新正确的数据库列之前，该作业可能会尝试在默认数据库 (`postgres`) 中运行。如果用户名具有权限，则作业将在默认数据库中成功运行。 ## pg\$1cron 扩展的参考 您可以将以下参数、函数和表与 `pg_cron` 扩展搭配使用。有关更多信息，请参阅 pg\$1cron 文档中的[什么是 pg\$1cron？](https://github.com/citusdata/pg_cron)。 **Topics** + [ ### 用于管理 pg\$1cron 扩展的参数 ](#PostgreSQL_pg_cron.parameters) + [ ### 函数参考：cron.schedule ](#PostgreSQL_pg_cron.schedule) + [ ### 函数参考：cron.unschedule ](#PostgreSQL_pg_cron.unschedule) + [ ### 用于计划任务和捕获状态的表 ](#PostgreSQL_pg_cron.tables) ### 用于管理 pg\$1cron 扩展的参数 以下是用于控制 `pg_cron` 扩展行为的参数列表。 | 参数 | 说明 | | --- | --- | | cron.database\$1name | 保存 `pg_cron` 元数据的数据库。 | | cron.host | 要连接到 PostgreSQL 的主机名。您无法修改此值。 | | cron.log\$1run | 在 `job_run_details` 表中记录运行的每个任务。值为 `on` 或 `off`。有关更多信息，请参阅“[用于计划任务和捕获状态的表](#PostgreSQL_pg_cron.tables)”。 | | cron.log\$1statement | 在运行所有 cron 语句之前将其记入日志。值为 `on` 或 `off`。 | | cron.max\$1running\$1jobs | 可以同时运行的最大作业数。 | | cron.use\$1background\$1workers | 使用后台工作程序而不是客户端会话。您无法修改此值。 | 使用以下 SQL 命令来显示这些参数及其值。 ``` postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name; ``` ### 函数参考：cron.schedule 此函数计划 cron 作业。作业最初是在默认 `postgres` 数据库中计划的。该函数返回一个表示作业标识符的 `bigint` 值。要计划作业在 PostgreSQL 数据库实例的其他数据库中运行，请参阅 [为原定设置数据库以外的数据库计划 cron 任务](#PostgreSQL_pg_cron.otherDB) 中的示例。 该函数有两种语法格式。 **语法** ``` cron.schedule (job_name, schedule, command ); cron.schedule (schedule, command ); ``` **参数** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html) **示例** ``` postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history'); schedule ---------- 145 (1 row) postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts'); schedule ---------- 146 (1 row) ``` ### 函数参考：cron.unschedule 此函数删除 cron 作业。您可以指定 `job_name` 或 `job_id`。策略可以确保您是删除作业计划的拥有者。该函数返回一个布尔值，指示成功或失败。 该函数使用以下语法格式。 **语法** ``` cron.unschedule (job_id); cron.unschedule (job_name); ``` **参数** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html) **示例** ``` postgres=> SELECT cron.unschedule(108); unschedule ------------ t (1 row) postgres=> SELECT cron.unschedule('test'); unschedule ------------ t (1 row) ``` ### 用于计划任务和捕获状态的表 将以下各表用于计划 cron 作业和记录作业完成的方式。 | 表 | 描述 | | --- | --- | | cron.job | 包含有关每个计划作业的元数据。与此表的大多数交互应使用 `cron.schedule` 和 `cron.unschedule` 函数完成。 我们不建议直接授予对此表的更新或插入权限。这样做将允许用户更新 `username` 列，从而以 `rds-superuser` 身份运行。 | | cron.job\$1run\$1details | 包含过去运行的计划作业的历史信息。这对于调查运行的作业的状态、返回消息以及开始和结束时间非常有用。 为了防止此表无限增长，请定期清除此表。有关示例，请参阅[清除 pg\$1cron 历史记录表](#PostgreSQL_pg_cron.job_run_details)。 | # 使用 pglogical 跨实例同步数据 所有当前可用的 RDS for PostgreSQL 版本都支持 `pglogical` 扩展。pglogic 扩展早于 PostgreSQL 在版本 10 中引入的功能类似的逻辑复制特征。有关更多信息，请参阅[为 Amazon RDS for PostgreSQL 执行逻辑复制](PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication.md)。 `pglogical` 扩展支持在两个或更多 RDS for PostgreSQL 数据库实例之间进行逻辑复制。它还支持在不同的 PostgreSQL 版本之间进行复制，以及在 RDS for PostgreSQL 数据库实例和 Aurora PostgreSQL 数据库集群上运行的数据库之间进行复制。`pglogical` 扩展使用发布-订阅模型将对表和其他对象（例如序列）的更改从发布者复制到订阅者。它依赖于复制插槽来确保更改从发布者节点同步到订阅者节点，定义如下。 + *发布者节点*是作为要复制到其他节点的数据来源的 RDS for PostgreSQL 数据库实例。发布者节点定义要在发布集中复制的表。 + *订阅者节点*是用于接收来自发布商的 WAL 更新的 RDS for PostgreSQL 数据库实例。订阅者创建订阅以连接到发布者并获取解码后的 WAL 数据。订阅者创建订阅时，将在发布者节点上创建复制插槽。 在下文中，您可以了解有关设置 `pglogical` 扩展的信息。 **Topics** + [ ## pglogical 扩展的要求和限制 ](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations) + [ # 设置 pglogical 扩展 ](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md) + [ # 为 RDS for PostgreSQL 数据库实例设置逻辑复制 ](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md) + [ # 在主要升级后重新建立逻辑复制 ](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md) + [ # 管理 RDS for PostgreSQL 的逻辑复制插槽 ](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md) + [ # pglogical 扩展的参数参考 ](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md) ## pglogical 扩展的要求和限制 所有当前可用的 RDS for PostgreSQL 版本都支持 `pglogical` 扩展。 发布者节点和订阅者节点都必须设置为进行逻辑复制。 要从发布者复制到订阅用户的表必须具有相同的名称和相同的架构。这些表还必须包含相同的列，并且这些列必须使用相同的数据类型。发布者表和订阅者表必须具有相同的主键。我们建议您仅使用 PRIMARY KEY 作为唯一约束。 对于 CHECK 约束和 NOT NULL 约束，订阅者节点上的表可能比发布者节点上的表具有更宽松的约束。 `pglogical` 扩展提供了诸如双向复制之类的特征，PostgreSQL（版本 10 及更高版本）中内置的逻辑复制特征不支持这些特征。有关更多信息，请参阅[使用 pglogic 进行 PostgreSQL 双向复制](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)。 # 设置 pglogical 扩展 要在 RDS for PostgreSQL 数据库实例上设置 `pglogical` 扩展，首先要将 `pglogical` 添加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组上的共享库中。您还需要将 `rds.logical_replication` 参数的值设置为 `1`，以开启逻辑解码。最后，在数据库中创建此扩展。您可以使用 AWS 管理控制台或 AWS CLI 执行这些任务。 您必须拥有 `rds_superuser` 角色的权限才能执行这些任务。 以下步骤假设您的 RDS for PostgreSQL 数据库实例与自定义数据库参数组相关联。有关创建自定义数据库参数组的信息，请参阅 [Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。 ## 控制台 **设置 pglogical 扩展** 1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 在导航窗格中，选择 RDS for PostgreSQL 数据库实例。 1. 打开 的**配置**选项卡。RDS for PostgreSQL 数据库实例的 Configuration（配置）选项卡。在实例详细信息中，找到 **Parameter group**（参数组）链接。 1. 选择此链接以打开与您的 RDS for PostgreSQL 数据库实例关联的自定义参数。 1. 在 **Parameters**（参数）搜索字段中，键入 `shared_pre` 以查找 `shared_preload_libraries` 参数。 1. 选择 **Edit parameters**（编辑参数）以访问属性值。 1. 将 `pglogical` 添加到 **Values**（值）字段的列表中。使用逗号分隔值列表中的项目。 ![\[添加了 pglogical 的 shared_preload_libraries 参数的图像。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/apg_rpg_shared_preload_pglogical.png) 1. 找到 `rds.logical_replication` 参数并将其设置为 `1`，以开启逻辑复制。 1. 重启 RDS for PostgreSQL 数据库实例，以使更改生效。 1. 当实例可用时，可以使用 `psql`（或 pgAdmin）连接到 RDS for PostgreSQL 数据库实例。 ``` psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb ``` 1. 要验证 pglogical 是否初始化，可以运行以下命令。 ``` SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pglogical (1 row) ``` 1. 验证启用逻辑解码的设置，如下所示。 ``` SHOW wal_level; wal_level ----------- logical (1 row) ``` 1. 创建扩展，如下所示。 ``` CREATE EXTENSION pglogical; EXTENSION CREATED ``` 1. 选择**保存更改**。 1. 通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 从数据库列表中选择 RDS for PostgreSQL 数据库实例以将其选中，然后从 Actions（操作）菜单中选择 **Reboot**（重启）。 ## AWS CLI **设置 pglogical 扩展** 要使用 AWS CLI 设置 pglogical，您可以调用 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 操作来修改自定义参数组中的某些参数，如以下过程所示。 1. 使用以下 AWS 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 ``` 1. 使用以下 AWS 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 ``` 1. 使用以下 AWS CLI 命令重启 RDS for PostgreSQL 数据库实例，以便初始化 pglogical 库。 ``` aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region ``` 1. 当实例可用时，使用 `psql` 连接到 RDS for PostgreSQL 数据库实例。 ``` psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb ``` 1. 创建扩展，如下所示。 ``` CREATE EXTENSION pglogical; EXTENSION CREATED ``` 1. 使用以下 AWS CLI 命令重启 RDS for PostgreSQL 数据库实例。 ``` aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region ``` # 为 RDS for PostgreSQL 数据库实例设置逻辑复制 以下过程说明如何在两个 RDS for PostgreSQL 数据库实例之间启动逻辑复制。这些步骤假设来源（发布者）和目标（订阅者）都如[设置 pglogical 扩展](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)中所述设置了 `pglogical` 扩展。 **注意** 订阅用户节点的 `node_name` 不能以 `rds` 开头。 **创建发布者节点并定义要复制的表** 这些步骤假设您的 RDS for PostgreSQL 数据库实例有一个数据库，其中包含一个或多个您要复制到另一个节点的表。您需要在订阅者上根据发布者重新创建表结构，因此，如果需要，首先获取表结构。为此，您可以使用 `psql` 元命令 `\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); ``` 1. 使用以下 SQL 语句用生成的数据填充表。 ``` INSERT INTO docs_lab_table VALUES (generate_series(1,5000)); INSERT 0 5000 ``` 1. 使用以下 SQL 语句验证表中是否存在数据。 ``` SELECT count(*) FROM docs_lab_table; ``` 1. 将这一 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) ``` 1. 将要复制的表添加到默认的复制集。有关复制集的更多信息，请参阅 pglogical 文档中的[复制集](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets)。 ``` 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 扩展](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)。 1. 使用 `psql` 连接到要从发布者接收更新的实例。 ``` psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb ``` 1. 在订阅者 RDS for PostgreSQL 数据库实例上，创建与发布者上存在的相同表。在本例中，该表为 `docs_lab_table`。您可以按如下所示创建表。 ``` CREATE TABLE docs_lab_table (a int PRIMARY KEY); ``` 1. 验证此表为空。 ``` SELECT count(*) FROM docs_lab_table; count ------- 0 (1 row) ``` 1. 将这一 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) ``` 1. 创建订阅。 ``` 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 的逻辑复制插槽](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md) ## 确定逻辑复制已中断 您可以通过查询发布者节点或订阅者节点来确定复制过程是否已中断，如下所示。 **检查发布者节点** + 使用 `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 ``` 1. 通过使用 `pglogical.alter_subscription_disable` 函数停用订阅。 ``` SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true); alter_subscription_disable ---------------------------- t (1 row) ``` 1. 通过查询 `pg_replication_origin` 获取发布者节点的标识符，如下所示。 ``` SELECT * FROM pg_replication_origin; roident | roname ---------+------------------------------------- 1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c (1 row) ``` 1. 将上一步的响应与 `pg_replication_origin_create` 命令一起使用，以分配重新建立时订阅可以使用的标识符。 ``` SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c'); pg_replication_origin_create ------------------------------ 1 (1 row) ``` 1. 通过传递其状态为 `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_level` 和 `pglogical.conflict_resolution` 等参数用于处理更新冲突。当对订阅来自发布者的更改的相同表进行本地更改时，可能会出现冲突。在不同情况下也可能发生冲突，例如双向复制或当多个订阅者从同一个发布者进行复制时。有关更多信息，请参阅[使用 pglogical 进行 PostgreSQL 双向复制](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)。 | 参数 | 说明 | | --- | --- | | pglogical.batch\$1inserts | 在可能时执行批量插入。默认情况下未设置。更改为“1”将打开，更改为“0”将关闭。 | | pglogical.conflict\$1log\$1level | 设置用于记录已解决的冲突的日志级别。支持的字符串值为 debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic。 | | pglogical.conflict\$1resolution | 设置在冲突可以解决时用来解决冲突的方法。支持的字符串值为 error、apply\$1remote、keep\$1local、last\$1update\$1wins、first\$1update\$1wins。 | | pglogical.extra\$1connection\$1options | 要添加到所有对等节点连接的连接选项。 | | pglogical.synchronous\$1commit | pglogical 专用的同步提交值 | | pglogical.use\$1spi | 使用 SPI（服务器编程接口）而非低级 API 来应用更改。设置为“1”将打开，设置为“0”将关闭。有关 SPI 的更多信息，请参阅 PostgreSQL 文档中的[服务器编程接口](https://www.postgresql.org/docs/current/spi.html)。 | # 使用 pgactive 支持主动-主动复制 `pgactive` 扩展使用主动-主动复制来支持和协调多个 RDS for PostgreSQL 数据库上的写入操作。Amazon RDS for PostgreSQL 支持以下版本的 `pgactive` 扩展。 + RDS for PostgreSQL 17.0 和所有更高版本 + RDS for PostgreSQL 16.1 及更高的 16 版本 + RDS for PostgreSQL 15.4-R2 及更高的 15 版本 + RDS for PostgreSQL 14.10 及更高的 14 版本 + RDS for PostgreSQL 13.13 及更高的 13 版本 + RDS for PostgreSQL 12.17 及更高的 12 版本 + RDS for PostgreSQL 11.22 **注意** 当复制配置中的多个数据库上有写入操作时，可能会发生冲突。有关更多信息，请参阅 [处理主动-主动复制中的冲突](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-conflicts.md)。 **Topics** + [ ## pgactive 扩展的局限性 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.requirements-limitations) + [ # 初始化 pgactive 扩展功能 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.basic-setup.md) + [ # 为 RDS for PostgreSQL 数据库实例设置主动-主动复制 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.setup-replication.md) + [ # 衡量 pgactive 成员之间的复制延迟 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.replicationlag.md) + [ # 配置 pgactive 扩展的参数设置 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.md) + [ # 了解双活冲突 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.replication.md) + [ # 了解 pgactive 架构 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.schema.md) + [ # pgactive 函数参考 ](pgactive-functions-reference.md) + [ # 处理主动-主动复制中的冲突 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-conflicts.md) + [ # 处理主动-主动复制中的序列 ](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-sequences.md) ## pgactive 扩展的局限性 + 所有表都需要主键，否则不允许使用更新和删除。不应更新“主键”列中的值。 + 序列可能存在间隙，有时可能不遵循顺序。不会复制序列。有关更多信息，请参阅 [处理主动-主动复制中的序列](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-sequences.md)。 + 不会复制 DDL 和大型对象。 + 辅助唯一索引可能会导致数据差异。 + 组中所有节点上的排序规则需要相同。 + 跨节点负载均衡是一种反模式。 + 大型事务可能会导致复制滞后。 # 初始化 pgactive 扩展功能 要在 RDS for PostgreSQL 数据库实例上初始化 `pgactive` 扩展功能，请将参数 `rds.enable_pgactive` 的值设置为 `1`，然后在数据库中创建扩展。这样做会自动开启参数 `rds.logical_replication` 和 `track_commit_timestamp` 并将 `wal_level` 的值设置为 `logical`。 您必须拥有 `rds_superuser` 角色的权限才能执行这些任务。 您可以使用 AWS 管理控制台或 AWS CLI 创建所需的 RDS for PostgreSQL 数据库实例。以下步骤假设您的 RDS for PostgreSQL 数据库实例与自定义数据库参数组相关联。有关创建自定义数据库参数组的信息，请参阅[Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。 ## 控制台 **初始化 pgactive 扩展功能** 1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 在导航窗格中，选择 RDS for PostgreSQL 数据库实例。 1. 打开 RDS for PostgreSQL 数据库实例的**配置**选项卡。在实例详细信息中，找到**数据库实例参数组**链接。 1. 选择此链接以打开与 RDS for PostgreSQL 数据库实例关联的自定义参数。 1. 找到 `rds.enable_pgactive` 参数，并将其设置为 `1` 以初始化 `pgactive` 功能。 1. 选择**保存更改**。 1. 在 Amazon RDS 控制台的导航窗格中，选择**数据库**。 1. 选择您的 RDS for PostgreSQL 数据库实例，然后从**操作**菜单中选择**重启**。 1. 确认数据库实例重启，以便您的更改生效。 1. 当数据库实例可用时，您可以使用 `psql` 或任何其它 PostgreSQL 客户端连接到 RDS for PostgreSQL 数据库实例。 以下示例假设 RDS for PostgreSQL 数据库实例有一个名为 *postgres* 的原定设置数据库。 ``` psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password=PASSWORD --dbname=postgres ``` 1. 要验证 pgactive 是否初始化，可以运行以下命令。 ``` postgres=>SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries'; ``` 如果 `pgactive` 在 `shared_preload_libraries` 中，则前面的命令将返回以下内容： ``` ?column? ---------- t ``` ## AWS CLI **初始化 pgactive 扩展功能** 要使用 AWS CLI 设置 `pgactive`，请调用 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 操作来修改自定义参数组中的某些参数，如以下过程所示。 1. 使用以下 AWS CLI 命令将 `rds.enable_pgactive` 设置为 `1`，以初始化 RDS for PostgreSQL 数据库实例的 `pgactive` 功能。 ``` postgres=>aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=rds.enable_pgactive,ParameterValue=1,ApplyMethod=pending-reboot" \ --region aws-region ``` 1. 使用以下 AWS CLI 命令重启 RDS for PostgreSQL 数据库实例，以便初始化 `pgactive` 库。 ``` aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region ``` 1. 当实例可用时，使用 `psql` 连接到 RDS for PostgreSQL 数据库实例。 ``` psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master user --password=PASSWORD --dbname=postgres ``` 1. 要验证 pgactive 是否初始化，可以运行以下命令。 ``` postgres=>SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries'; ``` 如果 `pgactive` 在 `shared_preload_libraries` 中，则前面的命令将返回以下内容： ``` ?column? ---------- t ``` # 为 RDS for PostgreSQL 数据库实例设置主动-主动复制 以下过程说明如何在 `pgactive` 可用的两个 RDS for PostgreSQL 数据库实例之间启动主动-主动复制。要运行多区域高可用性示例，您需要在两个不同的区域中部署 Amazon RDS for PostgreSQL 实例，并设置 VPC 对等。有关更多信息，请参阅 [VPC 对等](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html)。 **注意** 在多个区域之间发送流量可能会产生额外费用。 这些步骤假定已使用 `pgactive` 扩展启用 RDS for PostgreSQL 数据库实例。有关更多信息，请参阅 [初始化 pgactive 扩展功能](Appendix.PostgreSQL.CommonDBATasks.pgactive.basic-setup.md)。 **使用 `pgactive` 扩展配置第一个 RDS for PostgreSQL 数据库实例** 以下示例说明如何创建 `pgactive` 组，以及在 RDS for PostgreSQL 数据库实例上创建 `pgactive` 扩展所需的其它步骤。 1. 使用 `psql` 或其它客户端工具连接第一个 RDS for PostgreSQL 数据库实例。 ``` psql --host=firstinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password=PASSWORD --dbname=postgres ``` 1. 使用以下命令在 RDS for PostgreSQL 实例上创建数据库： ``` postgres=> CREATE DATABASE app; ``` 1. 使用以下命令将连接切换到新数据库： ``` \c app ``` 1. 使用以下 SQL 语句创建并填充示例表： 1. 使用以下 SQL 语句创建一个示例表。 ``` app=> CREATE SCHEMA inventory; CREATE TABLE inventory.products ( id int PRIMARY KEY, product_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP); ``` 1. 使用以下 SQL 语句用一些示例数据填充表。 ``` app=> INSERT INTO inventory.products (id, product_name) VALUES (1, 'soap'), (2, 'shampoo'), (3, 'conditioner'); ``` 1. 使用以下 SQL 语句验证表中是否存在数据。 ``` app=>SELECT count(*) FROM inventory.products; count ------- 3 ``` 1. 在现有数据库上创建 `pgactive` 扩展。 ``` app=> CREATE EXTENSION pgactive; ``` 1. 要安全地创建并初始化 pgactive 组，请使用以下命令： ``` app=> -- connection info for endpoint1 CREATE SERVER pgactive_server_endpoint1 FOREIGN DATA WRAPPER pgactive_fdw OPTIONS (host '', dbname 'app'); CREATE USER MAPPING FOR postgres SERVER pgactive_server_endpoint1 OPTIONS (user 'postgres', password ''); -- connection info for endpoint2 CREATE SERVER pgactive_server_endpoint2 FOREIGN DATA WRAPPER pgactive_fdw OPTIONS (host '', dbname 'app'); CREATE USER MAPPING FOR postgres SERVER pgactive_server_endpoint2 OPTIONS (user 'postgres', password ''); ``` 现在，您可以初始化复制组并添加此第一个实例： ``` SELECT pgactive.pgactive_create_group( node_name := 'endpoint1-app', node_dsn := 'user_mapping=postgres pgactive_foreign_server=pgactive_server_endpoint1' ); ``` 使用以下命令作为替代方案（安全性较低）来创建并初始化 pgactive 组： ``` app=> SELECT pgactive.pgactive_create_group( node_name := 'node1-app', node_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD'); ``` node1-app 是您分配的名称，用于唯一标识 `pgactive` 组中的节点。 **注意** 要在可公开访问的数据库实例上成功执行此步骤，必须通过将 `rds.custom_dns_resolution` 参数设置为 `1` 将其开启。 1. 要检查数据库实例是否就绪，请使用以下命令： ``` app=> SELECT pgactive.pgactive_wait_for_node_ready(); ``` 如果命令成功，您可以看到以下输出： ``` pgactive_wait_for_node_ready ------------------------------ (1 row) ``` **配置第二个 RDS for PostgreSQL 实例并将其加入 `pgactive` 组** 以下示例说明如何将 RDS for PostgreSQL 数据库实例加入 `pgactive` 组，以及在数据库实例上创建 `pgactive` 扩展所需的其它步骤。 这些步骤假设已使用 `pgactive` 扩展设置了其它 RDS for PostgreSQL 数据库实例。有关更多信息，请参阅 [初始化 pgactive 扩展功能](Appendix.PostgreSQL.CommonDBATasks.pgactive.basic-setup.md)。 1. 使用 `psql` 连接到要从发布者接收更新的实例。 ``` psql --host=secondinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password=PASSWORD --dbname=postgres ``` 1. 使用以下命令在第二个 RDS for PostgreSQL 数据库实例上创建数据库： ``` postgres=> CREATE DATABASE app; ``` 1. 使用以下命令将连接切换到新数据库： ``` \c app ``` 1. 在现有数据库上创建 `pgactive` 扩展。 ``` app=> CREATE EXTENSION pgactive; ``` 1. 使用以下命令以更安全的方式将 RDS for PostgreSQL 第二个数据库实例加入 `pgactive` 组： ``` -- connection info for endpoint1 CREATE SERVER pgactive_server_endpoint1 FOREIGN DATA WRAPPER pgactive_fdw OPTIONS (host '', dbname 'app'); CREATE USER MAPPING FOR postgres SERVER pgactive_server_endpoint1 OPTIONS (user 'postgres', password ''); -- connection info for endpoint2 CREATE SERVER pgactive_server_endpoint2 FOREIGN DATA WRAPPER pgactive_fdw OPTIONS (host '', dbname 'app'); CREATE USER MAPPING FOR postgres SERVER pgactive_server_endpoint2 OPTIONS (user 'postgres', password ''); ``` ``` SELECT pgactive.pgactive_join_group( node_name := 'endpoint2-app', node_dsn := 'user_mapping=postgres pgactive_foreign_server=pgactive_server_endpoint2', join_using_dsn := 'user_mapping=postgres pgactive_foreign_server=pgactive_server_endpoint1' ); ``` 使用以下命令作为替代方案（安全性较低）将 RDS for PostgreSQL 第二个数据库实例加入 `pgactive` 组 ``` app=> SELECT pgactive.pgactive_join_group( node_name := 'node2-app', node_dsn := 'dbname=app host=secondinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD', join_using_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD'); ``` node2-app 是您分配的名称，用于唯一标识 `pgactive` 组中的节点。 1. 要检查数据库实例是否就绪，请使用以下命令： ``` app=> SELECT pgactive.pgactive_wait_for_node_ready(); ``` 如果命令成功，您可以看到以下输出： ``` pgactive_wait_for_node_ready ------------------------------ (1 row) ``` 如果第一个 RDS for PostgreSQL 数据库相对较大，则可以看到 `pgactive.pgactive_wait_for_node_ready()` 正在发出还原操作的进度报告。输出看上去类似于以下内容： ``` NOTICE: restoring database 'app', 6% of 7483 MB complete NOTICE: restoring database 'app', 42% of 7483 MB complete NOTICE: restoring database 'app', 77% of 7483 MB complete NOTICE: restoring database 'app', 98% of 7483 MB complete NOTICE: successfully restored database 'app' from node node1-app in 00:04:12.274956 pgactive_wait_for_node_ready ------------------------------ (1 row) ``` 此后，`pgactive` 将在两个数据库实例之间同步数据。 1. 您可以使用以下命令来验证第二个数据库实例的数据库是否具有数据： ``` app=> SELECT count(*) FROM inventory.products; ``` 如果数据成功同步，您将看到以下输出： ``` count ------- 3 ``` 1. 运行以下命令插入新值： ``` app=> INSERT INTO inventory.products (id, product_name) VALUES (4, 'lotion'); ``` 1. 连接到第一个数据库实例的数据库并运行以下查询： ``` app=> SELECT count(*) FROM inventory.products; ``` 如果已初始化主动-主动复制，则输出类似于以下内容： ``` count ------- 4 ``` **从 `pgactive` 组中分离和移除数据库实例** 您可以使用以下步骤从 `pgactive` 组中分离和移除数据库实例： 1. 您可以使用以下命令将第二个数据库实例与第一个数据库实例分离： ``` app=> SELECT * FROM pgactive.pgactive_detach_nodes(ARRAY[‘node2-app']); ``` 1. 使用以下命令从第二个数据库实例中移除 `pgactive` 扩展： ``` app=> SELECT * FROM pgactive.pgactive_remove(); ``` 要强制移除此扩展，请执行以下操作： ``` app=> SELECT * FROM pgactive.pgactive_remove(true); ``` 1. 使用以下命令删除扩展： ``` app=> DROP EXTENSION pgactive; ``` # 衡量 pgactive 成员之间的复制延迟 您可以使用以下查询来查看 `pgactive` 成员之间的复制延迟。在每个 `pgactive` 节点上运行此查询以了解全部信息。 ``` app=> SELECT * FROM pgactive.pgactive_get_replication_lag_info(); │-[ RECORD 1 ]--------+--------------------------------------------- │node_name | node2-app │node_sysid | 7481018224801653637 │application_name | pgactive:7481018224801653637:send │slot_name | pgactive_16385_7481018224801653637_0_16385__ │active | t │active_pid | 783486 │pending_wal_decoding | 0 │pending_wal_to_apply | 0 │restart_lsn | 0/2108150 │confirmed_flush_lsn | 0/2154690 │sent_lsn | 0/2154690 │write_lsn | 0/2154690 │flush_lsn | 0/2154690 │replay_lsn | 0/2154690 │-[ RECORD 2 ]--------+--------------------------------------------- │node_name | node1-app │node_sysid | 7481018033434600853 │application_name | pgactive:7481018033434600853:send │slot_name | pgactive_16385_7481018033434600853_0_16385__ │active | t │active_pid | 783488 │pending_wal_decoding | 0 │pending_wal_to_apply | 0 │restart_lsn | 0/20F5AD0 │confirmed_flush_lsn | 0/214EF68 │sent_lsn | 0/214EF68 │write_lsn | 0/214EF68 │flush_lsn | 0/214EF68 │replay_lsn | 0/214EF68 ``` 至少需要监控以下诊断信息： active 在 active 为 false 时设置警报，这表示该插槽当前未被使用（订阅用户实例已断开与发布者的连接）。 pending\$1wal\$1decoding 在 PostgreSQL 的逻辑复制中，WAL 文件以二进制格式存储。发布者必须解码这些 WAL 更改并将其转换为逻辑更改（例如插入、更新或删除操作）。 指标 pending\$1wal\$1decoding 显示发布者端等待解码的 WAL 文件的数量。 该数量可能因以下因素增大： + 当未连接订阅用户时，active 状态将为 false，并且 pending\$1wal\$1decoding 将增大 + 该插槽处于 active 状态，但发布者无法跟上 WAL 更改的量 pending\$1wal\$1to\$1apply 指标 pending\$1wal\$1apply 表示订阅用户端等待应用的 WAL 文件的数量。 有以下几个因素可能会阻止订阅用户应用更改，并且可能导致出现磁盘已满的情况： + 架构差异 – 例如，当您在名为 sample 的表的 WAL 流中做出了更改，但订阅用户端却不存在该表时 + 主键列中的值已更新 + 辅助唯一索引可能会导致数据差异 # 配置 pgactive 扩展的参数设置 您可以使用以下查询来查看与 `pgactive` 扩展关联的所有参数。 ``` app=> SELECT * FROM pg_settings WHERE name LIKE 'pgactive.%'; ``` 您可以使用各种参数配置 `pgactive` 扩展。可通过 AWS 管理控制台或 AWS CLI 界面设置这些参数。 ## pgactive 扩展的主要参数 下表提供了 `pgactive` 扩展的主要参数的参考： | 参数 | 单位 | 默认值 | 说明 | | --- | --- | --- | --- | | pgactive.conflict\$1logging\$1include\$1tuples | `boolean` | – | 记录 `pgactive` 扩展的完整元组信息。 要使更改生效，必须先重启服务器。 | | pgactive.log\$1conflicts\$1to\$1table | `boolean` | – | 确定 `pgactive` 扩展是否已将检测到的冲突记录到 `pgactive.pgactive_conflict_history` 表中。有关更多信息，请参阅冲突日志记录。 要使更改生效，必须先重启服务器。 | | pgactive.log\$1conflicts\$1to\$1logfile | `boolean` | – | 确定 `pgactive` 扩展是否已将检测到的冲突记录到 PostgreSQL 日志文件中。有关更多信息，请参阅冲突日志记录。 要使更改生效，必须先重启服务器。 | | pgactive.synchronous\$1commit | `boolean` | off | 确定 pgactive 应用工作线程的提交行为。在此设置处于禁用（off）状态时，应用工作线程会执行异步提交，这将提高 PostgreSQL 在应用操作期间的吞吐量，但会延迟向上游发送的重播确认。将此设置设为 `off` 始终是安全的，不会导致事务丢失或跳过。此设置仅影响下游节点上的磁盘刷新时间以及向上游发送确认的时间。系统会延迟发送重播刷新确认，直到通过检查点或定期工作等不相关的操作将提交刷新到磁盘。但是，如果上游具有 `synchronous_standby_names` 中列出的下游，则将此设置设定为 `off` 会导致上游的同步提交需要更长的时间才能向客户端返回成功响应。在此情况下，可将该参数设置为 `on`。 即使将该参数设置为 `on` 并在 `synchronous_standby_names` 中列出了节点，主动-主动配置中也仍然可能发生复制冲突。这是因为系统缺少节点间锁定和全局快照管理，可能导致不同节点上的并发事务修改同一元组。此外，事务仅在上游节点上提交后才开始复制。启用同步提交并不会将 pgactive 扩展转换为始终一致的系统。 | | pgactive.temp\$1dump\$1directory | `string` | – | 定义数据库克隆操作在初始设置期间所需的临时存储路径。此目录必须可由 postgres 用户写入，并且拥有足够的存储空间来容纳完整的数据库转储。系统仅在通过逻辑复制操作进行初始数据库设置期间使用此位置。`pgactive_init_copy command` 不使用该参数。 | | pgactive.max\$1ddl\$1lock\$1delay | `milliseconds` | `-1` | 指定在强制中止并发写入事务之前，DDL 锁的最长等待时间。默认值为 `-1`，这将使用 `max_standby_streaming_delay` 中设置的值。该参数接受时间单位。例如，可以将它设置为 10s 来表示 10 秒。在此等待期间，系统会尝试获取 DDL 锁，同时等待正在进行的写入事务完成提交或回滚。有关更多信息，请参阅“DDL 锁定”。 | | pgactive.ddl\$1lock\$1timeout | `milliseconds` | `-1` | 指定 DDL 锁为获取锁而尝试等待的时长。默认值为 `-1`，这将使用 lock\$1timeout 中指定的值。您可以使用时间单位来设置此参数，例如 10s 表示 10 秒。此计时器仅控制为获取 DDL 锁而等待的时长。在系统获取锁并开始 DDL 操作后，计时器将停止。此参数不会限制可将 DDL 锁保持的总持续时间或总 DDL 操作时间。要控制操作的总持续时间，请改用 `statement_timeout`。有关更多信息，请参阅“DDL 锁定”。 | | pgactive.debug\$1trace\$1ddl\$1locks\$1level | `boolean` | – | 覆盖 `pgactive` 扩展中 DDL 锁定操作的默认调试日志级别。配置此设置后，会导致系统在 LOG 调试级别而非默认级别发出与 DDL 锁相关的消息。使用此参数可监控 DDL 锁定活动，而无需在整个服务器上启用详细 `DEBUG1` 或 `DEBUG2` 日志级别。 可用日志级别（按详细程度升序排列）： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 有关监控选项的更多信息，请参阅“监控全局 DDL 锁”。 对此设置所做的更改会在重新加载配置后生效。您无需重新启动服务器。 | ## pgactive 扩展的其他参数 下表列出了 `pgactive` 扩展中不常用的配置选项和可用的内部配置选项。 | 参数 | 单位 | 默认值 | 说明 | | --- | --- | --- | --- | | pgactive.debug\$1apply\$1delay | `integer` | – | 为 `pgactive.pgactive_connections` 条目中未明确设定应用延迟的已配置连接设置应用延迟（以毫秒为单位）。此延迟是在节点创建或加入时设置的，pgactive 不会在对等节点上重播事务，直到提交后至少经过指定的毫秒数。 主要用于在测试环境中模拟高延迟网络，以便更轻松地制造冲突。例如，如果在节点 A 和 B 上设置了 500 毫秒的延迟，则在节点 A 上插入一个值后，至少为您提供了 500 毫秒的时间在节点 B 上执行冲突插入。 需要重新加载服务器或重新启动应用工作线程，设置才能生效。 | | pgactive.connectability\$1check\$1duration | `integer` | – | 指定数据库工作线程在尝试连接失败后，继续尝试建立连接的持续时间（以秒为单位）。工作线程每秒尝试连接一次，直到连接成功或达到此超时值。当数据库引擎在工作线程准备好建立连接之前启动时，此设置很有用。 | | pgactive.skip\$1ddl\$1replication | `boolean` | `on` | 控制如何在启用 `pgactive` 时，在 Amazon RDS 中复制或处理 DDL 更改。在设置为 `on` 时，该节点会像非 pgactive 节点一样处理 DDL 更改。在使用此参数时，需符合以下要求： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 您可以使用超级用户权限以全局和本地（会话级别）这两种方式修改此参数。 错误地更改此参数可能会中断您的复制设置。 | | pgactive.do\$1not\$1replicate | `boolean` | – | 此参数仅供内部使用。在事务中设置此参数时，所做的更改不会复制到数据库集群内的其他节点。 错误地更改此参数可能会中断您的复制设置。 | | pgactive.discard\$1mismatched\$1row\$1attributes | `boolean` | – | 此参数仅供专业人员使用。建议仅在解决特定的复制问题时使用此参数。在以下情况下可使用此参数： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 此设置会覆盖以下错误消息，并允许产生数据差异，以便让复制进程持续进行：`cannot right-pad mismatched attributes; attno %u is missing in local table and remote row has non-null, non-dropped value for this attribute` 错误地更改此参数可能会中断您的复制设置。 | | pgactive.debug\$1trace\$1replay | `boolean` | – | 在设置为 `on` 时，它会为下游应用工作线程处理的每个远程操作发出一条日志消息。日志包括： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 日志还会捕获已排队的 DDL 命令和表删除事件。para> 默认情况下，日志不包括行字段内容。要在日志中包含行值，您必须在启用以下标志的情况下重新编译： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 启用此日志记录设置可能会影响性能。建议仅在需要此设置来进行问题排查时启用它。对此设置所做的更改会在重新加载配置后生效。您无需重新启动服务器。 | | pgactive.extra\$1apply\$1connection\$1options | | – | 您可以为所有与 pgactive 节点的对等节点连接配置连接参数。这些参数可控制 keepalives 和 SSL 模式等设置。默认情况下，pgactive 使用以下连接参数： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 要覆盖默认参数，请使用以下类似命令： pgactive.extra\$1apply\$1connection\$1options = 'keepalives=0' 单个节点连接字符串优先于这些设置和 pgactive 的内置连接选项。有关连接字符串格式的更多信息，请参阅 [libpq connection strings](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)。 我们建议将默认的 keepalive 设置保持启用状态。仅在您通过不可靠的网络完成大量事务时遇到问题的情况下，才禁用 keepalives。 我们建议将默认的 keepalive 设置保持启用状态。仅在您通过不可靠的网络完成大量事务时遇到问题的情况下，才禁用 keepalives。对此设置所做的更改会在重新加载配置后生效。您无需重新启动服务器。 | | pgactive.init\$1node\$1parallel\$1jobs (int) | | – | 指定在逻辑节点与 `pgactive.pgactive_join_group` 函数联接期间，`pg_dump` 和 `pg_restore` 可使用的并行作业数。 对此设置所做的更改会在重新加载配置后生效。您无需重新启动服务器。 | | pgactive.max\$1nodes | `int` | 4 | 指定 pgactive 扩展组中可包含的节点的最大数目。默认值为 4 个节点。在设置此参数的值时，必须注意以下几点： [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) 可通过两种方式设置此参数，即在配置文件中进行设置或者使用 `ALTER SYSTEM SET` 命令进行设置 此参数的默认值为 `4`，这意味着，`pgactive` 扩展组始终最多只能包含 4 个节点。 重新启动服务器后，此更改将生效。 | | pgactive.permit\$1node\$1identifier\$1getter\$1function\$1creation | `boolean` | – | 此参数仅供内部使用。启用后，`pgactive` 扩展将允许创建 pgactive 节点标识符 getter 函数。 | # 了解双活冲突 如果您在双活模式下使用 pgactive，在从多个节点写入同一个表时可能会造成数据冲突。一些集群化系统会使用分布式锁来防止并发访问，不过 pgactive 采用的是乐观方法，更适合分布在不同地理位置的应用程序。 一些数据库集群系统使用分布式锁来防止并发数据访问。但是，此方法只适合距离非常近的服务器，不支持分布在不同地理位置的应用程序，因为此方法需要极低的延迟才能实现良好的性能。pgactive 扩展没有使用分布式锁（这是一种悲观方法），而是使用乐观方法。这意味着： + 可帮助您尽可能避免冲突。 + 允许出现某些类型的冲突。 + 发生冲突时会提供冲突解决方案。 通过这种方法，您在构建分布式应用程序时具有更好的灵活性。 ## 发生冲突的原因 节点间冲突是由事件序列引起的，如果涉及的所有事务同时发生在同一个节点上，则不会出现节点间冲突。由于节点仅在事务提交后交换更改，因此每个事务在其提交的节点上都是分别有效的，但如果事务在另一个同时完成其他工作的节点上运行，就会无效。由于 pgactive 的应用操作本质上是在其他节点上重播事务，因此，如果正在应用的事务与在接收节点上提交的事务之间存在冲突，则重播操作会失败。 当所有事务都运行在单个节点上时，大多数冲突不会发生的原因是 PostgreSQL 采用了事务间通信机制来防止冲突，包括： + UNIQUE 索引 + SEQUENCE + 行和关系锁定 + SERIALIZABLE 依赖关系跟踪 所有这些机制都是在事务之间进行通信的方法，用于防止出现意外并发问题 pgactive 不使用分布式事务管理器或锁管理器，因此可以实现低延迟并很好地处理网络分区。但是，这意味着不同节点上事务的运行是彼此完全隔离的。虽然隔离通常可以提高数据库的一致性，但在这种情况下，您需要减少隔离以防止冲突。 ## 冲突类型 可能发生的冲突包括： **Topics** + [ ### PRIMARY KEY 或 UNIQUE 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict1) + [ ### INSERT/INSERT 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict2) + [ ### 违反多个 UNIQUE 约束的 INSERT ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict3) + [ ### UPDATE/UPDATE 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict4) + [ ### PRIMARY KEY 上的 UPDATE 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict5) + [ ### 违反多个 UNIQUE 约束的 UPDATE ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict6) + [ ### UPDATE/DELETE 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict7) + [ ### INSERT/UPDATE 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict8) + [ ### DELETE/DELETE 冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict9) + [ ### 外键约束冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict10) + [ ### 排除约束冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict11) + [ ### 全局数据冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict12) + [ ### 锁冲突和死锁中止 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict13) + [ ### 分歧冲突 ](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict14) ### PRIMARY KEY 或 UNIQUE 冲突 当多个操作试图以单个节点上不可能出现的方式修改同一个行键时，就会发生行冲突。这些冲突体现了最常见的数据冲突类型。 pgactive 采用上次更新获胜处理方法或者您的自定义冲突处理程序，来解决检测到的冲突。 行冲突包括： + INSERT 与 INSERT + INSERT 与 UPDATE + UPDATE 与 DELETE + INSERT 与 DELETE + DELETE 与 DELETE + INSERT 与 DELETE ### INSERT/INSERT 冲突 这是最常见的冲突，当两个不同节点上的 INSERT 操作创建具有相同 PRIMARY KEY 值的元组（或在没有 PRIMARY KEY 时，使用相同的 UNIQUE 约束值），就会发生这种冲突。 pgactivelink 使用来自原始主机的时间戳来保留最新的元组，以此解决 INSERT 冲突。您可以使用自定义冲突处理程序覆盖此默认行为。虽然此过程不需要管理员专门采取操作，但请注意，pgactivelink 会丢弃所有节点上的其中一个 INSERT 操作。除非您的自定义处理程序实施自动数据合并，否则不会自动进行数据合并。 pgactivelink 只能解决涉及单个约束违规的冲突。如果某个 INSERT 违反了多个 UNIQUE 约束，您必须实施其他冲突解决策略。 ### 违反多个 UNIQUE 约束的 INSERT INSERT/INSERT 冲突可能违反多个 UNIQUE 约束，包括 PRIMARY KEY。pgactivelink 只能处理涉及单个 UNIQUE 约束的冲突。当冲突违反多个 UNIQUE 约束时，应用工作线程会失败并返回以下错误： `multiple unique constraints violated by remotely INSERTed tuple.` 在较早的版本中，这种情况生成的是“分歧唯一性冲突”错误。 要解决这些冲突，您必须手动采取操作。您可以对发生冲突的本地元组执行 DELETE，或者对元组执行 UPDATE 以使用新的远程元组来消除冲突。请注意，您可能需要解决多个相互冲突的元组。目前，pgactivelink 没有提供内置功能用于忽略、丢弃或合并违反多个唯一约束的元组。 **注意** 有关更多信息，请参阅“违反多个 UNIQUE 约束的 UPDATE”。 ### UPDATE/UPDATE 冲突 当两个节点并发修改同一个元组而没有更改其 PRIMARY KEY 时，就会发生此冲突。pgactivelink 使用上次更新获胜逻辑或您的自定义冲突处理程序（如果已定义）来解决这些冲突。PRIMARY KEY 对于元组匹配和冲突解决至关重要。对于没有 PRIMARY KEY 的表，pgactivelink 拒绝 UPDATE 操作，并显示以下错误： `Cannot run UPDATE or DELETE on table (tablename) because it does not have a primary key.` ### PRIMARY KEY 上的 UPDATE 冲突 pgactive 在处理 PRIMARY KEY 更新时存在限制。虽然您可以对 PRIMARY KEY 执行 UPDATE 操作，但对于这些操作，pgactive 无法使用最后更新获胜逻辑自动解决冲突。您必须确保 PRIMARY KEY 更新不会与现有值冲突。如果在 PRIMARY KEY 更新期间发生冲突，这些冲突就会成为分歧冲突，需要您手动干预。有关处理这些情况的更多信息，请参阅[分歧冲突](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict14)。 ### 违反多个 UNIQUE 约束的 UPDATE 当传入的 UPDATE 违反多个 UNIQUE 约束或 PRIMARY KEY 值时，pgactivelink 无法应用上次更新获胜冲突解决方案。此行为类似于出现多个约束冲突的 INSERT 操作。这些情况会造成分歧冲突，需要您手动干预。有关更多信息，请参阅[分歧冲突](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict14)。 ### UPDATE/DELETE 冲突 当一个节点对一行执行 UPDATE 而另一个节点同时对该行执行 DELETE 时，就会出现此冲突。在这种情况下，重播时会发生 UPDATE/DELETE 冲突。除非您的自定义冲突处理程序另行规定，否则解决方案是丢弃在 DELETE 之后到达的所有 UPDATE。 pgactivelink 需要一个 PRIMARY KEY 来匹配元组并解决冲突。对于没有 PRIMARY KEY 的表，表会拒绝 DELETE 操作，并显示以下错误： `Cannot run UPDATE or DELETE on table (tablename) because it does not have a primary key.` **注意** pgactivelink 无法区分 UPDATE/DELETE 和 INSERT/UPDATE 冲突。在这两种情况下，UPDATE 均会影响不存在的行。由于异步复制以及节点之间缺乏重播排序，pgactivelink 无法确定是在对新行（尚未收到 INSERT）还是对已删除行执行 UPDATE 操作。在这两种情况下，pgactivelink 都会丢弃 UPDATE。 ### INSERT/UPDATE 冲突 这种冲突可能发生在多节点环境中。当一个节点 INSERT 一行数据，第二个节点对该行执行 UPDATE，而第三个节点在收到原始 INSERT 之前就接收到 UPDATE 时，就会发生这种情况。默认情况下，除非您的自定义冲突触发器另行规定，否则 pgactivelink 会通过放弃 UPDATE 来解决这些冲突。请注意，这种解决方法可能会导致节点间的数据不一致。有关类似场景及其处理方法的更多信息，请参阅 [UPDATE/DELETE 冲突](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict7)。 ### DELETE/DELETE 冲突 当两个不同的节点并发删除同一个元组时，就会发生这种冲突。pgactivelink 认为这些冲突无害，因为两个 DELETE 操作会产生相同的最终结果。在此场景中，pgactivelink 可以安全地忽略其中一个 DELETE 操作，而不会影响数据一致性。 ### 外键约束冲突 对现有本地数据应用远程事务时，FOREIGN KEY 约束可能会导致冲突。发生这些冲突的情况通常是应用事务的顺序，不同于事务在发起节点上的逻辑顺序。 默认情况下，pgactive 会将 session\$1replication\$1role 作为 `replica` 来应用更改，这会在复制过程中绕过外键检查。在双活配置中，这可能会导致外键违规。大多数违规行为都是暂时性的，当复制进度跟上时就会解决。但是，由于 pgactive 不支持跨节点行锁定，因此可能会出现悬挂外键。 这种行为是具备分区容错性的异步双活系统所固有的。例如，节点 A 可能会插入新的子行，而节点 B 同时在删除其父行。系统无法阻止节点之间这种类型的并发修改。 为尽可能减少外键冲突，我们给出了以下建议： + 将外键关系限制为密切相关的实体。 + 尽可能从单个节点修改相关实体。 + 选择极少需要进行修改的实体。 + 针对修改实施应用程序级的并发控制。 ### 排除约束冲突 pgactivelink 不支持排除约束，并会限制创建这种约束。 **注意** 在将现有的独立数据库转换为 pgactivelink 数据库时，请手动删除所有排除约束。 在分布式异步系统中，不可能保证所有行集都没有违反约束。这是因为不同节点上的所有事务都是完全隔离的。排除约束可能导致重播死锁，在这种情况下，由于排除约束违规，重播无法从任何节点进展到另一个节点。 如果您强制 pgactivelink 创建排除约束，或者在将独立数据库转换到 pgactivelink 时不删除现有排除约束，则复制很可能会中断。要恢复复制进度，请删除或更改与传入的远程元组冲突的本地元组，这样便可以应用远程事务。 ### 全局数据冲突 使用 pgactivelink 时，当节点具有不同的全局 PostgreSQL 系统级数据（例如角色）时，就可能会发生冲突。这些冲突的结果是操作（主要是 DDL）会成功并在一个节点上提交，但无法应用于其他节点。 如果一个节点上有某个用户但另一个节点上没有，就会出现复制问题： + Node1 有名为 `fred` 的用户，但是 Node2 上没有该用户 + 当 `fred` 在 Node1 上创建表时，表在复制时的所有者是 `fred` + 将此 DDL 命令应用于 Node2 时，命令会失败，因为用户 `fred` 不存在 + 在失败后，会在 Node2 上的 PostgreSQL 日志中生成 ERROR 条目并递增 `pgactive.pgactive_stats.nr_rollbacks` 计数器 **解决方案：**在 Node2 上创建用户 `fred`。该用户不必具备相同的权限，但必须存在于两个节点上。 如果一个节点上有某个表但另一个节点上没有，则数据修改操作会失败： + Node1 上有名为 `foo` 的表，但 Node2 上没有 + Node1 上对 `foo` 表执行的任何 DML 操作，在复制到 Node2 时会失败 **解决方案：**在 Node2 上创建具有相同结构的表 `foo`。 **注意** pgactivelink 目前不复制 CREATE USER 命令或 DDL 操作。DDL 复制功能计划在未来版本中发布。 ### 锁冲突和死锁中止 由于 pgactive 应用进程的运行方式类似于普通用户会话，因此这些进程遵循标准的行和表锁定规则。这可能会导致 pgactivelink 应用进程需要等待用户事务或其他应用进程持有的锁被释放。 以下类型的锁会影响应用进程： + 用户会话施加的显式表级锁（LOCK TABLE...） + 用户会话施加的显式行级锁（SELECT ... FOR UPDATE/FOR SHARE） + 来自外键的锁定 + 由于行 UPDATE、INSERT 或 DELETE 而导致的隐式锁定，这可能源自本地活动，也可能是从其他服务器应用的 在下列操作之间可能会出现死锁： + pgactivelink 应用进程和用户事务 + 两个应用进程 发生死锁时，PostgreSQL 的死锁检测器会终止其中一个出现问题的事务。如果 pgactivelink 应用工作线程的进程终止，则会自动重试而且通常会成功。 **注意** 这些问题是暂时性的，通常无需管理员干预。如果由于空闲用户会话锁定导致某个应用进程被长时间阻止，您可以终止该用户会话来恢复复制。这种情况类似于某个用户持有的长时间锁定影响到了其他用户会话。 要识别与锁定相关的重播延迟，请在 PostgreSQL 中启用 `log_lock_waits` 功能。 ### 分歧冲突 当各节点之间本应相同的数据意外地变得不同时，就会发生分歧冲突。虽然这些冲突不应该发生，但在当前的实施中，无法可靠地预防所有冲突。 **注意** 在一行上，如果有某个节点在所有节点处理更改之前更改该行的主键，则修改行的 PRIMARY KEY 会导致分歧冲突。请避免更改主键，或将更改限制在某个指定节点上。有关更多信息，请参阅 [PRIMARY KEY 上的 UPDATE 冲突](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict5)。 涉及行数据的分歧冲突通常需要管理员干预。要解决这些冲突，您必须手动调整一个节点上的数据来与其他节点相匹配，同时使用 `pgactive.pgactive_do_not_replicate` 暂时禁用复制。如果您按照文档说明使用 pgactive 并避免使用标记为不安全的设置或函数，则不应出现这些冲突。 作为管理员，您必须手动解决这些冲突。根据冲突类型，您需要使用诸如 `pgactive.pgactive_do_not_replicate` 之类的高级选项。请谨慎使用这些选项，因为使用不当会导致情况恶化。由于可能出现的冲突各种各样，我们无法提供通用的解决方案说明。 当不同节点之间本应相同的数据意外地变得不同时，就会发生分歧冲突。虽然这些冲突不应该发生，但在当前的实施中，无法可靠地预防所有此类冲突。 ## 避免或容忍冲突 在大多数情况下，您可以使用合适的应用程序设计，从而避免冲突或使应用程序具备容忍冲突的能力。 只有在多个节点上同时进行多个操作时才会发生冲突。为避免冲突，请采取以下方法： + 仅向一个节点写入 + 写入各个节点上的独立数据库子集（例如，向每个节点分配一个单独的架构） 对于 INSERT 与 INSERT 冲突，请使用全局序列来彻底防止冲突。 如果您的应用场景不能接受冲突，请考虑在应用程序级别实施分布式锁定。通常，最好的方法是对应用程序进行设计，使其与 pgactive 的冲突解决机制配合使用，而不是尝试预防所有冲突。有关更多信息，请参阅 [冲突类型](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflicttypes)。 ## 冲突日志记录 pgactivelink 会将冲突事件记录到 `pgactive.pgactive_conflict_history` 表中，供您诊断和处理双活冲突。仅当 `pgactive.log_conflicts_to_table` 设置为 true 时，才会将冲突记录到此表。无论 `pgactive.log_conflicts_to_table` 的设置如何，当 log\$1min\$1messages 设置为 `LOG` 或 `lower` 时，pgactive 扩展还会将冲突记录到 PostgreSQL 日志文件中。 使用冲突历史记录表可以： + 衡量您的应用程序造成冲突的频率 + 确定在什么位置发生冲突 + 改进应用程序以降低冲突率 + 检测冲突解决方案无法达成所需结果的情况 + 确定哪些地方需要用户定义的冲突触发器或更改应用程序设计 对于行冲突，您可以选择记录行值。这由 `pgactive.log_conflicts_to_table` 设置控制。请注意： + 这是一个全局数据库级选项 + 无法按各个表来控制对行值的记录 + 字段数量、数组元素或字段长度没有限制 + 如果您在处理数 MB 的行而这些行可能会触发冲突，则不建议启用此功能 由于冲突历史记录表包含来自数据库中各个表的数据（每个表都可能采用不同架构），因此记录的行值存储为 JSON 字段。JSON 使用 `row_to_json` 创建，类似于直接从 SQL 中调用。PostgreSQL 不提供 `json_to_row` 函数，因此您需要特定于表的代码（采用 PL/pgSQL、PL/Python、PL/Perl 等）才能从记录的 JSON 中重新构造复合类型的元组。 **注意** 对用户定义冲突的支持计划作为未来的扩展功能推出。 # 了解 pgactive 架构 pgactive 架构管理 RDS for PostgreSQL 中的主动-主动复制。此架构包含用于存储复制配置和状态信息的表。 **注意** pgactive 架构正在演进中，可能会发生变更。不要直接修改这些表中的数据。 pgactive 架构中的键表包括： + `pgactive_nodes` – 存储有关主动-主动复制组中的节点的信息。 + `pgactive_connections` – 存储每个节点的连接详细信息。 ## pgactive\$1nodes pgactive\$1nodes 存储有关参与主动-主动复制组的节点的信息。 | 列 | 类型 | 排序规则 | 是否可为 null | 默认值 | | --- | --- | --- | --- | --- | | node\$1sysid | 文本 | – | 不为 null | – | | node\$1timeline | oid | – | 不为 null | – | | node\$1dboid | oid | – | 不为 null | – | | node\$1status | char | – | 不为 null | – | | node\$1name | 文本 | – | 不为 null | – | | node\$1dsn | 文本 | – | 不为 null | – | | node\$1init\$1from\$1dsn | 文本 | – | 不为 null | – | | node\$1read\$1only | 布尔值 | – | – | false | | node\$1seq\$1id | smallint | – | 不为 null | – | **node\$1sysid** 在 `pgactive_create_group` 或 `pgactive_join_group` 期间生成的节点的唯一 ID **node\$1status** 节点的准备情况： + **b** – 开始设置 + **i** – 正在初始化 + **c** – 追赶 + **o** – 创建出站插槽 + **r** – 已就绪 + **k** – 已终止 此列不指明节点已连接还是已断开连接。 **node\$1name** 用户提供的唯一节点名称。 **node\$1dsn** 连接字符串或用户映射名称。 **node\$1init\$1from\$1dsn** 从中创建了此节点的 DSN。 ## pgactive\$1connection pgactive\$1connections 存储每个节点的连接详细信息。 | 列 | 类型 | 排序规则 | 是否可为 null | 默认值 | | --- | --- | --- | --- | --- | | conn\$1sysid | 文本 | none | 不为 null | none | | conn\$1timeline | oid | none | 不为 null | none | | conn\$1dboid | oid | none | 不为 null | none | | conn\$1dsn | 文本 | none | 不为 null | none | | conn\$1apply\$1delay | 整数 | none | none | none | | conn\$1replication\$1sets | 文本 | none | none | none | conn\$1sysid 此条目所指的节点的节点标识符。 conn\$1dsn 与 pgactive.pgactive\$1nodes `node_dsn` 相同。 conn\$1apply\$1delay 如果设置此参数，则将设定从远程节点应用每个事务前的等待时间（以毫秒为单位）。主要用于调试。如果为 null，则全局默认值将适用。 ## 使用复制集 复制集决定要在复制操作中包含或排除的表。默认情况下，除非您使用以下函数另行指定，否则将复制所有表： + `pgactive_exclude_table_replication_set()` – 从复制中排除指定的表 + `pgactive_include_table_replication_set()` – 在复制中包含指定的表 **注意** 在配置复制集之前，请注意以下几点： 仅在运行 `pgactive_create_group()` 后且在运行 `pgactive_join_group()` 之前，才能配置表包含或排除。 使用 `pgactive_exclude_table_replication_set()` 后，您将无法使用 `pgactive_include_table_replication_set()`。 使用 `pgactive_include_table_replication_set()` 后，您将无法使用 `pgactive_exclude_table_replication_set()`。 系统会根据您的初始配置通过不同的方式处理新创建的表： + 如果排除表：在 `pgactive_join_group()` 之后创建的任何新表都将自动包含在复制中。 + 如果包含表：在 `pgactive_join_group()` 之后创建的任何新表都将自动从复制中排除。 要查看特定表的复制集配置，请使用 `pgactive.pgactive_get_table_replication_sets()` 函数。 # pgactive 函数参考 在下文中，您可以找到 pgactive 函数及其参数、返回值和实际使用说明，以助您高效使用它们： ## get\$1last\$1applied\$1xact\$1info 检索指定节点的上次应用的事务信息。 **Arguments (参数)** + sysid (text) - timeline OID + dboid (OID) **返回类型** 它将记录以下内容： + last\$1applied\$1xact\$1id（OID） + last\$1applied\$1xact\$1committs（带时区的时间戳） + last\$1applied\$1xact\$1at（带时区的时间戳） **使用说明** 使用此函数可检索指定节点的上次应用的事务信息。 ## pgactive\$1apply\$1pause 暂停复制应用进程。 **Arguments (参数)** 无 **返回类型** 布尔值 **使用说明** 调用此函数可暂停复制应用进程。 ## pgactive\$1apply\$1resume 恢复复制应用进程。 **Arguments (参数)** 无 **返回类型** void **使用说明** 调用此函数可恢复复制应用进程。 ## pgactive\$1is\$1apply\$1paused 检查复制应用当前是否已暂停。 **Arguments (参数)** 无 **返回类型** 布尔值 **使用说明** 使用此函数可检查复制应用当前是否已暂停。 ## pgactive\$1create\$1group 通过将独立数据库转换为初始节点来创建 pgactive 组。 **Arguments (参数)** + node\$1name (text) + node\$1dsn (text) + apply\$1delay integer DEFAULT NULL::integer - replication\$1sets text[] DEFAULT ARRAY[‘default’::text] **返回类型** void **使用说明** 通过将独立数据库转换为初始节点来创建 pgactive 组。此函数在将节点转换为 pgactive 节点之前执行完整性检查。在使用此函数之前，请确保您的 PostgreSQL 集群有足够的可用 `max_worker_processes` 来支持 pgactive 后台工作线程。 ## pgactive\$1detach\$1nodes 从 pgactive 组中移除指定节点。 **Arguments (参数)** + p\$1nodes (text[]) **返回类型** void **使用说明** 使用此函数可从 pgactive 组中移除指定节点。 ## pgactive\$1exclude\$1table\$1replication\$1set 将特定表排除在复制之外。 **Arguments (参数)** + p\$1relation（regclass） **返回类型** void **使用说明** 使用此函数可将特定表排除在复制之外。 ## pgactive\$1get\$1replication\$1lag\$1info 检索详细的复制滞后信息，包括节点详细信息、WAL 状态和 LSN 值。 **Arguments (参数)** 无 **返回类型** SETOF 记录 - node\$1name text - node\$1sysid text - application\$1name text - slot\$1name text - active boolean - active\$1pid integer - pending\$1wal\$1decoding bigint - 发送者节点上待解码的 WAL 的近似大小（以字节为单位）- pending\$1wal\$1to\$1apply bigint - 要在接收节点上应用的 WAL 的近似大小（以字节为单位）- restart\$1lsn pg\$1lsn - confirmed\$1flush\$1lsn pg\$1lsn - sent\$1lsn pg\$1lsn - write\$1lsn pg\$1lsn - flush\$1lsn pg\$1lsn - replay\$1lsn pg\$1lsn **使用说明** 调用此函数可检索复制滞后信息，包括节点详细信息、WAL 状态和 LSN 值。 ## pgactive\$1get\$1stats 检索 pgactive 复制统计信息。 **Arguments (参数)** 无 **返回类型** SETOF 记录 - rep\$1node\$1id oid - rilocalid oid - riremoteid text - nr\$1commit bigint - nr\$1rollback bigint - nr\$1insert bigint - nr\$1insert\$1conflict bigint - nr\$1update bigint - nr\$1update\$1conflict bigint - nr\$1delete bigint - nr\$1delete\$1conflict bigint - nr\$1disconnect bigint **使用说明** 使用此函数可检索 pgactive 复制统计信息。 ## pgactive\$1get\$1table\$1replication\$1sets 获取特定关系的复制集配置。 **Arguments (参数)** + relation (regclass) **返回类型** SETOF 记录 **使用说明** 调用此函数可获取特定关系的复制集配置。 ## pgactive\$1include\$1table\$1replication\$1set 将特定表包含在复制中。 **Arguments (参数)** + p\$1relation（regclass） **返回类型** void **使用说明** 使用此函数可将特定表包含在复制中。 ## pgactive\$1join\$1group 向现有 pgactive 组添加节点。 **Arguments (参数)** + node\$1name (text) + node\$1dsn (text) + join\$1using\$1dsn (text) + apply\$1delay (integer，可选) + replication\$1sets (text[]，默认值：['default']) + bypass\$1collation\$1check (boolean，默认值：false) + bypass\$1node\$1identifier\$1creation (boolean，默认值：false) + bypass\$1user\$1tables\$1check (boolean，默认值：false) **返回类型** void **使用说明** 调用此函数可将节点添加到现有 pgactive 组中。确保您的 PostgreSQL 集群有足够的 max\$1worker\$1processes 以供 pgactive 后台工作线程使用。 ## pgactive\$1remove 从本地节点中移除所有 pgactive 组件。 **Arguments (参数)** + force (boolean，默认值：false) **返回类型** void **使用说明** 调用此函数可从本地节点中移除所有 pgactive 组件。 ## pgactive\$1snowflake\$1id\$1nextval 生成特定于节点的唯一序列值。 **Arguments (参数)** + regclass **返回类型** bigint **使用说明** 使用此函数可生成特定于节点的唯一序列值。 ## pgactive\$1update\$1node\$1conninfo 更新 pgactive 节点的连接信息。 **Arguments (参数)** + node\$1name\$1to\$1update (text) + node\$1dsn\$1to\$1update (text) **返回类型** void **使用说明** 使用此函数可更新 pgactive 节点的连接信息。 ## pgactive\$1wait\$1for\$1node\$1ready 监控组创建或加入操作的进度。 **Arguments (参数)** + timeout (integer，默认值：0) + progress\$1interval (integer，默认值：60) **返回类型** void **使用说明** 调用此函数可监控组创建或加入操作的进度。 # 处理主动-主动复制中的冲突 `pgactive` 扩展适用于每个数据库，而不是每个集群。使用 `pgactive` 的每个数据库实例都是一个独立的实例，可以接受来自任何来源的数据更改。将更改发送到数据库实例时，PostgreSQL 会在本地提交更改，然后使用 `pgactive` 将更改异步复制到其它数据库实例。当两个 PostgreSQL 数据库实例几乎同时更新同一记录时，可能会发生冲突。 `pgactive` 扩展提供了冲突检测和自动解决机制。它跟踪在这两个数据库实例上提交事务的时间戳，并自动应用带有最新时间戳的更改。`pgactive` 扩展还会记录 `pgactive.pgactive_conflict_history` 表中发生冲突的时间。 `pgactive.pgactive_conflict_history` 会继续增加。您可能需要定义清除策略。可以通过定期删除一些记录或为此关系定义分区方案（然后分离、删除、截断感兴趣的分区）来完成此操作。要定期实施清除策略，一种选择是使用 `pg_cron` 扩展。请参阅 `pg_cron` 历史记录表示例的以下信息：[使用 PostgreSQL pg\$1cron 扩展安排维护计划](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html)。 # 处理主动-主动复制中的序列 带有 `pgactive` 扩展的 RDS for PostgreSQL 数据库实例使用两种不同的序列机制来生成唯一值。 **全局序列** 要使用全局序列，请使用 `CREATE SEQUENCE` 语句创建一个本地序列。使用 `pgactive.pgactive_snowflake_id_nextval(seqname)` 而非 `usingnextval(seqname)` 来获取序列的下一个唯一值。 以下示例创建全局序列： ``` app=> CREATE TABLE gstest ( id bigint primary key, parrot text ); ``` ``` app=>CREATE SEQUENCE gstest_id_seq OWNED BY gstest.id; ``` ``` app=> ALTER TABLE gstest \ ALTER COLUMN id SET DEFAULT \ pgactive.pgactive_snowflake_id_nextval('gstest_id_seq'); ``` **分区序列** 在分步或分区序列中，每个节点上都使用普通的 PostgreSQL 序列。每个序列的增量相同，从不同的偏移量开始。例如，在步骤 100 中，节点 1 生成序列为 101、201、301，依此类推，而节点 2 生成序列为 102、202、302，依此类推。即使节点长时间无法通信，该方案也能正常工作，但要求设计人员在建立模式时指定最大节点数，并且需要按节点进行配置。错误很容易导致序列重叠。 通过在节点上创建所需的序列，使用 `pgactive` 配置这种方法相对简单，如下所示： ``` CREATE TABLE some_table (generated_value bigint primary key); ``` ``` app=> CREATE SEQUENCE some_seq INCREMENT 100 OWNED BY some_table.generated_value; ``` ``` app=> ALTER TABLE some_table ALTER COLUMN generated_value SET DEFAULT nextval('some_seq'); ``` 然后，对每个节点调用 `setval` 以给出不同的偏移起始值，如下所示。 ``` app=> -- On node 1 SELECT setval('some_seq', 1); -- On node 2 SELECT setval('some_seq', 2); ``` # 使用 pg\$1repack 扩展减少表和索引的膨胀 您可以使用 `pg_repack` 扩展从表和索引中移除多余内容，作为 `VACUUM FULL` 的备选方法。RDS for PostgreSQL 版本 9.6.3 及更高版本支持该扩展。有关 `pg_repack` 扩展和完整表重新打包的更多信息，请参阅 [GitHub 项目文档](https://reorg.github.io/pg_repack/)。 与 `VACUUM FULL` 不同，在以下情况下，`pg_repack` 扩展只需在表重建操作期间短时间使用独占锁（AccessExclusiveLock）： + 初始创建日志表 - 创建日志表以记录在数据初始复制期间发生的更改，如以下示例所示： ``` postgres=>\dt+ repack.log_* List of relations -[ RECORD 1 ]-+---------- Schema | repack Name | log_16490 Type | table Owner | postgres Persistence | permanent Access method | heap Size | 65 MB Description | ``` + 最后的交换和删除阶段。 在重建操作的其余部分中，它只需对原始表使用 `ACCESS SHARE` 锁，即可将行从该表复制到新表。这有助于 INSERT、UPDATE 和 DELETE 操作照常进行。 ## 建议 当您使用 `pg_repack` 扩展从表和索引中移除膨胀内容时，以下建议适用： + 在非工作时间或在维护时段内执行重新打包，以最大限度地减少它对其它数据库活动性能的影响。 + 在重建活动期间，密切监视阻止会话，并确保原始表上不存在可能阻止 `pg_repack` 的活动，特别是在最后的交换和删除阶段，此时它需要对原始表进行独占锁定。有关更多信息，请参阅[识别阻止查询的内容](https://repost.aws/knowledge-center/rds-aurora-postgresql-query-blocked)。 当您看到阻止会话时，经慎重考虑后，可以使用以下命令将其终止。这有助于继续执行 `pg_repack` 以完成重建： ``` SELECT pg_terminate_backend(pid); ``` + 在事务速率非常高的系统上应用 `pg_repack's` 日志表中的应计更改时，应用过程可能无法跟上更改速率。在这种情况下，`pg_repack` 将无法完成应用过程。有关更多信息，请参阅 [在重新打包期间监控新表](#Appendix.PostgreSQL.CommonDBATasks.pg_repack.Monitoring)。如果索引严重膨胀，另一种解决方案是执行仅限索引的重新打包。这还有助于 VACUUM 的索引清理周期更快地完成。 您可以使用 PostgreSQL 版本 12 中的手动 VACUUM 跳过索引清理阶段，在 PostgreSQL 版本 14 中的紧急 autovacuum 期间会自动跳过索引清理阶段。这有助于在不消除索引膨胀的情况下更快地完成 VACUUM，并且仅适用于紧急情况，例如防止重叠 VACUUM。有关更多信息，请参阅《Amazon Aurora 用户指南》中的[避免索引膨胀](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.diag-table-ind-bloat.html#AuroraPostgreSQL.diag-table-ind-bloat.AvoidinginIndexes)。 ## 先决条件 + 该表必须具有 PRIMARY KEY 或非 null UNIQUE 约束。 + 客户端和服务器的扩展版本必须相同。 + 确保 RDS 实例的 `FreeStorageSpace` 超过表的总大小，而不会出现膨胀。例如，假设表（包括 TOAST 和索引）的总大小为 2TB，表中的总膨胀为 1TB。所需 `FreeStorageSpace` 必须大于以下计算返回的值： `2TB (Table size)` - `1TB (Table bloat)` = `1TB` 您可以使用以下查询来检查表的总大小，并使用 `pgstattuple` 来派生膨胀。有关更多信息，请参阅《Amazon Aurora 用户指南》中的[诊断表和索引膨胀](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.diag-table-ind-bloat.html) ``` SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size; ``` 活动完成后，将回收此空间。 + 确保 RDS 实例有足够的计算和 IO 容量来处理重新打包操作。您可以考虑纵向扩展实例类以实现性能的最佳平衡。 **使用 `pg_repack` 扩展** 1. 通过运行以下命令在 RDS for PostgreSQL 数据库实例上安装 `pg_repack` 扩展。 ``` CREATE EXTENSION pg_repack; ``` 1. 运行以下命令以授予对由 `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; ``` 1. 使用 `pg_repack` 客户端实用程序连接到数据库。使用具有 `rds_superuser` 特权的账户。例如，假设 `rds_test` 角色有 `rds_superuser` 特权。以下语法对完整表执行 `pg_repack`，包括 `postgres` 数据库中的所有表索引。 ``` pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres ``` **注意** 必须使用 -k 选项进行连接。不支持 -a 选项。 来自 `pg_repack` 客户端的响应提供有关重新打包的数据库实例上的表的信息。 ``` INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches" ``` 1. 以下语法对 `postgres` 数据库中包含索引的单个表 `orders` 进行重新打包。 ``` pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders -k postgres ``` 以下语法仅重新打包 `postgres` 数据库中 `orders` 表的索引。 ``` pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders --only-indexes -k postgres ``` ## 在重新打包期间监控新表 + 在重新打包的交换和删除阶段之前，数据库的大小增加量为表的总大小减去膨胀。您可以监控数据库大小的增长率，计算重新打包的速度，并粗略估计完成初始数据传输所需的时间。 例如，假设表的总大小为 2TB，数据库的大小为 4TB，表中的总膨胀为 1TB。重新打包操作结束时计算返回的数据库总大小值如下： `2TB (Table size)` \$1 `4 TB (Database size)` - `1TB (Table bloat)` = `5TB` 您可以通过对两个时间点之间的增长率（以字节为单位）进行采样来粗略估计重新打包操作的速度。如果增长率为每分钟 1GB，则可能需要 1000 分钟或大约 16.6 小时才能完成初始表构建操作。除了初始表构建外，`pg_repack` 还需要应用应计更改。所需时间取决于应用持续更改以及应计更改的速率。 **注意** 您可以使用 `pgstattuple` 扩展来计算表中的膨胀。有关更多信息，请参阅 [pgstattuple](https://www.postgresql.org/docs/current/pgstattuple.html)。 + 重新打包架构下 `pg_repack's` 日志表中的行数表示在初始加载后待应用于新表的更改量。 您可以检查 `pg_stat_all_tables` 中的 `pg_repack's` 日志表以监控应用于新表的更改。`pg_stat_all_tables.n_live_tup` 表示待应用于新表的记录数。有关更多信息，请参阅 [pg\$1stat\$1all\$1tables](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW)。 ``` postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%'; -[ RECORD 1 ]--------- relname | log_16490 n_live_tup | 2000000 ``` + 您可以使用 `pg_stat_statements` 扩展来找出重新打包操作中每个步骤所花费的时间。这有助于准备在生产环境中应用相同的重新打包操作。您可以调整 `LIMIT` 子句以进一步扩展输出。 ``` postgres=>SELECT SUBSTR(query, 1, 100) query, round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes FROM pg_stat_statements WHERE query ILIKE '%repack%' ORDER BY total_exec_time DESC LIMIT 5; query | total_exec_time_in_minutes -----------------------------------------------------------------------+---------------------------- CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) | 6.8627 INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1 | 6.4150 SELECT repack.repack_apply($1, $2, $3, $4, $5, $6) | 0.5395 SELECT repack.repack_drop($1, $2) | 0.0004 SELECT repack.repack_swap($1) | 0.0004 (5 rows) ``` 重新打包完全是一项不合时宜的操作，因此原始表不会受到影响，我们预计不会出现任何需要恢复原始表的意外挑战。如果重新打包意外失败，则必须检查错误的原因并加以解决。 问题解决后，在表所在的数据库中删除并重新创建 `pg_repack` 扩展，然后重试 `pg_repack` 步骤。此外，计算资源的可用性和表的并行可访问性在及时完成重新打包操作方面起着至关重要的作用。 # 升级和使用 PLV8 扩展 PLV8 是适用于 PostgreSQL 的可信 Javascript 语言扩展。您可以将其用于存储过程、触发条件和其他可从 SQL 调用的过程代码。PostgreSQL 的所有当前版本都支持此语言扩展。 如果使用 [PLV8](https://plv8.github.io/) 并将 PostgreSQL 升级到新的 PLV8 版本，您可以立即利用新扩展。按下列步骤将目录元数据与新版本的 PLV8 进行同步。这些步骤是可选的，但我们强烈建议您完成这些步骤以避免元数据错配警告。 升级过程会丢弃所有现有的 PLV8 功能。因此，建议您在升级之前创建 RDS for PostgreSQL 数据库实例的快照。有关更多信息，请参阅 [为 Amazon RDS 的单可用区数据库实例创建数据库快照](USER_CreateSnapshot.md)。 **重要** 从 PostgreSQL 版本 18 开始，Amazon RDS for PostgreSQL 将弃用 `plcoffee` 和 `plls` PostgreSQL 扩展。建议您停止在应用程序中使用 CoffeeScript 和 LiveScript，以确保为未来的引擎版本升级提供升级途径。 **将目录元数据与新版本的 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) ``` 1. 如果尚未创建 RDS for PostgreSQL 数据库实例的快照，请先创建一个。创建快照时，您可以继续下列步骤。 1. 获取数据库实例中 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'); ``` 1. 使用 pg\$1dump 创建仅架构转储文件。例如，在客户端计算机上的 `/tmp` 目录中创建文件。 ``` ./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp ``` 该示例使用以下选项： + `-Fc` - 自定义格式 + --仅架构 - 仅转储创建架构所需的命令（在本例中为功能） + `-U` - RDS 主用户名 + `database` - 数据库实例的数据库名称 有关 pg\$1dump 的更多信息，请参阅 PostgreSQL 文档中的 [pg\$1dump](https://www.postgresql.org/docs/current/static/app-pgdump.html )。 1. 提取位于转储文件中的“CREATE FUNCTION”DDL 语句。以下示例使用 `grep` 命令提取创建函数的 DDL 语句并将函数保存到文件中。将在后续步骤中使用该语句重新创建函数。 ``` ./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list ``` 有关 pg\$1restore 的更多信息，请参阅 PostgreSQL 文档中的 [pg\$1restore](https://www.postgresql.org/docs/current/static/app-pgrestore.html)。 1. 删掉函数和扩展。以下示例将删除基于 PLV8 的任何对象。级联选项确保删除任何依赖项。 ``` DROP EXTENSION plv8 CASCADE; ``` 如果您的 PostgreSQL 实例包含基于 plcoffee 或 plls 的对象，请对这些扩展重复此步骤。 1. 创建扩展。以下示例将创建 plv8、plcoffee 和 plls 扩展。 ``` CREATE EXTENSION plv8; CREATE EXTENSION plcoffee; CREATE EXTENSION plls; ``` 1. 使用转储文件和“驱动程序”文件创建函数。 以下示例将重新创建之前提取的函数。 ``` ./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp ``` 1. 使用以下查询验证是否已重新创建所有函数。 ``` SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee'); ``` PLV8 版本 2 在结果集中添加以下额外行： ``` proname | nspname | lanname ---------------+------------+---------- plv8_version | pg_catalog | plv8 ``` # 使用 PL/Rust 通过 Rust 语言编写 PostgreSQL 函数 PL/Rust 是 PostgreSQL 的可信 Rust 语言扩展。您可以将其用于存储过程、函数和其他可从 SQL 调用的过程代码。PL/Rust 语言扩展有以下版本可用： + RDS for PostgreSQL 17.1 及更高的 17 版本 + RDS for PostgreSQL 16.1 及更高的 16 版本 + RDS for PostgreSQL 15.2-R2 和更高的 15 版本 + RDS for PostgreSQL 14.9 和更高的 14 版本 + RDS for PostgreSQL 13.12 和更高的 13 版本 有关更多信息，请参阅 GitHub 上的 [PL/Rust](https://github.com/tcdi/plrust#readme)。 **Topics** + [ ## 设置 PL/Rust ](#PL_Rust-setting-up) + [ ## 使用 PL/Rust 创建函数 ](#PL_Rust-create-function) + [ ## 将 crate 与 PL/Rust 结合使用 ](#PL_Rust-crates) + [ ## PL/Rust 限制 ](#PL_Rust-limitations) ## 设置 PL/Rust 要在数据库实例上安装 plrust 扩展，请将 plrust 添加到与数据库实例相关联的数据库参数组中的 `shared_preload_libraries` 参数。安装了 plrust 扩展后，您可以创建函数。 要修改 `shared_preload_libraries` 参数，数据库实例必须与自定义参数组相关联。有关创建自定义数据库参数组的信息，请参阅[Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。 您可以使用 AWS 管理控制台或 AWS CLI 安装 plrust 扩展程序。 以下步骤假设您的数据库实例与自定义数据库参数组相关联。 ### 控制台 **在 `shared_preload_libraries` 参数中安装 plrust 扩展** 使用作为 `rds_superuser` 组（角色）成员的账户完成以下步骤。 1. 登录AWS 管理控制台并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。 1. 在导航窗格中，选择 **Databases (数据库)**。 1. 选择数据库实例的名称以显示其详细信息。 1. 打开数据库实例的**配置**选项卡，并找到数据库实例参数组链接。 1. 选择此链接以打开与您的数据库实例关联的自定义参数。 1. 在**参数**搜索字段中，键入 `shared_pre` 以查找 **`shared_preload_libraries`** 参数。 1. 选择 **Edit parameters**（编辑参数）以访问属性值。 1. 将 plrust 添加到**值**字段的列表中。使用逗号分隔值列表中的项目。 1. 重启数据库实例以使对 `shared_preload_libraries` 参数的更改生效。初次重启可能需要额外的时间才能完成。 1. 当实例可用时，验证 plrust 是否已初始化。使用 `psql` 连接到数据库实例，然后并运行以下命令。 ``` SHOW shared_preload_libraries; ``` 您的输出应类似于以下内容： ``` shared_preload_libraries -------------------------- rdsutils,plrust (1 row) ``` ### AWS CLI **在 shared\$1preload\$1libraries 参数中安装 plrust 扩展** 使用作为 `rds_superuser` 组（角色）成员的账户完成以下步骤。 1. 使用 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) AWS CLI 命令将 plrust 添加到 `shared_preload_libraries` 参数。 ``` aws rds modify-db-parameter-group \ --db-parameter-group-name custom-param-group-name \ --parameters "ParameterName=shared_preload_libraries,ParameterValue=plrust,ApplyMethod=pending-reboot" \ --region aws-region ``` 1. 使用 [reboot-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/reboot-db-instance) AWS CLI 命令重启数据库实例并初始化 plrust 库。初次重启可能需要额外的时间才能完成。 ``` aws rds reboot-db-instance \ --db-instance-identifier your-instance \ --region aws-region ``` 1. 当实例可用时，您可以验证 plrust 已初始化。使用 `psql` 连接到数据库实例，然后并运行以下命令。 ``` SHOW shared_preload_libraries; ``` 您的输出应类似于以下内容： ``` shared_preload_libraries -------------------------- rdsutils,plrust (1 row) ``` ## 使用 PL/Rust 创建函数 PL/Rust 会将函数编译为动态库、加载动态库并执行该库。 以下 Rust 函数从数组中筛选出倍数。 ``` postgres=> CREATE LANGUAGE plrust; CREATE EXTENSION ``` ``` CREATE OR REPLACE FUNCTION filter_multiples(a BIGINT[], multiple BIGINT) RETURNS BIGINT[] IMMUTABLE STRICT LANGUAGE PLRUST AS $$ Ok(Some(a.into_iter().filter(|x| x.unwrap() % multiple != 0).collect())) $$; WITH gen_values AS ( SELECT ARRAY(SELECT * FROM generate_series(1,100)) as arr) SELECT filter_multiples(arr, 3) from gen_values; ``` ## 将 crate 与 PL/Rust 结合使用 在 RDS for PostgreSQL 版本 16.3-R2 及更高版本、15.7-R2 及更高的 15 版本、14.12-R2 及更高的 14 版本、13.15-R2 及更高的 13 版本中，PL/Rust 支持额外的 crate： + `url` + `regex` + `serde` + `serde_json` 在 RDS for PostgreSQL 版本 15.5-R2 及更高版本、14.10-R2 及更高的 14 版本、13.13-R2 及更高的 13 版本中，PL/Rust 支持另外两个 crate： + `croaring-rs` + `num-bigint` 从 Amazon RDS for PostgreSQL 版本 15.4、14.9 和 13.12 开始，PL/Rust 支持以下 crate： + `aes` + `ctr` + `rand` 这些 crate 仅支持默认特征。新的 RDS for PostgreSQL 版本可能包含更新版本的 crate，并且可能不再支持旧版本的 crate。 按照执行主要版本升级的最佳实践来测试您的 PL/Rust 函数是否与新的主要版本兼容。有关更多信息，请参阅博客[将 Amazon RDS 升级到 PostgreSQL 主要和次要版本的最佳实践](https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-to-major-and-minor-versions-of-postgresql/)，以及《Amazon RDS 用户指南》中的[为 Amazon RDS 升级 PostgreSQL 数据库引擎](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html)。 有关在创建 PL/Rust 函数时使用依赖关系的示例，请访问[使用依赖关系](https://tcdi.github.io/plrust/use-plrust.html#use-dependencies)。 ## PL/Rust 限制 默认情况下，数据库用户无法使用 PL/Rust。要提供对 PL/Rust 的访问权限，请以具有 rds\$1superuser 权限的用户身份进行连接，然后运行以下命令： ``` postgres=> GRANT USAGE ON LANGUAGE PLRUST TO user; ``` # 使用 PostGIS 扩展管理空间数据 PostGIS 是一个 PostgreSQL 扩展，用于存储和管理空间信息。要了解有关 PostGIS 的更多信息，请参阅 [PostGIS.net](https://postgis.net/)。 从版本 10.5 开始，PostgreSQL 支持 PostGIS 用于处理地图框矢量平铺数据的 libprotobuf 1.3.0 库。 设置 PostGIS 扩展需要 `rds_superuser` 权限。我们建议您创建一个用户（角色），以管理 PostGIS 和您的空间数据。PostGIS 扩展及其相关组件为 PostgreSQL 添加了数千个函数。如果这对您的使用案例有意义，请考虑在自己的架构中创建 PostGIS 扩展。以下示例说明了如何在其各自的数据库中安装扩展，但这并不是必需的。 **Topics** + [ ## 步骤 1：创建用户（角色）来管理 PostGIS 扩展 ](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect) + [ ## 步骤 2：加载 PostGIS 扩展 ](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions) + [ ## 步骤 3：移交扩展架构的所有权 ](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership) + [ ## 步骤 4：移交 PostGIS 表的所有权 ](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects) + [ ## 步骤 5：测试扩展 ](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test) + [ ## 步骤 6：升级 PostGIS 扩展 ](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update) + [PostGIS 扩展版本](#CHAP_PostgreSQL.Extensions.PostGIS) + [将 PostGIS 2 升级到 PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3) ## 步骤 1：创建用户（角色）来管理 PostGIS 扩展 首先，以具有 `rds_superuser` 权限的用户身份连接到 RDS for PostgreSQL 数据库实例。如果您在设置实例时保留原定设置名称，则以 `postgres` 进行连接。 ``` psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password ``` 创建单独的角色（用户）来管理 PostGIS 扩展。 ``` postgres=> CREATE ROLE gis_admin LOGIN PASSWORD 'change_me'; CREATE ROLE ``` 向此角色授予 `rds_superuser` 权限，以允许角色安装扩展。 ``` postgres=> GRANT rds_superuser TO gis_admin; GRANT ``` 创建一个要用于 PostGIS 构件的数据库。此为可选步骤。或者，您可以在用户数据库中为 PostGIS 扩展创建架构，但这也不是必需的。 ``` postgres=> CREATE DATABASE lab_gis; CREATE DATABASE ``` 为 `gis_admin` 提供 `lab_gis` 数据库的所有权限。 ``` postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin; GRANT ``` 退出会话并以 `gis_admin` 身份重新连接 RDS for PostgreSQL 数据库实例。 ``` postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis Password for user gis_admin:... lab_gis=> ``` 按照后续步骤中的详细说明，继续设置扩展。 ## 步骤 2：加载 PostGIS 扩展 PostGIS 扩展模块包括多个相关的扩展，它们协同工作以提供地理空间功能。您可能不需要在此步骤中创建的所有扩展，具体取决于您的使用案例。 使用 `CREATE EXTENSION` 语句加载 PostGIS 扩展。 ``` CREATE EXTENSION postgis; CREATE EXTENSION CREATE EXTENSION postgis_raster; CREATE EXTENSION CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION CREATE EXTENSION postgis_topology; CREATE EXTENSION CREATE EXTENSION address_standardizer_data_us; CREATE EXTENSION ``` 您可以通过运行以下示例中显示的 SQL 查询来验证结果，该查询列出了扩展及其所有者。 ``` SELECT n.nspname AS "Name", pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; List of schemas Name | Owner --------------+----------- public | postgres tiger | rdsadmin tiger_data | rdsadmin topology | rdsadmin (4 rows) ``` ## 步骤 3：移交扩展架构的所有权 使用 ALTER SCHEMA 语句将架构的所有权移交给 `gis_admin` 角色。 ``` ALTER SCHEMA tiger OWNER TO gis_admin; ALTER SCHEMA ALTER SCHEMA tiger_data OWNER TO gis_admin; ALTER SCHEMA ALTER SCHEMA topology OWNER TO gis_admin; ALTER SCHEMA ``` 您可以通过运行以下 SQL 查询来确认所有权变更。或者，您也可以从 psql 命令行使用 `\dn` 元命令。 ``` SELECT n.nspname AS "Name", pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; List of schemas Name | Owner --------------+--------------- public | postgres tiger | gis_admin tiger_data | gis_admin topology | gis_admin (4 rows) ``` ## 步骤 4：移交 PostGIS 表的所有权 **注意** 请勿更改 PostGIS 函数的所有权。PostGIS 的正常运行和未来升级要求这些函数保留原始所有权。有关 PostGIS 权限的更多信息，请参阅 [PostgreSQL Security](https://postgis.net/workshops/postgis-intro/security.html)。 使用以下函数将 PostGIS 表的所有权移交给 `gis_admin` 角色。从 psql 提示符处运行以下语句以创建此函数。 ``` CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$; CREATE FUNCTION ``` 接下来，运行以下查询以运行 `exec` 函数，该函数进而将运行语句并更改权限。 ``` SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;') FROM ( SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE nspname in ('tiger','topology') AND relkind IN ('r','S','v') ORDER BY relkind = 'S') s; ``` ## 步骤 5：测试扩展 为避免需要指定架构名称，请使用以下命令将 `tiger` 架构添加到搜索路径中。 ``` SET search_path=public,tiger; SET ``` 使用以下 SELECT 语句测试 `tiger` 架构。 ``` SELECT address, streetname, streettypeabbrev, zip FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na; address | streetname | streettypeabbrev | zip ---------+------------+------------------+------- 1 | Devonshire | Pl | 02109 (1 row) ``` 要了解有关此扩展的更多信息，请参阅 PostGIS 文档中的 [Tiger 地理编码器](https://postgis.net/docs/Extras.html#Tiger_Geocoder)。 使用以下 `SELECT` 语句测试对 `topology` 架构的访问。这将调用 `createtopology` 函数，以使用指定的空间参考标识符（26986）和原定设置容差（0.5）注册新的拓扑对象（my\$1new\$1topo）。要了解更多信息，请参阅 PostGIS 文档中的 [CreateTopology](https://postgis.net/docs/CreateTopology.html)。 ``` SELECT topology.createtopology('my_new_topo',26986,0.5); createtopology ---------------- 1 (1 row) ``` ## 步骤 6：升级 PostGIS 扩展 PostgreSQL 的每个新发行版都支持与该发行版兼容的一个或多个 PostGIS 扩展版本。将 PostgreSQL 引擎升级到新版本不会自动升级 PostGIS 扩展。在升级 PostgreSQL 引擎之前，通常需要将 PostGIS 升级到当前 PostgreSQL 版本的最新可用版本。有关更多信息，请参阅 [PostGIS 扩展版本](#CHAP_PostgreSQL.Extensions.PostGIS)。 PostgreSQL 引擎升级后，您可以再次将 PostGIS 扩展升级到新升级的 PostgreSQL 引擎版本所支持的版本。有关升级 PostgreSQL 引擎的更多信息，请参阅。[如何执行 RDS for PostgreSQL 的主要版本升级](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.md) 您可以随时检查 RDS for PostgreSQL 数据库实例上可用的 PostGIS 扩展版本更新。为此，请运行以下命令。PostGIS 2.5.0 及更高版本可以使用此功能。 ``` SELECT postGIS_extensions_upgrade(); ``` 如果您的应用程序不支持最新的 PostGIS 版本，您可以安装主要版本中提供的旧版本 PostGIS，如下所示。 ``` CREATE EXTENSION postgis VERSION "2.5.5"; ``` 如果要从旧版本升级到特定 PostGIS 版本，还可以使用以下命令。 ``` ALTER EXTENSION postgis UPDATE TO "2.5.5"; ``` 根据要从中进行升级的版本，您可能需要再次使用此函数。第一次运行该函数的结果决定是否需要额外的升级功能。例如，从 PostGIS 2 升级到 PostGIS 3 就是这种情况。有关更多信息，请参阅 [将 PostGIS 2 升级到 PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)。 如果您升级此扩展是为了准备进行 PostgreSQL 引擎的主要版本升级，则可以继续执行其他初步任务。有关更多信息，请参阅。[如何执行 RDS for PostgreSQL 的主要版本升级](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.md) ## PostGIS 扩展版本 我们建议您安装 《Amazon RDS for PostgreSQL 发布说明》**的 [Amazon RDS for PostgreSQL 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html)中列出的所有扩展版本，如 PostGIS。要获取发行版中可用的版本列表，请使用以下命令。 ``` SELECT * FROM pg_available_extension_versions WHERE name='postgis'; ``` 您可以在《Amazon RDS for PostgreSQL 发布说明》**的以下部分中找到版本信息： + [Amazon RDS 上支持的 PostgreSQL 16 版本扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x) + [Amazon RDS 上支持的 PostgreSQL 版本 15 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-15x) + [Amazon RDS 上支持的 PostgreSQL 版本 14 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-14x) + [Amazon RDS 上支持的 PostgreSQL 版本 13 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-13x) + [Amazon RDS 上支持的 PostgreSQL 版本 12 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-12x) + [Amazon RDS 上支持的 PostgreSQL 版本 11 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-11x) + [Amazon RDS 上支持的 PostgreSQL 版本 10 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-101x) + [Amazon RDS 上支持的 PostgreSQL 版本 9.6.x 扩展](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-96x) ## 将 PostGIS 2 升级到 PostGIS 3 从版本 3.0 开始，PostGIS 光栅功能现在是一个单独的扩展，即 `postgis_raster`。此扩展有自己的安装和升级路径。这将从核心 `postgis` 扩展中删除光栅图像处理所需的数十个函数、数据类型和其他构件。这意味着，如果您的使用案例不需要光栅处理，则不需要安装 `postgis_raster` 扩展。 在以下升级示例中，第一个升级命令将光栅功能提取到 `postgis_raster` 扩展。然后，需要使用第二个升级命令将 `postgis_raster` 升级到新版本。 **从 PostGIS 2 升级到 PostGIS 3** 1. 确定 RDS for PostgreSQL 数据库实例上 PostgreSQL 版本可用的 PostGIS 的默认版本。为此，请运行以下查询。 ``` SELECT * FROM pg_available_extensions WHERE default_version > installed_version;   name   | default_version | installed_version |                          comment ---------+-----------------+-------------------+------------------------------------------------------------  postgis | 3.1.4           | 2.3.7             | PostGIS geometry and geography spatial types and functions (1 row) ``` 1. 确定 RDS for PostgreSQL 数据库实例上每个数据库中安装的 PostGIS 版本。换句话说，按如下方式查询每个用户数据库。 ``` SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass WHERE e.extname LIKE '%postgis%' ORDER BY 1;   Name   | Version | Schema |                             Description ---------+---------+--------+---------------------------------------------------------------------  postgis | 2.3.7   | public | PostGIS geometry, geography, and raster spatial types and functions (1 row) ``` 原定设置版本（PostGIS 3.1.4）和已安装版本（PostGIS 2.3.7）之间的不匹配意味着您需要升级 PostGIS 扩展。 ``` ALTER EXTENSION postgis UPDATE; ALTER EXTENSION WARNING: unpackaging raster WARNING: PostGIS Raster functionality has been unpackaged ``` 1. 运行以下查询，以验证光栅功能现在是否已包含在自己的程序包中。 ``` SELECT probin, count(*) FROM pg_proc WHERE probin LIKE '%postgis%' GROUP BY probin;           probin          | count --------------------------+-------  $libdir/rtpostgis-2.3    | 107  $libdir/postgis-3        | 487 (2 rows) ``` 输出将显示版本之间仍然存在差异。PostGIS 函数为版本 3（postgis-3），而光栅函数（rtpostgis）为第 2 版（rtpostgis-2.3）。要完成升级，请再次运行升级命令，如下所示。 ``` postgres=> SELECT postgis_extensions_upgrade(); ``` 您可以放心地忽略警告消息。再次运行以下查询，以验证升级已完成。当 PostGIS 和所有相关扩展未标记为需要升级时，升级即告完成。 ``` SELECT postgis_full_version(); ``` 1. 使用以下查询查看已完成的升级过程和单独打包的扩展，并验证其版本是否匹配。 ``` SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass WHERE e.extname LIKE '%postgis%' ORDER BY 1;       Name      | Version | Schema |                             Description ----------------+---------+--------+---------------------------------------------------------------------  postgis        | 3.1.5   | public | PostGIS geometry, geography, and raster spatial types and functions  postgis_raster | 3.1.5   | public | PostGIS raster types and functions (2 rows) ``` 输出显示，PostGIS 2 扩展已升级到 PostGIS 3，并且 `postgis` 和现在独立的 `postgis_raster` 扩展都为版本 3.1.5。 升级完成后，如果您不打算使用光栅功能，则可以按如下方式删除此扩展。 ``` DROP EXTENSION postgis_raster; ```