トップ > PostgreSQL > Pgpool-II によるPostgreSQL自動フェイルオーバー実装

Pgpool-II によるPostgreSQL自動フェイルオーバー実装

PostgreSQL Pgpool-II Linux データベース

本記事では、Pgpool-IIを使用してPostgreSQLの自動フェイルオーバーを実現する方法を紹介します。

Pgpool-IIとは

Pgpool-IIは、PostgreSQLサーバとPostgreSQLデータベースクライアントの間に位置するプロキシソフトウェアです。

主な機能は以下の通りです。

  • 自動フェイルオーバー
    • 複数のPostgreSQLサーバーをバックエンドとして管理し、プライマリーサーバーに到達できなくなったときは、自動的にスタンバイサーバーをプライマリーサーバーへ昇格させ、サービスを継続させます。
  • 負荷分散
    • SELECTクエリを複数のPostgreSQLサーバーに分散させ、システム全体のスループットを改善します。
  • コネクションプール
    • PostgreSQLサーバーへの確立された接続を維持し、他に指定されていれば接続パラメータも）が同じ新規のコネクションが到着する度に再利用します。
    • コネクションオーバヘッドを軽減することによって、システム全体のスループットを改善します。

今回は、自動フェイルオーバー機能の実装方法を説明します。

全体構成図

Pgpool-IIサーバーは1台、PostgreSQL サーバーはMaster/Standbyの2台構成とします。

サーバー構成

本手順は、以下要件のサーバーが準備済みであることを前提としています。

※ 本検証ではPgpool-IIサーバー、PostgreSQLサーバー共に同じハードウェア構成のサーバーを準備します。

  • ハードウェア
    • CPU:2コア
    • メモリ: 4GB
    • ディスク: 32GB x 1

また、ソフトウェアは以下のバージョンを使用します。

  • OS
    • AlmaLinux 9.5
  • PostgreSQL/Pgpool-II 
    • PostgreSQL17
    • Pgpool-II 4.5.5

PostgreSQL構築

PostgreSQLインストール

各PostgreSQLサーバーにrootユーザーでログインし、PostgreSQL用リポジトリをインストールします。

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

PostgreSQL17をインストールします。

# dnf install -y postgresql17-server

walアーカイブ用のディレクトリを作成します。

# su - postgres
$ mkdir /var/lib/pgsql/archivedir

Pgpool-II extensionsインストール

今回、Pgpool-IIのオンラインリカバリ機能を使用するために、各PostgreSQLサーバーにpgpool-II-pgXX-extensionsをインストールします。

オンラインリカバリ機能とは、ダウンしたノードを復旧させることができる仕組みです。 この機能を使用すると、プライマリノードからデータをスタンバイノードへとコピーし、プライマリと同期させます。

※オンラインリカバリ機能を使用しない場合、Pgpool-II extensionsインストールは不要です

Pgpool-IIの依存関係パッケージがインストールできるように、CRBリポジトリを有効化します。

# dnf config-manager --set-enabled crb

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

# dnf install https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-9-x86_64/pgpool-II-release-4.5-1.noarch.rpm

pgpool-II-pgXX-extensionsと依存パッケージのpgpool-II-pgXXをインストールします。

# dnf install pgpool-II-pg17-extensions pgpool-II-pg17

Pgpool-II オンラインリカバリ設定

オンラインリカバリ用スクリプトを作成します。サンプルスクリプトをコピーして、PGHOMEパラメータを編集します。

# cp -p /etc/pgpool-II/sample_scripts/recovery_1st_stage.sample /var/lib/pgsql/17/data/recovery_1st_stage
# cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /var/lib/pgsql/17/data/pgpool_remote_start
# chown postgres:postgres /var/lib/pgsql/17/data/{recovery_1st_stage,pgpool_remote_start}
# vi /var/lib/pgsql/17/data/recovery_1st_stage
(更新)
PGHOME=/usr/pgsql-17

# vi /var/lib/pgsql/17/data/pgpool_remote_start
(更新)
PGHOME=/usr/pgsql-17

Primary PostgreSQLサーバー構築

Primary PostgreSQLサーバーを構築します。尚、Standby サーバーはPgpool-IIのオンラインリカバリ機能を使用して構築しますので、この時点では構築しません。

PostgreSQLを初期化します。

$ /usr/pgsql-17/bin/initdb -D $PGDATA

設定ファイル"postgresql.conf"のパラメータ値を編集します。

$ vi $PGDATA/postgresql.conf
(更新)
# PostgreSQLへのアクセス許可設定
listen_addresses = '*'
# WALアーカイブモードを有効化し、アーカイブ用コマンドを指定
archive_mode = on
archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
# MasterからStandbyへのレプリケーション有効化と設定
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
# Standby側で参照クエリを受け付けるよう設定
hot_standby = on
# オンラインリカバリ機能を使用するためwal_log_hintsを有効化
wal_log_hints = on

設定ファイル"pg_hba.conf"にアクセス許可設定を追加します。今回は、Pgpool-IIサーバとPostgreSQLバックエンドサーバが同サブネット(samenet)にあることを想定しています。また、各ユーザがscram-sha-256認証方式で接続できるよう設定します。

$ vi $PGDATA/pg_hba.conf
(追加)
host    all    pgpool      samenet    scram-sha-256
host    all    postgres    samenet    scram-sha-256
host    replication    all    samenet    scram-sha-256

PostgreSQLを起動します。

$ /usr/pgsql-17/bin/pg_ctl start -D $PGDATA

PostgreSQLに接続し、ユーザーの作成およびパスワードの設定を行います。scram-sha-256認証方式に変更したうえで、レプリケーション遅延チェックとヘルスチェック用のpgpoolと、レプリケーション用のreplを作成します。また、オンラインリカバリで使用するpostgresユーザーのパスワードも設定します。

$ psql 
=# SET password_encryption = 'scram-sha-256';
=# CREATE ROLE pgpool WITH LOGIN;
=# CREATE ROLE repl WITH REPLICATION LOGIN;
=# \password pgpool
=# \password repl
=# \password postgres

Pgpool-IIのバックエンドであるPostgreSQLサーバーの状態を確認するshow pool_nodesコマンドでreplication_statカラムとreplication_sync_stateカラムを表示するには、pgpoolユーザーはPostgreSQLのスーパーユーザであるか、pg_monitorグループに所属する必要があります(Pgpool-II 4.1以降)。

以下のコマンドで、pgpoolユーザーをそのグループに所属させます。

$ psql
=# GRANT pg_monitor TO pgpool;

これでPrimary PostgreSQL サーバーの構築は完了です。

StandbyサーバーはPgpool-IIのオンラインリカバリ機能で構築するので、この時点では構築しません。オンラインリカバリ機能での構築手順は後述します。

Pgpool-II構築

Pgpool-IIインストール

Pgpool-IIサーバーに、Pgpool-IIをインストールします。

Pgpool-IIの依存関係パッケージがインストールできるように、CRBリポジトリを有効化します。

# dnf config-manager --set-enabled crb

Pgpool-II用リポジトリをインストールします。

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

インストールが成功すると、/etc/yum.repos.d ディレクトリ配下にリポジトリが作成されます。

# ls /etc/yum.repos.d/pgpool-II-release-45.repo

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

# dnf install pgpool-II-pg17

Pgpool-IIをインストールするとpostgresユーザーが作成されます。postgresユーザーのホームディレクトリ(/var/lib/pgsql)を作成します。

# mkdir /var/lib/pgsql
# chown postgres:postgres /var/lib/pgsql

Pgpool-II設定ファイル編集

pgpool.confを編集します。

# vi /etc/pgpool-II/pgpool.conf
(更新)
#アクセス許可設定および、受付ポートを設定
listen_addresses = '*'
pcp_listen_addresses = '*'
port = 9999
#クラスタリングモードの選択。今回は推奨されているストリーミングレプリケーションを選択
backend_clustering_mode = 'streaming_replication'
# バックエンド情報をPrimary/Standby PostgreSQLサーバーを指定
backend_hostname0 = '<Primary PostgreSQLサーバー名>'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/17/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = '<Primary PostgreSQLサーバー名>'

backend_hostname1 = '<Standby PostgreSQLサーバー名>'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/17/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = '<Standby PostgreSQLサーバー名>'

#PostgreSQL自動フェイルオーバーのためのヘルスチェックを有効化
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3

#フェイルオーバーコマンドを設定
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'

#オンラインリカバリ用のパラメーターを設定
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'

#pool_hba.conf によるアクセス制限を有効化します
enable_pool_hba = on

#socket pathを以下パスに変更
unix_socket_directories = '/var/run/pgpool'
pcp_socket_dir = '/var/run/pgpool'

ここまでパラメーターを設定したら、保存します。

フェイルオーバー用スクリプトを作成します。サンプルスクリプトをコピーして、PGHOMEパラメータを編集します。

# cp -p /etc/pgpool-II/sample_scripts/failover.sh.sample /etc/pgpool-II/failover.sh
# chown postgres:postgres /etc/pgpool-II/failover.sh
# vi /etc/pgpool-II/failover.sh
(更新)
PGHOME=/usr/pgsql-17

pool_hba.confにアクセス許可設定を追記します。書式はpg_hba.confとほぼ同じです。同サブネット(samenet)からのアクセスを許可します。また、各ユーザがscram-sha-256認証方式で接続できるよう設定します。

# vi /etc/pgpool-II/pool_hba.conf
(追記)
host all pgpool samenet scram-sha-256
host all postgres samenet scram-sha-256

パスワードファイル"pool_passwd"に、PostgreSQLユーザーの暗号化したパスワードを出力します。Pgpool-IIがPostgreSQLへ接続するための認証情報です。

# su - postgres
$ echo 'pgpoolkey' > ~/.pgpoolkey
$ chmod 600 ~/.pgpoolkey
$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
$ pg_enc -m -k ~/.pgpoolkey -u postgres -p

Pgpool-IIの管理コマンドであるPCPコマンドを使用するには、username:encryptedpassword形式のPCPユーザ名とmd5暗号化パスワードをpcp.confに登録する必要があります。今回は、PCPユーザ名を"pgpool"、パスワードを"pgpool_password"に設定します。

# echo 'pgpool:'`pg_md5 pgpool_password` >> /etc/pgpool-II/pcp.conf

SSH公開鍵交換

自動フェイルオーバー、オンラインリカバリ機能を利用するために、Pgpool-IIサーバーとPostgreSQLサーバー間でSSH公開鍵交換を行います。

Pgpool-IIサーバーとPostgreSQLサーバーのpostgresユーザーでSSH秘密鍵・公開鍵を生成します。

# 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ファイルに追記します。

$ vi /var/lib/pgsql/.ssh/authorized_keys

SELinuxが有効の場合は以下のコマンドを実行し、パスワードなしでのSSH接続を許可します。

$ restorecon -Rv ~/.ssh

設定後、以下SSHコマンドで各サーバーへパスワードなしでログインできることを確認します。

$ ssh postgres@serverX -i ~/.ssh/id_rsa_pgpool

パスワード入力なしでストリーミングレプリケーションを実行するために、Master/Standby PostgreSQLサーバーにパスワードファイル".pgpass"を生成します。

$ vi /var/lib/pgsql/.pgpass
(追記)
<Primary PostgreSQLサーバー名>:5432:replication:repl:<パスワード>
<Standby PostgreSQLサーバー名>:5432:replication:repl:<パスワード>
<Primary PostgreSQLサーバー名>:5432:postgres:postgres:<パスワード>
<Standby PostgreSQLサーバー名>:5432:postgres:postgres:<パスワード>

ファイルのパーミッションを以下のように設定します。

$ chmod 600  /var/lib/pgsql/.pgpass

Firewalld設定

Pgpool-IIサーバー Firewalld設定

Pgpool-IIのポートに対する許可設定を追加します。

# firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp
# firewall-cmd --reload

PostgreSQLサーバー Firewalld設定

PostgreSQLのポートに対する許可設定を追加します。

# firewall-cmd --permanent --zone=public --add-service=postgresql
# firewall-cmd --reload

Pgpool-II起動/Standby PostgreSQLサーバー構築

Pgpool-IIを起動します。

# systemctl start pgpool.service

Pgpool-II経由でPostgreSQLへ接続できるか確認するのと同時に、バックエンドのPostgreSQLのステータスを確認します。Primary PostgreSQLサーバーはステータスが"up"で、roleは"Primary"になっているはずです。Standbyはまだ構築していないのでステータスがunsedになっています。Standbyのnode_id番号を確認してください。

$ psql -h <Pgpool-IIサーバーのIPアドレス> -p 9999 -U pgpool postgres -c "show pool_nodes"
ユーザー 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       | <Primary PostgreSQLサーバー名> | 5432 | up     | up        | 0.500000  | primary | primary | 0          | true              | 0
 |                   |                        | 2024-12-19 19:45:34
 1       | <Standby PostgreSQLサーバー名> | 5432 | unused | down      | 0.500000  | standby | unknown | 0          | false             | 0
 |                   |                        | 2024-12-19 19:45:34
(2 行)

Standbyのステータスが"unsed"になっていることを確認したら、以下コマンドを実行しノードをアタッチします。-n オプションの値は前手順で確認したnode_idを指定します。

$ pcp_attach_node  -h <Pgpool-IIサーバーのIPアドレス> -p 9898 -U pgpool -n 1 -W

アタッチが成功すると、ステータスが”down”に変化します。

$ psql -h <Pgpool-IIサーバーのIPアドレス> -p 9999 -U pgpool postgres -c "show pool_nodes"
 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       | <Primary PostgreSQLサーバー名> | 5432 | up     | up        | 0.500000  | primary | primary | 0          | true              | 0
 |                   |                        | 2024-12-19 19:45:34
 1       | <Standby PostgreSQLサーバー名> | 5432 | down   | down      | 0.500000  | standby | unknown | 0          | false             | 0
 |                   |                        | 2024-12-19 19:58:41
(2 行)

Standbyサーバーに対してオンラインリカバリを実行します。

# pcp_recovery_node -h <Pgpool-IIサーバーのIPアドレス> -p 9898 -U pgpool -n 1 -W

ステータスが"up"、roleが"Standby"になっていれば、Standbyサーバー の構築完了です。

$ psql -h <Pgpool-IIサーバーのIPアドレス> -p 9999 -U pgpool postgres -c "show pool_nodes"
 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       | <Primary PostgreSQLサーバー名> | 5432 | up     | up        | 0.500000  | primary | primary | 0          | false             | 0
 |                   |                        | 2024-12-19 19:45:34
 1       | <Standby PostgreSQLサーバー名> | 5432 | up     | up        | 0.500000  | standby | standby | 0          | true              | 0
 | streaming         | async                  | 2024-12-19 20:43:07
(2 行)

自動フェイルオーバーテスト

Primary PostgreSQLサーバーへログインし、疑似障害としてPostgreSQLを停止します。

$ /usr/pgsql-17/bin/pg_ctl -m immediate stop -D $PGDATA

以下コマンドを実行し、Pgpoo-II経由でPostgreSQLへ接続可能であることと、Primaryのステータスが"down"となりStandbyサーバーのroleが"Primary"へ昇格していれば、自動フェイルオーバー成功です。

$ psql -h <Pgpool-IIサーバーのIPアドレス> -p 9999 -U pgpool postgres -c "show pool_nodes"
 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       | <Primary PostgreSQLサーバー名> | 5432 | down   | down      | 0.500000  | standby | unknown | 0          | false             | 0
 |                   |                        | 2024-12-19 20:47:07
 1       | <Standby PostgreSQLサーバー名> | 5432 | up     | up        | 0.500000  | primary | primary | 0          | true              | 0
 |                   |                        | 2024-12-19 20:47:07
(2 行)

最後に

今回は、Pgpool-IIを利用してPostgreSQLの自動フェイルオーバー機能を実装しました。

PostgreSQLの標準機能では自動フェイルオーバーは実装できないので、高可用性の面でPgpool-IIはとても有用ですね。

今回の構成はPgpool-IIサーバーが1台なのでSPOFになる可能性があります。今後、Pgpool-IIサーバーの冗長構成についても検証していきたいと思います。

執筆担当者プロフィール
坂本 典久

坂本 典久（日本ビジネスシステムズ株式会社）

クラウドソリューション事業本部所属。 SQL ServerやPostgreSQLなどのデータベース設計・構築、Azureなどのクラウドを中心としたシステムの設計・構築に携わっています。

担当記事一覧