8.3. ネイティブレプリケーション/スナップショットアイソレーションモードの構築の例

8.3.3.1. postgresユーザパスワードの設定

全サーバで以下のコマンドを実行し、postgresユーザのパスワードの設定を行います。

[全サーバ]# passwd postgres
   

8.3.3.2. SSHの設定

オンラインリカバリ機能を利用するには、すべてのサーバにパスワードなしでSSH接続できるように設定する必要があります。 全サーバで以下のコマンドを実行し、SSHの設定を行います。 生成される鍵ファイル名はid_rsa_pgpoolとします。

[全サーバ]# su - postgres
[全サーバ]$ cd ~/.ssh
[全サーバ]$ ssh-keygen -t rsa -f id_rsa_pgpool
[全サーバ]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
[全サーバ]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
[全サーバ]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3
   

設定後、postgresユーザで、 ssh postgres@serverX -i ~/.ssh/id_rsa_pgpoolコマンドを実行し、 パスワード入力せずログインできることを確認してください。 必要に応じて/etc/ssh/sshd_configを編集し、 sshdを再起動してください。

8.3.3.3. .pgpassの作成

PostgreSQLユーザのパスワード入力を求められることなく、 オンラインリカバリのスクリプトを実行できるように、すべてのサーバにて 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.3.3.4. ファイアーウォールの設定

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

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

Watchdogを利用する場合は、9000と9694ポートも開放する必要があります。

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

8.3.4.1. スナップショットアイソレーションモードを利用する場合

スナップショットアイソレーションモードはPostgreSQLのトランザクション隔離レベルが「repeatable read」の場合のみ使用できます。スナップショットアイソレーションモードを利用する場合、 postgresql.confにdefault_transaction_isolation = 'repeatable read'を設定しておきます。

[server1]$ vi $PGDATA/postgresql.conf
(以下を追加)
default_transaction_isolation = 'repeatable read'
   

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

Pgpool-IIのクラスタリングモードを設定します。

• ネイティブレプリケーションモードの場合

  backend_clustering_mode = 'native_replication'
       

• スナップショットアイソレーションモードの場合

  backend_clustering_mode = 'snapshot_isolation'
       

8.3.5.2. listen_addresses

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

listen_addresses = '*'
   

8.3.5.3. ヘルスチェック

定期的にPostgreSQLに接続し、死活監視を行うために、ヘルスチェックを有効にします。 health_check_periodのデフォルト値が0で、これはヘルスチェックが無効であることを意味します。 また、ネットワークが不安定な場合には、バックエンドが正常であるにも関わらず、 ヘルスチェックに失敗し、フェイルオーバや縮退運転が発生してしまう可能性があります。 そのようなヘルスチェックの誤検知を防止するため、ヘルスチェックのリトライ回数を health_check_max_retries = 3 に設定しておきます。 この設定例では、health_check_passwordはpgpool.confに指定せず、 pool_passwdファイルに作成します。 作成方法については後述の項8.2.6.9を参照ください。 Pgpool-II 4.0から、sr_check_passwordが空白の場合、 Pgpool-IIは空のパスワードを使用する前に まずpool_passwdファイルからsr_check_userに 指定したユーザのパスワードを取得できるか試みます。

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

8.3.5.4. バックエンドの設定

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

# - Backend Connection Settings -

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

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

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

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

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

recovery_user = 'postgres'
recovery_password = ''

recovery_1st_stage_command = 'recovery_1st_stage.sh'
recovery_2nd_stage_command = 'recovery_2nd_stage.sh'
   

ネイティブレプリケーションモード/スナップショットアイソレーションモード専用のオンラインリカバリ用のサンプルスクリプトreplication_mode_recovery_1st_stage.sample、 replication_mode_recovery_2nd_stage.sample 及びpgpool_remote_start.sampleは /etc/pgpool-II/配下にインストールされていますので、 これらのファイルをmainノード(server1)のデータベースクラスタ配下に配置します。

[server1]# cp -p /etc/pgpool-II/replication_mode_recovery_1st_stage.sample /var/lib/pgsql/14/data/recovery_1st_stage.sh
[server1]# cp -p /etc/pgpool-II/replication_mode_recovery_2nd_stage.sample /var/lib/pgsql/14/data/recovery_2nd_stage.sh
[server1]# cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/14/data/pgpool_remote_start
[server1]# chown postgres:postgres /var/lib/pgsql/14/data/{recovery_1st_stage.sh,recovery_2nd_stage.sh,pgpool_remote_start}
   

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

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

[server1]# vi /var/lib/pgsql/14/data/recovery_2nd_stage.sh
...
PGHOME=/usr/pgsql-14
...

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

また、オンラインリカバリ機能を使用するには、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.6. クライアント認証の設定

PostgreSQLの設定の章で、 Pgpool-IIとPostgreSQLの間に 認証方式をscram-sha-256に設定しました。 この設定例では、クライアントとPgpool-IIの間でも scram-sha-256認証方式を利用し接続するように設定します。 Pgpool-IIのクライアント認証の設定ファイルは pool_hba.confと呼ばれ、YUMからインストールする場合、 デフォルトでは/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認証に設定します。

[server1]# vi /etc/pgpool-II/pool_hba.conf
(以下を追加)
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はそれらのパスワードを復号化するために復号鍵が必要となります。 全サーバで復号鍵ファイルをPgpool-IIの起動ユーザ postgres (Pgpool-II 4.0以前のバージョンではroot) のホームディレクトリ配下に作成します。

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

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

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

[server1]$ cat /etc/pgpool-II/pool_passwd
pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
postgres:AESHs/pWL5rtXy2IwuzroHfqg==
   

8.3.5.7. PCPパスワード

PCPコマンドを使用するにはユーザ認証が必要になるので、 ユーザ名とmd5ハッシュに変換されたパスワードを "username:encrypted password"の形式で pcp.confファイルに設定します。

以下のようにpg_md5コマンドを利用し、 ハッシュ化されたpgpoolユーザのパスワードエントリを/etc/pgpool-II/pcp.confに追加します。

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

8.3.5.8. ログの設定

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

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
   

server1にログファイルを格納するディレクトリを作成します。

[server1]# mkdir /var/log/pgpool_log/
[server1]# chown postgres:postgres /var/log/pgpool_log/
   

8.3.8.1. PostgreSQLサーバの構築

まず、Pgpool-IIのオンラインリカバリ機能を利用し、PostgreSQLノード1、ノード2を構築します。

[server1]# pcp_recovery_node -h server1 -p 9898 -U pgpool -n 1
Password:
pcp_recovery_node -- Command Successful

[server1]# pcp_recovery_node -h server1 -p 9898 -U pgpool -n 2
Password:
pcp_recovery_node -- Command Successful
   

ノード0がmainノードとして起動しており、ノード1とノード2がreplicaとして起動していることを確認します。

# psql -h server1 -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  | main    | main    | 0          | true              | 0                 |                   |                        | 2021-12-02 16:48:21
 1       | server2  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
 2       | server3  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
(3 rows)
   

8.3.8.2. レプリケーション機能の検証

pgbenchを使って、レプリケーション機能を試してみましょう。

[server1]# /usr/pgsql-14/bin/createdb test -U postgres -p 9999
[server1]# /usr/pgsql-14/bin/pgbench -h server1 -U postgres -i -p 9999 test
   

Pgpool-IIのレプリケーション機能が正しく動いているかどうか確かめるために、 それぞれのノードに接続して、同じ結果を返すかどうか見てみます。

[server1]# /usr/pgsql-14/bin/psql -h server1 -U postgres -p 5432 test
test=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

[server1]# /usr/pgsql-14/bin/psql -h server2 -U postgres -p 5432 test
test=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

[server1]# /usr/pgsql-14/bin/psql -h server3 -U postgres -p 5432 test
test=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)
   

server1、server2、server3のPostgreSQLは、同一の結果を返しています。

次に、pgbenchをしばらく走らせて、結果を見てみます。

[server1]# /usr/pgsql-14/bin/pgbench -h server1 -U postgres -p 9999 -T 10 test
   

すべてのPostgreSQLは、同一の結果を返しています。

[server1]# /usr/pgsql-14/bin/psql -h server1 -U postgres -p 5432 test -c "SELECT sum(abalance) FROM pgbench_accounts"
Password for user postgres:
  sum
--------
 -99710
(1 row)

[server1]# /usr/pgsql-14/bin/psql -h server2 -U postgres -p 5432 test -c "SELECT sum(abalance) FROM pgbench_accounts"
Password for user postgres:
  sum
--------
 -99710
(1 row)

[server1]# /usr/pgsql-14/bin/psql -h server3 -U postgres -p 5432 test -c "SELECT sum(abalance) FROM pgbench_accounts"
Password for user postgres:
  sum
--------
 -99710
(1 row)
   

8.3.8.3. ノード障害

次にmainノードであるserver1のPostgreSQLを停止し、 mainノードの切り替えを確認してみます。

[server1]# su - postgres -c "/usr/pgsql-14/bin/pg_ctl -m i stop"
   

ノード0を停止した後に、ノード0が切り離されて、mainノードの切り替えが発生することを確認します。

[server1]# psql -h server1 -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  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:57:45
 1       | server2  | 5432 | up     | up        | 0.333333  | main    | main    | 1          | true              | 0                 |                   |                        | 2021-12-02 16:48:21
 2       | server3  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
(3 rows)
   

8.3.8.4. オンラインリカバリ

次に、Pgpool-IIのオンラインリカバリ機能を利用し、 先ほど停止した旧mainノードを復旧させます。

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

ノード1がmainノードとして起動していることを確認します。

# psql -h server1 -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  | main    | main    | 0          | true              | 0                 |                   |                        | 2021-12-02 16:57:45
 1       | server2  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
 2       | server3  | 5432 | up     | up        | 0.333333  | replica | replica | 0          | false             | 0                 |                   |                        | 2021-12-02 16:48:21
(3 rows)
   

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