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

SHOW ALLで設定値を一覧する

PostgreSQLの全設定値を一覧するにはSHOW ALLコマンドを実行する。

SHOW ALL;
              name              | setting |                      description
---------------------------------+---------+---------------------------------------------------------
 application_name                | psql    | Sets the application name to be reported in statistics...
 archive_mode                    | off     | Allows archiving of WAL files using archive_command.
 autovacuum                      | on      | Starts the autovacuum subprocess.
 max_connections                 | 100     | Sets the maximum number of concurrent connections.
 shared_buffers                  | 16384   | Sets the number of shared memory buffers used by the ...
 work_mem                        | 4096    | Sets the maximum memory to be used for query workspaces.
・
・
・
(300 rows)

設定項目数は300件を超え(バージョンによって異なる)、name・setting・descriptionの3カラムのみが表示される。

個別の設定値だけを確認する場合はSHOWコマンドに設定名を指定する。

SHOW work_mem;
 work_mem
----------
 4MB
(1 row)

pg_settingsで設定値を検索する

SHOW ALLは一覧表示のみで絞り込みができないため、設定名を一部の文字列で検索したりカテゴリ単位で絞り込んだりする場合はpg_settingsシステムカタログを使用する。

SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE '%timeout%';

                name                 | setting | unit
--------------------------------------+---------+------
 archive_timeout                      | 0       | s
 authentication_timeout               | 60      | s
 checkpoint_timeout                   | 300     | s
 deadlock_timeout                     | 1000    | ms
 idle_in_transaction_session_timeout  | 0       | ms
 idle_session_timeout                 | 0       | ms
 lock_timeout                         | 0       | ms
 statement_timeout                    | 0       | ms
 tcp_user_timeout                     | 0       | ms
 wal_receiver_timeout                 | 60000   | ms
 wal_sender_timeout                   | 60000   | ms
(11 rows)

categoryカラムでカテゴリごとに絞り込むことも可能である。

SELECT DISTINCT category FROM pg_settings ORDER BY category;

                     category
----------------------------------------------------
 Autovacuum
 Client Connection Defaults / Locale and Formatting
 Connections and Authentication / Connection Settings
 Query Tuning / Planner Cost Constants
 Resource Usage / Memory
・
・
・
(42 rows)

SELECT name, setting, unit
FROM pg_settings
WHERE category = 'Resource Usage / Memory'
ORDER BY name;

            name            | setting | unit
----------------------------+---------+------
 autovacuum_work_mem        | -1      | kB
 dynamic_shared_memory_type | posix   |
 hash_mem_multiplier        | 2       |
 huge_pages                 | try     |
 huge_page_size              | 0      | kB
 logical_decoding_work_mem  | 65536   | kB
 maintenance_work_mem       | 65536   | kB
 max_prepared_transactions  | 0       |
 max_stack_depth            | 2048    | kB
 min_dynamic_shared_memory  | 0       | MB
 shared_buffers              | 16384  | 8kB
 shared_memory_type         | mmap    |
 temp_buffers                | 1024   | 8kB
 vacuum_buffer_usage_limit  | 256     | kB
 work_mem                    | 4096   | kB
(15 rows)

参考: 【PostgreSQL】接続数の上限と現在の接続数を確認する

pg_settingsで確認できる主なカラム

pg_settingsはSHOW ALLにはない詳細なカラムを持つ。

name: 設定名
setting: 現在の設定値
unit: 単位(kB、ms、sなど)
category: 設定のカテゴリ
short_desc: 設定の簡単な説明
context: 設定変更に必要な操作
vartype: 値の型(bool、integer、stringなど)
min_val、max_val: 設定可能な最小値・最大値
boot_val: PostgreSQL起動時のデフォルト値
reset_val: RESETコマンド実行時に戻る値
source: 現在の設定値の反映元
sourcefile、sourceline: 設定ファイルのパスと行番号
pending_restart: 再起動待ちの変更があるか

contextで変更方法を確認する

設定値を変更する際は、変更を反映するために再起動やリロードが必要な項目とセッション単位で即座に反映できる項目がある。contextカラムを確認すると変更方法が分かる。

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

           name             | setting | context
-----------------------------+---------+------------
 log_min_duration_statement  | -1      | superuser
 max_connections              | 100     | postmaster
 shared_buffers               | 16384   | postmaster
 work_mem                     | 4096    | user
(4 rows)

contextの主な値は以下のとおり。

postmaster: 設定ファイルを変更した上でPostgreSQLの再起動が必要
sighup: 設定ファイルを変更した上でSIGHUPシグナル送信(pg_reload_conf()など)で反映
superuser: スーパーユーザーがSETコマンドでセッション単位に変更可能
user: 一般ユーザーがSETコマンドでセッション単位に変更可能

shared_buffersやmax_connectionsはpostmasterのため、postgresql.confを変更してもPostgreSQLを再起動するまで反映されない。

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

sourceとpending_restartで反映状況を確認する

sourceカラムを確認すると、現在の設定値がどこから反映されているかが分かる。

SELECT name, setting, source
FROM pg_settings
WHERE name = 'max_connections';

       name       | setting |       source
-------------------+---------+---------------------
 max_connections   | 100     | configuration file
(1 row)

sourceにはdefault(デフォルト値のまま)、configuration file(postgresql.confによる変更)、command line(起動オプションでの変更)などの値が入る。

postmasterコンテキストの設定をpostgresql.confで変更した直後は、pending_restartカラムで再起動待ちかどうかを確認できる。

SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

     name       | setting | pending_restart
-----------------+---------+-----------------
 max_connections | 100     | t
(1 row)

pending_restartがtの項目は、settingカラムがまだ古い値のままであり、PostgreSQLを再起動すると設定ファイルの新しい値に反映される。