Amazon Relational Database Service

用户指南 (API 版本 2014-10-31)

AWS 文档 » Amazon Relational Database Service (RDS) » 用户指南 » Amazon RDS 上的 PostgreSQL » PostgreSQL 的常见数据库管理员任务

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

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

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

创建角色

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

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

下例展示如何创建用户，然后向该用户授予 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 数据库实例修改的参数以及每个参数的类型。

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

在 Amazon RDS 上使用 PostgreSQL Autovacuum

我们强烈建议您使用 PostgreSQL 数据库的 autovacuum 功能来保持 PostgreSQL 数据库实例正常运行。由于 autovacuum 检查已大量插入、更新或删除元组的表，您可以使用 autovacuum 防止事务 ID 重叠。Autovacuum 自动执行 VACUUM 和 ANALYZE 命令。使用 autovacuum 是 PostgreSQL 所需的而不是 Amazon RDS 强制的，并且使用此功能对于获得良好性能是至关重要的。默认情况下，为所有新的 Amazon RDS PostgreSQL 数据库实例启用此功能，并将正确设置相关的配置参数。由于默认值是某个通用值，因此，您可以通过针对特定工作负载来优化参数中受益。此部分可以帮助您执行所需的 autovacuum 优化。

有关创建提醒您事务 ID 重现的过程的信息，请参阅 AWS 数据库博客文章 Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL。

维护工作内存

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

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

一般来说，对于大型主机，将 maintenance_work_mem 参数设置为一个介于 1 GB 和 2 GB 之间的值。对于特大型主机，将该参数设置为一个介于 1 GB 和 4 GB 之间的值。为该参数设置的值应取决于工作负荷。Amazon RDS 已将该参数的默认值更新为 GREATEST({DBInstanceClassMemory/63963136*1024},65536)​。

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

在 PostgreSQL 执行动态操作以避免数据丢失之前，PostgreSQL 数据库可具有 20 亿“正在进行的”未执行 unvacuum 操作的事务。如果未执行 unvacuum 操作的事务数达到 (2^31 - 10000000)，则日志将开始发出需要执行 vacuum 操作的警告。如果未 vacuum 的事务的数目 (2^31 - 1000000)，则 PostgreSQL 会将数据库设置为只读并需要脱机的、单用户的独立 vacuum。这需要停机几小时或几天 (取决于规模)。在 PostgreSQL 文档中找到了 TransactionID 重现的非常详细的说明。

以下查询可用于显示数据库中未执行 vacuum 操作的事务的数目。数据库的 pg_database 行的 datfrozenxid 列是数据库中出现的正常 XID 的下限；它是数据库中的每表 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 亿时，TransactionID (XID) 重叠将出现，并且数据库将进入只读状态。此查询可用于生成指标，并且一天可运行几次。默认情况下，将设置 autovacuum 以确保事务期限不超过 200000000 (autovacuum_freeze_max_age)。

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

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

我们提供了以下建议：

确定哪些表当前有资格经销 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 会话还可以指示以下问题：

执行手动 Vacuum 冻结

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

以下步骤是一个指南，并且此过程存在几种变化。例如，在测试期间，您发现设定的 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 冻结详细信息。详细设置很有用，因为虽然 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 将立即重新启动很重要。您需要在第二个会话中启动您的命令，然后终止第一个会话中的 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 将立即重新启动很重要。您需要在第二个会话中启动您的命令，然后终止第一个会话中的 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，其中一些最重要的参数为：

表级别的参数

可在表级别设置与 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”消息。如果您解决问题并且需要更多详细信息，您可以使用其他日志记录级别值，例如 debug1 或 debug3。由于这些设置会生成写入到错误日志文件中的非常详细的内容，因此，使用这些调试参数一小段时间。有关这些调试设置的更多信息，请参阅 PostgreSQL 文档。

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

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

您可以设置几个参数来记录 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 扩展。以下列表显示需要执行的操作；本节稍后将更详细地介绍每个步骤。

步骤 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 项目。

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

要在您的客户 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 服务器，请执行以下操作：

限制密码管理

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

您可以通过静态参数 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 角色属性。

本页内容：

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