手を動かすPostgreSQL(8)-GUCパラメータリロード変更編-

前回のあらすじ

前回はGUCパラメータを再起動で変更する方法について説明しました。

sighup(postgresql.confのリロード)について

sighupのパラメータとして、今回はfsyncを使います。

ossdb=# SELECT * FROM pg_settings WHERE name='fsync';
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name            | fsync
setting         | on
unit            | 
category        | Write-Ahead Log / Settings
short_desc      | Forces synchronization of updates to disk.
extra_desc      | The server will use the fsync() system call in several places to make sure that updates are physically written to disk. This insures that a database cluster will recover to a consistent state after an operating system or hardware crash.
context         | sighup
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | on
reset_val       | on
sourcefile      | 
sourceline      | 
pending_restart | f

一応postmaster(再起動)のパラメータが反映されないのも確かめたいので、postmasterのパラメータとしてshared_buffersを用います。

ossdb=# SELECT * FROM pg_settings WHERE name='shared_buffers';
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | shared_buffers
setting         | 8192
unit            | 8kB
category        | Resource Usage / Memory
short_desc      | Sets the number of shared memory buffers used by the server.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 16
max_val         | 1073741823
enumvals        | 
boot_val        | 1024
reset_val       | 8192
sourcefile      | /usr/local/var/postgresql@14/postgresql.conf
sourceline      | 127
pending_restart | f

前回shared_buffersは半分にしたので、今回は倍にして元の値に戻します。fsyncは普通にoffにしましょう。

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 128MB			# min 128kB
					# (change requires restart)
#huge_pages = try			# on, off, or try
					# (change requires restart)
#huge_page_size = 0			# zero for system default
					# (change requires restart)
#temp_buffers = 8MB			# min 800kB
#max_prepared_transactions = 0		# zero disables the feature
					# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
・
・
・
#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#wal_level = replica			# minimal, replica, or logical
					# (change requires restart)
#fsync = off				# flush data to disk for crash safety
					# (turning this off can cause
					# unrecoverable data corruption)
#synchronous_commit = on		# synchronization level;
					# off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync		# the default is the first option
					# supported by the operating system:
					#   open_datasync
					#   fdatasync (default on Linux and FreeBSD)
					#   fsync
					#   fsync_writethrough
					#   open_sync

変更できたら、postgresql.confを保存します。postgresql.confのリロードは、postmasterにSIGHUPシグナルが送れれば方法は割となんでもいいです。今回は

ossdb=# SELECT pg_reload_conf();

これで行きます。
それでは変更されたか結果を見てみましょう。

ossdb=# SELECT * FROM pg_settings WHERE name='fsync';
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name            | fsync
setting         | off
unit            | 
category        | Write-Ahead Log / Settings
short_desc      | Forces synchronization of updates to disk.
extra_desc      | The server will use the fsync() system call in several places to make sure that updates are physically written to disk. This insures that a database cluster will recover to a consistent state after an operating system or hardware crash.
context         | sighup
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | on
reset_val       | on
sourcefile      | 
sourceline      | 
pending_restart | f

また、shared_buffersは変更されていません。8192のままです。

ossdb=# SELECT * FROM pg_settings WHERE name='shared_buffers';
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | shared_buffers
setting         | 8192
unit            | 8kB
category        | Resource Usage / Memory
short_desc      | Sets the number of shared memory buffers used by the server.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 16
max_val         | 1073741823
enumvals        | 
boot_val        | 1024
reset_val       | 8192
sourcefile      | /usr/local/var/postgresql@14/postgresql.conf
sourceline      | 127
pending_restart | f

これでリロードによるパラメータ変更ができました。

---

宣伝

OSS-DB Goldの認定教材をリリースしました。
2023年2月時点で唯一のVer.3.0対応の教材です。
受験を検討されている方はぜひご検討の程をお願い致します。

---

次回の記事はこちら