Файл postgresql.conf и представление pg_file_settings

Посмотрим небольшой фрагмент конфигурационного файла.

=> SELECT setting FROM pg_settings WHERE name = 'config_file';
                setting                
---------------------------------------
 /usr/local/pgsql/data/postgresql.conf
(1 row)

=> \! sed -n '35,46p' /usr/local/pgsql/data/postgresql.conf
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'		# use data in another directory
					# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'	# host-based authentication file
					# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf'	# ident configuration file
					# (change requires restart)

Большинство строк в файле закомментированы, а для соответствующих параметров используются значения по умолчанию.


Чтобы увидеть все незакоментированные строки конфигурационного файла, можно обратиться к представлению pg_file_settings:

=> SELECT sourceline, name, setting, applied
   FROM   pg_file_settings
   WHERE  sourcefile LIKE '%postgresql.conf';
 sourceline |            name            |      setting       | applied 
------------+----------------------------+--------------------+---------
         64 | max_connections            | 100                | t
        113 | shared_buffers             | 128MB              | t
        127 | dynamic_shared_memory_type | posix              | t
        472 | log_timezone               | W-SU               | t
        566 | datestyle                  | iso, dmy           | t
        568 | timezone                   | W-SU               | t
        581 | lc_messages                | en_US.UTF-8        | t
        583 | lc_monetary                | ru_RU.UTF-8        | t
        584 | lc_numeric                 | ru_RU.UTF-8        | t
        585 | lc_time                    | ru_RU.UTF-8        | t
        588 | default_text_search_config | pg_catalog.english | t
(11 rows)

Столбец applied говорит о том, можно ли применить значение без перезапуска сервера. Представление pg_file_settings показывает лишь содержимое файлов конфигурации, поэтому реальные значения параметров могут отличаться.


Представление pg_settings

Получить действующие значения всех параметров можно в представлении pg_settings. Вот что в нем содержится, например, для work_mem:

=> SELECT name, setting, unit,
          boot_val, reset_val,
          source, sourcefile, sourceline,
          pending_restart, context
   FROM   pg_settings WHERE name = 'work_mem'\gx
-[ RECORD 1 ]---+---------
name            | work_mem
setting         | 4096
unit            | kB
boot_val        | 4096
reset_val       | 4096
source          | default
sourcefile      | 
sourceline      | 
pending_restart | f
context         | user


Рассмотрим ключевые столбцы представления pg_settings:


Столбец context определяет действия, необходимые для применения параметра. Среди возможных значений:


Порядок применения строк postgresql.conf

Если один и тот же параметр встречается в файле несколько раз, то устанавливается значение из последней считанной строки.

Запишем в конец postgresql.conf две строки с параметром work_mem:

=> \! echo work_mem=12MB >> /usr/local/pgsql/data/postgresql.conf
=> \! echo work_mem=8MB >> /usr/local/pgsql/data/postgresql.conf
=> SELECT sourceline, name, setting, applied
   FROM   pg_file_settings
   WHERE  name = 'work_mem';
 sourceline |   name   | setting | applied 
------------+----------+---------+---------
        659 | work_mem | 12MB    | f
        660 | work_mem | 8MB     | t
(2 rows)

По applied = f для строки с 12MB уже понятно, что это значение не будет применено.


Для параметра work_mem значение context = user. Значит, его можно менять прямо во время сеанса, а чтобы изменить значение во всех сеансах, достаточно перечитать файлы конфигурации:

=> SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)


Убедимся, что work_mem получил значение от последней строки:

=> SELECT name, setting, unit,
          boot_val, reset_val,
          source, sourcefile, sourceline,
          pending_restart, context
   FROM   pg_settings WHERE name = 'work_mem'\gx
-[ RECORD 1 ]---+--------------------------------------
name            | work_mem
setting         | 8192
unit            | kB
boot_val        | 4096
reset_val       | 8192
source          | configuration file
sourcefile      | /usr/local/pgsql/data/postgresql.conf
sourceline      | 660
pending_restart | f
context         | user


Команда ALTER SYSTEM

Для примера установим параметр work_mem:

=> ALTER SYSTEM SET work_mem TO '16mb';
ERROR:  invalid value for parameter "work_mem": "16mb"
HINT:  Valid units for this parameter are "kB", "MB", "GB", and "TB".

Что случилось?


ALTER SYSTEM выполняет проверку на допустимые значения.

=> ALTER SYSTEM SET work_mem TO '16MB';
ALTER SYSTEM

Вот теперь все правильно.


В результате выполнения команды значение 16MB записано в файл postgresql.auto.conf:

=> SELECT *
FROM regexp_split_to_table(pg_read_file('postgresql.auto.conf'), '\n');
               regexp_split_to_table               
---------------------------------------------------
 # Do not edit this file manually!
 # It will be overwritten by ALTER SYSTEM command.
 work_mem = '16MB'
 
(4 rows)


...но не установлено:

=> SHOW work_mem;
 work_mem 
----------
 8MB
(1 row)


Чтобы применить изменения для work_mem, перечитаем файлы конфигурации:

=> SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

=> SELECT name, setting, unit,
          boot_val, reset_val,
          source, sourcefile, sourceline,
          pending_restart, context
   FROM   pg_settings WHERE name = 'work_mem'\gx
-[ RECORD 1 ]---+-------------------------------------------
name            | work_mem
setting         | 16384
unit            | kB
boot_val        | 4096
reset_val       | 16384
source          | configuration file
sourcefile      | /usr/local/pgsql/data/postgresql.auto.conf
sourceline      | 3
pending_restart | f
context         | user


Для удаления строк из postgresql.auto.conf используется команда ALTER SYSTEM RESET.

=> ALTER SYSTEM RESET work_mem;
ALTER SYSTEM
=> SELECT *
FROM regexp_split_to_table(pg_read_file('postgresql.auto.conf'), '\n');
               regexp_split_to_table               
---------------------------------------------------
 # Do not edit this file manually!
 # It will be overwritten by ALTER SYSTEM command.
 
(3 rows)


Еще раз перечитаем конфигурацию. Теперь восстановится значение из postgresql.conf:

=> SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

=> SELECT name, setting, unit,
          boot_val, reset_val,
          source, sourcefile, sourceline,
          pending_restart, context
   FROM   pg_settings WHERE name = 'work_mem'\gx
-[ RECORD 1 ]---+--------------------------------------
name            | work_mem
setting         | 8192
unit            | kB
boot_val        | 4096
reset_val       | 8192
source          | configuration file
sourcefile      | /usr/local/pgsql/data/postgresql.conf
sourceline      | 660
pending_restart | f
context         | user


Установка параметров во время исполнения

Для изменения параметров во время сеанса можно использовать команду SET:

=> SET work_mem TO '24MB';
SET

Или функцию set_config:

=> SELECT set_config('work_mem', '32MB', false);
 set_config 
------------
 32MB
(1 row)

Третий параметр функции говорит о том, нужно ли устанавливать значение только для текущей транзакции (true) или до конца работы сеанса (false). Это важно при работе приложения через пул соединений, когда в одном сеансе могут выполняться транзакции разных пользователей.


Чтение значений параметров во время выполнения

Получить значение параметра можно разными способами:

=> SHOW work_mem;
 work_mem 
----------
 32MB
(1 row)

=> SELECT current_setting('work_mem');
 current_setting 
-----------------
 32MB
(1 row)

=> SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';
   name   | setting | unit 
----------+---------+------
 work_mem | 32768   | kB
(1 row)


Установка параметров транзакции

Откроем транзакцию и установим новое значение work_mem:

=> RESET work_mem;
RESET
=> BEGIN;
BEGIN
=> SET work_mem TO '64MB';
SET
=> SHOW work_mem;
 work_mem 
----------
 64MB
(1 row)


Если транзакция откатывается, то установка параметра отменяется:

=> ROLLBACK;
ROLLBACK
=> SHOW work_mem;
 work_mem 
----------
 8MB
(1 row)


Пользовательские параметры

Параметры можно создавать прямо во время сеанса, в том числе с предварительной проверкой на существование:

=> SELECT CASE WHEN current_setting('myapp.currency_code', true) IS NULL THEN
                set_config('myapp.currency_code', 'RUB', false)
            ELSE
                current_setting('myapp.currency_code')
            END;
 current_setting 
-----------------
 RUB
(1 row)

В имени пользовательского параметра обязательно должна быть точка, чтобы отличать их от стандартных параметров.


Теперь myapp.currency_code можно использовать как глобальную переменную сеанса:

=> SELECT current_setting('myapp.currency_code');
 current_setting 
-----------------
 RUB
(1 row)

Пользовательские параметры можно указать в postgresql.conf, тогда они автоматически будут инициализироваться во всех сеансах.


Конец демонстрации.