Query for All Postgres Configuration Parameters’ Current Values

configurationpostgresqlquery

You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but how about reading?

➥ Is there a way to query for all the current settings?

I know the settings in the client authentication configuration file pg_hba.conf can be read with the pg_hba_file_rules view, like this: table pg_hba_file_rules ;. Is there something similar for postgresql.conf?

Best Answer

`SHOW ALL`

The SHOW ALL command displays the current setting of run-time parameters in 3 columns.

SHOW ALL ;

`pg_settings`

The pg_settings view shows the same items as SHOW ALL but with additional details, across 17 columns versus 3 columns.

TABLE pg_settings ;

`pg_file_settings`

To read what is stored in the postgresql.conf file itself, use the view pg_file_settings.

If freshly written, this file will hold values that may not yet be in effect within the Postgres server. After writing, the settings must be loaded in one of various ways including a server restart.

TABLE pg_file_settings ;

Admin tool

Your databased-administration tool may display these settings.

For example, in pgAdmin 4, choose the cluster name (the Postgres installation) in the navigation panel, click the Dashboard tab, and in a list bottom panel titled Server activity, click the Configuration tab to see a list of your settings.

Writing

By the way… For info about writing these settings with ALTER SYSTEM commands, see my Answer on another Question, How to edit postgresql.conf with pgAdmin 4?.

Related Solutions

Postgresql – How to get user-specific settings in PostgreSQL

You can get the role configuration by querying pg_catalog.pg_shadow or pg_catalog.pg_user, as the following:

--Query pg_shadow

francs=# select usename,useconfig from pg_shadow ;
   usename    |           useconfig           
--------------+-------------------------------
 postgres     | 
 user_a       | 
 user_b       | 
 skytf_select | {"search_path=skytf, public"}
 francs       | 
 dwetl        | 
 skytf        | 
(7 rows)

francs=# alter role francs  set log_statement='all';
ALTER ROLE

francs=# select usename,useconfig from pg_shadow ;
   usename    |           useconfig           
--------------+-------------------------------
 postgres     | 
 user_a       | 
 user_b       | 
 skytf_select | {"search_path=skytf, public"}
 rancs        | {log_statement=all}
 dwetl        | 
 skytf        | 
(7 rows)

Postgresql – Can’t backup a Postgres database: FATAL: Peer authentication failed for user “postgres”

In pg_hba.conf, change this line:

local   all             all                                     peer

to:

local   all             all                                     password

Then you can log in locally using a password.

Best Answer

SHOW ALL

pg_settings

pg_file_settings