pg_file_settings 是一个 系统目录 视图，它提供服务器配置文件当前内容的摘要。
pg_file_settings 在 PostgreSQL 9.5 中被添加。
用法
pg_file_settings 提供了关于所有配置文件当前内容的信息，包括重复的配置项以及该配置项是否可以应用的信息。这种评估在访问 pg_file_settings 时进行，因此可以在使用 e.g. pg_reload_conf() 向 PostgreSQL 发信号重新加载配置之前，验证更改是否能够成功应用。
视图 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 19)
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
pg_file_settings (PostgreSQL 18)
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
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
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
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
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
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
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
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
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
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
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 |
文档: pg_file_settings
变更历史
此视图自 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