# 使用扩展和外部数据包装器
要扩展 Aurora PostgreSQL 兼容版数据库集群的功能，您可以安装和使用各种 PostgreSQL *扩展*。例如，如果您的使用案例要求在非常大的表中输入密集型数据，则可以安装 `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` 扩展以对数据进行分区，从而分散工作负载。
**注意**
自 Aurora PostgreSQL 14.5 起，Aurora PostgreSQL 支持适用于 PostgreSQL 的可信语言扩展。此特征是作为扩展 `pg_tle` 实现的，您可以将其添加到 Aurora PostgreSQL 中。通过使用此扩展，开发人员可以在安全的环境中创建自己的 PostgreSQL 扩展，从而简化设置和配置要求以及新扩展的许多初步测试。有关更多信息，请参阅 [使用适用于 PostgreSQL 的可信语言扩展](PostgreSQL_trusted_language_extension.md)。
在某些情况下，您可以将特定*模块*添加到 Aurora 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/#)。
提供对外部数据的访问权限的扩展更具体地称为*外部数据包装器*（FDW）。例如，`oracle_fdw` 扩展允许 Aurora PostgreSQL 数据库集群使用 Oracle 数据库。
您还可以通过在 `rds.allowed_extensions` 参数中列出扩展，精确指定可以在 Aurora PostgreSQL 数据库实例上安装哪些扩展。有关更多信息，请参阅[限制 PostgreSQL 扩展的安装](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction.html)。
接下来，您可以找到有关设置和使用 Aurora PostgreSQL 可用的一些扩展、模块和 FDW 的信息。为简单起见，这些都称为“扩展”。您可以找到可与当前可用的 Aurora PostgreSQL 版本结合使用的扩展列表，请参阅《Aurora PostgreSQL 版本注释》**中的 [Amazon Aurora PostgreSQL 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)。
+ [使用 lo 模块管理大型对象](PostgreSQL_large_objects_lo_extension.md)
+ [使用 PostGIS 扩展管理空间数据](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [使用 pg\$1partman 扩展管理 PostgreSQL 分区](PostgreSQL_Partitions.md)
+ [使用 PostgreSQL pg\$1cron 扩展计划维护](PostgreSQL_pg_cron.md)
+ [使用 pgAudit 记录数据库活动](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [使用 pglogical 跨实例同步数据](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [通过使用 oracle\$1fdw 扩展来使用 Oracle 数据库](postgresql-oracle-fdw.md)
+ [通过使用 tds\$1fdw 扩展来使用 SQL Server 数据库](postgresql-tds-fdw.md)
# 使用适用于 PostgreSQL 的 Amazon Aurora 委派扩展支持
使用适用于 PostgreSQL 的 Amazon Aurora 委派扩展支持，您可以将扩展管理委派给其他用户，而且不必让该用户成为 `rds_superuser`。通过这种委派扩展支持，将创建一个名为 `rds_extension` 的新角色，您必须将其分配给用户才能管理其他扩展。此角色可以创建、更新和删除扩展。
您可以通过在 `rds.allowed_extensions` 参数中列出扩展，指定可以在 Aurora PostgreSQL 数据库实例上安装哪些扩展。有关更多信息，请参阅[将 PostgreSQL 扩展与 Amazon RDS for PostgreSQL 结合使用](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html)。
您可以通过 `rds.allowed_delegated_extensions` 参数来限制具有 `rds_extension` 角色的用户可以管理的可用扩展列表。
委派扩展支持在以下版本中可用：
+ 所有更高版本
+ 15.5 及更高的 15 版本
+ 14.10 及更高的 14 版本
+ 13.13 及更高的 13 版本
+ 12.17 及更高的 12 版本
**Topics**
+ [为用户开启委派扩展支持](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [在适用于 PostgreSQL 的 Aurora 委派扩展支持中使用的配置](#AuroraPostgreSQL.delegated_ext_config)
+ [关闭对委派扩展的支持](#AuroraPostgreSQL.delegated_ext_disable)
+ [使用 Amazon Aurora 委派扩展支持的优势](#AuroraPostgreSQL.delegated_ext_benefits)
+ [适用于 PostgreSQL 的 Aurora 委派扩展支持的限制](#AuroraPostgreSQL.delegated_ext_limit)
+ [某些扩展所需的权限](#AuroraPostgreSQL.delegated_ext_perm)
+ [安全考虑因素](#AuroraPostgreSQL.delegated_ext_sec)
+ [已禁用删除扩展级联](#AuroraPostgreSQL.delegated_ext_drop)
+ [可以使用委派扩展支持添加的扩展示例](#AuroraPostgreSQL.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 Aurora 的参数组](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 的 Aurora 委派扩展支持中使用的配置
| 配置名称 | 说明 | 默认值 | 备注 | 谁可以修改或授予权限 |
| --- | --- | --- | --- | --- |
| `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/AuroraUserGuide/Aurora_delegated_ext.html) 要了解有关设置此参数的更多信息，请参阅[为用户开启委派扩展支持](#AuroraPostgreSQL.delegated_ext_mgmt)。 | rds\$1superuser |
| `rds.allowed_extensions` | 此参数可让客户限制可以在 Aurora PostgreSQL 数据库实例中安装的扩展。有关更多信息，请参阅[限制 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 支持的所有扩展。 空表示无法在 Aurora PostgreSQL 数据库实例中安装任何扩展。 | 管理员 |
| `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 Aurora 委派扩展支持的优势
通过使用适用于 PostgreSQL 的 Amazon Aurora 委派扩展支持，您可以安全地将扩展管理委派给没有 `rds_superuser` 角色的用户。该功能具有以下优势：
+ 您可以轻松地将扩展管理委派给您选择的用户。
+ 这不需要 `rds_superuser` 角色。
+ 支持为同一个数据库集群中的不同数据库提供不同的扩展集。
## 适用于 PostgreSQL 的 Aurora 委派扩展支持的限制
+ 在扩展创建过程中创建的对象可能需要额外的权限才能使扩展正常运行。
## 某些扩展所需的权限
要创建、使用或更新以下扩展，委派用户应具有对以下函数、表和架构的必要权限。
| 需要所有权或权限的扩展 | 函数 | 表 | 架构 | 文本搜索词典 | Comment |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | | us\$1gaz、us\$1lex、us\$1lex、I.us\$1rules | | | |
| amcheck | bt\$1index\$1check、bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions、part\$1config、part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | topology、layer | topology | | 委派用户必须是数据库所有者 |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default、geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## 安全考虑因素
请记住，具有 `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
```
# 使用 lo 模块管理大型对象
lo 模块（扩展）适用于通过 JDBC 或 ODBC 驱动程序使用 PostgreSQL 数据库的数据库用户和开发人员。JDBC 和 ODBC 都希望数据库能够在对大型对象的引用发生更改时处理对大型对象的删除。但 PostgreSQL 并非如此。PostgreSQL 并不假定在对于对象的引用发生变化时应该删除该对象。结果是对象保留在磁盘上，未引用。lo 扩展包括一个函数，您可以使用该函数在引用发生变化时触发，以便在需要时删除对象。
**提示**
要确定数据库是否可以从 lo 扩展中受益，请使用 `vacuumlo` 实用程序检查孤立的大型对象。要在不采取任何操作的情况下获取孤立的大型对象的计数，请使用 `-n` 选项（无操作）运行此实用程序。要了解如何操作，请参阅下面的 [vacuumlo utility](#vacuumlo-utility)。
lo 模块适用于 Aurora PostgreSQL 13.7、12.11、11.16、10.21 及更高的次要版本。
要安装模块（扩展），您需要 `rds_superuser` 权限。安装 lo 扩展会将以下内容添加到数据库中：
+ `lo` – 这是一种大型对象 (lo) 数据类型，可用于二进制大型对象 (BLOB) 和其他大型对象。`lo` 数据类型是 `oid` 数据类型的域。换句话说，它是一个具有可选限制的对象标识符。有关更多信息，请参阅 PostgreSQL 文档中的[对象标识符](https://www.postgresql.org/docs/14/datatype-oid.html)。简单来说，您可以使用 `lo` 数据类型，以区分包含大型对象引用的数据库列与其他对象标识符 (OID)。
+ `lo_manage` – 这是一个函数，您可以在包含大型对象引用的表列上的触发器中使用。无论何时删除或修改引用大型对象的值，触发器都会取消 (`lo_unlink`) 对象与其引用之间的关联。仅当列是对大型对象的唯一数据库引用时，才对列使用触发器。
有关大型对象模块的更多信息，请参阅 PostgreSQL 文档中的 [Lo](https://www.postgresql.org/docs/current/lo.html)。
## 安装 lo 扩展
在安装 lo 扩展之前，请确保您具有 `rds_superuser` 权限。
**安装 lo 扩展**
1. 使用 `psql` 连接到 Aurora PostgreSQL 数据库集群的主数据库实例。
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
出现提示时请输入密码。`psql` 客户端会建立连接并显示默认的管理连接数据库 `postgres=>`，作为提示符。
1. 按如下方式安装扩展。
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
您现在可以使用 `lo` 数据类型定义表中的列。例如，您可以创建包含光栅图像数据的表 (`images`)。您可以对列 `raster` 使用 `lo` 数据类型，如以下示例所示，它将创建一个表。
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## 使用 lo\$1manage 触发器函数删除对象
在更新或删除 `lo` 时，可以在 `lo` 或其他大型对象列上的触发器中使用 `lo_manage` 函数来清理（并防止出现孤立对象）。
**在引用大型对象的列上设置触发器**
+ 请执行以下操作之一：
+ 对此参数使用列名称，在每个列上创建一个 BEFORE UPDATE OR DELETE 触发器，以包含对大型对象的唯一引用。
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ 仅在更新列时才应用触发器。
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
`lo_manage` 触发器函数仅在插入或删除列数据的上下文中起作用，具体取决于您定义触发器的方式。对数据库执行 `DROP` 或 `TRUNCATE` 操作时，它不起作用。这意味着在删除之前应该从任何表中删除对象列，以防止创建孤立的对象。
例如，假设您要删除包含 `images` 表的数据库。您可以按如下方式删除该列。
```
postgres=> DELETE FROM images COLUMN raster
```
假设在该列上定义了用于处理删除 `lo_manage` 的函数，现在可以安全地删除该表。
## 使用 `vacuumlo` 删除孤立的大型对象
`vacuumlo` 实用程序识别孤立的大型对象并从数据库中删除它们。此实用程序自 PostgreSQL 9.1.24 以来一直可用。如果数据库用户定期使用大型对象，建议您偶尔运行 `vacuumlo` 来清理孤立的大型对象。
在安装 lo 扩展之前，您可以使用 `vacuumlo` 评估 Aurora PostgreSQL 数据库集群是否可以受益。要执行此操作，请使用 `-n` 选项（无操作）运行 `vacuumlo` 以显示要删除的内容，如下所示：
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
如输出所示，孤立的大型对象对于这个特定的数据库来说并不是问题。
有关此实用程序的更多信息，请参阅 PostgreSQL 文档中的 [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html)。
## 了解 `vacuumlo` 的工作方式
`vacuumlo` 命令从 PostgreSQL 数据库中删除孤立的大型对象（LO），而不会影响用户表或与用户表冲突。
该命令的工作原理如下：
1. `vacuumlo` 首先创建一个临时表，其中包含数据库中大型对象的所有对象 ID（OID）。
1. 然后，`vacuumlo` 会全面地扫描数据库中使用 `oid` 或 `lo` 数据类型的每一列。如果 `vacuumlo` 在这些列中找到匹配的 OID，则会从临时表中删除该 OID。`vacuumlo` 仅检查专门命名为 `oid` 或 `lo` 的列，而不检查基于这些类型的域。
1. 临时表中的其余条目表示孤立的大型对象，然后 `vacuumlo` 会安全地删除这些大型对象。
## 提高 `vacuumlo` 性能
您可以通过使用 `-l` 选项增加批处理大小来提高 `vacuumlo` 的性能。这样将允许 `vacuumlo` 一次处理更多大型对象。
如果您的系统有足够的内存，并且您可以将临时表完全容纳在内存中，则在数据库级别增加 `temp_buffers` 设置可提高性能。这样有助于表完全驻留在内存中，从而可提高总体性能。
接下来查询会估计临时表的大小：
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## 大型对象的注意事项
接下来，您可以找到在处理大型对象时需要注意的一些重要注意事项：
+ `Vacuumlo` 是唯一的解决方案，因为目前没有其他方法可以删除孤立的大型对象。
+ 使用复制技术的诸如 pglogical、本机逻辑复制和 AWS DMS 等工具不支持复制大型对象。
+ 在设计数据库架构时，请尽可能避免使用大型对象，并考虑改用 `bytea` 等替代数据类型。
+ 定期运行 `vacuumlo`，至少每周一次，以防止孤立的大型对象出现问题。
+ 在存储大型对象的表上使用带有 `lo_manage` 函数的触发器，以帮助防止创建孤立的大型对象。
# 使用 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 引擎的更多信息，请参阅。[测试将生产数据库集群升级到新的主要版本](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary)
您可以随时检查 Aurora 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 引擎的主要版本升级，则可以继续执行其他初步任务。有关更多信息，请参阅。[测试将生产数据库集群升级到新的主要版本](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary)
## PostGIS 扩展版本
我们建议您安装《Aurora PostgreSQL 发布说明》**的 [Aurora PostgreSQL 兼容版的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) 中列出的所有扩展版本，如 PostGIS。要获取发行版中可用的版本列表，请使用以下命令。
```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```
您可以在《Aurora PostgreSQL 发布说明》**的以下部分中找到版本信息：
+ [Aurora PostgreSQL 14 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Aurora PostgreSQL 兼容版 13 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Aurora PostgreSQL 兼容版 12 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Aurora PostgreSQL 兼容版 11 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Aurora PostgreSQL 兼容版 10 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Aurora PostgreSQL 兼容版 9.6 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## 将 PostGIS 2 升级到 PostGIS 3
从版本 3.0 开始，PostGIS 光栅功能现在是一个单独的扩展，即 `postgis_raster`。此扩展有自己的安装和升级路径。这将从核心 `postgis` 扩展中删除光栅图像处理所需的数十个函数、数据类型和其他构件。这意味着，如果您的使用案例不需要光栅处理，则不需要安装 `postgis_raster` 扩展。
在以下升级示例中，第一个升级命令将光栅功能提取到 `postgis_raster` 扩展。然后，需要使用第二个升级命令将 `postgis_raster` 升级到新版本。
**从 PostGIS 2 升级到 PostGIS 3**
1. 确定 Aurora PostgreSQL 数据库集群 为此，请运行以下查询。
```
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. 确定 Aurora 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;
```
# 使用 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)。
**注意**
Aurora PostgreSQL 版本 12.6 及更高版本支持该 `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 Aurora 中修改数据库参数组中的参数](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)。
# 使用 PostgreSQL pg\$1cron 扩展计划维护
您可以使用 PostgreSQL `pg_cron` 扩展来计划 PostgreSQL 数据库中的维护命令。有关扩展的更多信息，请参阅 pg\$1cron 文档中的[什么是 pg\$1cron？](https://github.com/citusdata/pg_cron)
Aurora PostgreSQL 引擎版本 12.6 及更高版本支持 `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 数据库实例关联的自定义参数组。
重新启动 PostgreSQL 数据库实例，以使对参数组的更改生效。要了解有关使用参数组的更多信息，请参阅[Amazon Aurora PostgreSQL 参数](AuroraPostgreSQL.Reference.ParameterGroups.md)。
1. 重新启动 PostgreSQL 数据库实例后，使用具有 `rds_superuser` 权限的账户运行以下命令。例如，如果在创建 Aurora 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 在大多数情况下处理清理维护。但是，您可能希望在选择的时间计划对特定表执行清理操作。
以下示例介绍了使用 `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 Aurora 中修改数据库参数组中的参数](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/AuroraUserGuide/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/AuroraUserGuide/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)。 |
# 使用 pgAudit 记录数据库活动
金融机构、政府机构和许多行业需要保留*审计日志*以满足监管要求。通过将 PostgreSQL Audit 扩展（pgAudit）与 Aurora PostgreSQL 数据库集群结合使用，您可以捕获审计人员通常需要或满足监管要求的详细记录。例如，您可以设置 pgAudit 扩展来跟踪对特定数据库和表所做的更改，记录进行更改的用户以及许多其他详细信息。
pgAudit 扩展通过更详细地扩展日志消息，进一步构建原生 PostgreSQL 日志记录基础设施的功能。换句话说，您将使用与查看任何日志消息相同的方法来查看审计日志。有关 PostgreSQL 日志记录的更多信息，请参阅 [Aurora PostgreSQL 数据库日志文件](USER_LogAccess.Concepts.PostgreSQL.md)。
pgAudit 扩展会编辑日志中的敏感数据，例如明文密码。如果您的 Aurora PostgreSQL 数据库集群配置为记录数据操作语言（DML）语句（详情请见[为您的 Aurora PostgreSQL 数据库集群开启查询日志记录](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md)），则可以使用 PostgreSQL Audit 扩展来避免明文密码问题。
您可以在数据库实例上配置具有高度明确性的审计。您可以审计所有数据库和所有用户。或者，您可以选择仅审计某些数据库、用户和其他对象。您也可以明确排除对某些用户和数据库进行审计。有关更多信息，请参阅 [从审计日志记录中排除用户或数据库](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)。
考虑到可以捕获的详细信息量，我们建议您在使用 pgAudit 时监控存储消耗。
所有可用的 Aurora PostgreSQL 版本都支持 pgAudit 扩展。有关 Aurora PostgreSQL 版本支持的 pgAudit 版本的列表，请参阅《Aurora PostgreSQL 版本注释》[https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)中的 *Amazon Aurora PostgreSQL 的扩展版本*。。
**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 扩展
要在 Aurora PostgreSQL 数据库集群上设置 pgAudit 扩展，首先要将 pgAudit 添加到 Aurora PostgreSQL 数据库集群的自定义数据库集群参数组上的共享库中。有关创建自定义数据库集群参数组的信息，请参阅[Amazon Aurora 的参数组](USER_WorkingWithParamGroups.md)。接下来，安装 pgAudit 扩展。最后，指定要审计的数据库和对象。本部分中的过程向您展示如何操作。您可以使用 AWS 管理控制台或 AWS CLI。
您必须拥有 `rds_superuser` 角色的权限才能执行所有这些任务。
以下步骤假设您的 Aurora PostgreSQL 数据库集群 与自定义数据库集群 参数组相关联。
## 控制台
**设置 pgAudit 扩展**
1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。
1. 在导航窗格中，选择 Aurora PostgreSQL 数据库集群的写入器实例 。
1. 打开 Aurora PostgreSQL 数据库集群写入器实例的**配置**选项卡。的 Configuration（配置）选项卡。在实例详细信息中，找到 **Parameter group**（参数组）链接。
1. 选择此链接以打开与您的 Aurora 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/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. 重启 Aurora PostgreSQL 数据库集群的写入器实例，以使对 `shared_preload_libraries` 参数的更改生效。
1. 当实例可用时，请验证 pgAudit 是否已初始化。使用 `psql` 连接到 Aurora 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/AuroraUserGuide/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. 从“数据库”列表中选择 Aurora 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 命令重启 Aurora PostgreSQL 数据库集群的写入器实例，以便初始化 pgaudit 库。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 当实例可用时，您可以验证 `pgaudit` 是否已初始化。使用 `psql` 连接到 Aurora 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 命令重启 Aurora PostgreSQL 数据库集群的写入器实例。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# 审计数据库对象
在 Aurora 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/AuroraUserGuide/images/pgaudit-log-example.png)
如本示例所示，“LOG: AUDIT: SESSION”行提供了有关表及其架构的信息以及其他详细信息。
**设置对象审计**
1. 使用 `psql` 连接到 Aurora 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 命令重启 Aurora PostgreSQL 数据库集群的写入器实例，以使对参数的更改生效。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-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 Aurora 日志文件](USER_LogAccess.md)。
要了解关于 pgAudit 扩展的更多信息，请参阅 GitHub 上的 [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md)。
# 从审计日志记录中排除用户或数据库
如 [Aurora PostgreSQL 数据库日志文件](USER_LogAccess.Concepts.PostgreSQL.md)中所述，PostgreSQL 日志会消耗存储空间。使用 pgAudit 扩展会在不同程度上增加日志中收集的数据量，具体取决于您跟踪的更改。您可能不需要审计 Aurora PostgreSQL 数据库集群 中的每个用户或数据库。
为了最大限度地减少对存储的影响，避免不必要地捕获审计记录，您可以将用户和数据库排除在审计范围之外。您还可以在给定会话中更改日志记录。下面的示例向您演示如何操作。
**注意**
会话级别的参数设置优先于 Aurora PostgreSQL 数据库集群的写入器实例的自定义数据库参数组中的设置。如果您不希望数据库用户绕过您的审计日志记录配置设置，请务必更改其权限。
假设您的 Aurora 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` 操作进行审计日志记录。
# 使用 pglogical 跨实例同步数据
所有当前可用的 Aurora PostgreSQL 版本都支持 `pglogical` 扩展。pglogic 扩展早于 PostgreSQL 在版本 10 中引入的功能类似的逻辑复制特征。有关更多信息，请参阅[使用 Aurora 进行 PostgreSQL 逻辑复制的概述](AuroraPostgreSQL.Replication.Logical.md)。
`pglogical` 扩展支持在两个或更多 Aurora PostgreSQL 数据库集群 它还支持在不同的 PostgreSQL 版本之间进行复制，以及在 RDS for PostgreSQL 数据库实例和 Aurora PostgreSQL 数据库集群上运行的数据库之间进行复制。`pglogical` 扩展使用发布-订阅模型将对表和其他对象（例如序列）的更改从发布者复制到订阅者。它依赖于复制插槽来确保更改从发布者节点同步到订阅者节点，定义如下。
+ *发布者节点*是作为要复制到其他节点的数据来源的 Aurora PostgreSQL 数据库集群。发布者节点定义要在发布集中复制的表。
+ *订阅者节点*是用于接收来自发布商的 WAL 更新的 Aurora PostgreSQL 数据库集群。订阅者创建订阅以连接到发布者并获取解码后的 WAL 数据。订阅者创建订阅时，将在发布者节点上创建复制插槽。
在下文中，您可以了解有关设置 `pglogical` 扩展的信息。
**Topics**
+ [pglogical 扩展的要求和限制](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [设置 pglogical 扩展](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [为 Aurora PostgreSQL 数据库集群设置逻辑复制](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [在主要升级后重新建立逻辑复制](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [管理 Aurora PostgreSQL 的逻辑复制插槽](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [pglogical 扩展的参数参考](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## pglogical 扩展的要求和限制
所有当前可用的 Aurora 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 扩展
要在 Aurora PostgreSQL 数据库集群上设置 `pglogical` 扩展，首先要将 `pglogical` 添加到 Aurora PostgreSQL 数据库集群的自定义数据库集群参数组上的共享库中。您还需要将 `rds.logical_replication` 参数的值设置为 `1`，以开启逻辑解码。最后，在数据库中创建此扩展。您可以使用 AWS 管理控制台或 AWS CLI 执行这些任务。
您必须拥有 `rds_superuser` 角色的权限才能执行这些任务。
以下步骤假设您的 Aurora PostgreSQL 数据库集群与自定义数据库集群参数组相关联。有关创建自定义数据库集群参数组的信息，请参阅[Amazon Aurora 的参数组](USER_WorkingWithParamGroups.md)。
## 控制台
**设置 pglogical 扩展**
1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台：[https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)。
1. 在导航窗格中，选择 Aurora PostgreSQL 数据库集群的写入器实例 。
1. 打开 Aurora PostgreSQL 数据库集群写入器实例的**配置**选项卡。在实例详细信息中，找到 **Parameter group**（参数组）链接。
1. 选择此链接以打开与您的 Aurora 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/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. 找到 `rds.logical_replication` 参数并将其设置为 `1`，以开启逻辑复制。
1. 重启 Aurora PostgreSQL 数据库集群的写入器实例，以使更改生效。
1. 当实例可用时，可以使用 `psql`（或 pgAdmin）连接到 Aurora 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. 从数据库列表中选择 Aurora 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`，以针对 Aurora 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 命令重启 Aurora PostgreSQL 数据库集群的写入器实例，以便初始化 pglogical 库。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 当实例可用时，使用 `psql` 连接到 Aurora 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 命令重启 Aurora PostgreSQL 数据库集群的写入器实例。
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# 为 Aurora PostgreSQL 数据库集群设置逻辑复制
以下过程说明如何在两个 Aurora PostgreSQL 数据库集群之间启动逻辑复制。这些步骤假设来源（发布者）和目标（订阅者）都如[设置 pglogical 扩展](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)中所述设置了 `pglogical` 扩展。
**注意**
订阅用户节点的 `node_name` 不能以 `rds` 开头。
**创建发布者节点并定义要复制的表**
这些步骤假设您的 Aurora 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. 将这一 Aurora 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` 扩展设置了 Aurora 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. 在订阅者 Aurora 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. 将这一 Aurora 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)
```
此后，对发布者上的表所做的更改将复制到订阅者上的表中。
# 在主要升级后重新建立逻辑复制
对于设置为逻辑复制的发布者节点的 Aurora PostgreSQL 数据库集群，在可以对其执行主要版本升级之前，您必须删除所有复制插槽，即使是不活动的复制插槽也是如此。我们建议您暂时从发布者节点转移数据库事务，删除复制插槽，升级 Aurora PostgreSQL 数据库集群，然后重新建立并重新启动复制。
复制插槽仅托管在发布者节点上。逻辑复制场景中的 Aurora PostgreSQL 订阅者节点没有要删除的插槽。Aurora PostgreSQL 主要版本升级过程支持将订阅者升级到独立于发布者节点的 PostgreSQL 的新主要版本。但是，升级过程确实会中断复制过程并干扰发布者节点和订阅者节点之间的 WAL 数据同步。在升级发布者、订阅者或两者之后，您需要在发布者和订阅者之间重新建立逻辑复制。以下过程说明了如何确定复制已中断以及如何解决此问题。
## 确定逻辑复制已中断
您可以通过查询发布者节点或订阅者节点来确定复制过程是否已中断，如下所示。
**检查发布者节点**
+ 使用 `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)
```
# 管理 Aurora PostgreSQL 的逻辑复制插槽
对于在逻辑复制场景中充当发布者节点的 Aurora 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)。 |
# 使用 Amazon Aurora PostgreSQL 支持的外部数据包装器
外部数据包装器 (FDW) 是一种特定类型的扩展，提供对外部数据的访问。例如，`oracle_fdw` 扩展允许您的 Aurora PostgreSQL 数据库实例 使用 Oracle 数据库。
在下文中，您可以了解有关几种支持的 PostgreSQL 外部数据包装器的信息。
**Topics**
+ [使用 log\$1fdw 扩展通过 SQL 访问数据库日志](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [使用 postgres\$1fdw 扩展访问外部数据](postgresql-commondbatasks-fdw.md)
+ [使用 mysql\$1fdw 扩展处理 MySQL 数据库](postgresql-mysql-fdw.md)
+ [通过使用 oracle\$1fdw 扩展来使用 Oracle 数据库](postgresql-oracle-fdw.md)
+ [通过使用 tds\$1fdw 扩展来使用 SQL Server 数据库](postgresql-tds-fdw.md)
# 使用 log\$1fdw 扩展通过 SQL 访问数据库日志
Aurora PostgreSQL 数据库集群支持 `log_fdw` 扩展，您可以使用该扩展通过 SQL 界面访问数据库引擎日志。此 `log_fdw` 扩展提供了两个新函数，便于创建数据库日志的外部表：
+ `list_postgres_log_files` – 列出数据库日志目录中的文件，以及文件大小 (以字节为单位)。
+ `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – 针对当前数据库中的指定文件构建外部表。
`log_fdw` 创建的所有函数均归 `rds_superuser` 所有。`rds_superuser` 角色的成员可以将这些函数的访问权限授予其他数据库用户。
默认情况下，日志文件由 Amazon Aurora 以 `stderr`（标准错误）格式生成，如 `log_destination` 参数中指定。此参数只有两个选项，即，`stderr` 和 `csvlog`（逗号分隔值，CSV）。如果您向参数添加 `csvlog` 选项，Amazon Aurora 会同时生成 `stderr` 和 `csvlog` 日志。这可能会影响数据库集群的存储容量，因此您需要了解影响日志处理的其它参数。有关更多信息，请参阅 [设置日志目标（`stderr`、`csvlog`）](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format)。
生成 `csvlog` 日志的一个优势是 `log_fdw` 扩展允许您构建将数据整齐地拆分为多个列的外部表。为此，您的实例需要与自定义数据库参数组关联，以便您可以更改 `log_destination` 的设置。有关如何执行此操作的更多信息，请参阅。[Amazon Aurora 的参数组](USER_WorkingWithParamGroups.md)
以下示例假设 `log_destination` 参数包含 `cvslog`。
**使用 log\$1fdw 扩展**
1. 安装 `log_fdw` 扩展。
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. 创建日志服务器，作为外部数据包装器。
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. 选择日志文件列表中的所有文件。
```
postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;
```
示例响应如下所示。
```
file_name | file_size_bytes
------------------------------+-----------------
postgresql.log.2023-08-09-22.csv | 1111
postgresql.log.2023-08-09-23.csv | 1172
postgresql.log.2023-08-10-00.csv | 1744
postgresql.log.2023-08-10-01.csv | 1102
(4 rows)
```
1. 为所选文件创建包含单个“log\$1entry”列的表。
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
除了告知现在存在表格外，响应不提供详细信息。
```
-----------------------------------
(1 row)
```
1. 选择日志文件的示例。以下代码检索日志时间和错误消息描述。
```
postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
```
示例响应如下所示。
```
log_time | message
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)
```
# 使用 postgres\$1fdw 扩展访问外部数据
您可以使用 [postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html) 扩展访问远程数据库服务器上表中的数据。如果您从 PostgreSQL 数据库实例设置远程连接，则访问还可用于您的只读副本。
**使用 postgres\$1fdw 访问远程数据库服务器**
1. 安装 postgres\$1fdw 扩展。
```
CREATE EXTENSION postgres_fdw;
```
1. 使用 CREATE SERVER 创建外部数据服务器。
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. 创建用户映射，用于标识将在远程服务器上使用的角色。
**重要**
要掩蔽密码以使其不出现在日志中，请在会话级别设置 `log_statement=none`。在参数级别进行设置不会掩蔽密码。
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. 创建一个表，该表映射到远程服务器上的表。
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# 使用 mysql\$1fdw 扩展处理 MySQL 数据库
要从 Aurora PostgreSQL 数据库集群访问 MySQL 兼容数据库，您可以安装并使用 `mysql_fdw` 扩展。这款外部数据包装器允许您使用 RDS for MySQL、Aurora MySQL、MariaDB 和其他 MySQL 兼容数据库。从 Aurora PostgreSQL 数据库集群到 MySQL 数据库的连接会尽可能加密，具体取决于客户端和服务器配置。但是，如果您愿意，可以强制加密。有关更多信息，请参阅 [将传输中加密与扩展配合使用](#postgresql-mysql-fdw.encryption-in-transit)。
`mysql_fdw` 扩展在 Amazon Aurora PostgreSQL 版本 15.4、14.9、13.12、12.16、 以及更高版本中受支持。它支持从 RDS for PostgreSQL 数据库到 MySQL 兼容数据库实例上的表的选择、插入、更新和删除。
**Topics**
+ [将 Aurora PostgreSQL 数据库设置为使用 mysql\$1fdw 扩展](#postgresql-mysql-fdw.setting-up)
+ [示例：从 Aurora PostgreSQL 使用 Aurora MySQL 数据库](#postgresql-mysql-fdw.using-mysql_fdw)
+ [将传输中加密与扩展配合使用](#postgresql-mysql-fdw.encryption-in-transit)
## 将 Aurora PostgreSQL 数据库设置为使用 mysql\$1fdw 扩展
在您的 Aurora PostgreSQL 数据库集群上设置 `mysql_fdw` 扩展涉及到在数据库集群中加载扩展，然后创建到 MySQL 数据库实例的连接点。对于该任务，您需要了解有关 MySQL 数据库实例的以下详细信息：
+ 主机名或终端节点。对于Aurora MySQL 数据库集群，您可以使用控制台查找终端节点。选择 Connectivity & security（连接和安全）选项卡，然后查看 Endpoint and port（终端节点和端口）部分。
+ 端口号。MySQL 的默认端口是 3306。
+ 数据库的名称。数据库标识符。
您还需要为 MySQL 端口 3306 提供对安全组或访问控制列表 (ACL) 的访问权限。Aurora PostgreSQL 数据库集群和 Aurora MySQL 数据库集群均需要访问端口 3306。如果访问权限配置不正确，当尝试连接到 MySQL 兼容表时，您会看到一条与以下内容类似的错误消息：
```
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)
```
在以下过程中，您（作为 `rds_superuser` 账户）创建外部服务器。然后，您将访问外部服务器的权限授予特定用户。然后，这些用户创建其自身到相应 MySQL 用户账户的映射以使用 MySQL 数据库实例。
**使用 mysql\$1fdw 访问 MySQL 数据库服务器**
1. 使用具有 `rds_superuser` 角色的账户连接到您的 PostgreSQL 数据库实例。如果在创建 Aurora PostgreSQL 数据库集群时接受默认值，则用户名为 `postgres`，您可以使用 `psql` 命令行工具进行连接，如下所示：
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. 按如下方式安装 `mysql_fdw` 扩展：
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
在 Aurora PostgreSQL 数据库集群上安装扩展后，您可以设置提供与 MySQL 数据库连接的外部服务器。
**创建外部服务器**
在 Aurora PostgreSQL 数据库集群上执行这些任务。这些步骤假定您以具有 `rds_superuser` 特权的用户身份连接，例如 `postgres`。
1. 在 Aurora PostgreSQL 数据库集群中创建外部服务器：
```
postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. 向适当的用户授予访问外部服务器的权限。这些用户应该是非管理员用户，即，没有 `rds_superuser` 角色的用户。
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
PostgreSQL 用户通过外部服务器创建和管理其自身与 MySQL 数据库的连接。
## 示例：从 Aurora PostgreSQL 使用 Aurora MySQL 数据库
假设您在 Aurora PostgreSQL 数据库实例上有一个简单的表。您的 Aurora PostgreSQL 用户想要在该表中查询（`SELECT`）、`INSERT`、`UPDATE` 和 `DELETE` 项目。假设 `mysql_fdw` 扩展是在您的 RDS for PostgreSQL 数据库实例上创建的，如前面的过程中所述。以具有 `rds_superuser` 权限的用户身份连接到 RDS for PostgreSQL 数据库实例后，您可以继续执行以下步骤。
1. 在 Aurora PostgreSQL 数据库实例上，创建一个外部服务器：
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. 将使用权授予没有 `rds_superuser` 权限的用户，例如 `user1`：
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. 作为 *user1* 连接，然后创建一个到 MySQL 用户的映射：
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. 创建链接到 MySQL 表的外部表：
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. 针对外表运行简单查询：
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. 您可以从 MySQL 表中添加、更改和删除数据。例如：
```
test=> INSERT INTO mytab values (2, 'mango');
INSERT 0 1
```
再次运行 `SELECT` 查询以查看结果：
```
test=> SELECT * FROM mytab ORDER BY 1;
a | b
---+-------
1 | apple
2 | mango
(2 rows)
```
## 将传输中加密与扩展配合使用
默认情况下，从 Aurora PostgreSQL 到 MySQL 的连接使用传输中加密（TLS/SSL）。但是，当客户端和服务器配置不同时，连接会回退为非加密状态。您可以通过在 RDS for MySQL 用户账户上指定 `REQUIRE SSL` 选项来对所有传出连接强制加密。这种方法也适用于 MariaDB 和 Aurora MySQL 用户账户。
对于配置为 `REQUIRE SSL` 的 MySQL 用户帐户，如果无法建立安全连接，则连接尝试将失败。
要对现有 MySQL 数据库用户帐户强制加密，可以使用 `ALTER USER` 命令。根据 MySQL 版本的不同，语法有所不同，如下表所示。有关更多信息，请参阅《MySQL 参考手册》**中的 [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html)。
| MySQL 5.7、MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
有关 `mysql_fdw` 扩展的更多信息，请参阅 [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw) 文档。
# 通过使用 oracle\$1fdw 扩展来使用 Oracle 数据库
要从 Aurora PostgreSQL 数据库集群 访问 Oracle 数据库，您可以安装并使用 `oracle_fdw` 扩展。此扩展是 Oracle 数据库的外部数据包装器。要了解有关此扩展的更多信息，请参阅 [oracle\$1fdw](https://github.com/laurenz/oracle_fdw) 文档。
Aurora PostgreSQL 12.7（Amazon Aurora PostgreSQL 版本 4.2）及更高版本支持 `oracle_fdw` 扩展。
**Topics**
+ [启用 oracle\$1fdw 扩展](#postgresql-oracle-fdw.enabling)
+ [示例：使用链接到 Amazon RDS for Oracle Database 的外部服务器](#postgresql-oracle-fdw.example)
+ [在传输过程中使用加密](#postgresql-oracle-fdw.encryption)
+ [了解 pg\$1user\$1mappings 视图和权限](#postgresql-oracle-fdw.permissions)
## 启用 oracle\$1fdw 扩展
要使用 oracle\$1fdw 扩展，请执行以下步骤。
**启用 oracle\$1fdw 扩展**
+ 使用具有 `rds_superuser` 权限的账户运行以下命令。
```
CREATE EXTENSION oracle_fdw;
```
## 示例：使用链接到 Amazon RDS for Oracle Database 的外部服务器
以下示例展示如何使用链接到 Amazon RDS for Oracle Database 的外部服务器。
**创建链接到 RDS for Oracle 数据库的外部服务器**
1. 请注意 RDS for Oracle 数据库实例上的以下内容：
+ Endpoint
+ 端口
+ 数据库名称
1. 创建外部服务器。
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. 将使用权授予没有 `rds_superuser` 权限的用户，例如 `user1`。
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. 作为 `user1` 连接并创建到 Oracle 用户的映射。
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. 创建链接到 Oracle 表的外部表。
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. 查询外部表。
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
如果查询报告以下错误，请检查您的安全组和访问控制列表（ACL）以确保两个实例可以通信。
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## 在传输过程中使用加密
PostgreSQL-to-Oracle 传输中加密基于客户端和服务器配置参数的组合。有关使用 Oracle 21c 的示例，请参阅 Oracle 文档中的[关于协商加密和完整性的值](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5)。用于 Amazon RDS 上 oracle\$1fdw 的客户端配置有 `ACCEPTED`，这意味着加密取决于 Oracle 数据库服务器配置，并且使用 Oracle 安全库（libnnz）进行加密。
如果您的数据库位于 RDS for Oracle 上，请参阅 [Oracle 本机网络加密](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html)来配置加密。
## 了解 pg\$1user\$1mappings 视图和权限
PostgreSQL 目录 `pg_user_mapping` 存储 Aurora PostgreSQL 用户到外部数据（远程）服务器上用户的映射。虽然对目录的访问受到限制，但是您可以使用 `pg_user_mappings` 视图来查看映射。在下面，您可以找到一个示例，该示例演示如何将权限应用于示例 Oracle 数据库，但此信息更普遍地适用于任何外部数据包装器。
在以下输出中，您可以找到映射到三个不同示例用户的角色和权限。用户 `rdssu1` 和 `rdssu2` 是 `rds_superuser` 角色的成员，而 `user1` 不是。此示例使用 `psql` 元命令 `\du` 列出现有角色。
```
test=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
rdssu1 | | {rds_superuser}
rdssu2 | | {rds_superuser}
user1 | | {}
```
所有用户，包括具有 `rds_superuser` 权限的用户，都可以查看 `pg_user_mappings` 表中他们自己的用户映射 (`umoptions`)。如以下示例所示，当 `rdssu1` 尝试获取所有用户映射时，即使存在 `rdssu1``rds_superuser` 权限，也会出现错误：
```
test=> SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
```
下面是一些示例。
```
test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd}
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd}
(3 rows)
test=> SET SESSION AUTHORIZATION user1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+--------------------------------
16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd}
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
```
由于 `information_schema._pg_user_mappings` 和 `pg_catalog.pg_user_mappings` 的实施差异，手动创建的 `rds_superuser` 需要额外的权限才能在 `pg_catalog.pg_user_mappings` 中查看密码。
`rds_superuser` 无需额外权限即可在 `information_schema._pg_user_mappings` 中查看密码。
没有 `rds_superuser` 角色的用户只能在以下条件下在 `pg_user_mappings` 中查看密码：
+ 当前用户是被映射的用户，拥有服务器或对其具有 `USAGE` 权限。
+ 当前用户是服务器所有者，此映射用于 `PUBLIC`。
# 通过使用 tds\$1fdw 扩展来使用 SQL Server 数据库
您可以使用 PostgreSQL `tds_fdw` 扩展来访问支持表格数据流 (TDS) 协议的数据库，例如 Sybase 和 Microsoft SQL Server 数据库。此外部数据包装器可让您从 Aurora PostgreSQL 数据库集群连接到使用 TDS 协议的数据库，包括 Amazon RDS for Microsoft SQL Server。有关更多信息，请参阅 GitHub 上的 [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) 文档。
Amazon Aurora PostgreSQL 版本 13.6 及更高版本支持该 `tds_fdw` 扩展。
## 将 Aurora PostgreSQL 数据库设置为使用 tds\$1fdw 扩展
在以下过程中，您可以找到设置 `tds_fdw` 并将其与 Aurora PostgreSQL 数据库集群结合使用的示例。在可以使用 `tds_fdw` 连接到 SQL Server 数据库之前，您需要获取实例的以下详细信息：
+ 主机名或终端节点。对于 RDS for SQL Server 数据库实例，您可以使用控制台查找终端节点。选择 Connectivity & security（连接和安全）选项卡，然后查看 Endpoint and port（终端节点和端口）部分。
+ 端口号。Microsoft SQL Server 的默认端口是 1433。
+ 数据库的名称。数据库标识符。
您还需要为 SQL Server 端口 1433 提供对安全组或访问控制列表 (ACL) 的访问权限。Aurora PostgreSQL 数据库集群和 RDS for SQL Server 数据库实例都需要访问端口 1433。如果访问权限配置不正确，当您尝试查询 Microsoft SQL Server 时，会看到以下错误消息：
```
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
```
**使用 tds\$1fdw 连接到 SQL Server 数据库**
1. 使用具有 `rds_superuser` 角色的账户连接到您的 Aurora PostgreSQL 数据库集群的主要实例：
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. 安装 `tds_fdw` 扩展：
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
在 Aurora PostgreSQL 数据库集群上安装扩展后，应设置外部服务器。
**创建外部服务器**
使用具有 `rds_superuser` 权限的账户在 Aurora PostgreSQL 数据库集群上执行这些任务。
1. 在 Aurora PostgreSQL 数据库集群中创建外部服务器：
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing');
CREATE SERVER
```
要访问 SQLServer 端的非 ASCII 数据，请在 Aurora PostgreSQL 数据库集群中使用 character\$1set 选项创建服务器链接：
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing', character_set 'UTF-8');
CREATE SERVER
```
1. 向没有 `rds_superuser` 角色权限的用户授予权限，例如 `user1`：
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. 以 user1 身份进行连接，然后创建到 SQL Server 用户的映射：
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. 创建链接到 SQL Server 表的外部表：
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. 查询外部表：
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
### 使用传输中的加密进行连接
Aurora PostgreSQL 到 SQL Server 的连接使用传输中加密 (TLS/SSL)，具体取决于 SQL Server 数据库配置。如果 SQL Server 未配置为加密，则向 SQL Server 数据库发出请求的 RDS for PostgreSQL 客户端将回退到未加密状态。
您可以通过设置 `rds.force_ssl` 参数对与 RDS for SQL Server 数据库实例的连接进行加密。要了解操作方法，请参阅[强制与数据库实例的连接使用 SSL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing)。有关 RDS for SQL Server 的 SSL/TLS 配置的更多信息，请参阅[将 SSL 与 Microsoft SQL Server 数据库实例结合使用](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html)。