OSS-DB Silver
技術解説無料セミナー
2021/06/29 開催

主題

運⽤管理（52％）

副題

設定ファイル 【重要度︓5】

本⽇の講師

富⼠通株式会社
⻑尾 正樹© LPI-Japan / EDUCO all rights reserved.

講師の紹介 (1/2)
n⻑尾 正樹#OSS-DB
 2012年より、富⼠通のPostgreSQLデータベース「FUJITSU Software Enterprise Postgres」の主にデータベース移⾏アセスメント業務に従事。
 現在は、プロモーションや、PostgreSQL開発者向けに「PostgreSQL技術インデックス」の執筆を通して、PostgreSQLのポテンシャル・魅⼒をお伝えしている。
PostgreSQLの運⽤ノウハウや導⼊事例、最新動向などを紹介「PostgreSQLインサイド」
PostgreSQL
富⼠通の技術者に聞く︕
PostgreSQLの技術
技術インデックス

PostgreSQLの利⽤⽅法を詳しく解説

チューニング

性能

データ連携

l 開発の背景l 今後の展望

強化機能を技術者にインタビュー

PostgreSQLインサイド https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/Enterprise Postgres https://www.fujitsu.com/jp/products/software/middleware/database/enterprisepostgres/© LPI-Japan all rights reserved.

2

講師の紹介 (2/2)
n富⼠通では、導⼊検討から開発、運⽤までレベルに合わせたトレーニングをご⽤意
フェーズ
導⼊検討
・設計

開発・構築

運⽤・保守

講習会

eラーニング
富⼠通のデータベース概説

初級

PostgreSQL技術者のための富⼠通のデータベース適⽤

中級

実習で学ぶ
PostgreSQLによるSQL開発

初級

富⼠通版PostgreSQLによるデータベース構築と運⽤のポイント

初級

実習で学ぶ PostgreSQL
DB構築と運⽤操作#OSS-DB上級

PostgreSQL
導⼊トレーニング

初級

PostgreSQLクラスタシステム
上級
構築トレーニング

PostgreSQL
運⽤管理トレーニング

中級フェーズと講座の関係は、⼀例です。上記ラインナップは、データベーストレーニングの⼀部です。
詳細は、「 https://www.fujitsu.com/jp/software/resources/training/ 」ご覧ください。
© LPI-Japan all rights reserved.

3

OSS-DB／オープンソースデータベース技術者認定試験

nOSS-DBとは#OSS-DB

オープンソースのデータベースソフトウェア「PostgreSQL」を扱うことができる技術⼒の認定です。様々な分野でPostgreSQLの利⽤拡⼤が進む中でOSS-DBの認定を持つことは、⾃分のキャリアのアピールにもつながります。

üOSS-DB Goldは設計やコンサルティングができる技術⼒の証明

PostgreSQLについての深い知識を持ち、データベースの設計や開発のほか、パフォーマン
スチューニングやトラブルシューティングまで⾏えることが証明できますüOSS-DB Silverは導⼊や運⽤ができる技術⼒の証明

PostgreSQLについての基本的な知識を持ち、データベースの運⽤管理が⾏えるエンジニアとしての証明ができますü対象のバージョンはPostgreSQL 11© LPI-Japan all rights reserved.

4

今回のテーマ
nPostgreSQLのしくみ(アーキテクチャ)を知り、動作をイメージする#OSS-DB

 運⽤時に知っておいて欲しいしくみについて説明する

nPostgreSQLを最適に動作するよう設定する PostgreSQLは、様々な運⽤規模・形態に対応できるよう、多くの設定パラメータで調整できる作りになっている
 Silverの出題範囲をやや超える部分もあるが、しくみと動作をイメージすることが、PostgreSQLの理解を深め、応⽤が利くようになる

nその他
 実施例については、以下の環境で確認した- Red Hat Enterprise Linux 7.7- PostgreSQL 11.12© LPI-Japan all rights reserved.

5

⽬次
nPostgreSQLの基本構成（概要）nPostgreSQLを利⽤するまでの４ステップ#OSS-DB インストール クラスタ作成
 PostgreSQLサーバ起動
 接続と利⽤

n設定ファイル(postgresql.conf)について
n運⽤時に意識すべきPostgreSQLのしくみとパラメータ設定
 接続と認証 データアクセス VACUUM(バキューム)とANALYZE(アナライズ)
 ロガー© LPI-Japan all rights reserved.

6

PostgreSQLの基本構成
nPostgreSQLを構成する要素（概要）※Silver関連の範囲
テーブル、インデックス、
WALのキャッシュ領域

接続処理

postgresインスタンス
接続要求

セッション
確⽴

ファイル

SQLの解析
と実⾏

共有メモリ
・ 共有バッファ・ WALバッファなど

セッション毎に確保される作業⽤メモリ

データベースのファイル群

バックグラウンドワーカプロセス
アーカイバ
チェックポインタ(ディスク上のファイル)
データファイル(table, index)
WALファイル
アーカイブWAL
ファイル

⾃動バキューム(ランチャ)

サーバログ
ファイル

ロガー(サーバログ)

設定ファイル

SQL実⾏とは⾮同期に動作する、各種内部処理群

・・・

メモリ

プロセスメモリ・ 作業メモリ・ メンテナンス⽤作業メモリ
・ ⼀時バッファ

1対1の関係
データベースクラスタ

・・・

プロセス

プロセス⽣成バックエンドプロセス

クライアント凡例

マスターサーバプロセス#OSS-DB

PostgreSQLの
動作を指定© LPI-Japan all rights reserved.

7

PostgreSQLを利⽤するまでの４ステップ
nインストールから利⽤までの簡単な流れについて説明#OSS-DB①インストール③PostgreSQLサーバ起動(pg_ctl start –D <クラスタ>コマンド)②クラスタ作成(initdbコマンド)④接続/利⽤(psql,メタコマンド)

postgresインスタンス

データベースクラスタ

接続要求

マスターサーバプロセス

プロセス⽣成バックエンドプロセス

クライアント
セッション
確⽴

プロセスメモリ・ 作業メモリ・ メンテナンス⽤作業メモリ
・ ⼀時バッファ

共有メモリ
・ 共有バッファ・ WALバッファなど
バックグラウンドワーカプロセス
アーカイバ
チェックポインタ(ディスク上のファイル)
データファイル(table, index)
WALファイル
アーカイブWAL
ファイル

⾃動バキューム(ランチャ)

サーバログ
ファイル

ロガー(サーバログ)

設定ファイル・・・・・・© LPI-Japan all rights reserved.

8

①インストール (Red Hat Enterprise Linuxの場合)
nソースコードからのインストールの例(すべてデフォルト) PostgreSQL管理⽤のユーザを作成しておく ソースコードをコミュニティのサイトから
ダウンロードし展開する
 展開したフォルダに移動してコンパイルする※デフォルトのインストール先︓/usr/local/pgsql(configureのオプションにて、別フォルダも指定できる)※makeコマンドの成功メッセージ︓
All of PostgreSQL is successfully made. Ready to install.

 OSの管理者ユーザでインストール※make installの成功メッセージ︓
PostgreSQL installation complete.

n設定

 データベースクラスタ⽤のフォルダを準備 環境変数を設定する
export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
export PGDATA=/usr/local/pgsql/data# useradd postgres# passwd postgres# su - postgres#OSS-DB
事前にOSの必要パッケージもインストールしておく !

$ tar xvzf postgresql-11.12.tar.gz$ cd postgresql-11.12$ ./configure$ make$ make check$ sudo make install# mkdir –p /usr/local/pgsql/data# chown postgres:postgres /usr/local/pgsql/data# vi ~/.bash_profile# source ~/.bash_profile
実行ファイル、ライブラリ、データ格納先のパスを指定© LPI-Japan all rights reserved.

9②クラスタ作成
ninitdbコマンドを実⾏

環境変数PGDATAで定義済みなら、省略可能#OSS-DB initdb --no-locale --encoding=UTF8 -D /usr/local/pgsql/data
日本語を扱う場合、文字処理の性能面から、ロケールはno-locale(無効)を推奨

日本語を扱う場合、エンコーディング(文字セット)はUTF8かEUC_JPかEUC_JIS_2004を指定

nデータベースクラスタのフォルダ構成 (⻘⾊はフォルダ)$ ls /usr/local/pgsql/data/
PG_VERSION
pg_dynshmem
pg_multixact
base (A)pg_hba.conf (E) pg_notify
globalpg_ident.conf pg_replslot
pg_commit_ts pg_logical
pg_serial

pg_snapshots
pg_stat
pg_stat_tmp
pg_subtransデフォルトで以下の3つのデータベースが作成される(C)、(D)
PostgreSQL管理者ユーザが⽤意し、設定ファイルでパスを指定する。

(ディスク上のファイル)
データファイル(table, index)(A)

WALファイル(B)

アーカイブWAL
ファイル

(C)

サーバログ
ファイル

(D)

設定ファイル(E)・・・

base (A)1 ←template1データベース用フォルダ(実データ)
13286 ←template0データベース用フォルダ(実データ)
13287 ←postgresデータベース用フォルダ(実データ)

pg_tblspc
postgresql.auto.conf
pg_twophase postgresql.conf (E)pg_wal (B)postmaster.opts
pg_xact

データベースクラスタ© LPI-Japan all rights reserved.

10

③PostgreSQLサーバ起動
nPostgreSQLサーバの起動⽅法

環境変数PGDATAで定義済みなら、省略可能

 pg_ctl start –D /usr/local/pgsql/data[root@VM037311 ~]# ps aux | grep ^postgres
postgres 11963 0.0 0.7 272088 13168 pts/0
postgres 11965 0.0 0.1 272208 2908 ?
postgres 11966 0.0 0.1 272088 2136 ?
postgres 11967 0.0 0.2 272088 5040 ?
postgres 11968 0.0 0.1 272500 2072 ?
postgres 11969 0.0 0.0 127020 1072 ?
postgres 11970 0.0 0.0 272500 1596 ?

S
Ss
Ss
Ss
Ss
Ss
Ss

#OSS-DB
postgresインスタンス内の各種プロセスが存在する

10:44
10:44
10:44
10:44
10:44
10:44
10:44

0:00 /usr/local/pgsql/bin/postgres
0:00 postgres: checkpointer0:00 postgres: background writer
0:00 postgres: walwriter
0:00 postgres: autovacuum launcher
0:00 postgres: stats collector
0:00 postgres: logical replication launcher

npg_ctlのその他の主な機能
 stop指定されたデータディレクトリで稼働しているサーバを停⽌する

 restart
 reload status

stopとstartを実⾏する（設定ファイルの設定値が全て反映される）
設定ファイルの再読み込み（パラメータの⼀部は反映のために再起動が必要）※postgresql.confのコメントにサーバが稼働しているかどうかを確認する”(change requires restart)”が⼊っている項⽬- mオプション（smart:全クライアントの接続終了を待つ / fast:トランザクションをすべてロールバックする /immediate:緊急停⽌）© LPI-Japan all rights reserved.

11

④接続と利⽤ (1/2)
npsqlコマンド（PostgreSQLの対話的ターミナル）#OSS-DB $ psql [オプション] [データベース名(ユーザ名)]- 主なオプション -h: ホスト、-p: ポート、-U:ユーザ名-l: データベース⼀覧表⽰、-c: 指定コマンドの実⾏、-f: 指定ファイル内のコマンドの実⾏、-d: データベース名

 実⾏例(スーパーユーザで実⾏した場合)$ psql postgres
psql (11.12)
Type "help" for help.
postgres=# SQL文;
postgres=# メタコマンド
postgresはデータベース名

 メタコマンド$ psql -l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access privileges-----------+----------+----------+---------+-------+----------------------postgres | postgres | UTF8
| C
| C
|
template0 | postgres | UTF8
| C
| C
| =c/postgres
+
|
|
|
|
| postgres=CTc/postgres
template1 | postgres | UTF8
| C
| C
| =c/postgres
+
|
|
|
|
| postgres=CTc/postgres
デフォルトのデータベース(postgres)、および、(3 rows)
テンプレートデータベース(template0, template1)が作成されている
template0: データベースの初期内容を保持（書き込み不可）
template1: データベースのひな形（書き込み可能）- ¥q(psqlを終了する)、¥d(テーブル、ビュー、シーケンスの⼀覧を表⽰する)など© LPI-Japan all rights reserved.

12

④接続と利⽤ (2/2)
nテーブル作成・アクセス#OSS-DB$ psql postgres
psql (11.12)
Type "help" for help.
postgres=# create table tbl (id int, name text);
CREATE TABLE
postgres=# insert into tbl values(1, 'suzuki');
INSERT 0 1
postgres=# select * from tbl;
id | name----+-------1 | suzuki(1 row)
postgres=# ¥q© LPI-Japan all rights reserved.

13

設定ファイル(postgresql.conf)についてn⽤途は︖
 PostgreSQLの動作を決める設定ファイル

npostgresql.confの記述⽅法#OSS-DB
postgresql.confの設定値の例#listen_addresses = 'localhost'#port = 5432
max_connections = 100

 書式「パラメータ名 = 設定値」（=は空⽩でもよい） 1⾏に1つずつ
 空⽩⾏は無視され、#から⾏末まではコメント
shared_buffers = 128MB#logging_collector = off パラメータ名は、⼤⽂字/⼩⽂字を区別しない#log_rotation_age = 1d
 設定値の型︓論理値(Boolean)、整数(integer)、
浮動⼩数点(floating point)、#search_path = '"$user", public'
⽂字列(string)、列挙型(enum)
⼤⽂字/⼩⽂字の 論理値︓on, off, true, false, yes, no, 1, 0 (t,f,y,nも使える)
区別なし ⽂字列︓複数指定や空⽩を含める場合は、ʼ(シングルクォート)で囲む
⽂字列内のシングルクォートは”(ダブルクォート)で囲む
 メモリの単位︓B(バイト), kB(キロバイト), MB(メガバイト), GB(ギガバイト), TB(テラバイト)
 時間の単位︓ms(ミリ秒), s(秒), min(分), h(時間), d(⽇)
単位については、
⼤⽂字/⼩⽂字の区別あり© LPI-Japan all rights reserved.

14

設定ファイル(postgresql.conf)に関する例題
npostgresql.conf の記述⽅法について、正しいものを2つ選びなさい。

#OSS-DBA) 論理型のパラメータで真を表す値として、on、true、t、yes、y などが使⽤できる。
B) 数値型のパラメータの値は、読みやすくするため 12,345 のように3桁ごとにカンマを⼊れて記述する。
C) 時間の単位にはms（ミリ秒）、s（秒）、m（分）、h（時間）、d（⽇）があり、⼤⽂字と⼩⽂字は区別されない。
D) メモリの単位は B、kB、MB、GB、TB があるが、キロバイトの k だけが⼩⽂字、他はすべて⼤⽂字で記述する。
E) 時間やメモリのパラメータ値を記述するとき、単位を省略して数値だけ記述すると、それぞれデフォルトとしてミリ秒、バイトが使⽤される。
※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_05/98_191113© LPI-Japan all rights reserved.

15

運⽤時に意識すべきPostgreSQLのしくみ
nPostgreSQLの主な機構を①〜④に分けて説明#OSS-DB①接続/認証②データアクセス
postgresインスタンス

接続要求

マスターサーバプロセス

プロセス⽣成バックエンドプロセス

クライアント
セッション
確⽴

プロセスメモリ・ 作業メモリ・ メンテナンス⽤作業メモリ
・ ⼀時バッファ

データベースクラスタ(ディスク上のファイル)共有メモリ
・ 共有バッファ・ WALバッファなど

データファイル(table, index)
WALファイル

バックグラウンドワーカプロセス

アーカイブWAL
ファイル

アーカイバ
チェックポインタ
⾃動バキューム(ランチャ)

サーバログ
ファイル

ロガー(サーバログ)

・・・

・・・

③VACUUM/
ANALYZE

設定ファイル④ロガー© LPI-Japan all rights reserved.

16

①接続と認証のしくみ
n接続要求で、認証/バックエンドプロセス起動/セッション確⽴が⾏われる
nクライアントの認証⽅法は pg_hba.conf に記述する#OSS-DB

クライアント認証⽤のファイル(pg_hba.conf)

認証
postgresインスタンス
マスターサーバプロセス

1) 接続要求

クライアント3) セッション確⽴

2) プロセス⽣成「どこから(ADDRESS)誰(USER)がどのデータベース(DATABASE)にどんな接続⽅式(TYPE)でどのような認証⽅式(METHOD)でアクセスするか」 を1⾏で指定する。認証時は、先頭⾏から順にチェックされる。

バックエンドプロセス
プロセスメモリ・ 作業メモリ・ メンテナンス⽤作業メモリ
・ ⼀時バッファ# TYPE DATABASE USER ADDRESSMETHOD# IPv4 local connections:hostall
all 127.0.0.1/32 md5# IPv6 local connections:
hostall
all ::1/128
md5
・・・

意味

クライアントのIPアドレス127.0.0.1/32(*1)のすべてのユーザが、(レプリケーション以外の)すべてのデータベースにTCP/IP接続で
md5暗号化によるパスワード認証(*2)での接続を許可する。
(*1) ローカルループバックアドレス。⾃分⾃⾝のIPアドレス。localhostも同じ意味。
(*2) クライアントからのパスワードが暗号化されて送信される。

参考）テスト⽤に無条件でTCP/IP接続を許可させる設定
host all all 0.0.0.0/0 trust© LPI-Japan all rights reserved.

17

①設定パラメータ（postgresql.conf）
n接続⽤#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

listen_addresses

localhost

クライアントからの接続を受け付ける
PostgreSQLサーバ側のIPアドレスかホスト名を指定する。

(ʻ*ʼを指定すると、すべてのIPアドレスから接続できるようになる)

port

5432

接続時のTCPポートを指定する。

同⼀サーバでPostgreSQLを複数イ
ンスタンスを起動する場合には、それぞれ異なるportを指定する。

max_connections

100

最⼤受付数(*)を指定する。

search_path'"$user",
public'

SQLでスキーマを省略した場合の、スキーマ検索 (スキーマを扱う場合は考慮が必要)
順を指定する。

default_transaction_isolation'read
committed'

トランザクションのデフォルトの分離レベルを指定する。

(トランザクション分離レベルを意識する場合には考慮が必要)

client_encoding

sql_ascii

クライアントエンコーディングを指定する。

⽂字変換できないとエラーになる。

tcp_keepalives_xxx

0(OSのデフォ
ルト値を使⽤)クライアントから応答がない場合に⾃動切断できるよう指定する。

クライアントとの通信が不通になると、
⻑時間リソースが回収されなくなる。

max_connections数分のプロセス(*)インスタンスへの同時接続数 +メモリが消費される。プロセスメモリをsuperuser_reserved_connections(デフォルト値: 76MBで⾒積もっても、×100セッショ
3)の値
ンで最⼤7.6GBのメモリが必要になる。

© LPI-Japan all rights reserved.

18

①設定パラメータ（postgresql.conf）
nプロセスメモリ⽤#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

work_mem(作業メモリ)

4MB

SQLの処理において、並び替えやハッシュ
テーブル操作⽤に使⽤するメモリ容量を指定する。

複雑なSQLの場合、並⾏して処理が実⾏されるため、設定値×並
列数分のメモリを消費する場合がある。

meaintenance_work_mem(メンテナンス⽤作業メモリ)

64MB

VACUUM、CREATE INDEX、および
ALTER TABLE ADD FOREIGN KEYの様な保守操作で使⽤されるメモリの最⼤容量を指定する。

⼤きい値を設定することで、
VACUUM処理などの性能が良くなることがあるが、autovacuum_max_workersの数分消費される。

temp_buffers(⼀時バッファ)8MB

⼀時テーブルにアクセスする時にのみ使⽤されるバッファの最⼤量を指定する。

ー© LPI-Japan all rights reserved.

19

接続／認証に関する例題
nPostgreSQLサーバはIPアドレス 192.168.1.10 のサーバで動作しており、#OSS-DBpg_hba.conf の内容が以下である。
local all all ident
host all all 127.0.0.1/32 ident
host foo bar 192.168.1.0/24 md5
host all bar 192.168.1.0/24 reject
host foo all 192.168.1.0/24 trust
IPアドレスが 192.168.1.11 のクライアントからこのデータベースに接続するときの記述について、
選択肢の中から正しいものをすべて選びなさい。

A) ユーザ foo がデータベース bar にアクセスするときはパスワードを⼊⼒する必要がある。
B) ユーザ bar がデータベース foo にアクセスするときはパスワードを⼊⼒する必要がある。
C) ユーザ foo はデータベース foo にはアクセスできない。
D) ユーザ bar はデータベース bar にはアクセスできない。
E) ユーザ hoge はデータベース foo にパスワードなしでアクセスできる。
※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_01/14_120418© LPI-Japan all rights reserved.

20

②データアクセスのしくみ（書き込み保証）
n「⾼速なデータ更新」と「データ保証」を両⽴する#OSS-DB データ更新は共有メモリ上にキャッシュして⾼速に⾏う※WAL:ログ先⾏書き込み(Write Ahead Logging) ファイルの更新記録（トランザクションログ、WAL）をメモリに貯める トランザクションがCOMMITしたら、まとまった単位でシーケンシャルにディスクに書き出す
⼤きなファイルへのランダムアクセス(I/Oが多く、低速)

postgresインスタンス
データの
読み書き

クライアントクエリ

共有メモリ
共有バッファ
(shared_buffers)

バックエンドプロセス

データベースクラスタ(ディスク上のファイル)

読み込み(キャッシュ)8,192バイトのブロック単位

データファイル(table、index)

後で書き戻し
WALの書き出し

WALバッファ(wal_buffers)
WALファイル

WAL
WAL
WAL

COMMIT時に同期書き出し

まとまった単位でシーケンシャルに書き出すことで、より⾼速に処理できる© LPI-Japan all rights reserved.

21

②データアクセス（書き込み保証）(1/3)
nSQLが実⾏されたときのデータアクセスの流れ#OSS-DB
1. 共有バッファに更新すべきデータが無いと、ファイルからブロック単位でキャッシュする
2. 共有バッファ上でデータを更新し、変更履歴(WAL)をWALバッファに書き込む
3. COMMITで変更履歴(WAL)をWALファイルに書き込む
データベースクラスタ

postgresインスタンス※COMMIT: 処理がすべて成功し、結果を確定させること共有メモリー

トランザクションの例

クライアント更新系
SQL

BEGIN ;INSERT ⽂;
UPDATE ⽂;
COMMIT ;データ更新
バックエンドプロセス

共有バッファ

キャッシュされたブロック
WALバッファ

変更履歴
INSERTログ
UPDATEログ

postgresインスタンス
共有メモリー
共有バッファ

クライアントCOMMIT

この状態であれば、システムがダウンしても復旧は可能。
しかし、クラスタ内では、ファイル上のデータが更新されず、変更履歴(WAL)がたまっていく。

バックエンドプロセス

キャッシュされたブロック
WALバッファ

テーブル
データ

WAL
ファイル

データベース
クラスタ
テーブル
データ

WAL
ファイル

変更履歴
INSERTログ
UPDATEログ© LPI-Japan all rights reserved.

22

②データアクセス（書き込み保証）(2/3)
nチェックポイント処理（「チェックポインタ」プロセス）#OSS-DB SQL実⾏とは⾮同期に動作する
 定期的に共有バッファ内の更新されたブロックをファイルに書き出す
 不要になった変更履歴(WAL)はリサイクルあるいは削除される
データベースクラスタ

postgresインスタンス
共有メモリー
共有バッファ

クライアントバックエンドプロセス

キャッシュされたブロック
WALバッファ

テーブル
データ

チェック
ポインタ

WAL
ファイル

変更履歴
INSERTログ
UPDATEログ

これで、テーブルデータが更新され、WALファイルがあふれる⼼配がない。
しかし、データファイルの更新には多くのI/Oが発⽣するため、チェックポイント処理はシステムに負荷が掛かる。
© LPI-Japan all rights reserved.

23

②データアクセス（書き込み保証）(3/3)
nオンライン処理に影響を与えないように、そ〜っと実⾏する
チェックポイント
処理
開始

チェックポイント
処理
終了

開始

チェックポイント間隔(秒)(checkpoint_timeout)
この間にディスクへの書き出しが発⽣する#OSS-DB

チェックポイント
処理
終了

開始
1.0

0.5

チェックポイント間隔を1.0としてcheckpoint_completion_target (デフォルト 0.5)の割合
で完了するよう実⾏する注意）WALの量がmax_wal_sizeで設定された量に達すると、チェックポイント間隔で指定された時間を待たずに次のチェックポイント処理は開始される↑
I/O
ディスクI/Oを平準化できる

Time→© LPI-Japan all rights reserved.

24

②設定パラメータ（postgresql.conf）
n共有メモリ#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

shared_buffers(共有バッファ)

128MB

データベースサーバが共有バッファのために使⽤するメ
モリ量として、搭載メモリーの約25〜30％程度を指定する。

サイズを⼤きくし過ぎるとOS側で利⽤できるキャッシュメモリが減り、サーバ全体の性能が落ちる。

wal_buffers(WALバッファ)

shared_buffersの1/32

COMMIT前にWALバッファがあふれないようなサイズを指定する。そのためには、実際の業務のピーク時に発⽣するディスクI/Oの量を実測しながら調整する。
（Linuxの場合はiostatコマンドなどで確認）© LPI-Japan all rights reserved.

25

②設定パラメータ（postgresql.conf）
nWAL関連#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

wal_level

replica

WALに書かれる情報量を選択する。
(minimal < replica < logical)
replica:WALアーカイブやレプリケーション利⽤時
logical:WALをロジカルデコーディングをする場合

設定値によってWALの情報量が変化するため、バックアップ/リカバ
リ時のデータ量に影響する。

max_wal_size

1GB

チェックポイント処理中に発⽣するWALの最⼤サイズを指定する。WALのサイズがおおよそmax_wal_size/ (1+checkpoint_completion_target) に達するとチェッ
クポイントが実施される。
ログメッセージに「LOG: checkpoints are
occurring too frequently ・・・」が出⼒された場
合にはサイズを増やす。

サイズが不⾜すると、頻繁にチェッ
クポイント処理が動作する。また、サイズを⼤きくすると、クラッシュリカ
バリに必要な時間が⻑くなる。

checkpoint_timeout

5分

チェックポイント間隔を指定する。
(30分程度が推奨)

checkpoint_completi 0.5
on_target

通常のオンライン業務の負荷が⾼い場合は、⼤きくても0.9まで増やすことで、チェックポイントによる負荷を減らすことができる。

最⼤値1.0を指定すると、ある時点でチェックポイントが完了しなくなる場合がある。

© LPI-Japan all rights reserved.

26補⾜）クラッシュリカバリ
nWALを利⽤してリカバリする#OSS-DB トランザクション更新処理のWALは随時、WALファイルに書き出される
 更新処理Aの後にチェックポイント処理が⾏われた場合、更新処理Aの結果はデータファイルに反映済み
 障害発⽣後のリカバリは、PostgreSQLを再起動することで、未適⽤のWAL(B)(C)を検出し適⽤される

更新処理A

チェックポインタ(定期的に実⾏)

更新処理B

更新処理C

データファイル

更新処理D
障害

再起動

データファイル

WALファイル

WAL書き出しWAL(A)

WAL(B)WAL(C)© LPI-Japan all rights reserved.

27

WALの応⽤）WALを利⽤したバックアップ「PITR」(1/3)
nオンラインバックアップとPITR(Point In Time Recovery)のしくみ#OSS-DB

 運⽤時︓定期的にベースバックアップとアーカイブログを取得しておく 障害発⽣後︓ベースバックアップ、アーカイブログ、WALから最新の状態まで復旧する$PGDATA
データ
ファイル

更新処理

更新処理

更新処理

更新処理

更新処理

WAL

WAL

WAL

WAL

WAL

定期的な
バックアップ
別ディスク(disk1)

データ ベース
ファイル バックアップ

WALを
アーカイブ

障害
更新処理

未アーカイブWAL

データ
ファイル

再起動

pg_wal
最新状態に
リカバリ

障害の状況によっては消失する可能性あり

アーカイブ
ログ

© LPI-Japan all rights reserved.

28

WALの応⽤）WALを利⽤したバックアップ「PITR」(2/3)
nオンラインバックアップ（運⽤中）#OSS-DB WALアーカイブを取得するための設定(postgresql.conf)
WALアーカイブ⽤の

wal_level = replica
フォルダを準備しておく
archive_mode = onarchive_command = 'cp "%p" "/disk1/archivedir/%f"' 定期的なベースバックアップの実⾏

1. pg_start_backup(ʻラベル名ʼ)関数の実⾏
2. データベースクラスタをバックアップ

PostgreSQLは停⽌しなくてよい
スーパーユーザで実施する# cd /usr/local/pgsql# tar czvfp /disk1/backup/backup.tar.gz data

OSのコマンドでよい

3. pg_stop_backup()関数を実⾏
1.〜3.は、pg_basebackupに置き換えることも可能（1.と3.の処理は、 pg_basebackup内部に含まれているため実施不要）$ pg_basebackup –D /disk1/backup –X s –P –h ホスト名 –U ユーザ名
© LPI-Japan all rights reserved.

29

WALの応⽤）WALを利⽤したバックアップ「PITR」(3/3)
nPITRによるリカバリ（障害発⽣後の復旧）
1. PostgreSQLが停⽌していることを確認$ pg_ctl status

2. データベースクラスタから未アーカイブのWALを退避# cp –rp /usr/local/pgsql/data/pg_wal /disk1# rm –rf /usr/local/pgsql/data
クラスタは削除しておく

3. ベースバックアップのリストア# cd /usr/local/pgsql# tar xzvfp /disk1/backup/backup.tar.gz

OSのコマンドでよい# rm –rf /usr/local/pgsql/data/pg_wal# cp –rp /disk1/pg_wal /usr/local/pgsql/data$ vi /usr/local/pgsql/data/recovery.conf

データベースクラスタ

2.退避

データ
ファイル pg_wal

pg_wal3.リストア

4.復元

データベースクラスタ

4. 未アーカイブのWALファイルを復元
5. recovery.confの作成#OSS-DB$PGDATA

データ
ファイル pg_wal
ベースバックアップしたもの

WALアーカイブ⽤のフォルダ# 以下の⾏を追加
restore_command = 'cp "/disk1/archivedir/%f" "%p"’# リカバリポイントも指定できる#recovery_target_time = '2021/06/22 00:00:00'

6. PostgreSQLを起動し、recovery.confの拡張⼦がdoneになったことを確認$ pg_ctl start$ ls /usr/local/pgsql/data/recovery.*/usr/local/pgsql/recovery.done© LPI-Japan all rights reserved.

30

WALの応⽤）設定パラメータ（postgresql.conf）
nPITR関連(WALアーカイブの設定)
パラメータ

デフォルト値

設定⽅法

wal_level

replica

WALアーカイブを利⽤する場合は、replicaか
logicalを指定する。

archive_mode

off

onかalwaysに設定することでWALがアーカイブされるようにする。

archive_command''

アーカイブスクリプトを設定する。
例︓(%p:アーカイブ先フォルダ、%f:ファイル名)ʻcp “%p” “/disk1/archivedir/%f”'#OSS-DB
注意

アーカイブ先は、システムの信頼性を⾼めるために、別ディスクを指定することが望ましい© LPI-Japan all rights reserved.

31

PITR（バックアップ⽅式）に関する例題
nポイントインタイムリカバリのためのバックアップ⼿順
として、間違っているものを1つ選びなさい。

#OSS-DBA) データベースに管理者ユーザで接続し、pg_start_backup を実⾏する。
B) データベースを停⽌する。
C) データベースクラスタのディレクトリ全体を、tar などのOSコマンドを利⽤してコピーする。
D) データベースに管理者ユーザで接続し、pg_stop_backup を実⾏する。
E) 上記の⼀連の処理の代わりに、OSのコマンドラインから pg_basebackup
コマンドを実⾏する。

※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_06/102_200312© LPI-Japan all rights reserved.

32

③VACUUMとANALYZEのしくみ (1/4)
nPostgreSQLは追記型アーキテクチャを採⽤
テーブルデータ
レコード1
レコード2
レコード3
レコード4
レコード5

#OSS-DB

テーブルデータ
レコード1
レコード2
レコード3
レコード4
レコード5
レコード6

DELETEUPDATE

空きスペース

削除マーク
削除マーク

空きスペース

不要領域（再利⽤不可）挿⼊だけでなく、更新でもレコードが追加される「不要領域」は、データファイルのサイズを増⼤させ、ディスク領域の圧迫とI/O増加による性能低下につながる。
この「不要領域」を回収する⼿段がVACUUM。

nトランザクションの同時実⾏制御⽅式（MVCC）を実装※MVCC:多版型同時実⾏制御 (Multi Version Concurrency Controlの略)

テーブルデータ

Aさん

SELECT
100

読み込み
ロック

レコード1
削除マーク
レコード2
レコード3
削除マーク
レコード4
レコード5
レコード6

UPDATE
100→200

空きスペース書き込み
ロック

COMMIT

Bさん

Aさんが「読み込みロック」を獲得してレコード4を参照中、Bさんは「書き込みロック」を獲得して、同じデータを更新することが可能になる。
ロック競合の観点でメリットになる。

© LPI-Japan all rights reserved.

33

③VACUUMとANALYZEのしくみ (2/4)
nVACUUMで「不要領域」を再利⽤可能にする（2種類の⽅法）#OSS-DB VACUUM テーブル名;- 同時に実⾏される殆どのSQLとは、テーブルへのロックが競合しない。
- 運⽤中に実⾏できる。

 VACUUM FULL テーブル名;- 不要領域を物理的にディスクから削除。
- 排他的ロックが必要なため、保守作業中に実⾏する必要がある。

※テーブル名を省略するとすべてのテーブルが対象。
※VACUUMは、指定したテーブルに指定されているインデックスも処理対象にする。
テーブルデータ
レコード1
レコード2
レコード3
レコード4
レコード5
レコード6
空きスペース

削除マーク
削除マーク

VACUUM処理

VACUUM FULL処理

レコード1
空きスペースレコード3
空きスペース
レコード5
レコード6

レコード1
レコード3
レコード5
レコード6

空きスペース

空きスペースとして、再利⽤を可能にする

新しいファイル
で作り直す
空きスペース© LPI-Japan all rights reserved.

34

③VACUUMとANALYZEのしくみ (3/4)
nANALYZEで『統計情報』を更新する#OSS-DB PostgreSQLは、クライアントからSQLを受け取ると、テーブルにどのようなデータが格納されれているかを⽰す『統計情報』に基づいて、最も効率の良い処理⽅式と処理順序を組み⽴てて実⾏する。この統計情報がテーブルの実態と合っていないとSQLが効率的に実⾏できない。
 『統計情報』を更新するには「ANALYZE テーブル名;」を実⾏する。
※テーブル名を省略するとすべてのテーブルが対象。

バックエンドプロセス

SQL⽂

プランナ

エグゼキュータ

実⾏

実⾏計画
設定ファイル

処理コストの低い
計画を作成

各種コスト情報(*1)

統計情報
システムテーブル
pg_statisticなど(テーブルの中⾝)

⾏数
NULL値の割合
カラムデータのばらつきなど

ANALYZE処理
サンプリングで⾼速に収集
テーブルデータ(*1)各種コスト情報

seq_page_cost = 1.0
random_page_cost = 4.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
・・・© LPI-Japan all rights reserved.

35

③VACUUMとANALYZEのしくみ (4/4)
nVACUUMとANALYZEをまとめて実⾏する⽅法#OSS-DB VACUUM ANALYZE テーブル名; ※テーブル名を省略するとすべてのテーブルが対象。
※VACUUMを実⾏すると、ほぼ全テーブルをスキャンするため、
ANALYZEも⼀緒に実⾏した⽅が効率がよい。

nautovacuum(⾃動バキューム)について
 VACUUMを定期的に⾃動で実⾏する機能（⾃動メンテナンス機能）。
 デフォルトで有効に設定されている。
- 設定パラメータautovacuum = on(デフォルト) テーブル毎に、更新/削除された⾏数が、指定された”しきい値”を超えた場合に実⾏される。
 VACUUMを実⾏後、ANALYZEも⼀緒に実⾏される。
 実際の運⽤において、autovacuumが定期的に実⾏されていれば、不要領域や実⾏計画の問題は発⽣しにくい。
⼤量データの挿⼊／削除の直後や、⾼頻度のデータ更新時は、
統計情報の更新が追いつかないことがあるため、⼿動でのANALYZEを併⽤することも検討が必要。
© LPI-Japan all rights reserved.

36

③設定パラメータ（postgresql.conf）
nVACUUMとANALYZE#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

autovacuum

有効(on)

onを指定すると、autovacuumが実⾏されるようになる。

track_countsパラメータも有効(on)になっている必要がある。

autovacuum_naptime

1分

autovacuumの実⾏チェックが⾏われる間隔を変更する場合に指定する。

テーブルの統計情報の変化に応じて設定値を検討する。

autovacuum_vacuum_threshold

50レコードautovacuum_vacuum_scale_factor

0.2

以下の計算値（しきい値）を超えたテーブルで
VACUUMが実⾏される。
［ autovacuum_vacuum_threshold ＋
autovacuum_vacuum_scale_factor × レコー
ド数 ］

更新が少ないテーブルやレコード数が⾮常に⼤きいテーブ
ルは、なかなかVACUUMの対象にならない。（→不要領域の回収が遅れる）

autovacuum_analyze_threshold

50レコードautovacuum_analyze_scale_factor

0.1

以下の計算値（しきい値）を超えたテーブルで
ANALYZEが実⾏される。
［ autovacuum_analyze_threshold ＋
autovacuum_analyze_scale_factor × レコー
ド数］

更新が少ないテーブルやレコード数が⾮常に⼤きいテーブルは、は、なかなかANALYZEの対象にならない。（→統計情報の更新頻度が低くなる）© LPI-Japan all rights reserved.

37

VACUUMとANALYZEに関する例題
nバキューム（VACUUM）と⾃動バキュームの違いの説明として適切なものを3つ選びなさい。

#OSS-DBA) VACUUMはオプションで指定しなければ不要領域の回収のみを実⾏するのに対し、
⾃動バキュームは不要領域の回収と解析（ANALYZE）の両⽅を実⾏する。
B) VACUUMは対象とするテーブルのリストを指定する必要があるが、⾃動バキュームは
挿⼊・更新・削除されたデータが⼀定量以上のテーブルが⾃動的に対象となる。
C) VACUUMは⼀時テーブル（temporary table）を対象とできるが、⾃動バキュームでは⼀時テーブルは対象外である。
D) VACUUMはオプションを指定することでファイルサイズを縮⼩することができるが、⾃動バキュームには同等の機能がない。
E) VACUUMはOSの機能などを使って定期的に⾃動実⾏するようにできる。
⾃動バキュームはPostgreSQLサーバの稼働中で、トランザクションの実⾏量が少ないときに⾃動的に実⾏されるので、OSの機能の設定は不要である。

※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_06/109_201029© LPI-Japan all rights reserved.

38

VACUUMとANALYZEに関する例題
nVACUUM と VACUUM FULLの違いの説明として適切なものを2つ選びなさい。

#OSS-DBA) VACUUM FULLはデータベース内の全テーブルが対象になるが、VACUUMは指定した
特定のテーブルのみを対象とする。
B) VACUUM FULLはテーブルのANALYZEも実⾏するが、VACUUMはANALYZEを実⾏しない。
C) VACUUM FULLは処理対象のテーブルの排他的ロックを取得するため、通常のテーブルの読み書きの処理と競合が発⽣する。VACUUMは排他的ロックを取得しない。
D) VACUUM FULLを実⾏すると通常はテーブルを構成するファイルのサイズが⼩さくなるが、
VACUUMを実⾏しても通常はテーブルを構成するファイルのサイズは変わらない。
E) OSのコマンドラインから vacuumdb コマンドを発⾏することで VACUUM を実⾏できるが、
VACUUM FULL はコマンドラインからは実⾏できない。
※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_06/108_200902© LPI-Japan all rights reserved.

39

VACUUMとANALYZEに関する例題
nANALYZEについて述べたものとして、適切なものを2つ選びなさい。

#OSS-DBA) テーブルがアクセスされる頻度、更新される頻度といった統計情報を取得する。
B) デフォルトではテーブル全体を解析するため、巨⼤なテーブルのANALYZEには時間がかかる。
C) 実⾏時のパラメータにより、データベース全体、データベース内の特定のテーブルのみ、
特定のテーブルの特定の列のみ、などANALYZEの対象を制御できる。
D) 実⾏時のパラメータにより、統計情報の取得の⽬標値を変更できるので、これにより、
統計情報の正確さやANALYZEに要する時間を制御できる。
E) ⾃動バキュームの実⾏時に⾃動的に実⾏される。
※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_04/66_160808© LPI-Japan all rights reserved.

40

④ロガーのしくみnロガーとは︖#OSS-DB

 サーバログをstderr、syslog、csvlog等へ送る機構。

n設定⽅針※ stderr: 標準エラー出⼒、csvlog: CSV形式での標準エラー出⼒

 デフォルト設定のままだと問題の検出や調査に必要な情報がログ出⼒されないため、ログファイルに出⼒する必要がある
 ログ⾏の先頭に各種プレフィックスに付加すると、障害時の調査等に役⽴つ ログファイルはローテーションさせる① log_destination = 'syslog, stderr'postgresql.confへの設定例① サーバログをsyslogとログファイルへ出⼒② ログファイルは$PGDATA/pg_log/postgres-Mon.logのような名前で、毎⽇のログを1週間でローテーションする③ プレフィックスに、タイムスタンプ、ユーザ名、
データベース名、プロセスID、ログ⾏番号を出⼒④ 5秒以上かかったSQL⽂は記録⑤ ワーニング以上のエラーはログファイルへ出⼒し、
クライアント側にも伝える② logging_collector = on
② log_directory = 'pg_log'② log_filename = 'postgres-%a.log'② log_truncate_on_rotation = on
② log_rotation_age = 1440
③ log_line_prefix = '[%t] %u %d %p [%l] '④ log_min_duration_statement = 5000
⑤ log_min_messages = warning⑤ log_min_error_statement = warning⑤ client_min_messages = log© LPI-Japan all rights reserved.

41

④設定パラメータ（postgresql.conf）
nサーバログの出⼒設定(サーバログ系)#OSS-DB

パラメータ

デフォルト値

設定⽅法

log_destination'stderr'

サーバログの出⼒先を選択する。ʼstderrʼ、
csvlogの場合、
ʻsyslogʼ、 ʼcsvlogʼなどから、カンマ区切りで複数指定が logging_collect可能。
orがonであること。

logging_collector

off

onにすると、標準エラー出⼒へ送られたサーバログをファイ
ルにリダイレクト(書き出す)。併せて、log_directory、
log_filenameも設定する。

log_directory'log'

ログファイルを格納するフォルダを、絶対パス、あるいは、$PGDATAからの相対パスで指定する。

log_filename'postgresql-%Y-%m%d_%H%M%S.log'

ログファイル名を指定する。(%Y→年、%m→
⽉、%d→⽇、%H→時、%M→分、%S→秒に変換)

client_min_messages

noticelog_min_messag
eswarning

どのメッセージ階層をクライアントに送るかを指定する。
設定値は、それぞ有効な値は、debug5、 debug4、debug3、
れ後⽅の階層も含
debug2、 debug1、log、notice、 warning、error、 む。(例えば、fatal
fatal、panic。
を指定するとpanic
どのメッセージ階層をサーバログに書き込むかを指定する。 も含む)
有効な値は、debug5、debug4、 debug3、debug2、debug1、 info、notice、warning、
error、log、fatal、panic。

注意

logging_collectorがon(サーバログ
をファイルへ出⼒)のときに指定する。

logの優先度が双⽅で異なる。
© LPI-Japan all rights reserved.

42

④設定パラメータ（postgresql.conf）
nサーバログの出⼒設定(サーバログ系 のつづき)#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

log_min_error_st
atement

error

エラーの原因となったSQL⽂をサーバログに記録する。
有効な値は、debug5、debug4、 debug3、debug2、debug1、 info、notice、warning、 error、log、fatal、
panic。

設定値は、それぞれ後⽅の階層も含む。(例えば、fatalを指定するとpanicも含む)

log_line_prefix'%m [%p] 'ログ⾏の先頭に出⼒するprintfの書式⽂字列を指定する。
(%m:ミリ秒付きタイムスタンプ, %p:プロセスID)

障害解析、性能解析に必要な情報が出⼒されるようにしておく。

log_truncate_on_ offrotation

ログをローテーションする場合には、onを指定する。併せて、
log_filename、log_rotation_ageも設定する。
例)毎⽇のログを1週間(7⽇)分保持する場合
log_truncate_on_rotation = on
log_filename = ʻpostgres-%a.logʼ
log_rotation_age = 1440

logging_collectorが有効になっている必要がある。

log_rotation_age

ログファイルの最⼤寿命を指定します。指定した時間(分単位)を経過すると、新しいログファイルが⽣成されます。

logging_collectorが有効になっている必要がある。

1d© LPI-Japan all rights reserved.

43

④設定パラメータ（postgresql.conf）
nサーバログの出⼒設定(性能監視系)#OSS-DB

パラメータ

デフォルト値

設定⽅法

注意

log_statement

none

どのSQL⽂をログに記録するかを指定する。指定できる値は以下のとおり。
none(off) / ddl(CREATE/ALTER/DROP) /mod(ddl+INSERT/UPDATE/DELETE/TRUNCATE/
COPY FROM) / all(全てのメッセージ)

より広い範囲のログを記録すると、ログの量が⼤きくなりシステムに負荷がかかる。

log_min_duration -1(記録無効)_statement

指定したミリ秒数以上かかったSQL⽂について、それぞれの⽂の実⾏に要した時間をログに記録する。
0を指定すると、すべてのSQL⽂が対象になる。

© LPI-Japan all rights reserved.

44

ロガーに関する例題
npostgresql.conf で設定するパラメータについて、正しい説明を2つ選びなさい。

#OSS-DBA) log_destination には、ログ出⼒先となるファイル名を指定する。
B) logging_collector を on にすることで、標準エラー出⼒に送られたログメッセージを
ログファイルにリダイレクトできる。
C) log_connections を on にすることで、クライアントからサーバへの接続試⾏がログに出⼒される。
D) log_statement を on にすることで、SQL ⽂をログに記録できる。
E) log_line_prefix を on にすることで、ログ情報の出⼒時刻、ユーザ名、
プロセスIDなど様々な付加情報がログファイルに出⼒される。
※この例題は実際のOSS-DB技術者認定試験とは異なります

・OSS-DB公式サイトの「Silverの例題解説」より引⽤
https://oss-db.jp/sample/silver_management_01/19_120815709© LPI-Japan all rights reserved.

45

まとめ
以下の内容について述べた。

#OSS-DB

nPostgreSQLの基本構成
 PostgreSQLを利⽤するまでの４ステップ
インストール 〜 クラスタ作成 〜 PostgreSQLサーバ起動 〜 接続と利⽤

n運⽤時に意識すべきPostgreSQLのしくみ＋設定パラメータの設定⽅法
 接続と認証 データアクセス VACUUM(バキューム)とANALYZE(アナライズ)
 ロガー© LPI-Japan all rights reserved.

46