pgpool-II環境構築

## この記事について
[pgpool-IIの使い方を考える](https://qiita.com/sankaku/items/94f2b6421f100e5f48f7)のサブページです。

## 前提
[【前提】CentOS検証環境初期設定](https://qiita.com/sankaku/items/073683cfe674cb73d5de)

上記で作成した環境のクローン（クローン時にMACアドレスは初期化）を3ノード作成して、それぞれ以下IPアドレスを設定しておきます。

※ノードを1つ完成させてから、そこからクローンを作成して残り2ノードを作成し、設定値を変更しても問題ありません。

- 192.168.56.111/24

- 192.168.56.112/24

- 192.168.56.113/24

※IPアドレス変更操作例は以下記載内容を参照してください。
[PostgreSQL環境構築](https://qiita.com/sankaku/items/9f266aaa2c745dd9fa38)

## インストール前設定

### firewalld設定

ポート単位で許可設定します。各ポートをサービス設定してから許可する手法でも問題ありません。検証環境では開放が必要なポートを手探りで進めておりまして、サービス設定は面倒なので直接ポートを許可します。

- pgpool-IIで使用するポートを許可します。

```:command
# firewall-cmd --permanent --zone=internal --add-port=9999/tcp
# firewall-cmd --permanent --zone=internal --add-port=9898/tcp
# firewall-cmd --permanent --zone=internal --add-port=9000/tcp
# firewall-cmd --permanent --zone=internal --add-port=11211/tcp
# firewall-cmd --permanent --zone=internal --add-port=9694/udp 
```

- firewalldをリロードして反映します。

```:command
# firewall-cmd --reload
```

### pgpool-IIユーザ作成とssh鍵交換

pgpool-IIユーザを作成します。yumインストール版のpgpool-II起動ユーザはデフォルトではrootです。検証環境では専用ユーザで起動することにします。

- pgpool-IIユーザを作成してパスワード設定をします。

```:command
# useradd pgpool
# passwd pgpool
```

- ssh鍵を作成します。

[【前提】CentOS検証環境初期設定](https://qiita.com/sankaku/items/073683cfe674cb73d5de)

rootユーザ向けに作成した鍵をそのまま使いまわす場合の例を以下に記します。

```:command
# cd /home/pgpool/
# cp -rp /root/.ssh ./
# chown -R pgpool:pgpool .ssh
# cd .ssh/
# vi authorized_keys
```

```:変更概要
（行末の root@localhost.localdomain を pgpool@localhost.localdomain に変更します。）
```

### ssh相互ログイン確認

PostgreSQL：4ノード、pgpool-II： 3ノードの計7ノードでrootユーザ、pgpoolユーザを使用して相互にパスワード無しでsshログインできることを確認しておきます。初回ログイン時に確認を求められないようにknown_hostは以下のように記載しておきます。
1度ログインして自動で書き込ませても構いませんが、対象が多いので面倒です。

known_hostsの設定例

```:/root/.ssh/known_hosts
localhost ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.111 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.112 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.113 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.121 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.122 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.123 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
192.168.56.124 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBA5O+wXAfO9rYsE6t8DrrV6ojxrArkmDlBg2Jk5Ce7Mxn8Bp/uHLb0wBQbuQNGjnSVOlE70bC8tZ35sFmJny+Ok=
```

### SetUID指定

- pgpoolユーザからroot権限で実行する必要のあるコマンドをSetUID指定します。

```:command
# chmod 4755 /usr/sbin/arping
# chmod 4755 /usr/sbin/ifconfig
```

### ログ出力ディレクトリ作成

poolの状態を出力するディレクトリを作成しておきます。

- 以下の通りディレクトリを作成します。

```:file-info
ディレクトリパス：/var/log/pgpool
ユーザ/グループ：pgpool:pgpool
```

### PostgreSQLのクライアント機能のみインストール

- psqlコマンド等を利用できるようにクライアント機能のみインストールしておきます。

```:command
# yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
# yum install postgresql10.x86_64
```

## pgpool-IIインストール

### pgpool-IIのyumインストール

- pgpool-II公式リポジトリからyumインストールします。（確認プロンプトにはyで答える。）

```:command
# yum install http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm
# yum install pgpool-II-pg10* 
```

### systemd起動設定のカスタマイズ

pgpool-II起動ユーザをpgpoolユーザとします。

- 設定変更の為、デフォルト設定を/etc/systemd/systemにコピーします。

```:command
# cp -p /usr/lib/systemd/system/pgpool.service /etc/systemd/system/ 
```

- pgpool.serviceを以下の通り変更します。

[Service]直下に以下を追記。

```:追記
User=pgpool
Group=pgpool 
```

### pidファイルの配備先tmpfileカスタマイズ

pgpool-II起動ユーザをpgpoolユーザとするので、pidファイルを格納するtmpフォルダのユーザグループも変更します。

（全てのPIDファイル格納先を/var/run/pgpoolとしたかったのですが、当検証環境ではpcp_socketのpidファイルを/tmp配下から移動するとpcpコマンドが正しく動作しなくなるので、pcp_socketのみ/tmpとします。）

- 設定変更の為、デフォルト設定を/etc/tmpfiles.dにコピーします。

```:command
# cp -p /usr/lib/tmpfiles.d/pgpool-II-pg10.conf /etc/tmpfiles.d/
```

- pgpool-II-pg10.confを以下の通り変更します。

```:変更前
d /var/run/pgpool 0755 root root -
```

```:変更後
d /var/run/pgpool 0755 pgpool pgpool -
x /tmp/.s.* 
```

### pgpool-IIからPostgreSQL postgresユーザへのパスワード無しログイン

pgpool-IIから死活監視等で使用する為、PostgreSLQのpostgresユーザにパスワード無しでログイン出来るようにします。

```:command
# cd /etc/pgpool-II/
# pg_md5 --md5auth --username=postgres postgres
# cat pool_passwd
```

```:(出力例)
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
```

### pcpユーザpgpool及びパスワード設定

- pcpユーザpgpoolとパスワードを設定する。

```:command
# cd /etc/pgpool-II/
# pg_md5 pgpool
```

- 出力されたmd5ハッシュ値をpcp.confに設定する。

```:追記例
# USERID:MD5PASSWD
pgpool:ba777e4c2f15c11ea8ac3be7e0440aa0
```

### pool_hba.confの設定

pgpool-II経由でPostgreSLQに接続する場合のpgpool-IIのユーザパスワードを設定します。

- pool_hba.confに以下の通り設定します。

設定ファイルパス：/etc/pgpool-II/pool_hba.conf

---

*# "local" is for Unix domain socket connections only*

|  | TYPE | DATABASE | USER | ADDRESS | METHOD | 備考 |
|:---|:---|:---|:---|:---|:---|:---|
| 維持 | local | all | all |  | trust | デフォルト |

*# IPv4 local connections:*

|  | TYPE | DATABASE | USER | ADDRESS | METHOD | 備考 |
|:---|:---|:---|:---|:---|:---|:---|
| 維持 | host | all | all | 127.0.0.1/32 | trust | デフォルト |
| 維持 | host | all | all | 192.168.56.0/24 | md5 | デフォルト |
| 削除 | host | all | all | ::1/128 | trust | デフォルト IPv6は使用しない。 |

---

### pcpパスワード無しログイン

pcpコマンドをスクリプトから自動実行する為、pcpにパスワード無しでログインできるようにしておきます。

pgpoolユーザにのみ設定しておけば十分なのですが、検証中操作の利便も考慮してrootユーザにも設定しておきます。

- rootユーザ向け

```:file-info
ファイルパス：/root/.pcppass
ユーザ/グループ：root/root
権限：600
```

```:/root/.pcppass
#hostname:port:username:password
localhost:9898:pgpool:pgpool
```

- pgpoolユーザ向け

```:file-info
ファイルパス：/home/pgpool/.pcppass
ユーザ/グループ：pgpool/pgpool
権限：600
```

```:/home/pgpool/.pcppass
#hostname:port:username:password
localhost:9898:pgpool:pgpool
```

### スクリプト配備

以下スクリプトを配備します。

- ファイルパス：/etc/pgpool-II/fail_over.sh
- ファイルパス：/etc/pgpool-II/fail_over_wrap.sh
- ファイルパス：/etc/pgpool-II/follow_master.sh
- ファイルパス：/etc/pgpool-II/follow_master_wrap.sh

ユーザ/グループ、権限周りの設定は以下の通りです。

```:file-info
ユーザ/グループ（全て共通）：pgpool/pgpool
権限（全て共通）：755
```

各スクリプトの記載内容は以下の通りです。

- fail_over.sh

処理概要 ：スタンバイのマスター昇格処理

```bash:fail_over.sh
#!/bin/bash
FALLING_ID=$1         # %d
OLDPRIMARY_ID=$2      # %P
NEWPRIMARY_HOST=$3    # %H
TRIGGER_PATH='/var/lib/pgsql/10/trigger'

if [ ${FALLING_ID} = ${OLDPRIMARY_ID} ]; then
     ssh ${NEWPRIMARY_HOST} "sudo -u postgres touch ${TRIGGER_PATH}"
fi;
exit 0;
```

- fail_over_wrap.sh

処理概要：上記処理をデバッグモードで実行してsyslog出力

エラー処理を書く手間を省く為、かつ、基本的にはめったに動く処理ではないはずなので、デバッグモードで全てのログをファイル出力させます。

```bash:fail_over_wrap.sh
#!/bin/bash
sh -x /etc/pgpool-II/fail_over.sh $1 $2 $3 &> >(/usr/bin/logger -t pgpool_script)
exit 0;
```

上記程度の処理であればpgpool.confからbash処理を呼び出すところに書けるかなと思って当初そのようにしてみたのですが、うまいこと行かなかったので、pgpool.confからfail_over_wrap.shを呼び出して、fail_over.shが動くという感じにしました。

- follow_master.sh

処理概要 ：マスター昇格対象外スタンバイ向け上位マスター切替処理

```bash:follow_master.sh
#!/bin/bash
FALLING_ID=$1         # %d
OLDPRIMARY_ID=$2      # %P
OLDPRIMARY_HOST=$3          # %h
NEWPRIMARY_HOST=$4          # %H

NODES[0]='192.168.56.121'
NODES[1]='192.168.56.122'
NODES[2]='192.168.56.123'
NODES[3]='192.168.56.124'

if [ ${FALLING_ID} = ${OLDPRIMARY_ID} ]; then

  i=0
  for node in "${NODES[@]}"; do
    if [ ${node} != ${OLDPRIMARY_HOST} ] && [ ${node} != ${NEWPRIMARY_HOST} ]; then

      ssh ${node} 'sudo systemctl stop postgresql-10'
      ssh ${node} 'sudo rm -rf /var/lib/pgsql/10/data/'
      ssh ${node} 'sudo mkdir /var/lib/pgsql/10/data'
      ssh ${node} 'sudo chmod -R 700 /var/lib/pgsql/10/data'
      ssh ${node} 'sudo chown -R postgres:postgres /var/lib/pgsql/10/data'
      ssh ${node} "sudo -u postgres sh -c 'cd;pg_basebackup -h ${NEWPRIMARY_HOST} -D /var/lib/pgsql/10/data/ -R -U repuser'"
      ssh ${node} "sudo -u postgres sh -c 'echo \"trigger_file = '\'/var/lib/pgsql/10/trigger\''\" >> /var/lib/pgsql/10/data/recovery.conf'"
      ssh ${node} 'sudo systemctl start postgresql-10'
      pcp_attach_node -U pgpool -w ${i}

    fi
    i=$((++i))
  done

fi;
exit 0;
```

- follow_master_wrap.sh

処理概要：上記処理をデバッグモードで実行してsyslog出力

当処理の目的はfail_over_wrap.shと同様です。

```bash:follow_master_wrap.sh
#!/bin/bash
sh -x /etc/pgpool-II/follow_master.sh $1 $2 $3 $4 &> >(/usr/bin/logger -t pgpool_script)
exit 0;
```

### pgpool.conf設定変更

- pgpool.confに以下の通り設定します。

設定ファイルパス：/etc/pgpool-II/pgpool.conf

| 項目 | デフォルト値 | 192.168.56.111設定値 | 192.168.56.112設定値 | 192.168.56.113設定値 | 備考 |
|:---|:---|:---|:---|:---|:---|
| listen_addresses | 'localhost' | '*' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| socket_dir | '/tmp' | '/var/run/pgpool' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| pcp_socket_dir | '/tmp' | デフォルトと同じ | 192.168.56.111と同じ | 192.168.56.111と同じ | 検証環境ではpcp_socket_dirを変更するとpcpコマンドが動作しないため、デフォルトから変更しません。 |
| backend_hostname0 | 'localhost' | '192.168.56.121' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_port0 | 5432 | デフォルトと同じ | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_weight0 | 1 | デフォルトと同じ | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_data_directory0 | '/var/lib/pgsql/data' | '/var/lib/pgsql/10/data' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_flag0 | 'ALLOW_TO_FAILOVER' | デフォルトと同じ | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_hostname1 | 無し | '192.168.56.122' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_port1 | 無し | 5432 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_weight1 | 無し | 1 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_data_directory1 | 無し | '/var/lib/pgsql/10/data' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_flag1 | 無し | 'ALLOW_TO_FAILOVER' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_hostname2 | 無し | '192.168.56.123' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_port2 | 無し | 5432 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_weight2 | 無し | 1 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_data_directory2 | 無し | '/var/lib/pgsql/10/data' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_flag2 | 無し | 'ALLOW_TO_FAILOVER' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_hostname3 | 無し | '192.168.56.124' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_port3 | 無し | 5432 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_weight3 | 無し | 1 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_data_directory3 | 無し | '/var/lib/pgsql/10/data' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| backend_flag3 | 無し | 'ALLOW_TO_FAILOVER' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| enable_pool_hba | off | on | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| load_balance_mode | off | on | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| master_slave_mode | off | on | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| sr_check_user | 'nobody' | 'repuser' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| sr_check_password | '' | 'repuser' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| follow_master_command | '' | '/etc/pgpool-II/follow_master_wrap.sh %d %P %h %H' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| health_check_period | 0 | 5 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| health_check_user | 'nobody' | 'postgres' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| health_check_password | '' | 'postgres' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| health_check_max_retries | 0 | 3 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| health_check_retry_delay | 1 | 10 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| failover_command | '' | '/etc/pgpool-II/fail_over_wrap.sh %d %P %H' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| use_watchdog | off | on | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| trusted_servers | '' | '192.168.56.1' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| wd_hostname | '' | '192.168.56.111' | '192.168.56.112' | '192.168.56.113' |  |
| wd_priority | 1 | 3 | 2 | 1 |  |
| wd_authkey | '' | 'pgpoolwd1' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| wd_ipc_socket_dir | '/tmp' | '/var/run/pgpool' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| delegate_IP | '' | '192.168.56.110' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| if_up_cmd | 'ip addr add $_IP_$/24 dev eth0 label eth0:0' | 'ip addr add $_IP_$/24 dev bond0 label bond0:0' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| if_down_cmd | 'ip addr del $_IP_$/24 dev eth0' | 'ip addr del $_IP_$/24 dev bond0' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| heartbeat_destination0 | 'host0_ip1' | '192.168.56.112' | '192.168.56.111' | '192.168.56.111' |  |
| heartbeat_destination_port0 | 9694 | デフォルトと同じ | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| heartbeat_device0 | '' | 'bond0' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| heartbeat_destination1 | 無し | '192.168.56.113' | '192.168.56.113' | '192.168.56.112' |  |
| heartbeat_destination_port1 | 無し | 9694 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| heartbeat_device1 | 無し | 'bond0' | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| other_pgpool_hostname0 | 無し | '192.168.56.112' | '192.168.56.111' | '192.168.56.111' |  |
| other_pgpool_port0 | 無し | 9999 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| other_wd_port0 | 無し | 9000 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| other_pgpool_hostname1 | 無し | '192.168.56.113' | '192.168.56.113' | '192.168.56.112' |  |
| other_pgpool_port1 | 無し | 9999 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |
| other_wd_port1 | 無し | 9000 | 192.168.56.111と同じ | 192.168.56.111と同じ |  |

### ディレクトリのユーザ権限変更

- /etc/pgpool-II配下全てのユーザパスワードを以下の通り変更します。

```:file-info
ディレクトリパス（配下も含めて全て）：/etc/pgpool-II
ユーザ/グループ：pgool/pgpool
```

```:command
# chown -R pgpool:pgpool /etc/pgpool-II
```

### pgpool-II起動及び自動起動設定

- pgpool-IIを起動します。また、自動起動設定しておきます。

```:command
# systemctl status pgpool
# systemctl enable pgpool
# systemctl start pgpool
# systemctl status pgpool
```