Pgpool-IIを使ったPostgreSQLのレプリケーション・冗長化方法

2021年3月12日 最終更新日時 : 2022年3月30日 cvadmin

概要

PPostgreSQL、Pgpool-IIを使って冗長化する構築方法を記載します。

前提条件

PostgreSQL、Pgpool-IIが構築されていること

環境

OSRHEL7.5
RDBMSpostgresql-12.1-2
冗長化pgpool-II-pg12-4.1.4-1

構築の流れ

  1. 構成の検討
  2. PostregSQLの設定変更
  3. Pgpool-IIの設定変更
  4. サービス再起動

構築方法

[1] 構成の検討

要件

冗長化をする上で一番大事なのは構成を検討することです。
DBはどんなプロダクトを使うか？
いくつサーバを用意するか？
どうやって冗長化するか？（レプリケーション方法等）
などを考慮しなければいけません。
まずはそれらしい要件を考えてみたいと思います。

  • OSSを使ったデータベースにして欲しい
  • 障害時に何かあっても大丈夫なように冗長化して欲しい
  • まずは2台で作って後々増やす可能性もある

この要件に対して「PostgreSQL（OSS）とPgpool-II（OSS）を使った冗長化構成」を提案したとして受け入れられた、という体で行きます。
では、次に「レプリケーション方法」を考えていきます。

PostgreSQLのレプリケーション検討

レプリケーションには「ロジカルレプリケーション」と「ストリーミングレプリケーション」の2種類ありますが、ここでは冗長化や負荷分散目的でよく使われる「ストリーミングレプリケーション」について簡単に説明します。

  • ストリーミングレプリケーション
    全てのデータベースを対象に、データ変更時に生成されるトランザクションログ（WAL）をスタンバイサーバへ転送し、スタンバイサーバがそれを適用することによってデータの複製を実現します。

続いて方式について検討します。
「マスタ・スタンバイ方式」と「マルチマスタ方式」がありますが、今回は「マスタ・スタンバイ方式」を採用します。

  • マスタ・スタンバイ方式
    参照クエリはマスタ、スタンバイ両方に投げ、マスタのみに更新クエリを投げます。

    スタンバイサーバが2台以上ある場合はこんな構成にもできます。
    • マルチスタンバイ構成
      マスタがすべてのスタンバイにデータを複製する構成。
      上記で説明した内容とほぼ同じです。スタンバイが増えただけですね。
    • カスケード構成
      マスタが１つのスタンバイにデータを複製し、そのスタンバイが他の接続されているスタンバイに複製していく構成。
      マスタ⇒スタンバイ⇒スタンバイ…という形の構成です。

今回は2台しかないので「マスタ・スタンバイ方式」のマスタ1台とスタンバイ１台とします。
レプリケーションの構成が決まったので冗長化について検討していきます。

Pgpool-IIの冗長化検討

Pgpool-IIはPostgreSQLの足りないところを補ってくれたり、まとめて管理できるようにもしてくれます。
Pgpool-IIで冗長化するには検討すべきことが多くありますが、今回は「2台構成」であり「障害時何かあった際にも動く（片方のサーバが落ちても更新・参照クエリが投げられる）」ようにしなければならないため、「負荷分散」「Watchdog」「自動フェイルオーバー」「レプリケーション」をPgpool-IIで実施したいと思います。

  • 負荷分散
    Pgpool-II側で行なう参照クエリの負荷分散です。
  • Watchdog
    Pgpool-IIを複数立て、お互いに死活監視やサーバの状態について共有し合います。
    Pgpool-IIの監視はもちろんですが、PostgreSQLの監視も行ないます。
  • 自動フェイルオーバー
    マスタサーバが落ちていた場合、自動的に残ったスタンバイサーバをマスタとして昇格させる機能です。
    障害時にはとても便利な機能です。
    ただしWatchdogで監視してないと使えません。

このような設定を追加し、お互い監視し合いたいので2台それぞれのサーバにPgpool-IIも入れることにしましょう。

最終的な構成

構成をまとめてみます。

マスタサーバ

IPアドレス：X.X.0.10

  • PostgreSQL
  • Pgpool-II

スタンバイサーバ

IPアドレス：X.X.0.20

  • PostgreSQL
  • Pgpool-II
  • プロダクトの役割
  • PostgreSQL
    • ストリーミングレプリケーション
  • Pgpool-II
    • 負荷分散
    • Watchdog
    • 自動フェイルオーバー
    • レプリケーション

ざっくりとですが、こんな形に決まりました。
構成も決まったので実際の設定値について説明します。

[2] PostregSQLの設定変更

PostgreSQLの設定内容について記述します。
※デフォルト値や「基本的な構築方法」で説明した設定値については説明を省略しています。

  • 設定ファイルのバックアップ

PostregSQLではほとんどの設定ファイルがdataディレクトリに保管されています。
そのため、ディレクトリごとバックアップを取っておいた方が安全です。
なるべくバックアップを取るようにしてください。

# cd /var/lib/pgsql
# cp -pr data data.yyyymmdd
  • postgresql.confの設定

PostgreSQLの主な設定ファイルです。
こんな感じに設定してみました。

# vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
synchronous_commit = remote_apply
synchronous_standby_names = 'FIRST 1 (s1)'
cluster_name = 's1'
  • synchronous_commit設定

「WALがディスクに書き込まれたことを確認するかどうか」を指定します。
簡単に言うと「複製時、スタンバイはどのタイミングで参照クエリを受け付けるか」のタイミングです。
複製の確認を全く待たないと負荷分散時の参照クエリ時間は短くなりますが最新のデータを見ることができる保証がありません。
書き込まれたことを最後まで確認すると、最新のデータが保証されますが負荷分散のレスポンスは低下します。
どんな設定値にするかはやりたい事次第ですが、今回は「remote_apply」（完全同期：WALの適用が完全に終わったタイミング）としています。

  • synchronous_standby_names設定

「同期レプリケーションを行なうスタンバイサーバのリスト」を指定します。
指定方法が少々特殊で、継続的に同期するサーバの台数やどのスタンバイサーバを優先的に同期させるか等、細かく設定できます。
ですが今回スタンバイは1台しかいないので「FIRST 1 (s1)」とします。

  • cluster_name設定

「クラスターの名前」を指定します。
この名前は上記のsynchronous_standby_namesに関連し、上記設定の「s1」とはここで名付けた名前になります。
ですのでこの名前が「standby1」だった場合、synchronous_standby_namesでは「FIRST 1 (standby1)」とする必要があります。

  • pg_hba.confの設定

PostgreSQLのアクセス制限設定ファイルです。
こんな感じに設定してみました。

# vi /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32           md5
host    all             all             X.X.0.0/16            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     replicationuser             X.X.0.0/16            md5
host    replication     all             ::1/128                 md5
  • アクセスネットワーク設定

今回設定するIPアドレスのネットワーク範囲です。
「X.X.0.0/16」を設定してみました。
すべてのデータベース（all）と、レプリケーション用（replication）設定を追加しています。ちなみに「replicationuser」と言うのは筆者の方で作成したレプリケーション用のユーザ名です。
レプリケーション用のユーザは他のユーザと分けておくことを推奨します。
※ユーザは事前にデータベース上で新規作成する必要があります。

[3] Pgpool-IIの設定変更

Pgpool-IIの設定内容について記述します。
※デフォルト値や「基本的な構築方法」で説明した設定値については説明を省略しています。

  • pgpool.confの設定

Pgpool-IIの主な設定ファイルです。
こんな感じに設定してみました。かなり長いです。

# vi /etc/pgpool-II/pgpool.conf
listen_addresses = '*'
backend_hostname0 = 'X.X.0.10'
backend_hostname1 = 'X.X.0.20'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'testpasswd'
follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %m %H %M %P %r %R'
health_check_period = 30
health_check_timeout = 0
health_check_user = 'postgres'
health_check_password = 'testpasswd'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 15
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
use_watchdog = on
trusted_servers = 'X.X.0.254'
wd_hostname = 'X.X.0.20'
delegate_IP = 'X.X.0.100'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens192 label ens192:vip'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens192'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens192'
enable_consensus_with_half_votes = on
heartbeat_destination0 = 'X.X.0.10'
heartbeat_device0 = 'ens192'
other_pgpool_hostname0 = 'X.X.0.10'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
  • backend_項目名X設定

backend_項目名+数字は「接続監視に関連した各サーバの設定」を指定します。
「backend_hostname0」は「1台目（数字は0スタート）のバックエンドのホスト名」という項目になります。
今回の設定では「backend_hostname0」は「X.X.0.10」、「backend_hostname1」は「X.X.0.20」にしてみました。
「hostname」以外にも「port」や「data_directory」が出てきますが、ほぼすべて同じ設定値にして問題ありません。と言いますか、同じにしてください。
ただし、上記の「hostname」と「application_name」だけはIPアドレスと識別名ですので、別にしてください。
※「backend_application_name0」はデフォルト値なので省略していますが、「server0」という名前が入っています。

  • load_balance_mode設定

負荷分散設定です。有効にします。

  • master_slave_項目名設定

マスタ・スレーブ設定です。
今回レプリケーション方法はストリーミング設定にするので「master_slave_sub_mode = 'stream'」とします。

  • sr_check_項目名設定

ストリーミングレプリケーションの細かな設定です。
遅延確認の間隔や、それをチェックするユーザを定義します。
チェックユーザは新たに作成するのが手間だったのでpostgresを使っています。

  • follow_master_command設定

マスターノードのフェイルオーバー後に実行するコマンドを指定します。
コマンドは公式ドキュメントにサンプルがあるのでそのまま流用しています。

  • health_check_項目名設定

ヘルスチェックを実施するデータベースやユーザ、ヘルスチェック間隔を指定します。
こちらも新たに作成するのが手間でしたのでデータベース、ユーザともにpostgresを使ってます。

  • failover_command設定

PostgreSQLバックエンドノードが切り離される時に実行するコマンドを指定します。
こちらも公式ドキュメントにサンプルがあるのでそのまま流用しています。

  • use_watchdog設定

watchdogの使用設定です。有効にします。

  • trusted_servers設定

watchdogで使用する自ホストの正常性確認用のPing宛先です。
今回はゲートウェイである「X.X.0.254」を指定してみました。

  • wd_hostname設定

watchdogで使用する送受信用のIPアドレスです。
自ホストを指定します。ですのでマスタとサーバ、それぞれ違う値を設定します。

  • delegate_IP設定

VIPのIPアドレスです。Pgpool-II側でVIPを立ててくれるので、既存のIPアドレスと被らないようにつける必要があります。
今回は「X.X.0.100」を設定してみました。
ちなみにですが、こちらはマスタとスレーブどちらも同じ内容を設定しないといけません。

  • if_up_cmd設定
  • if_down_cmd設定
  • arping_cmd設定

VIPを起動したり確認したりするコマンドを指定します。
デフォルト値からNICデバイス名称を変更しただけの内容ですが、ちゃんと自身のサーバと合わせないとVIPが起動しません。

  • enable_consensus_with_half_votes設定

このパラメータを有効にすると、Pgpool-IIがバックエンドノードの縮退/フェイルオーバを実行する際にクォーラムが存在するかどうかを考慮するようになります。

Pgpool-II 4.2.6 文書 第5章サーバの設定 5.14. Watchdog

とても分かり辛く、説明が難しいのですが、できるだけ簡単にお伝えしたいと思います。
基本的にPgpool-IIのWatchdogは奇数台推奨です。
奇数台を前提とした動きをするのでフェイルオーバー等もその影響を受けます。奇数台でないとフェイルオーバーもしてくれません。
ただし、この設定を有効にすると偶数台でも問題なく動きますよ、という内容になります。
今回は2台（偶数台）ですので有効にします。

  • heartbeat_項目名X設定

heartbeat_項目名+数字は「死活監視に関連した各サーバの設定」を指定します。
「heartbeat_destination0」は「1台目（数字は0スタート）のハートビート送信先IPアドレス」という項目になります。
backend設定と似たような設定ですね。
「heartbeat_destination0」は「もう片方のIPアドレス」、「heartbeat_device0」では「ens192」を設定しています。

  • other_項目名X設定

other_項目名+数字は「その他Pgpool-IIに関連した各サーバの設定」を指定します。
その他と言っても自身以外のPgpool-IIの設定ですのでheartbeatで設定したIPアドレスと同じになるかと思います。
また、Pgpool-IIのポートやWatchdogのポートも設定します。

  • pool_hba.confの設定

pgpool-IIのアクセス制限設定ファイルです。
こんな感じに設定してみました。

# vi /etc/pgpool-II/pool_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         X.X.0.0/16          md5
host    all         all         ::1/128               trust
  • アクセスネットワーク設定

今回設定するIPアドレスのネットワーク範囲です。
ほぼPostgreSQLと同じですね。こちらでも「X.X.0.0/16」を設定してみました。

  • failover.shの設定

Pgpool-IIのフェイルオーバーファイルです。
こちらは公式ドキュメントのサンプルを流用しています。

# vi /etc/pgpool-II/pool_hba.conf
 
#! /bin/sh -x
# 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
falling_node=$1          # %d
old_primary=$2           # %P
new_primary=$3           # %H
pgdata=$4                # %R
pghome=/usr
log=/var/log/pgpool/failover.log
date >> $log
echo "failed_node_id=$falling_node new_primary=$new_primary" >> $log
if [ $falling_node = $old_primary ]; then
    if [ $UID -eq 0 ]
    then
        su postgres -c "ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata"
    else
        ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata
    fi
    exit 0;
fi;
exit 0;


以上で設定変更は完了です。

[4] サービス再起動

  • Pgpool-IIサービス停止
# systemctl stop pgpool
  • PostregSQLサービス起動
# systemctl start postgresql
  • PostregSQLサービス確認
# systemctl status postgresql
● postgresql.service - PostgreSQL database server
～省略～
   Active: active (running)
～省略～

エラーが無いことを確認し、次にPgpool-IIを起動します。

  • サービス起動

Pgpool-IIサービスを起動します。マスタから起動してください。

# systemctl start pgpool
  • サービス状態確認
# systemctl status pgpool
● pgpool.service - Pgpool-II
～省略～
   Active: active (running)
～省略～
  • ポート確認

PostgreSQLのポートである5432と、Pgpool-IIのポートである9999が上がっています。

# ss -natu | egrep '5432|9999'
tcp    LISTEN  0       64               0.0.0.0:9999             0.0.0.0:*
tcp    LISTEN  0       128              0.0.0.0:5432             0.0.0.0:*
tcp    LISTEN  0       64                  [::]:9999                [::]:*
tcp    LISTEN  0       128                 [::]:5432                [::]:*
  • IPアドレス確認
# ip a
1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    ～省略～
2: ens192: mtu 1500 qdisc mq state UP group default qlen 1000
    ～省略～
    inet X.X.X.10/24 brd X.X.0.255 scope global noprefixroute ens192
       valid_lft forever preferred_lft forever
    inet X.X.X.20/24 scope global secondary ens192:vip
       valid_lft forever preferred_lft forever
    ～省略～

Pgpool-IIで設定したVIP用のIPアドレスが起動できていれば完成です。
ただし、VIPですのでマスタかスタンバイどちらかのみでVIPが起動します。
手順通りに進めていればマスタでVIPが立ち上がっているはずです。
先にPgpool-IIを起動した方でVIPが上がるのでマスタから起動をすることをオススメしています。

技術情報

商標について

関連サービス

OSS構築支援
OSS保守サポート

お気軽にお問い合わせください。応対時間 9:30-17:30 [ 土・日・祝日除く ]

お問い合わせ
  • X
タグ
OSS、Pgpool-II、PostgreSQL
前の記事
Pgpool-IIの基本的な構築方法
2021年2月15日
次の記事
PostgreSQL＋Pgpool-IIのパフォーマンス検証
2021年4月19日