はじめに
PostgreSQLはストリーミングレプリケーションで動かし、pgpool-IIを利用して負荷分散させる環境構築メモです。
環境
使用した環境は以下のとおり。
- CentOS 7.5（firewalldとSELinuxは無効化済み）
- PostgreSQL 11.2
- pgpool-II 4.0.5
以下で構築したものを利用します。
pgpool-IIのインストールは以下を参考しました。
構築
pgpool-IIのリポジトリパッケージをインストールします。
以下のURLから「RHEL/CentOS 7.x (Pgpool-II 4.0)」を利用します。
# yum install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm
続けて、PostgreSQL11用のpgpool-IIをインストールします。
# yum install -y pgpool-II-pg11.x86_64 pgpool-II-pg11-debuginfo.x86_64 pgpool-II-pg11-devel.x86_64 pgpool-II-pg11-extensions.x86_64
インストール後に"pgpool -v"コマンドで確認します。
# pgpool -v
pgpool-II version 4.0.5 (torokiboshi)
pgpoolのサーバでもpsqlを使いたいので、以下のようにインストールしておきます。
# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum -y install postgresql11
また、PostgreSQLサーバでは以下をインストールします。
（って書いてあったけど、他を探しても同じようにインストールしているのを見つけられなかった。本当に必要なのか？？？）
# yum install pgpool-II-pg11-extensions.x86_64
pgpool-IIの自動起動設定と起動
pgpoolを自動起動するように設定し、そのまま起動させます。
※今回はrootユーザで起動していますが、pgpoolのようなユーザを作成して起動するようにしたほうが良いです。
# systemctl enable pgpool.service
# systemctl start pgpool.service
pgpool-IIの設定変更
PostgreSQLはストリーミングレプリケーションを使用しているため、サンプル設定ファイルpgpool.conf.sample-streamを利用します。
まず、以下のように今の設定ファイルのバックアップと、サンプル設定のコピーを実行します。
# cp -p /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool.conf.org
# cp /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf
pgpool.confを修正します。
vi /etc/pgpool-II/pgpool.conf
PostgreSQLのストリームレプリケーションと組み合わせる場合、pgpoo-IIはマスタースレーブモードを利用します。
マスタースレーブモードは以下の2つの設定を実施します。サンプルをコピーしているので設定済みです。
master_slave_mode = on
master_slave_sub_mode = 'stream'
負荷分散を有効に設定する。
load_balance_mode = on
バックエンドデータの設定を以下のように行います。
バックエンドとなるPostgreSQLサーバの数だけ設定します。
今回は2台構成なので、それぞれのパラメータの最後に0,1がついた設定を実施しています。
backend_hostname0 = '192.168.10.168' # バックエンドのホスト名かIPアドレス
backend_port0 = 5432 # バックエンドのポート番号
backend_weight0 = 1 # 負荷分散時の比率
backend_data_directory0 = '/data' # バックエンドのデータベースクラスタのディレクトリ
backend_flag0 = 'ALLOW_TO_FAILOVER' # バックエンドの挙動。今回は気にせずデフォルトの設定を利用
backend_hostname1 = '192.168.10.169'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
コネクションプールの設定を変更します。
connection_cache = on # バックエンドへの接続をキャッシュする
num_init_children = 30 # pgpool-IIのプロセスからバックエンドへの接続の数
max_pool = 2 # Pgpool-IIの各子プロセスがキャッシュするコネクションの最大数
pgool-IIの接続設定。listen_addressesはリモートからアクセスできるように"*"を設定し、portは"9999"に設定しています。
listen_addresses = '*'
port = 9999
ログの設定を変更。
デフォルトだと通常はほとんどログが出力されなくて、動作を確認できなかったので、一時的に変更。通常はデフォルトで十分？
client_min_messages = log # デフォルトはnotice
log_min_messages = info # デフォルトはwarning
ストリーミングレプリケーションのチェックの設定
sr_check_user = 'postgres' # ストリーミングレプリケーションのチェックを行うPostgreSQLユーザ名を指定
sr_check_password = '' # ストリーミングレプリケーションのチェックを行うsr_check_user PostgreSQLユーザのパスワードを指定
sr_check_database = 'postgres' # ストリーミングレプリケーションの遅延チェックを行うデータベース名を指定
上の設定でsr_check_passwordを設定しません。パスワードなしで接続できるように、pg_md5コマンドでpool_passwdに設定します。
# pg_md5 --md5auth --username=postgres postgres
# cat /etc/pgpool-II/pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
これで設定は終了。
負荷分散させておりどちらのサーバでSQLが実行されているか分からないので、確認のためログにSQLが表示されるように設定を変更。
testdb=# ALTER DATABASE testdb SET log_statement = 'all';
ALTER DATABASE
# 戻す場合は以下を実行
testdb=# ALTER DATABASE testdb SET log_statement = DEFAULT;
psqlを用いて以下のようにSQLを実行したら、各サーバのログにSQLが出力され、負荷分散されていることを確認できます。
# psql -h localhost -p 9999 -U testuser testdb -c "select * from test"
2019-05-27 13:14:47.180 CEST [3297] LOG: statement: select * from test
pgpoolとクライアント間でMD5接続
PostgreSQLでの接続でmd5を使用している場合は、pgpoolとクライアント間でもMD5で接続するように設定する必要があります。
まず、pgpool.confで"enable_pool_hba = on"と設定します。
# vi /etc/pgpool-II/pgpool.conf
enable_pool_hba = on
pg_md5コマンドで認証ファイル（pool_passwd）を作成します。
"--username"でユーザ名を指定し、その後にパスワードを指定します。
実行すると"/etc/pgpool-II/pool_passwd"ファイルが生成されます。
# pg_md5 --md5auth --username=testuser testuser
# cat /etc/pgpool-II/pool_passwd
testuser:md5d70a0452418aeb8fb4030eae69ca2856
次にpool_hba.confに接続情報を追加します。
# vi /etc/pgpool-II/pool_hba.conf
host testdb testuser 192.168.10.0/24 md5
後はpgpoolを再起動します。
# systemctl restart pgpool
pgbenchでベンチマークを実行する
PostgreSQLではベンチマーク用のツールpgbenchが標準で同梱されています。
まずは、pgbenchでベンチマーク用のテーブルとデータを作成します。
$ pgbench -i testdb
$ pgbench -c 10 -t 100 testdb -h 192.168.10.163 -p 9999 -S
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
latency average = 7.983 ms
tps = 1252.714760 (including connections establishing)
tps = 1257.614213 (excluding connections establishing)
その他
(1) pgpoolにpsqlで接続しようとしたときに以下のエラーが発生しました。
原因は、pg_hba.confの設定ミスで、マスターとスレーブの設定が違うものになっていることでした。同じ設定になるように修正したらエラーは解消しました。
# psql -h localhost -p 9999 -U testuser testdb
psql: ERROR: unable to read message length
(2) pgpoolが起動しなくなる
pgpoolを強制終了してしまうとSocketのファイルが残ってしまい、以下のように起動しなくなることがあります。
Jun 01 00:35:10 pgpoolserver1 systemd[1]: Started Pgpool-II.
Jun 01 00:35:10 pgpoolserver1 systemd[1]: Starting Pgpool-II...
Jun 01 00:35:10 pgpoolserver1 pgpool[3410]: 2019-06-01 00:35:10: pid 3410: FATAL: failed to bind a socket: "/tmp/.s.PGSQL.9999"
Jun 01 00:35:10 pgpoolserver1 pgpool[3410]: 2019-06-01 00:35:10: pid 3410: DETAIL: bind socket failed with error: "Address already in use"
ファイルを削除すれば、起動するようになります。