Autovacuum postgresql 1с настройка

Обновлено: 07.07.2024

Познакомимся поближе с процессом VACUUM и теми задачами, которые он решает в PostgreSQL. Этот процесс чистит таблицы, обновляет статистику и обновляет карты видимости и свободного пространства. А также борется с переполнением счетчика транзакций.

Варианты запуска очистки

Очищать таблицы или базы данных от ненужных версий строк можно с помощью следующих команд:

VACUUM таблица; – очищает таблицу;
VACUUM; – базу данных;
$ vacuumdb – обёртка для использования в ОС.

Процедура VACUUM выполняется параллельно с другими транзакциями. При этом частый запуск нагружает систему, редкий запуск приводит к росту размера файлов. Подробнее почитать про очистку PostgreSQL можно тут. Мы уже встречались с понятием VACUUM в статье “Изоляция и многоверсионность в Postgresql“.

Дополнительно к этому существует процесс автоматической очистки AUTOVACUUM:

autovacuum launcher – следит за таблицами и в случае необходимости запускает autovacuum worker;
autovacuum worker – занимается очисткой таблиц.

Частота работы autovacuum worker зависит от частоты изменений таблицы. Чем активнее ведётся работа с таблицей, тем чаще туда приходит autovacuum. Autovacuum настраивается конфигурационными параметрами.

Как уже рассматривалось VACUUM и AUTOVACUUM не сжимает файл, а только очищает его образовывая в нем пустые пространства. Для полного перестроения файла, другими словами чтобы файл уменьшился, нужно использовать VACUUM FULL:

VACUUM FULL таблица; – очистка таблицы;
VACUUM FULL; – очистка базы;
$ vacuumdb --full – обёртка для использования в ОС.

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

Команда TRUNCATE блокирует таблицу и очищает её, при этом старые версии строк не сохраняются и файл уменьшается физически. Другой способ очистить таблицу это выполнить DELETE всех строк в ней, а затем запустить по этой таблице VACUUM FULL для очистки. TRUNCATE это транзакционная команда, поэтому её можно отменить (ROLLBACK).

Обновление статистики

Фоновый процесс autovacuum обновляет статистику. А статистика необходима планировщику, чтобы строить план запроса.

В ручную сбор статистику можно запустить с помощью следующих команд:

ANALYZE [таблица]; – обновить статистику по таблице;
ANALYZE; – обновить статистику по базе данных;
$ vacuumdb --analyze-only – обёртка для использования в ОС;
VACUUM ANALYZE; – выполнить очистку, а затем сбор статистики.

Существует много параметров конфигурации, которые управляют процессом autovacuum:

Обновление карт видимости и свободного пространства

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

Карта свободного пространства – нужна чтобы быстро вставлять новые версии строк.

Эти карты обновляет autovacuum:

после очистки, если на странице не осталось старых версий строк, autovacuum ставит галочку в карте видимости. Любой процесс, который запишет в эту страницу, эту галочку снимает.
когда autovacuum почистит страницу, в ней образуются дыры, и он обновляет карту свободного пространства.

Переполнение счётчика транзакций и заморозка

Счетчик транзакций 32-битный – это примерно 4 миллиарда значений. И он постоянно растёт! Если счетчик транзакций переполнится и начтет считать с нуля, то это приведет к большим проблемам на сервере. Для борьбы с этой проблемой существует процесс “Заморозка“.

При заморозке берутся версии строчек, в которых номер транзакции достаточно старый. То есть эта транзакция должна быть видна во всех новых снимках. Этот номер транзакции меняется на “минус бесконечность“. Система понимает, что эта строчка была создана когда-то давно и номер транзакции создавший её уже не имеет значения. Значит этот номер транзакции можно использовать повторно. Замороженные номера транзакций можно повторно использовать.

Почему не сделать счетчик 64 битным? В каждой версии строчки есть заголовок. Если счетчик будет 64 битным, то будет слишком много служебной информации на каждую версию строки.

Оптимизация производительности PostgreSQL для работы с 1С:Предприятие

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

Существуют разные рекомендации по оптимизации PostgreSQL для совместной работы с 1С, мы будем опираться на официальные рекомендации, изложенные на ИТС, также можно использовать онлайн-калькулятор для быстрого расчета некоторых параметров. Если данные калькулятора и рекомендации 1С будут расходиться - то предпочтение будет отдано рекомендациям 1С.

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

CPU - Core i5-4670 - 3.4/3.8 ГГц
RAM - 32 ГБ DDR3
Системный диск - SSD WD Green 120 ГБ
Диск для данных - 2 х SSD Samsung 860 EVO 250 ГБ - RAID1
СУБД - PostgresPro 11.6
Платформа - 8.3.16.1148
ОС - Debian 10 x64

Прежде всего выполним тестирование с параметрами по умолчанию:

Полученный результат - 22,32 по Гилеву высоким не назовешь, для субъективного контроля мы использовали конфигурацию Розница 2.2 с базой реального торгового предприятия объемом в 8 ГБ, в целом работу можно было признать удовлетворительной, но местами наблюдалась некоторая "задумчивость", особенно при открытии динамических списков.

Перейдем к оптимизации. Все изменения следует вносить в файл postgesql.conf, который располагается в Linuх для сборки от 1С по пути /etc/postgres/1x/main, а для сборки от PostgresPro в /var/lib/pgpro/1c-1x/data. В Windows данный файл располагается в каталоге данных, по умолчанию это C:\Program Files\PostgreSQL 1C\1х\data. Все параметры указаны в порядке их следования в конфигурационном файла.

Одним из основных параметров, используемых при расчетах, является объем оперативной памяти. При этом следует использовать то значение, которое вы готовы выделить серверу СУБД, за вычетом ОЗУ используемой ОС и другими службами, скажем, сервером 1С. В нашем случае будет использоваться значение в 24 ГБ.

Затем рассчитаем значения отдельных параметров с помощью калькулятора, для чего укажем ОС и версию Postgres, тип накопителя, количество доступной памяти и количество ядер процессора. В поле DB Type указываем Data Warehouses, количество соединений можем проигнорировать, так как вычисляемый результат будет значительно расходиться с рекомендациями 1С.

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

Максимальное число соединений, 1С рекомендует указанные выше значения, мы установили 1000.

Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение - четверть доступного объема памяти, в нашем случае 6 ГБ.

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

Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение. Это один из тех параметров, вычисляемое значение которого калькулятором существенно отличается от рекомендаций 1С. Для объема памяти в 24 ГБ рекомендуемыми значениями будут 375 - 750 МБ, мы выбрали 512 МБ.

Объем памяти для обслуживающих задач (автовакуум, реиндексация и т.д.), указываем рекомендованный калькулятором объем, в нашем случае 2 ГБ.

Максимальное количество открытых файлов на один процесс, в сборке от PostgresPro для Linux это значение по умолчанию.

Параметры процесса фоновой записи, который отвечает за синхронизацию страниц в shared_buffers с диском.

Допустимое число одновременных операций ввода/вывода. Для жестких дисков указывается по количеству шпинделей, для массивов RAID5/6 следует исключить диски четности. Для SATA SSD это значение рекомендуется указывать равным 200, а для быстрых NVMe дисков его можно увеличить до 500-1000. При этом следует понимать, что высокие значения в сочетании с медленными дисками сделают обратный эффект, поэтому подходите к этой настройке грамотно.

Важно! Параметр effective_io_concurrency настраивается только в среде Linux, в Windows системах его значение должно быть равно нулю.

Настройки фоновых рабочих процессов, выбираются исходя из количества процессорных ядер, берем значения из калькулятора. Выше указаны настройки для четырехъядерного СРU.

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

Альтернатива отключению fsync, позволяет серверу не ждать сохранения данных на диске, прежде чем сообщить клиенту об успешном завершении операции. Позволяет достаточно безопасно повысить производительность работы. В случае внезапного выключения питания могут быть потеряны несколько последних транзакций, но сама база останется в рабочем состоянии, также, как и при штатной отмене потерянных транзакций.

Задает размер буферов журнала предзаписи (WAL, он же журнал транзакций), если оставить эту настройку без изменений, то сервер будет автоматически устанавливать это значение в 1/32 от shared_buffers, но не менее 64 КБ и не более размера одного сегмента WAL в 16 МБ.

Указывает задержку в мс перед записью транзакций на диск при числе открытых транзакций, указанных во второй опции. Имеет смысл при количестве транзакций более 1000 в секунду, на меньших значениях эффекта не имеет.

Минимальный и максимальный размер файлов журнала предзаписи. Указываем значения из калькулятора, в нашем случае это 4 ГБ и 16 ГБ.

Скорость записи изменений на диск, рассчитывается как время между точками сохранения транзакций (чекпойнты) умноженное на данный показатель, позволяет растянуть процесс записи по времени и тем самым снизить одномоментную нагрузку на диски. В нашем случае использовано рекомендованное калькулятором максимальное значение 0,9.

Стоимость последовательного чтения с диска, является относительным числом, вокруг которого определяются все остальные переменные стоимости, данное значение является значением по умолчанию.

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

Так как это относительные значения, но не имеет смысла устанавливать random_page_cost ниже seq_page_cost, однако при применении производительных SSD имеет смысл понизить стоимость обоих значений, чтобы повысить приоритет дисковых операций по отношению к процессорным.

Для производительных SSD можно использовать значения:

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

Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования. При расчете следует использовать выделенный серверу объем RAM, а не полный объем ОЗУ. В нашем случае это 18 ГБ.

Включение автовакуума, это очень важный для производительности базы параметр. Не отключайте его!

Количество рабочих процессов автовакуума, рассчитывается по числу процессорных ядер, не менее 4, в нашем случае 4.

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

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

Максимальное количество блокировок в одной транзакции, рекомендация от 1С.

Данные опции специфичны для 1С и регулируют использование символа \ для экранирования.

Сохраним файл конфигурации и перезапустим PostgreSQL, в Linux это можно выполнить командами:

В Windows штатными средствами операционной системы, либо скриптами из поставки сборки PostgreSQL:

После чего снова выполним тестирование производительности, на этот раз мы получили следующий результат:

Как видим, достаточно несложные действия по оптимизации добавили серверу около 30% производительности, субъективные ощущения от работы с конфигурацией Розница также повысились, исчезло ощущение "задумчивости", повысилась отзывчивость системы.

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

Настройки PostgreSQL для работы с 1С:Предприятием. Часть 2

В статье описывается настройка PostgreSQL версий 9.6 и выше на максимальную производительность для работы с Платформой 1С:Предприятие. Предполагается, что сервер СУБД PostgreSQL является достаточно производительным и имеет не менее:

8 - 512 Gb RAM
4 - 256 CPU cores
RAID 0-1 или SSD

Рекомендуемые значения индивидуальны и зависят от системы и нагрузки на нее.

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

Настройки сервера для PostgreSQL

Рекомендуется изменять значение по умолчанию пути к директории pg_stat_temp так, чтобы она находилась отдельно от директории кластера. Причина в интенсивном изменении файлов в этой директории, что создает значительную нагрузку на дисковую подсистему. Директорию рекомендуется размещать в RAM-диске (для Windows) или tmpfs (для linux).

Параметры клиентских сеансов

row_security = off >= 9.5

Отключение контроля на уровне записей.

Параметры подключений

Выключение шифрования, которое может приводить к увеличению загрузки CPU.

Потребление оперативной памяти

Количество памяти, выделенной PostgreSQL для совместного кеша страниц. Эта память разделяется между всеми процессами PostgreSQL.

Максимальное количество страниц для временных таблиц - верхний лимит размера временных таблиц в каждой сессии.

work_mem = RAM/32..64 или 32MB..128MB

Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически максимально потребная память вычисляется как max_connections *work_mem, на практике она достигает такой величины крайне редко. Это рекомендательное значение используется оптимизатором: он оценивает размер памяти для выполнения запроса, и, если это значение больше work_mem, запрос будет выполняться с использованием временных таблиц (для промежуточных результатов, сортировки, группировки…). Work_mem не является в полном смысле лимитом: оптимизатор может сделать неправильную оценку, и запрос займёт больше памяти, чем изначально было выделено. Это значение можно уменьшать, следя за количеством создаваемых в системе временных файлов:

select sum(temp_files) as temp_files, pg_size_pretty(sum(temp_bytes)) as temp_size from pg_stat_database;

maintenance_work_mem = RAM/16..32 или work_mem * 4 или 256MB..4GB

Лимит памяти для обслуживающих задач, например вакуум, автовакуума или создания индексов.

В случае выявления существенной фрагментации памяти процессов PostgreSQL в Linux, имеет смысл воспользоваться переменной окружения (её нужно установить в файле/etc/systemd/system/postgresql-10.service):

Environment = MALLOC_MMAP_THRESHOLD_= 8192

Настройки WAL

Сброс буферов на диск (выполнение PostgerSQL системных вызовов fsync()). Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания.

Внимание: если RAID имеет кэш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кэша RAID контроллера! Иначе данные, записанные в кэш RAID, могут быть потеряны при выключении питания, и, как следствие, PostgreSQL не гарантирует целостность данных.

Выключение синхронной записи в WAL момент коммита транзакции. Создает риск потери последних нескольких транзакций (в течении 0.5-1" секунды), но гарантирует целостность базы данных. Может значительно увеличить производительность.

checkpoint_segments = 32..256 < 9.5

Максимальное количество сегментов WAL между точками восстановления - checkpoint. Слишком частые checkpoint приводят к значительной нагрузке на дисковую подсистему. Каждый сегмент имеет размер 16MB.

Степень "размазывания" checkpoint'a. Скорость записи во время checkpoint'а регулируется так, чтобы время checkpoint'а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target.

min_wal_size = 512MB .. 4G > = 9.5
max_wal_size = 2 * min_wal_size > = 9.5

Минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments.

commit_delay = 1000
commit_siblings = 5

Групповой коммит нескольких транзакций. Имеет смысл включать, если интенсивность транзакций превосходит 1000 TPS.

Фоновая запись на диск

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

bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400

Параметры, управляющие интенсивностью записи фонового процесса записи. За один циклbgwriterзаписывает не больше, чем было записано в прошлый цикл, умноженное наbgwriter_lru_multiplier, но не больше чем bgwriter_lru_maxpages.

Настройки выполнения очистки (автовакуума)

Внимание! Не выключайте автовакуум, это приведет к росту размеров базы и серьезной деградации производительности.

autovacuum_max_workers =" CPU "cores/4..2 но не меньше 4

Количество процессов автовакуума. Общее правило - чем больше запросов на запись выполняется в системе (такие системы называются OLTP), тем больше процессов.

Время сна процесса автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать «чиститься», что приведет у роста размера и снижению производительности работы. Малая величина приведет к бесполезной нагрузке.

Использование ресурсов ядра

Значение по умолчанию – 8000, его не нужно уменьшать. Оно может быть увеличено в зависимости от характера нагрузки (максимальное значение зависит от операционной системы). Один файл - это как минимум либо индекс либо таблица, но таблица/может состоять из нескольких файлов. Если PostgreSQL «упирается» в этот лимит, он начинает открывать/закрывать файлы, что может сказываться на производительности. Диагностировать проблему под Linux можно с помощью команды lsof.

Настройки планировщика запросов

effective_cache_size =" RAM - "shared_buffers

Оценка планировщика запроса о размере дискового кеша, доступного для одного запроса. Это представление влияет на оценку стоимости использования индекса. Чем выше это значение, тем больше вероятность, что оптимизатором будет выбираться сканирование по индексу (Index Scan), чем ниже, тем более вероятно, что будет выбрано последовательное сканирование (Seq Scan).

random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD

Стоимость чтения рандомной страницы, на которую будет опираться оптимизатор (по-умолчанию 4). Практическое значение параметра должно зависеть от «seek time» дисковой системы: чем он меньше, тем меньше должно быть значение random_page_cost (но не менее 1.0) . Излишне большое значение параметра увеличивает склонность PostgreSQL к выбору планов с сканированием всей таблицы (PostgreSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). Оценка стоимости последовательного чтения делается, в свою очередь, с учетом параметра seq_page_cost, который равен по умолчанию 1.

Задаёт максимальное число элементов в списке FROM, до которого планировщик будет объединять вложенные запросы с внешним запросом. При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

Задаёт максимальное количество элементов в списке FROM, до достижения которого планировщик будет сносить в него явные конструкции JOIN (за исключением FULL JOIN). При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

GEQO - генетический оптимизатор запросов PоstgreSQL, который осуществляет планирование запросов, применяя эвристический поиск вместо полного перебора отношений. Он позволяет сократить время планирования для сложных запросов с большим числом соединений, потому не рекомендуется его отключать. Однако надо учитывать, что полученный им план может оказаться менее эффективным и, как следствие, увеличится время выполнения запроса. Управлять его включением более тонко помогает следующий параметр:

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

Асинхронное поведение

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Допустимый диапазон от 1 до 1000. Значение по умолчанию равно 1, где это поддерживается, в остальных системах - 0.

Для одиночного диска можно условно поставить 1, для RAID - 2 или больше.

Сейчас эта оценка влияет только на выбор bitmap heap scan.

Параметры для платформы 1С:Предприятия

Разрешить использовать символ \ для экранирования.

Не выдавать предупреждение о использовании символа \ для экранирования.

Максимальное число блокировок индексов/таблиц в одной транзакции.

Количество одновременных соединений.

Параметры дополнительных модулей

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

Все остальные параметры имеют смысл, только если online_analyze.enable = on.

Включает синхронное автообновление статистики на временных таблицах.

Выполнение инструкции ANALYZE без опции VERBOSE.

Минимальное количество записей, предшествующее обновлению статистики.

«Доля» в величине таблицы, начиная с которой будет происходить автообновление.

Отслеживание изменений в рамках соединения (для локальных временных таблиц).

Таблицы в PostgreSQL представлены в виде страниц, размером 8 КБ, в которых размещены записи. Когда одна страница полностью заполняется записями, к таблице добавляется новая страница. При удалалении записей с помощью DELETE или изменении с помощью UPDATE, место где были старые записи не может быть повторно использовано сразу же.

Процесс очистки autovacuum, или команда VACUUM, пробегает по изменённым страницам и помечает такое место как свободное, после чего новые записи могут спокойно записываться в это место, то-есть размер файла таблицы физически не уменьшается.

Чтобы максимально уменьшить таблицу в PostgreSQL есть VACUUM FULL или CLUSTER, но оба эти способа требуют exclusively locks на таблицу (блокируют доступ к таблице на время работы, то есть в это время с таблицы нельзя ни читать, ни писать), что далеко не всегда является подходящим решением.

В принципе процедура очистки, autovacuum или VACUUM, все же может сама уменьшить размер таблицы убрав полностью пустые страницы, но только при условии что они находятся в самом конце таблицы.

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

Полностью отключить autovacuum можно параметром:

Так же для работы Autovacuum требуется параметр track_counts = on, в противном случае он работать не будет.

Отключать autovacuum крайне не рекомендуется, иначе имеет смысл самостоятельно запланировать регулярное выполнение команды VACUUM ANALYZE.

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

Можно управлять производительностью AUTOVACUUM, делая его более длительным но менее нагружающим систему.

По публикациям использование vacuum_cost_page_hit = 6, vacuum_cost_limit = 100, autovacuum_vacuum_cost_delay = 200ms уменьшает влияние AUTOVACUUM до 80%, но увеличивает время его выполнения втрое.

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

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

Конфигурационный файл postgresql.conf

Главный конфигурационный файл для кластера PostgreSQL – это postgresql.conf, в разных системах он может находится в разных местах. Так как мы собирали сервер из исходников и не меняли путь хранения этого файла, то по умолчанию он будет находится в каталоге PGDATA:

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

Самый точный способ узнать расположение этого файла, посмотреть из терминала psql:

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

Второй способ – из терминала psql:

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

Конфигурация сервера используя ALTER SYSTEM

Для настройки сервера также существует другой файл – postgresql.auto.conf. Он были придуман для настройки сервера из консоли psql. Читается этот файл после postgresql.conf, поэтому параметры из него имеют приоритет. Этот файл всегда находится в каталоге с данными (PGDATA).

Для создания параметров в файле postgresql.auto.conf нужно выполнить подобный запрос:

ALTER SYSTEM SET <параметр> TO <значение>;

Чтобы удалить параметр используем другой запрос:

ALTER SYSTEM RESET <параметр>;

А чтобы удалить все параметры из postgresql.auto.conf выполним:

ALTER SYSTEM RESET ALL;

Чтобы применить изменения нужно перечитать конфигурационные файлы как было описано выше.

Информация о текущих настройках сервера

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

pg_file_settings – какие параметры записаны в файлах postgresql.conf и postgresql.auto.conf;
pg_settings – текущие параметры, с которыми работает сервер.

Например посмотрим значение параметра config_file из представления pg_settings, который покажет конфигурационный файл текущего кластера:

Внесём изменения в параметр work_mem в postgresql.conf и postgresql.auto.conf. Затем посмотрим на все не закомментированные параметры в этих файлах:

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

Теперь посмотрим на реальное, текущее значение этого параметра:

В примере выше мы использовали расширенный режим (в конце запроса \gx), поэтому табличка перевёрнута. Разберём колонки:

name – имя параметра;
setting – текущее значение;
unit – единица измерения;
boot_val – значение по умолчанию (жёстко задано в коде postgresql);
reset_val – если перечитаем конфигурацию, то применится это значение;
source – источник, это значение по умолчанию;
sourcefile – если бы источником был конфигурационный файл, то тут был бы указан этот файл;
sourceline – номер строки в этом файле;
pending_restart – параметр изменили в конфигурационном файле и требуется перезапуск сервера. У нас требуется всего лишь перечитать конфигурацию;
context – действия, необходимые для применения параметра, может быть таким:
- internal – изменить нельзя, задано при установке;
- postmaster – требуется перезапуск сервера;
- sighup – требуется перечитать файлы конфигурации;
- superuser – суперпользователь может изменить для своего сеанса;
- user – любой пользователь может изменить для своего сеанса на лету.
Перечитаем конфигурацию сервера:

Как видим, параметр изменился. Он был взят из postgresql.auto.conf и теперь равняется 10 MB.

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

Для своего сеанса можно изменить параметры с context=user. Для этого используется конструкция:

SET <параметр> TO '<значение>';

Например сделаем это для work_mem:

Как видим, теперь источником является текущая сессия, а параметр равен 64 MB, но если мы перечитаем конфигурацию параметр снова станет равным 10 MB.

Чтобы вернуть все на место нужно просто перезайти в psql. Или выполнить команду RESET <параметр> :

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

Как вы могли заметить посмотреть текущее значение параметра ещё можно так:

Какие параметры требуют перезапуск сервера?

Чтобы это выяснить нужно посмотреть все параметры у которых context = postmaster:

Читайте также: