Уровень 2.0

Предусловие: изучен модуль «Архитектура» данного курса.

В этой главе мы рассмотрим, как управлять конфигурацией сервера СУБД Pangolin: какие параметры существуют, как и где их задавать, а также как отслеживать текущие настройки.

Конфигурация сервера

Настройка СУБД осуществляется изменением значений параметров. Значения можно устанавливать для различных уровней:

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

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

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

Еще один способ повлиять на работу сервера — переменные окружения. Так, например, переменная окружения PGDATA задает имя каталога данных кластера. По соглашению имена переменных окружения для PostgreSQL начинаются с префикса PG. Установка переменной окружения PGOPTIONS позволяет клиентам, базирующимся на libpq, передать строку с настройками параметров непосредственно серверу.

Параметры

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

postgres@postgres=# SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

postgres@postgres=# SHOW WORK_MEM;

 work_mem
----------
 4MB
(1 row)

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

postgres@postgres=# \dconfig+ wal_level

              List of configuration parameters
 Parameter |  Value  | Type |  Context   | Access privileges
-----------+---------+------+------------+-------------------
 wal_level | replica | enum | postmaster |
(1 row)

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

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

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

postgres@postgres=# 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 — сессионный параметр, доступен любому пользователю.

Посмотрим контексты двух параметров listen_addresses и work_mem:

postgres@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)

listen_addresses имеет контекст postmaster – значит для его изменения нужен перезапуск. work_mem имеет контекст user – его можно менять в любой момент в рамках сессии.

Контекст параметра указан в представлении pg_settings в поле context.

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

postgresql.conf

Самый основной способ установки параметров для всего экземпляра — определение их значений в конфигурационном файле postgresql.conf. По умолчанию postgresql.conf находится в каталоге PGDATA.

Обратиться к экземпляру PostgreSQL и узнать значение какого-либо параметра в командной строке можно с помощью опции -C, например, узнаем местоположение postgresql.conf:

[postgres@ServerName ~]$ postgres -C config_file 2> /dev/null

/pgdata/06/data/postgresql.conf

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

При необходимости можно изменить местоположение конфигурационного файла:

[postgres@ServerName ~]$ postgres -c config_file=/etc/pangolin/{pangolin-version}/postgresql.conf -D /pgdata/06/data

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

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

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

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

[postgres@ServerName ~]$ grep -i include $PGDATA/postgresql.conf

                                        # can include strftime() escapes
# CONFIG FILE INCLUDES
#include_dir = '...'                    # include files ending in '.conf' from
#include_if_exists = '...'              # include file only if it exists
#include = '...'                        # include file
                                                                                                        # Can include strftime() escapes.

Где:

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

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

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

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

postgres@postgres=# SELECT * FROM pg_file_settings;

           sourcefile            | sourceline | seqno |                 name                 |      setting       | applied | error
---------------------------------+------------+-------+--------------------------------------+--------------------+---------+-------
 /pgdata/06/data/postgresql.conf |         74 |     1 | max_connections                      | 100                | f       |
 /pgdata/06/data/postgresql.conf |        136 |     2 | shared_buffers                       | 128MB              | t       |
 /pgdata/06/data/postgresql.conf |        159 |     3 | dynamic_shared_memory_type           | posix              | t       |
 /pgdata/06/data/postgresql.conf |        250 |     4 | max_wal_size                         | 1GB                | t       |
 /pgdata/06/data/postgresql.conf |        251 |     5 | min_wal_size                         | 80MB               | t       |
...

Где:

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

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

Если в файле есть синтаксическая ошибка, например, строка не в формате параметр=значение, в представлении появится запись с пустыми полями name и setting и текстом ошибки:

postgres@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

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

postgres@postgres=# 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      | 1126
pending_restart | f

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

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

Полное описание столбцов pg_settings смотрите в документации.

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

Сначала посмотрим на текущее значение параметра max_parallel_workers:

postgres@postgres=# SELECT * FROM pg_settings WHERE name = 'max_parallel_workers' \gx

-[ RECORD 1 ]-----------------
name    | max_parallel_workers
setting | 8

А что это за параметр?

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

Теперь изменим его в рамках текущей сессии с помощью команды SET:

postgres@postgres=# SET max_parallel_workers TO 10;

SET

Проверим, что значение изменилось:

postgres@postgres=# SELECT name, setting FROM pg_settings WHERE name = 'max_parallel_workers' \gx

-[ RECORD 1 ]-----------------
name    | max_parallel_workers
setting | 10

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

postgres@postgres=# \c

You are now connected to database "postgres" as user "postgres".

Снова проверим значение параметра:

postgres@postgres=# SELECT name, setting FROM pg_settings WHERE name = 'max_parallel_workers' \gx

-[ RECORD 1 ]-----------------
name    | max_parallel_workers
setting | 8

Значение вернулось к исходному, так как сессионная настройка не сохраняется между подключениями.

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

Команда ALTER SYSTEM

Команда ALTER SYSTEM позволяет изменять настройки на уровне экземпляра. Она записывает новые значения в специальный файл postgresql.auto.conf в каталоге данных PGDATA.

postgres@postgres=# ALTER SYSTEM SET max_connections = 200;

ALTER SYSTEM

Посмотрим, что изменилось:

postgres@postgres=# \! cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_connections = '200'

Для применения изменений нужно:

• перезапустить сервер для параметров с контекстом postmaster как в нашем примере;
• перечитать конфигурационные файлы для параметров с контекстом sighup.

Перечитывание происходит при получении головным процессом экземпляра сигнала SIGHUP (Hang Up, номер -1, смотрите kill -l). Есть несколько способов передать сигнал:

• командой killall -s SIGHUP postgres;
• выполнить pg_ctl reload;
• от имени суперпользователя кластера выполнить SELECT pg_reload_conf();.

Внимание!

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

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

Файл postgresql.auto.conf читается после основного файла postgresql.conf и всех подключенных файлов. Поэтому если параметр задан и там, и там, применяется значение из postgresql.auto.conf. Если в этом файле параметр встречается несколько раз, действует последнее.

Изменим два параметра и перезапустим сервер:

postgres@postgres=# ALTER SYSTEM SET max_connections = 200;

ALTER SYSTEM

postgres@postgres=# ALTER SYSTEM SET shared_buffers = '512MB';

ALTER SYSTEM

Проверим записи в postgresql.auto.conf:

postgres@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'

Перезагрузим сервер для применения настроек:

[student@ServerName ~]$ sudo systemctl restart postgresql

Проверим, какие значения применились:

[student@ServerName ~]$ sudo su - postgres

[postgres@ServerName ~]$ 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

Видно, что значения из postgresql.auto.conf применены (applied = t), а из основного файла – нет (f).

Внимание!

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

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

postgres@postgres=# ALTER SYSTEM SET max_connections TO 110;

ALTER SYSTEM

postgres@postgres=# ALTER SYSTEM SET max_connections TO 120;

ALTER SYSTEM

Посмотрим, что записалось в файл:

postgres@postgres=# \! cat $PGDATA/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_connections = '120'

В postgresql.auto.conf записана только последняя настройка параметра max_connections.

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

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

postgres@postgres=# ALTER SYSTEM RESET max_connections;

ALTER SYSTEM

Проверим:

postgres@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 ALL удаляет все строки настроек в postgresql.auto.conf.

postgres@postgres=# ALTER SYSTEM RESET ALL;

ALTER SYSTEM

Внимание!

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

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

Суперпользователь может делегировать права на изменение параметров с несессионным контекстом обычным пользователям. Например, разрешим пользователю student менять параметр log_statement с контекстом superuser:

postgres@postgres=# GRANT SET, ALTER SYSTEM ON PARAMETER log_statement TO student;

GRANT

Проверим:

postgres@postgres=# \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)

В примере командой GRANT роли student предоставлена привилегия изменять параметр log_statement в сессиях с помощью SET и ALTER SYSTEM.

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

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

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

Параметры с контекстом user можно изменять командой SET (эквивалентен SET SESSION). Они действуют доя конца сессии.

postgres@postgres=# SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

postgres@postgres=# SET work_mem TO '8MB';

SET

postgres@postgres=# SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

После переключения значение сбрасывается:

postgres@postgres=# \c

You are now connected to database "postgres" as user "postgres".

postgres@postgres=# SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

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

Начнем транзакцию:

postgres@postgres=# BEGIN;

BEGIN

Установим параметр work_mem с помощью SET LOCAL. Он будет действовать только до конца транзакции:

postgres@postgres=*# SET LOCAL work_mem TO '128MB';

SET

Установим параметр hash_mem_multiplier с помощью SET SESSION:

postgres@postgres=*# SET SESSION hash_mem_multiplier TO 3;

SET

Зафиксируем транзакцию:

postgres@postgres=*# COMMIT;

COMMIT

Теперь проверим значения параметров:

postgres@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)

work_mem, установленный через SET LOCAL, сбросился после завершения транзакции (вернулся к 4MB). hash_mem_multiplier, установленный через SET SESSION, сохранился после фиксации транзакции (остался равным 3).

Подсказка

Для установки параметра можно также использовать функцию set_config(). Третий аргумент (is_local) которой аналогичен SET LOCAL, если true, и SET SESSION, если false.

А получить значение параметра можно функцией current_setting().

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

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

Начнем транзакцию:

postgres@postgres=# BEGIN;

BEGIN

Установим work_mem с помощью SET:

postgres@postgres=*# SET work_mem TO '8MB';

SET

Внутри транзакции значение изменилось:

postgres@postgres=*# SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

Откатим транзакцию:

postgres@postgres=*# ROLLBACK;

ROLLBACK

После отката параметр вернулся к исходному значению:

postgres@postgres=# SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

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

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

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

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

Параметры, в имени которых присутствует точка, называются пользовательскими. Они не входят в стандартный набор.

Установить значение пользовательского параметра можно функцией set_config(). Функция current_setting(), команда SHOW, метакоманда \dconfig выводят значения пользовательских параметров.

Найдем все пользовательские параметры, относящиеся к psql:

postgres@postgres=# SELECT name FROM pg_settings WHERE name ~ 'psql\.';

       name
-------------------
 psql.save_history
(1 row)

Видно, что существует параметр psql.save_history. Проверим его текущее значение командой SHOW:

postgres@postgres=# SHOW psql.save_history;

 psql.save_history
-------------------
 on
(1 row)

Тот же результат можно получить с помощью функции current_setting():

postgres@postgres=# SELECT current_setting('psql.save_history');

 psql.save_history
-------------------
 on
(1 row)

Теперь посмотрим, сколько всего пользовательских параметров (с точкой в имени) существует в системе:

postgres@postgres=# SELECT count(name) FROM pg_settings WHERE name ~ '\.';

 count
-------
    72
(1 row)

Посмотрим первые пять таких параметров:

postgres@postgres=# SELECT name FROM pg_settings WHERE name ~ '\.' LIMIT 5;

                           name
-----------------------------------------------------------
 loading_control.check_allow_list
 loading_control.check_hash
 password_policy.allow_hashed_password
 password_policy.alpha_numeric
 password_policy.check_syntax
(5 rows)

Итоги

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

Самопроверка

Вопрос 1

Как называется файл конфигурации, который содержит основные (базовые) настройки сервера?

pg_config.txtpg_settings.confpostgresql.confdb_config.conf

Вопрос 2

Что делает команда ALTER SYSTEM SET...?

Добавляет запись с указанным значением параметра в основной файл конфигурации postgresql.confДобавляет запись с указанным значением параметра в дополнительный файл конфигурации postgresql.auto.confСразу же применяет значение параметра в текущей сессииСразу же применяет значение параметра в текущей транзакцииСразу же применяет значение параметра в текущем сеансе

Вопрос 3

Что делает команда ALTER SYSTEM RESET <параметр>?

Удаляет все строки с объявлением этого параметра из postgresql.auto.confУдаляет все строки с объявлением этого параметра из postgresql.confНаходит строчку в postgresql.auto.conf, где объявляется этот параметр, и заменяет значением по умолчаниюНаходит строчку в postgresql.conf, где объявляется этот параметр, и заменяет значением по умолчанию

Вопрос 4

О чем сообщает значение postmaster поля context таблицы pg_catalog.pg_settings?

Для изменения значения данного параметра требуется перезапуск сервераДля изменения значения данного параметра достаточно перечитать конфигурацию без перезапускаЗначение данного параметра нельзя изменитьДанный параметр будет применен только для мастер-процесса postmasterПроцессу postmaster можно послать сигнал SIGQUIT для применения параметра