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 into postgresql.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

• Setting parameters
• Customizing SQL Functions in PostgreSQL: Exploring Various Approaches

David Zhang

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.