Пошук уроків, статей та іншого контенту
Змінюватимете параметри сервера, розрізнятимете рівні конфігурації та застосовуватимете зміни без зайвого простою.
Конфігурація PostgreSQL визначає поведінку сервера: використання пам’яті, журналювання, кількість підключень, часові обмеження запитів та багато інших параметрів.
Поточні значення параметрів можна переглянути через SQL:
SHOW work_mem;
SHOW max_connections;
SHOW config_file;Для перегляду кількох параметрів з додатковою інформацією зручно використовувати системне представлення pg_settings:
SELECT
name,
setting,
unit,
context,
source,
sourcefile,
pending_restart
FROM pg_settings
WHERE name IN (
'work_mem',
'shared_buffers',
'max_connections',
'log_min_duration_statement'
)
ORDER BY name;Основні стовпці:
name — назва параметра;
setting — поточне значення;
unit — одиниця вимірювання;
context — рівень, на якому параметр можна змінювати;
source — джерело поточного значення;
sourcefile — файл, з якого взято значення;
pending_restart — чи потребує нове значення перезапуску сервера.
Параметри PostgreSQL можна налаштовувати на різних рівнях.
Файл postgresql.conf містить основні налаштування сервера. Його розташування можна дізнатися так:
SHOW config_file;Типові параметри в цьому файлі мають такий вигляд:
# Максимальна кількість одночасних підключень
max_connections = 100
# Обсяг пам'яті для одного оператора сортування або хешування
work_mem = 4MB
# Записувати в журнал запити, довші за 500 мс
log_min_duration_statement = 500msПробіли навколо = необов’язкові. Значення часу та пам’яті краще вказувати з одиницями:
statement_timeout = 30s
shared_buffers = 1GBФайл postgresql.auto.conf створюється командою ALTER SYSTEM. Він доповнює основний файл конфігурації та має пріоритет над значеннями з postgresql.conf.
Налаштування можна призначити конкретній базі даних:
ALTER DATABASE reporting
SET statement_timeout = '30s';Таке значення застосовуватиметься до нових сесій, підключених до бази reporting.
Щоб видалити налаштування для бази:
ALTER DATABASE reporting
RESET statement_timeout;Налаштування можна призначити ролі:
ALTER ROLE app_user
SET work_mem = '16MB';Воно застосовуватиметься до нових підключень цієї ролі.
Скидання значення:
ALTER ROLE app_user
RESET work_mem;Комбінація ролі та бази даних дає змогу налаштувати параметр лише для певного користувача в певній базі:
ALTER ROLE app_user IN DATABASE reporting
SET statement_timeout = '10s';Команда SET змінює параметр лише для поточного підключення:
SET work_mem = '32MB';
SHOW work_mem;Після завершення сесії це значення зникає.
Команда RESET повертає значення, яке діє поза межами поточної сесії:
RESET work_mem;SET LOCAL діє лише до завершення поточної транзакції:
BEGIN;
SET LOCAL statement_timeout = '2s';
-- Запити всередині транзакції мають обмеження 2 секунди
SELECT count(*)
FROM large_table;
COMMIT;Після COMMIT або ROLLBACK параметр повертається до попереднього значення.
Це зручно, коли окремій операції потрібне тимчасове налаштування, але не потрібно змінювати всю сесію.
Одне й те саме налаштування може бути задане в кількох місцях. Загальний порядок від менш специфічного до більш специфічного такий:
значення за замовчуванням PostgreSQL;
postgresql.conf та підключені файли;
postgresql.auto.conf;
налаштування бази даних і ролі;
значення поточної сесії через SET;
тимчасове значення транзакції через SET LOCAL.
Більш специфічний рівень має пріоритет над менш специфічним. Наприклад, SET work_mem у сесії перекриє значення, задане для ролі.
Фактичне джерело можна перевірити через pg_settings:
SELECT
name,
setting,
source,
sourcefile,
sourceline
FROM pg_settings
WHERE name = 'work_mem';Не всі параметри можна змінювати на кожному рівні. Це визначає поле context.
Для параметрів PostgreSQL важливий не лише спосіб зміни, а й момент, коли зміна набуває чинності.
userТакі параметри можна змінювати через SET у поточній сесії. Приклади:
work_mem;
statement_timeout;
lock_timeout;
search_path.
SET statement_timeout = '5s';sighupДля таких параметрів достатньо перечитати конфігурацію без повного перезапуску сервера.
Наприклад:
log_min_duration_statement;
багато параметрів журналювання;
частина параметрів автовакууму.
Перечитати конфігурацію можна SQL-командою:
SELECT pg_reload_conf();Результат true означає, що PostgreSQL надіслав серверу сигнал перечитати конфігурацію. Це ще не гарантує, що кожен параметр було прийнято: значення потрібно перевірити.
postmasterТакі параметри застосовуються лише після повного перезапуску сервера. Приклад:
shared_buffers = 1GBПісля зміни такого параметра pg_reload_conf() недостатньо. Ознаку необхідності перезапуску можна перевірити так:
SELECT
name,
setting,
pending_restart
FROM pg_settings
WHERE pending_restart = true;Перезапуск залежить від способу запуску PostgreSQL: це може бути системний сервіс, контейнер або ручний запуск через pg_ctl. Важливо планувати його окремо, оскільки всі активні підключення буде перервано.
ALTER SYSTEMКоманда ALTER SYSTEM записує значення до postgresql.auto.conf. Це зручно для централізованої зміни налаштувань без ручного редагування файлу.
Приклад повного циклу:
-- Встановлюємо журналювання запитів, довших за 500 мс
ALTER SYSTEM SET log_min_duration_statement = '500ms';
-- Просимо сервер перечитати конфігурацію
SELECT pg_reload_conf();
-- Перевіряємо фактичне значення та джерело
SELECT
name,
setting,
unit,
source,
pending_restart
FROM pg_settings
WHERE name = 'log_min_duration_statement';
-- Повертаємо параметр до значення за замовчуванням
ALTER SYSTEM RESET log_min_duration_statement;
-- Застосовуємо скидання без перезапуску
SELECT pg_reload_conf();ALTER SYSTEM змінює налаштування всього кластера. Тому для параметрів, які можна встановити на рівні ролі або бази даних, часто краще використовувати ALTER ROLE чи ALTER DATABASE.
Встановити всі параметри назад до значень за замовчуванням можна командою:
ALTER SYSTEM RESET ALL;Цю команду слід застосовувати обережно: вона видаляє всі налаштування, записані через ALTER SYSTEM.
Конфігурацію можна розділяти на кілька файлів за допомогою директив:
include 'logging.conf'
include_if_exists 'local.conf'
include_dir 'conf.d'include вимагає наявності вказаного файлу;
include_if_exists не вважає відсутність файлу помилкою;
include_dir підключає файли з каталогу.
Це дає змогу зберігати, наприклад, параметри журналювання та параметри продуктивності в окремих файлах. Порядок підключення важливий: якщо параметр задано кілька разів, зазвичай діє останнє прочитане значення.
Після редагування конфігурації варто перевірити, чи PostgreSQL зміг її прочитати:
SELECT
name,
setting,
applied,
error
FROM pg_file_settings
WHERE error IS NOT NULL
OR applied = false;Це допомагає виявити:
синтаксичні помилки;
невідомі параметри;
некоректні значення;
параметри, які були перекриті пізнішим записом.
Для конкретного параметра зручно переглянути всі його властивості:
SELECT
name,
setting,
unit,
category,
short_desc,
context,
vartype,
min_val,
max_val,
enumvals,
source,
sourcefile,
pending_restart
FROM pg_settings
WHERE name = 'shared_buffers';Поля min_val, max_val та enumvals показують допустимий діапазон або список значень, якщо PostgreSQL надає таку інформацію.
Перед зміною параметра використовуйте послідовність:
Перевірте поточне значення через SHOW або pg_settings.
Перевірте context, щоб знати, чи потрібен reload або restart.
Виберіть відповідний рівень:
SET або SET LOCAL для тимчасової зміни;
ALTER ROLE для користувача;
ALTER DATABASE для бази;
postgresql.conf або ALTER SYSTEM для кластера.
Застосуйте зміну через pg_reload_conf(), якщо це потрібно.
Перевірте фактичне значення та source.
Перегляньте pending_restart.
Якщо параметр потребує перезапуску, виконайте його в заплановане вікно обслуговування.
SET змінить конфігурацію сервераSET work_mem = '64MB';Ця команда змінює лише поточну сесію. Для постійного налаштування потрібен інший рівень, наприклад:
ALTER ROLE app_user SET work_mem = '64MB';pg_reload_conf() для параметра, якому потрібен restartReload перечитує конфігурацію, але не може застосувати параметри рівня postmaster. Перевіряйте context і pending_restart.
Файл може містити правильний запис, але його можуть перекривати:
пізніший запис у конфігурації;
postgresql.auto.conf;
налаштування ролі або бази;
SET у поточній сесії.
Тому після зміни перевіряйте pg_settings.
Налаштування через ALTER DATABASE і ALTER ROLE застосовуються під час створення нового підключення. Уже відкрита сесія може продовжувати використовувати старе значення.
Значення пам’яті та часу без одиниць можуть бути неоднозначними або неприйнятними. Використовуйте явні одиниці:
SET work_mem = '16MB';
SET statement_timeout = '10s';Помилка в конфігураційному файлі може призвести до того, що частина змін не застосовується. Після reload перевіряйте pg_file_settings і журнал сервера.
Поточні параметри переглядають через SHOW або pg_settings.
Основний файл конфігурації можна знайти через SHOW config_file.
SET змінює значення поточної сесії, а SET LOCAL — лише поточної транзакції.
ALTER ROLE і ALTER DATABASE задають значення для нових підключень.
ALTER SYSTEM записує кластерне налаштування до postgresql.auto.conf.
pg_reload_conf() застосовує параметри, яким достатньо перечитування конфігурації.
Параметри з context = 'postmaster' потребують повного перезапуску.
pg_settings, pg_file_settings і pending_restart допомагають перевірити результат зміни та уникнути зайвого простою.