List PostgreSQL Configuration Files Using SQL Select

This post shows how to list the configuration files and directories that an instance of PostgreSQL uses. Sometimes we need to know these to customize PostgreSQL.

PostgreSQL Docker Container

To keep things simple, we can start up a PostgreSQL Docker container to avoid installing PostgreSQL on our machine. Therefore, we need to install Docker on our machine and use PostgreSQL 12.

Then, run the following command.

The command will start up a PostgreSQL Docker container.

Next, we need to connect to the server and run a SQL statement.

PostgreSQL Configuration Files

All references to the configuration files and directories are available in the pg_settings table. Then, run the following SQL statement. We can use a PgAdmin Docker container to connect to the PostgreSQL server. Alternatively, other SQL IDEs can be used like JetBrain’s DataGrip or a PgAdmin directly installed on our machine. We can also use the psql command, which is a text-based PostgreSQL client.

We will get the following output. For this PostgreSQL version, we have four items.

The configuration file PostgreSQL.conf contains properties that directly control the PostgreSQL server’s behaviors. These behavior include functions in auditing, and authentication, among other things. Then, the data_directory points to the location where all the PostgreSQL data typically reside. Then, the hba_file  refers to the host-based authentication configuration file. Lastly, the ident_file refers to the user identification configuration file with user name mapping.

For more information on these configurations, please read The Runtime Config File Location.

Alternative Commands

Aside from using the above SQL statement, we can also use the configuration name as a parameter to the SHOW command. Consider the following command to show the value of the config_file parameter.

The command generates the following output.

We can only use the following commands.

These commands require us to know the exact name of the configuration parameters. Otherwise, the show command will not work.