PostgreSQL 的常见数据库管理员任务 - Amazon Relational Database Service
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

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

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

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

创建角色

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

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

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

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

  • 使用 rds_superuser 命令查看所有未分配 pg_stat_activity 角色的用户,并使用 pg_terminate_backendpg_cancel_backend 命令停止其连接。

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

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

create role testuser with password 'testuser' login; grant rds_superuser to testuser;

管理 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_connectionsshared_buffers 来说过大,则将使 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

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;
  2. 修改与数据库实例关联的参数组以执行以下操作:

    • 使用包含 pgaudit 的共享预加载库。

    • 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" \ --region us-west-2 aws rds modify-db-parameter-group \ --db-parameter-group-name rds-parameter-group-96 \ --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;
  6. 运行以下命令确认 pgaudit.role 设置为 rds_pgaudit

    SHOW pgaudit.role; pgaudit.role ------------------ rds_pgaudit

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

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

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

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

有关查看日志的信息,请参阅访问 Amazon RDS 数据库日志文件

使用 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"

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

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

例如,以下命令使用 pgbadger 正确设置日期为 2014-02-04 的 Amazon RDS for 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');

限制密码管理

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

您可以通过静态参数 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 TOALTER ROLE 命令也会受限制。因为重命名具有 MD5 密码的 PostgreSQL 角色会清除密码,因此这些命令会受限制。

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

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