Query for all the Postgres configuration parameters‘ current values?

Question

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?

@a_horse_with_no_name Thanks. I posted an Answer based on your comment. — Basil Bourque, Oct 24 '18 at 19:50

Basil Bourque · Accepted Answer · 2018-10-25T00:08:14.627

49

`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?.

edited Oct 25 '18 at 00:08

answered Oct 24 '18 at 19:50

Basil Bourque

10,806
20
59
92

Is TABLE pg_file_settings; equivalent to SELECT * FROM pg_file_settings;? – Rafs May 30 '22 at 16:00
@RTD Yes. The first is a convenient shortcut specific to Postgres, the second is standard SQL. – Basil Bourque May 30 '22 at 16:10
Cool, thanks. If you have a piece of documentation for this shortcut, I'd be grateful. – Rafs May 30 '22 at 16:34
@RTD Well, I tried to find that command in the manual. But searching for “TABLE” in Postgres doc results in an endless number of hits. – Basil Bourque May 30 '22 at 17:15
@RTD Found it, after I thought to use the Index chapter of the doc. The TABLE command is documented on the page for SELECT. Search that page for “TABLE Command”, about 3/4 down the page. – Basil Bourque May 30 '22 at 17:25
Greatly appreciated, I tried to search too but I was getting numerous false positives. Thanks! – Rafs May 31 '22 at 08:43
Is there a command like `SHOW '%log%', say, if you just want paramters like logging? I've tried different ways but nothing works? – Vérace Oct 27 '23 at 06:51