Файл 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, тогда они
автоматически будут инициализироваться во всех сеансах.






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