konfiguracziya

Конфигурация

Параметры конфигурации postgresql, файлы конфигурации postgresql, управление параметрами postgresql на уровнях экземпляра и сеанса

Общие сведения

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

Основной файл конфигурации

Основной файл конфигурации postgresql.conf по умолчанию находится в /etc/postgresql/13/main
Файл может находиться в каталоге PGDATA ( /var/postgresql/.... )
Если одна и та же директива ( ключ=значение ) указана несколько раз, тогда по умолчанию будет использоваться последнее значение в файле
После изменения конфигурации необходимо перечитать файл конфигурации


#Перечитываем файл конфигурации:
#Так
sudo pg_ctlcluster 12 main reload
#Или так
sudo -u postgres pg_ctl -p 9999 reload -D /var/lib/postgresql/12/main
#Узнать расположение основного файла конфигурации:
sudo -u postgres psql -c 'SHOW config_file'
#Увидеть основные конфигурационные файлы:
SELECT pg_read_file('/etc/postgresql/12/main/postgresql.conf', 1516, 860);

data_directory = '/var/lib/postgresql/12/main' # use data in another directory
hba_file = '/etc/postgresql/12/main/pg_hba.conf' # host-based authentication file
ident_file = '/etc/postgresql/12/main/pg_ident.conf' # ident configuration file
external_pid_file = '/var/run/postgresql/12-main.pid' # write an extra PID file

pg_file_settings

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


#Просмотреть все незакомментированные параметры конфигурации:
SELECT sourceline, name, setting, applied 
FROM pg_file_settings 
WHERE sourcefile LIKE '/etc/postgresql/12/main/postgresql.conf';
--Изменить значение параметра в postgresql.conf
echo max_connections=5O | sudo tee -a /etc/postgresql/13/main/postgresql.conf
--Увидеть ошибки конфигурационного файла можно в таблице pg_file_settings
SELECT * FROM pg_file_settings ;

Значение в столбце applied говорит о том, можно ли применить значение без перезапуска сервера

pg_settings

В представлении pg_settings доступны значения всех параметров


#Просмотр всех параметров
SELECT name, setting, sourcefile FROM pg_settings;
#Просмотр конкретного параметра
SELECT * FROM pg_settings WHERE name = 'TimeZone' \gx

Ключевые столбцы представления pg_settings name, setting, unit — название, значение параметра и единица измерения;
boot_val — значение по умолчанию;
reset_val — значение, которое восстановит команда RESET;
source — источник текущего значения параметра;
pending_restart — значение изменено в файле конфигурации, но для применения требуется или не требуется ( t/f ) перезапуск сервера.

Столбец context определяет действия, необходимые для применения параметра: internal — изменить нельзя, значение задано при установке;
postmaster — требуется перезапуск сервера;
sighup — требуется перечитать файлы конфигурации,
superuser — суперпользователь может изменить для своего сеанса;
user — любой пользователь может изменить для своего сеанса.

postgresql.auto.conf

ALTER SYSTEM добавляет или изменяет строку
SET параметр TO значение;
ALTER SYSTEM RESET параметр; удаляет строку
ALTER SYSTEM RESET ALL; удаляет все строки
postgresql.auto.conf считывается последним, после postgresql.conf, располагается в PGDATA
Вручную изменять данный файл не стоит, для этого есть команда ALTER SYSTEM
После изменений параметров с помощью ALTER SUSTEM необходимо перечитать конфигурационные файлы
Содержимое обоих файлов (postgresql.conf и postgresql.auto.conf) можно увидеть через представление pg_file_settings. А актуальные значения параметров — в представлении pg_settings


ALTER SYSTEM SET work_mem TO '16MB';
SELECT pg_read_file('postgresql.auto.conf')
\g (tuples_only=on format=unaligned)
#Перечитаем файлы конфигурации
SELECT pg_reload_conf();
#Проверим
SHOW work_mem;
#Или так
SELECT * FROM pg_settings WHERE name = 'work_mem'\gx
# Сбросим значение work_mem
ALTER SYSTEM RESET work_mem;
#Перечитаем конфигурацию
SELECT pg_reload_conf();
#И проверим еще раз
=# SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

Конфигурация в текущем сеансе

Изменять параметры конфигурации можно во время сеанса командой SET или функцией set_config.
Увидеть текущие значения можно с помощью SHOW или функция current_setting
Срок действия конфигурации можно установить до конца сеанса (по умолчанию) или до конца транзакции (SET LOCAL)


#Установка параметров для текущего сеанса
SET work_mem TO '24MB';
#Тоже самое можно сделать с помощю команды
SELECT set_config('work_mem', '32MB', false);
#Третий параметр функции говорит о том, нужно ли устанавливать значение только для текущей транзакции (true) или до конца работы сеанса (false).
#Просмотреть значения параметров можно так
SHOW work_mem;
#Или так
SELECT current_setting('work_mem');
#Или так
SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';

Установка параметров внутри транзакции


--Сброс значения
RESET work_mem;
--Открываем транзакцию
BEGIN;
--Устанавливаем значение
SET work_mem TO '64MB';
--Смотрим значение внутри транзакции
SHOW work_mem;
--Если транзакция откатывается, установка параметров отменяется
ROLLBACK;

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


--Установить значение парметра, если оно существует
SELECT CASE
  WHEN current_setting('myapp.currency_code', true) IS NULL
    THEN set_config('myapp.currency_code', 'RUB', false)
  ELSE
    current_setting('myapp.currency_code')
END;

--Посмотрим значение переменной
SELECT current_setting('myapp.currency_code');
--Пользовательские парметры можно указывать в файле postgresql.conf