pg_file_settings
pg_file_settings is a system catalogue view providing a summary of the ccurrent ontents of the server's configuration files.
pg_file_settings was added in PostgreSQL 9.5.
Usage
pg_file_settings provides information about the current contents of all configuration files, including duplicated configuration items and information about whether it is possible to apply the congfiguration item. This evaluation is performed when pg_file_settings is accessed, making it possible to verify whether changes can be successfully applied before signalling PostgreSQL to reload its configuration with e.g. pg_reload_conf().
The view pg_settings displays the configuration parameter settings as successfully applied at the last configuration reload.
Permissions
pg_file_settings can currently only be viewed by superusers, and (as of PostgreSQL 12) the default role pg_read_all_settings does not apply to this view.
To enable members of pg_read_all_settings to access this view, permission must be granted on the view itself as well as the underlying function:
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;
Definition by PostgreSQL version
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 | | |
Documentation: 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 | | |
Documentation: 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 | | |
Documentation: 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 | | |
Documentation: 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 | | |
Documentation: 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 |
Documentation: 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 |
Documentation: pg_file_settings
Change history
This view has not been modified since it was added in PostgreSQL 9.5.
- PostgreSQL 9.5
- added (commit a97e0c33).
Examples
With multiple entries for the parameter port, one of which is invalid:
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 |
An error will only be displayed if the problematic parameter is the last one to be evaluated:
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
References
- PostgreSQL documentation: pg_file_settings