Exploring configuration files and parameters

The main configuration file for PostgreSQL is postgresql.conf, a text-based file that drives the cluster when it starts.

Usually, when changing the configuration of the cluster, you must edit the postgresql.conf file to write the new settings and, depending on the context of the settings you have edited, to issue a cluster SIGHUP signal (that is, reload the configuration) or restart it.

Every configuration parameter is associated with a context, and depending on the context, you can apply changes with or without a cluster restart. Available contexts are as follows:

• internal: A group of parameters that are set at compile time and therefore cannot be changed at runtime.
• postmaster: All the parameters that require the cluster to be restarted (that is, to kill the postmaster process and start it again) to activate them.
• sighup: All the configuration parameters that can be applied with a SIGHUP signal sent to the postmaster process, which is equivalent to issuing a reload signal in the operating system service manager.
• backend and superuser-backend: All the parameters that can be set at runtime but will be applied to the next normal or administrative connection.
• user and superuser: A group of settings that can be changed at runtime and are immediately active for normal and administrative connection.

The configuration parameters will be explained later in the book, but the following is an example of a minimal configuration file with some different settings:

$ cat /postgres/16/data/postgresql.conf
shared_buffers = 512MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
work_mem = 32MB
min_wal_size = 1GB
max_wal_size = 2GB

The postgresql.auto.conf file has the very same syntax as the main postgresql.conf file but is automatically overwritten by PostgreSQL when the configuration is changed at runtime directly within the system, by means of specific administrative statements such as ALTER SYSTEM. The postgresql.auto.conf file is always loaded at the very last moment, therefore overwriting other settings. In a fresh installation, this file is empty, meaning it will not overwrite any other custom setting.

You are not tied to having a single configuration file, and, in fact, there are specific directives that can be used to include other configuration files. The configuration of the cluster will be detailed in a later chapter.

The PostgreSQL HBA file (pg_hba.conf) is another text file that contains the connection allowance: it lists the databases, users, and networks that are allowed to connect to your cluster. The HBA method can be thought of as a firewall embedded into PostgreSQL. As an example, the following is an excerpt from a pg_hba.conf file:

hosts   all luca 192.168.222.1/32 md5
hostssl all enrico 192.168.222.1/32 md5

In short, the preceding lines mean that the user luca can connect to any database in the cluster with the machine with the IPv4 address 192.168.222.1, while the user enrico can connect to any database from the same machine but only on an SSL-encrypted connection. All the available pg_hba.conf rules will be detailed in a later chapter, but for now, it is sufficient to know that this file acts as a “list of firewall rules” for incoming connections.