Администрирование PostgreSQL, часть 4 - Конфигурирование PostgreSQL

Четвёртая часть курса по PostgreSQL - разбираемся, откуда PostgreSQL берёт значения параметров, чем SET отличается от ALTER SYSTEM, и почему часть настроек применяется мгновенно, а часть - только после перезапуска 🐧.

Предисловие

В первой, второй и третьей частях мы почти не трогали настройки сервера - разве что мельком глянули shared_buffers и work_mem через SHOW. Сегодня разберёмся куда серьёзнее: откуда PostgreSQL вообще берёт значение каждого параметра, что произойдёт, если поменять его через SET, а что - через ALTER SYSTEM, и почему на один и тот же параметр эти два способа могут повлиять совершенно по-разному.

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

Вводные данные

ПО, используемое в статье:

Стенд - тот самый контейнер postgres из первой части, запущенный и доступный на 127.0.0.1:5432.

Где физически лежат настройки

cd ~/Postgres

docker compose exec postgres psql -U ivan -d raven

Спросим у самого сервера, где что лежит:

SHOW config_file;

SHOW hba_file;

SHOW data_directory;

Так как в первой части мы явно задавали PGDATA=/var/lib/postgresql/data/pgdata, data_directory и config_file будут смотреть именно туда - initdb при первом запуске контейнера сгенерировал в этом каталоге postgresql.conf, postgresql.auto.conf и pg_hba.conf со значениями по умолчанию.

Заглянуть в файлы можно прямо из хоста, не заходя в контейнер интерактивно:

docker compose exec postgres cat /var/lib/postgresql/data/pgdata/postgresql.conf | head -n 30

docker compose exec postgres cat /var/lib/postgresql/data/pgdata/postgresql.auto.conf

• postgresql.auto.conf на свежем стенде почти пустой - там только служебный комментарий-предупреждение:

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

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

Приоритет источников: представление pg_settings

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

SELECT name, setting, source, context FROM pg_settings WHERE name = 'work_mem';

• source - откуда взято действующее значение (default, configuration file, session и т.д.);
• context - при каких условиях параметр вообще можно поменять, к этому вернёмся отдельно.

Порядок, в котором PostgreSQL ищет значение параметра, от низкого приоритета к высокому:

1. встроенное значение по умолчанию (компилируется в сам сервер);
2. postgresql.conf;
3. postgresql.auto.conf (то есть ALTER SYSTEM);
4. настройки на уровне конкретной базы данных или роли (ALTER DATABASE ... SET, ALTER ROLE ... SET);
5. настройки текущего сеанса (SET, PGOPTIONS, параметры строки подключения).

Каждый следующий уровень перекрывает предыдущий, но только для того, кто его установил - это и есть источник всех интересных эффектов ниже.

В DBeaver то же самое представление доступно без единой строчки SQL - в свойствах подключения, вкладка Configuration, которую мы уже открывали в прошлой части: там есть поиск по имени параметра и колонка с текущим значением.

SET - изменения на уровне сессии

Откроем две сессии psql, как в прошлом уроке - без этого разница между SET и ALTER SYSTEM не так наглядна.

docker compose exec postgres psql -U ivan -d raven

В обеих сессиях для начала проверим значение по умолчанию:

SHOW work_mem;

 work_mem 
----------
 4MB

SHOW work_mem;

 work_mem 
----------
 4MB

Меняем значение только в сессии A:

SET work_mem = '64MB';

SHOW work_mem;

 work_mem 
----------
 64MB

SHOW work_mem; -- как будто ничего не произошло

 work_mem 
----------
 4MB

SET меняет параметр только в рамках текущего подключения. Сессия B ни о чём не узнала и не узнает, пока не выполнит собственный SET.

Есть и более узкий вариант - SET LOCAL, который живёт только до конца текущей транзакции:

BEGIN;

SET LOCAL work_mem = '128MB';

SHOW work_mem; -- 128MB

COMMIT; -- или ROLLBACK

SHOW work_mem; -- снова обычное значение сессии

Вернуть значение к тому, что было до SET, можно командой RESET:

RESET work_mem;

ALTER SYSTEM - персистентные изменения

ALTER SYSTEM - это SQL-обёртка над правкой postgresql.auto.conf, доступная только суперпользователю:

ALTER SYSTEM SET work_mem = '32MB';

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

docker compose exec postgres cat /var/lib/postgresql/data/pgdata/postgresql.auto.conf

Но в обеих открытых сессиях SHOW work_mem; пока покажет старое значение - файл изменился, но сервер об этом ещё не знает. Нужно перечитать конфигурацию:

SELECT pg_reload_conf();

А теперь самое интересное - смотрим на обе сессии:

SHOW work_mem;

 work_mem 
----------
 32MB

SHOW work_mem;

 work_mem 
----------
 64MB

Сессия B, у которой не было собственного SET, подхватила новое значение из postgresql.auto.conf сразу после pg_reload_conf(), без переподключения. А сессия A имеет 64MB - её персональный SET перекрывает конфигурационное значение, пока она сама не сделает RESET:

RESET work_mem;

SHOW work_mem;

 work_mem 
----------
 32MB

Отсюда практическое правило: если параметр “не применяется” после ALTER SYSTEM + pg_reload_conf() - вероятнее всего в этой же сессии до этого выполняли ручной SET.

Убрать персистентное значение и вернуться к postgresql.conf можно так:

ALTER SYSTEM RESET work_mem;

SELECT pg_reload_conf();

Context: когда хватит reload, а когда нужен restart

Не все параметры одинаковы - у каждого есть context, определяющий, каким способом его вообще можно поменять:

work_mem из примера выше - как раз context = user. А вот max_connections - классический postmaster:

ALTER SYSTEM SET max_connections = 300;

SELECT pg_reload_conf();

SELECT name, setting, pending_restart FROM pg_settings WHERE name = 'max_connections';

      name       | setting | pending_restart 
-----------------+---------+-----------------
 max_connections | 100     | t

Столбец pending_restart = t говорит: значение записано, но не применится, пока сервер не перезапустится целиком. У нас это одна команда:

systemctl --user restart postgres

SHOW max_connections; -- теперь 300

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

PostgreSQL позволяет заводить и свои собственные “переменные” - удобно, например, для передачи контекста приложения внутрь триггеров или политик RLS. Единственное требование - имя обязательно с точкой, namespace.key:

SELECT set_config('raven.tenant', 'demo', false);

SELECT current_setting('raven.tenant');

 current_setting 
-----------------
 demo

Третий аргумент set_config - is_local: false действует до конца сессии (как обычный SET), true - только до конца транзакции (как SET LOCAL). Такие параметры точно так же можно закрепить постоянно через ALTER SYSTEM SET raven.tenant = 'demo';, если он должен быть значением по умолчанию для всего кластера.

Возможные проблемы

• изменил ALTER SYSTEM, а SHOW показывает старое значение

Забыли SELECT pg_reload_conf(); - ALTER SYSTEM только пишет файл, применяет изменения именно reload/restart.

• сделал reload, а по факту всё равно старое значение

Смотрите пункт про SET/SET LOCAL в этой же сессии выше по разговору - сессионное значение перекрывает конфигурационное, пока не будет RESET.

• ошибка в конфиге не убивает сервер, но и не применяется

Если руками поправить postgresql.conf (например, при кастомном образе с примонтированным файлом) и допустить опечатку - PostgreSQL при reload просто проигнорирует некорректную строку и продолжит работать на старом значении. Проверить, что не применилось и почему, можно так:

SELECT * FROM pg_file_settings WHERE applied = false;

Кстати, после наших изменений количества соединений, увидим один applied = false:

Послесловие

Из этой части курса стоит уяснить одну мысль: SET - это про “здесь и сейчас, для меня”, ALTER SYSTEM - про “для всех и навсегда, но не мгновенно”.

В следующем уроке - обслуживание: VACUUM и WAL. Как раз к настройкам autovacuum_vacuum_scale_factor и checkpoint_timeout, о которых сегодня мы только слышали мельком через pg_settings, вернёмся уже предметно.

Спасибо, что читаете. Успехов в изучении PostgreSQL! 🐧

Используемые материалы

• Документация: файлы конфигурации
• Документация: ALTER SYSTEM
• Документация: представление pg_settings

👨‍💻Ну и…

Не забывайте про нашу телегу📱и чат 💬

Или может хотите стать соавтором? Тогда клик сюда🔗

Всех благ✌️

That should be it. If not, check the logs 🙂