postgres configuration pg_settings - ghdrako/doc_snipets GitHub Wiki

• https://www. postgresql.org/docs/current/static/view-pg-settings.html

The pg_settings view also provides the short_desc and extra_desc columns. We can use these as shortcuts to remember why we might have changed a setting, without pulling up the PostgreSQL documentation.

obtain a list of settings that require a server restart and their current value:

 SELECT name, setting
  FROM pg_settings
 WHERE context = 'postmaster';
 Execute this query for a list of only those settings that are not changed from
 the default and require restart:
 SELECT name, setting, boot_val
  FROM pg_settings
 WHERE context = 'postmaster'
   AND boot_val = setting;

list of all the settings and a translation of how each setting is managed:

 SELECT name,
        CASE context
        WHEN 'postmaster' THEN 'RESTART'
        WHEN 'sighup' THEN 'Reload'
        WHEN 'backend' THEN 'Reload'
        WHEN 'superuser' THEN 'Reload / Superuser SET'
        WHEN 'superuser-backend'
              THEN 'Reload / Superuser Session'
        WHEN 'user' THEN 'Reload / User SET'
        END AS when_changed
  FROM pg_settings
 WHERE context != 'internal'
 ORDER BY when_changed;

provide a list of settings they've changed.

 SELECT name, setting
  FROM pg_settings
 WHERE boot_val IS DISTINCT FROM setting;

The IS DISTINCT FROM clause considers NULL as a distinct value instead of an unknown one, permitting direct comparisons