Settings

The PostgreSQL settings control different aspects of the PostgreSQL server, including replication, write-ahead logs, resource consumption, query planning, logging, authentication, statistic collection, garbage collection, client connections, lock management, error handling, and debug options.

The following SQL command shows the number of PostgreSQL settings. Note that this number might differ slightly between different installations as well as customized settings:

postgres=# SELECT count(*) FROM pg_settings;
 count 
-------
   269
(1 row)

The parameters can be as follows:

• Boolean: 0, 1, true, false, on, off, or any case-insensitive form of the previous values. The ENABLE_SEQSCAN setting falls into this category.
• Integer: An integer might specify a memory or time value; there is an implicit unit for each setting such as second or minute. In order to avoid confusion, PostgreSQL allows units to be specified. For example, one could specify 128 MB as a shared_buffers setting value.
• Enum: These are predefined values such as ERROR and WARNING.
• Floating point: cpu_operator_cost has a floating point domain. cpu_operator_cost is used to optimize the PostgreSQL execution plans.
• String: A string might be used to specify the file location on a hard disk, such as the location of the authentication file.

The setting context determines how to change a setting's value and when the change can take effect. The setting contexts are as follows:

• Internal: The setting cannot be changed directly. One might need to recompile the server source code or initialize the database cluster to change this. For example, the length of PostgreSQL identifiers is 63 characters.
• Postmaster: Changing a setting value requires restarting the server. Values for these settings are typically stored in the PostgreSQL postgresql.conf file.
• Sighup: No server restart is required. The setting change can be made by amending the postgresql.conf file, followed by sending a SIGHUP signal to the PostgreSQL server process.
• Backend: No server restart is required. They can also be set for a particular session.
• Superuser: Only a superuser can change this setting. This setting can be set in postgresql.conf or via the SET command.
• User: This is similar to superuser, and is typically used to change the session-local values.

PostgreSQL provides the SET and SHOW commands to change and inspect the value of a setting parameter respectively. These commands are used to change the setting parameters in the superuser and user context. Typically, changing the value of a setting parameter in the postgresql.conf file makes the effect global.

The settings can also have a local effect, and can be applied to different contexts, such as sessions and tables. For example, let's assume that you would like some clients to be able to perform the read-only operation; this is useful for configuring tools such as Confluence (Atlassian). In this case, you can achieve that by setting the default_transaction_read_only parameter:

postgres=# SET default_transaction_read_only to on;
SET
postgres=# CREATE TABLE test_readonly AS SELECT 1;
ERROR: cannot execute CREATE TABLE AS in a read-only transaction

In the preceding example, the creation of a table has failed within the opened session; however, if one opens a new session and tries to execute the CREATE TABLE command, it will be executed successfully because the default value of the default_transaction_read_only setting is off. Setting the default_transaction_read_only parameter in the postgresql.conf file will have a global effect, as mentioned earlier.

PostgreSQL also provides the pg_reload_conf() function, which is equivalent to sending the SIGHUP signal to the PostgreSQL process.

In general, it is preferable to use pg_reload_conf() or reload the configuration settings via the init script because it is safer than the SIGHUP kill signal due to human error. 

In order to set the database in the read-only mode in a Debian Linux distribution, one can do the following:

1. Edit postgresql.conf and alter the value of default_transaction_read_only. This can be done in Ubuntu with the following commands:

$sudo su postgres
$CONF=/etc/postgresql/10/main/postgresql.conf
$sed -i "s/#default_transaction_read_only = off/default_transaction_read_only = on/" $CONF

1. Reload the configuration by executing the pg_reload_conf() function:

$psql -U postgres -c "SELECT pg_reload_conf()"

One needs to plan carefully for changing the setting parameter values that require server down time. For non-critical changes, one can change the postgresql.conf file in order to make sure that the change will take effect when the server is restarted due to security updates. For urgent changes, one should follow certain processes, such as scheduling downtime and informing the user of this downtime. Developers, in general, are concerned with two settings categories, which are as follows:

• Client connection defaults: These settings control the statement behaviors, locale, and formatting
• Query planning: These settings control the planner configuration, and give hints to the developer on how to rewrite SQL queries