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

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

この設定例ではPostgreSQL 16を使っていますが、 各種スクリプトはPostgreSQL 10以降での動作確認を行っています。

8.2.1. 前提条件

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

8.2.2. 全体構成

今回は、Linuxサーバを3台用意し、それぞれのホスト名はserver1server2server3とします。 使用するOSはすべてRocky Linux 8とします。 それぞれのサーバにPostgreSQLPgpool-IIをインストールします。 3台のPostgreSQLがストリーミングレプリケーション構成になります。全体構成図は以下の通りです。

図 8-1. 全体構成図

注意: 「Leader」「Standby」「Primary」「Standby」といった役割は固定されているものではなく、運用と共に変化することがあります。

表 8-2. ホスト名とIPアドレス

ホスト名IPアドレス仮想IP
server1192.168.100.51192.168.100.50
server2192.168.100.52
server3192.168.100.53

表 8-3. PostgreSQLのバージョンと設定情報

項目説明
PostgreSQLバージョン16.0-
ポート番号5432-
$PGDATA/var/lib/pgsql/16/data-
アーカイブモード有効/var/lib/pgsql/archivedir
レプリケーションスロット有効本設定例では、フェイルオーバやオンラインリカバリの時に実行されるスクリプトで、自動的にレプリケーションスロットを削除/作成しています。 これらのスクリプトでは、backend_hostnameXに指定したホスト名をレプリケーションスロット名として使用しています。 スクリプトの詳細については表8-5を参照してください。
同期/非同期レプリケーション非同期-

表 8-4. Pgpool-IIのバージョンと設定情報

項目説明
Pgpool-IIバージョン4.5.0-
ポート番号9999Pgpool-IIが接続を受け付けるポート番号
9898PCPプロセスが接続を受け付けるポート番号
9000Watchdogが接続を受け付けるポート番号
9694Watchdogのハートビート信号を受信するUDPポート番号
設定ファイル/etc/pgpool-II/pgpool.confPgpool-IIの設定ファイル
Pgpool-II起動ユーザpostgres (Pgpool-II 4.1以降)Pgpool-II 4.0以前のバージョンでは、デフォルトではrootでPgpool-IIを起動する
Pgpool-II動作モードストリーミングレプリケーションモード-
Watchdog機能有効ハートビート方式

表 8-5. RPMに含まれるサンプルスクリプト

機能スクリプト説明
自動フェイルオーバ/etc/pgpool-II/sample_scripts/failover.sh.sampleフェイルオーバを実行するスクリプト。failover_commandで使用します。
/etc/pgpool-II/sample_scripts/follow_primary.sh.sample上記フェイルオーバスクリプトが実行された後に、新しいプライマリサーバとスタンバイサーバを同期させるスクリプト。follow_primary_commandで使用します。 PostgreSQLサーバが2台の場合はこのスクリプトの設定は不要です。
オンラインリカバリ/etc/pgpool-II/sample_scripts/recovery_1st_stage.sampleスタンバイサーバをリカバリするスクリプト。recovery_1st_stage_commandで使用します。
/etc/pgpool-II/sample_scripts/pgpool_remote_start.sample上記recovery_1st_stage_commandが実行された後に、スタンバイノードを起動させるスクリプト。
Watchdog/etc/pgpool-II/sample_scripts/escalation.sh.sample 任意の設定。Pgpool-IIのリーダー/スタンバイ切り替え時に、旧Watchdogリーダープロセスの異常終了によって旧Watchdogリーダーで仮想IPが起動したまま、新しいリーダーノードで仮想IPが起動されることを防ぐために、新しいリーダー以外で起動している仮想IPを停止するスクリプト。wd_escalation_commandで使用します。

上記各種スクリプトはRPMパッケージに同梱されており、必要に応じてカスタマイズできます。

8.2.3. インストール

この設定例ではYUMを使用してPostgreSQLおよびPgpool-IIをインストールします。

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

[全サーバ]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[全サーバ]# dnf -qy module disable postgresql
[全サーバ]# dnf install -y postgresql16-server
  

Pgpool-IIのインストールはPgpool-II開発コミュニティが提供するYumリポジトリを用いてインストールします。

Pgpool-II関連のパッケージはPostgreSQLコミュニティのリポジトリにもあるため、 PostgreSQLコミュニティのリポジトリからPgpool-IIをインストールしないように/etc/yum.repos.d/pgdg-redhat-all.repoにexclude設定を追加します。

[全サーバ]# vi /etc/yum.repos.d/pgdg-redhat-all.repo
  

以下は、/etc/yum.repos.d/pgdg-redhat-all.repoの設定例です。

[pgdg-common]
...
exclude=pgpool*


[pgdg16]
...
exclude=pgpool*

[pgdg15]
...
exclude=pgpool*

[pgdg14]
...
exclude=pgpool*

[pgdg13]
...
exclude=pgpool*

[pgdg12]
...
exclude=pgpool*

[pgdg11]
...
exclude=pgpool*
  

Pgpool-IIをインストールします。

[全サーバ]# dnf install -y https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-8-x86_64/pgpool-II-release-4.5-1.noarch.rpm
[全サーバ]# dnf install -y pgpool-II-pg16-*
  

8.2.4. 事前設定

Pgpool-IIの設定の前に、以下の設定を行ってください。

8.2.4.1. プライマリのセットアップ

PostgreSQLプライマリサーバでストリーミングレプリケーションの設定を行います。 この設定の例ではアーカイブリカバリを行うように設定します。

まず、すべてのサーバにてWALを格納するディレクトリ/var/lib/pgsql/archivedirを事前に作成します。この設定例では、プライマリサーバのみでWALアーカイブをローカルで実施します。

[全サーバ]# su - postgres
[全サーバ]$ mkdir /var/lib/pgsql/archivedir
    

server1PostgreSQLプライマリサーバの初期化を行います。

[server1]# su - postgres
[server1]$ /usr/pgsql-16/bin/initdb -D $PGDATA
    

次にserver1で設定ファイル$PGDATA/postgresql.confを以下のように編集します。 pg_rewindを使うためにwal_log_hintsを有効にしておきます。 プライマリが後でスタンバイになる可能性があるので、hot_standby = onにしておきます。

listen_addresses = '*'
archive_mode = on
archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
    

server1でPostgreSQLプライマリを起動します。

[server1]# su - postgres
[server1]$ /usr/pgsql-16/bin/pg_ctl start -D $PGDATA
    

8.2.4.2. スタンバイのセットアップ

スタンバイサーバをセットアップする方法としては、次のような複数の方法があります。

  • スタンバイでpg_basebackupを実行して、プライマリのデータディレクトリのバックアップを生成します。(この方法を利用する場合は、手動でレプリケーションスロットを作成してください。)

  • Pgpool-IIのオンラインリカバリ機能(項5.11)を使用して、スタンバイサーバを自動的にセットアップします。

この設定例では、Pgpool-IIの構成が完了した後に、項8.2.8.2Pgpool-IIのオンラインリカバリを使用してスタンバイサーバをセットアップします。

8.2.4.3. PostgreSQLユーザの作成

Pgpool-IIPostgreSQLのユーザを使用してPostgreSQLへ接続し、ヘルスチェックやレプリケーション遅延チェックを行います。 セキュリティ上の理由で、この設定例ではスーパーユーザを使わず、レプリケーション遅延チェックとヘルスチェック専用のユーザpgpoolを作成します。 また、レプリケーション専用のユーザreplを作成します。 オンラインリカバリ機能を利用するにはスーパーユーザ権限が必要なので、ここではpostgresユーザを使用します。

Pgpool-II 4.0からscram-sha-256認証が利用できるようになりました。 この設定例では、scram-sha-256認証方式を利用します。 まず、password_encryption = 'scram-sha-256'に変更してから、ユーザを登録します。

表 8-6. ユーザ

ユーザ名パスワード備考
replreplPostgreSQLのレプリケーション専用ユーザ
pgpoolpgpool Pgpool-IIのレプリケーション遅延チェック(sr_check_user)、 ヘルスチェック専用ユーザ(health_check_user)
postgrespostgresオンラインリカバリの実行ユーザ
[server1]# psql -U postgres -p 5432
postgres=# SET password_encryption = 'scram-sha-256';
postgres=# CREATE ROLE pgpool WITH LOGIN;
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
postgres=# \password pgpool
postgres=# \password repl
postgres=# \password postgres
    

SHOW POOL_NODESコマンドでreplication_statカラムとreplication_sync_stateカラムを表示するには、pgpoolユーザはPostgreSQLのスーパーユーザであるか、pg_monitorグループに所属する必要があります(Pgpool-II 4.1以降)。 以下のコマンドでpgpoolユーザをそのグループに所属させます。

GRANT pg_monitor TO pgpool;
    

注意: detach_false_primary(Pgpool-II 4.1以降)を利用する予定がある場合、"pgpool" ロールはPostgreSQLのスーパーユーザであるか、pg_monitorグループに所属する必要があります。

Pgpool-IIサーバとPostgreSQLバックエンドサーバが同じサブネットワークにあることを想定し、各ユーザがscram-sha-256認証方式で接続するように、pg_hba.confを編集しておきます。

host    all             pgpool          samenet                 scram-sha-256
host    all             postgres        samenet                 scram-sha-256
host    replication     all             samenet                 scram-sha-256
    

8.2.4.4. SSH公開鍵認証の設定

自動フェイルオーバ、オンラインリカバリ機能を利用するには、すべてのPgpool-IIノード間でpostgresユーザ(Pgpool-IIのデフォルトの起動ユーザ。Pgpool-II 4.0以前、デフォルトの起動ユーザはroot)として双方向にSSH公開鍵認証(パスワードなし)で接続できるように設定する必要があります。

まず、全サーバで以下のコマンドを実行し、SSH鍵ファイルを作成します。 この設定例では生成される鍵ファイル名はid_rsa_pgpoolとします。

[全サーバ]# su - postgres
[全サーバ]$ mkdir ~/.ssh
[全サーバ]$ chmod 700 ~/.ssh
[全サーバ]$ cd ~/.ssh
[全サーバ]$ ssh-keygen -t rsa -f id_rsa_pgpool
    

次に、公開鍵id_rsa_pgpool.pubを各サーバの/var/lib/pgsql/.ssh/authorized_keysファイルに追加します。

設定後、postgresユーザでssh postgres@serverX -i ~/.ssh/id_rsa_pgpoolコマンドを実行し、パスワードなしでログインできることを確認してください。

注意: 公開鍵認証によるログインに失敗した場合には、以下を確認してください。

  • /etc/ssh/sshd_configで公開鍵認証が許可されていることを確認します。

PubkeyAuthentication yes
      

  • SELinuxを有効化している場合は、SSH公開鍵認証(パスワードなし)が失敗する可能性があるので、すべてのサーバで以下のコマンドを実行する必要があります。

[全サーバ]# su - postgres
[全サーバ]$ restorecon -Rv ~/.ssh
      

8.2.4.5. .pgpassの作成

パスワード入力なしで、ストリーミングレプリケーションやpg_rewindを実行するために、すべてのサーバでpostgresユーザのホームディレクトリ/var/lib/pgsql.pgpassを作成し、パーミッションを600に設定しておきます。

[全サーバ]# su - postgres
[全サーバ]$ vi /var/lib/pgsql/.pgpass
(以下を追加)
server1:5432:replication:repl:<replユーザのパスワード>
server2:5432:replication:repl:<replユーザのパスワード>
server3:5432:replication:repl:<replユーザのパスワード>
server1:5432:postgres:postgres:<postgresユーザのパスワード>
server2:5432:postgres:postgres:<postgresユーザのパスワード>
server3:5432:postgres:postgres:<postgresユーザのパスワード>
[全サーバ]$ chmod 600  /var/lib/pgsql/.pgpass
    

8.2.4.6. firewallの設定

Pgpool-IIPostgreSQLに接続する際には、ファイアーウォールによって目的のポートが開けられていなければなりません。 Rocky Linux 8/RHEL 8の場合、以下のように設定します。

[全サーバ]# firewall-cmd --permanent --zone=public --add-service=postgresql
[全サーバ]# firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp  --add-port=9694/udp
[全サーバ]# firewall-cmd --reload
   

8.2.5. pgpool_node_idファイルの作成

Pgpool-II 4.2以降、すべての設定パラメータがすべてのホストで同一になりました。 Watchdog機能が有効になっている場合、どの設定がどのホストであるかを区別するには、 pgpool_node_idファイルの設定が必要になります。 pgpool_node_idファイルを作成し、 そのファイルにpgpool(watchdog)ホストを識別するためのノード番号(0、1、2など)を追加します。

8.2.6. PCP接続認証の設定

PCPコマンドを使用するには、username:encryptedpassword形式のPCPユーザ名とmd5暗号化パスワードをpcp.confに登録する必要があります。

この例では、PCPユーザ名をpgpool、パスワードをpgpool_passwordに設定します。 以下のように、pg_md5を使用して、pgpoolユーザの暗号化されたパスワードエントリを作成します。

[全サーバ]# echo 'pgpool:'`pg_md5 pgpool_password` >> /etc/pgpool-II/pcp.conf

[全サーバ]# cat /etc/pgpool-II/pcp.conf
# USERID:MD5PASSWD
pgpool:4aa0cb9673e84b06d4c8a848c80eb5d0
  

8.2.7. Pgpool-IIの設定

RPMからインストールした場合、Pgpool-IIの設定ファイル pgpool.conf/etc/pgpool-IIにあります。

Pgpool-II 4.2以降、すべての設定パラメーターがすべてのホストで同一になったので、 どれか一つのノード上でpgpool.confを編集し、 編集したpgpool.confファイルを他のpgpoolノードにコピーすれば良いです。

8.2.7.1. クラスタリングモード

Pgpool-IIにはいくつかのクラスタリングモードがあります。 クラスタリングモードの設定にはbackend_clustering_modeを使用します。 今回の設定例では、ストリーミングレプリケーションモードを設定します。

backend_clustering_mode = 'streaming_replication'
   

8.2.7.2. listen_addresses

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

listen_addresses = '*'
pcp_listen_addresses = '*'
   

8.2.7.3. port

Pgpool-IIが接続を受け付けるために監視するポート番号を指定します。

port = 9999
   

8.2.7.4. ストリーミングレプリケーションのチェック

レプリケーションの遅延チェックユーザsr_check_userpgpoolユーザを設定します。 この設定例では、sr_check_passwordpgpool.confに指定せず、 pool_passwdファイルに作成します。 作成方法については後述の項8.2.7.9を参照ください。 Pgpool-II 4.0から、sr_check_passwordが空の場合、 Pgpool-IIは空のパスワードを使用する前に まずpool_passwdファイルからsr_check_userに 指定したユーザのパスワードを取得できるか試みます。

sr_check_user = 'pgpool'
sr_check_password = ''
   

8.2.7.5. ヘルスチェック

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

health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
   

8.2.7.6. バックエンドの設定

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

# - Backend Connection Settings -

backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/16/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/16/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

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

SHOW POOL_NODESコマンドでPgpool-II 4.1で追加された replication_statカラムとreplication_sync_stateカラムを表示するには、 backend_application_nameパラメータを設定する必要があります(Pgpool-II 4.1以降)。 ここではそれぞれのホスト名を設定します。 backend_application_nameXに設定された値が、primary_conninfoapplication_nameに設定された値と一致していることを確認してください。

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

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

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

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

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

注意: %N%SPgpool-II 4.1で追加された引数です。 Pgpool-II 4.0または以前のバージョンを利用している場合、 これらの引数を指定できないので、ご注意ください。

サンプルスクリプトfailover.sh及び follow_primary.sh/etc/pgpool-II/配下にインストールされていますので、これらのファイルをコピーして作成します。

[全サーバ]# cp -p /etc/pgpool-II/sample_scripts/failover.sh.sample /etc/pgpool-II/failover.sh
[全サーバ]# cp -p /etc/pgpool-II/sample_scripts/follow_primary.sh.sample /etc/pgpool-II/follow_primary.sh
[全サーバ]# chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
   

基本的にはPGHOMEを環境に合わせて変更すれば、動作します。

[全サーバ]# vi /etc/pgpool-II/failover.sh
...
PGHOME=/usr/pgsql-16
...

[全サーバ]# vi /etc/pgpool-II/follow_primary.sh
...
PGHOME=/usr/pgsql-16
...
   

follow_primary.shPCP_USERに指定されているユーザがpcp.confに登録されていることを確認してください。 この設定例では、前述の項8.2.6セクションですでに登録済みです。

# cat /etc/pgpool-II/follow_primary.sh
...
PCP_USER=pgpool
...
   

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

この例では、PCPユーザがpgpoolで、パスワードがpgpool_passwordとします。

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

注意: follow_primary.shスクリプトはテーブルスペースに対応していません。 テーブルスペースを使っている場合は、スクリプトを自分で変更する必要があります。

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

続いて、オンラインリカバリを実行するPostgreSQLユーザ及びオンラインリカバリ時に呼び出されるスクリプトを設定します。 オンラインリカバリで実行されるpgpool_recovery関数は PostgreSQLのスーパーユーザ権限が必要なため、recovery_userスーパーユーザを指定しなければなりません。 ここでは、postgresユーザを指定します。

この設定例ではrecovery_passwordを空のままにし、pool_passwdにエントリを作成します。pool_passwdにエントリを作成する方法については、項8.2.7.9を参照してください。

recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
   

オンラインリカバリ用のサンプルスクリプトrecovery_1st_stage 及びpgpool_remote_start/etc/pgpool-II/配下にインストールされていますので、 これらのファイルをプライマリサーバ(server1)のデータベースクラスタ配下に配置します。

[server1]# cp -p /etc/pgpool-II/sample_scripts/recovery_1st_stage.sample /var/lib/pgsql/16/data/recovery_1st_stage
[server1]# cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /var/lib/pgsql/16/data/pgpool_remote_start
[server1]# chown postgres:postgres /var/lib/pgsql/16/data/{recovery_1st_stage,pgpool_remote_start}
   

基本的にはPGHOMEを環境に合わせて変更すれば、動作します。

[server1]# vi /var/lib/pgsql/16/data/recovery_1st_stage
...
PGHOME=/usr/pgsql-16
...

[server1]# vi /var/lib/pgsql/16/data/pgpool_remote_start
...
PGHOME=/usr/pgsql-16
...
   

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

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

注意: recovery_1st_stageスクリプトはテーブルスペースに対応していません。 テーブルスペースを使っている場合は、スクリプトを自分で変更する必要があります。

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

事前設定の章で、 Pgpool-IIPostgreSQLの間に 認証方式を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のフォーマットはPostgreSQLpg_hba.confとほとんど同じです。 pgpoolpostgresユーザの認証方式をscram-sha-256に設定します。 この設定例では、Pgpool-IIに接続しているアプリケーションが同じサブネット内にあると想定しています。

host    all         pgpool           samenet          scram-sha-256
host    all         postgres         samenet          scram-sha-256
   

注意: Pgpool-II 4.0の場合、pgpool.confファイル内のhealth_check_passwordsr_check_passwordwd_lifecheck_passwordrecovery_passwordにはAES256暗号化形式、平文形式しか指定できないので、ご注意ください。

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

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

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

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

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

8.2.7.10. Watchdogの設定

デフォルトではWatchdog機能が無効になっているため、Watchdogを有効にします。

use_watchdog = on
   

Pgpool-IIリーダーノードが立ち上げる仮想IPをdelegate_ipに指定します。 仮想IPはまだ使われていないIPアドレスを指定してください。

delegate_ip = '192.168.100.50'
   

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

注意: RPMからインストールした場合、postgresユーザがパスワードなしに sudoを介してip/arpingを実行できるように設定済みです。

postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping
     

ここでは、sudoを介して実行するように設定します。

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'
    

注意: /etc/sudoersで「Defaults requiretty」を設定している場合は、 Pgpool-IIの起動ユーザがttyなしで if_up_cmdif_down_cmd 及びarping_cmdコマンドを実行できるように設定する必要があります。

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

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

Watchdogが稼働するサーバ情報を設定しておきます。 pgpool_portXには項8.2.7.3portに設定されているポート番号を指定します。

hostname0 = 'server1'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'server2'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'server3'
wd_port2 = 9000
pgpool_port2 = 9999
   

Watchdog死活監視の設定では、 死活監視の方法を指定するwd_lifecheck_method、 監視間隔(秒)を指定するwd_intervalを設定します。 この設定例では、死活監視の方法はheartbeatを用います。

wd_lifecheck_method = 'heartbeat'
wd_interval = 10
   

wd_lifecheck_methodheartbeatに設定されている場合、 heartbeat通信用の各Pgpool-IIサーバ情報を設定しておきます。

heartbeat_hostname0 = 'server1'
heartbeat_port0 = 9694
heartbeat_device0 = ''

heartbeat_hostname1 = 'server2'
heartbeat_port1 = 9694
heartbeat_device1 = ''

heartbeat_hostname2 = 'server3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
   

wd_lifecheck_methodheartbeatに設定されている場合、 障害と判断する秒数を指定するwd_heartbeat_deadtime、 ハートビート信号の送信間隔(秒)を指定するwd_heartbeat_keepaliveを設定します。

wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
   

Watchdogプロセスが異常終了した場合に、 旧リーダーノードで仮想IPが残ったまま、新しいリーダーノードで同じ仮想IPが起動されてしまう可能性があります。 それを防ぐためにwd_escalation_commandに、新しいリーダー以外のPgpool-IIノードで仮想IPを停止するスクリプトを設定します。 この設定は任意の設定です。

wd_escalation_command = '/etc/pgpool-II/escalation.sh'
    

サンプルスクリプトescalation.sh/etc/pgpool-II/配下にインストールされています。

[全サーバ]# cp -p /etc/pgpool-II/sample_scripts/escalation.sh.sample /etc/pgpool-II/escalation.sh
[全サーバ]# chown postgres:postgres /etc/pgpool-II/escalation.sh
    

サーバのホスト名、仮想IP、仮想IPを設定するネットワークインターフェース名を環境に合わせて変更してください。

[全サーバ]# vi /etc/pgpool-II/escalation.sh
...
PGPOOLS=(server1 server2 server3)
VIP=192.168.100.50
DEVICE=enp0s8
...
   

注意: Watchdogノードの数が偶数の場合は、 enable_consensus_with_half_votesパラメータをonにする必要があります。

注意: use_watchdog = onの場合は、Pgpool-IIノード番号をpgpool_node_idに設定する必要があります。 詳細は項8.2.5を参照ください。

8.2.7.11. ログの設定

Pgpool-II 4.2以降、ログ収集プロセスが追加されました。 ここでは、ログ収集プロセス(logging_collector)を有効にします。

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
   

すべてのサーバでログファイルを格納するディレクトリを作成します。

[全サーバ]# mkdir /var/log/pgpool_log/
[全サーバ]# chown postgres:postgres /var/log/pgpool_log/
   

これで、server1でのpgpool.confの設定は完了です。 最後に、server1pgpool.confを他のサーバserver2server3にコピーします。

[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server2:/etc/pgpool-II/pgpool.conf
[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server3:/etc/pgpool-II/pgpool.conf
   

8.2.8. 動作確認

これから、動作確認を行います。

8.2.8.1. Pgpool-IIの起動と停止

  • Pgpool-IIの起動

    Pgpool-IIを起動します。

    Pgpool-IIを起動する前に、PostgreSQLをあらかじめ起動する必要があります。 PostgreSQLプライマリサーバがまだ起動していない場合は、まず次のコマンドを実行してPostgreSQL起動します。

    [server1]# su - postgres
    [server1]$ /usr/pgsql-16/bin/pg_ctl start -D $PGDATA
         

    server1server2およびserver3で以下のコマンドを実行し、Pgpool-IIを起動します。

    [全サーバ]# systemctl start pgpool.service
         
  • Pgpool-IIの停止

    Pgpool-IIを停止するときに、以下のコマンドを実行してPgpool-IIを停止します。

    また、PostgreSQLを停止する場合は、Pgpool-IIを先に停止する必要があります。 Pgpool-IIが起動している状態でPostgreSQLを停止する、想定外のフェイルオーバが起きてしまうので、ご注意ください。

    # systemctl stop pgpool.service
         

8.2.8.2. PostgreSQLスタンバイサーバの作成

まず、Pgpool-IIのオンラインリカバリ機能を利用し、server2server3をスタンバイサーバとして構築し、Pgpool-II管理下に追加します。

仮想IP経由でPgpool-IIに接続し、バックエンドノードのステータスを確認します。 下記の結果のように、プライマリサーバがserver1で起動しており、server2server3は「down」状態になっています。

[いずれかのサーバ]# psql -h 192.168.100.50 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool: 
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | up        | 0.333333  | primary | primary | 0          | true              | 0                 |                   |                        | 2023-11-10 15:30:14
 1       | server2  | 5432 | down   | down      | 0.333333  | standby | unknown | 0          | false             | 0                 |                   |                        | 2023-11-10 15:30:14
 2       | server3  | 5432 | down   | down      | 0.333333  | standby | unknown | 0          | false             | 0                 |                   |                        | 2023-11-10 15:30:14
(3 rows)
   

オンラインリカバリ機能を使用するには、pcp_recovery_nodeコマンドを実行します。 pcp_recovery_nodeコマンドでrecovery_1st_stagepgpool_remote_startスクリプトが実行されるので(recovery_1st_stagerecovery_1st_stage_commandパラメータに設定したスクリプト)、この2つのスクリプトが現在稼働中のプライマリサーバserver1のデータベースクラスタに存在することを確認してください。

[いずれかのサーバ]# pcp_recovery_node -h 192.168.100.50 -p 9898 -U pgpool -n 1 -W
Password:
pcp_recovery_node -- Command Successful

[いずれかのサーバ]# pcp_recovery_node -h 192.168.100.50 -p 9898 -U pgpool -n 2 -W
Password:
pcp_recovery_node -- Command Successful
   

server2server3PostgreSQLがスタンバイとして起動していることを確認します。

[いずれかのサーバ]# psql -h 192.168.100.50 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2023-11-10 15:30:14
 1       | server2  | 5432 | up     | up        | 0.333333  | standby | standby | 0          | true              | 0                 | streaming         | async                  | 2023-11-10 16:32:33
 2       | server3  | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2023-11-10 16:33:08
(3 rows)
   

8.2.8.3. Watchdogリーダー/スタンバイの切り替え

pcp_watchdog_infoPgpool-IIWatchdogの情報を確認します。 最初に起動したPgpool-IIがリーダーになります。

[いずれかのサーバ]# pcp_watchdog_info -h 192.168.100.50 -p 9898 -U pgpool -W
Password:
3 3 YES server1:9999 Linux server1 server1

server1:9999 Linux server1 server1 9999 9000 4 LEADER 0 MEMBER  # 最初に起動したPgpool-IIがリーダーになる
server2:9999 Linux server2 server2 9999 9000 7 STANDBY 0 MEMBER # スタンバイとして稼働している
server3:9999 Linux server3 server3 9999 9000 7 STANDBY 0 MEMBER # スタンバイとして稼働している
   

リーダーであるserver1Pgpool-IIを停止し、server2またはserver3のどちらかがリーダーになることを確認します。 server1Pgpool-IIを停止するにはPgpool-IIを停止するか、マシンをシャットダウンします。ここでは、Pgpool-IIを停止します。

[server1]# systemctl stop pgpool.service

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

server2:9999 Linux server2 server2 9999 9000 4 LEADER 0 MEMBER    # server2がリーダーに昇格
server1:9999 Linux server1 server1 9999 9000 10 SHUTDOWN 0 MEMBER # server1が停止している
server3:9999 Linux server3 server3 9999 9000 7 STANDBY 0 MEMBER   # server3がタンバイとして稼働している
   

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

[server1]# systemctl start pgpool.service

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

server2:9999 Linux server2 server2 9999 9000 4 LEADER 0 MEMBER
server1:9999 Linux server1 server1 9999 9000 7 STANDBY 0 MEMBER
server3:9999 Linux server3 server3 9999 9000 7 STANDBY 0 MEMBER
   

8.2.8.4. 自動フェイルオーバ

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

[いずれかのサーバ]# psql -h 192.168.100.50 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2023-11-10 15:30:14
 1       | server2  | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2023-11-10 16:32:33
 2       | server3  | 5432 | up     | up        | 0.333333  | standby | standby | 0          | true              | 0                 | streaming         | async                  | 2023-11-10 16:33:08
(3 rows)
   

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

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

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

[いずれかのサーバ]# psql -h 192.168.100.50 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool:
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | down   | down      | 0.333333  | standby | unknown | 0          | false             | 0                 |                   |                        | 2023-11-10 17:05:40
 1       | server2  | 5432 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2023-11-10 17:05:40
 2       | server3  | 5432 | up     | up        | 0.333333  | standby | standby | 0          | true              | 0                 | streaming         | async                  | 2023-11-10 17:05:51
(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              | 7198
usesysid         | 16385
usename          | repl
application_name | server3
client_addr      | 192.168.100.53
client_hostname  |
client_port      | 40916
backend_start    | 2023-11-10 17:10:03.067241+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/12000260
write_lsn        | 0/12000260
flush_lsn        | 0/12000260
replay_lsn       | 0/12000260
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-11-10 17:17:23.886477+00
   

8.2.8.5. オンラインリカバリ

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

[いずれかのサーバ]# pcp_recovery_node -h 192.168.100.50 -p 9898 -U pgpool -n 0 -W
Password:
pcp_recovery_node -- Command Successful
   

server1がスタンバイとして起動していることを確認します。

[any server]# psql -h 192.168.100.50 -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     | up        | 0.333333  | standby | standby | 0          | true              | 0                 | streaming         | async                  | 2023-11-10 17:22:03
 1       | server2  | 5432 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2023-11-10 17:05:40
 2       | server3  | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2023-11-10 17:05:51
(3 rows)
   

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