この記事は PostgreSQL Advent Calendar 2023 の 22 日目です。昨日の記事 は @sawada_masahiko さんの PostgreSQL でトランザクション ID をできるだけ早く消費する方法 でした。この記事では 2023 年 12 月 12 日リリースの Pgpool-II 4.5 の新機能を紹介します。
Pgpool-II とは
Pgpool-II とは、PostgreSQL のクライアントとサーバ間で動作し、自動フェイルオーバや負荷分散など、PostgreSQL 本体にない機能を提供する、オープンソースのミドルウェアです。ライセンスは PostgreSQL とよく似たもので、保証なしに誰でも自由に使えます。
Pgpool-II は、例年、PostgreSQL の新バージョンリリースから数か月後、新バージョンの SQL パーサ取り込みを含む、新機能を追加した新バージョンがリリースされています。バージョン 4.5 は前バージョン 4.4 から約 1 年ぶりのリリースになります。
Pgpool-II 検証環境のセットアップ
Pgpool-II 4.5 の新機能を検証するため、簡易的に pgpool_setup コマンドで 1 台のサーバ上に Pgpool-II 1 台、プライマリ 1 台とスタンバイ 2 台からなる PostgreSQL のクラスタをセットアップします。Rocky Linux 9 では以下の手順になります。
-
PostgreSQL をインストールします。
$ sudo dnf -qy install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm Installed: pgdg-redhat-repo-42.0-36PGDG.noarch $ sudo dnf -qy module disable postgresql $ sudo dnf -qy install postgresql16-server Installed: lz4-1.9.3-5.el9.x86_64 postgresql16-16.1-2PGDG.rhel9.x86_64 postgresql16-libs-16.1-2PGDG.rhel9.x86_64 postgresql16-server-16.1-2PGDG.rhel9.x86_64 -
Pgpool-II をインストールします。
$ sudo dnf -qy install https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-9-x86_64/pgpool-II-release-4.5-1.noarch.rpm Installed: pgpool-II-release-4.5-1.noarch $ sudo dnf -qy --enablerepo=crb install pgpool-II-pg16 pgpool-II-pg16-extensions Installed: libmemcached-awesome-1.1.0-12.el9.x86_64 pgpool-II-pg16-4.5.0-1pgdg.rhel9.x86_64 pgpool-II-pg16-extensions-4.5.0-1pgdg.rhel9.x86_64Rocky Linux 9 では、Pgpool-II と依存関係のある
libmemcachedパッケージをインストールするため、CRB リポジトリを有効にします。 -
PostgreSQL のスーパーユーザ
postgresに切り替わります。$ sudo su - postgrespostgresユーザ以外でも構いませんが、その場合、/etc/pgpool-IIディレクトリ内のファイルを参照できるように、読み取り権限を与えておく必要があります。 -
パスワードなしでローカルホストに SSH 接続できるように設定します。
$ ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa): (何も入力しない) Created directory '/var/lib/pgsql/.ssh'. Enter passphrase (empty for no passphrase): (何も入力しない) Enter same passphrase again: (何も入力しない) Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub The key fingerprint is: SHA256:diMOLACUq0G1326DrH+g3QqbhmJiMWznBbK2IAdb+FI postgres@node-1.example.com The key's randomart image is: +---[RSA 3072]----+ |o.o. | | + . | |..o. | |+oEo... | |oBo o.o.S o | |*Bo..+o+ o . | |=oB.+oo+. | |o= +=..o. | |= .+.oo | +----[SHA256]-----+ $ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys $ ssh localhost The authenticity of host 'localhost (::1)' can't be established. ED25519 key fingerprint is SHA256:FzHn2kWMn2hqHzYBorboBUJZwMIpgTDpIqn4GLocJiQ. This key is not known by any other names Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added 'localhost' (ED25519) to the list of known hosts. Last login: Sun Dec 17 17:11:22 2023 $ exit logout Connection to localhost closed.Pgpool-II の各種スクリプトは SSH 接続する作りになっているため、1 台のサーバでも SSH の設定が必要になります。
-
クラスタをセットアップします。
$ mkdir ~/pgpool_test $ cd ~/pgpool_test $ pgpool_setup -n 3 PostgreSQL major version: 160 Starting set up in streaming replication mode (省略) pgpool-II setting for streaming replication mode is done. To start the whole system, use /var/lib/pgsql/pgpool_test/startall. To shutdown the whole system, use /var/lib/pgsql/pgpool_test/shutdownall. pcp command user name is "postgres", password is "postgres". Each PostgreSQL, pgpool-II and pcp port is as follows: #1 port is 11002 #2 port is 11003 #3 port is 11004 pgpool port is 11000 pcp port is 11001 The info above is in README.port.pgpool_setupコマンドは空のディレクトリ内で実行します。-nオプションには PostgreSQL のノード数を指定します。そのほかのオプションについては マニュアル を参照してください。クラスタの起動、停止にはそれぞれ
startall、shutdownallスクリプトを使い、PCP (Pgpool-II Control Protocol) のユーザ名、パスワードはいずれもpostgres、各ポートの用途は以下になります。ポート 用途 11002 PostgreSQL 1 台目 11003 PostgreSQL 2 台目 11004 PostgreSQL 3 台目 11000 Pgpool-II 11001 PCP -
クラスタを起動します。
$ ./startall サーバーの起動完了を待っています....2754 2023-12-18 13:30:03.013 JST LOG: ログ出力をログ収集プロセスにリダイレクトしています 2754 2023-12-18 13:30:03.013 JST ヒント: ここからのログ出力はディレクトリ"log"に現れます。 完了 サーバー起動完了 サーバーの起動完了を待っています....2765 2023-12-18 13:30:03.278 JST LOG: ログ出力をログ収集プロセスにリダイレクトしています 2765 2023-12-18 13:30:03.278 JST ヒント: ここからのログ出力はディレクトリ"log"に現れます。 完了 サーバー起動完了 サーバーの起動完了を待っています....2783 2023-12-18 13:30:03.594 JST LOG: ログ出力をログ収集プロセスにリダイレクトしています 2783 2023-12-18 13:30:03.594 JST ヒント: ここからのログ出力はディレクトリ"log"に現れます。 完了 サーバー起動完了 -
クラスタが正常に動作しているかを確認します。
$ psql -h localhost -p 11000 -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 | localhost | 11002 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2023-12-18 13:41:33 1 | localhost | 11003 | up | up | 0.333333 | standby | standby | 0 | true | 0 | streaming | async | 2023-12-18 13:41:33 2 | localhost | 11004 | up | up | 0.333333 | standby | standby | 0 | false | 0 | streaming | async | 2023-12-18 13:41:33 (3 行)Pgpool-II が動作するポート 11000 に接続し、
SHOW pool_nodesコマンドで PostgreSQL のノード情報を取得し、3 台ともすべてstatus(状態) がup(動作中) になっていれば、クラスタは正常に動作しています。
Pgpool-II 4.5 の新機能
Pgpool-II 4.5 の変更点はたくさんありますが、リリースノート でおもな新機能として取り上げられているものを紹介します。
マルチステートメント内のトランザクション検出
マルチステートメント (multi-stament) とは、複数のクエリを ; (セミコロン) でつなげ、1 つにまとめたものです。Pgpool-II では、マルチステートメントはプライマリのみで実行されます。トランザクションの開始も、マルチステートメント内であれば、プライマリのみで実行されます。
以前は、マルチステートメント内のトランザクションの開始を検出できませんでした。そのため、後続のクエリがトランザクション内でしか実行できなくても、トランザクションが未開始のスタンバイで実行される可能性がありました。
例えば、SAVEPOINT コマンドはプライマリと負荷分散先ノードで実行されますが、マルチステートメント内でトランザクションの開始後に実行し、負荷分散先ノードがスタンバイの場合、プライマリでは実行に成功するのに対し、スタンバイでは失敗し、ノード間の結果が異なるため、セッションが切断されていました。
$ psql -q -h localhost -p 11000 test
=# BEGIN\; SELECT 1;
?column?
----------
1
(1 行)
=*# SAVEPOINT sp;
WARNING: packet kind of backend 2 ['E'] does not match with main/majority nodes packet kind ['C']
FATAL: failed to read kind from backend
DETAIL: kind mismatch among backends. Possible last query was: "SAVEPOINT sp;" kind details are: 0[C]2[E: SAVEPOINT can only be used in transaction blocks]
HINT: check data consistency among db nodes
サーバーとの接続が想定外にクローズされました
おそらく要求の処理前または処理中にサーバーが異常終了
したことを意味しています。
サーバーへの接続が失われました。リセットしています: 成功。
バージョン 4.5 では、マルチステートメント内でトランザクションの開始を検出し、後続のクエリがトランザクション内でしか実行できない場合、プライマリのみで実行するように修正されています。
本題とは直接関係ありませんが、psql でマルチステートメントを実行する場合、; の前に \ (バックスラッシュ) を付け、エスケープする必要があります。そうしないと、単一のクエリに分割され、実行されてしまいます。
この修正は、新機能というよりバグ修正の意味合いが強いため、バージョン 4.1 ～ 4.4 系列にも適用されています。
サーバ側プリペアドステートメントの負荷分散
プリペアドステートメント (prepared statement) とは、クエリ文字列とそれに埋め込む値を分離したものです。一般的にプリペアドステートメントと言えば、クライアント側でデータベース接続 API が提供する機能のことですが、PostgreSQL ではサーバ側で SQL の機能としても提供されています。
サーバ側のプリペアドステートメントは PREPARE コマンドで作成し、EXECUTE コマンドで実行し、DEALLOCATE コマンドで削除します。バージョン 4.4 以前はプライマリのみで実行されていましたが、バージョン 4.5 では参照のみの場合に負荷分散されるようになりました。
$ psql -q -h localhost -p 11000 test
=# CREATE TABLE tb (
c1 int PRIMARY KEY,
c2 text NOT NULL
);
=# INSERT INTO tb
VALUES (1, 'foo'), (2 'bar'), (3, 'baz');
=# PREPARE st(int) AS SELECT c2 FROM tb WHERE c1 = $1;
=# EXECUTE st(1);
c2
-----
foo
(1 行)
=# \c
=# PREPARE st(int) AS SELECT c2 FROM tb WHERE c1 = $1;
=# EXECUTE st(2);
c2
-----
bar
(1 行)
=# \c
=# PREPARE st(int) AS SELECT c2 FROM tb WHERE c1 = $1;
=# EXECUTE st(3);
c2
-----
baz
(1 行)
=# \q
$ less log/pgpool.log
(省略)
2023-12-19 06:21:37.720: psql pid 6434: LOG: DB node id: 1 backend pid: 8458 statement: PREPARE st(int) AS SELECT c2 FROM tb WHERE c1 = $1;
2023-12-19 06:21:42.382: psql pid 6434: LOG: DB node id: 1 backend pid: 8458 statement: EXECUTE st(1);
2023-12-19 06:21:43.540: psql pid 6434: LOG: DB node id: 0 backend pid: 8459 statement: DISCARD ALL
2023-12-19 06:21:43.540: psql pid 6434: LOG: DB node id: 1 backend pid: 8458 statement: DISCARD ALL
2023-12-19 06:21:44.897: psql pid 8623: LOG: DB node id: 0 backend pid: 8898 statement: SELECT pg_catalog.version()
2023-12-19 06:21:44.897: psql pid 8623: LOG: DB node id: 2 backend pid: 8900 statement: PREPARE st(int) AS SELECT c2 FROM tb WHERE c1 = $1;
2023-12-19 06:21:47.174: psql pid 8623: LOG: DB node id: 2 backend pid: 8900 statement: EXECUTE st(2);
2023-12-19 06:21:51.215: psql pid 8623: LOG: DB node id: 0 backend pid: 8898 statement: DISCARD ALL
2023-12-19 06:21:51.215: psql pid 8623: LOG: DB node id: 2 backend pid: 8900 statement: DISCARD ALL
2023-12-19 06:21:53.003: psql pid 7852: LOG: DB node id: 0 backend pid: 8716 statement: PREPARE st(int) AS SELECT c2 FROM tb WHERE c1 = $1;
2023-12-19 06:21:55.672: psql pid 7852: LOG: DB node id: 0 backend pid: 8716 statement: EXECUTE st(3);
2023-12-19 06:21:56.669: psql pid 7852: LOG: DB node id: 0 backend pid: 8716 statement: DISCARD ALL
\c コマンドで再接続してはプリペアドステートメントの実行を繰り返し、Pgpool-II のログを確認すると、DB node id: 0 のプライマリ以外でも PREPARE、EXECUTE コマンドが実行されていることが確認できます。
なお、クライアント側のプリペアドステートメントは以前から負荷分散されています。
delay_threshold_by_time パラメータのミリ秒単位指定
delay_threshold_by_time は、ストリーミングレプリケーションが指定した時間より遅延しているスタンバイに負荷分散しないように制御するパラメータです。
delay_threshold_by_time = 1ms
# Threshold before not dispatching query to standby node
# The default unit is in millisecond(s)
# Disabled (0) by default
バージョン 4.4 以前は時間を s (秒)、min (分)、h (時間)、d (日) 単位でしか指定できませんでしたが、バージョン 4.5 では ms (ミリ秒) 単位でも指定できるようになりました。なお、バージョン 4.4 以前で単位に ms を指定しても、エラーにならずに秒単位になります。
単位を指定しなかった場合、バージョン 4.4 以前は秒単位なのに対し、バージョン 4.5 ではミリ秒単位で単位が異なるため、バージョンアップの際には注意してください。
フェイルオーバやバックエンドエラー発生時のセッション切断軽減
Pgpool-II では、内部処理のいたるところでノードの状態をチェックしていますが、それがフェイルオーバやバックエンドエラー発生時にされると、そのノードを使っていなくても、使えなくなったソケットにアクセスするなどし、セッションが切断されてしまいます。
バージョン 4.5 では、ノードの状態をチェックする際にフェイルオーバの完了を待つなど、セッションの切断を軽減する改善がされています。完全になくすことは困難で、いまだ発生する可能性はあります。pcp_detach_node コマンドでノードを切り離した上で停止した場合は発生しなくなっています。
バージョン 4.4 以前は、pcp_detach_node コマンドでノードを切り離しても、停止時にタイミングによってはセッションが切断される場合があります。
$ /usr/pgsql-15/bin/pgbench -h localhost -p 11000 -i test
(省略)
$ /usr/pgsql-15/bin/pgbench -h localhost -p 11000 -S -c 10 -C -T 30 test
pgbench (15.5)
starting vacuum...end.
(pgbench の実行中に別の端末でノードを切り離し、停止)
$ pcp_detach_node -h localhost -p 11001 2
Password: (パスワードを入力)
pcp_detach_node -- Command Successful
$ /usr/pgsql-15/bin/pg_ctl stop -D data2
サーバー停止処理の完了を待っています....完了
サーバーは停止しました
pgbench: error: connection to server at "localhost" (::1), port 11000 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pgbench: error: client 3 aborted while establishing connection
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 1269
number of failed transactions: 0 (0.000%)
latency average = 236.540 ms
average connection time = 23.057 ms
tps = 42.276183 (including reconnection times)
pgbench: error: Run was aborted; the above results are incomplete.
ユーザ名に基づく負荷分散先ノードの設定
負荷分散先ノードは、backend_weight パラメータで指定した比率でランダムに選択されますが、指定した条件に基づき制御することもできます。これまで、その条件はデータベース名、アプリケーション名のみでしたが、バージョン 4.5 では、ユーザ名に基づき制御する user_redirect_preference_list パラメータが追加されました。
user_redirect_preference_list = 'postgres:1(0.5)'
# comma separated list of pairs of user name and node id.
# example: postgres:primary,user[0-4]:1,user[5-9]:2'
# valid for streaming replicaton mode only.
負荷分散先ノードの設定は ユーザ名:ノードID(割合) を , (カンマ) 区切りで指定します。ユーザ名には正規表現を指定でき、ノード ID の代わりに primary (プライマリ)、standby (スタンバイ) も指定できます。
例えば、postgres:1(0.5) と指定すると、postgres ユーザで接続した場合に、ノード ID: 1 のノードで 50% のクエリが実行されるようになります。
現状では、ユーザ名とデータベース名はそれぞれ別に指定しかできませんが、例えば、postgres ユーザが test データベースに接続した場合など、ユーザ名とデータベース名を組み合わせて指定できるようになると、より使いやすくなると思います。
pcp_socket_dir パラメータの複数ディレクトリ指定
pcp_socket_dir は、PCP が接続を受けつける Unix ドメインソケットの作成先ディレクトリを指定するパラメータです。
pcp_socket_dir = '/var/run/postgresql,/tmp'
# Unix domain socket path(s) for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
バージョン 4.4 以前はディレクトリを 1 つしか指定できませんでしたが、バージョン 4.5 では , 区切りで複数のディレクトリを指定できるようになりました。, の前後に空白が含まれていると、ファイルパスの一部と見なされてしまうため、空白を入れないように注意してください。
PostgreSQL 16 の SQL パーサ取り込み
Pgpool-II は、クエリが参照系か更新系かなどをチェックするため、PostgreSQL の SQL パーサを内包しています。バージョン 4.5 では、PostgreSQL 16 での SQL 構文の変更に対応するため、PostgreSQL 16 の SQL パーサが取り込まれました。
おわりに
Pgpool-II 4.5 の紹介はこれで終了です。
バージョン 4.5 はあまり大きな新機能はありませんでしたが、フェイルオーバやバックエンドエラー発生時の不要なセッション切断が減るのは地味にうれしい改善です。Pgpool-II のメンテナンス期間は PostgreSQL と同じく 5 年で、バージョン 4.0 は 2023 年 11 月に EOL を迎えました。古いバージョンを使っている方はこれを機にバージョンアップを検討してはいかがでしょうか？