Posted on by Hans-Jürgen Schönig

A lot has been written about configuring postgresql.conf, postgresql.auto.conf and so on. However, sometimes it requires to take a second look in order to understand, how PostgreSQL really handles configuration parameters. You will notice that PostgreSQL configuration offers more than meets the eye at first glance. So let us dive into PostgreSQL GUCs and configuration on a more theoretical level!

postgresql.conf: The classical method

Most people will directly change settings in postgresql.conf silently, assuming that this is the place to change PostgreSQL configuration parameters. However, this is not the only place you can use. The purpose of this blog is to show you which other options you have and how you can use these features to make your database configuration better.

For the sake of simplicity, I will use an easy configuration parameter to demonstrate how PostgreSQL operates:

test=# SELECT now();
            now
-------------------------------
 2019-11-23 13:08:32.869274+01
(1 row)

The first thing you have to learn is how to figure out where configuration parameters actually come from. To do that, take a look at the pg_settings view:

test=# \x
Expanded display is on.
test=# SELECT * FROM pg_settings WHERE name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name            | TimeZone
setting         | Europe/Vienna
unit            | 
category        | Client Connection Defaults / Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      | 
context         | user
vartype         | string
source          | configuration file
min_val         | 
max_val         | 
enumvals        | 
boot_val        | GMT
reset_val       | Europe/Vienna
sourcefile      | /home/hs/db12/postgresql.conf
sourceline      | 651
pending_restart | f

postgresql.conf allows to include files. The idea is to give users the chance to break up postgresql.conf into smaller chunks.

postgresql.conf and included files

The rule here is simple: If your parameter is used inside a configuration file more than once, the LAST entry is going to be taken. In general, a parameter should only be in a config file once, but in case an error happens, you can be sure that the last entry is the one that counts.

Understanding ALTER SYSTEM

After the builtin settings, after taking what there is in postgresql.conf and after taking those include files into account, PostgreSQL will take a look at postgresql.auto.conf. The main question is: What is postgresql.auto.conf? It happens quite frequently, that administrators don’t have full access to the system (e.g. no SSH access). In this case superusers can take advantage of ALTER SYSTEM, which allows you to change PostgreSQL parameters using plain SQL. Here is how it works:

test=# ALTER SYSTEM SET timezone = 'UTC-4';
ALTER SYSTEM

If you run ALTER SYSTEM, the database will made changes to postgresql.auto.conf:

[hs@asus db12]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
timezone = 'UTC-4'

These values will have precedence over postgresql.conf.

 

Builtin settings

As you can see, the parameter is now GMT. This is the default value set by the PostgreSQL binaries, in case there are no configuration parameters at all.

test=# \x
Expanded display is on.
test=# SELECT * FROM pg_settings WHERE name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name            | TimeZone
setting         | GMT
unit            | 
category        | Client Connection Defaults / Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      | 
context         | user
vartype         | string
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | GMT
reset_val       | GMT
sourcefile      | 
sourceline      | 
pending_restart | f

However, in many cases you don’t want to set a value permanently. For instance, you might only want to set it during maintenance mode. Maybe you want to start PostgreSQL on a different port to manually, while fixing a problem, to lock out users. In this case you can pass parameters via pg_ctl directly:

[hs@asus db12]$ pg_ctl -D /home/hs/db12/ -l /dev/null -o "--timezone=UTC-3" restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[hs@asus db12]$ psql test
psql (12.0)
Type "help" for help.

test=# SELECT now();
              now              
-------------------------------
 2019-11-23 15:11:17.906164+03
(1 row)

 

Using ALTER DATABASE SET …

In 80% of cases it is totally enough to either take the built-ins, postgresql.conf, or postgresql.auto.conf. Using -o is already quite rare. However, there is a lot more. Sometimes you want your configuration to be way finer grained. What if a parameter should only be used inside a specific database? Here is how it works:

 

test=# ALTER DATABASE test SET timezone = 'UTC-5';
ALTER DATABASE

 

After reconnecting to the database, you will see that the value is set correctly:

test=# SELECT now();
              now              
-------------------------------
 2019-11-23 17:15:15.587692+05
(1 row)

Not all changes can be made at the database level. Things such as “shared_buffers”, “port” can only be changed at the instance level and are not possible at the database level anymore, as shown in the next example:

test=# ALTER DATABASE test SET port = 6000;
ERROR:  parameter "port" cannot be changed without restarting the server

ALTER USER … SET …

So far changes have been made to postgresql.conf, postgresql.auto.conf, on startup as well as on a per-database level. However, how about specific users? To do that, consider ALTER USER … SET …:

 

test=# ALTER USER hs SET timezone = 'UTC-6';
ALTER ROLE

After a reconnect the value will be shown:

test=# SELECT now();
              now              
-------------------------------
 2019-11-23 18:16:29.362417+06
(1 row)

ALTER USER … IN DATABASE … test …

But what if this is still not fine-grained enough? What if you only want to set a value for a user inside a transaction? PostgreSQL can even do that:

test=# ALTER USER hs IN DATABASE test SET timezone = 'UTC-7';
ALTER ROLE

After a reconnect the value will be shown:

test=# SELECT now();
            now
-------------------------------
 2019-11-23 19:17:39.890558+07
(1 row)

Why is this kind of configuration useful? Suppose you are using a “datawarehouse” user to run some specific aggregations in of the databases. These specific operations might need special memory parameters, such as work_mem, to be efficient.

Changing PostgreSQL parameter at the session level

Sometimes hardwiring configuration settings is still not flexible enough. In PostgreSQL configuration, parameters can even be changed on a per session level. But be careful: This seemingly simple feature is highly sophisticated. The important thing to consider, is that in PostgreSQL everything is transaction. This includes PostgreSQL configuration parameters, as you can see in the next example:

test=# BEGIN;
BEGIN
test=# SET timezone = 'UTC-9';
SET
test=# SAVEPOINT a;
SAVEPOINT
test=# SELECT now();
              now              
-------------------------------
 2019-11-23 21:18:39.625348+09
(1 row)

test=# SET timezone = 'UTC-10';
SET
test=# ROLLBACK TO SAVEPOINT a;
ROLLBACK
test=# SELECT now();
              now              
-------------------------------
 2019-11-23 21:18:39.625348+09
(1 row)

test=# ROLLBACK;
ROLLBACK
test=# SELECT now();
              now              
-------------------------------
 2019-11-23 20:19:05.245293+08
(1 row)

What you can see, is that PostgreSQL even takes savepoints et cetera into account. If a transaction is not committed, the configuration parameters will be rolled back.

Assigning parameters to functions

After this introduction, there is a final feature I want to share: Parameters can be assigned to functions. Consider the following scenario:

SELECT 	accounting_tokyo(), 
	accounting_miami(), 
	accounting_berlin();

The problem is that a “day” is not the same everywhere on the planet. So let us assume you want to calculate the turnover of every office per day. You can basically assign the timezone setting to each of those functions. Every function could run in a different timezone within the same SELECT statements.

CREATE FUNCTION shows how a setting can be passed to a function:

test=# \h CREATE FUNCTION
Command:     CREATE FUNCTION
Description: define a new function
Syntax:
CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  …

    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } …

Finally …

Configuring PostgreSQL parameters is really way more powerful than most users recognize. There are many ways to set parameters and it makes sense to explore these options to optimize your configuration. If you want to learn more about PostgreSQL configuration, you might want to check out my post about configuring parallel index creation.

Hans-Jürgen Schönig

Hans-Jürgen Schönig has worked with PostgreSQL since the 90's. He is the CEO and technical lead of CYBERTEC PostgreSQL International, a market leader in the field. He's served countless customers around the globe since the year 2000. He is also the author of the well-received "Mastering PostgreSQL" book series, as well as several other books about PostgreSQL replication and administration.