pg_settings is a system view that exposes the server's runtime configuration parameters. It provides an alternative interface to the SHOW and SET commands, and surfaces additional metadata about each parameter that SHOW alone does not expose — such as minimum and maximum allowed values.
Columns
| Column | Type | Description |
|---|---|---|
name | text | The name of the runtime configuration parameter. |
setting | text | The current value of the parameter. |
unit | text | The implicit unit of the parameter. |
category | text | The logical group of the parameter. |
short_desc | text | A short description of the parameter. |
extra_desc | text | A more detailed description of the parameter. |
context | text | The context required to set the parameter value. See Context values. |
vartype | text | The parameter type. Valid values: bool, enum, integer, real, and string. |
source | text | The source of the current parameter value. |
min_val | text | The minimum allowed value of the parameter. Empty for non-numeric parameters. |
max_val | text | The maximum allowed value of the parameter. Empty for non-numeric parameters. |
enumvals | text[] | The allowed values for an enumeration parameter. This value is empty for non-numeric values. |
boot_val | text | The parameter value that is assigned at server startup if the parameter is not modified afterwards. |
reset_val | text | The value that RESET would restore the parameter to in the current session. |
sourcefile | text | The configuration file where the current value is set. Empty if the value comes from a source other than a configuration file, or if the querying user is not a superuser or a member of pg_read_all_settings. Relevant when using include directives in configuration files. |
sourceline | int4 | The line number in the configuration file where the current value is set. Empty under the same conditions as sourcefile. |
pending_restart | bool | true if the value has been changed in the configuration file and a server restart is required for it to take effect. Otherwise false. |
Context values
The context column indicates how and when a parameter can be changed. The seven values are listed below in order from most restrictive to least restrictive.
| Context value | How to change |
|---|---|
internal | Cannot be changed directly. These settings reflect internally determined values. Some may be adjustable by rebuilding the server with different configuration options, or by changing initdb options. |
postmaster | Requires a server restart. Values are typically set in postgresql.conf or passed on the command line at startup. Settings with a lower-ranked context can also be set at startup. |
sighup | Can be changed in postgresql.conf without restarting the server. Send a SIGHUP signal to the postmaster to reload the file. The postmaster forwards SIGHUP to its child processes so they all pick up the new value. |
superuser-backend | Can be changed in postgresql.conf without restarting the server. Can also be set per-session in the connection request packet (for example, via the PGOPTIONS environment variable of libpq), but only if the connecting user is a superuser. Settings do not change in a session after it starts. New values take effect only in sessions started afterwards. |
backend | Can be changed in postgresql.conf without restarting the server. Can also be set per-session in the connection request packet (for example, via the PGOPTIONS environment variable of libpq). Any user can make such changes for their session. Settings do not change in a session after it starts. New values take effect only in sessions started afterwards. |
superuser | Can be set in postgresql.conf or within a session using the SET command. Changes in postgresql.conf take effect in existing sessions only if no session-local value is set via SET. |
user | Can be set in postgresql.conf or within a session using the SET command by any user. Changes in postgresql.conf take effect in existing sessions only if no session-local value is set via SET. |
Usage notes
Modifying parameters via UPDATE
pg_settings does not support INSERT or DELETE. It does support UPDATE: applying an UPDATE to a row of pg_settings is equivalent to executing the SET command on that parameter. The change only affects the current session.
If the UPDATE is issued inside a transaction that is later rolled back, the change is reverted. If the transaction is committed, the change persists until the end of the session, unless overridden by another UPDATE or SET command.