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 
-------
   289
(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 seconds or minutes. In order to avoid confusion, PostgreSQL allows units to be specified. For example, you 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. You 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.

PostgreSQL's ALTER SYSTEM command and postgresql.conf.auto provide a convenient way to change the configuration of the whole database cluster without editing the postgresql.conf file manually. It uses postgresql.conf.auto to overwrite the configuration parameters in postgresql.conf, so postgresql.conf.auto has a higher priority than postgresql.conf. For more information, have a look at the Chapter 12, The PostgreSQL Catalog.

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 (by 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 you open a new session and try 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 order to force all new sessions into the read-only mode in a Debian Linux distribution, you 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/11/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()"

You need to plan carefully for changing the settings' parameter values that require server downtime. For non-critical changes, you 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, you 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