7.3. Pgpool-II + Watchdogの構築の例

ここでは、ストリーミングレプリケーション構成のPostgreSQLPgpool-IIで管理するシステムの構成例を示します。この例では、3台のPgpool-IIを使ってPostgreSQLを管理し、単一障害点やスプリットブレインの起きない堅牢なクラスタを運用することが可能です。

7.3.1. 前提条件

Pgpool-IIサーバとPostgreSQLサーバが同じサブネットにあることを前提とします。

7.3.2. 全体構成

今回は、Linux サーバ 5 台を用意し、それぞれのホスト名は 「osspc16」、「osspc17」、「osspc18」、「osspc19」、「osspc20」 とします。使用するOSはすべてCentOS7とします。Pgpool-II3台 +PostgresQL2台(ストリーミングレプリケーション)の全体構成図は以下の通りです。

図 7-1. 全体構成図

注意: 「アクティブ」「スタンバイ」「Primary」「Standby」といった役割は固定されているものではなく、運用と共に変化することがあります。

7.3.3. インストール

ここでは、 PostgreSQL 9.6.1 とPgpool-II 3.6.1 をRPMからインストールします。

表 7-2. Pgpool-II、PostgreSQLのバージョンと設定情報

サーババージョンホスト名ポート番号$PGDATAディレクトリ
PostgreSQLサーバ (プライマリ)PostgreSQL9.6.1osspc195432/var/lib/pgsql/9.6/data
PostgreSQLサーバ (スタンバイ) PostgreSQL9.6.1osspc205432/var/lib/pgsql/9.6/data
Pgpool-IIサーバPgpool-II3.6.1osspc169999-
Pgpool-IIサーバPgpool-II3.6.1osspc179999-
Pgpool-IIサーバPgpool-II3.6.1osspc189999-

Pgpool-IIのインストール方法は以下の通りです。

     # yum install http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-release-3.6-1.noarch.rpm
     # yum install pgpool-II-pg96
     # yum install pgpool-II-pg96-debuginfo
     # yum install pgpool-II-pg96-devel
     # yum install pgpool-II-pg96-extensions
    

PostgreSQLのインストールはPostgreSQLコミュニティのリポジトリを使います。

     # yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
     # yum install postgresql96-server
    

7.3.4. 事前設定

7.3.5. Pgpool-IIの設定

7.3.5.1. 共通設定

以下の操作はosspc16, osspc17, osspc18での共通の設定です。

RPM からインストールする場合、すべてのPgpool-II設定ファイルは/etc/pgpool-IIにあります。今回はストリーミングレプリケーションモードのテンプレートとしてpgpool.conf.sample-streamサンプルファイルを使用します。

      # cp /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf
     

Pgpool-IIが全てのIPアドレスから接続を受け付けるように、listen_addressesパラメータに'*'を設定します。

      listen_addresses = '*'
     

レプリケーションの遅延チェックユーザとパスワードを設定します。

      sr_check_user = 'pgpool'
      sr_check_password = 'pgpool'
     

自動フェイルオーバのため、ヘルスチェックを有効にします。 また、ネットワークが不安定な場合には、バックエンドが正常であるにも関わらず、ヘルスチェックに失敗し、フェイルオーバや縮退運転が発生してしまう可能性があります。そのようなヘルスチェックの誤検知を防止するため、ヘルスチェックのリトライ回数をhealth_check_max_retries = 10 に設定しておきます。

      health_check_period = 5
      # Health check period
      # Disabled (0) by default
      health_check_timeout = 20
      # Health check timeout
      # 0 means no timeout
      health_check_user = 'pgpool'
      health_check_password = 'pgpool'

      health_check_max_retries = 10
     

また、バックエンド情報を前述のosspc19osspc20の設定に従って設定しておきます。

      # - Backend Connection Settings -

      backend_hostname0 = 'osspc19'
      backend_port0 = 5432
      backend_weight0 = 1
      backend_data_directory0 = '/var/lib/pgsql/9.6/data'
      backend_flag0 = 'ALLOW_TO_FAILOVER'

      backend_hostname1 = 'osspc20'
      backend_port1 = 5432
      backend_weight1 = 1
      backend_data_directory1 = '/var/lib/pgsql/9.6/data'
      backend_flag1 = 'ALLOW_TO_FAILOVER'
     

7.3.5.2. フェイルオーバの設定

failover_commandを次のように設定します。引数の %d %P %H %R は、それぞれ実行時にPgpool-IIによって「切り離されたノード番号」、「古いプライマリノードのID 」、「新しいマスターのホスト名」、「新しいマスターノードのデータベースクラスタのパス 」に置き換えられます。

      failover_command = '/etc/pgpool-II/failover.sh %d %P %H %R'
     

また、/etc/pgpool-II/failover.shを作成し、実行権限を与えておきます。

      # vi /etc/pgpool-II/failover.sh
      # chmod 755 /etc/pgpool-II/failover.sh
     

  • /etc/pgpool-II/failover.sh

    #! /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
    #                  %H = new master node host name
    #                  %M = old master node id
    #                  %P = old primary node id
    #                  %r = new master port number
    #                  %R = new master database cluster path
    #                  %% = '%' character
    
    falling_node=$1          # %d
    old_primary=$2           # %P
    new_primary=$3           # %H
    pgdata=$4                # %R
    
    pghome=/usr/pgsql-9.6
    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;
           

7.3.5.3. オンラインリカバリの設定

続いて、オンラインリカバリを行うためのPostgreSQLのユーザ名およびオンラインリカバリ時に呼び出されるコマンドrecovery_1st_stageを設定します。オンラインリカバリ用のスクリプトrecovery_1st_stagepgpool_remote_startをプライマリサーバのデータベースクラスタ配下に配置し、実行権限を与えておきます。

      recovery_user = 'postgres'
      recovery_password = 'postgres'

      recovery_1st_stage_command = 'recovery_1st_stage'
     
      $ vi /var/lib/pgsql/9.6/data/recovery_1st_stage
      $ vi /var/lib/pgsql/9.6/data/pgpool_remote_start
      $ chmod 755 /var/lib/pgsql/9.6/data/recovery_1st_stage
      $ chmod 755 /var/lib/pgsql/9.6/data/pgpool_remote_start
     

  • /var/lib/pgsql/9.6/data/recovery_1st_stage

    #!/bin/bash -x
    # Recovery script for streaming replication.
    
    pgdata=$1
    remote_host=$2
    remote_pgdata=$3
    port=$4
    
    pghome=/usr/pgsql-9.6
    archivedir=/var/lib/pgsql/archivedir
    hostname=$(hostname)
    
    ssh -T postgres@$remote_host "
    rm -rf $remote_pgdata
    $pghome/bin/pg_basebackup -h $hostname -U repl -D $remote_pgdata -x -c fast
    rm -rf $archivedir/*
    
    cd $remote_pgdata
    cp postgresql.conf postgresql.conf.bak
    sed -e 's/#*hot_standby = off/hot_standby = on/' postgresql.conf.bak > postgresql.conf
    rm -f postgresql.conf.bak
    cat > recovery.conf << EOT
    standby_mode = 'on'
    primary_conninfo = 'host="$hostname" port=$port user=repl'
    restore_command = 'scp $hostname:$archivedir/%f %p'
    EOT
    "
           
  • /var/lib/pgsql/9.6/data/pgpool_remote_start

    #! /bin/sh -x
    
    pghome=/usr/pgsql-9.6
    remote_host=$1
    remote_pgdata=$2
    
    # リカバリ先のPostgreSQLを起動
    ssh -T $remote_host $pghome/bin/pg_ctl -w -D $remote_pgdata start > /dev/null 2>&1 < /dev/null &
           

また、オンラインリカバリ機能を使用するには、pgpool_recoverypgpool_remote_startpgpool_switch_xlogという関数が必要になるので、PostgreSQLサーバosspc19の template1 にpgpool_recoveryをインストールしておきます。

      # su - postgres
      $ psql template1
      =# CREATE EXTENSION pgpool_recovery;
     

7.3.5.4. クライアント認証の設定

事前設定の章で、PostgreSQLのクライアント認証方式をmd5に設定しましたので、クライアントがPgpool-IIを経由でバックエンドに接続できるようにPgpool-II側でもクライアント認証の設定を行う必要があります。Pgpool-IIのクライアント認証の設定ファイルはpool_hba.confと呼ばれ、RPMパッケージからインストールする場合、デフォルトでは/etc/pgpool-II配下にインストールされます。デフォルトではpool_hba.confによる認証は無効になっているので、 pgpool.confでは以下の設定を on に変更します。

      enable_pool_hba = on
     

pool_hba.confのフォーマットはPostgreSQLpg_hba.confとほとんど同じです。pgpoolpostgresユーザをmd5認証に設定します。

      host    all         pgpool           0.0.0.0/0          md5
      host    all         postgres         0.0.0.0/0          md5
     

md5 認証で用いるデフォルトのファイル名は pool_passwd です。 「pg_md5 --md5auth --username=ユーザ名 パスワード」 コマンドを実行すると、ユーザ名と md5 によりハッシュ化 されたパスワードが pool_passwd に登録されます。 pool_passwd がまだ存在しなければ、pgpool.confと同じディレクトリ内に作成されます。

      # pg_md5 --md5auth --username=pgpool <pgpoolユーザのパスワード>
      # pg_md5 --md5auth --username=postgres <postgresユーザのパスワード>
     

7.3.5.5. Watchdogの設定

osspc16osspc17osspc18watchdogを有効にします。

      use_watchdog = on
     

アクティブ機が立ち上げる仮想 IP を delegate_IP に指定します。仮想 IP はまだ使われていない IP アドレスを指定してください。osspc16、osspc17、osspc18 の共通の設定です。

      delegate_IP = '133.137.174.153'
     

各watchdog が稼働するサーバ情報を設定しておきます。

  • osspc16の場合

    	wd_hostname = 'osspc16'
    	wd_port = 9000
           
  • osspc17の場合

    	wd_hostname = 'osspc17'
    	wd_port = 9000
           
  • osspc18の場合

    	wd_hostname = 'osspc18'
    	wd_port = 9000
           

各監視対象のPgpool-IIサーバ情報を設定しておきます。

  • osspc16の場合

    	# - Other pgpool Connection Settings -
    
    	other_pgpool_hostname0 = 'osspc17'
    	other_pgpool_port0 = 9999
    	other_wd_port0 = 9000
    
    	other_pgpool_hostname1 = 'osspc18'
    	other_pgpool_port1 = 9999
    	other_wd_port1 = 9000
           
  • osspc17の場合

    	# - Other pgpool Connection Settings -
    
    	other_pgpool_hostname0 = 'osspc16'
    	other_pgpool_port0 = 9999
    	other_wd_port0 = 9000
    
    	other_pgpool_hostname1 = 'osspc18'
    	other_pgpool_port1 = 9999
    	other_wd_port1 = 9000
           
  • osspc18の場合

    	# - Other pgpool Connection Settings -
    
    	other_pgpool_hostname0 = 'osspc16'
    	other_pgpool_port0 = 9999
    	other_wd_port0 = 9000
    
    	other_pgpool_hostname1 = 'osspc17'
    	other_pgpool_port1 = 9999
    	other_wd_port1 = 9000
           

ハートビート信号の送信先のホスト名とポート番号を指定します。

  • osspc16の場合

    	heartbeat_destination0 = 'osspc17'
    	heartbeat_destination_port0 = 9694
    	heartbeat_device0 = ''
    
    	heartbeat_destination1 = 'osspc18'
    	heartbeat_destination_port1 = 9694
    	heartbeat_device1 = ''
    
           
  • osspc17の場合

    	heartbeat_destination0 = 'osspc16'
    	heartbeat_destination_port0 = 9694
    	heartbeat_device0 = ''
    
    	heartbeat_destination1 = 'osspc18'
    	heartbeat_destination_port1 = 9694
    	heartbeat_device1 = ''
    
           
  • osspc18の場合

    	heartbeat_destination0 = 'osspc16'
    	heartbeat_destination_port0 = 9694
    	heartbeat_device0 = ''
    
    	heartbeat_destination1 = 'osspc17'
    	heartbeat_destination_port1 = 9694
    	heartbeat_device1 = ''
           

7.3.5.6. PCPコマンドの設定

PCPコマンドを使用するにはユーザ認証が必要になるので、ユーザ名とmd5ハッシュに変換されたパスワードをpcp.confファイルに設定します。ここではユーザ名にpostgresを使用し、<ユーザ名:ハッシュ化されたパスワード>を/etc/pgpool-II/pcp.confに追記しておきます。

      # pg_md5 -p
      Password: (パスワードを入力)
      (ハッシュ化されたパスワードをpcp.confに貼り付ける)

      # vi /etc/pgpool-II/pcp.conf
      (以下1行を追記)
      ユーザ名:ハッシュ化されたパスワード
     

ここで、Pgpool-IIの設定は完了です。

7.3.6. システムの起動と停止

Pgpool-IIの設定が完了したら、次にPgpool-IIを起動します。Pgpool-IIを起動する前に、バックエンドのPostgreSQLをあらかじめ起動する必要があります。また、PostgreSQLを停止する場合、Pgpool-IIを先に停止する必要があります。

7.3.7. ログの確認

Pgpool-IIのログはjournalctlコマンドを使用し確認します。

     # journalctl -a | grep pgpool
    

7.3.8. 動作確認

これから、動作確認を行います。まず、osspc16osspc17osspc18で以下のコマンドで Pgpool-II を起動します。

     # systemctl start pgpool.service
    

7.3.8.1. PostgreSQL スタンバイサーバを構築

まず、Pgpool-IIのオンラインリカバリ機能を利用し、スタンバイサーバを構築します。pcp_recovery_nodeコマンドで実行されるrecovery_1st_stage_commandパラメータに指定したrecovery_1st_stagepgpool_remote_startスプリクトが実行されるので、この 2つのスクリプトが現在稼働中のプライマリサーバosspc19のデータベースクラスタの下に存在することを確認します。

      # pcp_recovery_node -h 133.137.174.153 -p 9898 -U postgres -n 1
     

osspc20がスタンバイとして起動されたことを確認します。

      # psql -h 133.137.174.153 -p 9999 -U pgpool postgres

      postgres=> show pool_nodes;
      node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
      ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
      0       | osspc19  | 5432 | up     | 0.500000  | primary | 0          | true              | 0
      1       | osspc20  | 5432 | up     | 0.500000  | standby | 0          | false             | 0
      (2 行)
     

7.3.8.2. watchdog アクティブ/スタンバイの切り替え

pcp_watchdog_infoPgpool-IIwatchdogの情報を確認します。最初に起動したPgpool-IIが「MASTER」になります。

      # pcp_watchdog_info -h 133.137.174.153 -p 9898 -U postgres
      Password:
      3 YES osspc16:9999 Linux osspc16 osspc16

      osspc16:9999 Linux osspc16 osspc16 9999 9000 4 MASTER  #最初に起動されたサーバがMASTERになる
      osspc17:9999 Linux osspc17 osspc17 9999 9000 7 STANDBY #スタンバイとして稼働
      osspc18:9999 Linux osspc18 osspc18 9999 9000 7 STANDBY #スタンバイとして稼働
     

アクティブであるosspc16を停止し、osspc17またはosspc18がスタンバイからアクティブに昇格することを確認します。osspc16を停止する方法はPgpool-IIを停止する、またはマシンをシャットダウンします。ここでは、Pgpool-IIを停止します。

      [root@osspc16 ~]# systemctl stop pgpool.service
      [root@osspc16 ~]# pcp_watchdog_info -h 133.137.174.153 -p 9898 -U postgres
      Password:
      3 YES osspc17:9999 Linux osspc17 osspc17

      osspc17:9999 Linux osspc17 osspc17 9999 9000 4 MASTER     #osspc17がアクティブに昇格
      osspc16:9999 Linux osspc16 osspc16 9999 9000 10 SHUTDOWN  #osspc16が停止された
      osspc18:9999 Linux osspc18 osspc18 9999 9000 7 STANDBY    #スタンバイとして稼働
     

先ほど停止したPgpool-IIを再起動し、スタンバイとして起動したことを確認します。

      [root@osspc16 ~]# systemctl start pgpool.service
      [root@osspc16 ~]# pcp_watchdog_info -h 133.137.174.153 -p 9898 -U postgres
      Password:
      3 YES osspc17:9999 Linux osspc17 osspc17

      osspc17:9999 Linux osspc17 osspc17 9999 9000 4 MASTER
      osspc16:9999 Linux osspc16 osspc16 9999 9000 7 STANDBY
      osspc18:9999 Linux osspc18 osspc18 9999 9000 7 STANDBY
     

7.3.8.3. 自動フェイルオーバ

psqlで仮想IPに接続し、バックエンドの情報を確認します。

      # psql -h 133.137.174.153 -p 9999 -U pgpool postgres

      postgres=> show pool_nodes;
      node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
      ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
      0       | osspc19  | 5432 | up     | 0.500000  | primary | 0          | true              | 0
      1       | osspc20  | 5432 | up     | 0.500000  | standby | 0          | false             | 0
      (2 行)
     

次にプライマリであるosspc19PostgreSQLを停止し、フェイルオーバするかどうか確認してみます。

      $ pg_ctl -D /var/lib/pgsql/9.6/data -m immediate stop
     

osspc19を停止後、フェイルオーバが発生し、osspc20がプライマリに昇格したことを確認します。

      # psql -h 133.137.174.153 -p 9999 -U pgpool postgres

      postgres=> show pool_nodes;
      node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
      ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
      0       | osspc19  | 5432 | down   | 0.500000  | standby | 0          | false             | 0
      1       | osspc20  | 5432 | up     | 0.500000  | primary | 0          | true              | 0
      (2 行)
     

7.3.8.4. オンラインリカバリ

次に、Pgpool-IIのオンラインリカバリ機能を利用し、先ほど停止した旧プライマリサーバをスタンバイとして復旧させます。pcp_recovery_nodeコマンドで実行されるrecovery_1st_stage_commandパラメータに指定したrecovery_1st_stagepgpool_remote_startスプリクトが現在稼働中のプライマリサーバosspc20のデータベースクラスタの下に存在することを確認します。

      # pcp_recovery_node -h 133.137.174.153 -p 9898 -U postgres -n 0
     

osspc19がスタンバイとして起動されたことを確認します。

      # psql -h 133.137.174.153 -p 9999 -U pgpool postgres

      postgres=> show pool_nodes;
      node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
      ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
      0       | osspc19  | 5432 | up     | 0.500000  | standby | 0          | false             | 0
      1       | osspc20  | 5432 | up     | 0.500000  | primary | 0          | true              | 0
      (2 行)
     

以上で、動作確認が完了です。