PostgreSQL的视图pg_settings

PostgreSQL的视图pg_settings

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

pg_settings 是 PostgreSQL 提供的系统视图，用于显示和修改数据库的配置参数。它包含了当前会话和系统范围内的各种配置设置，包括性能调优、安全、连接管理等方面的信息。通过查询和修改 pg_settings，数据库管理员可以实时查看和调整 PostgreSQL 的运行配置。

pg_settings 视图字段说明

以下是 pg_settings 视图中的一些主要字段及其说明：

• name：配置参数的名称。
• setting：当前参数的值。
• unit：参数的单位（如果适用）。
• category：参数所属的类别。
• short_desc：参数的简要描述。
• extra_desc：参数的附加描述。
• context：参数可以被设置的上下文（如 internal, postmaster, backend, superuser, sighup 等）。
• vartype：参数的类型（如 bool, enum, integer, real, string 等）。
• source：参数值的来源（如 default, environment variable, configuration file, command line, session 等）。
• min_val：参数的最小值（如果适用）。
• max_val：参数的最大值（如果适用）。
• enumvals：枚举类型参数的所有可能值。
• boot_val：参数的引导值。
• reset_val：参数的重置值。
• sourcefile：设置参数值的配置文件（如果适用）。
• sourceline：设置参数值的配置文件中的行号（如果适用）。

使用示例

查询所有配置参数

可以用以下查询获取所有配置参数及其当前设置：

SELECT * FROM pg_settings;

查询特定参数

查询特定参数的当前值和详细信息，例如查询 work_mem 参数：

postgres=# SELECT name,sourcefile,min_val,max_val FROM pg_settings WHERE name = 'work_mem';
   name   |           sourcefile            | min_val |  max_val   
----------+---------------------------------+---------+------------
 work_mem | /home/pg16/data/postgresql.conf | 64      | 2147483647
(1 row)

调整配置参数

在会话范围内调整配置参数的值，可以使用 SET 语句。例如，将 work_mem 设置为 64MB：

SET work_mem = '64MB';

要永久调整配置参数，需要修改 postgresql.conf 文件并重新加载配置。

示例：在 postgresql.conf 文件中添加或修改以下行：

work_mem = '64MB'

可以使用 pg_reload_conf() 函数重新加载配置文件：

SELECT pg_reload_conf();

查看和调整参数的来源

查询参数的来源

查看 source 列，可以知道当前参数值的来源：

SELECT name, setting, source FROM pg_settings;

查看特定参数的来源

例如，查看 shared_buffers 参数的来源：

postgres=# SELECT name, setting,unit,sourcefile FROM pg_settings WHERE name = 'shared_buffers';
      name      | setting | unit |           sourcefile            
----------------+---------+------+---------------------------------
 shared_buffers | 16384   | 8kB  | /home/pg16/data/postgresql.conf
(1 row)

有用的配置参数分类

根据类别对配置参数进行分类查询，可以更有针对性地进行调优和管理。

性能调优参数

SELECT * FROM pg_settings WHERE category LIKE 'Resource Usage%';

安全相关参数

SELECT * FROM pg_settings WHERE category LIKE 'Connections and Authentication%';

复制和灾难恢复参数

SELECT * FROM pg_settings WHERE category LIKE 'Replication%';

小结

pg_settings 视图提供了一个强大且灵活的工具，用于查看和调整 PostgreSQL 的配置参数。通过 pg_settings，数据库管理员可以实时监控和修改数据库的运行配置，从而优化性能、增强安全性和提升系统的可靠性。合理利用这些配置参数和调整能力，可以确保 PostgreSQL 数据库高效稳定地运行，满足各种应用场景的需求。

谨记：心存敬畏，行有所止。