設定の反映方法は3通り

PostgreSQLの設定を変更したあと、変更を反映する操作は設定項目ごとに異なる。

graph TD
  A[設定を変更] --> B{context}
  B -->|internal| C[変更不可]
  B -->|postmaster| D[設定ファイル + 再起動]
  B -->|sighup| E["設定ファイル + リロード"]
  B -->|superuser-backend / backend| F["設定ファイル + リロード<br/>反映は新規セッションから"]
  B -->|superuser / user| G["設定ファイル + リロード<br/>またはSETでセッション単位に変更"]

postgresql.confを書き換えてリロードしただけでは反映されない設定もあるため、変更前に判別しておく。判別にはpg_settingsのcontextカラムを使用する。

参照：設定値の一覧・検索方法は【PostgreSQL】SHOW ALLやpg_settingsで設定値を一覧・検索する

contextカラムで判別する

SELECT name, setting, context
FROM pg_settings
WHERE name IN ('max_connections', 'shared_buffers', 'wal_level',
               'archive_command', 'log_min_duration_statement', 'work_mem', 'server_version');

            name            |             setting             |  context
----------------------------+---------------------------------+------------
 archive_command            | (disabled)                      | sighup
 log_min_duration_statement | -1                              | superuser
 max_connections            | 100                             | postmaster
 server_version             | 17.10 (Debian 17.10-1.pgdg13+1) | internal
 shared_buffers             | 16384                           | postmaster
 wal_level                  | replica                         | postmaster
 work_mem                   | 4096                            | user
(7 rows)

contextの値と必要な操作の対応は以下のとおり。

context必要な操作
internal変更不可。ビルド時やinitdb時に決まる値
postmaster設定ファイルの変更とPostgreSQLの再起動
sighup設定ファイルの変更とリロード
superuser-backend設定ファイルの変更とリロード。接続要求時の指定も可能だが、スーパーユーザーかSET権限を持つロールに限られる
backend設定ファイルの変更とリロード。接続要求時の指定は任意のユーザーが可能
superuser設定ファイルの変更とリロード。スーパーユーザーかSET権限を持つロールはSETでセッション単位に変更可能
user設定ファイルの変更とリロード。任意のユーザーがSETでセッション単位に変更可能

再起動が必要なのはpostmasterだけである。contextは必要な操作だけでなく、セッション単位の変更に必要な権限も表す。superuserとuser、superuser-backendとbackendの違いは、変更をスーパーユーザー(またはSET権限を付与されたロール)に限定するかどうかである。

superuser-backendとbackendは接続要求時に指定できる。libpqを使う場合はPGOPTIONS環境変数で渡す。

$ PGOPTIONS="-c log_connections=on" psql

ただしリロードが既存セッションに及ぶ範囲はcontextごとに異なる。superuser-backendとbackendは接続開始後に値が変わらないため、リロード後に接続したセッションから反映される。superuserとuserは既存セッションにも反映されるが、SETでセッションローカルな値を設定済みの場合はSETした値が残る。

再起動が必要な設定を一覧する

contextにpostmasterを指定して一覧すると、再起動が必要な項目を確認できる。

SELECT count(*) FROM pg_settings WHERE context = 'postmaster';

 count
-------
    65
(1 row)

件数はバージョンやビルドオプションによって異なる。

SELECT name, setting, unit
FROM pg_settings
WHERE context = 'postmaster'
  AND name IN ('max_connections', 'shared_buffers', 'wal_level', 'wal_buffers',
               'max_worker_processes', 'max_wal_senders', 'shared_preload_libraries',
               'listen_addresses', 'port', 'archive_mode');

           name           | setting | unit
--------------------------+---------+------
 archive_mode             | off     |
 listen_addresses         | *       |
 max_connections          | 100     |
 max_wal_senders          | 10      |
 max_worker_processes     | 8       |
 port                     | 5432    |
 shared_buffers           | 16384   | 8kB
 shared_preload_libraries |         |
 wal_buffers              | 512     | 8kB
 wal_level                | replica |
(10 rows)

共有メモリのサイズを決める設定(shared_buffers、max_connections、wal_buffersなど)や、起動時にプロセスを構成する設定(listen_addresses、shared_preload_libraries、archive_modeなど)がpostmasterに該当する。

pg_stat_statementsのようにshared_preload_librariesへ追加する拡張は、導入時に必ず再起動が必要である。

参考: 【PostgreSQL】pg_stat_statementsで重いクエリを特定する

変更が再起動待ちかを確認する

ALTER SYSTEMで2つの設定を変更し、リロードした状態を確認する。

ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

pg_reload_conf()はSIGHUPを送るだけで即座に戻る。設定の再読み込みは非同期に実行されるため、直後にpg_settingsを参照すると古い値のまま見える場合がある。値が変わらない場合は少し待つか、接続し直して確認する。

SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN ('max_connections', 'log_min_duration_statement');

            name            | setting | pending_restart
----------------------------+---------+-----------------
 log_min_duration_statement | 500     | f
 max_connections            | 100     | t
(2 rows)

superuserのlog_min_duration_statementはリロードだけで500に反映されている。一方のmax_connectionsはsettingが100のままであり、pending_restartがtになっている。

pending_restartがtの項目を一覧すると、再起動待ちの変更が残っているかが分かる。

SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE pending_restart;

      name       | setting |       source       |                sourcefile                | sourceline
-----------------+---------+--------------------+------------------------------------------+------------
 max_connections | 100     | configuration file | /var/lib/postgresql/data/postgresql.conf |         65
(1 row)

settingとsourcefileは、変更後の値ではなく現在有効な値の出所を示す。ALTER SYSTEMではなくpostgresql.confを直接編集した場合も、同様にpending_restartがtになる。

サーバーログを確認する

再起動待ちの設定があると、リロードのたびにサーバーログへ以下が出力される。

LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "max_connections" cannot be changed without restarting the server
LOG:  parameter "log_min_duration_statement" changed to "500ms"
LOG:  configuration file "/var/lib/postgresql/data/postgresql.auto.conf" contains errors; unaffected changes were applied

contains errorsは設定ファイルの構文エラーのようにも読めるが、再起動が必要な設定の変更が残っているだけで、他の設定は適用済みである旨を示す。

再起動して反映する

$ pg_ctl restart -D /var/lib/postgresql/data

systemdで管理している環境ではsystemctl restartを使用する。

$ sudo systemctl restart postgresql

再起動するとmax_connectionsが200に切り替わり、pending_restartもfに戻る。

SELECT name, setting, pending_restart, sourcefile
FROM pg_settings
WHERE name IN ('max_connections', 'log_min_duration_statement');

            name            | setting | pending_restart |                  sourcefile
----------------------------+---------+-----------------+-----------------------------------------------
 log_min_duration_statement | 500     | f               | /var/lib/postgresql/data/postgresql.auto.conf
 max_connections            | 200     | f               | /var/lib/postgresql/data/postgresql.auto.conf
(2 rows)

参考: 【PostgreSQL】SHOW ALLやpg_settingsで設定値を一覧・検索する

pg_file_settingsで反映結果を確認する

pg_file_settingsは設定ファイルの記述を1行ずつ表示するビューである。appliedカラムで各行が反映されたかどうかを確認できる。

以下は再起動前、max_connections = 200が未反映の状態である。

SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
WHERE name IN ('max_connections', 'log_min_duration_statement')
ORDER BY sourcefile, sourceline;

                  sourcefile                   | sourceline |            name            | setting | applied |            error
-----------------------------------------------+------------+----------------------------+---------+---------+------------------------------
 /var/lib/postgresql/data/postgresql.auto.conf |          3 | max_connections            | 200     | f       | setting could not be applied
 /var/lib/postgresql/data/postgresql.auto.conf |          4 | log_min_duration_statement | 500ms   | t       |
 /var/lib/postgresql/data/postgresql.conf      |         65 | max_connections            | 100     | f       |
(3 rows)

max_connections = 200の行はappliedがfで、errorにsetting could not be appliedが入っている。再起動が必要な設定を変更したまま再起動していない状態を意味する。再起動するとappliedがtに変わる。

postgresql.conf側のmax_connections = 100もappliedがfである。同じ設定を複数箇所に記述した場合は後に読み込まれた記述が優先されるため、先の行は反映されない。ALTER SYSTEMが書き込むpostgresql.auto.confはpostgresql.confを読み込んだあとに読み込まれるため、postgresql.auto.conf側が優先される。

pg_settingsが現在有効な値を示すのに対し、pg_file_settingsはファイルに書かれた内容を示す。両者を比較すると、変更が未反映のまま残っていないかを確認できる。

参考: 【PostgreSQL】postgresql.confの場所を探す(show config_file)

SETコマンドのエラーメッセージで判断する

SETを実行したときのエラーメッセージからも判別できる。

postmasterの設定は、サーバーの再起動が必要である旨のエラーになる。

SET wal_level = 'minimal';
ERROR:  parameter "wal_level" cannot be changed without restarting the server

sighupの設定はセッション単位の変更ができないため、別のエラーメッセージになる。

SET archive_command = '/bin/true';
ERROR:  parameter "archive_command" cannot be changed now

superuser-backendとbackendの設定は、接続開始後の変更ができない旨のエラーになる。

SET log_connections = 'on';
ERROR:  parameter "log_connections" cannot be set after connection start

internalの設定はいかなる方法でも変更できない。

SET server_version = '18';
ERROR:  parameter "server_version" cannot be changed

userやsuperuserの設定はエラーにならず、セッション単位で即座に反映される。

SET work_mem = '64MB';
SHOW work_mem;
 work_mem
----------
 64MB
(1 row)

リロードで反映する

sighupの設定は、設定ファイルを変更したあとリロードすると反映される。SQLから実行する場合はpg_reload_conf()を使用する。

SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

シェルからはpg_ctl reloadを使用する。

$ pg_ctl reload -D /var/lib/postgresql/data
server signaled

systemdで管理している環境ではsystemctl reloadでも同じくSIGHUPが送られる。

$ sudo systemctl reload postgresql

いずれの方法でもサーバーログに以下が出力される。

LOG:  received SIGHUP, reloading configuration files

変更を取り消す

ALTER SYSTEMによる変更はALTER SYSTEM RESETで取り消せる。postgresql.auto.confから該当行が削除され、postgresql.confの値またはデフォルト値に戻る。

ALTER SYSTEM RESET max_connections;
SELECT pg_reload_conf();

SELECT name, setting, pending_restart, sourcefile
FROM pg_settings
WHERE name = 'max_connections';

      name       | setting | pending_restart |                  sourcefile
-----------------+---------+-----------------+-----------------------------------------------
 max_connections | 200     | t               | /var/lib/postgresql/data/postgresql.auto.conf
(1 row)

postmasterの設定を取り消した場合も再起動までは現在の値が有効なままであり、pending_restartがtになる。再起動するとpostgresql.confの値に戻る。

pending_restartがtの間はSHOWも古い値を返す。監視や自動化で設定値を参照する場合、再起動漏れに気付かないまま古い値を読み続ける点に注意する。

参考

＼第一線のプログラマーの行動原理を学べる！／

プログラマが知るべき97のこと

Amazonで見る