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!
Table of Contents
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
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.
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:
|
1 2 |
test=# ALTER SYSTEM SET timezone = 'UTC-4'; ALTER SYSTEM |
If you run ALTER SYSTEM, the database will made changes to postgresql.auto.conf:
|
1 2 3 4 |
[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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[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) |
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:
|
1 2 |
test=# ALTER DATABASE test SET timezone = 'UTC-5'; ALTER DATABASE |
After reconnecting to the database, you will see that the value is set correctly:
|
1 2 3 4 5 |
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:
|
1 2 |
test=# ALTER DATABASE test SET port = 6000; ERROR: parameter 'port' cannot be changed without restarting the server |
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 …:
|
1 2 |
test=# ALTER USER hs SET timezone = 'UTC-6'; ALTER ROLE |
After a reconnect the value will be shown:
|
1 2 3 4 5 |
test=# SELECT now(); now ------------------------------- 2019-11-23 18:16:29.362417+06 (1 row) |
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:
|
1 2 |
test=# ALTER USER hs IN DATABASE test SET timezone = 'UTC-7'; ALTER ROLE |
After a reconnect the value will be shown:
|
1 2 3 4 5 |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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.
After this introduction, there is a final feature I want to share: Parameters can be assigned to functions. Consider the following scenario:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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' } … |
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.
+43 (0) 2622 93022-0
office@cybertec.at
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Just a small addition - not to forget about the SET LOCAL syntax of transaction level setting of parameters:
BEGIN;
SET LOCAL timezone = ... ;
COMMIT;
This article didn't display correctly on Chrome/Android (neither mobile not desktop view). There is nothing between colon and next sentence, when explaining -o, ALTER SYSTEM and other below explanations with colon (images not loading?).
wordpress had eaten the codes for some reason. i just fixed it