Basic Tools
Server Configuration
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
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:
edu@postgrespro.ru
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
Configuration 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, server processes, 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
documentation: https://postgrespro.com/docs/postgresql/16/runtime-config
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 set in the configuration files affect the whole DBMS instance,
unless explicitly configured otherwise.
Some parameters can be set for a specific database or for a specific user’s
sessions. Such settings will overrule those declared in configuration files. These
types of settings will be discussed in further lessons of the course.
Lastly, many parameters can be changed at the session level, during client
operaton.
4
postgresql.conf
Main configuration file
loaded when the server starts
option to load additional configuration files
located in the data directory (PGDATA) by default
/etc/postgresql/16/main
After any changes to the parameters, the file has to be reloaded
$ pg_ctl reload
$ pg_ctlcluster 16 main reload
=> SELECT pg_reload_conf();
changes to some parameters require a server restart to apply
The main configuration file is postgresql.conf.
The default file’s location is defined during initial PostgreSQL compilation. The
server executable accepts -c config_file as a command line argument to set the
configuration file path.
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 which stores parameters in a key-value
format.
Additional configuration files can be included. By default, PostgreSQL on Ubuntu
automatically includes all .conf files from /etc/postgresql/16/main/conf.d.
If the same parameter is defined in the configuration files 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; deletes 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
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 expiration date: 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 additional included configuration file, make an error when
changing the max_connections parameter.
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, as shown in the demonstration.
At the end of this file, you will find include_dir directive that loads additional
configuration files.
Create a file namedHmax_connections.confHin the directory specified by this
directive. Edit this file either as the owner (the postgres user) or as a
superuser.
To obtain the necessary privileges, use the sudo command.
Use your preferred text editor. For example:
sudo nano /etc/postgresql/16/main/conf.d/max_connections.conf
To exit nano, use the Ctrl+X key combination.
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).
