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

8.3.5.1. 共通設定

以下の操作はserver1, server2, server3での共通の設定です。

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

    [全サーバ]# cp -p /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.confに指定せず、pool_passwdファイルに作成します。Pgpool-II 4.0から、sr_check_passwordが空白の場合、Pgpool-IIは空のパスワードを使用する前にまずpool_passwdファイルからsr_check_userに指定したユーザのパスワードを取得できるか試みます。

    sr_check_user = 'pgpool'
    sr_check_password = ''
   

自動フェイルオーバのため、ヘルスチェックを有効にします。health_check_periodのデフォルト値が0で、これはヘルスチェックが無効であることを意味します。 また、ネットワークが不安定な場合には、バックエンドが正常であるにも関わらず、ヘルスチェックに失敗し、フェイルオーバや縮退運転が発生してしまう可能性があります。そのようなヘルスチェックの誤検知を防止するため、ヘルスチェックのリトライ回数をhealth_check_max_retries = 3 に設定しておきます。 health_check_user、health_check_passwordは前述のsr_check_user、sr_check_passwordと同様に設定します。

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

    health_check_max_retries = 3
   

また、バックエンド情報を前述のserver1、server2及びserver3の設定に従って設定しておきます。複数バックエンドノードを定義する場合、以下のbackend_*などのパラメータ名の末尾にノードIDを表す数字を付加することで複数のバックエンドを指定することができます。

    # - Backend Connection Settings -

    backend_hostname0 = 'server1'
    # 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/11/data'
    # Data directory for backend 0
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    # Controls various backend behavior
    # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
    backend_hostname1 = 'server2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/var/lib/pgsql/11/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'

    backend_hostname2 = 'server3'
    backend_port2 = 5432
    backend_weight2 = 1
    backend_data_directory2 = '/var/lib/pgsql/11/data'
    backend_flag2 = 'ALLOW_TO_FAILOVER'
   

SHOW POOL_NODESコマンドでレプリケーション状態と同期レプリケーション状態を表示するには、backend_application_nameパラメータを設定する必要があります。ここではそれぞれのホスト名を設定します。(Pgpool-II 4.1以降)

       ...                                                                          
       backend_application_name0 = 'server1'
       ...  
       backend_application_name1 = 'server2'
       ...  
       backend_application_name2 = 'server3'
      

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

PostgreSQLバックエンドノードがダウンした時に実行するスクリプトをfailover_commandに設定します。 また、PostgreSQLサーバが3台の場合、プライマリノードのフェイルオーバ後に新しいプライマリからスレーブをリカバリするためにfollow_master_commandも設定する必要があります。follow_master_commandはプライマリノードのフェイルオーバ後に実行されます。PostgreSQLサーバが2台の場合、follow_master_commandの設定は不要です。

それぞれの実行スクリプトの引数は、それぞれ実行時にPgpool-IIによってバックエンドの具体的な情報に置き換えられます。各引数の意味はfailover_commandをご参照ください。

    failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
    follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %m %M %H %P %r %R'
   

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

    # vi /etc/pgpool-II/failover.sh
    # vi /etc/pgpool-II/follow_master.sh
    # chmod +x /etc/pgpool-II/{failover.sh,follow_master.sh}
   

• /etc/pgpool-II/failover.sh

  #!/bin/bash
  # This script is run by failover_command.
  
  set -o xtrace
  exec > >(logger -i -p local1.info) 2>&1
  
  # Special values:
  #   %d = failed node id
  #   %h = failed node hostname
  #   %p = failed node port number
  #   %D = failed node database cluster path
  #   %m = new master node id
  #   %H = new master node hostname
  #   %M = old master node id
  #   %P = old primary node id
  #   %r = new master port number
  #   %R = new master database cluster path
  #   %N = old primary node hostname
  #   %S = old primary node port number
  #   %% = '%' character
  
  FAILED_NODE_ID="$1"
  FAILED_NODE_HOST="$2"
  FAILED_NODE_PORT="$3"
  FAILED_NODE_PGDATA="$4"
  NEW_MASTER_NODE_ID="$5"
  NEW_MASTER_NODE_HOST="$6"
  OLD_MASTER_NODE_ID="$7"
  OLD_PRIMARY_NODE_ID="$8"
  NEW_MASTER_NODE_PORT="$9"
  NEW_MASTER_NODE_PGDATA="${10}"
  OLD_PRIMARY_NODE_HOST="${11}"
  OLD_PRIMARY_NODE_PORT="${12}"
  
  PGHOME=/usr/pgsql-11
  
  
  logger -i -p local1.info failover.sh: start: failed_node_id=$FAILED_NODE_ID old_primary_node_id=$OLD_PRIMARY_NODE_ID failed_host=$FAILED_NODE_HOST new_master_host=$NEW_MASTER_NODE_HOST
  
  ## If there's no master node anymore, skip failover.
  if [ $NEW_MASTER_NODE_ID -lt 0 ]; then
      logger -i -p local1.info failover.sh: All nodes are down. Skipping failover.
  	exit 0
  fi
  
  ## Test passwrodless SSH
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
  
  if [ $? -ne 0 ]; then
      logger -i -p local1.info failover.sh: passwrodless SSH to postgres@${NEW_MASTER_NODE_HOST} failed. Please setup passwrodless SSH.
      exit 1
  fi
  
  ## If Standby node is down, skip failover.
  if [ $FAILED_NODE_ID -ne $OLD_PRIMARY_NODE_ID ]; then
      logger -i -p local1.info failover.sh: Standby node is down. Skipping failover.
  
      ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$OLD_PRIMARY_NODE_HOST -i ~/.ssh/id_rsa_pgpool "
          ${PGHOME}/bin/psql -p $OLD_PRIMARY_NODE_PORT -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')\"
      "
  
      if [ $? -ne 0 ]; then
          logger -i -p local1.error failover.sh: drop replication slot "${FAILED_NODE_HOST}" failed
          exit 1
      fi
  
      exit 0
  fi
  
  ## Promote Standby node.
  logger -i -p local1.info failover.sh: Primary node is down, promote standby node ${NEW_MASTER_NODE_HOST}.
  
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
      postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -D ${NEW_MASTER_NODE_PGDATA} -w promote
  
  if [ $? -ne 0 ]; then
      logger -i -p local1.error failover.sh: new_master_host=$NEW_MASTER_NODE_HOST promote failed
      exit 1
  fi
  
  logger -i -p local1.info failover.sh: end: new_master_node_id=$NEW_MASTER_NODE_ID started as the primary node
  exit 0
       

• /etc/pgpool-II/follow_master.sh

  #!/bin/bash
  # This script is run after failover_command to synchronize the Standby with the new Primary.
  # First try pg_rewind. If pg_rewind failed, use pg_basebackup.
  
  set -o xtrace
  exec > >(logger -i -p local1.info) 2>&1
  
  # Special values:
  #   %d = failed node id
  #   %h = failed node hostname
  #   %p = failed node port number
  #   %D = failed node database cluster path
  #   %m = new master node id
  #   %H = new master node hostname
  #   %M = old master node id
  #   %P = old primary node id
  #   %r = new master port number
  #   %R = new master database cluster path
  #   %N = old primary node hostname
  #   %S = old primary node port number
  #   %% = '%' character
  
  FAILED_NODE_ID="$1"
  FAILED_NODE_HOST="$2"
  FAILED_NODE_PORT="$3"
  FAILED_NODE_PGDATA="$4"
  NEW_MASTER_NODE_ID="$5"
  OLD_MASTER_NODE_ID="$6"
  NEW_MASTER_NODE_HOST="$7"
  OLD_PRIMARY_NODE_ID="$8"
  NEW_MASTER_NODE_PORT="$9"
  NEW_MASTER_NODE_PGDATA="${10}"
  
  PGHOME=/usr/pgsql-11
  ARCHIVEDIR=/var/lib/pgsql/archivedir
  REPLUSER=repl
  PCP_USER=pgpool
  PGPOOL_PATH=/usr/bin
  PCP_PORT=9898
  
  logger -i -p local1.info follow_master.sh: start: Standby node ${FAILED_NODE_ID}
  
  ## Test passwrodless SSH
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
  
  if [ $? -ne 0 ]; then
      logger -i -p local1.info follow_master.sh: passwrodless SSH to postgres@${NEW_MASTER_NODE_HOST} failed. Please setup passwrodless SSH.
      exit 1
  fi
  
  ## Get PostgreSQL major version
  PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'`
  
  if [ $PGVERSION -ge 12 ]; then
  RECOVERYCONF=${FAILED_NODE_PGDATA}/myrecovery.conf
  else
  RECOVERYCONF=${FAILED_NODE_PGDATA}/recovery.conf
  fi
  
  ## Check the status of Standby
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
  postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -w -D ${FAILED_NODE_PGDATA} status
  
  
  ## If Standby is running, synchronize it with the new Primary.
  if [ $? -eq 0 ]; then
  
      logger -i -p local1.info follow_master.sh: pg_rewind for $FAILED_NODE_ID
  
      # Create replication slot "${FAILED_NODE_HOST}"
      ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
          ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_create_physical_replication_slot('${FAILED_NODE_HOST}');\"
      "
  
      ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
  
          set -o errexit
  
          ${PGHOME}/bin/pg_ctl -w -m f -D ${FAILED_NODE_PGDATA} stop
  
          cat > ${RECOVERYCONF} << EOT
  primary_conninfo = 'host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT} user=${REPLUSER} application_name=${FAILED_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass'''
  recovery_target_timeline = 'latest'
  restore_command = 'scp ${NEW_MASTER_NODE_HOST}:${ARCHIVEDIR}/%f %p'
  primary_slot_name = '${FAILED_NODE_HOST}'
  EOT
  
          if [ ${PGVERSION} -ge 12 ]; then
              touch ${FAILED_NODE_PGDATA}/standby.signal
          else
              echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
          fi
  
          ${PGHOME}/bin/pg_rewind -D ${FAILED_NODE_PGDATA} --source-server=\"user=postgres host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT}\"
  
      "
  
      if [ $? -ne 0 ]; then
          logger -i -p local1.error follow_master.sh: end: pg_rewind failed. Try pg_basebackup.
  
          ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
               
              set -o errexit
  
              # Execute pg_basebackup
              rm -rf ${FAILED_NODE_PGDATA}
              rm -rf ${ARCHIVEDIR}/*
              ${PGHOME}/bin/pg_basebackup -h ${NEW_MASTER_NODE_HOST} -U $REPLUSER -p ${NEW_MASTER_NODE_PORT} -D ${FAILED_NODE_PGDATA} -X stream
  
              if [ ${PGVERSION} -ge 12 ]; then
                  sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \
                         -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${FAILED_NODE_PGDATA}/postgresql.conf
              fi
       
              cat > ${RECOVERYCONF} << EOT
  primary_conninfo = 'host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT} user=${REPLUSER} application_name=${FAILED_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass'''
  recovery_target_timeline = 'latest'
  restore_command = 'scp ${NEW_MASTER_NODE_HOST}:${ARCHIVEDIR}/%f %p'
  primary_slot_name = '${FAILED_NODE_HOST}'
  EOT
  
              if [ ${PGVERSION} -ge 12 ]; then
                      touch ${FAILED_NODE_PGDATA}/standby.signal
              else
                      echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
              fi
          "
  
          if [ $? -ne 0 ]; then
              # drop replication slot
              ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
                  ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')\"
              "
  
              logger -i -p local1.error follow_master.sh: end: pg_basebackup failed
              exit 1
          fi
      fi
  
      # start Standby node on ${FAILED_NODE_HOST}
      ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
              postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool $PGHOME/bin/pg_ctl -l /dev/null -w -D ${FAILED_NODE_PGDATA} start
  
      # If start Standby successfully, attach this node
      if [ $? -eq 0 ]; then
  
          # Run pcp_attact_node to attach Standby node to Pgpool-II.
          ${PGPOOL_PATH}/pcp_attach_node -w -h localhost -U $PCP_USER -p ${PCP_PORT} -n ${FAILED_NODE_ID}
  
          if [ $? -ne 0 ]; then
                  logger -i -p local1.error follow_master.sh: end: pcp_attach_node failed
                  exit 1
          fi
  
      # If start Standby failed, drop replication slot "${FAILED_NODE_HOST}"
      else
  
          ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool \
          ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c "SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')"
  
          logger -i -p local1.error follow_master.sh: end: follow master command failed
          exit 1
      fi
  
  else
      logger -i -p local1.info follow_master.sh: failed_nod_id=${FAILED_NODE_ID} is not running. skipping follow master command
      exit 0
  fi
  
  logger -i -p local1.info follow_master.sh: end: follow master command complete
  exit 0
       

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

続いて、オンラインリカバリを行うためのPostgreSQLのユーザ名およびオンラインリカバリ時に呼び出されるコマンドrecovery_1st_stageを設定します。 オンラインリカバリで実行されるpgpool_recovery関数はPostgreSQLのスーパーユーザ権限が必要なため、recovery_userにスーパーユーザを指定しなければなりません。ここでは、postrgesユーザを指定します。 オンラインリカバリ用のスクリプトrecovery_1st_stage、pgpool_remote_startをプライマリサーバ(server1)のデータベースクラスタ配下に配置し、実行権限を与えておきます。

    recovery_user = 'postgres'
    # Online recovery user
    recovery_password = ''
    # Online recovery password

    recovery_1st_stage_command = 'recovery_1st_stage'
   

    [server1]# su - postgres
    [server1]$ vi /var/lib/pgsql/11/data/recovery_1st_stage
    [server1]$ vi /var/lib/pgsql/11/data/pgpool_remote_start
    [server1]$ chmod +x /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}
   

• /var/lib/pgsql/11/data/recovery_1st_stage

  #!/bin/bash
  # This script is executed by "recovery_1st_stage" to recovery a Standby node.
  
  set -o xtrace
  exec > >(logger -i -p local1.info) 2>&1
  
  PRIMARY_NODE_PGDATA="$1"
  DEST_NODE_HOST="$2"
  DEST_NODE_PGDATA="$3"
  PRIMARY_NODE_PORT="$4"
  DEST_NODE_ID="$5"
  DEST_NODE_PORT="$6"
  
  PRIMARY_NODE_HOST=$(hostname)
  PGHOME=/usr/pgsql-11
  ARCHIVEDIR=/var/lib/pgsql/archivedir
  REPLUSER=repl
  
  logger -i -p local1.info recovery_1st_stage: start: pg_basebackup for Standby node $DEST_NODE_ID
  
  ## Test passwrodless SSH
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
  
  if [ $? -ne 0 ]; then
      logger -i -p local1.info recovery_1st_stage: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
      exit 1
  fi
  
  ## Get PostgreSQL major version
  PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'`
  if [ $PGVERSION -ge 12 ]; then
      RECOVERYCONF=${DEST_NODE_PGDATA}/myrecovery.conf
  else
      RECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf
  fi
  
  ## Create replication slot "${DEST_NODE_HOST}"
  ${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ
  SELECT pg_create_physical_replication_slot('${DEST_NODE_HOST}');
  EOQ
  
  ## Execute pg_basebackup to recovery Standby node
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool "
  
      set -o errexit
  
      rm -rf $DEST_NODE_PGDATA
      rm -rf $ARCHIVEDIR/*
  
      ${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -p $PRIMARY_NODE_PORT -D $DEST_NODE_PGDATA -X stream
  
      if [ ${PGVERSION} -ge 12 ]; then
          sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \
                 -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${DEST_NODE_PGDATA}/postgresql.conf
      fi
  
      cat > ${RECOVERYCONF} << EOT
  primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass'''
  recovery_target_timeline = 'latest'
  restore_command = 'scp ${PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p'
  primary_slot_name = '${DEST_NODE_HOST}'
  EOT
  
      if [ ${PGVERSION} -ge 12 ]; then
              touch ${DEST_NODE_PGDATA}/standby.signal
      else
              echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
      fi
  
      sed -i \"s/#*port = .*/port = ${DEST_NODE_PORT}/\" ${DEST_NODE_PGDATA}/postgresql.conf
  "
  
  if [ $? -ne 0 ]; then
  
      ${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ
  SELECT pg_drop_replication_slot('${DEST_NODE_HOST}');
  EOQ
  
      logger -i -p local1.error recovery_1st_stage: end: pg_basebackup failed. online recovery failed
      exit 1
  fi
  
  logger -i -p local1.info recovery_1st_stage: end: recovery_1st_stage complete
  exit 0
       

• /var/lib/pgsql/11/data/pgpool_remote_start

  #!/bin/bash
  # This script is run after recovery_1st_stage to start Standby node.
  
  set -o xtrace
  exec > >(logger -i -p local1.info) 2>&1
  
  PGHOME=/usr/pgsql-11
  DEST_NODE_HOST="$1"
  DEST_NODE_PGDATA="$2"
  
  
  logger -i -p local1.info pgpool_remote_start: start: remote start Standby node $DEST_NODE_HOST
  
  ## Test passwrodless SSH
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
  
  if [ $? -ne 0 ]; then
      logger -i -p local1.info pgpool_remote_start: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
      exit 1
  fi
  
  ## Start Standby node
  ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool "
      $PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start
  "
  
  if [ $? -ne 0 ]; then
      logger -i -p local1.error pgpool_remote_start: $DEST_NODE_HOST PostgreSQL start failed.
      exit 1
  fi
  
  logger -i -p local1.info pgpool_remote_start: end: $DEST_NODE_HOST PostgreSQL started successfully.
  exit 0
       

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

    [server1]# su - postgres
    [server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
   

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

事前設定の章で、Pgpool-IIとPostgreSQLの間に認証方式をscram-sha-256に設定しました。この設定例では、クライアントとPgpool-IIの間でもscram-sha-256認証方式を利用し接続するように設定します。 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ユーザをscram-sha-256認証に設定します。

    host    all         pgpool           0.0.0.0/0          scram-sha-256
    host    all         postgres         0.0.0.0/0          scram-sha-256
   

Pgpool-IIのクライアント認証で用いるデフォルトのパスワードファイル名はpool_passwdです。 scram-sha-256認証を利用する場合、Pgpool-IIはそれらのパスワードを復号化するために復号鍵が必要となります。全サーバで復号鍵ファイルをrootユーザのホームディレクトリ配下に作成します。

    [全サーバ]# echo '任意の文字列' > ~/.pgpoolkey 
    [全サーバ]# chmod 600 ~/.pgpoolkey
   

「pg_enc -m -k /path/to/.pgpoolkey -u ユーザ名 -p」 コマンドを実行すると、ユーザ名とAES256で暗号化したパスワードのエントリがpool_passwdに登録されます。 pool_passwd がまだ存在しなければ、pgpool.confと同じディレクトリ内に作成されます。

    [全サーバ]# pg_enc -m -k /root/.pgpoolkey -u pgpool -p
    db password: [pgpoolユーザのパスワード]
    [全サーバ]# pg_enc -m -k /root/.pgpoolkey -u postgres -p
    db password: [postgresユーザのパスワード]

    # cat /etc/pgpool-II/pool_passwd 
    pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
    postgres:AESHs/pWL5rtXy2IwuzroHfqg==
   

8.3.5.5. Watchdogの設定

デフォルトではwatchdog機能が無効のため、server1、server2及びserver3でwatchdogを有効にします。

    use_watchdog = on
   

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

    delegate_IP = '192.168.137.150'
   

仮想IPの起動/停止、ARPリクエストの送信を行う設定パラメータif_up_cmd、if_down_cmd、arping_cmdに、ネットワーク環境に合わせてネットワークインターフェース名を設定します。 今回の例で使ったネットワークインターフェースは「enp0s8」となっています。 if_up/down_cmdやarping_cmdを実行するにはroot権限が必要となりますので、 一般ユーザが実行できるようにip/arpingコマンドにsetuidを設定するか、 Pgpool-II起動ユーザ、デフォルトではpostgresユーザ (Pgpool-II 4.1以降) がパスワードなしにsudoを実行できるように設定する必要があります。 ここでは、sudoを介して実行するように設定します。RPMからインストールした場合、postgresユーザがパスワードなしにsudoを介してip/arpingを実行できるように設定済みです。

if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'
   

ipコマンドやarpingコマンドのパスがデフォルトのパスと異なる場合、環境に合わせてif_cmd_pathやarping_pathを設定しておいてください。 ただし、 if_up/down_cmd及びarping_cmdに指定したコマンドが"/"で始まる場合、 フルパスとみなしif_cmd_path及びarping_pathの設定を無視します。

if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
   

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

• server1の場合

        wd_hostname = 'server1'
        wd_port = 9000
       

• server2の場合

        wd_hostname = 'server2'
        wd_port = 9000
       

• server3の場合

        wd_hostname = 'server3'
        wd_port = 9000
       

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

• server1の場合

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

• server2の場合

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

• server3の場合

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

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

• server1の場合

        heartbeat_destination0 = 'server2'
        # 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 = 'server3'
        heartbeat_destination_port1 = 9694
        heartbeat_device1 = ''
  
       

• server2の場合

        heartbeat_destination0 = 'server1'
        # 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 = 'server3'
        heartbeat_destination_port1 = 9694
        heartbeat_device1 = ''
  
       

• server3の場合

        heartbeat_destination0 = 'server1'
        # 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 = 'server2'
        heartbeat_destination_port1 = 9694
        heartbeat_device1 = ''
       

8.3.5.6. /etc/sysconfig/pgpoolの設定

Pgpool-IIを起動時にpgpool_statusファイルを無視させたい場合、/etc/sysconfig/pgpoolの起動オプションOPTSに「-D」を追加します。

    [全サーバ]# vi /etc/sysconfig/pgpool 
    (...省略...)
    OPTS=" -D -n"
   

8.3.5.7. ログの設定

この例では、Pgpool-IIのログ出力はsyslogを利用するように設定します。

    log_destination = 'syslog'
    # Where to log
    # Valid values are combinations of stderr,
    # and syslog. Default to stderr.

    syslog_facility = 'LOCAL1'
    # Syslog local facility. Default to LOCAL0
   

全サーバではログファイルを作成します。

    [全サーバ]# mkdir /var/log/pgpool-II
    [全サーバ]# touch /var/log/pgpool-II/pgpool.log
   

次にsyslogの設定ファイルを以下のように編集します。

    [全サーバ]# vi /etc/rsyslog.conf
    ...(省略)...
    *.info;mail.none;authpriv.none;cron.none;LOCAL1.none    /var/log/messages
    LOCAL1.*                                                /var/log/pgpool-II/pgpool.log
   

また、Pgpool-IIに関して/var/log/messagesと同様のログローテーションを行うように、logrotateの設定を以下のように行います。

    [全サーバ]# vi /etc/logrotate.d/syslog
    ...(省略)...
    /var/log/messages
    /var/log/pgpool-II/pgpool.log
    /var/log/secure
   

設定が終わったら、rsyslogサービスを再起動します。

    [全サーバ]# systemctl restart rsyslog
   

8.3.5.8. PCPコマンドの設定

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

    [全サーバ]# echo 'pgpool:'`pg_md5 PCPコマンドパスワード` >> /etc/pgpool-II/pcp.conf
   

8.3.5.9. .pcppassの設定

前述のfollow_master_commandのスクリプトでパスワード入力なしでPCPコマンドを実行できるように、すべてのサーバでPgpool-IIの起動ユーザのホームディレクトリに.pcppassを作成します。

    [全サーバ]# echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass
    [全サーバ]# chmod 600 ~/.pcppass
   

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

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

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

    # pcp_recovery_node -h 192.168.137.150 -p 9898 -U pgpool -n 1
    Password: 
    pcp_recovery_node -- Command Successful

    # pcp_recovery_node -h 192.168.137.150 -p 9898 -U pgpool -n 2
    Password: 
    pcp_recovery_node -- Command Successful
   

server2とserver3のPostgreSQLがスタンバイとして起動されていることを確認します。

    # psql -h 192.168.137.150 -p 9999 -U pgpool postgres -c "show pool_nodes"
    Password for user pgpool:
    node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
    ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
    0       | server1  | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2019-08-06 11:13:17
    1       | server2  | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 | streaming         | async                  | 2019-08-06 11:13:25
    2       | server3  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2019-08-06 11:14:20
    (3 rows)
   

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

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

    # pcp_watchdog_info -h 192.168.137.150 -p 9898 -U pgpool
    Password: 
    3 YES server1:9999 Linux server1 server1

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

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

    [server1]# systemctl stop pgpool.service

    # pcp_watchdog_info -p 9898 -h 192.168.137.150 -U pgpool
    Password: 
    3 YES server2:9999 Linux server2 server2

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

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

    [server1]# systemctl start pgpool.service

    [server1]# pcp_watchdog_info -p 9898 -h 192.168.137.150 -U pgpool
    Password: 
    3 YES server2:9999 Linux server2 server2

    server2:9999 Linux server2 server2 9999 9000 4 MASTER
    server1:9999 Linux server1 server1 9999 9000 7 STANDBY
    server3:9999 Linux server3 server3 9999 9000 7 STANDBY
   

8.3.7.3. 自動フェイルオーバ

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

    # psql -h 192.168.137.150 -p 9999 -U pgpool postgres -c "show pool_nodes"
    Password for user pgpool:
    node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
    ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
    0       | server1  | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2019-08-06 11:13:17
    1       | server2  | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 | streaming         | async                  | 2019-08-06 11:13:25
    2       | server3  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2019-08-06 11:14:20
    (3 rows)
   

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

    [server1]$ pg_ctl -D /var/lib/pgsql/11/data -m immediate stop
   

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

    # psql -h 192.168.137.150 -p 9999 -U pgpool postgres -c "show pool_nodes"
    Password for user pgpool:
    node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
    ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
    0       | server1  | 5432 | down   | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2019-08-06 11:36:03
    1       | server2  | 5432 | up     | 0.333333  | primary | 0          | true              | 0                 |                   |                        | 2019-08-06 11:36:03
    2       | server3  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2019-08-06 11:36:15
    (3 rows)
   

server3が新しいプライマリserver2のスタンバイとして起動されています。

    [server3]# psql -h server3 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"
    pg_is_in_recovery 
    -------------------
    t

    [server2]# psql -h server2 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"
    pg_is_in_recovery 
    -------------------
    f

    [server2]# psql -h server2 -p 5432 -U pgpool postgres -c "select * from pg_stat_replication" -x
    -[ RECORD 1 ]----+------------------------------
    pid              | 11059
    usesysid         | 16392
    usename          | repl
    application_name | server3
    client_addr      | 192.168.137.103
    client_hostname  | 
    client_port      | 48694
    backend_start    | 2019-08-06 11:36:07.479161+09
    backend_xmin     | 
    state            | streaming
    sent_lsn         | 0/75000148
    write_lsn        | 0/75000148
    flush_lsn        | 0/75000148
    replay_lsn       | 0/75000148
    write_lag        | 
    flush_lag        | 
    replay_lag       | 
    sync_priority    | 0
    sync_state       | async
    reply_time       | 2019-08-06 11:42:59.823961+09
   

8.3.7.4. オンラインリカバリ

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

    # pcp_recovery_node -h 192.168.137.150 -p 9898 -U pgpool -n 0
    Password: 
    pcp_recovery_node -- Command Successful
   

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

    # psql -h 192.168.137.150 -p 9999 -U pgpool postgres -c "show pool_nodes"
    Password for user pgpool:
    node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
    ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
    0       | server1  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2019-08-06 11:48:05
    1       | server2  | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2019-08-06 11:36:03
    2       | server3  | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 | streaming         | async                  | 2019-08-06 11:36:15
    (3 rows)
   

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