PostgreSQL環境設定内容の確認方法

PostgreSQLは、postgresql.confというファイルで 環境設定を行う。

設定された内容は、pg_settingsというビューで確認でき、
pg_settingsと設計書を比較して内容を確認していたが、
容易にいかないため、断念していた。

理由は、設定した内容が そのまま格納されている訳ではないため。

例えば、shared_buffersに100MBと設定しても、
100、もしくは 100MBと格納される訳ではない。

pg_settingsには、unitという項目があり、単位がある。

shared_buffersは、8kB単位となるため、
shared_buffersに100MBと設定すると、
pg_settingsは以下と格納される。
・setting：12800
・unit 　：8kB

テーブルやインデックスは shared_buffersに乗っかるが、
8KB単位になるからだ。

pg_settingsのunitは、以下の5種類ある。
・16MB
・8kB
・kB
・min
・ms
・s

例えば、16MBなのは 以下。
・max_wal_size
・min_wal_size

これは、WALが1ファイル16MBだから。

そこで、アドオン・テーブルを作成し、CSVファイルへ出力して
設計書と内容を確認する方法を紹介する。

概要は以下。
・pg_settingsからアドオン・テーブルへINSERTし、その後単位毎にUPDATEし、
　単位を揃える
・pg_settingsからデータを取得する際、IPアドレスを指定して接続する。
　ローカル接続すると、以下の値が0になってしまうため。
　・tcp_keepalives_idle
　・tcp_keepalives_interval
　・tcp_keepalives_count
・pg_settingsからデータを出力する際、区切り文字を「|」等に指定する。
　「,」だと、データ上に「,」があると、 Excel上 セルが分かれてしまうため。
・ExcelでCSVファイルを開き、区切り位置に「|」を指定し、列を分割する。
・アドオン・テーブルは以下をPrimary Keyにし、環境が複数あった場合、
　EXCEL内で比較／確認できるように設計している。
　・server_name
　・instance
　・name（pg_settingsのname）

-- テーブル作成
create table infra_settings (
server_name varchar(030), -- サーバ名
instance varchar(030), -- インスタンス名
name varchar(064),
setting varchar(256),
setting_kb varchar(030), -- 容量の場合、kbを格納
setting_mb varchar(030), -- 容量の場合、mbを格納
setting_gb varchar(030), -- 容量の場合、gbを格納
setting_ms varchar(030), -- 時間の場合、msを格納
setting_s varchar(030), -- 時間の場合、sを格納
setting_min varchar(030), -- 時間の場合、minを格納
setting_h varchar(030), -- 時間の場合、hを格納
unit varchar(030),
category varchar(256),
vartype varchar(030),
source varchar(064),
min_val varchar(030),
max_val varchar(030),
boot_val varchar(256),
sourcefile varchar(256),
pending_restart varchar(030),
CONSTRAINT infra_settings_pk
PRIMARY KEY (server_name,instance,name)
USING INDEX TABLESPACE index_tablespace
)
WITH (FILLFACTOR=95)
TABLESPACE <テーブルスペース名>;

-- INSERT（hostとistcは置換すること）
insert into infra_settings (
server_name
,instance
,name
,setting
,unit
,category
,vartype
,source
,min_val
,max_val
,boot_val
,sourcefile
,pending_restart )
select
:host
,:istc
,name
,setting
,unit
,category
,vartype
,source
,min_val
,max_val
,boot_val
,sourcefile
,pending_restart
from pg_settings;
--
update infra_settings
set setting_mb = (to_number(setting,'999999999')*16)
, setting_gb = (to_number(setting,'999999999')*16/1024)
where unit = '16MB';
--
update infra_settings
set setting_kb = (to_number(setting,'999999999')*8)
, setting_mb = (to_number(setting,'999999999')*8/1024)
, setting_gb = (to_number(setting,'999999999')*8/1024/1024)
where unit = '8kB';
--
update infra_settings
set setting_kb = to_number(setting,'999999999')
, setting_mb = (to_number(setting,'999999999')/1024)
where unit = 'kB';
--
update infra_settings
set setting_min = to_number(setting,'999999999')
, setting_h = (to_number(setting,'999999999')/60)
where unit = 'min';
--
update infra_settings
set setting_ms = to_number(setting,'999999999')
, setting_s = (to_number(setting,'999999999')/1000)
, setting_min = (to_number(setting,'999999999')/1000/60)
where unit = 'ms';
--
update infra_settings
set setting_s = to_number(setting,'999999999')
, setting_min = (to_number(setting,'999999999')/60)
where unit = 's';