Setting and getting database cluster settings

The PostgreSQL configuration settings control several aspects of the PostgreSQL cluster. For the administration aspect, one can define the statement time out, number of allowed connections, transaction type—read only or read/write—, and so on. From the development point of view, these settings can help a developer optimize queries.

Getting ready

The following recipe shows how to get and set a certain configuration value.

How to do it…

Getting a value can be done in several ways, such as selecting the value from the pg_settings catalog view, browsing the postgresql.conf file, or using the following function and statement:

car_portal=# SELECT current_setting('work_mem');
 current_setting
-----------------
 4MB
(1 row)

car_portal=# show work_mem;
 work_mem
----------
 4MB
(1  row)

In order to change a certain configuration value, one could use the set_config(settin_name, setting_value, scope) function, as follows:

car_portal=# SELECT set_config('work_mem...