# Amazon RDS for PostgreSQL 的数据库管理员常见任务
数据管理员 (DBA) 在管理 Amazon RDS for PostgreSQL 数据库实例时执行各种任务。如果您是一个已经熟悉 PostgreSQL 的 DBA，那么您需要注意在硬件上运行 PostgreSQL 和 RDS for PostgreSQL 之间的一些重要区别。例如，由于它是托管服务，Amazon RDS 不允许对数据库实例进行 Shell 访问。这意味着您无法直接访问 `pg_hba.conf` 和其他配置文件。对于 RDS for PostgreSQL，通常对本地实例的 PostgreSQL 配置文件所做的更改是针对与 RDS for PostgreSQL 数据库实例关联的自定义数据库参数组做出的。有关更多信息，请参阅 [Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。
您也不能以与访问本地 PostgreSQL 实例相同的方式访问日志文件。要了解有关日志记录的更多信息，请参阅[RDS for PostgreSQL 数据库日志文件](USER_LogAccess.Concepts.PostgreSQL.md)。
再例如，您无法访问 PostgreSQL `superuser` 账户。在 RDS for PostgreSQL 上，`rds_superuser` 角色是权限最高的角色，它在设置时被授予 `postgres`。无论您是熟悉使用本地 PostgreSQL 还是对 RDS for PostgreSQL 完全陌生，我们都建议您了解 `rds_superuser` 角色，以及如何使用角色、用户、组和权限。有关更多信息，请参阅 [了解 PostgreSQL 角色和权限](Appendix.PostgreSQL.CommonDBATasks.Roles.md)。
以下是 RDS for PostgreSQL 的一些数据库管理员常见任务。
**Topics**
+ [
# RDS for PostgreSQL 中支持的排序规则
](PostgreSQL-Collations.md)
+ [
# 了解 PostgreSQL 角色和权限
](Appendix.PostgreSQL.CommonDBATasks.Roles.md)
+ [
# PostgreSQL 中的失效连接处理
](Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.md)
+ [
# 在 Amazon RDS for PostgreSQL 上使用 PostgreSQL autovacuum
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md)
+ [
# 管理 Amazon RDS for PostgreSQL 中的高对象计数
](PostgreSQL.HighObjectCount.md)
+ [
# 管理 Amazon RDS for PostgreSQL 中的 TOAST OID 争用
](Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.md)
+ [
## 使用 RDS for PostgreSQL 支持的日志记录机制
](#Appendix.PostgreSQL.CommonDBATasks.Auditing)
+ [
# 使用 PostgreSQL 管理临时文件
](PostgreSQL.ManagingTempFiles.md)
+ [
## 将用于日志分析的 pgBadger 与 PostgreSQL 结合使用
](#Appendix.PostgreSQL.CommonDBATasks.Badger)
+ [
## 使用 PGSnapper 监控 PostgreSQL
](#Appendix.PostgreSQL.CommonDBATasks.Snapper)
+ [
# 在 RDS for PostgreSQL 中管理自定义强制转换
](PostgreSQL.CustomCasts.md)
+ [
# RDS for PostgreSQL 中并行查询的最佳实践
](PostgreSQL.ParallelQueries.md)
+ [
# 在 RDS for PostgreSQL 数据库实例上使用参数
](Appendix.PostgreSQL.CommonDBATasks.Parameters.md)
# RDS for PostgreSQL 中支持的排序规则
排序规则是一组规则，用于确定如何对存储在数据库中的字符串进行排序和比较。排序规则在计算机系统中起着重要作用，并作为操作系统的一部分包含在其中。当向语言中添加新字符或排序规则发生变化时，排序规则会随着时间的推移而变化。
排序规则库为排序规则定义特定的规则和算法。PostgreSQL 中最常用的排序规则库是 GNU C（Glibc）和 Unicode 国际化组件（ICU）。原定设置情况下，RDS for PostgreSQL 使用 Glibc 排序规则，其中包括适用于多字节字符序列的 Unicode 字符排序顺序。
当您在 RDS for PostgreSQL 中创建新的数据库实例时，它将检查操作系统中是否有可用的排序规则。`CREATE DATABASE` 命令的 PostgreSQL 参数 `LC_COLLATE` 和 `LC_CTYPE` 用于指定排序规则，该排序规则是该数据库中的原定设置排序规则。或者，您也可以在 `CREATE DATABASE` 中使用 `LOCALE` 参数来设置这些参数。这决定了数据库中字符串的原定设置排序规则以及将字符分类为字母、数字或符号的规则。您也可以选择用于列、索引或查询的排序规则。
RDS for PostgreSQL 依赖于操作系统中的 Glibc 库来提供排序规则支持。RDS for PostgreSQL 实例定期使用最新版本的操作系统进行更新。这些更新有时包含较新版本的 Glibc 库。较新版本的 Glibc 很少会更改某些字符的排序顺序或排序规则，这可能会导致数据的排序方式不同或生成无效的索引条目。如果您在更新期间发现用于排序规则的排序顺序有问题，则可能需要重建索引。
为了减少 Glibc 更新可能产生的影响，RDS for PostgreSQL 现在包含一个独立的原定设置排序规则库。这个排序规则库在 RDS for PostgreSQL 14.6、13.9、12.13、11.18、10.23 和更高的次要版本中可用。它与 Glibc 2.26-59.amzn2 兼容，并提供排序顺序稳定性以防止错误的查询结果。
# 了解 PostgreSQL 角色和权限
在使用 AWS 管理控制台 创建 RDS for PostgreSQL 数据库实例时，将同时创建管理员账户。默认情况下，其名称为 `postgres`，如以下屏幕截图所示：
![\[Create database（创建数据库）页面中凭据的默认登录身份是 postgres。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/default-login-identity-apg-rpg.png)
您可以选择其他名称，而不是接受默认值（`postgres`)。如果这样做，您选择的名称必须以字母开头，并且必须介于 1 到 16 个字母数字字符之间。为简单起见，在整个指南中，我们将使用默认值 (`postgres`) 来指代此主用户账户。
如果您使用 `create-db-instance` AWS CLI 而不是 AWS 管理控制台，则可以通过将名称传递给命令中的 `master-username` 参数来创建名称。有关更多信息，请参阅 [创建 Amazon RDS 数据库实例](USER_CreateDBInstance.md)。
无论您是使用 AWS 管理控制台、AWS CLI 还是 Amazon RDS API，也无论您是使用默认 `postgres` 名称还是选择其他名称，这第一个数据库用户账户都是 `rds_superuser` 组的成员并具有 `rds_superuser` 权限。
**Topics**
+ [
# 了解 rds\$1superuser 角色
](Appendix.PostgreSQL.CommonDBATasks.Roles.rds_superuser.md)
+ [
# 控制用户对 PostgreSQL 数据库的访问
](Appendix.PostgreSQL.CommonDBATasks.Access.md)
+ [
# 委托和控制用户密码管理
](Appendix.PostgreSQL.CommonDBATasks.RestrictPasswordMgmt.md)
+ [
# 使用 SCRAM 进行 PostgreSQL 密码加密
](PostgreSQL_Password_Encryption_configuration.md)
# 了解 rds\$1superuser 角色
在 PostgreSQL 中，*角色*可以针对数据库中的各种对象定义一个用户、一个组或一组授予组或用户的特定权限。PostgreSQL 命令 `CREATE USER` 和 `CREATE GROUP` 已替换为更通用的 `CREATE ROLE`，并使用特定属性来区分数据库用户。数据库用户可以被视为具有 LOGIN 权限的角色。
**注意**
仍然可以使用 `CREATE USER` 和 `CREATE GROUP` 命令。有关更多信息，请参阅 PostgreSQL 文档中的[数据库角色](https://www.postgresql.org/docs/current/user-manag.html)。
`postgres` 用户是您的 RDS for PostgreSQL 数据库实例上权限最高的数据库用户。它具有以下 `CREATE ROLE` 语句所定义的特征。
```
CREATE ROLE postgres WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION VALID UNTIL 'infinity'
```
属性 `NOSUPERUSER`、`NOREPLICATION`、`INHERIT` 和 `VALID UNTIL 'infinity'` 是 CREATE ROLE 的默认选项，除非另有说明。
默认情况下，`postgres` 拥有授予 `rds_superuser` 角色的权限以及创建角色和数据库的权限。`rds_superuser` 角色允许 `postgres` 用户执行以下操作：
+ 添加可用于 Amazon RDS 的扩展。有关更多信息，请参阅 [使用 Amazon RDS for PostgreSQL 支持的 PostgreSQL 功能](PostgreSQL.Concepts.General.FeatureSupport.md)
+ 为用户创建角色并向用户授予权限。有关更多信息，请参阅 PostgreSQL 文档中的 [CREATE ROLE](https://www.postgresql.org/docs/current/sql-createrole.html) 和 [GRANT](https://www.postgresql.org/docs/14/sql-grant.html)。
+ 创建数据库。有关更多信息，请参阅 PostgreSQL 文档中的 [CREATE DATABASE](https://www.postgresql.org/docs/14/sql-createdatabase.html)。
+ 将 `rds_superuser` 权限授予没有这些权限的用户角色，并根据需要撤销权限。建议您仅向执行超级用户任务的那些用户授予此角色。换句话说，您可以将此角色授予数据库管理员 (DBA) 或系统管理员。
+ 向没有 `rds_superuser` 角色的数据库用户授予（和撤销）`rds_replication` 角色。
+ 向没有 `rds_superuser` 角色的数据库用户授予（和撤销）`rds_password` 角色。
+ 通过使用 `pg_stat_activity` 视图获取有关所有数据库连接的状态信息。需要时，`rds_superuser` 可以通过使用 `pg_terminate_backend` 或 `pg_cancel_backend` 停止任何连接。
在 `CREATE ROLE postgres...` 语句中，您可以看到 `postgres` 用户角色明确禁止 PostgreSQL `superuser` 权限。RDS for PostgreSQL 是一项托管服务，因此您无法访问主机操作系统，也无法使用 PostgreSQL `superuser` 账户进行连接。许多需要独立 PostgreSQL 上的 `superuser` 访问权限的任务都由 Amazon RDS 自动管理。
有关授权权限的更多信息，请参阅 PostgreSQL 文档中的 [GRANT](http://www.postgresql.org/docs/current/sql-grant.html)。
`rds_superuser` 角色是 中的几个*预定义*角色之一。RDS for PostgreSQL 数据库实例。
**注意**
在 PostgreSQL 13 和更早版本中，*预定义*角色称为*默认*角色。
在下面的列表中，您可以找到为新 自动创建的一些其他预定义角色。RDS for PostgreSQL 数据库实例。无法更改预定义角色及其权限。无法删除、重命名或修改这些预定义角色的权限。此类尝试会导致错误。
+ **rds\$1password** – 可以为数据库用户更改密码和设置密码限制的角色。默认情况下，`rds_superuser` 角色被授予此角色，并且可以将此角色授予数据库用户。有关更多信息，请参阅 [控制用户对 PostgreSQL 数据库的访问控制用户对 PostgreSQL 的访问](Appendix.PostgreSQL.CommonDBATasks.Access.md)。
+ 对于早于 14 的 RDS for PostgreSQL 版本，`rds_password` 角色可以为数据库用户和具有 `rds_superuser` 角色的用户更改密码和设置密码限制。在 RDS for PostgreSQL 14 及更高版本中，`rds_password` 角色只能为数据库用户更改密码和设置密码限制。只有具有 `rds_superuser` 角色的用户才能对具有 `rds_superuser` 角色的其他用户执行这些操作。
+ **rdsadmin** – 具有 `superuser` 权限的管理员将对独立的 PostgreSQL 数据库执行许多管理任务，此角色专为处理这些管理任务而创建。此角色由 RDS for PostgreSQL 在内部用于许多管理任务。
+ **rdstopmgr** – Amazon RDS 在内部用于支持多可用区部署的角色。
+ **rds\$1reserved** – Amazon RDS 在内部用于保留数据库连接的角色。
# 查看角色及其权限
根据您的 PostgreSQL 版本，您可以使用不同的命令在 RDS for PostgreSQL 数据库实例中查看预定义角色及其权限。要查看所有预定义角色，可以连接到 RDS for PostgreSQL 数据库实例，并使用 `psql` 运行以下命令。
**对于 `psql` 15 及更早版本**
连接到 RDS for PostgreSQL 数据库实例，并在 psql 中使用 `\du` 命令：
```
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+------------------------------------------------------
postgres | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
rds_ad | Cannot login | {}
rds_iam | Cannot login | {}
rds_password | Cannot login | {}
rds_replication | Cannot login | {}
rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_password,rds_replication}
rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Password valid until infinity |
```
**对于 `psql` 16 及更高版本**
```
postgres=> \drg+
List of role grants
Role name | Member of | Options | Grantor
---------------+-----------------------------+---------------------+----------
postgres | rds_superuser | INHERIT, SET | rdsadmin
rds_superuser | pg_checkpoint | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | pg_monitor | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | pg_signal_backend | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | pg_use_reserved_connections | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | rds_password | ADMIN, INHERIT, SET | rdsadmin
rds_superuser | rds_replication | ADMIN, INHERIT, SET | rdsadmin
```
要检查没有版本依赖关系的角色成员资格，可以使用以下 SQL 查询：
```
SELECT m.rolname AS "Role name", r.rolname AS "Member of"
FROM pg_catalog.pg_roles m
JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)
LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE m.rolname !~ '^pg_'
ORDER BY 1, 2;
```
在输出中，您可以看到 `rds_superuser` 不是数据库用户角色（无法登录），但它具有许多其他角色的权限。您还可以看到数据库用户 `postgres` 是 `rds_superuser` 角色的成员。如前所述，`postgres` 是 Amazon RDS 控制台的 **Create database**（创建数据库）页面中的默认值。如果您选择了另一个名称，则该名称将显示在角色列表中。
# 控制用户对 PostgreSQL 数据库的访问
PostgreSQL 中的新数据库始终使用数据库 `public` 架构中的一组默认权限创建，允许所有数据库用户和角色创建对象。例如，这些权限使数据库用户能够连接数据库，并在连接后创建临时表格。
为了更好地控制用户对您在 RDS for PostgreSQL 数据库实例上创建的数据库实例的访问，我们建议您撤消这些默认 `public` 权限。撤消后，您可以更精确地为数据库用户授权，如以下过程中所示。
**为新数据库实例设置角色和权限**
假设您正在新创建的 RDS for PostgreSQL 数据库实例上设置数据库，以供几位研究人员使用，他们都需要对数据库的读写访问权限。
1. 使用 `psql`（或 pgAdmin）连接到 RDS for PostgreSQL 数据库实例：
```
psql --host=your-db-instance.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
出现提示时请输入密码。`psql` 客户端会建立连接并显示默认的管理连接数据库 `postgres=>`，作为提示符。
1. 要阻止数据库用户在 `public` 架构中创建对象，执行以下操作：
```
postgres=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
```
1. 接下来，创建一个新数据库实例：
```
postgres=> CREATE DATABASE lab_db;
CREATE DATABASE
```
1. 在这个新数据库上，撤消 `PUBLIC` 架构的所有权限。
```
postgres=> REVOKE ALL ON DATABASE lab_db FROM public;
REVOKE
```
1. 为数据库用户创建角色。
```
postgres=> CREATE ROLE lab_tech;
CREATE ROLE
```
1. 为具有此角色的数据库用户提供连接到数据库的能力。
```
postgres=> GRANT CONNECT ON DATABASE lab_db TO lab_tech;
GRANT
```
1. 向具有 `lab_tech` 角色的所有用户授予对此数据库的所有权限。
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_db TO lab_tech;
GRANT
```
1. 创建数据库用户，如下所示：
```
postgres=> CREATE ROLE lab_user1 LOGIN PASSWORD 'change_me';
CREATE ROLE
postgres=> CREATE ROLE lab_user2 LOGIN PASSWORD 'change_me';
CREATE ROLE
```
1. 向这两个用户授予与 lab\$1tech 角色关联的权限：
```
postgres=> GRANT lab_tech TO lab_user1;
GRANT ROLE
postgres=> GRANT lab_tech TO lab_user2;
GRANT ROLE
```
此时，`lab_user1` 和 `lab_user2` 可以连接到 `lab_db` 数据库。此示例未遵循企业使用的最佳实践，其中可能包括创建多个数据库实例、不同的架构和授予有限权限。有关更多完整信息和其他方案，请参阅[管理 PostgreSQL 用户和角色](https://aws.amazon.com/blogs//database/managing-postgresql-users-and-roles/)。
有关 PostgreSQL 数据库中特权的更多信息，请参阅 PostgreSQL 文档中的 [GRANT](https://www.postgresql.org/docs/current/static/sql-grant.html) 命令。
# 委托和控制用户密码管理
作为 DBA，您可能需要委托用户密码的管理。或者，您可能希望防止数据库用户更改其密码或重新配置密码限制，例如密码生命周期。要确保只有您选择的数据库用户才能更改密码设置，可以启用受限密码管理特征。激活此特征时，只有那些已被授予 `rds_password` 角色的数据库用户可以管理密码。
**注意**
要使用受限密码管理，您的 RDS for PostgreSQL 数据库实例必须运行 PostgreSQL 10.6 或更高版本。
默认情况下，此特征为 `off`，如下所示：
```
postgres=> SHOW rds.restrict_password_commands;
rds.restrict_password_commands
--------------------------------
off
(1 row)
```
要启用此特征，请使用自定义参数组并将 `rds.restrict_password_commands` 的设置更改为 1。一定要重新启动 RDS for PostgreSQL 数据库实例，此设置才能生效。
激活此特征后，以下 SQL 命令需要 `rds_password` 权限：
```
CREATE ROLE myrole WITH PASSWORD 'mypassword';
CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01';
ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01';
ALTER ROLE myrole WITH PASSWORD 'mypassword';
ALTER ROLE myrole VALID UNTIL '2023-01-01';
ALTER ROLE myrole RENAME TO myrole2;
```
如果密码使用 MD5 哈希算法，重命名角色 (`ALTER ROLE myrole RENAME TO newname`) 也会受到限制。
激活此特征后，在没有 `rds_password` 角色权限的情况下尝试这些 SQL 命令中的任何一个都会生成以下错误：
```
ERROR: must be a member of rds_password to alter passwords
```
我们建议您仅将 `rds_password` 授予少数几个仅用于密码管理的角色。如果您将 `rds_password` 权限授予没有 `rds_superuser` 权限的数据库用户，则还需要授他们 `CREATEROLE` 属性。
请确保您验证了密码要求，例如客户端上的过期时间以及所需的复杂性。如果您使用自己的客户端实用程序进行与密码相关的更改，则该实用程序需要是 `rds_password` 的成员并具有 `CREATE ROLE` 权限。
# 使用 SCRAM 进行 PostgreSQL 密码加密
在对密码进行加密时，*加盐质询响应身份验证机制（SCRAM）*是 PostgreSQL 的默认消息摘要（MD5）算法的替代方案。―般认为 SCRAM 身份验证机制比 MD5 更安全。要了解有关这两种不同的密码保护方法的更多信息，请参阅 PostgreSQL 文档中的[密码身份验证](https://www.postgresql.org/docs/14/auth-password.html)。
我们建议您使用 SCRAM 而不是 MD5 作为您的 RDS for PostgreSQL 数据库实例的密码加密方案。这是一种加密质询-响应机制，它使用 scram-sha-256 算法进行密码身份验证和加密。
要支持 SCRAM，您可能需要更新客户端应用程序的库。例如，42.2.0 之前的 JDBC 版本不支持 SCRAM。有关更多信息，请参阅 PostgreSQL JDBC 驱动程序文档中的 [PostgreSQL JDBC 驱动程序](https://jdbc.postgresql.org/changelogs/2018-01-17-42.2.0-release/)。有关其他 PostgreSQL 驱动程序和 SCRAM 支持的列表，请参阅 PostgreSQL 文档中的[驱动程序列表](https://wiki.postgresql.org/wiki/List_of_drivers)。
RDS for PostgreSQL 版本 13.1 和更高版本支持 scram-sha-256。这些版本还可让您将数据库实例配置为需要 SCRAM，如以下过程所述。
## 设置 RDS for PostgreSQL 数据库实例以要求使用 SCRAM
您可以要求 RDS for PostgreSQL 数据库实例仅接受使用 scram-sha-256 算法的密码。
**重要**
对于带有 PostgreSQL 数据库的现有 RDS 代理，如果您将数据库身份验证修改为仅使用 `SCRAM`，代理将在长达 60 秒的时间内不可用。要避免此问题，请执行以下操作之一：
确保数据库同时允许 `SCRAM` 和 `MD5` 身份验证。
要仅使用 `SCRAM` 身份验证，请创建一个新代理，将应用程序流量迁移到新代理，然后删除先前与数据库关联的代理。
在对系统进行更改之前，请务必了解完整的过程，如下所示：
+ 获取有关所有数据库用户的所有角色和密码加密的信息。
+ 仔细检查 RDS for PostgreSQL 数据库实例的参数设置，以了解用于控制密码加密的参数。
+ 如果您的 RDS for PostgreSQL 数据库实例使用默认参数组，您需要创建自定义数据库参数组，然后将其应用到您的 RDS for PostgreSQL 数据库实例，以便您可以在需要时修改参数。如果您的 RDS for PostgreSQL 数据库实例使用自定义参数组，您可以稍后根据需要在此过程中修改必要的参数。
+ 将 `password_encryption` 参数更改为 `scram-sha-256`。
+ 通知所有数据库用户他们需要更新密码。对您的 `postgres` 账户执行相同的操作。使用 scram-sha-256 算法对新密码进行加密和存储。
+ 验证是否使用加密类型对所有密码加密。
+ 如果所有密码都使用 scram-sha-256，您可以将 `rds.accepted_password_auth_method` 参数从 `md5+scram` 更改为 `scram-sha-256`。
**警告**
仅将 `rds.accepted_password_auth_method` 更改为 scram-sha-256 之后，使用 `md5` 加密的密码的任何用户（角色）都将无法连接。
### 做好准备，以要求 RDS for PostgreSQL 数据库实例使用 SCRAM
在对 RDS for PostgreSQL 数据库实例进行任何更改之前，检查所有现有的数据库用户账户。另外，请检查用于密码的加密类型。您可以使用 `rds_tools` 扩展来执行这些任务。要了解哪些 PostgreSQL 版本支持 `rds_tools`，请参阅 [Extension versions for Amazon RDS for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html)。
**获取数据库用户（角色）和密码加密方法的列表**
1. 使用 `psql` 连接到 RDS for PostgreSQL 数据库实例，如下所示。
```
psql --host=db-name.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. 安装 `rds_tools` 扩展。
```
postgres=> CREATE EXTENSION rds_tools;
CREATE EXTENSION
```
1. 获取角色和加密的列表。
```
postgres=> SELECT * FROM
rds_tools.role_password_encryption_type();
```
您将看到类似以下内容的输出。
```
rolname | encryption_type
----------------------+-----------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
lab_tester | md5
user_465 | md5
postgres | md5
(8 rows)
```
### 创建自定义数据库参数组
**注意**
如果您的 RDS for PostgreSQL 数据库实例已使用自定义参数组，您不需要创建新参数组。
有关 Amazon RDS 的参数组的概述，请参阅[在 RDS for PostgreSQL 数据库实例上使用参数](Appendix.PostgreSQL.CommonDBATasks.Parameters.md)。
用于密码的密码加密类型在一个参数（即 `password_encryption`）中设置。 RDS for PostgreSQL 数据库实例允许的加密在另一个参数 `rds.accepted_password_auth_method` 中设置。更改其中任何一个的默认值都要求您创建自定义数据库参数组，然后将其应用到您的实例。
也可以使用 AWS 管理控制台 或 RDS API 创建自定义数据库参数组。有关更多信息，请参阅。
现在可以将自定义参数组与数据库实例关联。
**创建自定义数据库参数组**
1. 使用 CLI 命令 `[create-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-parameter-group.html) ` 创建自定义数据库参数组。此示例使用 `postgres13` 作为此自定义参数组的来源。
对于 Linux、macOS 或 Unix：
```
aws rds create-db-parameter-group --db-parameter-group-name 'docs-lab-scram-passwords' \
--db-parameter-group-family postgres13 --description 'Custom parameter group for SCRAM'
```
对于：Windows
```
aws rds create-db-parameter-group --db-parameter-group-name "docs-lab-scram-passwords" ^
--db-parameter-group-family postgres13 --description "Custom DB parameter group for SCRAM"
```
1. 使用 CLI 命令 `[modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html)` 将此自定义参数组应用于 RDS for PostgreSQL 数据库集群。
对于 Linux、macOS 或 Unix：
```
aws rds modify-db-instance --db-instance-identifier 'your-instance-name' \
--db-parameter-group-name "docs-lab-scram-passwords
```
对于：Windows
```
aws rds modify-db-instance --db-instance-identifier "your-instance-name" ^
--db-parameter-group-name "docs-lab-scram-passwords
```
要将 RDS for PostgreSQL 数据库实例与您的自定义数据库参数组重新同步，您需要重启集群的主实例和所有其他实例。为了尽量减少对用户的影响，请安排在常规维护时段内执行此操作。
### 配置密码加密以使用 SCRAM
RDS for PostgreSQL 数据库实例使用的密码加密机制在数据库参数组的 `password_encryption` 参数中设置。允许的值为未设置、`md5` 或 `scram-sha-256`。默认值取决于 RDS for PostgreSQL 版本，如下所示：
+ RDS for PostgreSQL 14 和更高版本 – 默认为 `scram-sha-256`
+ RDS for PostgreSQL 13 – 默认为 `md5`
使用附加到 RDS for PostgreSQL 数据库实例的自定义数据库参数组，您可以修改密码加密参数的值。
![\[接下来，RDS 控制台显示 RDS for PostgreSQL 的 password_encryption 参数的默认值。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/rpg-pwd-encryption-md5-scram-1.png)
**将密码加密设置更改为 scram-sha-256**
+ 将密码加密的值更改为 scram-sha-256，如下所示。可以立即应用更改，因为参数是动态的，这样，无需重新启动即可使更改生效。
对于 Linux、macOS 或 Unix：
```
aws rds modify-db-parameter-group --db-parameter-group-name \
'docs-lab-scram-passwords' --parameters 'ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate'
```
对于：Windows
```
aws rds modify-db-parameter-group --db-parameter-group-name ^
"docs-lab-scram-passwords" --parameters "ParameterName=password_encryption,ParameterValue=scram-sha-256,ApplyMethod=immediate"
```
### 将用户角色的密码迁移到 SCRAM
您可以将用户角色的密码迁移到 SCRAM，如下所述。
**将数据库用户（角色）密码从 MD5 迁移到 SCRAM**
1. 以管理员用户身份（默认用户名 `postgres`）登录，如下所示。
```
psql --host=db-name.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. 通过使用以下命令，在 RDS for PostgreSQL 数据库实例上检查 `password_encryption` 参数的设置。
```
postgres=> SHOW password_encryption;
password_encryption
---------------------
md5
(1 row)
```
1. 将此参数的值更改为 scram-sha-256。有关更多信息，请参阅 [配置密码加密以使用 SCRAM](#PostgreSQL_Password_Encryption_configuration.configure-password-encryption)。
1. 再次检查该值，以确保它现在已设置为 `scram-sha-256`，如下所示。
```
postgres=> SHOW password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)
```
1. 通知所有数据库用户更改其密码。请确保还要更改您自己的用于 `postgres` 账户的密码（具有 `rds_superuser` 权限的数据库用户）。
```
labdb=> ALTER ROLE postgres WITH LOGIN PASSWORD 'change_me';
ALTER ROLE
```
1. 对于您的 RDS for PostgreSQL 数据库实例上的所有数据库重复此过程。
### 更改参数以要求使用 SCRAM
这是该过程的最后一步。在以下过程中进行更改后，任何仍对密码使用 `md5` 加密的用户账户（角色）都将无法登录到 RDS for PostgreSQL 数据库实例。
`rds.accepted_password_auth_method` 指定 RDS for PostgreSQL 数据库实例在登录过程中接受的用户密码加密方法。默认值为 `md5+scram`，这意味着可以接受任一种方法。在下面的图中，您可以找到此参数的默认设置。
![\[RDS 控制台显示 rds.accepted_password_auth_method 参数的默认值和允许的值。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/pwd-encryption-md5-scram-2.png)
此参数允许的值仅为 `md5+scram` 或 `scram`。如果将此参数值更改为 `scram`，则必须使用这种方法。
**更改参数值以要求对密码进行 SCRAM 身份验证**
1. 验证 RDS for PostgreSQL 数据库实例上所有数据库的所有数据库用户密码是否使用 `scram-sha-256` 进行密码加密。为此，查询 `rds_tools` 以获得角色（用户）和加密类型，如下所示。
```
postgres=> SELECT * FROM rds_tools.role_password_encryption_type();
rolname | encryption_type
----------------------+-----------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
lab_tester | scram-sha-256
user_465 | scram-sha-256
postgres | scram-sha-256
( rows)
```
1. 在您的 RDS for PostgreSQL 数据库实例中的所有数据库实例中重复此查询。
如果所有密码都使用 scram-sha-256，您可以继续操作。
1. 将接受的密码身份验证的值更改为 scram-sha-256，如下所示。
对于 Linux、macOS 或 Unix：
```
aws rds modify-db-parameter-group --db-parameter-group-name 'docs-lab-scram-passwords' \
--parameters 'ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate'
```
对于：Windows
```
aws rds modify-db-parameter-group --db-parameter-group-name "docs-lab-scram-passwords" ^
--parameters "ParameterName=rds.accepted_password_auth_method,ParameterValue=scram,ApplyMethod=immediate"
```
# PostgreSQL 中的失效连接处理
当客户端应用程序已放弃或异常终止，但数据库会话在服务器上仍保持活动状态时，就会出现失效连接。当客户端进程崩溃或意外终止，但没有正确地关闭其数据库连接或取消正在进行的请求时，通常会出现这种情况。
当服务器进程处于空闲状态或试图向客户端发送数据时，PostgreSQL 可以高效地识别和清理失效连接。但是，对于处于空闲状态、等待客户端输入或当前正在运行查询的会话，检测过程颇具挑战性。为了处理这些情况，PostgreSQL 提供了 `tcp_keepalives_*`、`tcp_user_timeout` 和 `client_connection_check_interval` 参数。
**Topics**
+ [
## 了解 TCP keepalive
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.Understanding)
+ [
## RDS for PostgreSQL 中的关键 TCP keepalive 参数
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.Parameters)
+ [
## TCP keepalive 设置的使用案例
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.UseCases)
+ [
## 最佳实践
](#Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.BestPractices)
## 了解 TCP keepalive
TCP Keepalive 是一种协议级机制，有助于保持和验证连接完整性。每个 TCP 连接都保持内核级别的设置，这些设置控制着 keepalive 行为。当 keepalive 计时器到期时，系统会执行以下操作：
+ 发送一个没有数据且设置了 ACK 标志的探测数据包。
+ 根据 TCP/IP 规范，期待来自远程端点的响应。
+ 根据响应或无响应来管理连接状态。
## RDS for PostgreSQL 中的关键 TCP keepalive 参数
| 参数 | 说明 | 默认值 |
| --- |--- |--- |
| tcp\$1keepalives\$1idle | Specifies number of seconds of inactivity before sending keepalive message. | 300 |
| tcp\$1keepalives\$1interval | Specifies number of seconds between retransmissions of unacknowledged keepalive messages. | 30 |
| tcp\$1keepalives\$1count | Maximum lost keepalive messages before declaring connection dead | 2 |
| tcp\$1user\$1timeout | Specifies how long (in Milliseconds) unacknowledged data can remain before forcibly closing the connection. | 0 |
| client\$1connection\$1check\$1interval | Sets the interval (in Milliseconds) for checking client connection status during long-running queries. This ensures quicker detection of closed connections. | 0 |
## TCP keepalive 设置的使用案例
### 使空闲会话保持连接状态
为防止空闲连接因处于非活动状态而被防火墙或路由器终止：
+ 配置 `tcp_keepalives_idle` 定期发送 keepalive 数据包。
### 检测失效连接
要迅速检测失效连接：
+ 调整 `tcp_keepalives_idle`、`tcp_keepalives_interval` 和 `tcp_keepalives_count`。例如，使用 Aurora PostgreSQL 默认值，检测到失效连接大约需要一分钟（2 次探测 × 30 秒）。降低这些值可以加快检测速度。
+ 使用 `tcp_user_timeout` 来指定等待确认的最长时间。
TCP keepalive 设置有助于内核检测失效连接，但在使用套接字之前，PostgreSQL 可能无法执行操作。如果会话正在运行长时间的查询，则可能只有在查询完成后才能检测到失效连接。在 PostgreSQL 14 及更高版本中，`client_connection_check_interval` 可以通过在查询执行期间定期轮询套接字来加快失效连接检测。
## 最佳实践
+ **设置合理的 keepalive 间隔：**调整 `tcp_user_timeout`、`tcp_keepalives_idle`、`tcp_keepalives_count` 和 `tcp_keepalives_interval`，以平衡检测速度与资源用量。
+ **针对环境进行优化：**使设置与网络行为、防火墙策略和会话需求相一致。
+ **利用 PostgreSQL 功能**：在 PostgreSQL 14 及更高版本中使用 `client_connection_check_interval` 来进行高效的连接检查。
# 在 Amazon RDS for PostgreSQL 上使用 PostgreSQL autovacuum
我们强烈建议您使用 Autovacuum 功能来保持 PostgreSQL 数据库实例正常运行。Autovacuum 自动启动 VACUUM 和 ANALYZE 命令。它会检查包含大量插入的、更新的或删除的元组的表。进行此检查后，它会通过从 PostgreSQL 数据库中删除过时的数据或元组来回收存储。
在使用任何默认 PostgreSQL 数据库参数组创建的 RDS for PostgreSQL 数据库实例上，会默认启用 autovacuum。默认情况下，还会设置与 Autovacuum 功能关联的其他配置参数。这些默认值是通用值，因此可以针对特定工作负载优化与 Autovacuum 功能关联的某些参数。
在下文中，您可以了解有关 autovacuum 功能以及如何为 RDS for PostgreSQL 数据库实例优化其部分参数的更多信息。有关高级信息，请参阅[使用 PostgreSQL 的最佳实践](CHAP_BestPractices.md#CHAP_BestPractices.PostgreSQL)。
**Topics**
+ [
## 为 Autovacuum 分配内存
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.WorkMemory)
+ [
## 减少事务 ID 重叠的可能性
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)
+ [
# 确定数据库中的表是否需要 vacuum 操作
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.NeedVacuuming.md)
+ [
# 确定哪些表当前符合 Autovacuum 条件
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.EligibleTables.md)
+ [
# 确定 Autovacuum 当前是否正在运行以及运行时长
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AutovacuumRunning.md)
+ [
# 执行手动 vacuum 冻结
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)
+ [
# 在 Autovacuum 正在运行时重新为表建立索引
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Reindexing.md)
+ [
# 使用大型索引管理 autovacuum
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md)
+ [
# 其他影响 Autovacuum 的参数
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.OtherParms.md)
+ [
# 设置表级别 Autovacuum 参数
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.TableParameters.md)
+ [
# 记录 autovacuum 和 vacuum 活动
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md)
+ [
# 了解对无效数据库使用 autovacuum 的行为
](appendix.postgresql.commondbatasks.autovacuumbehavior.md)
+ [
# 识别并解决 RDS for PostgreSQL 中的积极真空拦截器
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.md)
## 为 Autovacuum 分配内存
影响Autovacuum 性能的最重要参数之一是 [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM) 参数。在 RDS for PostgreSQL 版本 14 及更早版本中，`autovacuum_work_mem` 参数设置为 -1，表示改用 `maintenance_work_mem` 的设置。对于所有其它版本，`autovacuum_work_mem` 由 GREATEST(\$1DBInstanceClassMemory/32768\$1, 65536) 决定。
手动 vacuum 操作始终使用 `maintenance_work_mem` 设置，默认设置为 GREATEST(\$1DBInstanceClassMemory/63963136\$11024\$1, 65536)，也可以使用 `SET` 命令在会话级别对其进行调整，以进行更有针对性的手动 `VACUUM` 操作。
`autovacuum_work_mem` 确定 autovacuum 在对索引执行 vacuum 操作时，用于容纳无效元组 (`pg_stat_all_tables.n_dead_tup`) 的标识符的内存。
当执行计算以确定 `autovacuum_work_mem` 参数的值时，请注意以下事项：
+ 如果将此参数设得过低，则 vacuum 过程可能必须扫描表多次才能完成其工作。此类多次扫描可能会对性能产生负面影响。对于较大的实例，将 `maintenance_work_mem` 或 `autovacuum_work_mem` 设置为至少 1 GB，可以提高对包含大量无效元组的表执行 vacuum 操作的性能。但是，在 PostgreSQL 版本 16 及更早版本中，vacuum 的内存使用量上限为 1 GB，这足以单次处理大约 1.79 亿个无效元组。如果表的无效元组比这更多，vacuum 将需要多次遍历表的索引，从而显著增加所需的时间。从 PostgreSQL 版本 17 开始，没有 1 GB 的限制，自动真空可以使用基数树处理超过 1.79 亿个元组。
元组标识符的大小为 6 个字节。要估算对表的索引执行 vacuum 操作所需的内存，请查询 `pg_stat_all_tables.n_dead_tup` 来找到无效元组的数量，然后将该数字乘以 6，以确定单次对索引执行 vacuum 操作所需的内存。您可以使用以下查询：
```
SELECT
relname AS table_name,
n_dead_tup,
pg_size_pretty(n_dead_tup * 6) AS estimated_memory
FROM
pg_stat_all_tables
WHERE
relname = 'name_of_the_table';
```
+ `autovacuum_work_mem` 参数可与 `autovacuum_max_workers` 参数结合使用。`autovacuum_max_workers` 中的每个工作进程均可使用您分配的内存。如果您有多个小型表，请分配更多的 `autovacuum_max_workers` 和更少的 `autovacuum_work_mem`。如果您拥有大型表（大于 100 GB），请分配更多内存和更少的工作进程。您需要分配有足够的内存才能对最大的表成功完成操作。因此，请确保工作进程和内存的组合等于要分配的总内存。
## 减少事务 ID 重叠的可能性
在一些情况下，与 Autovacuum 相关的参数组设置可能不够积极，无法阻止事务 ID 重叠。为解决此问题，RDS for PostgreSQL 提供了自动调整 Autovacuum 参数值的机制。*适应性 autovacuum* 是 RDS for PostgreSQL 的一项功能。在 PostgreSQL 文档中找到了 [TransactionID 重叠](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND)的详细说明。
默认情况下，动态参数 `rds.adaptive_autovacuum` 设置为 ON 的 RDS for PostgreSQL 实例启用适应性 autovacuum。强烈建议您保持启用此选项。不过，要关闭适应性 Autovacuum 参数优化，请将 `rds.adaptive_autovacuum` 参数设置为 0 或 OFF。
即使在 Amazon RDS 优化 Autovacuum 参数时，仍可能出现事务 ID 重叠。鼓励您为事务 ID 重叠实施 Amazon CloudWatch 警报。有关更多信息，请参阅 AWS 数据库博客上的贴子[在 RDS for PostgreSQL 中为事务 ID 重叠实施预警系统](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/)。
启用适应性 autovacuum 参数优化后，当 CloudWatch 指标 `MaximumUsedTransactionIDs` 达到 `autovacuum_freeze_max_age` 参数值或 500000000 中的较大值时，Amazon RDS 将开始调整 autovacuum 参数。
如果表继续倾向于事务 ID 重叠，则 Amazon RDS 将继续调整 autovacuum 的参数。其中每次调整都会将更多资源专用于 Autovacuum 以避免重叠。Amazon RDS 更新以下与 Autovacuum 相关的参数：
+ [Autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)
+ [ Autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)
+ [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM)
+ [Autovacuum\$1naptime](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-NAPTIME)
仅当新值使 Autovacuum 更积极时，RDS 才会修改这些参数。在数据库实例上的内存中修改参数。不会更改参数组中的值。要查看当前内存中的设置，请使用 PostgreSQL [SHOW](https://www.postgresql.org/docs/current/sql-show.html) SQL 命令。
当 Amazon RDS 修改其中任何 autovacuum 参数时，它会为受影响的数据库实例生成事件。此事件在 AWS 管理控制台 上和通过 Amazon RDS API 显示。在 `MaximumUsedTransactionIDs` CloudWatch 指标返回的值低于阈值后，Amazon RDS 会将内存中与 autovacuum 相关的参数重置回参数组中指定的值。然后，它会生成另一个与此更改对应的事件。
# 确定数据库中的表是否需要 vacuum 操作
您可以使用以下查询显示数据库中解冻事务的数目。数据库的 `datfrozenxid` 行的 `pg_database` 列是显示在该数据库中的正常事务 ID 的下限。此列是数据库中每个表的 `relfrozenxid` 值的最小值。
```
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;
```
例如，运行上述查询的结果可能如下所示。
```
datname | age
mydb | 1771757888
template0 | 1721757888
template1 | 1721757888
rdsadmin | 1694008527
postgres | 1693881061
(5 rows)
```
当数据库的期限达到 20 亿个事务 ID 时，事务 ID (XID) 重叠将出现，并且数据库将变成只读状态。您可以使用此查询来生成指标，并且一天可运行几次。默认情况下，将设置 Autovacuum 以确保事务期限不超过 200000000 ()。[https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)
示例监控策略可能类似于：
+ 将 `autovacuum_freeze_max_age` 值设置为 2 亿个事务。
+ 如果表中的解冻事务达到 5 亿个，则会触发低严重性警报。这不是一个不合理的值，但它可能指示 Autovacuum 未保持同步。
+ 如果表期限为 10 亿，这应被视为要采取操作的警报。通常，您出于性能原因，需要使期限更接近 `autovacuum_freeze_max_age`。建议您使用以下建议进行调查。
+ 如果表达到 15 亿个未执行 vacuum 操作的事务，则这会触发高严重性警报。根据数据库使用事务 ID 的频率，此警报将指示系统运行 Autovacuum 的时间不多了。在这种情况下，建议您立即解决此问题。
如果表持续违反这些阈值，请进一步修改 autovacuum 参数。默认情况下，手动使用 VACUUM（已禁用基于成本的延迟）比使用默认的 Autovacuum 更积极，但对整个系统来说也更具侵入性。
我们建议执行下列操作：
+ 了解和启用监控机制，以便您了解最早的事务的期限。
有关创建提醒您事务 ID 重叠的过程的信息，请参阅 AWS 数据库博客帖子 [Implement an early warning system for transaction ID wraparound in Amazon RDS for PostgreSQL](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/)。
+ 对于更复杂的表，在维护时段内定期执行手动 vacuum 冻结操作，并依赖 Autovacuum。有关执行手动 vacuum 冻结的信息，请参阅[执行手动 vacuum 冻结](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)。
# 确定哪些表当前符合 Autovacuum 条件
通常，它是需要执行 vacuum 操作的一个或两个表。其 `relfrozenxid` 值大于 `autovacuum_freeze_max_age` 中的事务数的表始终是 Autovacuum 的目标。否则，如果元组数因上一个 VACUUM 超出 vacuum 阈值而变得过时，则对表执行 vacuum 操作。
[Autovacuum 阈值](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM)的定义如下：
```
Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples
```
其中，`vacuum base threshold` 为 `autovacuum_vacuum_threshold`，`vacuum scale factor` 为 `autovacuum_vacuum_scale_factor`，而 `number of tuples` 为 `pg_class.reltuples`。
在连接到数据库时，运行以下查询可查看 autovacuum 认为有资格执行 vacuum 操作的表的列表。
```
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM
pg_settings WHERE name = 'autovacuum_vacuum_threshold'),
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'),
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'),
sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt)
SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
age(relfrozenxid) as xid_age,
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples)
AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM
pg_class c join pg_namespace ns on ns.oid = c.relnamespace
join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) *
c.reltuples <= n_dead_tup)
ORDER BY age(relfrozenxid) DESC LIMIT 50;
```
# 确定 Autovacuum 当前是否正在运行以及运行时长
如果需要手动对表执行 vacuum 操作，确保确定 autovacuum 当前是否正在运行。如果它当前正在运行，则您可能需要调整参数以使其更高效地运行，或者暂时禁用 autovacuum 以便手动运行 VACUUM。
使用以下查询来确定 Autovacuum 是否正在运行、它已运行的时长以及它是否正在等待其他会话。
```
SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
ORDER BY xact_start;
```
在运行查询后，您应看到类似以下内容的输出。
```
datname | usename | pid | state | wait_event | xact_runtime | query
--------+----------+-------+--------+------------+-------------------------+--------------------------------------------------------------------------------------------------------
mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound)
mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound)
mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3
mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+
| | | | | | FROM pg_stat_activity +
| | | | | | WHERE query like '%VACUUM%' +
| | | | | | ORDER BY xact_start; +
```
有多个问题可能会导致长时间运行 Autovacuum 会话（即，多天）。最常见的问题是，对于表的大小或更新速率来说，设置的 [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) 参数值太小。
建议您使用以下公式来设置 `maintenance_work_mem` 参数值。
```
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
```
短时间运行的 Autovacuum 会话还可以指示以下问题：
+ 它可以指示，对于工作负载而言，`autovacuum_max_workers` 不足。在这种情况下，您将需要指示工作线程数。
+ 它可以指示存在索引损坏（autovacuum 将发生崩溃并在同一关系上重新启动，但毫无进展）。在这种情况下，运行手动 `vacuum freeze verbose table` 以查看准确原因。
# 执行手动 vacuum 冻结
您可能需要对已具有正在运行的 vacuum 进程的表执行手动 vacuum 操作。如果您已使用接近 20 亿个事务（或高于您监控的任何阈值）的期限标识表，则这会很有用。
以下步骤是指导原则，此过程存在几种变化。例如，在测试期间，假设您发现设定的 [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) 参数值过小，并且您需要立即对表采取措施。不过，可能您不希望此时恢复实例。通过使用前几节中的查询，您可以确定哪个表存在问题，并找到长时间运行的 Autovacuum 会话。您知道您需要更改 `maintenance_work_mem` 参数设置，但您还需要立即采取行动，对有问题的表执行 vacuum 操作。以下过程说明了在此情况下应采取的措施。
**手动执行 vacuum 冻结**
1. 打开针对包含要执行 vacuum 操作的表的数据库的两个会话。对于第二个会话，使用“screen”或其他维护会话的实用工具 (如果您的连接已中断)。
1. 在第一个会话中，获取正在表上运行的 autovacuum 会话的进程 ID (PID)。
运行以下查询可获取 Autovacuum 会话的 PID。
```
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
```
1. 在第二个会话中，计算该操作所需的内存量。在此示例中，我们确定自己最多可以为该操作使用 2GB 的内存，因此，我们将当前会话的 [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) 设置为 2 GB。
```
SET maintenance_work_mem='2 GB';
SET
```
1. 在第二个会话中，为表发出 `vacuum freeze verbose` 命令。详细设置很有用，因为虽然 PostgreSQL 中当前没有进度报告，但您可以查看活动。
```
\timing on
Timing is on.
vacuum freeze verbose pgbench_branches;
```
```
INFO: vacuuming "public.pgbench_branches"
INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions
in 43 out of 43 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 9347 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 2.765 ms
```
1. 在第一个会话中，如果 autovacuum 阻止 vacuum 会话，`pg_stat_activity` 显示 vacuum 会话的等待为 `T`。在此情况下，您需要终止 autovacuum 进程，如下所示。
```
SELECT pg_terminate_backend('the_pid');
```
**注意**
某些较低版本的 Amazon RDS 无法使用上述命令终止 autovacuum 进程，并且会失败，显示以下错误：`ERROR: 42501: must be a superuser to terminate superuser process LOCATION: pg_terminate_backend, signalfuncs.c:227`。
此时，您的会话将开始。由于此表可能位于其工作列表中的最高位置，因此 Autovacuum 将立即重新启动。
1. 在第二个会话中启动您的 `vacuum freeze verbose` 命令，然后终止第一个会话中的 autovacuum 过程。
# 在 Autovacuum 正在运行时重新为表建立索引
如果索引已损坏，Autovacuum 将继续处理表并失败。在此情况下，如果您尝试执行手动 vacuum 操作，您将收到一条与以下内容类似的错误消息。
```
postgres=> vacuum freeze pgbench_branches;
ERROR: index "pgbench_branches_test_index" contains unexpected
zero page at block 30521
HINT: Please REINDEX it.
```
当索引损坏并且 autovacuum 尝试在表上运行时，您将处理已经正在运行的 autovacuum 会话。在您发出 [REINDEX](https://www.postgresql.org/docs/current/static/sql-reindex.html) 命令时，将删除表上的排他锁。将阻止写入操作以及使用该特定索引的读取操作。
**在对表运行 Autovacuum 时重新为表建立索引**
1. 打开针对包含要执行 vacuum 操作的表的数据库的两个会话。对于第二个会话，使用“screen”或其他维护会话的实用工具 (如果您的连接已中断)。
1. 在第一个会话中，获取正在表上运行的 Autovacuum 会话的 PID。
运行以下查询可获取 Autovacuum 会话的 PID。
```
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY
xact_start;
```
1. 在第二个会话中，发出 reindex 命令。
```
\timing on
Timing is on.
reindex index pgbench_branches_test_index;
REINDEX
Time: 9.966 ms
```
1. 在第一个会话中，如果 autovacuum 阻止该过程，您将在 `pg_stat_activity` 中看到 vacuum 会话的等待为“T”。在此情况下，您将终止 autovacuum 过程。
```
SELECT pg_terminate_backend('the_pid');
```
此时，您的会话将开始。由于此表可能位于其工作列表中的最高位置，因此，了解 Autovacuum 将立即重新启动很重要。
1. 在第二个会话中启动您的命令，然后终止第一个会话中的 autovacuum 过程。
# 使用大型索引管理 autovacuum
作为其操作的一部分，在对表运行 *autovacuum* 时会执行多个 [vacuum 阶段](https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES)。在清理表之前，首先对所有索引执行 vacuum 操作。删除多个大型索引时，此阶段会消耗大量的时间和资源。因此，作为最佳实践，请务必控制表上的索引数量并消除未使用的索引。
对于此过程，请先检查总体索引大小。然后，确定是否存在可以删除的潜在未使用索引，如以下示例所示。
**检查表及其索引的大小**
```
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
6404 MB
(1 row)
```
```
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));
pg_size_pretty
11 GB
(1 row)
```
在此示例中，索引的大小大于表的大小。这种差异可能会导致性能问题，因为索引膨胀或未使用，这会影响 autovacuum 以及插入操作。
**检查是否有未使用的索引**
使用 [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW) 视图，您可以检查 `idx_scan` 列使用索引的频率。在以下示例中，未使用索引的 `idx_scan` 值为 `0`。
```
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------------+-----------------------+----------+--------------+---------------
16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0
16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0
16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0
16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0
16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0
16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0
16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0
(7 rows)
```
```
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
schemaname | relname | indexrelname | idx_scan
------------+------------------+-----------------------+----------
public | pgbench_accounts | index_f | 6
public | pgbench_accounts | index_b | 3
public | pgbench_accounts | pgbench_accounts_pkey | 0
public | pgbench_accounts | index_d | 0
public | pgbench_accounts | index_e | 0
public | pgbench_accounts | index_c | 0
public | pgbench_accounts | index_a | 0
(7 rows)
```
**注意**
这些统计数据自统计数据重置之时起开始递增。假设您的索引仅用于业务季度末或仅用于特定报告。自从统计数据重置后，此索引可能就没有使用过。有关更多信息，请参阅[统计数据函数](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS)。用于强制唯一性的索引不会执行扫描，也不应被标识为未使用的索引。要识别未使用的索引，您应该对应用程序及其查询有深入的了解。
要检查数据库上次重置统计数据的时间，请使用 [ https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW]( https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW)
```
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';
datname | stats_reset
----------+-------------------------------
postgres | 2022-11-17 08:58:11.427224+00
(1 row)
```
## 尽快对表执行 vacuum 操作
**RDS for PostgreSQL 12 及更高版本**
如果大型表中的索引过多，则您的数据库实例可能接近事务 ID 重叠（XID），也就是 XID 计数器变为零时。如果不进行检查，这种情况可能导致数据丢失。但是，您可以在不清理索引的情况下快速对表执行 vacuum 操作。在 RDS for PostgreSQL 12 及更高版本中，可以将 VACUUM 与 [https://www.postgresql.org/docs/current/sql-vacuum.html](https://www.postgresql.org/docs/current/sql-vacuum.html) 子句结合使用。
```
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;
INFO: vacuuming "public.pgbench_accounts"
INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
```
如果 autovacuum 会话已在运行，则必须将其终止才能开始手动 VACUUM。有关执行手动 vacuum 冻结的信息，请参阅[执行手动 vacuum 冻结](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)。
**注意**
定期跳过索引清理会导致索引膨胀，这会降低扫描性能。索引保留死行，而表保留死行指针。因此，`pg_stat_all_tables.n_dead_tup` 会一直增加，直到 autovacuum 或带有索引清理功能的手动 VACUUM 运行。作为最佳实践，请仅使用此过程来防止事务 ID 重叠。
**RDS for PostgreSQL 11 及更低版本**
但是，在 RDS for PostgreSQL 11 及更低版本中，让 vacuum 过程更快地完成的唯一方法是减少表上的索引数量。删除索引可能会影响查询计划。我们建议您先删除未使用的索引，然后在 XID 重叠非常接近时删除索引。vacuum 过程完成后，您可以重新创建这些索引。
# 其他影响 Autovacuum 的参数
以下查询将显示直接影响 Autovacuum 及其行为的一些参数的值。PostgreSQL 文档中完整介绍了 [Autovacuum 参数](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html)。
```
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'autovacuum_max_workers',
'autovacuum_analyze_scale_factor',
'autovacuum_naptime',
'autovacuum_analyze_threshold',
'autovacuum_analyze_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_cost_delay',
'autovacuum_vacuum_cost_limit',
'vacuum_cost_limit',
'autovacuum_freeze_max_age',
'maintenance_work_mem',
'vacuum_freeze_min_age');
```
所有这些参数都会影响 Autovacuum，其中一些最重要的参数为：
+ [maintenance\$1work\$1mem](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE_WORK_MEM)
+ [Autovacuum\$1freeze\$1max\$1age](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)
+ [Autovacuum\$1max\$1workers](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS)
+ [Autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)
+ [ Autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)
# 设置表级别 Autovacuum 参数
您可以在表级别设置与 autovacuum 相关的[存储参数](https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS)，这可能优于更改整个数据库的行为。对于大型表，您可能需要设置主动设置，并且可能不希望 Autovacuum 对所有表的行为都相同。
以下查询将显示哪些表当前拥有表级别选项。
```
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT null;
```
例如，对于比您的其他表大得多的表，这可能会很有用。假设您具有一个 300GB 表和另外 30 个小于 1GB 的表。在这种情况下，可以为大型表设置一些特定的参数，这样便无需更改整个系统的行为。
```
ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);
```
这样便可通过使用系统中的更多资源来禁用此表的基于成本的 autovacuum 延迟。通常，每次达到 `autovacuum_cost_limit` 时，autovacuum 会暂停 `autovacuum_vacuum_cost_delay`。有关更多详细信息，请参阅 PostgreSQL 文档中有关[基于成本的 vacuum 操作](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST)的内容。
# 记录 autovacuum 和 vacuum 活动
根据 `rds.force_autovacuum_logging_level` 参数中指定的级别，有关 autovacuum 活动的信息将发送到 `postgresql.log`。以下是此参数允许的值以及该值为默认设置的 PostgreSQL 版本：
+ `disabled`（PostgreSQL 10、PostgreSQL 9.6）
+ `debug5`, `debug4`, `debug3`, `debug2`, `debug1`
+ `info`（PostgreSQL 12、PostgreSQL 11）
+ `notice`
+ `warning`（PostgreSQL 13 及更高版本）
+ `error`、日志、`fatal`、`panic`
`rds.force_autovacuum_logging_level` 与 `log_autovacuum_min_duration` 参数结合使用。`log_autovacuum_min_duration` 参数的值为阈值（以毫秒为单位），超出该值后将记录 autovacuum 操作。设置为 `-1` 不会记录任何内容，而设置 0 将记录所有操作。和 `rds.force_autovacuum_logging_level` 一样，`log_autovacuum_min_duration` 的默认值取决于版本，如下所示：
+ `10000 ms` - PostgreSQL 14、PostgreSQL 13、PostgreSQL 12 和 PostgreSQL 11
+ `(empty)` - PostgreSQL 10 和 PostgreSQL 9.6 没有默认值
建议您将 `rds.force_autovacuum_logging_level` 设置为 `WARNING`。我们还建议您将 `log_autovacuum_min_duration` 设置为 1000 到 5000 之间的值。设置为 5000 的记录活动耗时将超过 5000 毫秒。如果由于冲突锁定或并行删除关系跳过了 autovacuum 操作，则并非 -1 的任何其他设置也会记录消息。有关更多信息，请参阅 PostgreSQL 文档中的 [Automatic Vacuuming](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html)。
要对问题进行故障排除，可以将 `rds.force_autovacuum_logging_level` 参数更改为调试级别之一，从 `debug1` 到最高 `debug5` 以获取最详细的信息。我们建议您在短时间内使用调试设置，并且仅用于故障排除目的。要了解更多信息，请参阅 PostgreSQL 文档中的[何时记录](https://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN)。
**注意**
PostgreSQL 允许 `rds_superuser` 账户查看 `pg_stat_activity` 中的 Autovacuum 会话。例如，您可识别并结束阻止命令运行或运行速度慢于手动发出的 vacuum 命令的 Autovacuum 会话。
# 了解对无效数据库使用 autovacuum 的行为
在 `pg_database` 目录的 `datconnlimit` 列中引入了一个新值 `-2`，来指示在 DROP DATABASE 操作过程中已被中断的数据库为无效的。
以下 RDS for PostgreSQL 版本中提供了这个新值：
+ 15.4 及所有更高版本
+ 14.9 及更高版本
+ 13.12 及更高版本
+ 12.16 及更高版本
+ 11.21 及更高版本
无效的数据库不会影响 autovacuum 冻结有效数据库的功能的能力。Autovacuum 会忽略无效的数据库。因此，对于 PostgreSQL 环境中的所有有效数据库，常规 vacuum 操作将继续正常且高效地运行。
**Topics**
+ [
## 监控事务 ID
](#appendix.postgresql.commondbatasks.autovacuum.monitorxid)
+ [
## 调整监控查询
](#appendix.postgresql.commondbatasks.autovacuum.monitoradjust)
+ [
## 解决无效的数据库问题
](#appendix.postgresql.commondbatasks.autovacuum.connissue)
## 监控事务 ID
`age(datfrozenxid)` 函数常用于监控数据库的事务 ID（XID）期限，以防止事务 ID 重叠。
由于无效数据库被排除在 autovacuum 之外，因此其事务 ID（XID）计数器可以达到最大值 `2 billion`，重叠到 `- 2 billion`，并无限期地继续此循环。监控事务 ID 重叠的典型查询可能如下所示：
```
SELECT max(age(datfrozenxid)) FROM pg_database;
```
但是，在为 `datconnlimit` 引入 -2 值后，无效的数据库可能会扭曲此查询的结果。由于这些数据库无效，而不应作为定期维护检查的一部分，因此它们可能会导致误报，致使您认为 `age(datfrozenxid)` 高于实际值。
## 调整监控查询
为确保准确的监控，应调整监控查询来排除无效的数据库。请遵循这一建议的查询：
```
SELECT
max(age(datfrozenxid))
FROM
pg_database
WHERE
datconnlimit <> -2;
```
此查询可确保在 `age(datfrozenxid)` 计算中只考虑有效的数据库，从而真实地反映整个 PostgreSQL 环境中的事务 ID 期限。
## 解决无效的数据库问题
当尝试连接到无效的数据库时，可能会遇到类似于以下内容的错误消息：
```
postgres=> \c db1
connection to server at "mydb.xxxxxxxxxx.us-west-2.rds.amazonaws.com" (xx.xx.xx.xxx), port xxxx failed: FATAL: cannot connect to invalid database "db1"
HINT: Use DROP DATABASE to drop invalid databases.
Previous connection kept
```
此外，如果 `log_min_messages` 参数设置为 `DEBUG2` 或更高，您可能会注意到以下日志条目，表明 autovacuum 进程正在跳过无效的数据库：
```
2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db6"
2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db1"
```
要解决此问题，请按照尝试连接期间提供的 `HINT` 进行操作。使用 RDS 主账户或具有 `rds_superuser` 角色的数据库账户连接到任何有效的数据库，然后删除无效的数据库。
```
SELECT
'DROP DATABASE ' || quote_ident(datname) || ';'
FROM
pg_database
WHERE
datconnlimit = -2 \gexec
```
# 识别并解决 RDS for PostgreSQL 中的积极真空拦截器
在 PostgreSQL 中，清理对于确保数据库良好运行状况至关重要，因为它可以回收存储并防止出现[事务 ID 重叠](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND)问题。但是，有时可能会阻止真空清理操作按预期运行，这样可能会导致性能下降、存储膨胀，甚至会因事务 ID 重叠而影响您的数据库实例的可用性。因此，识别和解决这些问题对于实现最佳数据库性能和可用性具有至关重要的意义。阅读[了解 Amazon RDS for PostgreSQL 环境中的自动真空功能](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/)，以了解有关自动真空的更多信息。
`postgres_get_av_diag()` 函数可用于识别会阻止或延迟积极 vacuum 进度的问题。提供了建议，其中可能包括用于解决可识别问题的命令，或者在无法识别问题时提供进一步诊断的指导。当龄期超过 RDS 的 5 亿个事务 ID 的[自适应自动真空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)阈值时，将报告积极真空拦截器。
**事务 ID 的龄期是多少？**
事务 ID 的 `age()` 函数会计算自数据库 (`pg_database.datfrozenxid`) 或表 (`pg_class.relfrozenxid`) 的最早解冻事务 ID 以来发生的事务数。此值表示自上次积极真空操作以来的数据库活动，并突出显示即将到来的真空进程可能出现的工作负载。
**什么是积极真空？**
积极真空操作会对表中的所有页面进行全面扫描，包括在常规真空期间通常跳过的页面。这种彻底的扫描旨在“冻结”接近其最大龄期的事务 ID，从而有效地防止出现被称为[事务 ID 重叠](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND)的情况。
要使 `postgres_get_av_diag()` 报告拦截器，拦截器必须至少有 5 亿个事务。
**Topics**
+ [
# 在 RDS for PostgreSQL 中安装 autovacuum 监控和诊断工具
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md)
+ [
# RDS for PostgreSQL 中的 postgres\$1get\$1av\$1diag() 函数
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.md)
+ [
# 在 RDS for PostgreSQL 中解决可识别的真空拦截器
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md)
+ [
# 在 RDS for PostgreSQL 中解除无法识别的真空拦截器
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Unidentifiable_blockers.md)
+ [
# 解决 RDS for PostgreSQL 中的真空性能问题
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md)
+ [
# RDS for PostgreSQL 中的通知消息说明
](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)
# 在 RDS for PostgreSQL 中安装 autovacuum 监控和诊断工具
`postgres_get_av_diag()` 函数目前在以下 RDS for PostgreSQL 版本中可用：
+ 17.2 及更高的 17 版本
+ 16.7 及更高的 16 版本
+ 15.11 及更高的 15 版本
+ 14.16 及更高的 14 版本
+ 13.19 及更高的 13 版本
要使用 `postgres_get_av_diag()`，请创建 `rds_tools` 扩展。
```
postgres=> CREATE EXTENSION rds_tools ;
CREATE EXTENSION
```
验证是否已安装扩展。
```
postgres=> \dx rds_tools
List of installed extensions
Name | Version | Schema | Description
----------+---------+-----------+----------------------------------------------------------
rds_tools | 1.8 | rds_tools | miscellaneous administrative functions for RDS PostgreSQL
1 row
```
验证是否已创建函数。
```
postgres=> SELECT
proname function_name,
pronamespace::regnamespace function_schema,
proowner::regrole function_owner
FROM
pg_proc
WHERE
proname = 'postgres_get_av_diag';
function_name | function_schema | function_owner
----------------------+-----------------+----------------
postgres_get_av_diag | rds_tools | rds_superuser
(1 row)
```
# RDS for PostgreSQL 中的 postgres\$1get\$1av\$1diag() 函数
`postgres_get_av_diag()` 函数会检索有关 RDS for PostgreSQL 数据库中阻止或滞后的 autovacuum 进程的诊断信息。为了获得准确的结果，需要在具有最早事务 ID 的数据库中执行查询。有关使用具有最早事务 ID 的数据库的更多信息，请参阅[未连接到具有最早事务 ID 龄期的数据库](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)
```
SELECT
blocker,
DATABASE,
blocker_identifier,
wait_event,
TO_CHAR(autovacuum_lagging_by, 'FM9,999,999,999') AS autovacuum_lagging_by,
suggestion,
suggested_action
FROM (
SELECT
*
FROM
rds_tools.postgres_get_av_diag ()
ORDER BY
autovacuum_lagging_by DESC) q;
```
`postgres_get_av_diag()` 函数将返回一个包含以下信息的表：
**blocker**
指定阻止真空的数据库活动的类别。
+ [活动语句](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement)
+ [事务中空闲](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction)
+ [准备好的事务](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction)
+ [逻辑复制槽](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot)
+ [带有物理复制槽的只读副本](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [使用流式复制的只读副本](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [临时表](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables)
**database**
在适用和支持的情况下指定数据库的名称。这是其中活动正在进行并正在阻止或将阻止自动真空的数据库。这是您需要连接并执行操作的数据库。
**blocker\$1identifier**
指定正在阻止或将阻止自动真空的活动的标识符。该标识符可以是进程 ID 以及 SQL 语句、准备好的事务、只读副本的 IP 地址以及复制槽的名称（逻辑或物理）。
**wait\$1event**
指定阻止会话的[等待事件](PostgreSQL.Tuning.md)，适用于以下拦截器：
+ 活动语句
+ 事务中空闲
**autovacum\$1lagging\$1by**
指定自动真空在每个类别的积压工作中滞后的事务数。
**suggestion**
指定解除拦截器的建议。这些说明包括活动所在的数据库的名称（如果适用）、会话的进程 ID（PID）（如果适用）以及要执行的操作。
**suggested\$1action**
建议需要执行哪些操作来解除拦截器。
# 在 RDS for PostgreSQL 中解决可识别的真空拦截器
自动真空会执行积极真空操作，并将事务 ID 的龄期降低到低于由 RDS 实例的 `autovacuum_freeze_max_age` 参数指定的阈值。您可以使用 Amazon CloudWatch 指标 `MaximumUsedTransactionIDs` 跟踪此龄期。
要查找 Amazon RDS 实例的 `autovacuum_freeze_max_age` 设置（默认为 2 亿个事务 ID），您可以使用以下查询：
```
SELECT
TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
pg_settings
WHERE
name = 'autovacuum_freeze_max_age';
```
请注意，`postgres_get_av_diag()` 仅在龄期超过 Amazon RDS 的 5 亿个事务 ID 的[自适应自动真空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)阈值时检查积极真空拦截器。要使 `postgres_get_av_diag()` 检测拦截器，拦截器必须至少有 5 亿个事务。
`postgres_get_av_diag()` 函数可识别以下类型的拦截器：
**Topics**
+ [
## 活动语句
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement)
+ [
## 事务中空闲
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction)
+ [
## 准备好的事务
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction)
+ [
## 逻辑复制槽
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot)
+ [
## 只读副本
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [
## 临时表
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables)
## 活动语句
在 PostgreSQL 中，活动语句是数据库当前正在执行的 SQL 语句。这包括查询、事务或任何正在进行的操作。通过 `pg_stat_activity` 进行监控时，状态列指示具有相应 PID 的进程处于活动状态。
`postgres_get_av_diag()` 函数在识别系活动语句的语句时会显示类似于以下内容的输出。
```
blocker | Active statement
database | my_database
blocker_identifier | SELECT pg_sleep(20000);
wait_event | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"SELECT pg_terminate_backend (29621);"}
```
**建议采取的措施**
按照 `suggestion` 列中的指导，用户可以连接到存在活动语句的数据库，并按照 `suggested_action` 列中的指定，建议仔细查看终止会话的选项。如果终止是安全的，则可以使用 `pg_terminate_backend()` 函数终止会话。此操作可由管理员（如 RDS 主账户）或具有所需 `pg_terminate_backend()` 权限的用户执行。
**警告**
已终止的会话将撤消 (`ROLLBACK`) 其所做的更改。根据您的要求，您可能需要重新运行相应语句。但是，建议只有在自动真空过程完成其积极真空操作之后才这样做。
## 事务中空闲
事务中空闲语句是指已打开显式事务（例如通过发出 `BEGIN` 语句）、执行了一些工作，并且现在正在等待客户端传递更多工作或通过发出 `COMMIT`、`ROLLBACK` 或 `END`（这将导致隐式 `COMMIT`）来发出事务结束信号的任何会话。
`postgres_get_av_diag()` 函数在将 `idle in transaction` 语句识别为拦截器时，显示类似于以下内容的输出。
```
blocker | idle in transaction
database | my_database
blocker_identifier | INSERT INTO tt SELECT * FROM tt;
wait_event | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"SELECT pg_terminate_backend (28438);"}
```
**建议采取的措施**
如 `suggestion` 列中所示，您可以连接到存在事务中空闲会话的数据库，并使用 `pg_terminate_backend()` 函数终止会话。该用户可以是您的管理员（RDS 主账户）用户或具有 `pg_terminate_backend()` 权限的用户。
**警告**
已终止的会话将撤消 (`ROLLBACK`) 其所做的更改。根据您的要求，您可能需要重新运行相应语句。但是，建议只有在自动真空过程完成其积极真空操作之后才这样做。
## 准备好的事务
PostgreSQL 允许属于称为[准备好的事务](https://www.postgresql.org/docs/current/sql-prepare-transaction.html)的两阶段提交策略一部分的事务。通过将 `max_prepared_transactions` 参数设置为非零值即可启用这些功能。准备好的事务旨在确保事务的持久性，即使在数据库崩溃、重启或客户端断开连接后仍可用。与定期事务一样，它们会被分配一个事务 ID，并且可能会影响自动真空。如果保持已准备状态，则自动真空将无法执行冻结，这样可能会导致事务 ID 重叠。
当事务被无限期地准备而没有被事务管理器解决时，它们将成为孤立的已准备事务。解决此问题的唯一方法是分别使用 `COMMIT PREPARED` 或 `ROLLBACK PREPARED` 命令提交或回滚事务。
**注意**
请注意，在准备事务期间进行的备份在还原后仍将包含该事务。有关如何查找和关闭此类事务的信息，请参阅以下信息。
`postgres_get_av_diag()` 函数在识别出系已准备事务的拦截器时显示以下输出。
```
blocker | Prepared transaction
database | my_database
blocker_identifier | myptx
wait_event | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}
```
**建议采取的措施**
如建议列中所述，连接到已准备事务所在的数据库。根据 `suggested_action` 列，仔细查看是执行 `COMMIT` 还是 `ROLLBACK`，并执行适当的操作。
为了总体上监控已准备事务，PostgreSQL 提供了一个名为 `pg_prepared_xacts` 的目录视图。您可以使用以下查询来查找已准备事务。
```
SELECT
gid,
prepared,
owner,
database,
transaction AS oldest_xmin
FROM
pg_prepared_xacts
ORDER BY
age(transaction) DESC;
```
## 逻辑复制槽
复制槽的用途是保存未使用的更改，直到它们被复制到目标服务器。有关更多信息，请参阅 PostgreSQL 的 [Logical replication](https://www.postgresql.org/docs/current/logical-replication.html)。
有两种类型的逻辑复制槽。
**非活动逻辑复制槽**
复制终止后，无法删除未使用的事务日志，且复制槽变为非活动状态。尽管订阅用户当前未使用非活动逻辑复制槽，但它仍保留在服务器上，从而保留 WAL 文件并防止删除旧的事务日志。这样会增加磁盘使用量，特别是阻止自动真空清理内部目录表，因为系统必须保护 LSN 信息不被覆盖。如果不加以解决，可能会导致目录臃肿、性能下降和重叠真空风险增加，从而可能导致事务停机。
**活动但速度较慢的逻辑复制槽**
有时，由于逻辑复制的性能下降，目录中无效元组的删除会延迟。这种复制延迟会减慢 `catalog_xmin` 的更新速度，并可能导致目录臃肿和重叠真空。
`postgres_get_av_diag()` 函数在找到逻辑复制槽作为拦截器时，会显示类似于以下内容的输出。
```
blocker | Logical replication slot
database | my_database
blocker_identifier | slot1
wait_event | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}
```
**建议采取的措施**
要解决此问题，请检查复制配置中是否存在可能终止应用进程的目标架构或数据问题。最常见的原因如下：
+ 缺少列
+ 数据类型不兼容
+ 数据不匹配
+ 缺少表
如果该问题与基础设施问题有关：
+ 网络问题 – [如何解决 Amazon RDS 数据库处于不兼容网络状态的问题？](https://repost.aws/knowledge-center/rds-incompatible-network)。
+ 数据库或数据库实例不可用的原因如下：
+ 副本实例用尽存储空间 – 有关添加存储的信息，请查看 [Amazon RDS DB 实例存储空间不足](https://repost.aws/knowledge-center/rds-out-of-storage)。
+ 不兼容参数 – 有关如何能够解决该问题的更多信息，请查看[如何修复一直处于不兼容参数状态的 Amazon RDS 数据库实例问题？](https://repost.aws/knowledge-center/rds-incompatible-parameters)。
如果您的实例位于 AWS 网络之外或 AWS EC2 上，请咨询您的管理员，了解如何解决可用性或基础设施相关问题。
**删除非活动槽**
**警告**
注意：在删除复制槽之前，请仔细确保其没有正在进行的复制、处于非活动状态且处于不可恢复状态。过早删除槽可能会中断复制或导致数据丢失。
确认不再需要复制槽后，将其删除以允许自动真空继续运行。条件 `active = 'f'` 可确保仅删除非活动槽。
```
SELECT pg_drop_replication_slot('slot1') WHERE active ='f'
```
## 只读副本
为 [Amazon RDS 只读副本](USER_PostgreSQL.Replication.ReadReplicas.md)启用 `hot_standby_feedback` 设置后，它会阻止主数据库上的自动真空删除只读副本上运行的查询可能仍需要的死行。这样会影响所有类型的物理只读副本，包括那些带有或不带由复制槽管理的只读副本。此行为是必需的，因为在备用副本上运行的查询要求这些行在主副本上保持可用，从而防止发生[查询冲突](https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT)和取消。
**带有物理复制槽的只读副本**
带有物理复制槽的只读副本可显著增强 RDS for PostgreSQL 中复制的可靠性和稳定性。这些槽可确保主数据库保留基本的预写日志文件，直到副本处理它们，即使在网络中断期间也能保持数据一致性。
从 RDS for PostgreSQL 版本 14 开始，所有副本均使用复制槽。在早期版本中，只有跨区域副本使用复制槽。
`postgres_get_av_diag()` 函数在找到以物理复制槽作为拦截器的只读副本时，会显示类似于以下内容的输出。
```
blocker | Read replica with physical replication slot
database |
blocker_identifier | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx
wait_event | Not applicable
autovacuum_lagging_by | 554,080,689
suggestion | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query:
| SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;
| Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. + |
suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;"," +
| [OR] +
| ","Disable hot_standby_feedback"," +
| [OR] +
| ","Delete the read replica if not needed"}
```
**使用流式复制的只读副本**
Amazon RDS 允许在较旧版本（最高为版本 13）中设置不带物理复制槽的只读副本。这种方法通过允许主数据库更积极地回收 WAL 文件来减少开销，这在磁盘空间有限的环境中是有利的，并可以容忍偶尔发生的 ReplicaLag。但是，如果没有槽，备用副本必须保持同步，以免丢失 WAL 文件。Amazon RDS 使用已存档的 WAL 文件来帮助副本在落后时赶上，但是此过程需要仔细监控，而且可能速度很慢。
`postgres_get_av_diag()` 函数在找到作为拦截器的流式只读副本时，会显示类似于以下内容的输出。
```
blocker | Read replica with streaming replication slot
database | Not applicable
blocker_identifier | xx.x.x.xxx/xx
wait_event | Not applicable
autovacuum_lagging_by | 610,146,760
suggestion | Run the following query on the replica "xx.x.x.xxx" to find the long running query: +
| SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343; +
| Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. +
|
suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;"," +
| [OR] +
| ","Disable hot_standby_feedback"," +
| [OR] +
| ","Delete the read replica if not needed"}
```
**建议采取的措施**
按照 `suggested_action` 列中的建议，仔细查看用于解除阻止自动真空的这些选项。
+ **终止查询** – 按照建议列中的指导，您可以连接到只读副本，如 suggested\$1action 列中指定，建议仔细查看终止会话的选项。如果终止被视为是安全的，则可以使用 `pg_terminate_backend()` 函数终止会话。此操作可由管理员（如 RDS 主账户）或具有所需 pg\$1terminate\$1backend() 权限的用户执行。
您可以在只读副本上运行以下 SQL 命令来终止用于阻止主数据库上的真空清理旧行的查询。在函数的输出中报告 `backend_xmin` 的值：
```
SELECT
pg_terminate_backend(pid)
FROM
pg_catalog.pg_stat_activity
WHERE
backend_xmin::text::bigint = backend_xmin;
```
+ **禁用热备用反馈** – 如果 `hot_standby_feedback` 参数会导致明显的真空延迟，请考虑禁用该参数。
`hot_standby_feedback` 参数允许只读副本将其查询活动通知主数据库，从而防止主数据库对备用数据库上使用的表或行执行清理操作。虽然这样可以确保备用数据库上的查询稳定性，但它可能会显著延迟主数据库上的清理时间。禁用此功能允许主数据库继续进行清理，而无需等待备用数据库赶上。但是，如果备用数据库尝试访问已被主数据库清理的行，则可能导致备用数据库上的查询取消或失败。
+ **如果不需要只读副本，请将其删除** – 如果不再需要只读副本，则可以将其删除。这将消除相关的复制开销，并允许主数据库在不受副本阻碍的情况下回收事务日志。
## 临时表
使用 `TEMPORARY` 关键字创建的[临时表](https://www.postgresql.org/docs/current/sql-createtable.html)驻留在临时架构中，例如 pg\$1temp\$1xxx，并且只能由创建它们的会话访问。会话结束时会删除临时表。但是，这些表对于 PostgreSQL 的自动真空流程是不可见的，必须由创建它们的会话手动清理。尝试从另一个会话中清空临时表是无效的。
在特殊情况下，存在一个临时表，但没有活动会话拥有该表。如果所属会话由于致命崩溃、网络问题或类似事件而意外结束，则可能无法清理临时表，从而会将其留作为“孤立”表。当 PostgreSQL 自动真空进程检测到孤立的临时表时，它会记录以下消息：
```
LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"
```
`postgres_get_av_diag()` 函数在将临时表识别为拦截器时，会显示类似于以下内容的输出。要使该函数正确显示与临时表相关的输出，需要在存在这些表的同一数据库中执行该函数。
```
blocker | Temporary table
database | my_database
blocker_identifier | pg_temp_14.ttemp
wait_event | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action | {"DROP TABLE ttemp;"}
```
**建议采取的措施**
按照输出的 `suggestion` 列中提供的说明识别并删除阻止自动真空运行的临时表。使用以下命令删除由 `postgres_get_av_diag()` 报告的临时表。根据 `postgres_get_av_diag()` 函数提供的输出替换表名称。
```
DROP TABLE my_temp_schema.my_temp_table;
```
以下查询可用于识别临时表：
```
SELECT
oid,
relname,
relnamespace::regnamespace,
age(relfrozenxid)
FROM
pg_class
WHERE
relpersistence = 't'
ORDER BY
age(relfrozenxid) DESC;
```
# 在 RDS for PostgreSQL 中解除无法识别的真空拦截器
本节探讨了可能阻碍清理取得进展的其他原因。`postgres_get_av_diag()` 函数目前无法直接识别这些问题。
**Topics**
+ [
## 页面无效
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages)
+ [
## 索引不一致
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Index_inconsistency)
+ [
## 事务速率异常高
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.High_transaction_rate)
## 页面无效
当 PostgreSQL 在访问页面时检测到该页面的校验和不匹配时，就会出现无效页面错误。内容无法读取，从而阻止自动真空冻结元组。这样实际上会停止清理过程。PostgreSQL 的日志中写入了以下错误：
```
WARNING: page verification failed, calculated checksum YYYYY but expected XXXX
ERROR: invalid page in block ZZZZZ of relation base/XXXXX/XXXXX
CONTEXT: automatic vacuum of table myschema.mytable
```
**确定对象类型**
```
ERROR: invalid page in block 4305910 of relation base/16403/186752608
WARNING: page verification failed, calculated checksum 50065 but expected 60033
```
从错误消息中，路径 `base/16403/186752608` 提供以下信息：
+ “base”是 PostgreSQL 数据目录下的目录名称。
+ “16403”是数据库 OID，您可以在 `pg_database` 系统目录中进行查找。
+ “186752608”是 `relfilenode`，您可以使用它在 `pg_class` 系统目录中查找架构和对象名称。
通过在受影响的数据库中检查以下查询的输出，可以确定对象类型。以下查询会检索 oid 为 186752608 的对象信息。将该 OID 替换为与您遇到的错误相关的 OID。
```
SELECT
relname AS object_name,
relkind AS object_type,
nspname AS schema_name
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.oid = 186752608;
```
有关更多信息，请参阅 PostgreSQL 文档 [https://www.postgresql.org/docs/current/catalog-pg-class.html](https://www.postgresql.org/docs/current/catalog-pg-class.html)，以了解所有支持的对象类型，如 `pg_class` 中的 `relkind` 列所示。
**指南**
此问题的最有效解决方案取决于特定 Amazon RDS 实例的配置以及受不一致页面影响的数据类型。
**如果对象类型是索引：**
建议重建索引。
+ **使用 `CONCURRENTLY` 选项** – 在 PostgreSQL 版本 12 之前，重建索引需要独占的表锁定，从而限制对表的访问。在 PostgreSQL 版本 12 及更高版本中，`CONCURRENTLY` 选项允许行级别锁定，从而显著提高表的可用性。命令如下：
```
REINDEX INDEX ix_name CONCURRENTLY;
```
虽然 `CONCURRENTLY` 的破坏性较小，但在繁忙的表中其速度可能会变慢。如果可能，可以考虑在流量较低的时段构建索引。
有关更多信息，请参阅 PostgreSQL [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) 文档。
+ **使用 `INDEX_CLEANUP FALSE` 选项** – 如果索引很大并且估计需要大量时间才能完成，则您可以通过在排除索引的同时执行手动 `VACUUM FREEZE` 来解除阻止 autovacuum。PostgreSQL 版本 12 及更高版本中提供了此功能。
绕过索引将允许您跳过索引不一致的真空过程并缓解重叠问题。但是，这样并不能解决潜在的无效页面问题。要完全应对和解决无效页面问题，您仍然需要重建索引。
**如果对象类型为实体化视图：**
如果实体化视图上出现无效页面错误，请登录到受影响的数据库并刷新以解决无效的页面：
刷新实体化视图：
```
REFRESH MATERIALIZED VIEW schema_name.materialized_view_name;
```
如果刷新失败，请尝试重新创建：
```
DROP MATERIALIZED VIEW schema_name.materialized_view_name;
CREATE MATERIALIZED VIEW schema_name.materialized_view_name AS query;
```
刷新或重新创建实体化视图可在不影响基础表数据的情况下还原该视图。
**对于所有其他对象类型：**
对于所有其他对象类型，请联系 AWS 支持。
## 索引不一致
逻辑上不一致的索引可能会阻碍自动真空取得进展。在索引的真空阶段或 SQL 语句访问索引时，将记录以下错误或类似错误。
```
ERROR: right sibling's left-link doesn't match:block 5 links to 10 instead of expected 2 in index ix_name
```
```
ERROR: failed to re-find parent key in index "XXXXXXXXXX" for deletion target page XXX
CONTEXT: while vacuuming index index_name of relation schema.table
```
**指南**
在手动 `VACUUM FREEZE` 上使用 `INDEX_CLEANUP` 重建索引或跳过索引。有关如何重建索引的信息，请参阅[如果对象类型是索引](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages)。
+ **使用 CONCURRENTLY 选项** – 在 PostgreSQL 版本 12 之前，重建索引需要独占的表锁定，从而限制对表的访问。在 PostgreSQL 版本 12 及更高版本中，CONCURRENTLY 选项允许行级别锁定，从而显著提高表的可用性。命令如下：
```
REINDEX INDEX ix_name CONCURRENTLY;
```
虽然 CONCURRENTLY 的破坏性较小，但在繁忙的表中其速度可能会变慢。如果可能，可以考虑在流量较低的时段构建索引。有关更多信息，请参阅 *PostgreSQL* 文档中的 [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html)。
+ **使用 INDEX\$1CLEANUP FALSE 选项** – 如果索引很大并且估计需要大量时间才能完成，则您可以通过在排除索引的同时执行手动 VACUUM FREEZE 来解除阻止 autovacuum。PostgreSQL 版本 12 及更高版本中提供了此功能。
绕过索引将允许您跳过索引不一致的真空过程并缓解重叠问题。但是，这样并不能解决潜在的无效页面问题。要完全应对和解决无效页面问题，您仍然需要重建索引。
## 事务速率异常高
在 PostgreSQL 中，事务速率高会显著影响自动真空的性能，从而导致无效元组的清理速度变慢，并增加事务 ID 重叠的风险。您可以通过衡量两个时间段之间的 `max(age(datfrozenxid))` 差异（通常为每秒）来监控事务速率。此外，您还可以使用 RDS 性能详情中的以下计数器指标来衡量事务速率（xact\$1commit 与 xact\$1rollback 之和），即事务总数。
| 计数器 | 类型 | 单位 | 指标 |
| --- | --- | --- | --- |
| xact\$1commit | 事务 | 每秒提交数 | db.Transactions.xact\$1commit |
| xact\$1rollback | 事务 | 每秒回滚数 | db.Transactions.xact\$1rollback |
快速增加表示高事务负载，这可能会使自动真空不堪重负，从而导致膨胀、锁争用和潜在的性能问题。这样可能会以多种方式对自动真空进程产生负面影响：
+ **表活动：**正在经历清理操作的特定表可能会遇到大量事务，从而导致延迟。
+ **系统资源：**整个系统可能会过载，使自动真空难以访问必要的资源以便高效运行。
请考虑以下策略，以允许自动真空更有效地运行并跟上其任务：
1. 如果可能，请降低事务速率。请考虑在可行的情况下对类似的事务进行批处理或分组。
1. 在非高峰时段，每晚、每周或每两周通过手动 `VACUUM FREEZE` 操作来定位频繁更新的表。
1. 请考虑扩展您的实例类以分配更多系统资源来处理高事务量和自动真空操作。
# 解决 RDS for PostgreSQL 中的真空性能问题
本节讨论经常导致真空性能变慢的因素以及如何解决这些问题。
**Topics**
+ [
## 清理大型索引
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Large_indexes)
+ [
## 需要清理的表或数据库太多
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Multiple_tables)
+ [
## 正在运行积极真空（以防止重叠）
](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum)
## 清理大型索引
VACUUM 按以下阶段顺序运行：初始化、堆扫描、索引和堆真空清理、索引清理、堆截断和最终清理。在堆扫描期间，该过程修剪页面、整理碎片并冻结它们。完成堆扫描后，VACUUM 将清理索引，将空页返回到操作系统，并执行最终的清理任务，例如对空闲空间映射表执行真空清理和更新统计信息。
对索引执行真空清理操作时，如果 `maintenance_work_mem`（或 `autovacuum_work_mem`）不足以处理索引，则可能需要多次扫描。在 PostgreSQL 16 及更早版本中，用于存储无效元组 ID 存在 1 GB 内存限制，对于大型索引通常需要多次扫描。PostgreSQL 17 引入了 `TidStore`，这会动态分配内存，而不是使用单一分配数组。此功能消除了 1 GB 限制，可以更高效地利用内存，并减少了每次索引操作中进行多次索引扫描的需求。
在 PostgreSQL 17 中，对于大型索引，如果可用内存无法一次性容纳整个索引的处理需求，那么仍可能需要多次扫描。通常，更大的索引往往包含更多需要多次扫描的无效元组。
**检测缓慢的 vacuum 操作**
`postgres_get_av_diag()` 函数可以检测 vacuum 操作何时因内存不足而运行缓慢。有关此函数的更多信息，请参阅[在 RDS for PostgreSQL 中安装 autovacuum 监控和诊断工具](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md)。
当可用内存不足以一次性完成索引 vacuum 时，`postgres_get_av_diag()` 函数会发出以下通知。
**`rds_tools` 1.8**
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_work_mem is "XXX" and might not be sufficient. Consider increasing the setting, and if necessary, scaling up the Amazon RDS instance class for more memory.
Additionally, review the possibility of manual vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;).
```
**`rds_tools` 1.9**
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_work_mem is XX might not be sufficient. Consider increasing the setting to XXX, and if necessary, scaling up the RDS instance class for more
memory. The suggested value is an estimate based on the current number of dead tuples for the table being vacuumed, which might not fully reflect the latest state. Additionally, review the possibility of manual
vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;). For more information, see
[Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html)
.
```
**注意**
`postgres_get_av_diag()` 函数依靠 `pg_stat_all_tables.n_dead_tup` 来估计索引清理所需的内存量。
当 `postgres_get_av_diag()` 函数识别出由于 `autovacuum_work_mem` 不足而需要多次索引扫描的慢速 vacuum 操作时，它将生成以下消息：
```
NOTICE: Your vacuum is performing multiple index scans due to insufficient autovacuum_work_mem:XXX for index vacuuming.
For more information, see [Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html).
```
**指南**
您可以使用手动 `VACUUM FREEZE` 应用以下解决方法来加快表的冻结速度。
**增加用于清理的内存**
正如 `postgres_get_av_diag()` 函数所建议的那样，建议增加 `autovacuum_work_mem` 参数以解决实例级别的潜在内存限制问题。虽然 `autovacuum_work_mem` 是一个动态参数，但请务必注意，要使新的内存设置生效，自动真空进程守护程序需要重新启动其工作线程。要实现这一点，请执行以下操作：
1. 确认新设置已落实到位。
1. 终止当前运行自动真空的进程。
这种方法可确保将调整后的内存分配应用于新的自动真空操作。
为了获得更直接的结果，请考虑在会话中手动执行 `VACUUM FREEZE` 操作，并增加 `maintenance_work_mem` 设置：
```
SET maintenance_work_mem TO '1GB';
VACUUM FREEZE VERBOSE table_name;
```
如果您使用的是 Amazon RDS，并且发现需要额外的内存来支持更高的 `maintenance_work_mem` 或 `autovacuum_work_mem` 值，请考虑升级到具有更多内存的实例类。这样可以提供必要的资源来增强手动和自动清理操作，从而提高整体真空和数据库性能。
**禁用 INDEX\$1CLEANUP**
PostgreSQL 版本 12 及更高版本中的手动 `VACUUM` 允许跳过索引清理阶段，而 PostgreSQL 版本 14 及更高版本中的紧急自动真空会根据 [https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE) 参数自动执行此操作。
**警告**
跳过索引清理会导致索引膨胀并对查询性能产生负面影响。为了缓解这种情况，可以考虑在维护时段内对受影响的索引重建索引或清理受影响的索引。
有关处理大型索引的更多指导，请参阅[使用大型索引管理 autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md)中的文档。
**并行索引清理**
从 PostgreSQL 13 开始，默认情况下，可以使用手动 `VACUUM` 并行对索引进行清理，并为每个索引分配一个真空工件进程。但是，要使 PostgreSQL 确定真空操作是否符合并行执行的条件，必须满足特定标准：
+ 必须至少有两个索引。
+ `max_parallel_maintenance_workers` 参数应至少设置为 2。
+ 索引大小必须超过 `min_parallel_index_scan_size` 限制，默认为 512 KB。
您可以根据 Amazon RDS 实例上可用的 vCPU 数量和表上的索引数量来调整 `max_parallel_maintenance_workers` 设置，以优化清理周转时间。
有关更多信息，请参阅 [Parallel vacuuming in Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL](https://aws.amazon.com/blogs/database/parallel-vacuuming-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/)。
## 需要清理的表或数据库太多
如 PostgreSQL 的[自动真空进程守护程序](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM')文档中所述，自动真空进程守护程序通过多个进程运行。这包括一个持久性自动真空启动程序，负责为系统中的每个数据库启动自动真空工件进程。该启动程序安排这些工件大约每个数据库每 `autovacuum_naptime` 秒启动一次。
对于“N”个数据库，大约每 [`autovacuum_naptime`/N 秒] 就会有一个新工件开始工作。但是，并发工件的总数受 `autovacuum_max_workers` 设置的限制。如果需要清理的数据库或表的数量超过此限制，则一旦有工件可用，就会立即处理下一个数据库或表。
当许多大型表或数据库需要同时执行清理操作时，所有可用的自动真空工件可能会长时间被占用，从而会延迟其他表和数据库的维护。在事务速率较高的环境中，此瓶颈可能会迅速升级，并可能导致 Amazon RDS 实例中出现重叠真空问题。
当 `postgres_get_av_diag()` 检测到大量表或数据库时，它会提供以下建议：
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_max_workers:3 might not be sufficient. Consider increasing the setting and, if necessary, consider scaling up the Amazon RDS instance class for more workers.
```
**指南**
**增加 autovacuum\$1max\$1workers**
为了加快清理速度，我们建议调整 `autovacuum_max_workers` 参数以允许更多并发自动真空工件。如果性能瓶颈仍然存在，请考虑将 Amazon RDS 实例扩展到具有更多 vCPU 的类，这样可以进一步提高并行处理能力。
## 正在运行积极真空（以防止重叠）
PostgreSQL 中数据库（MaximumUsedTransactionIDs）的龄期仅会在成功完成积极真空（以防止重叠）时减少。在此真空结束之前，龄期将继续增加，具体取决于事务速率。
`postgres_get_av_diag()` 函数在检测到积极真空时会生成以下 `NOTICE`。但是，只有在真空处于活动状态至少两分钟后，它才会触发此输出。
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.
```
有关积极真空的更多信息，请参阅[当积极真空已经在运行时](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)。
您可以使用以下查询验证是否正在进行主动清理：
```
SELECT
a.xact_start AS start_time,
v.datname "database",
a.query,
a.wait_event,
v.pid,
v.phase,
v.relid::regclass,
pg_size_pretty(pg_relation_size(v.relid)) AS heap_size,
(
SELECT
string_agg(pg_size_pretty(pg_relation_size(i.indexrelid)) || ':' || i.indexrelid::regclass || chr(10), ', ')
FROM
pg_index i
WHERE
i.indrelid = v.relid
) AS index_sizes,
trunc(v.heap_blks_scanned * 100 / NULLIF(v.heap_blks_total, 0)) AS step1_scan_pct,
v.index_vacuum_count || '/' || (
SELECT
count(*)
FROM
pg_index i
WHERE
i.indrelid = v.relid
) AS step2_vacuum_indexes,
trunc(v.heap_blks_vacuumed * 100 / NULLIF(v.heap_blks_total, 0)) AS step3_vacuum_pct,
age(CURRENT_TIMESTAMP, a.xact_start) AS total_time_spent_sofar
FROM
pg_stat_activity a
INNER JOIN pg_stat_progress_vacuum v ON v.pid = a.pid;
```
您可以通过检查输出中的查询列来确定这是否为积极真空（以防止重叠）。“防止重叠”一词表示这是一个积极真空。
```
query | autovacuum: VACUUM public.t3 (to prevent wraparound)
```
例如，假设您有一个事务龄期为 10 亿的拦截器，还有一个需要积极真空的表，以防止在相同的事务龄期出现重叠。此外，在事务龄期为 7.5 亿时，还有另一个拦截器。在事务龄期为 10 亿时清除拦截器后，事务龄期不会立即下降到 7.5 亿。它将保持较高的状态，直到需要积极真空或任何龄期超过 7.5 亿的事务完成为止。在此期间，PostgreSQL 集群的事务龄期将持续提高。真空过程完成后，事务龄期将降至 7.5 亿，但在进一步的清理完成之前，事务龄期将再次开始增加。只要这些条件仍然存在，此循环就会继续，直到事务龄期最终下降到为您的 Amazon RDS 实例配置的级别（由 `autovacuum_freeze_max_age` 指定）。
# RDS for PostgreSQL 中的通知消息说明
`postgres_get_av_diag()` 函数提供以下通知消息：
**当龄期尚未达到监控阈值时**
默认情况下，`postgres_get_av_diag()` 识别拦截器的监控阈值为 5 亿个事务。如果 `postgres_get_av_diag()` 生成以下通知，则表示事务龄期尚未达到此阈值。
```
NOTICE: postgres_get_av_diag() checks for blockers that prevent aggressive vacuums only, it does so only after exceeding dvb_threshold which is 500,000,000 and age of this PostgreSQL cluster is currently at 2.
```
**未连接到具有最早事务 ID 龄期的数据库**
当连接到具有最早事务 ID 龄期的数据库时，`postgres_get_av_diag()` 函数可提供最准确的输出。`postgres_get_av_diag()` 报告的具有最早事务 ID 龄期的数据库将不同于您的案例中的“my\$1database”。如果您未连接到正确的数据库，则会生成以下通知：
```
NOTICE: You are not connected to the database with the age of oldest transaction ID. Connect to my_database database and run postgres_get_av_diag() for accurate reporting.
```
出于以下原因，连接到具有最早事务龄期的数据库非常重要：
+ **识别临时表拦截器：**由于临时表的元数据特定于每个数据库，因此它们通常位于在其中创建临时表的数据库中。但是，如果临时表恰好是最重要的拦截器，并且位于具有最早事务的数据库中，则可能会产生误导。连接到正确的数据库可确保准确识别临时表拦截器。
+ **诊断缓慢真空：**索引元数据和表计数信息是特定于数据库的，是诊断缓慢真空问题所必需的。
**按龄期计算具有最早事务的数据库位于 rdsadmin 或 template0 数据库中**
在某些情况下，`rdsadmin` 或 `template0` 数据库可能会被标识为具有最早事务 ID 龄期的数据库。如果发生这种情况，`postgres_get_av_diag()` 将发出以下通知：
```
NOTICE: The database with the age of oldest transaction ID is rdsadmin or template0, reach out to support if the reported blocker is in rdsadmin or template0.
```
验证列出的拦截器是否不是来自这两个数据库中的任何一个。如果据报告 `rdsadmin` 或 `template0` 中存在拦截器，请联系支持部门，因为这些数据库对用户不可访问，需要干预。
`rdsadmin` 或 `template0` 数据库包含最重要拦截器的可能性极小。
**当积极真空已经在运行时**
`postgres_get_av_diag()` 函数旨在报告何时正在运行积极真空进程，但它仅在真空处于活动状态至少 1 分钟后触发此输出。这种有意的延迟有助于减少误报的可能性。通过等待，该函数可确保仅报告有效、重要的真空，从而可更准确、更可靠地监控真空活动。
`postgres_get_av_diag()` 函数在检测到一个或多个正在进行的积极真空时会生成以下通知。
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.
```
如通知中所述，请继续监控真空的性能。有关积极真空的更多信息，请参阅[正在运行积极真空（以防止重叠）](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum)
**当自动真空关闭时**
如果数据库实例上禁用了自动真空，则 `postgres_get_av_diag()` 函数会生成以下通知：
```
NOTICE: Autovacuum is OFF, we strongly recommend to enable it, no restart is necessary.
```
Autovacuum 是 RDS for PostgreSQL 数据库实例的一项关键功能，可确保数据库顺畅运行。它会自动删除旧的行版本，回收存储空间，并防止表膨胀，从而帮助保持表和索引的高效性，以实现最佳性能。此外，它还可以防止发生事务 ID 重叠，这可能会停止 Amazon RDS 实例上的事务。禁用自动真空会导致数据库性能和稳定性的长期下降。建议您始终将其保持开启状态。有关更多信息，请参阅 [Understanding autovacuum in RDS for PostgreSQL environments](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/)。
关闭自动真空并不会阻止积极真空。一旦您的表达到 `autovacuum_freeze_max_age` 阈值，仍会发生这种情况。
**剩余的事务数严重不足**
`postgres_get_av_diag()` 函数会在即将发生重叠真空时生成以下通知。当您的 Amazon RDS 实例距离可能拒绝新事务还有 1 亿个事务时，将发出此通知。
```
WARNING: Number of transactions remaining is critically low, resolve issues with autovacuum or perform manual VACUUM FREEZE before your instance stops accepting transactions.
```
需要立即采取行动以免发生数据库停机。您应该密切监控真空操作，并考虑在受影响的数据库上手动启动 `VACUUM FREEZE` 以防止事务失败。
# 管理 Amazon RDS for PostgreSQL 中的高对象计数
虽然 PostgreSQL 限制是理论上的，但数据库中的对象计数极高会对各种操作造成明显的性能影响。本文档介绍几种常见的对象类型，当对象的总计数较高时，可能会导致多种可能的影响。
下表概述了对象类型及其潜在影响：
**对象类型和潜在影响**
| 对象的类型 | Autovacuum | 逻辑复制 | 主要版本升级 | pg\$1dump/pg\$1restore | 一般性能 | 实例重启 |
| --- | --- | --- | --- | --- | --- | --- |
| [关系](#PostgreSQL.HighObjectCount.Relations) | x | | x | x | x | |
| [临时表](#PostgreSQL.HighObjectCount.TempTables)。 | x | | | | x | |
| [未记录表](#PostgreSQL.HighObjectCount.UnloggedTables) | | x | | | | x |
| [分区](#PostgreSQL.HighObjectCount.Partitions) | | | | | x | |
| [临时文件](#PostgreSQL.HighObjectCount.TempFiles) | | | | | x | |
| [序列](#PostgreSQL.HighObjectCount.Sequences) | | x | | | | |
| [大型对象](#PostgreSQL.HighObjectCount.LargeObjects) | | x | x | | | |
## 关系
对 PostgreSQL 数据库中表的数量没有具体的硬性限制。理论上限极高，但在数据库设计过程中还需要记住其它实际限制。
**影响：Autovacuum 滞后**
由于工作线程不足以应对繁重的工作量，Autovacuum 可能难以跟上事务 ID 的增长或表膨胀。
**建议的操作：**要使 autovacuum 能够适应给定数量的表和给定的工作负载，需考虑多个调优因素。有关如何确定适当的 autovacuum 设置的建议，请参阅[使用 PostgreSQL autovacuum 的最佳实践](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html)。使用 [postgres\$1get\$1av\$1diag utility](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.html) 来监控事务 ID 增长问题。
**影响：主要版本升级/pg\$1dump 和还原**
Amazon RDS 在 pg\$1upgrade 执行期间使用“--link”选项来避免必须复制数据文件，但仍需要将架构元数据还原到新版本的数据库中。即使使用并行 pg\$1restore，但如果存在大量的关系，这也将增加停机时间。
**影响：一般性能降级**
由于目录大小，导致一般性能降级。每个表及其关联的列将添加到 `pg_attribute`、`pg_class` 和 `pg_depend` 表中，这些表在常规数据库操作中频繁使用。不会显示特定的等待事件，但共享缓冲区效率会受到影响。
**建议的操作：**定期检查这些特定表的表膨胀情况，偶尔对这些特定的表执行 `VACUUM FULL`。请注意，对目录表执行 `VACUUM FULL` 需要 `ACCESS EXCLUSIVE` 锁，这意味着在操作完成之前，任何其它查询都无法访问这些表。
**影响：文件描述符耗尽**
错误：“out of file descriptors: Too many open files in system; release and retry”。PostgreSQL `max_files_per_process` 参数决定了每个进程可以打开多少个文件。如果有大量的连接加入大量的表，则有可能达到此限制。
**建议的操作：**
+ 降低 `max_files_per_process` 参数的值可能有助于缓解此错误。每个进程和子进程（例如，并行查询）可以打开此数量的文件，如果查询要联接多个表，则此限制可能会耗尽。
+ 减少连接总数并使用连接池程序，例如 [Amazon RDS 代理](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html)或其它解决方案（如 PgBouncer）。要了解更多信息，请参阅 [PGBouncer](https://www.pgbouncer.org/) 网站。
**影响：索引节点耗尽**
错误：“设备上没有剩余空间”。如果在有充足的可用存储空间时观察到这种情况，则这是由于索引节点用完所致。[Amazon RDS 增强监控](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html)会显示正在使用的索引节点以及可用于主机的最大数量。
**近似阈值：**[百万](#PostgreSQL.HighObjectCount.Note)
## 临时表
使用临时表对测试数据或中间结果很有用，这是许多数据库引擎中常见的模式。必须了解在 PostgreSQL 中大量使用的影响，以避免一些陷阱。每个临时表创建和删除都会向系统目录表中添加行，当它们变得膨胀时，这将导致一般性能问题。
**影响：Autovacuum 滞后**
临时表不会被 autovacuum 清空，但会在它们存在期间保留事务 ID，如果不移除，可能会导致环绕。
**建议的操作：**临时表将在创建临时表的会话持续时间内生效，也可以手动删除它们。避免使用临时表执行长时间运行的事务的最佳实践，可防止这些表促成已用事务 ID 增长达到最大程度。
**影响：一般性能降级**
由于目录大小，导致一般性能降级。当会话不断地创建和删除临时表时，将会增加 `pg_attribute`、`pg_class` 和 `pg_depend` 表的大小，正常数据库操作中会频繁使用这些表。不会显示特定的等待事件，但共享缓冲区效率会受到影响。
**建议的操作：**
+ 定期检查这些特定表的表膨胀情况，偶尔对这些特定的表执行 `VACUUM FULL`。请注意，对目录表执行 `VACUUM FULL` 需要 `ACCESS EXCLUSIVE` 锁，这意味着在操作完成之前，任何其它查询都无法访问这些表。
+ 如果大量使用临时表，则在主要版本升级之前，强烈建议对这些特定的目录表执行 `VACUUM FULL`，以减少停机时间。
**一般最佳实践：**
+ 通过使用公用表表达式生成中间结果，减少临时表的使用。这些有时会使所需的查询复杂化，但会消除上面列出的影响。
+ 通过使用 `TRUNCATE` 命令清除内容，而不是执行删除/创建步骤，重用临时表。这也将消除由临时表导致的事务 ID 增长问题。
**近似阈值：**[数万](#PostgreSQL.HighObjectCount.Note)
## 未记录表
未记录的表可以提高性能，因为它们不会生成任何 WAL 信息。必须谨慎使用这些表，原因在于，它们在数据库崩溃恢复期间不会提供持久性，因为这些表会被截断。在 PostgreSQL 中，这是一项代价高昂的操作，因为每个未记录的表都会依次被截断。虽然对于少量未记录的表来说，此操作速度很快，但当它们的数量达到数千个时，可能会在启动过程中开始增加明显的延迟。
**影响：逻辑复制**
逻辑复制中通常不包括未记录的表，包括[蓝绿部署](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html)，因为逻辑复制依赖于 WAL 来捕获和传输更改。
**影响：恢复期间的停机时间延长**
在任何涉及数据库崩溃恢复的数据库状态（例如带失效转移的多可用区重启、Amazon RDS 时间点故障恢复和 Amazon RDS 主要版本升级）期间，都会发生截断未记录表的序列化操作。这可能导致停机时间比预期长很多。
**建议的操作：**
+ 尽量减少将未记录的表仅用于在数据库崩溃恢复操作期间可以接受丢失的数据。
+ 尽量减少使用未记录的表，因为当前的串行截断行为可能会导致数据库花费大量时间来启动。
**一般最佳实践：**
+ 未记录的表不具备崩溃安全功能。在 PostgreSQL 中，启动涉及崩溃恢复的时间点故障恢复需要花大量的时间，因为这是一个截断每个表的串行进程。
**近似阈值：**[数千](#PostgreSQL.HighObjectCount.Note)
## 分区
分区可以提高查询性能并提供数据的逻辑组织。在理想情况下，分区的组织方式是为了便于在查询计划和执行期间使用分区修剪。使用过多的分区可能会对查询性能和数据库维护产生负面影响。应谨慎选择如何对表进行分区，因为不良的设计可能会对查询计划和执行的性能产生负面影响。有关分区的详细信息，请参阅 [PostgreSQL 文档](https://www.postgresql.org/docs/current/ddl-partitioning.html)。
**影响：一般性能降级**
有时，计划时间开销会增加，并且查询的解释计划会变得更加复杂，因此很难确定调优机会。对于版本 18 之前的 PostgreSQL 版本，许多工作负载较高的分区可能会导致 `LWLock:LockManager` 等待。
**建议的操作：**确定最少的分区数量，这样您既能完成数据的整理，同时又能提供高性能的查询执行。
**影响：维护复杂性**
分区数量过多会带来维护困难，例如预创建和移除。Autovacuum 会将分区视为正常关系，必须执行定期清理，因此需要足够的工作线程来完成该任务。
**建议的操作：**
+ 确保预先创建分区，以便在需要新分区（例如，基于月度的分区）和回滚旧分区时不会阻塞工作负载。
+ 确保有足够的 autovacuum 工作线程来对所有分区进行正常的清理维护。
**近似阈值：**[数百](#PostgreSQL.HighObjectCount.Note)
## 临时文件
与上述临时表不同，当一个复杂的查询可能同时执行多个排序或哈希操作时，PostgreSQL 会创建临时文件，每个操作都使用实例内存来存储结果，直至达到在 `work_mem` 参数中指定的值。当实例内存不足时，会创建临时文件来存储结果。有关临时文件的更多详细信息，请参阅[管理临时文件](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ManagingTempFiles.html)。如果您的工作负载生成了大量此类文件，则可能会产生多种影响。
**影响：文件描述符耗尽**
错误：“out of file descriptors: Too many open files in system; release and retry”。PostgreSQL `max_files_per_process` 参数决定了每个进程可以打开多少个文件。如果有大量的连接加入大量的表，则有可能达到此限制。
**建议的操作：**
+ 降低 `max_files_per_process` 参数的值可能有助于缓解此错误。每个进程和子进程（例如，并行查询）可以打开此数量的文件，如果查询要联接多个表，则此限制可能会耗尽。
+ 减少连接总数并使用连接池程序（例如 [Amazon RDS 代理](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html)）或其它解决方案（如 PgBouncer）。要了解更多信息，请参阅 [PGBouncer](https://www.pgbouncer.org/) 网站。
**影响：索引节点耗尽**
错误：“设备上没有剩余空间”。如果在有充足的可用存储空间时观察到这种情况，则这是由于索引节点用完所致。[Amazon RDS 增强监控](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html)会显示正在使用的索引节点以及可用于主机的最大数量。
**一般最佳实践：**
+ 使用[性能详情](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html)监控临时文件使用情况。
+ 调优生成大量临时文件的查询，以查看是否可能减少临时文件总数。
**近似阈值：**[数千](#PostgreSQL.HighObjectCount.Note)
## 序列
序列是 PostgreSQL 中用于自动递增列的底层对象，它们为数据提供了唯一性和密钥。这些序列可以在单独的表上使用，在正常操作期间不会产生任何后果，但逻辑复制除外。
在 PostgreSQL 中，逻辑复制当前不会将序列的当前值复制到任何订阅用户。要了解更多信息，请参阅 [PostgreSQL 文档中的“Restrictions”页面](https://www.postgresql.org/docs/current/logical-replication-restrictions.html)。
**影响：切换时间延长**
如果您计划使用[ Amazon RDS 蓝绿部署](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html)进行任何类型的配置更改或升级，请务必了解大量的序列对切换的影响。切换的最后阶段之一会同步序列的当前值，如果数量达几千个，这将增加总体切换时间。
**建议的操作：**如果您的数据库工作负载支持使用共享 UUID 而不是按表排序的方法，这将削减切换期间的同步步骤。
**近似阈值：**[数千](#PostgreSQL.HighObjectCount.Note)
## 大型对象
大型对象存储在名为 pg\$1largeobject 的单个系统表中。每个大型对象在系统表 pg\$1largeobject\$1metadata 中也有一个条目。这些对象的创建、修改和清理方式与标准关系大不相同。大型对象不是通过 autovacuum 处理，而必须通过名为 vacuumlo 的单独进程定期清理。有关管理大型对象的示例，请参阅“使用 lo 模块管理大型对象”。
**影响：逻辑复制**
在逻辑复制期间，当前不会在 PostgreSQL 中复制大型对象。要了解更多信息，请参阅 [PostgreSQL 文档中的“Restrictions”页面](https://www.postgresql.org/docs/current/logical-replication-restrictions.html)。在[蓝绿](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html)配置中，这意味着蓝色环境中的大型对象不会复制到绿色环境中。
**影响：主要版本升级**
如果有数百万个大型对象，并且实例在升级期间无法处理它们，则升级可能会耗尽内存并失败。PostgreSQL 主要版本升级过程包括两个主要阶段：通过 pg\$1dump 转储架构和通过 pg\$1restore 还原架构。如果数据库具有数百万个大型对象，则您需要确保实例具有足够的内存，可在升级期间处理 pg\$1dump 和 pg\$1restore，并将其扩展到更大的实例类型。
**一般最佳实践：**
+ 定期使用 vacuumlo 实用程序移除您可能拥有的任何孤立的大型对象。
+ 考虑使用 BYTEA 数据类型来将大型对象存储在数据库中。
**近似阈值：**[百万](#PostgreSQL.HighObjectCount.Note)
## 近似阈值
本主题中提到的近似阈值仅用于估算特定资源可以扩展到多大程度。它们代表了更有可能受到上述影响的一般范围，但实际行为取决于您的特定工作负载、实例大小和配置。虽然可能超过这些估算值，但必须坚持谨慎行事并进行维护，以避免所列的影响。
# 管理 Amazon RDS for PostgreSQL 中的 TOAST OID 争用
TOAST（超大属性存储技术）是一项 PostgreSQL 功能，旨在处理超过典型 8KB 数据库块大小的大数据值。PostgreSQL 不允许物理行跨越多个块。块大小是行大小的上限。TOAST 通过将较大的字段值拆分为较小的块来克服这一限制。它将这些块单独存储在链接到主表的专用 TOAST 表中。有关更多信息，请参阅 [PostgreSQL TOAST storage mechanism and implementation documentation](https://www.postgresql.org/docs/current/storage-toast.html)。
**Topics**
+ [
## 了解 TOAST 操作
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.HowWorks)
+ [
## 识别性能方面的挑战
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.PerformanceChallenges)
+ [
## 建议
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Recommendations)
+ [
## 监控
](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Monitoring)
## 了解 TOAST 操作
TOAST 行压缩并将大字段值存储在行外。TOAST 为存储在 TOAST 表中的每个超大数据块分配一个唯一的 OID（对象标识符）。主表在页面上存储 TOAST 值的 ID 和关系 ID，以引用 TOAST 表中相应的行。这使得 PostgreSQL 能够高效地定位和管理这些 TOAST 数据块。然而，随着 TOAST 表的增长，系统面临耗尽可用 OID 的风险，进而导致性能下降，甚至因 OID 耗尽而引发潜在的停机。
### TOAST 中的对象标识符
对象标识符（OID）是 PostgreSQL 中用于引用数据库对象（如表、索引和函数）的一种系统范围内唯一标识符。这些标识符在 PostgreSQL 的内部操作中发挥着关键作用，使数据库能够高效地定位和管理对象。
对于符合 TOAST 条件的数据表，PostgreSQL 会为存储在相关 TOAST 表中的每个超大数据块分配一个 OID，用于唯一标识该数据块。系统还会为每个数据块关联一个 `chunk_id`，以便 PostgreSQL 在 TOAST 表中高效地组织和定位这些数据块。
## 识别性能方面的挑战
PostgreSQL 的 OID 管理依赖一个全局 32 位计数器，因此在生成 40 亿个唯一值后会发生循环回绕。尽管整个数据库集群共享这一计数器，但在 TOAST 操作中，OID 分配过程包括以下两步：
+ **全局计数器分配** - 全局计数器在集群范围内分配一个新的 OID。
+ **本地冲突检查** – TOAST 表负责确保新分配的 OID 不与该特定表中已有的 OID 冲突。
在以下情况下，可能会出现性能下降问题：
+ TOAST 表存在高度碎片或 OID 使用密集，导致 OID 分配过程延迟。
+ 在高数据变更率或广表结构中频繁使用 TOAST 时，系统将不断分配和重用 OID，从而加剧 OID 管理的负担。
有关更多信息，请参阅 [PostgreSQL TOAST table size limits and OID allocation documentation](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit)：
全局计数器负责生成 OID，并且每生成 40 亿个值后就会循环回绕，因此系统偶尔会生成一个已经使用过的值。PostgreSQL 会检测到这种情况，并自动尝试使用下一个 OID。如果 TOAST 表中存在一大段连续使用的 OID 而没有空隙，就可能导致 INSERT 操作变慢。随着 OID 空间逐渐填满，这些问题会变得更加明显，导致插入和更新操作的性能下降。
### 识别问题
+ 简单的 `INSERT` 语句会随机出现明显变慢的情况，时快时慢，缺乏一致性。
+ 延迟仅发生在涉及 TOAST 操作的 `INSERT` 和 `UPDATE` 语句上。
+ 当 PostgreSQL 在 TOAST 表中难以找到可用 OID 时，日志中会出现如下条目：
```
LOG: still searching for an unused OID in relation "pg_toast_20815"
DETAIL: OID candidates have been checked 1000000 times, but no unused OID has been found yet.
```
+ 性能详情显示与 `LWLock:buffer_io` 和 `LWLock:OidGenLock` 等待事件相关的平均活跃会话（AAS）数量较多。
您可以运行以下 SQL 查询来识别具有等待事件的长时间运行的 INSERT 事务：
```
SELECT
datname AS database_name,
usename AS database_user,
pid,
now() - pg_stat_activity.xact_start AS transaction_duration,
concat(wait_event_type, ':', wait_event) AS wait_event,
substr(query, 1, 30) AS TRANSACTION,
state
FROM
pg_stat_activity
WHERE (now() - pg_stat_activity.xact_start) > INTERVAL '60 seconds'
AND state IN ('active', 'idle in transaction', 'idle in transaction (aborted)', 'fastpath function call', 'disabled')
AND pid <> pg_backend_pid()
AND lower(query) LIKE '%insert%'
ORDER BY
transaction_duration DESC;
```
显示等待时间较长的 INSERT 操作的查询结果示例：
```
database_name | database_user | pid | transaction_duration | wait_event | transaction | state
---------------+-----------------+-------+----------------------+---------------------+--------------------------------+--------
postgres | db_admin_user| 70965 | 00:10:19.484061 | LWLock:buffer_io | INSERT INTO "products" (......... | active
postgres | db_admin_user| 69878 | 00:06:14.976037 | LWLock:buffer_io | INSERT INTO "products" (......... | active
postgres | db_admin_user| 68937 | 00:05:13.942847 | : | INSERT INTO "products" (......... | active
```
### 查明问题
+ **测试小型插入** - 插入一条小于 `toast_tuple_target` 阈值的记录。请注意，压缩会在 TOAST 存储之前应用。如果此操作没有性能问题，则说明问题与 TOAST 操作有关。
+ **测试新表** – 创建一个结构相同的新表，并插入一条大于 `toast_tuple_target` 的记录。如果此操作正常，则说明问题局限于原表的 OID 分配。
## 建议
以下方法可帮助解决 TOAST OID 争用问题。
+ **数据清理与归档** – 审查并删除任何过期或不必要的数据，以释放 OID 供将来使用，或者将数据归档。请考虑以下限制：
+ 可扩展性有限，因为将来并非总是能够执行清理操作。
+ 可能需要执行耗时的 VACUUM 操作，以清理由此产生的死元组。
+ **写入新表** - 为将来的插入操作创建新表，并使用 `UNION ALL` 视图将旧表和新表的数据合并用于查询。该视图呈现来自旧表和新表的组合数据，使查询能够像访问单张表一样访问数据。请考虑以下限制：
+ 对旧表的更新仍可能导致 OID 耗尽问题。
+ **分区或分片** - 对表进行分区或对数据进行分片，以提升可扩展性和性能。请考虑以下限制：
+ 查询逻辑和维护复杂度增加，可能需要对应用程序进行修改，以正确处理分区或分片数据。
## 监控
### 使用系统表
您可以使用 PostgreSQL 的系统表来监控 OID 使用量的增长情况。
**警告**
根据 TOAST 表中 OID 的数量，此操作可能需要一定时间才能完成。建议在非工作时间安排监控，以将对业务的影响降到最低。
以下匿名块统计每个 TOAST 表中使用的不同 OID 数量，并显示其父表的信息：
```
DO $$
DECLARE
r record;
o bigint;
parent_table text;
parent_schema text;
BEGIN
SET LOCAL client_min_messages TO notice;
FOR r IN
SELECT
c.oid,
c.oid::regclass AS toast_table
FROM
pg_class c
WHERE
c.relkind = 't'
AND c.relowner != 10 LOOP
-- Fetch the number of distinct used OIDs (chunk IDs) from the TOAST table
EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.toast_table INTO o;
-- If there are used OIDs, find the associated parent table and its schema
IF o <> 0 THEN
SELECT
n.nspname,
c.relname INTO parent_schema,
parent_table
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.reltoastrelid = r.oid;
-- Raise a concise NOTICE message
RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Number of used OIDs: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999');
END IF;
END LOOP;
END
$$;
```
按照 TOAST 表显示 OID 使用情况统计信息的输出示例：
```
NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Number of used OIDs: 45,623,317
NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Number of used OIDs: 10,000
NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Number of used OIDs: 1,000,000
DO
```
以下匿名块检索为每个非空 TOAST 表分配的最大 OID：
```
DO $$
DECLARE
r record;
o bigint;
parent_table text;
parent_schema text;
BEGIN
SET LOCAL client_min_messages TO notice;
FOR r IN
SELECT
c.oid,
c.oid::regclass AS toast_table
FROM
pg_class c
WHERE
c.relkind = 't'
AND c.relowner != 10 LOOP
-- Fetch the max(chunk_id) from the TOAST table
EXECUTE 'SELECT max(chunk_id) FROM ' || r.toast_table INTO o;
-- If there's at least one TOASTed chunk, find the associated parent table and its schema
IF o IS NOT NULL THEN
SELECT
n.nspname,
c.relname INTO parent_schema,
parent_table
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.reltoastrelid = r.oid;
-- Raise a concise NOTICE message
RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Max chunk_id: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999');
END IF;
END LOOP;
END
$$;
```
显示 TOAST 表的最大块 ID 的输出示例：
```
NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Max chunk_id: 45,639,907
NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Max chunk_id: 45,649,929
NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Max chunk_id: 46,649,935
DO
```
### 使用性能详情
在需要分配新的对象标识符（OID）的操作过程中，性能详情中会显示 `LWLock:buffer_io` 和 `LWLock:OidGenLock` 等待事件。这些等待事件的平均活跃会话（AAS）较高通常表明在 OID 分配和资源管理过程中存在争用。这种情况在数据变更频繁、大型数据使用广泛或对象创建频繁的环境中尤为常见。
#### LWLock:buffer\$1io
`LWLock:buffer_io` 是一种等待事件，当 PostgreSQL 会话在等待共享缓冲区的 I/O 操作完成时会发生。通常在以下情况下出现：数据库从磁盘读取数据到内存中将修改后的页面从内存写回磁盘。`BufferIO` 等待事件通过在 I/O 操作进行时阻止多个进程访问或修改同一缓冲区，从而确保数据一致性。该等待事件频繁出现可能表明数据库工作负载中存在磁盘瓶颈或过高的 I/O 活动。
在 TOAST 操作期间：
+ PostgreSQL 会为大型对象分配 OID，并通过扫描 TOAST 表的索引来确保其唯一性。
+ 大型 TOAST 索引可能需要访问多个页面以验证 OID 的唯一性。当缓冲池无法缓存所有所需页面时，会导致磁盘 I/O 增加。
索引的大小直接影响在这些操作中需要访问的缓冲区页面数量。即使索引本身未膨胀，其体积也可能增加缓冲 I/O，尤其在高并发或数据变更频繁的环境中。有关更多信息，请参阅 [LWLock:BufferIO wait event troubleshooting guide](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html)。
#### LWLock:OidGenLock
`OidGenLock` 是一种等待事件，当 PostgreSQL 会话在等待分配新的对象标识符（OID）时发生。该锁确保 OID 按顺序且安全地生成，每次仅允许一个进程生成 OID。
在 TOAST 操作期间：
+ **TOAST 表中数据块的 OID 分配** – PostgreSQL 在管理大数据记录时，为 TOAST 表中的数据块分配 OID。每个 OID 必须唯一，以避免系统目录中发生冲突。
+ **高并发** – 由于访问 OID 生成器是顺序进行的，当多个会话同时创建需要 OID 的对象时，可能会发生 `OidGenLock` 争用，增加会话等待 OID 分配完成的概率。
+ **依赖系统目录访问** – 分配 OID 需要更新共享系统目录表，如 `pg_class` 和 `pg_type`。如果这些表活动频繁（例如频繁执行 DDL 操作），会加剧 `OidGenLock` 的锁争用。
+ **高 OID 分配需求** – 在 TOAST 工作负载中，处理大型数据记录需要持续分配 OID，从而增加争用。
额外导致 OID 争用的因素：
+ **频繁创建对象** – 经常创建和删除对象（例如临时表）的工作负载，会放大全局 OID 计数器的争用。
+ **全局计数器锁定** – 为确保唯一性，全局 OID 计数器采用串行访问机制，这在高并发环境中会形成单一争用点。
## 使用 RDS for PostgreSQL 支持的日志记录机制
您可以设置多个参数、扩展和其他可配置项来记录 PostgreSQL 数据库实例中发生的活动。这些功能包括：
+ `log_statement` 参数可用于记录 PostgreSQL 数据库中的用户活动。要了解有关 RDS for PostgreSQL 日志记录以及如何监控日志的更多信息，请参阅 [RDS for PostgreSQL 数据库日志文件](USER_LogAccess.Concepts.PostgreSQL.md)。
+ `rds.force_admin_logging_level` 参数记录数据库实例上的数据库中由 Amazon RDS 内部用户 (rdsadmin) 执行的操作。它将输出写入 PostgreSQL 错误日志。允许的值包括 `disabled`、`debug5`、`debug4`、`debug3`、`debug2`、`debug1`、`info`、`notice`、`warning`、`error`、日志、`fatal` 和 `panic`。默认值为 `disabled`。
+ 可以设置 `rds.force_autovacuum_logging_level` 参数以捕获 PostgreSQL 错误日志中的各种 autovacuum 操作。有关更多信息，请参阅 [记录 autovacuum 和 vacuum 活动](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md)。
+ 可以安装和配置 PostgreSQL Audit（pgAudit）扩展，以在会话级别或在对象级别捕获活动。有关更多信息，请参阅 [使用 pgAudit 记录数据库活动](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)。
+ `log_fdw` 扩展使您可以使用 SQL 访问数据库引擎日志。有关更多信息，请参阅 [使用 log\$1fdw 扩展通过 SQL 访问数据库日志](CHAP_PostgreSQL.Extensions.log_fdw.md)。
+ `pg_stat_statements` 库被指定为 RDS for PostgreSQL 版本 10 及更高版本中 `shared_preload_libraries` 参数的默认值。您可以使用该库分析正在运行的查询。确保在数据库参数组中设置 `pg_stat_statements`。有关使用此库提供的信息监控 RDS for PostgreSQL 数据库实例的更多信息，请参阅 [RDS PostgreSQL 的 SQL 统计数据](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.PostgreSQL.md)。
+ `log_hostname` 参数将每个客户端连接的主机名捕获到日志中。对于 RDS for PostgreSQL 版本 12 及更高版本，原定设置情况下，此参数设置为 `off`。如果您将其开启，请务必监视会话连接时间。开启后，该服务使用域名系统（DNS）反向查找请求来获取进行连接的客户端的主机名并将其添加到 PostgreSQL 日志中。这在会话连接期间会产生明显的影响。我们建议您仅出于故障排除目的开启此参数。
一般来说，日志记录的目的是使数据库管理员能够监控、优化性能和进行故障排除。许多日志都会自动上载到 Amazon CloudWatch 或 Performance Insights。在这里，对这些日志进行排序和分组，从而为您的数据库实例提供完整指标。要了解有关 Amazon RDS 监控和指标的更多信息，请参阅 [监控 Amazon RDS 实例中的指标](CHAP_Monitoring.md)。
# 使用 PostgreSQL 管理临时文件
在 PostgreSQL 中，一个复杂的查询可能会同时执行几个排序或哈希操作，每个操作都使用实例内存来存储结果，直至达到在 [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) 参数中指定的值。当实例内存不足时，会创建临时文件来存储结果。这些文件写入磁盘以完成查询执行。稍后，将在查询完成后自动删除这些文件。在 RDS for PostgreSQL 中，这些文件存储在数据卷上的 Amazon EBS 中。有关更多信息，请参阅 [Amazon RDS 数据库实例存储](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html)。您可以监控 CloudWatch 内发布的 `FreeStorageSpace` 指标，以确保数据库实例具有足够的可用存储空间。有关更多信息，请参阅 [https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm](https://repost.aws/knowledge-center/storage-full-rds-cloudwatch-alarm)。
对于涉及多个并发查询的工作负载（这会增加临时文件的使用量），我们建议使用 Amazon RDS 优化型读取实例。这些实例使用基于本地非易失性存储规范（NVMe）的固态硬盘（SSD）块级存储来存放临时文件。有关更多信息，请参阅 [使用 Amazon RDS 优化型读取功能提高 RDS for PostgreSQL 的查询性能](USER_PostgreSQL.optimizedreads.md)。
您可以使用下面的参数和函数来管理实例中的临时文件。
+ **[https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK](https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK)** – 此参数取消任何超过 temp\$1files 大小（以 KB 为单位）的查询。此限制可防止任何查询无休止地运行并使用临时文件消耗磁盘空间。您可以使用来自 `log_temp_files` 参数的结果来估计该值。作为最佳实践，请检查工作负载行为并根据估计值设置限制。以下示例显示了当查询超过限制时如何取消查询。
```
postgres=>select * from pgbench_accounts, pg_class, big_table;
```
```
ERROR: temporary file size exceeds temp_file_limit (64kB)
```
+ **[https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES)** – 当删除会话的临时文件时，此参数会向 postgresql.log 发送消息。此参数在查询成功完成后生成日志。因此，它可能无助于对长时间运行的活跃查询进行故障排除。
以下示例显示，当查询成功完成后，条目将记录在 postgresql.log 文件中，同时清理临时文件。
```
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
```
+ **[https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE)** – 此函数在 RDS for PostgreSQL 13 及更高版本中提供，可让用户了解当前临时文件使用情况。完成的查询不会出现在该函数的结果中。在以下示例中，您可以查看此函数的结果。
```
postgres=>select * from pg_ls_tmpdir();
```
```
name | size | modification
-----------------+------------+------------------------
pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00
pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00
pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00
pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00
pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00
pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00
pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00
(7 rows)
```
```
postgres=>select query from pg_stat_activity where pid = 8355;
query
----------------------------------------------------------------------------------------
select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid
(1 row)
```
文件名包括生成了临时文件的会话的处理 ID（PID）。更高级的查询（如以下示例所示）对每个 PID 的临时文件执行总和。
```
postgres=>select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;
```
```
pid | count | sum
------+-------------------
8355 | 2 | 2144501760
8351 | 2 | 2090770432
8327 | 1 | 1072250880
8328 | 2 | 2144501760
(4 rows)
```
+ **`[ pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html)`** – 如果您激活 pg\$1stat\$1sat\$1statements 参数，则可以查看每个调用的平均临时文件使用量。您可以识别查询的 query\$1id 并使用它来检查临时文件使用情况，如以下示例所示。
```
postgres=>select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';
```
```
queryid
----------------------
-7170349228837045701
(1 row)
```
```
postgres=>select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;
```
```
queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call
----------------------+---------------------------+-------+-------------------------+----------------------------
-7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678
(1 row)
```
+ **`[Performance Insights](https://aws.amazon.com/rds/performance-insights/)`** – 在性能详情控制面板中，可以通过开启指标 **temp\$1bytes** 和 **temp\$1files** 来查看临时文件使用情况。然后，您可以看到这两个指标的平均值，并查看它们与查询工作负载的对应关系。性能详情中的视图并未专门显示正在生成临时文件的查询。但是，当您将性能详情与针对 `pg_ls_tmpdir` 显示的查询相结合时，您可以排查、分析并确定查询工作负载的变化。
有关如何使用性能详情分析指标和查询的更多信息，请参阅[使用 Performance Insights 控制面板分析指标](USER_PerfInsights.UsingDashboard.md)。
有关使用性能详情查看临时文件使用情况的示例，请参阅[使用性能详情查看临时文件使用情况](PostgreSQL.ManagingTempFiles.Example.md)
# 使用性能详情查看临时文件使用情况
可以使用性能详情，通过开启指标 **temp\$1bytes** 和 **temp\$1files** 来查看临时文件使用情况。性能详情中的视图不显示生成临时文件的特定查询，但是，当您将性能详情与针对 `pg_ls_tmpdir` 显示的查询相结合时，可以排查、分析并确定查询工作负载的变化。
1. 在性能详情控制面板中，选择**管理指标**。
1. 选择**数据库指标**，然后选择 **temp\$1bytes** 和 **temp\$1files** 指标，如下图所示。
![\[图表中显示的指标。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_metrics.png)
1. 在 **Top SQL** 选项卡中，选择**首选项**图标。
1. 在**首选项**窗口中，打开 **Top SQL** 选项卡中显示的以下统计数据，然后选择**继续**。
+ 临时写入次数/秒
+ 临时读取次数/秒
+ 临时批量写入/调用
+ 临时批量读取/调用
1. 当临时文件与针对 `pg_ls_tmpdir` 显示的查询相组合时，临时文件将被分解，如以下示例所示。
![\[显示临时文件使用情况的查询。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/rpg_mantempfiles_query.png)
当您的工作负载中排名靠前的查询经常创建临时文件时，就会发生 `IO:BufFileRead` 和 `IO:BufFileWrite` 事件。通过查看“数据库负载”和“热门 SQL”部分中的平均活动会话（AAS），您可以使用性能详情来确定在 `IO:BufFileRead` 和 `IO:BufFileWrite` 上等待的热门 SQL。
![\[图中的 IO:BufFileRead 和 IO:BufFileWrite。\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/images/perfinsights_IOBufFile.png)
有关如何使用性能详情按等待事件分析热门查询和负载的更多信息，请参阅[“Top SQL”（主要 SQL）选项卡概览](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.md#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL)。您应该识别和调整导致临时文件使用量和相关等待事件增加的查询。有关这些等待事件和补救措施的更多信息，请参阅 [IO:BufFileRead 和 IO:BufFileWrite](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.iobuffile.html)。
**注意**
[https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM) 参数控制排序操作何时耗尽内存以及结果何时写入临时文件中。我们建议您不要将此参数的设置更改为高于默认值，因为这将允许每个数据库会话消耗更多内存。此外，执行复杂联接和排序的单个会话可以执行并行操作，其中每个操作都会消耗内存。
作为最佳实践，当您有一个包含多个联接和排序的大型报告时，请使用 `SET work_mem` 命令在会话级别设置此参数。然后，更改仅应用于当前会话，而不会全局更改该值。
## 将用于日志分析的 pgBadger 与 PostgreSQL 结合使用
您可以使用日志分析器（如 [pgBadger](http://dalibo.github.io/pgbadger/)）分析 PostgreSQL 日志。pgBadger 文档声明 %l 模式（会话或进程的日志行）应为前缀的一部分。但如果将当前 RDS `log_line_prefix` 作为参数提供给 pgBadger，它应该仍生成报告。
例如，以下命令使用 pgBadger 正确设置日期为 2014-02-04 的 Amazon RDS for PostgreSQL 日志文件的格式。
```
./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00
```
## 使用 PGSnapper 监控 PostgreSQL
可以使用 PGSnapper 协助定期收集 Amazon RDS for PostgreSQL 性能相关的统计数据和指标。有关更多信息，请参阅[使用 PGSnapper 监控 Amazon RDS for PostgreSQL 性能](https://aws.amazon.com/blogs/database/monitor-amazon-rds-for-postgresql-and-amazon-aurora-postgresql-performance-using-pgsnapper/)。
# 在 RDS for PostgreSQL 中管理自定义强制转换
PostgreSQL 中的**类型强制转换**是将值从一种数据类型转换为另一种数据类型的过程。PostgreSQL 为许多常见的转换提供了内置的强制转换，但您也可以创建自定义类型强制转换来定义特定类型转换的行为方式。
强制转换指定如何执行从一种数据类型到另一种数据类型的转换。例如，将文本 `'123'` 转换为整数 `123`，或将数值 `45.67` 转换为文本 `'45.67'`。
有关 PostgreSQL 强制转换概念和语法的全面信息，请参阅 [PostgreSQL CREATE CAST 文档](https://www.postgresql.org/docs/current/sql-createcast.html)。
从 RDS for PostgreSQL 版本 13.23、14.20、15.15、16.11、17.7 和 18.1 开始，您可以使用 rds\$1casts 扩展为内置类型安装其它强制转换，同时仍然可以为自定义类型创建自己的强制转换。
**Topics**
+ [
## 安装和使用 rds\$1casts 扩展
](#PostgreSQL.CustomCasts.Installing)
+ [
## 支持的强制转换
](#PostgreSQL.CustomCasts.Supported)
+ [
## 创建和删除强制转换
](#PostgreSQL.CustomCasts.Creating)
+ [
## 使用适当的上下文策略创建自定义强制转换
](#PostgreSQL.CustomCasts.BestPractices)
## 安装和使用 rds\$1casts 扩展
要创建 `rds_casts` 扩展，请以 `rds_superuser` 身份连接到 RDS for PostgreSQL 数据库实例，然后运行以下命令。
```
CREATE EXTENSION IF NOT EXISTS rds_casts;
```
## 支持的强制转换
在要使用自定义强制转换的每个数据库中创建此扩展。在创建扩展后，请使用下面的命令来查看所有可用的强制转换：
```
SELECT * FROM rds_casts.list_supported_casts();
```
此函数列出了可用的强制转换组合（源类型、目标类型、强制上下文和强制转换函数）。例如，如果您想要创建从 `text` 到 `numeric` 作为一个 `implicit` 强制转换。您可以使用以下查询来查找强制转换是否可供创建：
```
SELECT * FROM rds_casts.list_supported_casts()
WHERE source_type = 'text' AND target_type = 'numeric';
id | source_type | target_type | qualified_function | coercion_context
----+-------------+-------------+--------------------------------------+------------------
10 | text | numeric | rds_casts.rds_text_to_numeric_custom | implicit
11 | text | numeric | rds_casts.rds_text_to_numeric_custom | assignment
13 | text | numeric | rds_casts.rds_text_to_numeric_custom | explicit
20 | text | numeric | rds_casts.rds_text_to_numeric_inout | implicit
21 | text | numeric | rds_casts.rds_text_to_numeric_inout | assignment
23 | text | numeric | rds_casts.rds_text_to_numeric_inout | explicit
```
rds\$1casts 扩展为每个强制转换提供两种类型的强制转换函数：
+ *\$1inout 函数*：使用 PostgreSQL 的标准 I/O 转换机制，其行为与使用 INOUT 方法创建的强制转换完全相同
+ *\$1custom functions*：提供增强的转换逻辑来处理边缘情况，例如将空字符串转换为 NULL 值以避免转换错误
`inout` 函数复制了 PostgreSQL 的原生强制转换行为，而 `custom` 函数则通过处理标准 INOUT 强制转换无法应对的场景（例如将空字符串转换为整数）来扩展此功能。
## 创建和删除强制转换
您可以使用两种方法创建和删除支持的强制转换：
### 强制转换创建
**方法 1：使用原生 CREATE CAST 命令**
```
CREATE CAST (text AS numeric)
WITH FUNCTION rds_casts.rds_text_to_numeric_custom
AS IMPLICIT;
```
**方法 2：使用 rds\$1casts.create\$1cast 函数**
```
SELECT rds_casts.create_cast(10);
```
`create_cast` 函数从 `list_supported_casts()` 输出中获取 ID。此方法更简单，可确保您使用正确的函数和上下文组合。此 id 保证在不同的 postgres 版本中保持不变。
要验证是否成功创建了强制转换，请查询 pg\$1cast 系统目录：
```
SELECT oid, castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext, castmethod
FROM pg_cast
WHERE castsource = 'text'::regtype AND casttarget = 'numeric'::regtype;
oid | castsource | casttarget | castfunc | castcontext | castmethod
--------+------------+------------+--------------------------------------+-------------+------------
356372 | text | numeric | rds_casts.rds_text_to_numeric_custom | i | f
```
`castcontext` 列显示：`e` 表示 EXPLICIT、`a` 表示 ASSIGNMENT，或 `i` 表示 IMPLICIT。
### 删除强制转换
**方法 1：使用 DROP CAST 命令**
```
DROP CAST IF EXISTS (text AS numeric);
```
**方法 2：使用 rds\$1casts.drop\$1cast 函数**
```
SELECT rds_casts.drop_cast(10);
```
`drop_cast` 函数采用在创建强制转换时使用的相同 ID。此方法可确保您删除使用相应 ID 创建的确切强制转换。
## 使用适当的上下文策略创建自定义强制转换
为整数类型创建多个强制转换时，如果所有强制转换都以 IMPLICIT 形式创建，则可能会出现运算符歧义错误。以下示例通过创建两个从文本到不同整数宽度的隐式强制转换来演示这个问题：
```
-- Creating multiple IMPLICIT casts causes ambiguity
postgres=> CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS IMPLICIT;
CREATE CAST
postgres=> CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT;
CREATE CAST
postgres=> CREATE TABLE test_cast(col int);
CREATE TABLE
postgres=> INSERT INTO test_cast VALUES ('123'::text);
INSERT 0 1
postgres=> SELECT * FROM test_cast WHERE col='123'::text;
ERROR: operator is not unique: integer = text
LINE 1: SELECT * FROM test_cast WHERE col='123'::text;
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
```
之所以出现此错误，是因为在将整数列与文本值进行比较时，PostgreSQL 无法确定要使用哪种隐式强制转换。int4 和 int8 隐式强制转换都是有效的候选强制转换，这会造成歧义。
为避免这种运算符歧义，请使用 ASSIGNMENT 上下文表示较小的整数宽度，并使用 IMPLICIT 上下文表示较大的整数宽度：
```
-- Use ASSIGNMENT for smaller integer widths
CREATE CAST (text AS int2)
WITH FUNCTION rds_casts.rds_text_to_int2_custom(text)
AS ASSIGNMENT;
CREATE CAST (text AS int4)
WITH FUNCTION rds_casts.rds_text_to_int4_custom(text)
AS ASSIGNMENT;
-- Use IMPLICIT for larger integer widths
CREATE CAST (text AS int8)
WITH FUNCTION rds_casts.rds_text_to_int8_custom(text)
AS IMPLICIT;
postgres=> INSERT INTO test_cast VALUES ('123'::text);
INSERT 0 1
postgres=> SELECT * FROM test_cast WHERE col='123'::text;
col
-----
123
(1 row)
```
使用这种策略，只有 int8 强制转换是隐式的，因此 PostgreSQL 可以毫不歧义地确定要使用哪种强制转换。
# RDS for PostgreSQL 中并行查询的最佳实践
并行查询执行是 PostgreSQL 中的一项功能，支持将单个 SQL 查询分解为由多个后台工作进程同时处理的较小任务。PostgreSQL 可以将查询的某些部分（例如扫描、联接、聚合或排序）分配到多个 CPU 内核，而不是完全在单个后端进程中执行查询。*领导进程*协调此执行过程并从*并行工作线程*收集结果。
但是，对于大多数生产工作负载，尤其是高并发 OLTP 系统，我们建议禁用自动并行查询执行。虽然并行性可以加快对分析或报告工作负载中大型数据集的查询，但它会带来重大风险，在繁忙的生产环境中，这些风险往往大于收益。
并行执行还会带来大量开销。每个并行工作线程都是一个完整的 PostgreSQL 后端进程，它需要进程分叉（复制内存结构和初始化进程状态）和身份验证（占用 `max_connections` 限制中的连接槽）。每个工作线程还会消耗其自己的内存，包括用于排序和哈希操作的 `work_mem`，每个查询有多个工作线程，内存使用量会迅速成倍增加（例如，4 个工作线程 × 64 MB `work_mem` = 每个查询 256 MB）。因此，与单进程查询相比，并行查询消耗的系统资源可能要大得多。如果调整不当，它们可能会导致 CPU 饱和（多个工作线程使可用的处理能力不堪重负）、上下文切换增加（操作系统频繁在多个工作进程之间切换，增加开销并降低吞吐量）或连接耗尽（由于每个并行工作进程占用一个连接槽，因此包含 4 个工作线程的单个查询总共使用 5 个连接，1 个领导进程 \$1 4 个工作进程，这可能会在高并发下迅速耗尽连接池，从而防止新的客户端连接并导致应用程序故障）。在高并发工作负载下，多个查询可能会同时尝试并行执行，此时这些问题尤其严重。
PostgreSQL 根据成本估算来决定是否使用并行性。在某些情况下，如果并行计划看起来代价较低，即使在实践中并不理想，计划程序也可能会自动切换到并行计划。如果索引统计数据已过时，或者膨胀使顺序扫描看起来比索引查找更具吸引力，则可能会发生这种情况。由于这种行为，自动并行计划有时可能会导致查询性能或系统稳定性出现倒退。
要从 RDS for PostgreSQL 中的并行查询中获得最大优势，务必根据您的工作负载对这些并行查询进行测试和调优，监控系统影响，并禁用自动并行计划选择以支持查询级控制。
## 配置参数
PostgreSQL 使用多个参数来控制并行查询的行为和可用性。了解和调优这些参数对于实现可预测的性能至关重要：
| 参数 | 说明 | 默认值 |
| --- | --- | --- |
| max\$1parallel\$1workers | 总共可以运行的后台工作进程的最大数量 | GREATEST(\$1DBInstanceVCPU/2,8) |
| max\$1parallel\$1workers\$1per\$1gather | 每个查询计划节点的最大工作线程数（例如，每个 Gather） | 2 |
| parallel\$1setup\$1cost | 为启动并行查询基础设施而增加的计划程序成本 | 1000 |
| parallel\$1tuple\$1cost | 在并行模式下处理的每个元组的成本（影响计划程序决策） | 0.1 |
| force\$1parallel\$1mode | 强制计划程序测试并行计划（off、on、regress） | off |
### 重要注意事项
+ `max_parallel_workers` 控制并行工作线程的总池。如果设置得过低，则某些查询可能会回退到串行执行。
+ `max_parallel_workers_per_gather` 影响单个查询可以使用多少个工作线程。值较高会提高并发性，但也会增加资源使用量。
+ `parallel_setup_cost` 和 `parallel_tuple_cost` 影响计划程序的成本模型。降低这些值可以使并行计划更有可能被选中。
+ `force_parallel_mode` 对测试很有用，但除非必要，否则不应在生产环境中使用。
**注意**
`max_parallel_workers` 参数的默认值是使用公式 `GREATEST($DBInstanceVCPU/2, 8)` 根据实例大小动态计算得出的。这意味着，当您将数据库实例扩展到更大的计算大小和更多 vCPU 时，可用并行工作线程的最大数量将自动增加。因此，以前以串行方式或有限并行性执行的查询可能会在纵向扩展操作后突然利用更多的并行工作线程，从而可能导致连接使用率、CPU 利用率和内存消耗量意外增加。重要的是要在发生任何计算扩展事件后监控并行查询行为，并在必要时调整 `max_parallel_workers_per_gather` 以保持可预测的资源使用量。
## 确定并行查询使用情况
查询可能会根据数据分布或统计数据转变为并行计划。例如：
```
SELECT count(*) FROM customers WHERE last_login < now() - interval '6 months';
```
此查询可能使用最近数据的索引，但切换为对历史数据进行并行顺序扫描。
您可以通过加载 `auto_explain` 模块来记录查询执行计划。要了解更多信息，请参阅 AWS 知识中心中的[记录查询执行计划](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#)。
您可以监控 [CloudWatch 数据库洞察](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights-Database-Instance-Dashboard.html)，以了解与并行查询相关的等待事件。要详细了解与并行查询相关的等待事件，请浏览 [IPC:并行等待事件](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-ipc-parallel.html)
从 PostgreSQL 版本 18 开始，您可以使用 [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW) 和 [https://www.postgresql.org/docs/current/pgstatstatements.html](https://www.postgresql.org/docs/current/pgstatstatements.html) 中的新列来监控并行工作线程活动：
+ `parallel_workers_to_launch`：计划启动的并行工作线程数量
+ `parallel_workers_launched`：实际启动的并行工作线程数量
这些指标有助于识别计划的并行性与实际并行性之间的差异，这可能指示资源约束或配置问题。使用以下查询来监控并行执行：
对于数据库级别的并行工作线程指标：
```
SELECT datname, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_database
WHERE datname = current_database();
```
对于查询级别的并行工作线程指标
```
SELECT query, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_statements
ORDER BY parallel_workers_launched;
```
## 如何控制并行性
有多种方法可以控制查询并行性，每种方法均针对不同的场景和需求而设计。
要全局禁用自动并行性，请[修改参数组](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.Modifying.html)来设置：
```
max_parallel_workers_per_gather = 0;
```
对于用户特定的永久设置，ALTER ROLE 命令提供了一种设置参数的方法，这些参数将应用于特定用户的所有将来会话。
例如：
`ALTER ROLE username SET max_parallel_workers_per_gather = 4;` 可确保每次该用户连接到数据库时，其会话都将在需要时使用此并行工作线程设置。
可以使用 SET 命令来实现会话级控制，该命令可在当前数据库会话的持续时间内修改参数。当您需要在不影响其他用户或将来会话的情况下临时调整设置时，这特别有用。设置后，这些参数将保持有效，直到显式重置或会话结束。这些命令很简单：
```
SET max_parallel_workers_per_gather = 4;
-- Run your queries
RESET max_parallel_workers_per_gather;
```
为了实现更精细的控制，SET LOCAL 支持您修改单个事务的参数。当您需要调整事务中一组特定查询的设置时，这是理想之选，之后设置会自动恢复到以前的值。这种方法有助于防止对同一会话中的其它操作产生意外影响。
## 诊断并行查询行为
使用 `EXPLAIN (ANALYZE, VERBOSE)` 来确认查询是否使用了并行执行：
+ 查找诸如 `Gather`、`Gather Merge` 或 `Parallel Seq Scan` 之类的节点。
+ 比较有并行性和无并行性的计划。
要暂时禁用并行性以进行比较：
```
SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE ;
RESET max_parallel_workers_per_gather;
```
# 在 RDS for PostgreSQL 数据库实例上使用参数
在某些情况下，您可以在不指定自定义参数组的情况下创建 RDS for PostgreSQL 数据库实例。如果是这样，您的数据库实例将使用您选择的 PostgreSQL 版本的默认参数组创建。例如，假设您使用 PostgreSQL 13.3 创建 RDS for PostgreSQL 数据库实例。在这种情况下，数据库实例使用 PostgreSQL 13 版本 `default.postgres13` 的参数组中的值创建。
您还可以创建自己的自定义数据库参数组。如果要从其默认值修改 RDS for PostgreSQL 数据库实例的任何设置，则需要执行此操作。要了解如何操作，请参阅[Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。
您可以通过几种不同的方法跟踪 RDS for PostgreSQL 数据库实例的设置。您可以使用 AWS 管理控制台、AWS CLI 或 Amazon RDS API。也可以从实例的 PostgreSQL `pg_settings` 表中查询值，如下所示。
```
SELECT name, setting, boot_val, reset_val, unit
FROM pg_settings
ORDER BY name;
```
要了解此查询所返回值的更多相关信息，请参阅 PostgreSQL 文档中的 [https://www.postgresql.org/docs/current/view-pg-settings.html](https://www.postgresql.org/docs/current/view-pg-settings.html)。
更改 RDS for PostgreSQL 数据库实例上的 `max_connections` 和 `shared_buffers` 的设置时要特别小心。例如，假设您修改 `max_connections` 或 `shared_buffers` 的设置，并且您使用的值对于实际工作负载来说太高。在这种情况下，RDS for PostgreSQL 数据库实例将无法启动。如果发生这种情况，您会在 `postgres.log` 中看到如下错误。
```
2018-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory
2018-09-18 21:13:15 UTC::@:[8097]:HINT: This error usually means that PostgreSQL's request for a shared memory segment
exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
```
但是，您无法更改默认 RDS for PostgreSQL 数据库参数组中包含的任何设置值。要更改任何参数的设置，请首先创建自定义数据库参数组。然后，更改该自定义组中的设置，并将自定义参数组应用到 RDS for PostgreSQL 数据库实例。要了解更多信息，请参阅[Amazon RDS 的参数组](USER_WorkingWithParamGroups.md)。
RDS for PostgreSQL 中有两种类型的参数。
+ **静态参数**：静态参数需要在更改后重启 RDS for PostgreSQL 数据库实例，以便新值生效。
+ **动态参数**：动态参数在更改设置后不需要重启。
**注意**
如果 RDS for PostgreSQL 数据库实例使用您自己的自定义数据库参数组，则可以更改正在运行的数据库实例上动态参数的值。您可以通过使用 AWS 管理控制台、AWS CLI 或 Amazon RDS API 实现此目的。
如果您有这样做的权限，则还可使用 `ALTER DATABASE`、`ALTER ROLE` 和 `SET` 命令更改参数值。
## RDS for PostgreSQL 数据库实例参数列表
下表列出了 RDS for PostgreSQL 数据库实例中的一些（但并非全部）可用参数。要查看所有可用参数，请使用 AWS CLI 命令 [describe-db-parameters](https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-parameters.html)。例如，要获取 RDS for PostgreSQL 版本 13 的原定设置参数组中所有可用参数的列表，请运行以下命令。
```
aws rds describe-db-parameters --db-parameter-group-name default.postgres13
```
您还可以使用控制台。从 Amazon RDS 菜单中选择 **Parameter groups**（参数组），然后从您的 AWS 区域中的可用参数组中选择参数组。
| 参数名称 | 应用类型 | 描述 |
| --- | --- | --- |
| `application_name` | 动态 | 设置要在统计数据和日志中报告的应用程序名称。 |
| `archive_command` | 动态 | 设置将被调用以存档 WAL 文件的 Shell 命令。 |
| `array_nulls` | 动态 | 允许在阵列中输入 NULL 元素。 |
| `authentication_timeout` | 动态 | 设置允许完成客户端身份验证的最长时间。 |
| `autovacuum` | 动态 | 启动 Autovacuum 子进程。 |
| `autovacuum_analyze_scale_factor` | 动态 | analyze 之前插入、更新或删除元组的次数，以对于 reltuple 的占比计。 |
| `autovacuum_analyze_threshold` | 动态 | analyze 之前插入、更新或删除元组的最小次数。 |
| `autovacuum_freeze_max_age` | 静态 | 对表进行 Autovacuum 以防事务 ID 重叠的期限。 |
| `autovacuum_naptime` | 动态 | 两次 Autovacuum 运行之间的睡眠时间。 |
| `autovacuum_max_workers` | 静态 | 设置同时运行的 Autovacuum 工作者的最大数量。 |
| `autovacuum_vacuum_cost_delay` | 动态 | Autovacuum 的真空开销延迟，以毫秒计。 |
| `autovacuum_vacuum_cost_limit` | 动态 | Autovacuum 在小睡之前可用的真空开销量。 |
| `autovacuum_vacuum_scale_factor` | 动态 | vacuum 之前更新或删除元组的次数，以对于 reltuple 的占比计。 |
| `autovacuum_vacuum_threshold` | 动态 | vacuum 之前更新或删除元组的最小次数。 |
| `backslash_quote` | 动态 | 设置字符串字面值中是否允许有反斜杠 (\$1)。 |
| `bgwriter_delay` | 动态 | 后台写入实例在两轮之间的睡眠时间。 |
| `bgwriter_lru_maxpages` | 动态 | 后台写入实例每轮要刷新的最大 LRU 页数。 |
| `bgwriter_lru_multiplier` | 动态 | 每轮要释放的平均缓冲区用量的倍数。 |
| `bytea_output` | 动态 | 设置字节的输出格式。 |
| `check_function_bodies` | 动态 | 在 CREATE FUNCTION 期间检查函数体。 |
| `checkpoint_completion_target` | 动态 | 在检查点期间刷新脏缓冲区所用的时间，以对于检查点间隔的占比计。 |
| `checkpoint_segments` | 动态 | 设置日志段中自动预写日志 (WAL) 检查点之间的最大距离。 |
| `checkpoint_timeout` | 动态 | 设置自动 WAL 检查点之间的最长时间。 |
| `checkpoint_warning` | 动态 | 如果填充检查点段的频率高于此，则启用警告。 |
| `client_connection_check_interval` | 动态 | 设置在运行查询时检查断开连接的时间间隔。 |
| `client_encoding` | 动态 | 设置客户端的字符集编码。 |
| `client_min_messages` | 动态 | 设置发送到客户端的消息级别。 |
| `commit_delay` | 动态 | 设置事务提交与将 WAL 刷新到磁盘之间的延迟，以微秒计。 |
| `commit_siblings` | 动态 | 设置执行 commit\$1delay 之前同时打开的最少事务数。 |
| `constraint_exclusion` | 动态 | 使计划程序可使用约束优化查询。 |
| `cpu_index_tuple_cost` | 动态 | 设置计划程序对索引扫描期间处理每个索引条目的开销的估算。 |
| `cpu_operator_cost` | 动态 | 设置计划程序对处理每个运算符或函数调用的开销的估算。 |
| `cpu_tuple_cost` | 动态 | 设置计划程序对处理每个元组 (行) 的开销的估算。 |
| `cursor_tuple_fraction` | 动态 | 设置计划程序对光标将检索的行占比的估算。 |
| `datestyle` | 动态 | 设置日期和时间值的显示格式。 |
| `deadlock_timeout` | 动态 | 设置在检查死锁之前等待锁定的时间。 |
| `debug_pretty_print` | 动态 | 缩进分析树和计划树的显示内容。 |
| `debug_print_parse` | 动态 | 记录每个查询的分析树。 |
| `debug_print_plan` | 动态 | 记录每个查询的执行计划。 |
| `debug_print_rewritten` | 动态 | 记录每个查询重写的分析树。 |
| `default_statistics_target` | 动态 | 设置默认统计数据目标。 |
| `default_tablespace` | 动态 | 设置要从中创建表和索引的默认表空间。 |
| `default_transaction_deferrable` | 动态 | 设置新事务的默认可延迟状态。 |
| `default_transaction_isolation` | 动态 | 设置每个新事务的事务隔离级别。 |
| `default_transaction_read_only` | 动态 | 设置新事务的默认只读状态。 |
| `default_with_oids` | 动态 | 默认情况下，使用对象 ID (OID) 创建新表。 |
| `effective_cache_size` | 动态 | 设置计划程序对于磁盘缓存大小的假设。 |
| `effective_io_concurrency` | 动态 | 磁盘子系统可有效处理的并行请求数。 |
| `enable_bitmapscan` | 动态 | 使计划程序可使用位图扫描计划。 |
| `enable_hashagg` | 动态 | 使计划程序可使用哈希聚合计划。 |
| `enable_hashjoin` | 动态 | 使计划程序可使用哈希联接计划。 |
| `enable_indexscan` | 动态 | 使计划程序可使用索引扫描计划。 |
| `enable_material` | 动态 | 使计划程序可使用具体化。 |
| `enable_mergejoin` | 动态 | 使计划程序可使用合并联接计划。 |
| `enable_nestloop` | 动态 | 使计划程序可使用嵌套循环的联接计划。 |
| `enable_seqscan` | 动态 | 使计划程序可使用顺序扫描计划。 |
| `enable_sort` | 动态 | 使计划程序可使用显式排序步骤。 |
| `enable_tidscan` | 动态 | 使计划程序可使用 TID 扫描计划。 |
| `escape_string_warning` | 动态 | 警告在普通字符串字面值中有反斜杠 (\$1) 转义符。 |
| `extra_float_digits` | 动态 | 设置所显示的浮点值位数。 |
| `from_collapse_limit` | 动态 | 设置超出其即不折叠子查询的 FROM 列表大小。 |
| `fsync` | 动态 | 强制将更新同步到磁盘。 |
| `full_page_writes` | 动态 | 在检查点后首次修改时向 WAL 写入整页。 |
| `geqo` | 动态 | 启用基因查询优化。 |
| `geqo_effort` | 动态 | GEQO：用于设置其他 GEQO 参数默认值的工作负载。 |
| `geqo_generations` | 动态 | GEQO：算法的迭代次数。 |
| `geqo_pool_size` | 动态 | GEQO：群体中的个体数。 |
| `geqo_seed` | 动态 | GEQO：随机路径选择的种子。 |
| `geqo_selection_bias` | 动态 | GEQO：群体中的选择性压力。 |
| `geqo_threshold` | 动态 | 设置超出其即使用 GEQO 的 FROM 项阈值。 |
| `gin_fuzzy_search_limit` | 动态 | 通过允许由 GIN 进行的精确搜索得出的最大结果数。 |
| `hot_standby_feedback` | 动态 | 确定热备用将反馈消息发送到主备用还是上游备用。 |
| `intervalstyle` | 动态 | 设置间隔值的显示格式。 |
| `join_collapse_limit` | 动态 | 设置超出其即不平展 JOIN 结构的 FROM 列表大小。 |
| `lc_messages` | 动态 | 设置显示消息的语言。 |
| `lc_monetary` | 动态 | 设置用于使货币金额格式化的区域设置。 |
| `lc_numeric` | 动态 | 设置用于使数字格式化的区域设置。 |
| `lc_time` | 动态 | 设置用于使日期和时间值格式化的区域设置。 |
| `log_autovacuum_min_duration` | 动态 | 设置如超出即记录 Autovacuum 操作的最短运行时间。 |
| `log_checkpoints` | 动态 | 记录每个检查点。 |
| `log_connections` | 动态 | 记录每个成功的连接。 |
| `log_disconnections` | 动态 | 记录会话结束，包括持续时间。 |
| `log_duration` | 动态 | 记录每个完成的 SQL 语句的持续时间。 |
| `log_error_verbosity` | 动态 | 设置记录消息的详细程度。 |
| `log_executor_stats` | 动态 | 向服务器日志写入执行者性能统计数据。 |
| `log_filename` | 动态 | 设置日志文件的文件名模式。 |
| `log_file_mode` | 动态 | 设置日志文件的文件权限。默认值为 0644。 |
| `log_hostname` | 动态 | 在连接日志中记录主机名。从 PostgreSQL 12 及更高版本开始，原定设置情况下，此参数为“off”。开启后，连接使用 DNS 反向查找来获取捕获到连接日志的主机名。如果您启用此参数，则应监控它对建立连接所花费时间的影响。 |
| `log_line_prefix ` | 动态 | 控制每个日志行前缀的信息。 |
| `log_lock_waits` | 动态 | 记录长锁定等待次数。 |
| `log_min_duration_statement` | 动态 | 设置如超出即记录语句的最短运行时间。 |
| `log_min_error_statement` | 动态 | 导致记录所有产生此水平或此水平之上错误的语句。 |
| `log_min_messages` | 动态 | 设置记录的消息级别。 |
| `log_parser_stats` | 动态 | 向服务器日志写入分析器性能统计数据。 |
| `log_planner_stats` | 动态 | 向服务器日志写入计划程序性能统计数据。 |
| `log_rotation_age` | 动态 | 将在 N 分钟后进行日志文件自动轮换。 |
| `log_rotation_size` | 动态 | 将在 N KB 后进行日志文件自动轮换。 |
| `log_statement` | 动态 | 设置所记录的语句类型。 |
| `log_statement_stats` | 动态 | 向服务器日志写入累计性能统计数据。 |
| `log_temp_files` | 动态 | 记录对大于此 KB 数的临时文件的使用情况。 |
| `log_timezone` | 动态 | 设置要在日志消息中使用的时区。 |
| `log_truncate_on_rotation` | 动态 | 在日志轮换期间截断同名的现有日志文件。 |
| `logging_collector` | 静态 | 启动子进程将 stderr 输出和/或 csvlog 捕获到日志文件中。 |
| `maintenance_work_mem` | 动态 | 设置要用于维护操作的最大内存。 |
| `max_connections` | 静态 | 设置最大并行连接数。 |
| `max_files_per_process` | 静态 | 设置同时为每个服务器进程打开的最大文件数。 |
| `max_locks_per_transaction` | 静态 | 设置每个事务的最大锁定数。 |
| `max_pred_locks_per_transaction` | 静态 | 设置每个事务的最大谓词锁定数。 |
| `max_prepared_transactions` | 静态 | 设置同时准备的最大事务数。 |
| `max_stack_depth` | 动态 | 设置最大堆栈长度，以 KB 计。 |
| `max_standby_archive_delay` | 动态 | 设置在有热备用服务器处理已存档的 WAL 数据时取消查询之前的最大延迟。 |
| `max_standby_streaming_delay` | 动态 | 设置在有热备用服务器处理流式 WAL 数据时取消查询之前的最大延迟。 |
| max\$1wal\$1size | 动态 | 设置触发检查点的 WAL 大小（MB）。[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Parameters.html) 对 Amazon RDS for PostgreSQL 数据库实例使用以下命令可查看其当前值： SHOW max_wal_size;
|
| min\$1wal\$1size | 动态 | 设置将 WAL 缩小到的最小大小。对于 PostgreSQL 9.6 版及更早版本，min\$1wal\$1size 以 16 MB 为单位。对于 PostgreSQL 10 版及更高版本，min\$1wal\$1size 以 1 MB 为单位。 |
| `quote_all_identifiers` | 动态 | 在生成 SQL 片段时向所有标识符添加引号 (")。 |
| `random_page_cost` | 动态 | 设置计划程序对非连续提取磁盘页面的开销的估算。除非开启查询计划管理（QPM），否则此参数没有值。开启 QPM 时，此参数的默认值为 4。 |
| rds.adaptive\$1autovacuum | 动态 | 在超过事务 ID 阈值时自动优化 Autovacuum 参数。 |
| rds.force\$1ssl | 动态 | 要求使用 SSL 连接。对于 RDS for PostgreSQL 版本 15，默认值设为 1（开启）。所有其他 RDS for PostgreSQL 主要版本 14 及更早版本的默认值都设为 0（关闭）。 |
| `rds.local_volume_spill_enabled` | 静态 | 允许将逻辑溢出文件写入本地卷。 |
| `rds.log_retention_period` | 动态 | 设置日志保留，以便 Amazon RDS 删除超过 n 分钟的 PostgreSQL 日志。 |
| rds.rds\$1superuser\$1reserved\$1connections | 静态 | 设置为 rds\$1superuser 保留的连接插槽数。此参数仅在版本 15 及更高版本中提供。有关更多信息，请参阅 PostgreSQL 文档 [reserved\$1connections](https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-RESERVED-CONNECTIONS)。 |
| `rds.replica_identity_full` | 动态 | 当您将此参数设置为 `on` 时，它会将所有数据库表的副本标识设置覆盖为 `FULL`。这意味着，无论您的 `REPLICA IDENTITY FULL` 设置如何，所有列值都将写入预写日志（WAL）。 由于额外的 WAL 日志记录，开启此参数可能会增加数据库实例 IOPS。 |
| rds.restrict\$1password\$1commands | 静态 | 将可以管理密码的用户限制为具有 rds\$1password 角色的用户。将此参数设置为 1 可启用密码限制。默认值为 0。 |
| `search_path` | 动态 | 设置针对非架构限定名称的架构搜索顺序。 |
| `seq_page_cost` | 动态 | 设置计划程序对连续提取磁盘页面的开销的估算。 |
| `session_replication_role` | 动态 | 设置触发器和重写规则的会话行为。 |
| `shared_buffers` | 静态 | 设置服务器使用的共享内存缓冲区数。 |
| `shared_preload_libraries ` | 静态 | 列出要预加载到 RDS for PostgreSQL 数据库实例中的共享库。支持的值包括 auto\$1explain、orafce、pgaudit、pglogical、pg\$1bigm、pg\$1cron、pg\$1hint\$1plan、pg\$1prewarm、pg\$1similarity、pg\$1stat\$1statements、pg\$1tle、pg\$1transport、plprofiler 和 plrust。 |
| `ssl` | 动态 | 启用 SSL 连接。 |
| `sql_inheritance` | 动态 | 导致在各种命令中默认加入子表。 |
| `ssl_renegotiation_limit` | 动态 | 设置在重新协商加密密钥之前发送和接收的流量。 |
| `standard_conforming_strings` | 动态 | 导致 ... 字符串按字面处理反斜杠。 |
| `statement_timeout` | 动态 | 设置允许任何语句的最长持续时间。 |
| `synchronize_seqscans` | 动态 | 启用同步顺序扫描。 |
| `synchronous_commit` | 动态 | 设置当前事务同步级别。 |
| `tcp_keepalives_count` | 动态 | 重新传输 TCP 保持连接信号的最大次数。 |
| `tcp_keepalives_idle` | 动态 | 发出两次 TCP 保持连接信号之间的时间。 |
| `tcp_keepalives_interval` | 动态 | 两次 TCP 保持连接信号重新传输之间的时间。 |
| `temp_buffers` | 动态 | 设置每个会话使用的临时缓冲区的最大数量。 |
| temp\$1file\$1limit | 动态 | 设置临时文件可以达到的最大大小，以 KB 为单位。 |
| `temp_tablespaces` | 动态 | 选择用于临时表和排序文件的表空间。 |
| `timezone` | 动态 | 设置用于显示和解译时间戳的时区。 互联网编号分配机构（IANA）每年多次在 [https://www.iana.org/time-zones](https://www.iana.org/time-zones) 上发布新的时区。每当 RDS 发布 PostgreSQL 的新次要维护版本时，RDS 都会附带发布时的最新时区数据。当您使用最新的 RDS for PostgreSQL 版本时，您会获得来自 RDS 的最新时区数据。为确保您的数据库实例具有最新的时区数据，我们建议升级到更高的数据库引擎版本。您无法手动修改 PostgreSQL 数据库实例中的时区表。RDS 不会修改或重置正在运行的数据库实例的时区数据。只有在执行数据库引擎版本升级时，才会安装新的时区数据。 |
| `track_activities` | 动态 | 收集有关运行命令的信息。 |
| `track_activity_query_size` | 静态 | 设置为 pg\$1stat\$1activity.current\$1query 保留的大小，以字节计。 |
| `track_counts` | 动态 | 收集有关数据库活动的统计数据。 |
| `track_functions` | 动态 | 收集有关数据库活动的函数级别统计数据。 |
| `track_io_timing` | 动态 | 收集有关数据库活动的时序统计数据。 |
| `transaction_deferrable` | 动态 | 指示是否将某个只读可序列化事务推迟到启动它不会发生序列化失败时。 |
| `transaction_isolation` | 动态 | 设置当前事务隔离级别。 |
| `transaction_read_only` | 动态 | 设置当前事务只读状态。 |
| `transform_null_equals` | 动态 | 将 expr=NULL 视为 expr IS NULL。 |
| `update_process_title` | 动态 | 更新进程标题以显示活动的 SQL 命令。 |
| `vacuum_cost_delay` | 动态 | 真空开销延迟，以毫秒计。 |
| `vacuum_cost_limit` | 动态 | 小睡之前可用的真空开销量。 |
| `vacuum_cost_page_dirty` | 动态 | 由真空弄脏的页面的真空开销。 |
| `vacuum_cost_page_hit` | 动态 | 在缓冲区缓存中找到的页面的真空开销。 |
| `vacuum_cost_page_miss` | 动态 | 在缓冲区缓存中未找到的页面的真空开销。 |
| `vacuum_defer_cleanup_age` | 动态 | 真空和热清理应推迟的事务数 (如果有)。 |
| `vacuum_freeze_min_age` | 动态 | 真空应冻结表格行的最短期限。 |
| `vacuum_freeze_table_age` | 动态 | 真空应扫描整个表以冻结元组的期限。 |
| `wal_buffers` | 静态 | 设置 WAL 的共享内存中的磁盘页面缓冲区数。 |
| `wal_writer_delay` | 动态 | 两次 WAL 刷新之间的 WAL 写入实例睡眠时间。 |
| `work_mem` | 动态 | 设置要用于查询工作区的最大内存。 |
| `xmlbinary` | 动态 | 设置如何将二进制值编码到 XML 中。 |
| `xmloption` | 动态 | 设置要将隐式分析和序列化操作中的 XML 数据视为文档还是内容片段。 |
Amazon RDS 对所有参数均使用默认的 PostgreSQL 单位。下表显示每个参数的 PostgreSQL 原定设置单位。
| 参数名称 | 单位 |
| --- | --- |
| `archive_timeout` | s |
| `authentication_timeout` | s |
| `autovacuum_naptime` | s |
| `autovacuum_vacuum_cost_delay` | ms |
| `bgwriter_delay` | ms |
| `checkpoint_timeout` | s |
| `checkpoint_warning` | s |
| `deadlock_timeout` | ms |
| `effective_cache_size` | 8 KB |
| `lock_timeout` | ms |
| `log_autovacuum_min_duration` | ms |
| `log_min_duration_statement` | ms |
| `log_rotation_age` | 分钟 |
| `log_rotation_size` | KB |
| `log_temp_files` | KB |
| `maintenance_work_mem` | KB |
| `max_stack_depth` | KB |
| `max_standby_archive_delay` | ms |
| `max_standby_streaming_delay` | ms |
| `post_auth_delay` | s |
| `pre_auth_delay` | s |
| `segment_size` | 8 KB |
| `shared_buffers` | 8 KB |
| `statement_timeout` | ms |
| `ssl_renegotiation_limit` | KB |
| `tcp_keepalives_idle` | s |
| `tcp_keepalives_interval` | s |
| `temp_file_limit` | KB |
| `work_mem` | KB |
| `temp_buffers` | 8 KB |
| `vacuum_cost_delay` | ms |
| `wal_buffers` | 8 KB |
| `wal_receiver_timeout` | ms |
| `wal_segment_size` | B |
| `wal_sender_timeout` | ms |
| `wal_writer_delay` | ms |
| `wal_receiver_status_interval` | s |