Сервер баз данных PostgreSQL имеет очень много параметров с помощью которых его можно настроить под любые нужды. В этой статье мы не будет рассматривать все эти параметры. Здесь мы посмотрим на различные cпособы конфигурирования PostgreSQL.
Если же вы хотите посмотреть список параметров настройки PostgreSQL, то ищите его в справочнике на официальном сайте: на английском и на русском языках.
Конфигурационный файл postgresql.conf
Главный конфигурационный файл для кластера PostgreSQL — это postgresql.conf, в разных системах он может находится в разных местах. Так как мы собирали сервер из исходников и не меняли путь хранения этого файла, то по умолчанию он будет находится в каталоге PGDATA:
postgres@s-pg13:~$ echo $PGDATA /usr/local/pgsql/data postgres@s-pg13:~$ ls -l $PGDATA/postgresql.conf -rw------- 1 postgres postgres 28023 июн 21 15:15 /usr/local/pgsql/data/postgresql.conf
Этот конфигурационный файл читается один раз при запуске сервера. Если параметр указан несколько раз, то применяется последний.
Самый точный способ узнать расположение этого файла, посмотреть из терминала psql:
postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.
postgres@postgres=# SHOW config_file;
config_file
---------------------------------------
/usr/local/pgsql/data/postgresql.conf
(1 row)
Time: 0,391 ms
Если вы измените параметры в этом файле, его нужно перечитать. Первый способ — из командной оболочки операционной системы:
postgres@postgres=# \q postgres@s-pg13:~$ pg_ctl reload server signaled
Второй способ — из терминала psql:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) Time: 0,555 ms
Но есть некоторые параметры, для изменения которых потребуется перезапуск сервера.
Конфигурация сервера используя 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, который покажет конфигурационный файл текущего кластера:
postgres@postgres=# SELECT setting FROM pg_settings WHERE name = 'config_file';
setting
---------------------------------------
/usr/local/pgsql/data/postgresql.conf
(1 row)
Time: 1,844 ms
Внесём изменения в параметр work_mem в postgresql.conf и postgresql.auto.conf. Затем посмотрим на все не закомментированные параметры в этих файлах:
postgres@postgres=# \! echo 'work_mem = 8MB' >> $PGDATA/postgresql.conf
postgres@postgres=# ALTER SYSTEM SET work_mem TO '10MB';
ALTER SYSTEM
Time: 0,728 ms
postgres@postgres=# SELECT sourceline, name, setting, applied FROM pg_file_settings;
sourceline | name | setting | applied
------------+----------------------------+--------------------+---------
63 | port | 5433 | f
64 | max_connections | 100 | t
121 | shared_buffers | 128MB | t
142 | dynamic_shared_memory_type | posix | t
228 | max_wal_size | 1GB | t
229 | min_wal_size | 80MB | t
563 | log_timezone | Europe/Moscow | t
678 | datestyle | iso, dmy | t
680 | timezone | Europe/Moscow | t
694 | lc_messages | ru_RU.UTF-8 | t
696 | lc_monetary | ru_RU.UTF-8 | t
697 | lc_numeric | ru_RU.UTF-8 | t
698 | lc_time | ru_RU.UTF-8 | t
701 | default_text_search_config | pg_catalog.russian | t
780 | work_mem | 8MB | f
3 | work_mem | 10MB | t
(16 rows)
Time: 0,650 ms
Как можно заметить в примере выше, у меня 2 одинаковых параметра work_mem. Колонка applied показывает, может ли быть применён параметр. Первый work_mem не может быть применен, так как второй его перезапишет. При этом реальное значение с которым работает сервер отличается, так как сервер не перечитал конфигурацию.
Теперь посмотрим на реальное, текущее значение этого параметра:
postgres@postgres=# 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 Time: 0,854 ms
В примере выше мы использовали расширенный режим (в конце запроса \gx), поэтому табличка перевёрнута. Разберём колонки:
- name — имя параметра;
- setting — текущее значение;
- unit — единица измерения;
- boot_val — значение по умолчанию (жёстко задано в коде postgresql);
- reset_val — если перечитаем конфигурацию, то применится это значение;
- source — источник, это значение по умолчанию;
- sourcefile — если бы источником был конфигурационный файл, то тут был бы указан этот файл;
- sourceline — номер строки в этом файле;
- pending_restart — параметр изменили в конфигурационном файле и требуется перезапуск сервера. У нас требуется всего лишь перечитать конфигурацию;
- context — действия, необходимые для применения параметра, может быть таким:
- internal — изменить нельзя, задано при установке;
- postmaster — требуется перезапуск сервера;
- sighup — требуется перечитать файлы конфигурации;
- superuser — суперпользователь может изменить для своего сеанса;
- user — любой пользователь может изменить для своего сеанса на лету.
Перечитаем конфигурацию сервера:
postgres@postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) Time: 3,178 ms postgres@postgres=# 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 | /usr/local/pgsql/data/postgresql.auto.conf sourceline | 3 pending_restart | f context | user Time: 1,210 ms
Как видим, параметр изменился. Он был взят из postgresql.auto.conf и теперь равняется 10 MB.
Установка параметров на лету
Для своего сеанса можно изменить параметры с context=user. Для этого используется конструкция:
SET <параметр> TO '<значение>';
Например сделаем это для work_mem:
postgres@postgres=# SET work_mem TO '64MB'; SET Time: 0,119 ms postgres@postgres=# 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 | 65536 unit | kB boot_val | 4096 reset_val | 10240 source | session sourcefile | sourceline | pending_restart | f context | user Time: 0,651 ms
Как видим, теперь источником является текущая сессия, а параметр равен 64 MB, но если мы перечитаем конфигурацию параметр снова станет равным 10 MB.
Чтобы вернуть все на место нужно просто перезайти в psql. Или выполнить команду RESET <параметр>:
postgres@postgres=# RESET work_mem; RESET Time: 0,211 ms postgres@postgres=# 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 | /usr/local/pgsql/data/postgresql.auto.conf sourceline | 3 pending_restart | f context | user Time: 0,632 ms
Тоже самое может проделывать приложение для одной транзакции, и если транзакция откатывается, то и значение параметра откатывается вместе с ней:
postgres@postgres=# BEGIN; BEGIN Time: 0,070 ms postgres@postgres=# SET work_mem TO '64MB'; SET Time: 0,085 ms postgres@postgres=# SHOW work_mem; work_mem ---------- 64MB (1 row) Time: 0,102 ms postgres@postgres=# ROLLBACK; ROLLBACK Time: 0,108 ms postgres@postgres=# SHOW work_mem; work_mem ---------- 10MB (1 row) Time: 0,120 ms
Как вы могли заметить посмотреть текущее значение параметра ещё можно так:
SHOW <параметр>;
Какие параметры требуют перезапуск сервера?
Чтобы это выяснить нужно посмотреть все параметры у которых context = postmaster:
postgres@postgres=# 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 | |
config_file | /usr/local/pgsql/data/postgresql.conf |
data_directory | /usr/local/pgsql/data |
data_sync_retry | off |
dynamic_shared_memory_type | posix |
event_source | PostgreSQL |
external_pid_file | |
hba_file | /usr/local/pgsql/data/pg_hba.conf |
hot_standby | on |
huge_pages | try |
ident_file | /usr/local/pgsql/data/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 |
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 | |
restore_command | |
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 | /tmp |
unix_socket_group | |
unix_socket_permissions | 0777 |
wal_buffers | 512 | 8kB
wal_level | replica |
wal_log_hints | off |
(53 rows)
Time: 0,666 ms
