Pgpool-IIを使ってPostgreSQL (ストリーミングレプリケーション構成) を管理する

PostgreSQLのストリーミングレプリケーションは、PostgreSQL単体では障害検知や自動フェイルオーバーといった機能はなく、他のMWにて制御する必要がある。

PostgreSQLを制御できるMWとしてPacemakerやPgpool-IIなどがある。本記事では、Pgpool-IIを使ってPostgreSQL (ストリーミングレプリケーション構成) を管理するための構築手順を記載する。

環境

OSはRHEL互換OSであるAlmaLinuxを利用し、パッケージ提供されているPostgreSQLを利用する。

• OS : AlmaLinux release 8.8
• PostgreSQL : 15.2
• Pgpool-II : 4.4.4

マスターとスタンバイの2台にPostgreSQLをインストールし、ストリーミングレプリケーションを構成する。ホスト名やIPアドレスは以下図を参照いただきたい。

今回はPgpool-II導入サーバ、マスターDBサーバ、スタンバイDBサーバで実施する作業が異なる。そのため、本記事で記載するプロンプトを以下の通り記載し、作業対象が判別できるようにした。

プロンプト	ユーザー	対象
[Pgpool]#	root	Pgpool-II導入サーバのみ実施
[Master/Standby]#	root	マスターとスタンバイ両方で実施
[Master]#	root	マスターのみ実施
[Standby]#	root	スタンバイのみ実施
[Pgpool]$	postgres	Pgpool-II導入サーバのみ実施
[Master/Standby]$	postgres	マスターとスタンバイ両方で実施
[Master]$	postgres	マスターのみ実施
[Standby]$	postgres	スタンバイのみ実施

PostgreSQLをストリーミングレプリケーションで構成する

PostgreSQLをストリーミングレプリケーションで構成する手順は、以前記載した以下記事を参照いただきたい。

• PostgreSQLをストリーミングレプリケーションで構成する (PostgreSQL 15.x版)

Pgpool-II構成手順概要

Pgpool-IIを構成する手順の概要を以下に図示する。

①Pgpool-IIインストール

1. Pgpool-IIインストール

Pgpool-IIはリポジトリ登録を行うことで、dnfを用いてインストールすることができる。DBとの接続確認にpsqlコマンドを多用するため、postgresqlパッケージも導入する。

[Pgpool]# rpm -ivh https://www.pgpool.net/yum/rpms/4.4/redhat/rhel-8-x86_64/pgpool-II-release-4.4-1.noarch.rpm
[Pgpool]# dnf module enable postgresql:15 -y
[Pgpool]# dnf install pgpool-II-pg15 postgresql -y

2. postgresユーザーのパスワードを変更

Pgpool-IIの操作は原則postgresユーザーにて操作する。パッケージインストール後にpostgresユーザーのパスワードを設定しておこう。

[Pgpool]# passwd postgres
[Pgpool]# mkdir /var/lib/pgsql
[Pgpool]# chown postgres:postgres /var/lib/pgsql

3. hostsを登録

Pgpool/Master/Standbyのホスト名とIPアドレスをお互いにhostsファイルへ登録しておく。これは、後程レプリケーションの設定の際にホスト名による登録をするために必要となる。

[Pgpool/Master/Standby]# vi /etc/hosts
192.168.11.116   t1116pgpl
192.168.11.117   t1117psgl
192.168.11.118   t1118psgl

4. UNIXソケット用ディレクトリ作成

Pgpool-IIのUNIXソケットで使用するディレクトリは、デフォルトで/run/postgresqlとなる(pgpool.confにて設定変更可能)。ただし、このディレクトリは存在しないため起動に失敗することから、ディレクトリの作成及び起動時に自動でディレクトリを作成するよう/etc/tmpfiles.d/pgpool.confを設定しておく。

[Pgpool]# mkdir /run/postgresql
[Pgpool]# chown postgres:postgres /run/postgresql
[Pgpool]# vi /etc/tmpfiles.d/pgpool.conf
d /run/postgresql 0755 postgres postgres

②Pgpool-II設定

1. Pgpool-II設定 (pgpool.conf設定)

Pgpool-IIの設定ファイルは/etc/pgpool-II/pgpool.confとなる。本設定ファイルの設定箇所を以下に記載する。

設定項目	設定内容
backend_clustering_mode	ストリーミングレプリケーションを構成するためstreaming_replicationを設定する。
listen_addresses	DBが接続を受け付けるIPアドレスを指定する。サーバが持つすべてのIPアドレスで接続を許可する場合は、*を指定する。
port	デフォルトの9999で設定する。
backend_hostname0	管理対象のDB(マスター)のホスト名を設定する。
backend_port0	管理対象のDB(マスター)の接続ポート番号を設定する。デフォルトであれば、5432を設定する。
backend_weight0	管理対象のDB(マスター)への参照アクセスの重みづけを行う。均等にアクセスさせる場合は1を設定する。
backend_data_directory0	管理対象のDB(マスター)のPostgreSQLのデータ領域のパスを設定する。デフォルトであれば、/var/lib/pgsql/dataを設定する。
backend_flag0	管理対象のDB(マスター)のPgpool-IIの挙動の設定となる。Pgpool-IIからフェイルオーバーや切り離しを可能とするデフォルトのALLOW_TO_FAILOVERを設定すれば通常問題ないだろう。
backend_application_name0	管理対象のDB(マスター)にてストリーミングレプリケーションを確認した際に設定されるapplication_nameを設定する。ここはbackend_hostname0で設定したホスト名を指定する。
backend_hostname1	管理対象のDB(スタンバイ)のホスト名を設定する。
backend_port1	管理対象のDB(スタンバイ)の接続ポート番号を設定する。デフォルトであれば、5432を設定する。
backend_weight1	管理対象のDB(スタンバイ)への参照アクセスの重みづけを行う。均等にアクセスさせる場合は1を設定する。
backend_data_directory1	管理対象のDB(スタンバイ)のPostgreSQLのデータ領域のパスを設定する。デフォルトであれば、/var/lib/pgsql/dataを設定する。
backend_flag1	管理対象のDB(スタンバイ)のPgpool-IIの挙動の設定となる。Pgpool-IIからフェイルオーバーや切り離しを可能とするデフォルトのALLOW_TO_FAILOVERを設定すれば通常問題ないだろう。
backend_application_name1	管理対象のDB(スタンバイ)にてストリーミングレプリケーションを確認した際に設定されるapplication_nameを設定する。ここはbackend_hostname1で設定したホスト名を指定する。
sr_check_period	ストリーミングレプリケーションの状態確認を行う間隔を指定する。デフォルトの10秒で設定する。
sr_check_user	ストリーミングレプリケーションの状態確認を行うユーザーを指定する。今回はpostgresユーザーを指定する。
sr_check_password	ストリーミングレプリケーションの状態確認を行うユーザーのパスワードを指定する。空白で指定すると、pool_passwdファイルの内容をもとにパスワードを確認する動作となる。今回は空白で設定する。
health_check_period	各DBの状態確認を行う間隔を指定する。デフォルトの0で設定されおり無効化されてしまっているので、ストリーミングレプリケーションの間隔と同じ10秒で設定する。
health_check_user	各DBの状態確認を行うユーザーを指定する。今回はpostgresユーザーを指定する。
health_check_password	各DBの状態確認を行うユーザーのパスワードを指定する。空白で指定すると、pool_passwdファイルの内容をもとにパスワードを確認する動作となる。今回は空白で設定する。
failover_command	フェイルオーバー発生時のコマンドのフルパスを設定する。本スクリプトは、DB側ではなくPgpool-IIをインストールしたサーバに保存する。スクリプト実行時に必要な情報をパラメータとして渡すことができるので、併せて設定する。
recovery_user	オンラインリカバリ実行時のユーザーを指定する。今回はpostgresユーザーを指定する。
recovery_password	オンラインリカバリ実行時のユーザーのパスワードを指定する。空白で指定すると、pool_passwdファイルの内容をもとにパスワードを確認する動作となる。今回は空白で設定する。
recovery_1st_stage_command	オンラインリカバリ実行時のコマンドを指定する。後述するが、オンラインリカバリのスクリプトは、DBのデータディレクトリ(通常/var/lib/pgsql/data)に保存する。

実際のpgpool.confの設定例を以下に記載する。

pgpool.conf

backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run/postgresql'
pcp_socket_dir = '/var/run/postgresql'
backend_hostname0 = 't1117psgl'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 't1117psgl'
backend_hostname1 = 't1118psgl'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 't1118psgl'
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 = 0
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = ''
health_check_period = 10
health_check_user = 'postgres'
health_check_password = ''
failover_command = '/var/lib/pgsql/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'replication_mode_recovery_1st_stage'
hostname0 = ''
wd_ipc_socket_dir = '/var/run/postgresql'

③Pgpool-IIパスワード設定

Pgpool-IIを使用する場合、以下4つのファイルに認証情報のファイルを設定する。

ファイル	パスワード記載内容	説明
.pgpoolkey	平文	scram-sha-256で用いる暗号化パスワード情報。
pool_passwd	scram-sha-256	Pgpool-IIがDB接続時に使用する認証情報。pg_encコマンドで設定する。
pcp.conf	md5	Pgpool-IIの管理コマンド(pcp_node_info コマンド等)を実行する際に使用する認証情報。pg_md5コマンドで設定する。
.pgpass	平文	psqlコマンド等を用いて、Pgpool-II経由でDB接続を行う際に使用する認証情報。

各認証情報ファイルの概要を以下に記載する。

1. scram-sha-256用の暗号化パスワードファイル設定

Pgpoolから各DBへの接続ユーザーのパスワードを設定する。認証方式はscram-sha-256を用いる。まずは、scram-sha-256で使用する暗号化キーを.pgpoolkeyファイルに記載する。パスワードが記載されることから、パーミッションは600で設定する。

[pgpool]# su - postgres
[pgpool]$ echo 'ScramP@ssw0rd!Key' > ~/.pgpoolkey
[pgpool]$ chmod 600 ~/.pgpoolkey

2. DB接続用ユーザーのパスワードファイル設定

pg_encコマンドを以下の通り実行することで、pool_passwdにDB接続用のユーザー及びパスワード情報が追記される。

[pgpool]$ pg_enc -m -f /etc/pgpool-II/pgpool.conf -u postgres 'P@ssw0rd!'
[pgpool]$ cat /etc/pgpool-II/pool_passwd
postgres:AES2XNdfLOB5dSz6BGu3O+sIQ==

3. Pgpool-II管理コマンド用パスワードファイル設定

Pgpool-IIでは管理用のコマンドが各種存在する(例えば、pcp_node_infoなど)。このコマンド実行時のユーザー及びパスワードを設定するため、認証用ファイルであるpcp.confファイルに以下の通り追記する。

[pgpool]# su - postgres
[pgpool]$ echo "postgres:$(pg_md5 'P@ssw0rd!')" >> /etc/pgpool-II/pcp.conf
[pgpool]$ tail -1 /etc/pgpool-II/pcp.conf
postgres:8a24367a1f46c141048752f2d5bbd14b

4. psqlコマンドによる接続時にパスワードを聞かれないようにする

Pgpool-II導入サーバのローカルからpsqlコマンドを用いて接続する際にパスワードを聞かれないようにするため、.pgpassファイルを以下の通りホームディレクトリに作成しておく。

[pgpool]$ vi ~/.pgpass
localhost:9999:*:postgres:P@ssw0rd!

5. マスターとスタンバイのSSHキーを互いに交換

Pgpool-IIはSSHを用いてDBに対してコマンド実行を行う場合がある。SSH接続の際にパスワードを聞かれないよう、SSHキーの交換を行っておく。

[Pgpool]# su - postgres
[Pgpool]$ ssh-keygen
[Pgpool]$ ssh-copy-id t1117psgl
[Pgpool]$ ssh-copy-id t1118psgl

④管理対象DBの設定

1. 管理対象DBへPgpool-II Extentionsをインストール

Pgpool-IIは管理対象とするPostgreSQL側にもインストールが必要である点に注意する。正確にはpgpool-II-pg15-extensionsのパッケージが必要であり、依存関係としてPgpool-II本体のインストールも必要となる。

[Master/Standby]# rpm -ivh https://www.pgpool.net/yum/rpms/4.4/redhat/rhel-8-x86_64/pgpool-II-release-4.4-1.noarch.rpm
[Master/Standby]# dnf install pgpool-II-pg15 pgpool-II-pg15-extensions -y

2. DBのアクセス設定にてPgpool-IIを許可

Pgpool-IIからDBへのアクセス許可のため、pg_hba.confに以下を追記する。認証方式はscram-sha-256とする。

[Master/Standby]# vi /var/lib/pgsql/data/pg_hba.conf
～(中略)～
host    all             all             192.168.11.116/32       scram-sha-256

設定反映のためDBの設定リロードを行う。

[Master/Standby]# systemctl reload postgresql

3. オンラインリカバリ用の設定

DBのフェイルオーバー後にDBを再参加させるオンラインリカバリを実行する際には、DBに事前に設定を投入しておく必要がある。

必要な設定はSQLで用意されているが、SQL実行前にpgpool-recovery.soのライブラリが必要となるので、あらかじめシンボリックリンクを作成しておく。

[Master/Standby]# ln -s /usr/pgsql-15/lib/pgpool-recovery.so /usr/lib64/pgsql/pgpool-recovery.so

次にpgpool-recovery.sqlのSQLを実行する。

[Master]# su - postgres
[Master]$ psql -f /usr/pgsql-15/share/extension/pgpool-recovery.sql template1
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

⑤Pgpool-II動作確認

1. Pgpool-II起動

ここまでで一度Pgpool-IIを起動し、動作確認を行う。wait for connection requestの行が多数表示されているが、Pgpool-IIはnum_init_childrenというパラメータでクライアントの最大同時接続数を制御しており、デフォルト32個で設定されているため、正常な表示となる。

[Pgpool]# systemctl start pgpool
[Pgpool]# systemctl status pgpool
● pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; disabled; vendor preset: disabled)
   Active: active (running) since Sat 2023-08-26 13:00:00 JST; 5s ago
  Process: 12643 ExecStop=/usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf $STOP_OPTS stop (code=killed, signal=TERM)
 Main PID: 12664 (pgpool)
    Tasks: 34 (limit: 23204)
   Memory: 145.5M
   CGroup: /system.slice/pgpool.service
           tq12664 /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -n
           tq12666 pgpool: PgpoolLogger
           tq12668 pgpool: wait for connection request
           tq12669 pgpool: wait for connection request
           tq12670 pgpool: wait for connection request
           tq12671 pgpool: wait for connection request
           tq12672 pgpool: wait for connection request
           tq12673 pgpool: wait for connection request
           tq12674 pgpool: wait for connection request
           tq12675 pgpool: wait for connection request
           tq12676 pgpool: wait for connection request
           tq12677 pgpool: wait for connection request
           tq12678 pgpool: wait for connection request
           tq12679 pgpool: wait for connection request
           tq12680 pgpool: wait for connection request
           tq12681 pgpool: wait for connection request
           tq12682 pgpool: wait for connection request
           tq12683 pgpool: wait for connection request
           tq12684 pgpool: wait for connection request
           tq12685 pgpool: wait for connection request
           tq12686 pgpool: wait for connection request
           tq12687 pgpool: wait for connection request
           tq12688 pgpool: wait for connection request
           tq12689 pgpool: wait for connection request
           tq12690 pgpool: wait for connection request
           tq12691 pgpool: wait for connection request
           tq12692 pgpool: wait for connection request
           tq12693 pgpool: wait for connection request
           tq12694 pgpool: wait for connection request

2. Pgpool-IIにてDBの状態確認

Pgpool-IIからバックエンドとなるDBの状態確認は、以下コマンドで確認できる。statusがupになっていることや、Master (=primary)とStandby (=standby)の役割が正常に認識していることを確認しよう。

[Pgpool]# su - postgres
[Pgpool]$ psql -p 9999 -x -c "show pool_nodes"
-[ RECORD 1 ]----------+--------------------
node_id                | 0
hostname               | t1117psgl
port                   | 5432
status                 | up　　　　←★upであること
pg_status              | up　　　　←★upであること
lb_weight              | 0.500000
role                   | primary　←★primaryであること
pg_role                | primary　←★primaryであること
select_cnt             | 0
load_balance_node      | false
replication_delay      | 0
replication_state      |
replication_sync_state |
last_status_change     | 2023-08-26 22:03:26
-[ RECORD 2 ]----------+--------------------
node_id                | 1
hostname               | t1118psgl
port                   | 5432
status                 | up　　　　←★upであること
pg_status              | up　　　　←★upであること
lb_weight              | 0.500000
role                   | standby　←★standbyであること
pg_role                | standby　←★standbyであること
select_cnt             | 0
load_balance_node      | true
replication_delay      | 0
replication_state      | streaming　←★streamingであること
replication_sync_state | sync　　　　←★syncであること
last_status_change     | 2023-08-26 22:03:26

以上で、Pgpool-IIを使ってPostgreSQL (ストリーミングレプリケーション構成) を管理するための構築手順は完了となる。

次回はPgpool-IIを使用して、DB停止時の自動フェイルオーバーの動作確認と、オンラインリカバリの動作確認を行う。