【转自Amazon AWS】PostgreSQL 的常见数据库管理员任务

https://aws.amazon.com/cn/about-aws/whats-new/2019/06/amazon-aurora-with-postgresql-compatibility-supports-cluster-cache-management/

PostgreSQL 的常见数据库管理员任务

本节介绍运行 PostgreSQL 数据库引擎的数据库实例的某些数据库管理员常见任务的 Amazon RDS 实施。为了产生托管服务体验，Amazon RDS 不允许通过 shell 访问数据库实例，而仅限访问某些需要高级特权的系统过程和表。

有关在 Amazon RDS 上使用 PostgreSQL 日志文件的信息，请参阅 PostgreSQL 数据库日志文件。

主题

• 创建角色
• 管理 PostgreSQL 数据库访问
• 使用 PostgreSQL 参数
• 在 Amazon RDS 上使用 PostgreSQL Autovacuum
• PostgreSQL 数据库实例的审核日志记录
• 使用 pgaudit 扩展
• 使用 pg_repack 扩展
• 使用 PostGIS
• 将用于日志分析的 pgBadger 与 PostgreSQL 配合使用
• 查看 pg_config 的内容
• 使用 orafce 扩展
• 使用 postgres_fdw 扩展访问外部数据
• 将自定义 DNS 服务器用于出站网络访问
• 限制密码管理

创建角色

创建数据库实例时，将您创建的主用户系统账户分配给 rds_superuser 角色。rds_superuser 角色是一个预定义的 Amazon RDS 角色，它类似于 PostgreSQL 超级用户角色（在本地实例中习惯上称为 postgres），但有一些限制。与 PostgreSQL 超级用户角色类似，rds_superuser 角色对数据库实例享有最大特权。除非用户需要访问数据库实例的大部分内容，否则不应将此角色分配给用户。

rds_superuser 角色可以执行以下操作：

• 添加可用于 Amazon RDS 的扩展。有关更多信息，请参阅支持的 PostgreSQL 功能和 PostgreSQL 文档。

• 管理表空间，包括创建和删除它们。有关更多信息，请参阅 PostgreSQL 文档中的表空间部分。

• 使用 rds_superuser 命令查看所有未分配 pg_stat_activity 角色的用户并使用 pg_terminate_backend 和 pg_cancel_backend 命令断开其连接。

• 向所有非 rds_superuser 角色授予和撤消 rds_replication 角色。有关更多信息，请参阅 PostgreSQL 文档中的 GRANT 部分。

下例展示如何创建用户，然后向该用户授予 rds_superuser 角色。必须授予用户定义的角色，如 rds_superuser。

create role testuser with password 'testuser' login; CREATE ROLE grant rds_superuser to testuser; GRANT ROLE

管理 PostgreSQL 数据库访问

在 PostgreSQL 的 Amazon RDS 中，您可以管理哪些用户有特权连接到哪些数据库。在其他 PostgreSQL 环境中，有时需要通过修改 pg_hba.conf 文件来进行此类管理。在 Amazon RDS 中，您可以使用数据库授予。

在 PostgreSQL 中创建新数据库时总会带有默认的一组特权。这些默认特权使 PUBLIC（所有用户）能够连接数据库并在连接后创建临时表格。

若要控制哪些用户有权连接 Amazon RDS 中某一特定的数据库，首先撤销默认的 PUBLIC 特权。然后再以更细化的方式授予特权。下面的示例代码显示了授予方法。

psql> revoke all on database <database-name> from public; psql> grant connect, temporary on database <database-name> to <user/role name>;

有关 PostgreSQL 数据库中有关特权的更多信息，请参阅 PostgreSQL 文档中的 GRANT 命令。

使用 PostgreSQL 参数

postgresql.conf 文件中为本地 PostgreSQL 实例设置的 PostgreSQL 参数保留在数据库实例的数据库参数组中。如果使用默认参数组创建数据库实例，则参数设置在一个名为 default.postgres9.6 的参数组中。

创建数据库实例时，将加载关联的数据库参数组中的参数。可通过更改该参数组中的值，修改参数值。如果您有更改参数值的安全特权，则还可使用 ALTER DATABASE、ALTER ROLE 和 SET 命令执行此操作。无法使用命令行 postgres 命令或 env PGOPTIONS 命令，因为您无权访问主机。

偶尔可能难以跟踪 PostgreSQL 参数设置。使用以下命令列出当前的参数设置和默认值。

select name, setting, boot_val, reset_val, unit from pg_settings order by name;

有关输出值的解释，请参阅 PostgreSQL 文档中的 pg_settings 主题。

如果设置的内存设置对于 max_connections、shared_buffers 或 effective_cache_size 来说过大，则将使 PostgreSQL 实例无法启动。您可能不熟悉某些参数使用的单位；例如，shared_buffers 设置服务器使用的 8KB 共享内存缓冲区的数量。

当实例尝试启动，但错误的参数设置使其无法启动时，将以下错误写入 postgres.log 文件。

2013-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory 2013-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.

PostgreSQL 参数有两种类型，静态和动态。在应用静态参数之前，需要先重新启动数据库实例。动态参数可立即应用。下表显示可为 PostgreSQL 数据库实例修改的参数以及每个参数的类型。

参数名称	应用类型	描述
application_name	动态	设置要在统计数据和日志中报告的应用程序名称。
array_nulls	动态	允许在阵列中输入 NULL 元素。
authentication_timeout	动态	设置允许完成客户端身份验证的最长时间。
autovacuum	动态	启动 autovacuum 子进程。
autovacuum_analyze_scale_factor	动态	analyze 之前插入、更新或删除元组的次数，以对于 reltuple 的占比计。
autovacuum_analyze_threshold	动态	analyze 之前插入、更新或删除元组的最小次数。
autovacuum_naptime	动态	两次 autovacuum 运行之间的睡眠时间。
autovacuum_vacuum_cost_delay	动态	autovacuum 的真空开销延迟，以毫秒计。
autovacuum_vacuum_cost_limit	动态	autovacuum 在小睡之前可用的真空开销量。
autovacuum_vacuum_scale_factor	动态	vacuum 之前更新或删除元组的次数，以对于 reltuple 的占比计。
autovacuum_vacuum_threshold	动态	vacuum 之前更新或删除元组的最小次数。
backslash_quote	动态	设置字符串字面值中是否允许有反斜杠 (\)。
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_encoding	动态	设置客户端的字符集编码。
client_min_messages	动态	设置发送到客户端的消息级别。
commit_delay	动态	设置事务提交与将 WAL 刷新到磁盘之间的延迟，以微秒计。
commit_siblings	动态	设置执行 commit_delay 之前同时打开的最少事务数。
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	动态	创建默认具有 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	动态	警告在普通字符串字面值中有反斜杠 (\) 转义符。
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_hostname	动态	在连接日志中记录主机名。
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 数的临时文件的使用情况。
maintenance_work_mem	动态	设置要用于维护操作的最大内存。
max_stack_depth	动态	设置最大堆栈长度，以 KB 计。
max_standby_archive_delay	动态	设置在有热备用服务器处理已存档的 WAL 数据时取消查询之前的最大延迟。
max_standby_streaming_delay	动态	设置在有热备用服务器处理流式 WAL 数据时取消查询之前的最大延迟。
max_wal_size	Static	设置触发检查点的 WAL 大小。对于 PostgreSQL 9.6 版及更早版本，max_wal_size 以 16 MB 为单位。对于 PostgreSQL 10 版及更高版本，max_wal_size 以 1 MB 为单位。
min_wal_size	Static	设置将 WAL 缩小到的最小大小。对于 PostgreSQL 9.6 版及更早版本，min_wal_size 以 16 MB 为单位。对于 PostgreSQL 10 版及更高版本，min_wal_size 以 1 MB 为单位。
quote_all_identifiers	动态	在生成 SQL 片段时向所有标识符添加引号 (")。
random_page_cost	动态	设置计划程序对非连续提取磁盘页面的开销的估算。
rds.adaptive_autovacuum	动态	在超过事务 ID 阈值时自动优化 autovacuum 参数。
rds.log_retention_period	动态	设置日志保留，以便 Amazon RDS 删除超过 N 分钟的 PostgreSQL 日志。
rds.restrict_password_commands	Static	将可以管理密码的用户限制为具有 rds_password 角色的用户。将此参数设置为 1 可启用密码限制。默认值为 0。
search_path	动态	设置针对非架构限定名称的架构搜索顺序。
seq_page_cost	动态	设置计划程序对连续提取磁盘页面的开销的估算。
session_replication_role	动态	设置触发器和重写规则的会话行为。
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_tablespaces	动态	选择用于临时表和排序文件的表空间。
timezone	动态	设置用于显示和解译时间戳的时区。
track_activities	动态	收集有关执行命令的信息。
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_writer_delay	动态	两次 WAL 刷新之间的 WAL 写入实例睡眠时间。
work_mem	动态	设置要用于查询工作区的最大内存。
xmlbinary	动态	设置如何将二进制值编码到 XML 中。
xmloption	动态	设置要将隐式分析和序列化操作中的 XML 数据视为文档还是内容片段。
autovacuum_freeze_max_age	Static	对表进行 autovacuum 以防事务 ID 重叠的期限。
autovacuum_max_workers	Static	设置同时运行的 autovacuum 工作者的最大数量。
max_connections	Static	设置最大并行连接数。
max_files_per_process	Static	设置同时为每个服务器进程打开的最大文件数。
max_locks_per_transaction	Static	设置每个事务的最大锁定数。
max_pred_locks_per_transaction	Static	设置每个事务的最大谓词锁定数。
max_prepared_transactions	Static	设置同时准备的最大事务数。
shared_buffers	Static	设置服务器使用的共享内存缓冲区数。
ssl	Static	启用 SSL 连接。
temp_file_limit	Static	设置临时文件可以达到的最大大小，以 KB 为单位。
track_activity_query_size	Static	设置为 pg_stat_activity.current_query 保留的大小，以字节计。
wal_buffers	Static	设置 WAL 的共享内存中的磁盘页面缓冲区数。

Amazon RDS 对所有参数均使用默认的 PostgreSQL 单位。下表显示每个参数的 PostgreSQL 默认单位和值。

参数名称	单位
effective_cache_size	8 KB
segment_size	8 KB
shared_buffers	8 KB
temp_buffers	8 KB
wal_buffers	8 KB
wal_segment_size	8 KB
log_rotation_size	KB
log_temp_files	KB
maintenance_work_mem	KB
max_stack_depth	KB
ssl_renegotiation_limit	KB
temp_file_limit	KB
work_mem	KB
log_rotation_age	minutes
autovacuum_vacuum_cost_delay	ms
bgwriter_delay	ms
deadlock_timeout	ms
lock_timeout	ms
log_autovacuum_min_duration	ms
log_min_duration_statement	ms
max_standby_archive_delay	ms
max_standby_streaming_delay	ms
statement_timeout	ms
vacuum_cost_delay	ms
wal_receiver_timeout	ms
wal_sender_timeout	ms
wal_writer_delay	ms
archive_timeout	s
authentication_timeout	s
autovacuum_naptime	s
checkpoint_timeout	s
checkpoint_warning	s
post_auth_delay	s
pre_auth_delay	s
tcp_keepalives_idle	s
tcp_keepalives_interval	s
wal_receiver_status_interval	s

在 Amazon RDS 上使用 PostgreSQL Autovacuum

我们强烈建议您使用 PostgreSQL 数据库的 autovacuum 功能来保持 PostgreSQL 数据库实例正常运行。Autovacuum 自动执行 VACUUM 和 ANALYZE 命令。Autovacuum 检查包含大量插入的、更新的或删除的元组的表。然后，Autovacuum 通过从 PostgreSQL 数据库中删除过时的数据或元组来回收存储。

默认情况下，为所有新的 Amazon RDS PostgreSQL 数据库实例启用 Autovacuum，并且默认情况下将正确设置相关的 autovacuum 配置参数。由于默认值是某个通用值，因此，您可以从针对特定工作负载来优化参数中受益。以下部分可以帮助您执行所需的 autovacuum 优化。

主题

• 为 Autovacuum 分配内存
• 减少事务 ID 重叠的可能性
• 确定数据库中的表是否需要 Vacuum 操作
• 确定哪些表当前有资格经销 Autovacuum 操作
• 确定 Autovacuum 当前是否正在运行以及运行时长
• 执行手动 Vacuum 冻结
• 在 Autovacuum 正在运行时重新为表建立索引
• 其他影响 Autovacuum 的参数
• 设置表级别 Autovacuum 参数
• Autovacuum 日志记录

为 Autovacuum 分配内存

影响 autovacuum 性能的最重要参数之一是 maintenance_work_mem 参数。该参数确定您为 autovacuum 分配多少内存以用于扫描数据库表和保留将执行 vacuum 操作的所有行 ID。如果将 maintenance_work_mem 参数的值设得太小，则 vacuum 过程可能必须扫描表多次才能完成其工作。此类多次扫描可能会对性能产生负面影响。

在执行计算以确定 maintenance_work_mem 参数值时，需记住以下两点：

• 该参数的默认单位为 KB。

• maintenance_work_mem 参数与 autovacuum_max_workers 参数结合使用。如果您有多个小型表，请分配更多的 autovacuum_max_workers 和更少的 maintenance_work_mem。如果您拥有大型表（假设表的大小大于 100GB），则请分配更多内存和更少工作进程。您需要分配有足够的内存才能对最大的表成功完成操作。每个 autovacuum_max_workers 均可使用您分配的内存。因此，您应确保工作进程和内存的组合等于要分配的总内存。

一般来说，对于大型主机，将 maintenance_work_mem 参数设置为一个介于 1GB 和 2GB 之间（介于 1048576KB 和 2097152KB 之间）的值。对于特大型主机，将该参数设置为一个介于 2GB 和 4GB 之间（介于 2097152KB 和 4194304KB 之间）的值。为该参数设置的值应取决于工作负载。Amazon RDS 已将该参数的默认值更新为按以下方式计算的 KB 值：

GREATEST({DBInstanceClassMemory/63963136*1024},65536)。

减少事务 ID 重叠的可能性

在一些情况下，与 autovacuum 相关的参数组设置可能不够积极，无法阻止事务 ID 重叠。为解决此问题，Amazon RDS for PostgreSQL 提供了自动调整 autovacuum 参数值的机制。适应性 autovacuum 参数优化 是 RDS for PostgreSQL 9.4 版及更高版本的一项功能。在 PostgreSQL 文档中找到了 TransactionID 重叠的详细说明。

默认情况下，为动态参数 rds.adaptive_autovacuum 设置为 ON 的 RDS PostgreSQL 实例启用适应性 autovacuum 参数优化。强烈建议您保持启用此选项。不过，要关闭适应性 autovacuum 参数优化，请将 rds.adaptive_autovacuum参数设置为 0 或 OFF。

即使在 RDS 优化 autovacuum 参数时，仍可能出现事务 ID 重叠。鼓励您为事务 ID 重叠实施 Amazon CloudWatch 警报。有关更多信息，请参阅博客帖子在 Amazon RDS for PostgreSQL 中为事务 ID 重叠实施预警系统。

启用适应性 autovacuum 参数优化后，当 CloudWatch 指标 MaximumUsedTransactionIDs 达到以下值中的较大值时，RDS 将开始调整 autovacuum 参数：

• 750000000

• autovacuum 参数 autovacuum_freeze_max_age

如果表继续倾向于事务 ID 重叠，则 RDS 将继续调整 autovacuum 的参数。其中每次调整都会将更多资源专用于 autovacuum 以避免重叠。RDS 更新以下与 autovacuum 相关的参数：

• autovacuum_vacuum_cost_delay

• autovacuum_vacuum_cost_limit

• autovacuum_work_mem

• autovacuum_naptime

仅当新值使 autovacuum 更积极时，RDS 才会修改这些参数。在数据库实例上的内存中修改参数。不会更改参数组中的值。要查看当前内存中的设置，请使用 PostgreSQL SHOW SQL 命令。

当 RDS 修改其中任何 autovacuum 参数时，它会为 AWS 管理控制台 (https://console.aws.amazon.com/rds/) 上和通过 RDS API 可见的受影响的数据库实例生成事件。在 MaximumUsedTransactionIDs CloudWatch 指标返回的值低于阈值后，RDS 会将内存中与 autovacuum 相关的参数重置回参数组中指定的值并生成另一个与此更改对应的事件。

确定数据库中的表是否需要 Vacuum 操作

您可以使用以下查询显示数据库中未执行 vacuum 操作的事务的数目。数据库的 pg_database 行的 datfrozenxid 列是显示在该数据库中的正常事务 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 (autovacuum_freeze_max_age)。

示例监控策略可能类似于：

• 将 autovacuum_freeze_max_age 值设置为 2 亿个事务。

• 如果表达到 5 亿个未执行 vacuum 操作的事务，则这会触发低严重性警报。这不是一个不合理的值，但它可能指示 autovacuum 未保持同步。

• 如果表期限为 10 亿，这应被视为要采取操作的警报。通常，您出于性能原因，需要使期限更接近 autovacuum_freeze_max_age。建议您使用以下建议进行调查。

• 如果表达到 15 亿个未执行 vacuum 操作的事务，则这会触发高严重性警报。根据数据库使用事务 ID 的频率，此警报将指示系统运行 autovacuum 的时间不多了。在这种情况下，建议您立即解决此问题。

如果表持续违反这些阈值，则您需要进一步修改 autovacuum 参数。默认情况下，手动使用 VACUUM（已禁用基于成本的延迟）比使用默认的 autovacuum 更积极，但对整个系统来说也更具侵入性。

我们建议执行下列操作：

• 了解和启用监控机制，以便您了解最早的事务的期限。

  有关创建提醒您事务 ID 重叠的过程的信息，请参阅 AWS 数据库博客帖子在 Amazon RDS for PostgreSQL 中为事务 ID 重叠实施预警系统。

• 对于更复杂的表，在维护时段内定期执行手动 vacuum 冻结操作，并依赖 autovacuum。有关执行手动 vacuum 冻结的信息，请参阅 执行手动 Vacuum 冻结。

确定哪些表当前有资格经销 Autovacuum 操作

通常，它是需要执行 vacuum 操作的一个或两个表。其 relfrozenxid 值大于 autovacuum_freeze_max_age 中的事务数的表始终是 autovacuum 的目标。否则，如果元组数因上一个 VACUUM 超出“vacuum 阈值”而变得过时，则对表执行 vacuum 操作。

autovacuum 阈值的定义如下：

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

在连接到数据库时，运行以下查询可查看 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 -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC LIMIT 50;

确定 Autovacuum 当前是否正在运行以及运行时长

如果需要手动对表执行 vacuum 操作，您需要确定 autovacuum 当前是否正在运行。如果它当前正在运行，则您可能需要调整参数以使其更高效地运行，或者终止 autovacuum 以便手动运行 VACUUM。

使用以下查询来确定 autovacuum 是否正在运行、它已运行的时长以及它是否正在等待其他会话。

如果您使用的是 Amazon RDS PostgreSQL 9.6+ 或更高版本，请使用以下查询：

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; +

如果您使用的是低于 Amazon RDS PostgreSQL 9.6 的版本 (9.3.12 或更高版本、9.4.7 或更高版本或 9.5.2+ 除外)，请使用以下查询：

SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

在运行查询后，您应看到类似以下内容的输出。

datname | usename | pid | waiting | xact_runtime | query --------+----------+-------+---------+-------------------------+---------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | f | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | f | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | f | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | f | 00:00:00 | SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query+ | | | | | FROM pg_stat_activity + | | | | | WHERE query like '%VACUUM%' + | | | | | ORDER BY xact_start; +

有多个问题可能会导致长时间运行 autovacuum 会话（即，多天）。最常见的问题是，对于表的大小或更新速率来说，设置的 maintenance_work_mem 参数值太小。

建议您使用以下公式来设置 maintenance_work_mem 参数值。

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

短时间运行的 autovacuum 会话还可以指示以下问题：

• 它可以指示，对于工作负载而言，autovacuum_max_workers 不足。在这种情况下，您将需要指示工作线程数。

• 它可以指示存在索引损坏（autovacuum 将发生崩溃并在同一关系上重新启动，但毫无进展）。在这种情况下，运行手动 vacuum 冻结详细 ___table___ 以查看准确原因。

执行手动 Vacuum 冻结

您可能需要对已具有正在运行的 vacuum 进程的表执行手动 vacuum 操作。如果您已使用接近 20 亿个事务（或高于您监控的任何阈值）的期限标识表，则这会很有用。

以下步骤是一个指南，并且此过程存在几种变化。例如，在测试期间，假设您发现设定的 maintenance_work_mem 参数值过小，并且您需要立即对表采取措施。不过，可能您不希望此时恢复实例。通过使用前几节中的查询，您可以确定哪个表存在问题，并找到长时间运行的 autovacuum 会话。您知道您需要更改 maintenance_work_mem 参数设置，但您还需要立即采取行动，对有问题的表执行 vacuum 操作。以下过程说明了在此情况下应采取的措施：

手动执行 vacuum 冻结

1. 打开针对包含要执行 vacuum 操作的表的数据库的两个会话。对于第二个会话，使用“screen”或其他维护会话的实用工具 (如果您的连接已中断)。

2. 在第一个会话中，获取正在表上运行的 autovacuum 会话的 PID。此操作要求您运行的是 Amazon RDS PostgreSQL 9.3.12 或更高版本、9.4.7 或更高版本或者 9.5.2 或更高版本以便完全了解正在运行的 rdsadmin 过程。

   运行以下查询可获取 autovacuum 会话的 PID。

   SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

3. 在第二个会话中，计算该操作所需的内存量。在此示例中，我们确定自己最多可以为该操作使用 2GB 的内存，因此，我们将当前会话的 maintenance_work_mem 设置为 2 GB。

   set maintenance_work_mem='2 GB'; SET

4. 在第二个会话中，为表发出 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

5. 在第一个会话中，如果 autovacuum 被阻止，您将在 pg_stat_activity 中看到 vacuum 会话的等待为“T”。在此情况下，您需要终止 autovacuum 过程，如下所示。

   SELECT pg_terminate_backend('the_pid');

6. 此时，您的会话将开始。由于此表可能位于其工作列表中的最高位置，因此，了解 autovacuum 将立即重新启动很重要。在第 2 个会话中启动您的 vacuum freeze verbose 命令，然后终止第 1 个会话中的 autovacuum 过程。

在 Autovacuum 正在运行时重新为表建立索引

如果索引已损坏，autovacuum 将继续处理表并失败。在此情况下，如果您尝试执行手动 vacuum 操作，您将收到一条与以下内容类似的错误消息：

mydb=# vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.

当索引损坏并且 autovacuum 尝试对表运行时，您将处理已经正在运行的 autovacuum 会话。在您发出“REINDEX”命令时，将删除表上的排他锁。将阻止写入操作以及使用该特定索引的读取操作。

在对表运行 autovacuum 时重新为表建立索引

1. 打开针对包含要执行 vacuum 操作的表的数据库的两个会话。对于第二个会话，使用“screen”或其他维护会话的实用工具 (如果您的连接已中断)。

2. 在第一个会话中，获取正在表上运行的 autovacuum 会话的 PID。此操作要求您运行的是 Amazon RDS PostgreSQL 9.3.12 或更高版本、9.4.7 或更高版本或者 9.5.2 或更高版本以便完全了解正在运行的 rdsadmin 过程。

   运行以下查询可获取 autovacuum 会话的 PID：

   SELECT datname, usename, pid, waiting, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;

3. 在第二个会话中，发出 reindex 命令.

   \timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms

4. 在第一个会话中，如果 autovacuum 被阻止，您将在 pg_stat_activity 中看到 vacuum 会话的等待为“T”。在此情况下，您将需要终止 autovacuum 过程。

   select pg_terminate_backend('the_pid');

5. 此时，您的会话将开始。由于此表可能位于其工作列表中的最高位置，因此，了解 autovacuum 将立即重新启动很重要。在第 2 个会话中启动您的命令，然后终止第 1 个会话中的 autovacuum 过程。

其他影响 Autovacuum 的参数

以下查询将显示直接影响 autovacuum 及其行为的一些参数的值。PostgreSQL 文档中完整介绍了 autovacuum 参数。

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_mork_mem

• autovacuum_freeze_max_age

• autovacuum_max_workers

• autovacuum_vacuum_cost_delay

• Autovacuum_vacuum_cost_limit

设置表级别 Autovacuum 参数

可在表级别设置与 Autovacuum 相关的存储参数，这可能优于更改整个数据库的行为。对于大型表，您可能需要设置主动设置，并且可能不希望 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 操作的更多详细信息。

Autovacuum 日志记录

默认情况下，postgresql.log 不包含有关 autovacuum 过程的信息。如果您使用的是 PostgreSQL 9.4.5 或更高版本，则可通过设置 rds.force_autovacuum_logging_level 参数来在从 autovacuum 工作线程操作生成的 PostgreSQL 错误日志中查看输出。允许的值包括 disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, 和 panic。默认值为 disabled，因为其他允许的值会向日志添加大量信息。

建议您将 rds.force_autovacuum_logging_level 参数的值设置为 log，并将 log_autovacuum_min_duration 参数的值设置为 1000 到 5000 毫秒。如果您将此值设置为 5000，则 Amazon RDS 会将所需时间多于 5 秒的任何活动写入日志。在应用程序锁定导致 autovacuum 故意跳过表时，它还会显示“vacuum skipped (已跳过 vacuum)”消息。如果您解决问题并且需要更多详细信息，您可以使用其他日志记录级别值，例如 debug1 或 debug3。由于这些设置会生成写入到错误日志文件中的非常详细的内容，因此，使用这些调试参数一小段时间。有关这些调试设置的更多信息，请参阅PostgreSQL 文档。

注意

PostgreSQL 9.4.7 版及更高版本通过允许 rds_superuser 账户查看 pg_stat_activity 中的 autovacuum 会话来提高 autovacuum 会话的可见性。例如，您可识别并终止阻止命令运行或执行慢于手动发出的真空命令的 autovacuum 会话。

PostgreSQL 数据库实例的审核日志记录

您可以设置几个参数来记录 PostgreSQL 数据库实例中发生的活动。这些参数包括：

• log_statement 参数可用于记录 PostgreSQL 数据库中的用户活动。有关更多信息，请参阅 PostgreSQL 数据库日志文件。

• rds.force_admin_logging_level 参数记录数据库实例上的数据库中由 RDS 内部用户 (rdsadmin) 执行的操作，并将输出写入到 PostgreSQL 错误日志。允许的值包括：disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal 和 panic。默认值是 disabled。

• rds.force_autovacuum_logging_level 参数记录数据库实例上所有数据库中的 autovacuum 工作人员操作，并将输出写入 PostgreSQL 错误日志。允许的值包括：disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal 和 panic。默认值是 disabled。Amazon RDS 对 rds.force_autovacuum_logging_level 的推荐设置是 LOG。请将 log_autovacuum_min_duration 设置为 1000 到 5000 的值。将此值设置为 5000 会将所需时间多于 5 秒的活动写入日志，并显示“vacuum skipped (已跳过 vacuum)”消息。有关该参数的更多信息，请参阅 使用 PostgreSQL 的最佳实践。

使用 pgaudit 扩展

pgaudit 扩展提供 Amazon RDS for PostgreSQL 9.6.3 版和更高版本和 9.5.7 版和更高版本的详细会话和对象审核日志记录。您可以使用此扩展启用会话审核或对象审核。

通过会话审核，您可以记录来自各种来源的审核事件，并在完全限定的命令文本可用时包括该文本。例如，可以通过将 pgaudit.log 设置为“READ”，使用会话审核记录连接到数据库的所有 READ 语句。

通过对象审核，您可以细化审核日志记录以使用特定的命令。例如，您可以指定要审核特定数量表上的 READ 操作的日志记录。

将基于对象的日志记录与 pgaudit 扩展一起使用

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

   CREATE ROLE rds_pgaudit; CREATE ROLE

2. 修改与数据库实例关联的参数组，以使用包含 pgaudit 的共享预加载库并设置参数 pgaudit.role。pgaudit.role 必须设置为角色 rds_pgaudit。

   以下命令修改自定义参数组。

   aws rds modify-db-parameter-group --db-parameter-group-name rds-parameter-group-96 --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" --region us-west-2

3. 重启实例以便数据库实例接收对参数组所做的更改。以下命令重启数据库实例。

   aws rds reboot-db-instance --db-instance-identifier rds-test-instance --region us-west-2

4. 运行以下命令确认 pgaudit 已初始化。

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

5. 运行以下命令创建 pgaudit 扩展。

   CREATE EXTENSION pgaudit; CREATE EXTENSION

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

   show pgaudit.role; pgaudit.role ------------------ rds_pgaudit

要测试审核日志记录，请运行您选择审核的多个命令。例如，您可以运行以下命令。

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

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

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

有关查看日志的信息，请参阅Amazon RDS 数据库日志文件。

使用 pg_repack 扩展

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

若要使用 pg_repack 扩展

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

   CREATE EXTENSION pg_repack;

2. 使用 pg_repack 客户端实用工具连接到数据库。使用具有 rds_superuser 权限的数据库角色连接到数据库。在以下连接示例中，rds_test 角色具有 rds_superuser 权限，使用的数据库终端节点是 rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com。

   pg_repack -h rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com -U rds_test -k postgres

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

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

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

使用 PostGIS

PostGIS 是一个 PostgreSQL 扩展，用于存储和管理空间信息。如果不熟悉 PostGIS，您可以在 PostGIS 简介中了解概要信息。

您需要执行一些设置才能使用 PostGIS 扩展。以下列表显示需要执行的操作；本节稍后将更详细地介绍每个步骤。

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

• 加载 PostGIS 扩展.

• 将扩展的所有权移交给 rds_superuser 角色。

• 将对象的所有权移交给 rds_superuser 角色.

• 测试扩展.

步骤 1：使用用于创建数据库实例的主用户名连接到数据库实例

首先，使用用于创建数据库实例的主用户名连接到数据库实例。将自动为该名称分配 rds_superuser 角色。您需要执行剩余步骤所需的 rds_superuser 角色。

以下示例使用 SELECT 显示当前用户；在这种情况下，当前用户应为您在创建数据库实例时选择的主用户名。

select current_user; current_user ------------- myawsuser (1 row)

步骤 2：加载 PostGIS 扩展

使用 CREATE EXTENSION 语句加载 PostGIS 扩展。还必须加载 扩展。然后，即可使用 \dn psql 命令列出 PostGIS 架构的所有者。

create extension postgis; CREATE EXTENSION create extension fuzzystrmatch; CREATE EXTENSION create extension postgis_tiger_geocoder; CREATE EXTENSION create extension postgis_topology; CREATE EXTENSION \dn List of schemas Name | Owner --------------+----------- public | myawsuser tiger | rdsadmin tiger_data | rdsadmin topology | rdsadmin (4 rows)

步骤 3：将扩展的所有权移交给 rds_superuser 角色

使用 ALTER SCHEMA 语句将架构的所有权移交给 rds_superuser 角色。

alter schema tiger owner to rds_superuser; ALTER SCHEMA alter schema tiger_data owner to rds_superuser; ALTER SCHEMA alter schema topology owner to rds_superuser; ALTER SCHEMA \dn List of schemas Name | Owner --------------+--------------- public | myawsuser tiger | rds_superuser tiger_data | rds_superuser topology | rds_superuser (4 rows)

步骤 4：将对象的所有权移交给 rds_superuser 角色

使用以下函数将 PostGIS 对象的所有权移交给 rds_superuser 角色。从 psql 提示符处运行以下语句以创建此函数。

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;

接下来，运行此查询以运行 exec 函数，该函数进而将执行语句并更改权限。

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;') FROM ( SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE nspname in ('tiger','topology') AND relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

步骤 5：测试扩展

使用以下命令将 tiger 添加到搜索路径中。

SET search_path=public,tiger;

使用以下 SELECT 语句测试 tiger。

select na.address, na.streetname, na.streettypeabbrev, na.zip from normalize_address('1 Devonshire Place, Boston, MA 02109') as na; address | streetname | streettypeabbrev | zip ---------+------------+------------------+------- 1 | Devonshire | Pl | 02109 (1 row)

使用以下 SELECT 语句测试 topology。

select topology.createtopology('my_new_topo',26986,0.5); createtopology ---------------- 1 (1 row)

将用于日志分析的 pgBadger 与 PostgreSQL 配合使用

可以使用日志分析器（如 pgbadger）分析 PostgreSQL 日志。pgbadger 文档声明 %l 模式 (会话/进程的日志行) 应为前缀的一部分。但如果将当前 rds log_line_prefix 作为参数提供给 pgbadger，则它仍将生成报告。

例如，以下命令使用 pgbadger 正确设置日期为 2014-02-04 的 Amazon RDS PostgreSQL 日志文件的格式。

./pgbadger -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00

查看 pg_config 的内容

在 PostgreSQL 版本 9.6.1 中，您可以使用新的 pg_config 视图，看到当前安装的 PostgreSQL 版本的编译时间配置参数。您可以通过调用 pg_config 函数来使用该视图，如下例所示。

select * from pg_config(); name | setting -------------------+--------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- BINDIR | /rdsdbbin/postgres-9.6.1.R1/bin DOCDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc HTMLDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc INCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include PKGINCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.1.R1/include/server LIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib PKGLIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib LOCALEDIR | /rdsdbbin/postgres-9.6.1.R1/share/locale MANDIR | /rdsdbbin/postgres-9.6.1.R1/share/man SHAREDIR | /rdsdbbin/postgres-9.6.1.R1/share SYSCONFDIR | /rdsdbbin/postgres-9.6.1.R1/etc PGXS | /rdsdbbin/postgres-9.6.1.R1/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE | '--prefix=/rdsdbbin/postgres-9.6.1.R1' '--with-openssl' '--with-perl' '--with-tcl' '--with-ossp-uuid' '--with-libxml' '--with-libraries=/rdsdbbin /postgres-9.6.1.R1/lib' '--with-includes=/rdsdbbin/postgres-9.6.1.R1/include' '--enable-debug' CC | gcc CPPFLAGS | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.1.R1/include CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict- aliasing -fwrapv -fexcess-precision=standard -g -O2 CFLAGS_SL | -fpic LDFLAGS | -L../../src/common -L/rdsdbbin/postgres-9.6.1.R1/lib -Wl,--as-needed -Wl, -rpath,'/rdsdbbin/postgres-9.6.1.R1/lib',--enable-new-dtags LDFLAGS_EX | LDFLAGS_SL | LIBS | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm VERSION | PostgreSQL 9.6.1 (23 rows)

如果您尝试直接访问该视图，则请求会失败。

select * from pg_config; ERROR: permission denied for relation pg_config

使用 orafce 扩展

orafce 扩展提供商业数据库中常用的函数，并且可以更轻松地将商业数据库移植到 PostgreSQL。Amazon RDS for PostgreSQL 9.6.6 以及更高版本支持该扩展。有关 orafce 的更多信息，请参阅 GitHub 上的 orafce 项目。

注意

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

使用 orafce 扩展

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

   注意

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

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

   CREATE EXTENSION orafce;

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

   ALTER SCHEMA oracle OWNER TO rds_superuser;

   注意

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

使用 postgres_fdw 扩展访问外部数据

您可以使用 postgres_fdw 扩展访问远程数据库服务器上表中的数据。如果您从 PostgreSQL 数据库实例设置远程连接，则访问还可用于您的只读副本。

使用 postgres_fdw 访问远程数据库服务器

1. 安装 postgres_fdw 扩展。

   CREATE EXTENSION postgres_fdw;

2. 使用 CREATE SERVER 创建外部数据服务器。

   CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');

3. 创建用户映射，用于标识将在远程服务器上使用的角色。

   CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');

4. 创建一个表，该表映射到远程服务器上的表。

   CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');

将自定义 DNS 服务器用于出站网络访问

Amazon RDS for PostgreSQL 支持您数据库实例上的出站网络访问，并允许从客户拥有的自定义 DNS 服务器进行域名服务 (DNS) 解析。通过自定义 DNS 服务器，您可以仅解析 Amazon RDS 数据库实例中的完全限定域名。

主题

• 启用自定义 DNS 解析
• 禁用自定义 DNS 解析
• 设置自定义 DNS 服务器

启用自定义 DNS 解析

要在您的客户 VPC 中启用 DNS 解析，您需要将自定义数据库参数组与 RDS PostgreSQL 实例相关联，通过将参数 rds.custom_dns_resolution 设置为 1 来启动参数，并重新启动数据库实例以使更改生效。

禁用自定义 DNS 解析

要在您的客户 VPC 中禁用 DNS 解析，您需要通过将参数 rds.custom_dns_resolution 设置为 0 来禁用参数，然后重新启动数据库实例以使更改生效。

设置自定义 DNS 服务器

自定义 DNS 名称服务器设置完成后，它需要 30 分钟将更改传播到数据库实例中。将更改传播到数据库实例后，需要进行 DNS 查找的所有出站网络流量将通过端口 53 查询您的 DNS 服务器。

要为您的 Amazon RDS PostgreSQL 数据库实例设置自定义 DNS 服务器，请执行以下操作：

1. 在附加到 VPC 的 DHCP 选项集中，将 domain-name-servers 选项设置为您的 DNS 名称服务器的 IP 地址。有关更多信息，请参阅 DHCP 选项集。

   注意

   domain-name-servers 选项最多可接受四个值，但您的 Amazon RDS 数据库实例只使用第一个值。

2. 请确保您的 DNS 服务器可以解析所有查找查询，包括公有 DNS 名称、Amazon EC2 私有 DNS 名称和特定客户的 DNS 名称。如果出站网络流量中包含您的 DNS 服务器不能处理的 DNS 查找，您的 DNS 服务器必须已配置适当的上游 DNS 提供程序。

3. 请配置您的 DNS 服务器，以生成等于或小于 512 字节的用户数据报协议 (UDP) 响应。

4. 请配置您的 DNS 服务器，以生成等于或小于 1024 字节的传输控制协议 (TCP) 响应。

5. 配置您的 DNS 服务器，允许流量通过 Amazon RDS 数据库实例的端口 53 入站。如果您的 DNS 服务器在 Amazon VPC 中，此 VPC 的安全组必须包含以下入站规则：允许端口 53 上的 UDP 和 TCP 流量。如果您的 DNS 服务器不在 Amazon VPC 中，它必须具有适当的防火墙白名单，允许端口 53 上的 UDP 和 TCP 入站流量。

   有关更多信息，请参阅您的 VPC 的安全组和添加和删除规则。

6. 配置您的 Amazon RDS 数据库实例的 VPC，允许流量通过端口 53 出站。您的 VPC 安全组必须包含以下出站规则：允许端口 53 上的 UDP 和 TCP 流量。

   有关更多信息，请参阅您的 VPC 的安全组和添加和删除规则。

7. 必须正确配置 Amazon RDS 数据库实例和 DNS 服务器之间的路由路径，允许 DNS 流量。

   如果 Amazon RDS 数据库实例和 DNS 服务器不在同一 VPC 中，必须在它们之间设置对等连接。有关更多信息，请参阅什么是 VPC 对等连接？

限制密码管理

您可以限制只有特殊角色的用户才能够管理数据用户密码。通过这样做，您可以在客户端更好地控制密码管理。

您可以通过静态参数 rds.restrict_password_commands 并使用名为 rds_password 的角色来启用受限制的密码管理。当参数 rds.restrict_password_commands 设置为 1 时，只有是 rds_password 角色成员的用户可以运行特定 SQL 命令。受限制的 SQL 命令包括修改数据库用户密码以及修改密码到期时间的命令。

要使用受限制的密码管理，您的数据库实例必须运行 Amazon RDS for PostgreSQL 10.6 或更高版本。由于 rds.restrict_password_commands 参数是静态参数，更改此参数后需要重新启动数据库。

在数据库启用了受限制的密码管理后，如果您尝试运行受限制 SQL 命令，则会收到以下错误：ERROR: must be a member of rds_password to alter passwords (错误：必须是 rds_password 成员才能变更密码)。

以下是启用了受限制的密码管理时的一些受限制 SQL 命令示例。

postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword'; postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword'; postgres=> ALTER ROLE myrole VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole RENAME TO myrole2;

一些包含 RENAME TO 的 ALTER ROLE 命令也会受限制。因为重命名具有 MD5 密码的 PostgreSQL 角色会清除密码，因此这些命令会受限制。

默认情况下，rds_superuser 角色具有 rds_password 角色的成员资格，并且无法更改。您可以使用 GRANT SQL 命令向其他角色提供 rds_password 角色的成员资格。我们建议您仅将 rds_password 的成员资格提供给少数几个仅用于密码管理的角色。这些角色需要 CREATEROLE 属性以修改其角色。

请确保您验证了密码要求，例如客户端上的过期时间以及所需的复杂性。我们建议您使用自己的客户端实用程序来限制与密码相关的更改。此实用程序应具有作为 rds_password 成员的角色并具有 CREATEROLE 角色属性。