pg_file_settings 是一个提供服务器配置文件当前内容摘要的系统目录视图。
pg_file_settings 在PostgreSQL 9.5中添加。
用法
pg_file_settings 提供有关所有配置文件当前内容的信息，包括重复的配置项以及是否可以应用配置项的信息。此评估在访问pg_file_settings时执行，从而可以在发出信号让 PostgreSQL 使用例如pg_reload_conf()重新加载其配置之前验证更改是否可以成功应用。
视图pg_settings 显示在上次配置重新加载时成功应用的配置参数设置。
权限
pg_file_settings 目前只能被超级用户查看，并且（截至PostgreSQL 12）默认角色pg_read_all_settings 不适用于此视图。
要使pg_read_all_settings 的成员能够访问此视图，必须在此视图本身以及底层函数上授予权限。
GRANT SELECT ON pg_catalog.pg_file_settings TO pg_read_all_settings; GRANT EXECUTE ON FUNCTION pg_catalog.pg_show_all_file_settings() TO pg_read_all_settings;
按 PostgreSQL 版本定义
pg_file_settings (PostgreSQL 17)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 16)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 15)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 14)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 13)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 12)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 11)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 10)
View "pg_catalog.pg_file_settings"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text | | |
sourceline | integer | | |
seqno | integer | | |
name | text | | |
setting | text | | |
applied | boolean | | |
error | text | | |
pg_file_settings (PostgreSQL 9.6)
View "pg_catalog.pg_file_settings"
Column | Type | Modifiers
------------+---------+-----------
sourcefile | text |
sourceline | integer |
seqno | integer |
name | text |
setting | text |
applied | boolean |
error | text |
pg_file_settings (PostgreSQL 9.5)
View "pg_catalog.pg_file_settings"
Column | Type | Modifiers
------------+---------+-----------
sourcefile | text |
sourceline | integer |
seqno | integer |
name | text |
setting | text |
applied | boolean |
error | text |
更改历史记录
自PostgreSQL 9.5中添加以来，此视图没有修改过。
- PostgreSQL 9.5
- 添加 (提交a97e0c33)。
示例
对于参数port有多个条目，其中一个无效。
postgres=# SELECT regexp_replace(sourcefile, '^/.+/','') AS sourcefile, sourceline, \
seqno, name, setting, applied, error FROM pg_file_settings WHERE name='port';
sourcefile | sourceline | seqno | name | setting | applied | error
-----------------------+------------+-------+------+---------+---------+-------
postgresql.conf | 63 | 1 | port | 5432 | f |
postgresql.local.conf | 2 | 23 | port | foo | f |
postgresql.local.conf | 4 | 25 | port | 5433 | t |
只有当有问题的参数是最后一个被评估的参数时，才会显示错误。
postgres=# SELECT regexp_replace(sourcefile, '^/.+/','') AS sourcefile, sourceline, \
seqno, name, setting, applied, error FROM pg_file_settings WHERE name='port';
sourcefile | sourceline | seqno | name | setting | applied | error
-----------------------+------------+-------+------+---------+---------+------------------------------
postgresql.conf | 63 | 1 | port | 5432 | f |
postgresql.local.conf | 3 | 24 | port | 5433 | f |
postgresql.local.conf | 26 | 46 | port | foo | f | setting could not be applied
参考
- PostgreSQL 文档： pg_file_settings