Перейти к основному содержимому

Глава 06. Конфигурация сервера

В этой главе:

  • Настройка сервера
  • Параметры
  • Контекст параметров
  • Файлы конфигурации
  • Представления
  • Управление параметрами настроек
  • Другие параметры

Настройка сервера​

  • Настройка осуществляется изменением значений параметров.

  • Можно установить значения параметров для:

    • экземпляра;
    • базы данных;
    • роли;
    • сессии;
    • транзакции.

  • Способы установки параметров на уровне экземпляра:

    • опция -c командной строки;
    • конфигурационные файлы.

Повлиять на работу экземпляра PostgreSQL можно следующими путями:

  • используя опции командной строки процесса postgres; ●используя настройки параметров времени исполнения;
  • с помощью переменных окружения.

Большинство опций командной строки имеют аналоги в виде параметров в конфигурационных файлах, хотя в некоторых случаях удобно указывать некоторые параметры в виде опций командной строки. Например, опция -D задает расположение каталога данных, а используя опцию -c (установить значение параметра) с параметром config_file=... можно указать имя конфигурационного файла.

Основной путь настройки сервера - параметры конфигурации времени исполнения. Параметры можно устанавливать не только в конфигурационных файлах, но и для конкретной базы данных или роли, сессии и даже транзакции. Однако, это зависит от конкретного параметра.

Еще один способ повлиять на работу сервера - переменные окружения. Так, например, переменная окружения PGDATA задает имя каталога данных кластера. По соглашению, имена переменных окружения для PostgreSQL начинаются с префикса PG. Установка переменной окружения PGOPTIONS позволяет клиентам, базирующимся на libpq передать строку с настройками параметров непосредственно серверу. https://www.postgresql.org/docs/15/runtime-config.html.

Параметры​

  • Имена параметров не чувствительны к регистру.

  • Типы параметров:

    • логический;
    • строчный;
    • числовой;
    • числовой с единицей измерения;
    • перечисление.

  • Параметры, их установленные значения, и прочие данные о них можно получить с помощью представления pg_catalog.pg_settings.

Имена параметров можно указывать как в верхнем регистре, так и в нижнем:

postgres=# show work_mem;
 work_mem
----------
 4MB
(1 строка)
postgres=# SHOW WORK_MEM;
 work_mem
----------
 4MB
(1 строка)

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

postgres=# \dconfig+ wal_level
              Список параметров конфигурации
 Параметр  | Значение | Тип  |  Контекст  | Права доступа
-----------+----------+------+------------+---------------
 wal_level | replica  | enum | postmaster |
(1 строка)

Получить значения параметров можно встроенной командой psql show, метакомандой \dconfig, или обратившись к представлению pg_catalog.pg_settings .

https://www.postgresql.org/docs/15/config-setting.html#CONFIG-SETTING-NAMES-VALUES.

Контекст параметров​

# SELECT context, count(*) FROM pg_settings GROUP BY context;
context            | count 
-------------------+-------
postmaster         | 135 
superuser-backend  | 4 
user               | 140 
internal           | 22 
backend            | 2 
sighup             | 133 
superuser          | 60
(7 rows)

Способ изменения:

– internal - параметр нельзя менять непосредственно; – postmaster - параметр устанавливается при старте сервера; – sighup - достаточно перечитать конфигурацию, получив сигнал SIGHUP; – superuser-backend - сессионный параметр суперпользователя, устанавливаемый одноразово; – backend - одноразово устанавливаемый сессионный параметр; – superuser - сессионный параметр суперпользователя; – user - сессионный параметр.

У каждого параметра имеется контекст его применения, определяющий способ его изменения. Например:

postgres=# \dconfig+ listen_addresses|work_mem
                List of configuration parameters
     Parameter    |   Value  |  Type   |   Context  | Access privileges
------------------+----------+---------+------------+---------------
 listen_addresses | *        | string  | postmaster |
 work_mem         | 4MB      | integer | user       |
(2 rows)

Контекст параметра указан в представлении pg_settings в поле context. https://www.postgresql.org/docs/15/view-pg-settings.html.

Файлы конфигурации​

Файл конфигурации postgresql.conf​

$ sudo -E -u postgres postgres -C config_file 2> /dev/null
/pgdata/06/data/postgresql.conf
  • Настраивает параметры для всего экземпляра.
  • По умолчанию находится в каталоге данных PGDATA.
  • Изменить местонахождение можно, указав при запуске postgres опцию -c config_file.
  • Порядок изменения параметра зависит от его контекста, требуется либо рестарт, либо перечитывание конфигурации.
  • В примере опция -C вывела значение параметра config_file.

При загрузке экземпляра первым считываются настройки параметров конфигурации из файла postgresql.conf. Это текстовый файл со строками вида параметр=значение. Комментарии после #. Файл считывается строка за строкой с первой до последней незакомментированной строки и если в более ранней строке назначено одно значение некоторого параметра, а далее этому же параметру в другой строке назначено другое значение, то в итоге параметру будет назначено последнее значение.

По умолчанию конфигурационный файл находится непосредственно в каталоге данных кластера PGDATA. Однако, это именно тот случай, когда удобно задать значение параметра настройки опцией командной строки при запуске головного процесса postgres. Например, так:

postgres -c config_file=/etc/pangolin/6.2.0/postgresql.conf -D
/pgdata/06/data

Здесь посредством опции -c установлено значение параметра конфигурации, указывающего местоположение конфигурационного файла. Наоборот, обратиться к экземпляру PostgreSQL и узнать значение какого-либо параметра в командной строке можно с помощью опции -C, как это показано на слайде.

Достаточно ли простого перечитывания файла конфигурации после изменения параметра или же необходима перезагрузка определяет контекст этого параметра.

https://www.postgresql.org/docs/15/config-setting.html.

Подключаемые файлы​

$ grep -i include $PGDATA/postgresql.conf
# CONFIG FILE INCLUDES
#include_dir = '...' # include files ending in '.conf' from #include_if_exists = '...' # include file only if it exists #include = '...' # include file

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

В конце файла postgresql.conf находятся директивы подключения дополнительных файлов конфигурации, в которые удобно помещать настройки параметров, переписывающие более ранние установки.

Вместо прямого редактирования файла конфигурации postgresql.conf можно использовать возможность подключения дополнительных файлов конфигурации, заключающаяся в том, что в конце файла postgresql.conf устанавливают одну из директив:

  • include - подключение заданного файла конфигурации;
  • include_if_exists - подключение файла при его наличии;
  • include_dir - подключить все файла с расширением .conf в заданном каталоге.

https://www.postgresql.org/docs/15/config-setting.html.

Представления​

Представление pg_file_settings​

# select seqno, sourcefile, sourceline, name, setting from pg_file_settings where seqno < 6; 
seqno  | sourcefile                      | sourceline |        name                | setting
-------+---------------------------------+------------+----------------------------+---------
     1 | /pgdata/06/data/postgresql.conf | 65         | max_connections            | 100
     2 | /pgdata/06/data/postgresql.conf | 127        | shared_buffers             | 128MB
     3 | /pgdata/06/data/postgresql.conf | 150        | dynamic_shared_memory_type | posix 
     4 | /pgdata/06/data/postgresql.conf | 244        | max_wal_size               | 1GB
     5 | /pgdata/06/data/postgresql.conf | 245        | min_wal_size               | 80MB
  • Показывает источник настройки параметра в файлах конфигурации.
  • Удобен для поиска ошибок конфигурации.
  • Текущие значения многих параметров могут быть настроены на уровне сессии или транзакции, то есть, отличаться от настроек в файлах.

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

Например:

postgres=# SELECT * FROM pg_file_settings WHERE name = 'max_connections' \gx
-[ RECORD 1 ]-------------------------------
sourcefile | /pgdata/06/data/postgresql.conf
sourceline | 65
seqno      | 1
name       | max_connections
setting    | 100
applied    | f
error      |
-[ RECORD 2 ]-------------------------------
sourcefile | /pgdata/06/data/postgresql.conf
sourceline | 1027
seqno      | 20
name       | max_connections
setting    | 100
applied    | t
error      |

Параметру max_connections назначены значения в 65 и 1027 строках. https://www.postgresql.org/docs/15/view-pg-file-settings.html.

Столбцы pg_file_settings​

  • sourcefile - имя файла конфигурации;
  • sourceline - номер строки в файле конфигурации, из которой получена эта запись;
  • seqno - порядковый номер записи;
  • name - параметр конфигурации;
  • setting - значение параметра;
  • applied - True, если значение может быть применено успешно;
  • error - сообщение об ошибке, либо NULL.

Все строки представления pg_file_settings связаны с соответствующими парами параметр=значение в конфигурационных файлах. Это позволяет легко отыскивать ошибки, например, связанные с назначением неправильных значений параметрам. Структура полей строк этого представления приведена на слайде.

Если ошибка связана с невозможностью прочитать файл или в файле имеется незакомментированная строка, не соответствующая положенной структуре параметр=значение. В представлении будут отсутствовать строки, показывающие на другие параметры. Например, если в первой строке файла postgresql.conf строка с неправильной структурой, в выводе представления будет следующее:

postgres=# SELECT * FROM pg_file_settings \gx
-[ RECORD 1 ]-------------------------------
sourcefile | /pgdata/06/data/postgresql.conf
sourceline | 1
seqno      | 1
name       | 
setting    | 
applied    | f
error      | syntax error

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

Показывает текущие настройки:

# SELECT * FROM pg_settings WHERE name = 'max_connections' \gx
-[ RECORD 1 ]------------------+-------------------------------------
name                           | max_connections 
setting                        | 100
unit                           |
category                       | Connections and Authentication / Connection Settings
short_desc                     | Sets the maximum number of concurrent connections.
extra_desc                     |
context                        | postmaster
vartype                        | integer
source                         | configuration file 
min_val                        | 1
max_val                        | 262143
enumvals                       |
boot_val                       | 100
reset_val                      | 100
sourcefile                     | /pgdata/06/data/postgresql.conf
sourceline                     | 1027
pending_restart                | f

Представление pg_settings выводит текущие значения параметров сервера. Они совсем не обязательно имеют те значения, которые были назначены в конфигурационных файлах. Например, многие параметры могут получить значение, которое будет назначено до конца сессии или даже транзакции. Проверим:

'max_parallel_workers' \gx
-[ RECORD 1 ]-----------------
name    | max_parallel_workers
setting | 8

postgres=# SET max_parallel_workers TO 10;
SET
postgres=# SELECT name, setting FROM pg_settings WHERE name = 'max_parallel_workers' \gx
-[ RECORD 1 ]-----------------
name    | max_parallel_workers
setting | 10

postgres=# \c
Вы подключены к базе данных "postgres" как пользователь "postgres". postgres=# SELECT name, setting FROM pg_settings WHERE name = 'max_parallel_workers' \gx
-[ RECORD 1 ]-----------------
name    | max_parallel_workers
setting | 8

В сессии было установлено значение параметра max_parallel_workers = 10. После переподключения параметр вернулся к значению 8.

Некоторые столбцы pg_settings:

  • name - параметр конфигурации;
  • setting - значение параметра;
  • unit - единица измерения;
  • boot_val - значение параметра при запуске сервера;
  • reset_val - значение параметра после RESET в сеансе;
  • sourcefile - файл конфигурации, в котором было задано текущее значение, или NULL;
  • sourceline - номер строки в файле конфигурации, в котором было задано текущее значение, или NULL;
  • pending_restart - true, если значение изменено в файле конфигурации, но требуется перезапуск; в противном случае — false.

Полное описание столбцов pg_settings в документации: https://www.postgresql.org/docs/15/view-pg-settings.html.

Для числовых параметров бывают важны поля представления min_val и max_val. Для перечислимых - enumvals, показывающее все возможные значения параметра.

Поле boot_val показывает назначаемое при старте сервера значение параметра, если оно не назначается где-либо иным способом.

Поле reset_val показывает к какому значению будет сброшен параметр в текущей сессии при выполнении команды RESET.

Поле pending_restart содержит true, если значение параметра было изменено в конфигурационном файле, и требуется рестартовать. Во всех остальных случаях в этом поле false.

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

  • Команда ALTER SYSTEM
  • Порядок применения настроек
  • Проверка применимости настроек
  • Сброс настройки
  • Привилегия изменять параметры

Команда ALTER SYSTEM​

postgres=# ALTER SYSTEM SET max_connections = 200; 
ALTER SYSTEM
postgres=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_connections = '200'
  • Команда ALTER SYSTEM изменяет настройки на уровне экземпляра, записывая строки конфигурации в файл postgresql.auto.conf в каталоге данных PGDATA.
  • В зависимости от контекста параметра требуется рестарт или перечитывание конфигурации.

Изменение параметра на уровне всего экземпляра можно осуществить посредством команды ALTER SYSTEM.

Эта команда сама по себе не меняет реальное значение параметра непосредственно в период исполнения. Результатом этой команды является запись в специальный файл конфигурации postgresql.auto.conf настройки значения параметра в стандартном виде параметр=значение. Этот файл не следует редактировать вручную, так как он предназначен для автоматизированного изменения параметров с помощью ALTER SYSTEM.

Для применения измененного значения параметра конфигурации необходимо либо перегрузить сервер для параметров с контекстом postmaster, либо перечитать файл конфигурации.

Перечитывание происходит при получении головным процессом экземпляра сигнала SIGHUP (Hang Up, его номер - 1, см. kill -l). Удобно либо передать сигнал командой killall -SIGHIP postgres, либо выполнить pg_ctl reload, либо от имени суперпользователя кластера выполнить SELECT pg_reload_conf();. https://www.postgresql.org/docs/15/sql-altersystem.html.

Порядок применения настроек​

postgres=# ALTER SYSTEM SET max_connections = 200; 
ALTER SYSTEM
postgres=# ALTER SYSTEM SET shared_buffers = '512MB'; 
ALTER SYSTEM
postgres=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command. 
max_connections = '200'
shared_buffers = '512MB'

Настройки в postgresql.auto.conf применяются после основного файла конфигурации postgresql.conf, а также дополнительных подключаемых файлов конфигурации.

Параметры, содержащиеся в postgresql.auto.conf прочитываются после параметров в postgresql.conf. Поэтому, если параметр был установлен с помощью ALTER SYSTEM, то он при применении перепишет установленное ранее в postgresql.conf значение. Если в postgresql.auto.conf параметр встречается несколько раз, актуальным является последнее вхождение параметра.

Проверка применимости настроек​

$ sudo systemctl restart postgresql
$ psql -U postgres -d postgres -c "select sourcefile, sourceline, name, setting,
applied from pg_file_settings where name in ('max_connections', 'shared_buffers');"
              sourcefile              | sourceline |      name       | setting | ap
--------------------------------------+------------+-----------------+---------+---
/pgdata/06/data/postgresql.conf       | 65         | max_connections | 100     | f
/pgdata/06/data/postgresql.conf       | 127        | shared_buffers  | 128MB   | f
/pgdata/06/data/postgresql.conf       | 1027       | max_connections | 100     | f
/pgdata/06/data/postgresql.auto.conf  | 3          | max_connections | 200     | t
/pgdata/06/data/postgresql.auto.conf  | 4          | shared_buffers  | 512MB   | t

В примере командой ALTER SYSTEM изменены настройки для двух параметров, контекст которых требует перезагрузки экземпляра.

После перезагрузки в поле applied представления pg_file_settings видно, что применены настройки из postgresql.auto.conf.

В примере на слайде еще раз продемонстрировано удобство представления pg_file_settings - в нем легко можно находить не только строки с ошибками, но и обнаруживать строки, в которых параметр был переписан.

Параметр max_connections был установлен в 65 строке файла postgresql.conf, но переписан в 3 строке файла postgresql.auto.conf. Параметр shared_buffers также был переписан.

Сброс настройки​

postgres=# ALTER SYSTEM RESET max_connections; 
ALTER SYSTEM
postgres=# \! cat $PGDATA/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_buffers = '512MB'

Команда ALTER SYSTEM RESET <параметр> сбрасывает одну настройку, стирая соответствующую строку в postgresql.auto.conf.

ALTER SYSTEM RESET ALL стирает все строки настроек в postgresql.auto.conf.

Настройки будут применены при рестарте или перечитывании.

Выполним три последовательные настройки параметров:

postgres=# ALTER SYSTEM SET max_connections TO 110;
ALTER SYSTEM
postgres=# ALTER SYSTEM SET max_connections TO 120;
ALTER SYSTEM
postgres=# ALTER SYSTEM SET max_parallel_workers TO 15;
ALTER SYSTEM
postgres=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_connections = '120'
max_parallel_workers = '15'

Обратите внимание на настройку nax_connections - она в файле встречается единожды.

Сбросим эту настройку и добавим work_mem:

postgres=# ALTER SYSTEM RESET max_connections;
ALTER SYSTEM
postgres=# ALTER SYSTEM SET work_mem TO '64MB';
ALTER SYSTEM
postgres=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_parallel_workers = '15'
work_mem = '64MB'

Теперь сбросим все настройки:

postgres=# ALTER SYSTEM RESET ALL;
ALTER SYSTEM

Привилегия изменять параметры​

postgres@student=# GRANT SET, ALTER SYSTEM ON PARAMETER log_statement TO student; 
GRANT
postgres@student=# \dconfig+ log_statement
                List of configuration parameters
   Parameter   | Value | Type |  Context  |  Access privileges
---------------+-------+------+-----------+----------------------
 log_statement | none  | enum | superuser | postgres=sA/postgres+
               |       |      |           | student=sA/postgres
 (1 row)

Суперпользователь может предоставить права на изменение параметра с не-сессионным контекстом непривилегированному пользователю. Проверить наличие этих прав можно с помощью метакоманды \dconfig+

В современных версиях PostgreSQL появилась возможность предоставить непривилегированной роли право менять конкретные параметры с контекстом superuser. В примере на слайде командой GRANT роли student предоставлена привилегия изменять параметр log_statement в сессиях с помощью SET и ALTER SYSTEM. https://www.postgresql.org/docs/15/sql-grant.html.

https://www.postgresql.org/docs/15/ddl-priv.html.

Подробнее о привилегиях в главе "Авторизация и аутентификация".

Другие параметры​

  • Сессионные параметры
  • Транзакционные параметры
  • Пользовательские параметры

Сессионные параметры​

student=> SHOW work_mem; 
4MB
student=> SET work_mem TO '8MB'; 
SET
student=> SHOW work_mem;
8MB 
student=> \c
You are now connected to database
"student" as user "student".
student=> SHOW work_mem; 
4MB

Параметрам с сессионным контекстом устанавливают значения командой SET.

Установленное значение по умолчанию действует до конца сеанса.

Для установки параметра текущей сессии можно также использовать функцию set_config, третий параметр которой должен быть установлен в false.

Команда SET (эквивалент SET SESSION) устанавливает сессионный параметр. При выходе из сессии он сбрасывается. Если SET или SET SESSION выполнены в транзакции (после BEGIN), то после фиксации транзакции командой COMMIT, параметр остается до конца сессии. Если же транзакция подверглась откату ROLLBACK, параметр сбрасывается. Если в транзакции выполнена команда SET LOCAL, параметр сбрасывается после любого выхода из транзакции:

postgres=# BEGIN;
BEGIN
postgres=*# SET LOCAL work_mem TO '128MB';
SET
postgres=*# SET SESSION hash_mem_multiplier TO 3;
SET
postgres=*# COMMIT;
COMMIT
postgres=# \dconfig+ work_mem|hash_mem_multiplier
                List of configuration parameters
     Parameter       |   Value  |  Type   | Context  | Access
---------------------+----------+---------+----------+-----------
 hash_mem_multiplier | 3        | real    | user     |
 work_mem            | 4MB      | integer | user     |
(2 rows)

Установить значение параметра можно также функцией set_config(), а получить - current_setting().

https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-ADMIN-SET.

Транзакционные параметры​

student=> BEGIN;
BEGIN
student=*> SET work_mem TO '8MB'; 
SET
student=*> SHOW work_mem;
8MB
student=*> ROLLBACK; 
ROLLBACK
student=> SHOW work_mem;
4MB

Параметры с сессионным контекстом, ограниченные текущей транзакцией, можно устанавливать командой SET.

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

Команда SET LOCAL сразу ограничивает назначение параметра текущей транзакцией.

Функция set_config с третьим параметром true также устанавливает параметры до конца текущей транзакции.

Если параметр был установлен с помощью любой формы SET в блоке транзакции и транзакция подверглась откату, параметр сбрасывается. В примере на слайде в блоке транзакции параметру work_mem было установлено значение 8Мб. Поскольку транзакция была отменена ROLLBACK, то при выходе из блока транзакции параметр вернулся к исходному значению https://www.postgresql.org/docs/15/sql-set.html.

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

student=> SELECT name FROM pg_settings WHERE name ~ 'psql\.'; psql.save_history
student=> SHOW psql.save_history; 
on
student=> SELECT current_setting('psql.save_history'); 
on
student=> SELECT count(name) FROM pg_settings WHERE name ~ '\.'; 
53

Если в имени параметра присутствует символ точка, параметр называется пользовательским для отличия их от стандартных параметров PostgreSQL.

Установить значение пользовательского параметра можно функцией set_config().

Функция current_setting, команда show, метакоманда \dconfig выводят значения пользовательских параметров.

Имена пользовательских параметров отличаются от обычных - в их имени обязательно присутствует точка. Некоторые из них:

postgres=# SELECT name FROM pg_settings WHERE name ~ '\.' LIMIT 25; 
                           name
-----------------------------------------------------------
 loading_control.check_allow_list
 loading_control.check_hash
 password_policy.allow_hashed_password
 password_policy.alpha_numeric
 password_policy.check_syntax
 password_policy.custom_function
 password_policy.deduplicate_ssl_no_ssl_fail_auth_attepmts
 password_policy.deny_default
 password_policy.expire_warning
 password_policy.failure_count_interval
 password_policy.grace_login_limit
 password_policy.grace_login_time_limit
 password_policy.illegal_values
 password_policy.in_history
 password_policy.lockout
 password_policy.lockout_duration
 password_policy.max_age
 password_policy.max_failure
 password_policy.max_inactivity
 password_policy.max_rpt_chars
 password_policy.min_age
 password_policy.min_alpha_chars
 password_policy.min_length
 password_policy.min_lowercase
 password_policy.min_special_chars
(25 строк)

Итоги​

  • Параметры настраивают поведение СУБД, их можно устанавливать для всего экземпляра, конкретной роли, базы данных, сеанса или транзакции.
  • Основной конфигурационный файл - postgresql.conf, команда ALTER SYSTEM вносит изменения в postgres.auto.conf.
  • Какой пользователь, на какой срок, а также, требуется ли рестарт сервера, определяет контекст параметра.
  • Представление pg_file_settings выводит назначения параметров в файлах конфигурации и не связано с текущей рабочей конфигурацией.
  • Представление pg_settings показывает текущую работающую конфигурацию.
  • Привилегия на установку конкретных параметров может быть предоставлена уполномоченной роли.
  • Параметры, в имени которых имеется точка, являются пользовательскими.