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'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/pgsql/9.6/data'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER or DISALLOW_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
  #                  %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/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'
                                   # Online recovery user
recovery_password = 'postgres'
                                   # Online recovery password

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'
                                      # Host name or IP address to connect to for other pgpool 0
                                      # (change requires restart)
  other_pgpool_port0 = 9999
                                      # Port number for othet pgpool 0
                                      # (change requires restart)
  other_wd_port0 = 9000
                                      # Port number for othet watchdog 0
                                      # (change requires restart)
  other_pgpool_hostname1 = 'osspc18'
  other_pgpool_port1 = 9999
  other_wd_port1 = 9000
              

• osspc17の場合

  # - Other pgpool Connection Settings -
  
  other_pgpool_hostname0 = 'osspc16'
                                      # Host name or IP address to connect to for other pgpool 0
                                      # (change requires restart)
  other_pgpool_port0 = 9999
                                      # Port number for othet pgpool 0
                                      # (change requires restart)
  other_wd_port0 = 9000
                                      # Port number for othet watchdog 0
                                      # (change requires restart)
  other_pgpool_hostname1 = 'osspc18'
  other_pgpool_port1 = 9999
  other_wd_port1 = 9000
              

• osspc18の場合

  # - Other pgpool Connection Settings -
  
  other_pgpool_hostname0 = 'osspc16'
                                      # Host name or IP address to connect to for other pgpool 0
                                      # (change requires restart)
  other_pgpool_port0 = 9999
                                      # Port number for othet pgpool 0
                                      # (change requires restart)
  other_wd_port0 = 9000
                                      # Port number for othet watchdog 0
                                      # (change requires restart)
  other_pgpool_hostname1 = 'osspc17'
  other_pgpool_port1 = 9999
  other_wd_port1 = 9000
              

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

• osspc16の場合

  heartbeat_destination0 = 'osspc17'
                                      # Host name or IP address of destination 0
                                      # for sending heartbeat signal.
                                      # (change requires restart)
  heartbeat_destination_port0 = 9694
                                      # Port number of destination 0 for sending
                                      # heartbeat signal. Usually this is the
                                      # same as wd_heartbeat_port.
                                      # (change requires restart)
  heartbeat_device0 = ''
                                      # Name of NIC device (such like 'eth0')
                                      # used for sending/receiving heartbeat
                                      # signal to/from destination 0.
                                      # This works only when this is not empty
                                      # and pgpool has root privilege.
                                      # (change requires restart)
  
  heartbeat_destination1 = 'osspc18'
  heartbeat_destination_port1 = 9694
  heartbeat_device1 = ''
  
              

• osspc17の場合

  heartbeat_destination0 = 'osspc16'
                                      # Host name or IP address of destination 0
                                      # for sending heartbeat signal.
                                      # (change requires restart)
  heartbeat_destination_port0 = 9694
                                      # Port number of destination 0 for sending
                                      # heartbeat signal. Usually this is the
                                      # same as wd_heartbeat_port.
                                      # (change requires restart)
  heartbeat_device0 = ''
                                      # Name of NIC device (such like 'eth0')
                                      # used for sending/receiving heartbeat
                                      # signal to/from destination 0.
                                      # This works only when this is not empty
                                      # and pgpool has root privilege.
                                      # (change requires restart)
  
  heartbeat_destination1 = 'osspc18'
  heartbeat_destination_port1 = 9694
  heartbeat_device1 = ''
  
              

• osspc18の場合

  heartbeat_destination0 = 'osspc16'
                                      # Host name or IP address of destination 0
                                      # for sending heartbeat signal.
                                      # (change requires restart)
  heartbeat_destination_port0 = 9694
                                      # Port number of destination 0 for sending
                                      # heartbeat signal. Usually this is the
                                      # same as wd_heartbeat_port.
                                      # (change requires restart)
  heartbeat_device0 = ''
                                      # Name of NIC device (such like 'eth0')
                                      # used for sending/receiving heartbeat
                                      # signal to/from destination 0.
                                      # This works only when this is not empty
                                      # and pgpool has root privilege.
                                      # (change requires restart)
  
  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またはosspc1がスタンバイからアクティブに昇格することを確認します。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 行)
        

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