PostgreSQLのパラメタ設定方法についてまとめます。PostgreSQLのパラメタは「リロードなしに反映できるもの」「リロードが必要となるもの」「PostgreSQLの再起動が必要となるもの」の3種類があります。
- PostgreSQL インストール
- PostgreSQL データベースの作成
- PostgreSQL contribモジュールの使い方
- PostgreSQL パラメタの設定方法 (いまここ)
- PostgreSQL ベンチマークツールの紹介
- PostgreSQL WAL(Write Ahead Log)の基本説明
- PostgreSQL バックアップとリストア
- PostgreSQL PITR(Point In Time Recovery)基本概念の説明
- PostgreSQL PITR(Point In Time Recovery)操作方法の説明
- PostgreSQL PITR(Point In Time Recovery)タイムライン操作
- PostgreSQL ストリーミングレプリケーションの最小構成
- PostgreSQL ストリーミングレプリケーションのパラメタ説明
- PostgreSQL ストリーミングレプリケーションの障害復旧
- PostgreSQL ストリーミングレプリケーションの多段構成
- PostgreSQL ストリーミングレプリケーションのDR向け設定
- PostgreSQL ストリーミングレプリケーションのコンフリクト
- PostgreSQL 自動バキューム(AUTO VACUUM)
- PostgreSQL HOT(Heap Only Tuple)
- PostgreSQL インデックスのメンテナンス
- PostgreSQL 統計情報の更新
- PostgreSQL 実行計画
- PostgreSQL スロークエリの調査方法
概要操作
設定値の確認
PostgreSQLに設定されているパラメタはSHOW ALLコマンドを使う事で確認します。
[postgres@centos10 ~]$ psql -c "SHOW ALL"
name | setting | description
----------------------------------------+----------------------------------------+--------------------------------------------------------------------------------------------------------------------------
---------------
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_cleanup_command | | Sets the shell command that will be executed at every restart point.
archive_command | cp %p /archive/%f | Sets the shell command that will be called to archive a WAL file.
archive_mode | on | Allows archiving of WAL files using archive_command.
archive_timeout | 1min | Forces a switch to the next WAL file if a new file has not been started within N seconds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
個人的な好みかもしれませんが、SHOW ALLをgrepで絞り込む操作がお気に入りです。
[postgres@centos10 ~]$ psql -c "SHOW ALL" | grep recovery hot_standby | on | Allows connections and queries during recovery. ignore_invalid_pages | off | Continues recovery after an invalid pages failure. promote_trigger_file | | Specifies a file name whose presence ends recovery in the standby. recovery_end_command | | Sets the shell command that will be executed once at the end of recovery. recovery_min_apply_delay | 0 | Sets the minimum delay for applying changes during recovery. recovery_target | | Set to "immediate" to end recovery as soon as a consistent state is reached. recovery_target_action | pause | Sets the action to perform upon reaching the recovery target. recovery_target_inclusive | on | Sets whether to include or exclude transaction with recovery target. recovery_target_lsn | | Sets the LSN of the write-ahead log location up to which recovery will proceed. recovery_target_name | | Sets the named restore point up to which recovery will proceed. recovery_target_time | | Sets the time stamp up to which recovery will proceed. recovery_target_timeline | latest | Specifies the timeline to recover into. recovery_target_xid | | Sets the transaction ID up to which recovery will proceed. trace_recovery_messages | log | Enables logging of recovery-related debugging information. [postgres@centos10 ~]$
そのほか、SHOWの後にパラメタ名を指定する事でも設定値を確認する事ができます。
[postgres@centos10 ~]$ psql psql (13.1) Type "help" for help. postgres=# SHOW recovery_target_action; recovery_target_action ------------------------ pause (1 row) postgres=#
パラメタの変更方法(1) – SETコマンド
パラメタはSETコマンドを使用すると、リロードなしに即時パラメタが反映されます。ただし、リロードなしに反映できるパラメタとリロードまたは再起動が必要となるパラメタがある事に注意が必要です。
[postgres@centos10 ~]$ psql psql (13.1) Type "help" for help. postgres=# SET max_connections = 200; ERROR: パラメータ"max_connections"を変更するにはサーバの再起動が必要です postgres=# SET application_name = 'test_app'; SET postgres=# SHOW application_name; application_name ------------------ test_app (1 row) postgres=#
パラメタの変更方法(2) – 設定ファイル
postgresql.confやpostgresql.auto.confを設定すると、次回のリロードまたは再起動時に設定が反映されます。
動作確認の意味を込めて、postgresql.confでmax_connectionsを変更してみましょう。操作例は以下の通りです。
# vi 13/data/postgresql.conf <omitted> # Add settings for extensions here max_connections = 200
再起動を行い設定を反映させます。
[postgres@centos10 ~]$ /usr/pgsql-13/bin/pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2020-12-13 12:37:44.882 JST [1527] LOG: ログ出力をログ収集プロセスにリダイレクトしています 2020-12-13 12:37:44.882 JST [1527] ヒント: ここからのログ出力はディレクトリ"log"に現れます。 done server started [postgres@centos10 ~]$ psql psql (13.1) Type "help" for help. postgres=# SHOW max_connections; max_connections ----------------- 200 (1 row) postgres=#
詳細操作
設定反映のタイミング
PostgreSQLのパラメタは「リロードなしに反映できるもの」「リロードが必要となるもの」「PostgreSQLの再起動が必要となるもの」の3種類があります。
どのタイミングで設定反映可能かは、pg_settingsシステムビューのcontext列を見れば分かります。
[postgres@centos10 ~]$ psql -c "SELECT name, context FROM pg_settings"
name | context
----------------------------------------+-------------------
allow_system_table_mods | superuser
application_name | user
archive_cleanup_command | sighup
archive_command | sighup
archive_mode | postmaster
archive_timeout | sighup
array_nulls | user
authentication_timeout | sighup
autovacuum | sighup
autovacuum_analyze_scale_factor | sighup
autovacuum_analyze_threshold | sighup
contextがuserまたはsuperuserであるものはSETコマンド実行時に反映されます。contextがsighupであるものはSIGHUPシグナルを受け取ってリロードされたタイミングで設定が反映されます。contextがpostmasterであるものはpostgresqlの再起動のタイミングで設定が反映されます。
| context | SETコマンド | リロード | 再起動 |
|---|---|---|---|
| user, superuser | ○ | ○ | ○ |
| sighup | × | ○ | ○ |
| postmaster | × | × | ○ |
設定変更方法
contextがuserまたはsuperuserであるものは、SETコマンドで設定を反映することができます。
[postgres@centos10 ~]$ psql psql (13.1) Type "help" for help. postgres=# SET client_encoding = 'SJIS'; SET postgres=#
ALTER SYSTEMコマンドを使用すると、postgresql.auto.confに設定値が書き込まれます。書き込まれた設定値は、次回のリロードまたは再起動で反映されます。
ALTER SYSTEMコマンドはSETサブコマンドでpostgresql.auto.confに設定を書き込み、RESETサブコマンドでpostgresql.auto.confの設定を削除しデフォルト値に戻す事ができます。
[postgres@centos10 ~]$ psql psql (13.1) Type "help" for help. postgres=# ALTER SYSTEM SET max_connections = 300; ALTER SYSTEM postgres=# ALTER SYSTEM RESET client_encoding; ALTER SYSTEM postgres=#
設定反映方法
SETコマンドで指定したパラメタは即時反映されます。一方、postgresql.confやpostgresql.auto.confに書き込まれた値は、リロードまたは再起動で設定が反映されます。
以下のような操作でpostgresqlのリロードが可能です。リロードを実施すると、contextがpostmasterとなっているパラメタ以外を反映する事ができます。
[postgres@centos10 ~]$ /usr/pgsql-13/bin/pg_ctl reload server signaled [postgres@centos10 ~]$
以下のコマンドでpostgresqlの再起動が可能です。再起動を実施すると、全てのパラメタを反映する事ができます。
[postgres@centos10 ~]$ /usr/pgsql-13/bin/pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2020-12-13 13:13:37.185 JST [1856] LOG: ログ出力をログ収集プロセスにリダイレクトしています 2020-12-13 13:13:37.185 JST [1856] ヒント: ここからのログ出力はディレクトリ"log"に現れます。 done server started [postgres@centos10 ~]$
rootユーザならば、systemctlを使ってpostgresqlを再起動する事もできます。
[root@centos10 ~]# systemctl restart postgresql-13.service [root@centos10 ~]#
