1. Overview
PostgreSQL provides a configuration file postgresql.conf
for end users to customize parameters. You may need to change some parameters to tune performance or deploy a PostgreSQL server in your working environment. In this blog post, we’ll explore different ways to manage these parameters.
2. Managing Parameters in Different Ways
PostgreSQL supports various parameters that allow users to customize behavior either globally (for the entire cluster) or locally (for one particular session, database, etc.). These parameters are mainly divided into five types: boolean, string, integer, floating point, and enum. Additionally, all parameter names are case-insensitive. Now, let’s explore how to manage parameters in four different ways with examples.
2.1. Changing Parameters in the Configuration File
This is a simple way to customize the server and is commonly used by most users when first working with PostgreSQL. For example, if you want to start PostgreSQL on port 5433
instead of the default port 5432
, you can either uncomment the line #port = 5432
in the postgresql.conf
file, changing 5432
to 5433
, or simply append a new line port = 5433
to the end of the file. Keep in mind, PostgreSQL will always use the last value found in the configuration file if there are duplicated configurations for the same parameter.
2.2. Changing Parameters via SQL
PostgreSQL provides three SQL commands to change parameters in different scopes: ALTER SYSTEM, ALTER DATABASE, and ALTER ROLE.
ALTER SYSTEM
changes the global default settings and persists them intopostgresql.auto.conf
. These changes will be applied on the next start.
Here is an example to change the log message output leve using ALTER SYSTEM
:
postgres=# ALTER SYSTEM SET log_min_messages='debug2';
ALTER SYSTEM
postgres=# show log_min_messages;
log_min_messages
------------------
warning
(1 row)
After restarting the PostgreSQL server, if you tail the log messages and manually issue a CHECKPOINT, you should see the message changed to debug2.
$ tail -f logfile
... ...
2023-09-22 12:45:28.137 PDT [4102315] LOG: checkpoint starting: immediate force wait
2023-09-22 12:45:28.137 PDT [4102315] DEBUG: performing replication slot checkpoint
2023-09-22 12:45:28.147 PDT [4102315] DEBUG: snapshot of 0+0 running transaction ids (lsn 0/154ACE0 oldest xid 741 latest complete 740 next xid 741)
2023-09-22 12:45:28.150 PDT [4102315] DEBUG: attempting to remove WAL segments older than log file 000000000000000000000000
2023-09-22 12:45:28.151 PDT [4102315] DEBUG: SlruScanDirectory invoking callback on pg_subtrans/0000
2023-09-22 12:45:28.151 PDT [4102315] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.014 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB; lsn=0/154ACE0, redo lsn=0/154ACA8
- The
ALTER DATABASE
command changes the per-database global default settings. To experience this command, first, delete postgresql.auto.conf to remove the previous changes and restart the PostgreSQL server, then run the commands below. You should see only database db2 related operations with DEBUG level messages.
postgres=# CREATE DATABASE db2;
CREATE DATABASE
postgres=# \c db2;
You are now connected to database "db2" as user "david".
db2=# ALTER DATABASE db2 SET log_min_messages='debug5';
ALTER DATABASE
db2=# select * from pg_db_role_setting;
setdatabase | setrole | setconfig
-------------+---------+---------------------------
16384 | 0 | {log_min_messages=debug5}
(1 row)
Connect to PostgreSQL and then type postgres=# checkpoint ;
. You will see messages like below without debug information.
2023-09-22 13:37:20.158 PDT [4102722] LOG: checkpoint starting: immediate force wait
2023-09-22 13:37:20.179 PDT [4102722] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.021 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=2281 kB; lsn=0/1951160, redo lsn=0/1951128
Connect to db2 and then type db2=# checkpoint ;
. You should see debug messages like below.
2023-09-22 13:37:52.876 PDT [4102743] DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2023-09-22 13:37:52.876 PDT [4102722] LOG: checkpoint starting: immediate force wait
2023-09-22 13:37:52.896 PDT [4102722] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.001 s, total=0.020 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=2053 kB; lsn=0/1951248, redo lsn=0/1951210
2023-09-22 13:37:52.896 PDT [4102743] DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
- The ALTER ROLE command allows both global and per-database settings to be overridden with user-specific values. To experience this role-based parameter change, first run the commands below to create a new role bob and change bob’s debug message to level5.
postgres=# CREATE ROLE bob LOGIN SUPERUSER;
CREATE ROLE
postgres=# ALTER ROLE bob SET log_min_messages = 'debug5';
ALTER ROLE
postgres=# SELECT rolconfig FROM pg_roles WHERE rolname = 'bob';
rolconfig
---------------------------
{log_min_messages=debug5}
(1 row)
Second, start two consoles: one using your default user, the other using bob, and then type CHECKPOINT in each console. You will find that log messages have debug information only when CHECKPOINT is executed in bob’s console.
2.3. Changing Parameters via Shell
The PostgreSQL server binary file postgres can accept parameters during startup. If you start the PostgreSQL server with pg_ctl, you can use the option -o to pass the parameters key and value to the postgres binary. For example:
pg_ctl -D pgdata -l logfile -o --log_min_messages='debug5' -o --port=5433 start
The PostgreSQL server should start with log level debug5 and listen on port 5433.
2.4. Changing Parameters via External Files
Sometimes, you may have many parameters customized for your PostgreSQL server, especially for performance tuning settings. These parameters can be managed in separate file(s), making them easily reusable. To achieve this, postgresql.conf allows the use of the keyword include
to include a configuration file and include_dir
to include a folder containing multiple .conf files.
To experience customize parameters using include
and include_dir
, append the following two lines to postgresql.conf:
include 'my_conf.conf'
include_dir 'my_conf_dir'
Add the port configuration to my_conf.conf in the same folder as postgresql.conf:
port = 5433
Create a new folder my_conf_dir under the folder containing postgresql.conf and add a file port.conf to the folder my_conf_dir with the port settings port = 5434.
Now, start the PostgreSQL server, and you should see it listening on port 5434. If you switch the order of include and include_dir, then restart the PostgreSQL server, you will find it listening on port 5433. This tells us that the key words include
and include_dir
follow the rule that the last value applies if duplicated settings are found. Here is a screenshoot to show the content in three different conf files.

3. Summary
In this blog post, we’ve explored four different ways to configure parameters for the PostgreSQL server. I hope this can help you better manage the configuration parameters and troubleshoot at runtime.
Reference

A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.
Recent Comments