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

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
     

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

      # - 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_stage、pgpool_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_recovery、pgpool_remote_start、pgpool_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のフォーマットはPostgreSQLのpg_hba.confとほとんど同じです。pgpoolとpostgresユーザを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の設定

osspc16、osspc17、osspc18でwatchdogを有効にします。

      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
      (以下１行を追記)
      ユーザ名:ハッシュ化されたパスワード
     

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

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

まず、Pgpool-IIのオンラインリカバリ機能を利用し、スタンバイサーバを構築します。pcp_recovery_nodeコマンドで実行されるrecovery_1st_stage_commandパラメータに指定したrecovery_1st_stageとpgpool_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_infoでPgpool-IIのwatchdogの情報を確認します。最初に起動した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 行)
     

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

      $ 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_stageとpgpool_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 行)
     

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