Pgpool-IIではじめる
PostgreSQLのクラスタ運用

SRA OSS, Inc. 日本支社
取締役支社長
石井達夫

自己紹介
●

SRA OSS, Inc. 日本支社という会社で、OSSの開発とビジネスに携わっています
PostgreSQLのリリース以来開発に関与し、現在はコミッタの一人●
PostgreSQL用のクラス
タソフト Pgpool-II の最初のバージョンを開発、今も開発リーダを務める●

2

Copyright(c) 2018 SRA OSS, Inc. Japan

PostgreSQLのクラスタ機能のロードマップ
2010

2011

2016

20xx

PostgreSQL 9.0
リリース、
ストリーミング
レプリケーション実装

PostgreSQL 9.1
リリース、同期
レプリケーション

PostgreSQL 10
リリース、
ロジカル
レプリケーション

PostgreSQL 1x
リリース、
マルチマスタレプリケーションシャーディング

3

Copyright(c) 2018 SRA OSS, Inc. Japan

クラスタ構成によるメリット●

可用性の向上サーバダウン時の復旧時間の短縮●できるだけ直近のデータまで復旧したい●●

同時アクセス時の検索性能の向上
複数の「レプリカ」で検索処理を分担して性能向上●
更新処理は性能向上できない●–

別の技術が必要(PostgreSQLではまだ正式サポートされていない)

4

Copyright(c) 2018 SRA OSS, Inc. Japan

クラスタ構成による耐障害性の向上
障害発生！
●

バックアップ取得

復元●●

復元中は運用停止
復元に要する時間はデータ量に比例する（ときには数日以上かかることも）バックアップ時点までしか戻れない障害発生！
リアルタイム
コピー取得●●

プライマリサーバ

新プライマリサーバ●

リアルタイム
コピー取得

5

Copyright(c) 2018 SRA OSS, Inc. Japan

障害発生時にプライマリサーバを切り替え（フェイルオーバ）
データ量が大きくてもサーバ切り替え時間は変わらない（数分以内）
ほぼ直前のコミットまで復元可能

クラスタ構成による検索性能向上
同時アクセス殺到！

高負荷状態
発生！

負荷を2台のサーバで分かち合うことで、個々のサーバの負荷軽減●●

リアルタイム
コピー取得

6

Copyright(c) 2018 SRA OSS, Inc. Japan

検索負荷を2台のサーバで分かち合うことで、個々のサーバの負荷軽減
更新性能は向上しないPostgreSQLにおけるレプリケーション技術(1)●

ストリーミングレプリケーション
広く使われ、実績もある(PostgreSQL 9.0以降で利用可能)●「物理」レプリケーションの一種●
トランザクションログレコードをリアルタイムで一個の主サーバ（プライマリ）から複数の従サーバ（スタンバイ）に転送、スタンバイはログを再生（リプレイ）してデータをコピーする●
データ更新はプライマリでのみ可能。スタンバイはリードオンリー●
データベースクラスタのほぼすべてのデータをレプリケーションできるが、特定のデータベースやテーブルだけをコピー（あるいはコピーしない）ということはできない●スタンバイはプライマリに昇格できるので、これを利用してプライマリ故障に対応する●
異なるメジャーバージョンのPostgreSQL間ではレプリケーションできない●
本日はこちらを前提にお話します●7

Copyright(c) 2018 SRA OSS, Inc. Japan

PostgreSQLにおけるレプリケーション技術(2)●ロジカルレプリケーション(PostgreSQL 10以降)
歴史が浅く、まだあまり実績はない●「論理」レプリケーションの一種●
DB操作の論理情報を含むトランザクションログレコードをリアルタイムで一個のパブリッシャから複数のサブスクライバに転送、サブスクライバはログを再生（リプレイ）してデータをコピーする●
データ更新はパブリッシャでもサブスクライバでも可能。ただし、更新の衝突はユーザが自己責任で解消する●指定したテーブルのみコピーできる●
一部レプリケーションできない操作がある(たとえば DDL)●
異なるメジャーバージョンのPostgreSQL間でもレプリケーションできる●–

当然のことながら、PostgreSQL 10以降のメジャーバージョンに限る

8

Copyright(c) 2018 SRA OSS, Inc. Japan

ストリーミングレプリケーションの仕組み
更新要求とコミット

非同期レプリケーションの場合のコミット完了報告

スタンバイサーバ
共有バッファ
変更

synchronous_commit =remote_apply

プライマリ
サーバ

テーブルインデックスなど

コミット
レコード
(メモリ)

リプレイ
同期レプリケーションの場合のコミット完了報告

WAL
レコード
(ディスク)

synchronous_commit =remote_write
synchronous_commit =on

ネットワーク上を転送

WAL
レコード
(メモリ)

9

Copyright(c) 2018 SRA OSS, Inc. Japan

WALレコード(ディスク)

WAL
レコード
(メモリ)

ストリーミングレプリケーション
クラスタ管理の難しさ(1)●

障害発生後のサーバ昇格は手動で実施しなければならない

障害発生！

●

プライマリサーバが切り替わったら、スタンバイを新プライマリに手動で再同期する必要がある

障害発生！
スタンバイ
再同期再同期
スタンバイ
新プライマリサーバ

新プライマリサーバ

10

Copyright(c) 2018 SRA OSS, Inc. Japan

ストリーミングレプリケーション
クラスタ管理の難しさ(2)スタンバイサーバには更新クエリを送ることができないので、アプリケーションの変更が必要かもしれない。その他にもスタンバイが受け付けないクエリがある●
クエリはすべてプライマリに投げる、という解決方法もあるが、検索負荷分散のメリットが失われる●

一時テーブル●強いロック●
2相コミット●シーケンス操作●
LISTEN/NOTIFY●
明示的な更新トランザクション宣言●

更新または参照クエリ

参照クエリ

●

プライマリ

SET TRANSACTION READ WRITEなど

スタンバイ

11

Copyright(c) 2018 SRA OSS, Inc. Japan

ストリーミングレプリケーション
クラスタ管理の難しさ(3)プライマリで発生した更新がスタンバイに届くまでに予想外の遅延(レプリケーション遅延)が発生することがある。たとえばマスターデータを更新したあと、直後に別セッションでスタンバイを参照すると、まだその更新を読み出せないことがある●
原因は、ネットワーク帯域幅不足、スタンバイサーバのスペック不足など
●

更新クエリ

参照クエリ

レプリケーション
遅延
スタンバイ

プライマリ

12

Copyright(c) 2018 SRA OSS, Inc. Japan

Pgpool-IIを使って問題を解決●

Pgpool-IIを使って解決ないし問題を軽減できるもの●

スタンバイに投げても良いクエリは？
–●レプリケーション遅延への対応–●

レプリケーション遅延を随時モニター。遅延が大きい場合はクエリをスタンバイに投げない

手動フェイルオーバ問題–●

SQLパーサを内蔵し、クエリの種類を細かく識別してスタンバイに投げるクエリを自動決定

バックエンドを監視して、ダウン時にフェイルオーバスクリプトを起動

新プライマリへの同期追随問題–

フェイルオーバ後にスタンバイを同期するスクリプトを定義できる

13

Copyright(c) 2018 SRA OSS, Inc. Japan

Pgpool-IIとはクライアントと(複数の)PostgreSQLの間に入るプロキシサーバ●
PostgreSQLをできるだけ透過的に単一サーバに見せる●
クラスタ管理の一部を自動化●
OSSとして配布●
PostgreSQLと同様、年に一度メジャーバージョンアップ
●現在Pgpool-II 4.0を開発中(実は本日beta1をリリース予定)●
詳細は https://www.pgpool/net をご覧ください。
●

プライマリ

Pgpool-II
14

Copyright(c) 2018 SRA OSS, Inc. Japanスタンバイ

スタンバイに送信しても良いクエリの判断●

設定ファイルで各PostgreSQLに送る検索クエリの比率を設定できる●

通常は、各サーバ同一比率
backend_weight0 = 1.0– backend_weight1 = 1.0– Backend_weight2 = 1.0–

Pgpool-IIに接続したセッションで、上記比率に基づいて乱数で検索クエリを送信するPostgreSQLを決定（「ロードバランスノード」と呼ぶ)●
以後、可能であればセッション終了まで検索クエリをロードバランスノードに投げる●

15

Copyright(c) 2018 SRA OSS, Inc. Japanスタンバイに送信しても良いクエリの判断
SQLパーサで解析、判断●
パーサだけで判断できないものはシステムカタログに問い合わせ●●●

毎回問い合わせていると遅くなるので、キャッシュを内蔵

注意を要する問い合わせ
一時テーブル●強いロック●
2相コミット●シーケンス操作●
LISTEN/NOTIFY●
明示的な更新トランザクション宣言●
SET TRANSACTION READ WRITEなど●

16

Copyright(c) 2018 SRA OSS, Inc. Japan検索負荷分散のオプション調整パラメータ(1)●black_function_list, white_function_list●

副作用のある関数(内部で更新を実施する関数)を呼び出す
SELECTを登録できる–●

例: nextval()

database_redirect_preference_list●

特定のデータベースに対して、検索クエリの送信先を指定できる
postgres:primary(0.3),mydb[0-4]:standby(0.7)– （）の中に重みの比率を書けるのは、4.0の新機能–●

app_redirect_preference_list●

特定のアプリケーション名に対して、同様のことが可能

17

Copyright(c) 2018 SRA OSS, Inc. Japan検索負荷分散のオプション調整パラメータ(2)●black_query_pattern_list●

常にプライマリに送信するクエリを登録できる（正規表現利用可能)–●●

特定のテーブルに対するSELECTは常にプライマリに送信したい場合などに利用

Pgpool-II 4.0の新機能

allow_sql_comments●

onの場合、SQLコメントをSQLの先頭に書くと検索クエリがプライマリに送られる–/*NO LOAD BALANCE*/SELECT
1;
BALANCE*/18

Copyright(c) 2018 SRA OSS, Inc. Japan検索負荷分散のオプション調整パラメータ(3)●disable_load_balance_on_write
更新クエリが表れたときの挙動を指定できる●
そのセッション内で行われた更新を、続く検索クエリでできるだけ最新データを参照したい場
合に利用●
アプリケーションをできるだけ修正せずにクラスタ環境に移行したいときに利用
●

–off
●

–transaction●–

明示的なトランザクション内で更新クエリが表れたら、トランザクション終了まで検索クエリをプライマリに送信

trans_transaction●–

この機能を使わない“transaction”と同様だが、そのトランザクションが終了して、次のトランザクションが開始しても検索クエリをプライマリに送信し続ける

always●

更新クエリが表れたら、以後セッション終了まで検索クエリをプライマリに送信

offでは検索負荷分散が最大の効果を発揮するが、旧来アプリケーションとの互換性は最小、alwaysは検索負荷分散の効果が最小だが、旧来アプリケーションとの互換性は最大●
Pgpool-II 4.0の新機能●

19

Copyright(c) 2018 SRA OSS, Inc. Japan

レプリケーション遅延への対応●

sr_check_period●●

delay_threshold●●

レプリケーション遅延を測定する時間間隔を秒単位で指定
レプリケーション遅延がここで指定したバイト数より大きければ、そのスタンバイをロードバランスノードに選ばないレプリケーション遅延の検知はリアルタイムではないことに注意！

20

Copyright(c) 2018 SRA OSS, Inc. Japan手動フェイルオーバ問題の解決●

Pgpool-IIが定期的にPostgreSQLの状態をチェックし、問題があればユーザ定義のフェイルオーバスクリプトを起動することによって、自動フェイルオーバを実現できる
フェイルオーバスクリプトの例（pgpool_setupが生成したスクリプトの一部)#! /bin/sh# Execute command by failover.
# special values: %d = node id#%h = host name#%p = port number#%D = database cluster path#%m = new master node id#%M = old master node id#%H = new master node host name#%P = old primary node id#%R = new master database cluster path#%r = new master port number#%% = '%' character[中略]if [ a"$failed_node_id" = a"$old_primary_node_id" ];then# master failed!
new_primary_db_cluster=${mydir}/data"$new_master_id"
echo $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over$pg_ctl -D $new_primary_db_cluster promote >>$log# let standby take oversleep 2fi21

Copyright(c) 2018 SRA OSS, Inc. Japan

新プライマリへの同期追随問題●

フェイルオーバ完了後、新プライマリへの同期追随を行うスク
リプトを “follow_master_command” でスクリプトを設定できる
Follow_master_commandの例（pgpool_setupが生成したスクリプトの一部)#! /bin/sh# Execute command by failover.
# special values: %d = node id#%h = host name#%p = port number#%D = database cluster path#%m = new master node id#%M = old master node id#%H = new master node host name#%P = old primary node id#%R = new master database cluster path#%r = new master port number#%% = '%' character[中略]# Stop standby node if it's running
if $pg_ctl -D $failed_db_cluster status|grep "is running" >/dev/null 2>&1
then$pg_ctl -w -m f -D $failed_db_cluster stop >> $log 2>&1
sleep 10# recovery the node
pcp_recovery_node -w -h localhost -p $PCP_PORT -n $failed_node_id >> $log 2>&1
elseecho "$failed_db_cluster is not running. skipping follow master command." >> $log
fi

22

Copyright(c) 2018 SRA OSS, Inc. Japanデモタイム●シナリオ
プライマリサーバをシャットダウン●
Pgpool-IIがそれを検知し、スタンバイ1を昇格、新プライマリにする●
続いてフォローマスターコマンドが起動され、スタンバイ2が新プライマリ（旧スタンバイ1)に同期、追随する●

23

Copyright(c) 2018 SRA OSS, Inc. Japan

Pgpool-II付属ツールを使って実験環境構築(1)

プライマリ
Port=11002

Pgpool-II
Port = 11000
実際に構築するのはプライマリ１台にスタンバイ2台が接続する構成
24

Copyright(c) 2018 SRA OSS, Inc. Japan

スタンバイ1
Port=11003

スタンバイ2
Port=11004

Pgpool-II付属ツールを使って実験環境構築(2)$ pgpool_setup -n 3
PostgreSQL major version: 10
Starting set up in streaming replication mode
creating startall and shutdownall[中略]
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
node_id | hostname | port | status | lb_weight | role
| select_cnt | load_balance_node | replication_delay | last_status_change---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------0| /tmp| 11002 | up
| 0.333333 | primary | 0| true
| 0| 2018-09-18 11:18:081| /tmp
| 11003 | down
| 0.333333 | standby | 0| false| 0| 1970-01-01 09:00:00
2| /tmp| 11004 | down
| 0.333333 | standby | 0| false| 0| 1970-01-01 09:00:00(3 rows)
recovery node 1...pcp_recovery_node -- Command Successful done.
recovery node 2...pcp_recovery_node -- Command Successful done.
creating follow master script
node_id | hostname | port | status | lb_weight | role
| select_cnt | load_balance_node | replication_delay | last_status_change---------+----------+-------+---------+-----------+---------+------------+-------------------+-------------------+--------------------0| /tmp
| 11002 | up| 0.333333 | primary | 0| false| 0| 2018-09-18 11:18:08
1| /tmp| 11003 | up
| 0.333333 | standby | 0| true
| 0| 2018-09-18 11:18:13
2| /tmp| 11004 | waiting | 0.333333 | standby | 0| false| 0| 2018-09-18 11:18:22(3 rows)
shutdown all
pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/testdb/startall.
To shutdown the whole system, use /home/t-ishii/testdb/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:#1 port is 11002#2 port is 11003#3 port is 11004
pgpool port is 11000
pcp port is 11001
The info above is in README.port.

25

Copyright(c) 2018 SRA OSS, Inc. Japan

まとめPostgreSQLのストリーミングレプリケーションを使ったクラスタ構
成の概要と問題点を説明しました●
そして、Pgpool-IIによる問題の解決方法の提案を行いました●
Pgpool-IIは非常に多機能なソフトで、今回ご説明できなかった機能が他にもたくさんあります●●●

たとえば、Pgpool-IIを単一障害点にしないための「watchdog」、オンメモ
リクエリ結果キャッシュなど更なる情報
Pgpool-IIのウェブサイトには、ほぼプロダクションで使えるレベルの構成例が載っています●SRA OSSでは、Pgpool-IIでクラスタを組むハンズオントレーニングも実施しています。ぜひご活用ください●

26

Copyright(c) 2018 SRA OSS, Inc. Japan宣伝●

来週の火曜日(9/25)に、「Pgpool-II Day」を開催しますPgpool-II生誕15周年を記念して●
Pgpool-II開発者（海外も含む)による技術講演●
間もなくリリース予定のPgpool-II 4.0の説明●●

SRA OSS, Inc.日本支社のページ → 「イベント、セミナー一覧」 → 「Pgpool-II 4.0 Anniversary」●●

https://www.sraoss.co.jp/event_seminar/2018/0925.
php

たまにしかやらないイベントなので、ぜひご参加ください！

27

Copyright(c) 2018 SRA OSS, Inc. JapanThank you!

28

Copyright(c) 2018 SRA OSS, Inc. Japan