Basic tools
Server configuration
13
Copyright
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Configuration parameters
Configuration files
Parameter management at the instance and session levels
3
Parameters
Purpose
managing DBMS operation and behavior
Setting parameters
for an instance via configuration files
for a separate database or user
for the current session
There are multiple parameters in PostgreSQL that control the DBMS behavior.
These parameters affect resource management, backend process operations,
and much more.
For example, the max_connections parameter limits the number of concurrent
connections to the server.
The full list of configuration parameters and their descriptions is available in the
In this topic, we will not cover any specific configuration parameters, but rather
discuss how to set their values.
Configuration parameters are generally managed in configuration files. The
parameter values defined in the configuration files affect the whole DBMS
instance, unless explicitly configured otherwise.
Some parameters can be set for specific databases or for a specific user’s
sessions. Such parameters will overrule those declared in configuration files.
These types of parameters will be discussed in further chapters of the course.
Lastly, many parameters can be changed at the session level, during server
operaton.
4
postgresql.conf
Main configuration file
loaded when the server starts
located in the data directory (PGDATA) by default
/etc/postgresql/13/main
After any changes to the parameters, the file has to be reloaded
$ pg_ctl reload
$ pg_ctlcluster 13 main reload
=> SELECT pg_reload_conf();
changes to some parameters require a server restart to apply
The main configuration file is postgresql.conf.
The file’s location is defined during initial PostgreSQL compilation. By default, the
file is located in the data catalog (PGDATA), but package distributions usually
place it somewhere else, depending on the OS-specific conventions.
This is a well-documented plaintext file that stores parameters in a key-value
format.
If the same parameter is defined in the file multiple times, only the most recently
read value will be used.
For any changes to parameters to apply, the file must be reloaded. Some
parameters require a server restart to apply.
6
postgresql.auto.conf
Configuration file managed by SQL commands
ALTER SYSTEM adds or changes a line
SET parameter TO value;
ALTER SYSTEM RESET parameter; removes a line
ALTER SYSTEM RESET ALL; deletes all lines
read after postgresql.conf
Location
always in the data directory (PGDATA)
Actions needed to apply
same as for postgresql.conf
The file postgresql.auto.conf is always loaded last. This file is always located in the
data catalog (PGDATA).
It should never be modified manually, but only with the ALTER SYSTEM command.
ALTER SYSTEM is an SQL interface for managing configuration parameters.
For any changes made with ALTER SYSTEM to take place, the server must reload
the configuration files, as it does with postgresql.conf.
The contents of both files (postgresql.conf and postgresql.auto.conf) can be
checked using the pg_file_settings view, and the current parameter values are
shown in the pg_settings view.
More about the ALTER SYSTEM command:
8
Current session
set until the end of the session or transaction
the act of setting parameters is transactional
custom parameters are allowed
client
application
PostgreSQLPostgreSQL
SET, set_config
SHOW, current_setting
Parameter values can be changed directly during the session with the SET
command or the set_config function. The SHOW command or the
current_setting function display the current parameter value.
By setting a new value, you can specify its effective range: until the end of
the session (by default) or until the end of the transaction (SET LOCAL).
In any case, setting parameters is transactional: if the current transaction is
rolled back, any parameters modified within it will return to the state they
were in at the start of the transaction.
In addition to the PostgreSQL system parameters, the same commands and
functions can be used to create and get the values of custom parameters.
10
Takeaways
The main configuration file is postgresql.conf
ALTER SYSTEM is an SQL interface for managing
configuration parameters stored in postgresql.auto.conf
When configuration files are modified, they have to be reloaded
Some parameters can be changed just for the current session
Changes to some parameters require a server restart to apply
11
Practice
1. Get a list of parameters that require a server restart to apply.
2. In the postgresql.conf file, make an error when changing the
max_connections parameter value.
Restart the server. Make sure that the server does not start
and check the message log.
Fix the error and start the server.
2. To get the location of the postgresql.conf file, check the value of the
config_file parameter.
Edit the postgresql.conf file either as the owner (the postgres user) or as a
superuser.
To do the former, you can open a new terminal window and execute the
command there:
sudo su postgres
To do the latter, open the file in a text editor from the command line using
the sudo command, for example:
sudo vim postgresql.conf
12
Practice+
1. Set the parameter work_mem = 32MB in the psql tool’s command
line options.
2. In the Ubuntu package distribution, the postgresql.conf file is not
located in the PGDATA directory. How does the server find this
configuration file at startup?
1. Use either the options key in the connection string or the PGOPTIONS
environment variable.
More on how connection strings are formed:
2. To see the location of the postgresql.conf file, check the config_file
parameter.
To find where the parameter is set, run the ps command for the postgres
main process. The process ID (PID) is the first line of the postmaster.pid file,
which is located in the data directory (PGDATA).
