pg_file_settings
pg_file_settings is a system catalogue view providing a summary of the contents of the server's configuration files.
In contrast to pg_settings, it provides information about the current contents of all configuration files, including duplicated configuration items and information about whether the item could be applied.
pg_file_settings was added in PostgreSQL 9.5 (commit a97e0c33).
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 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