pg_file_settings

A system catalogue view providing a summary of the current contents of the server's configuration files

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.

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
 

Categories

Configuration, System catalogue

See also

ALTER SYSTEM, pg_settings, postgresql.conf, postgresql.auto.conf, pg_hba_file_rules