1

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?

  • 2
    select * from pg_settings? – a_horse_with_no_name Oct 24 '18 at 19:29
  • @a_horse_with_no_name Thanks. I posted an Answer based on your comment. – Basil Bourque Oct 24 '18 at 19:50
0

SHOW ALL

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

SHOW ALL ;

screenshot of pgAdmin 4 running <code>SHOW ALL</code> command

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 ;

screenshot of pgAdmin 4 running <code>TABLE pg_settings</code>

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 ;

enter image description here

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.

screenshot of pgAdmin 4 > [cluster name] > Dashboard > Server activity > Configuration

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.