Сервер баз данных PostgreSQL имеет очень много параметров с помощью которых его можно настроить под любые нужды. В этой статье мы не будет рассматривать все эти параметры. Здесь мы посмотрим на различные способы конфигурирования PostgreSQL.
Конфигурационный файл postgresql.conf
Главный конфигурационный файл для кластера PostgreSQL — это postgresql.conf, в разных системах он может находится в разных местах. Самый точный способ узнать расположение этого файла, посмотреть из терминала psql:
# SHOW config_file;
config_file
-----------------------------------------
/etc/postgresql/16/main/postgresql.conf
(1 строка)
Время: 0,113 мс
Сервер читает этот конфиг один раз, при запуске. Если параметр указан несколько раз, то применяется последний.
Если вы измените параметры в этом файле, его нужно перечитать. Первый способ — из командной оболочки операционной системы:
$ sudo systemctl reload postgresql@16-main.service
Второй способ — из терминала psql:
# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 строка) Время: 0,364 мс
Но есть некоторые параметры, для изменения которых потребуется перезапуск сервера. То есть restart вместо reload.
Конфигурация сервера используя ALTER SYSTEM
Для настройки сервера также существует другой файл — postgresql.auto.conf. Он нужен для настройки сервера из консоли psql. Читается этот файл после postgresql.conf, поэтому параметры из него имеют более высокий приоритет. Этот файл всегда находится в каталоге с данными (PGDATA).
Давайте найдем этот файл, для начала в консоли psql выполните запрос SHOW data_directory, затем с помощью \! выполним команду ls.
# SHOW data_directory;
data_directory
-----------------------------
/var/lib/postgresql/16/main
(1 строка)
Время: 0,653 мс
# \! ls /var/lib/postgresql/16/main/postgresql.auto.conf
/var/lib/postgresql/16/main/postgresql.auto.conf
Редактировать этот файл вручную не нужно, он наполняется параметрами с помощью команд ALTER SYSTEM:
- Создание параметра:
ALTER SYSTEM SET <параметр> TO <значение>; - Удаление параметра:
ALTER SYSTEM RESET <параметр>; - Удаление всех параметров:
ALTER SYSTEM RESET ALL;
Чтобы применить изменения нужно перечитать конфигурационные файлы как было описано выше.
Вот пример создания и удаления параметров:
# SHOW work_mem; work_mem ---------- 4MB (1 строка) Время: 0,595 мс # ALTER SYSTEM SET work_mem TO '15MB'; ALTER SYSTEM Время: 1,780 мс # SHOW work_mem; work_mem ---------- 4MB (1 строка) Время: 0,142 мс # SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 строка) Время: 0,181 мс # SHOW work_mem; work_mem ---------- 15MB (1 строка) Время: 0,562 мс # ALTER SYSTEM RESET ALL; ALTER SYSTEM Время: 2,066 мс postgres@postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 строка) Время: 0,280 мс # SHOW work_mem; work_mem ---------- 4MB (1 строка) Время: 0,649 мс
Информация о текущих настройках сервера
В PostgreSQL есть 2 представления через которые можно посмотреть текущие настройки сервера:
- pg_file_settings — какие параметры записаны в файлах postgresql.conf и postgresql.auto.conf;
- pg_settings — текущие параметры, с которыми работает сервер.
Например посмотрим значение параметра config_file из представления pg_settings, который покажет конфигурационный файл текущего кластера:
# SELECT setting FROM pg_settings WHERE name = 'config_file';
setting
-----------------------------------------
/etc/postgresql/16/main/postgresql.conf
(1 строка)
Время: 1,664 мс
Внесём изменения в параметр work_mem в postgresql.conf и postgresql.auto.conf. Затем посмотрим на все не закомментированные параметры в этих файлах:
# \! echo 'work_mem = 8MB' >> /etc/postgresql/16/main/postgresql.conf
# ALTER SYSTEM SET work_mem TO '10MB';
ALTER SYSTEM
Время: 1,885 мс
# SELECT sourceline, name, setting, applied FROM pg_file_settings;
sourceline | name | setting | applied
------------+----------------------------+----------------------------------------+---------
42 | data_directory | /var/lib/postgresql/16/main | t
44 | hba_file | /etc/postgresql/16/main/pg_hba.conf | t
46 | ident_file | /etc/postgresql/16/main/pg_ident.conf | t
50 | external_pid_file | /var/run/postgresql/16-main.pid | t
64 | port | 5432 | t
65 | max_connections | 100 | t
68 | unix_socket_directories | /var/run/postgresql | t
108 | ssl | on | t
110 | ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem | t
113 | ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key | t
130 | shared_buffers | 128MB | t
153 | dynamic_shared_memory_type | posix | t
247 | max_wal_size | 1GB | t
248 | min_wal_size | 80MB | t
565 | log_line_prefix | %m [%p] %q%u@%d | t
603 | log_timezone | Europe/Moscow | t
607 | cluster_name | 16/main | t
715 | datestyle | iso, dmy | t
717 | timezone | Europe/Moscow | t
731 | lc_messages | ru_RU.UTF-8 | t
733 | lc_monetary | ru_RU.UTF-8 | t
734 | lc_numeric | ru_RU.UTF-8 | t
735 | lc_time | ru_RU.UTF-8 | t
741 | default_text_search_config | pg_catalog.russian | t
823 | work_mem | 8MB | f
3 | work_mem | 10MB | t
(26 строк)
Время: 1,024 мс
Как можно заметить в примере выше, у меня 2 одинаковых параметра work_mem. Колонка applied показывает, может ли быть применён параметр. Первый work_mem не может быть применен, так как второй его перезапишет. При этом реальное значение с которым работает сервер отличается, так как сервер не перечитал конфигурацию.
Теперь посмотрим на реальное, текущее значение этого параметра:
# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'\gx -[ RECORD 1 ]---+--------- name | work_mem setting | 4096 unit | kB boot_val | 4096 reset_val | 4096 source | default sourcefile | sourceline | pending_restart | f context | user Время: 1,594 мс
В примере выше мы использовали расширенный режим, поэтому табличка перевёрнута. Можно было бы включить вначале расширенный решим с помощью команды \x, а затем выполнить запрос. Либо как я показал в конце запроса вместо точки с запятой поставить \gx. Кстати про работу psql можете почитать статью Утилита psql для работы с PostgreSQL.
Разберём колонки:
- name — имя параметра;
- setting — текущее значение;
- unit — единица измерения;
- boot_val — значение по умолчанию (жёстко задано в коде postgresql);
- reset_val — если перечитаем конфигурацию, то применится это значение;
- source — источник, это значение по умолчанию;
- sourcefile — если бы источником был конфигурационный файл, то тут был бы указан этот файл;
- sourceline — номер строки в этом файле;
- pending_restart — параметр изменили в конфигурационном файле и требуется перезапуск сервера. У нас требуется всего лишь перечитать конфигурацию;
- context — действия, необходимые для применения параметра, может быть таким:
- internal — изменить нельзя, задано при установке;
- postmaster — требуется перезапуск сервера;
- sighup — требуется перечитать файлы конфигурации;
- superuser — суперпользователь может изменить для своего сеанса;
- user — любой пользователь может изменить для своего сеанса на лету.
Перечитаем конфигурацию сервера:
# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 строка) Время: 1,639 мс # SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'\gx -[ RECORD 1 ]---+------------------------------------------------- name | work_mem setting | 10240 unit | kB boot_val | 4096 reset_val | 10240 source | configuration file sourcefile | /var/lib/postgresql/16/main/postgresql.auto.conf sourceline | 3 pending_restart | f context | user Время: 1,582 мс
Как видим, параметр изменился. Он был взят из postgresql.auto.conf и теперь равняется 10 MB.
Кстати, проще всего использовать команду SHOW чтобы посмотреть на текущее значение параметра:
# SHOW work_mem; work_mem ---------- 10MB (1 строка) Время: 0,118 мс
Установка параметров на лету
Для своего сеанса можно изменить параметры с context=user. Для этого используется конструкция: SET <параметр> TO '<значение>';.
Например сделаем это для work_mem:
# SET work_mem TO '64MB'; SET Время: 0,104 мс # SHOW work_mem; work_mem ---------- 64MB (1 строка) Время: 0,122 мс # SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restar ntext FROM pg_settings WHERE name = 'work_mem'\gx -[ RECORD 1 ]---+--------- name | work_mem setting | 65536 unit | kB boot_val | 4096 reset_val | 10240 source | session sourcefile | sourceline | pending_restart | f context | user Время: 1,023 мс
Как видим, теперь источником является текущая сессия, а параметр равен 64 MB, но если мы перечитаем конфигурацию параметр снова станет равным 10 MB.
Чтобы вернуть все на место нужно просто перезайти в psql. Или выполнить команду RESET <параметр>:
# RESET work_mem; RESET Время: 0,108 мс # SHOW work_mem; work_mem ---------- 10MB (1 строка) Время: 0,180 мс
Тоже самое может проделывать приложение для одной транзакции, и если транзакция откатывается, то и значение параметра откатывается вместе с ней. Например приложение знает что сейчас будет выполнятся тяжелый запрос. Приложение начинает транзакцию, в транзакции меняет параметр work_mem, чтобы хватило памяти. Дальше выполняет любые другие команды в транзакции. И после возвращает параметр work_mem в исходное состояние. А если транзакция вдруг завершилась с ошибкой то все изменения, включая изменение параметра work_mem будут возвращены.
Параметры требующие перезапуск сервера
Чтобы это выяснить нужно посмотреть все параметры у которых context = postmaster:
# SELECT name, setting, unit FROM pg_settings WHERE context = 'postmaster';
name | setting | unit
-------------------------------------+-----------------------------------------+------
archive_mode | off |
autovacuum_freeze_max_age | 200000000 |
autovacuum_max_workers | 3 |
autovacuum_multixact_freeze_max_age | 400000000 |
bonjour | off |
bonjour_name | |
cluster_name | 16/main |
config_file | /etc/postgresql/16/main/postgresql.conf |
data_directory | /var/lib/postgresql/16/main |
data_sync_retry | off |
debug_io_direct | |
dynamic_shared_memory_type | posix |
event_source | PostgreSQL |
external_pid_file | /var/run/postgresql/16-main.pid |
hba_file | /etc/postgresql/16/main/pg_hba.conf |
hot_standby | on |
huge_page_size | 0 | kB
huge_pages | try |
ident_file | /etc/postgresql/16/main/pg_ident.conf |
ignore_invalid_pages | off |
jit_provider | llvmjit |
listen_addresses | localhost |
logging_collector | off |
max_connections | 100 |
max_files_per_process | 1000 |
max_locks_per_transaction | 64 |
max_logical_replication_workers | 4 |
max_pred_locks_per_transaction | 64 |
max_prepared_transactions | 0 |
max_replication_slots | 10 |
max_wal_senders | 10 |
max_worker_processes | 8 |
min_dynamic_shared_memory | 0 | MB
old_snapshot_threshold | -1 | min
port | 5432 |
recovery_target | |
recovery_target_action | pause |
recovery_target_inclusive | on |
recovery_target_lsn | |
recovery_target_name | |
recovery_target_time | |
recovery_target_timeline | latest |
recovery_target_xid | |
reserved_connections | 0 |
shared_buffers | 16384 | 8kB
shared_memory_type | mmap |
shared_preload_libraries | |
superuser_reserved_connections | 3 |
track_activity_query_size | 1024 | B
track_commit_timestamp | off |
unix_socket_directories | /var/run/postgresql |
unix_socket_group | |
unix_socket_permissions | 0777 |
wal_buffers | 512 | 8kB
wal_decode_buffer_size | 524288 | B
wal_level | replica |
wal_log_hints | off |
(57 строк)
Время: 1,040 мс
Если понравилась статья, подпишись на мой канал в VK или Telegram.
