26

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
  • 3
    select * from pg_settings?
    – user1822
    Commented Oct 24, 2018 at 19:29
  • @a_horse_with_no_name Thanks. I posted an Answer based on your comment. Commented Oct 24, 2018 at 19:50

1 Answer 1

50

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

7
  • Is TABLE pg_file_settings; equivalent to SELECT * FROM pg_file_settings;?
    – Rafs
    Commented May 30, 2022 at 16:00
  • @RTD Yes. The first is a convenient shortcut specific to Postgres, the second is standard SQL. Commented May 30, 2022 at 16:10
  • Cool, thanks. If you have a piece of documentation for this shortcut, I'd be grateful.
    – Rafs
    Commented May 30, 2022 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. Commented May 30, 2022 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. Commented May 30, 2022 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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