#はじめに
BTCのクマ松です。
この記事は PostgreSQL Advent Calendar 2021 23 日目のエントリです。
最近PostgreSQLの勉強を始めました。
お客様システムのAzure Database for PostgreSQL（単一サーバ）に対してRHELのAzure VMからpgpoolを使ってコネクションプールしようと実装した際の備忘録を記載します。
Azure Database for PostgreSQLには単一サーバとフレキシブルサーバというデプロイモードがありますが、2021年12月23日時点でフレキシブルサーバはプレビュー段階ですので、本番環境ではまだ利用が推奨されていません。
~~それぞれメリデメあるようなのでGAが待ち遠しいですね。~~←2021/11/30にGAされていました…！
#やってみた
##前提
pgpoolは、Azure VM（Red Hat Enterprise Linux 8.2 LVM - Gen1）にインストールします。
[azureuser@web01-kuma ~]$ cat /etc/os-release
NAME="Red Hat Enterprise Linux"
VERSION="8.2 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.2"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.2 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8.2:GA"
HOME_URL="https://www.redhat.com/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"
REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.2
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.2"
Ubuntuの場合は、Azure Database for PostgreSQL Blogの方に手順が書いてあるようです。
Set up Pgpool-II Query Caching with Azure Database for PostgreSQL
Azure Database for PostgreSQLのバージョンは11（Azureで選べる最新のバージョン）です。
VMもDBも構築し、VMからDBへ接続できるようネットワークの設定は諸々行っている状態です。
##pgpoolのリポジトリをインストール
[azureuser@web01-kuma ~]$ sudo dnf install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-8-x86_64/pgpool-II-release-4.2-1.noarch.rpm
Last metadata expiration check: 0:03:48 ago on Wed 22 Dec 2021 02:22:04 PM UTC.
pgpool-II-release-4.2-1.noarch.rpm 38 kB/s | 9.2 kB 00:00
Dependencies resolved.
～～～省略～～～
Installed:
pgpool-II-release-4.2-1.noarch
Complete!
※適宜リポジトリの定義ファイルを変更しましょう
/etc/yum.repos.d/pgpool-II-release-42.repo
[pgpool42]
name=pgpool-II 4.2 $releasever - $basearch
#baseurl=https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-$releasever-$basearch
baseurl=https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-8-$basearch
#enabled=1
enabled=0
##リポジトリからpgpool-IIをインストール
[azureuser@web01-kuma ~]$ sudo dnf install -y pgpool-II-pg11.x86_64 --enablerepo=pgpool42
pgpool-II 4.2 8.2 - x86_64 901 kB/s | 281 kB 00:00
Dependencies resolved.
～～～省略～～～
Installed:
libmemcached-libs-1.0.18-15.el8.x86_64 libpq-12.7-1.el8_2.x86_64 pgpool-II-pg11-4.2.6-1pgdg.rhel8.x86_64
Complete!
PostgreSQLをインストール
※Serverのインストールをしないとpgpoolが動きませんでした。
[azureuser@web01-kuma ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-8-x86_64/postgresql11-libs-11.11-1PGDG.rhel8.x86_64.rpm
Last metadata expiration check: 0:03:10 ago on Wed 22 Dec 2021 02:40:33 PM UTC.
postgresql11-libs-11.11-1PGDG.rhel8.x86_64.rpm 186 kB/s | 391 kB 00:02
Dependencies resolved.
～～～省略～～～
Installed:
postgresql11-libs-11.11-1PGDG.rhel8.x86_64
Complete!
[azureuser@web01-kuma ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-8-x86_64/postgresql11-11.11-1PGDG.rhel8.x86_64.rpm
Last metadata expiration check: 0:04:35 ago on Wed 22 Dec 2021 02:40:33 PM UTC.
postgresql11-11.11-1PGDG.rhel8.x86_64.rpm 1.1 MB/s | 1.7 MB 00:01
Dependencies resolved.
～～～省略～～～
Installed:
libicu-60.3-2.el8_1.x86_64 postgresql11-11.11-1PGDG.rhel8.x86_64
Complete!
[azureuser@web01-kuma ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-8-x86_64/postgresql11-server-11.11-1PGDG.rhel8.x86_64.rpm
Last metadata expiration check: 0:06:14 ago on Wed 22 Dec 2021 02:40:33 PM UTC.
postgresql11-server-11.11-1PGDG.rhel8.x86_64.rpm 1.7 MB/s | 4.9 MB 00:02
Dependencies resolved.
～～～省略～～～
Installed:
postgresql11-server-11.11-1PGDG.rhel8.x86_64
Complete!
※「dnf install posgresql」の場合、postgresql10.17がインストールされたためrpmで直接インストール。11以外のメジャーバージョンはpgdgでインストールできそうです。
[azureuser@web01-kuma ~]$ dnf list "postgresql*-server"
Last metadata expiration check: 0:00:07 ago on Fri 06 Aug 2021 12:58:16 PM JST.
Available Packages
postgresql10-server.x86_64 10.17-1PGDG.rhel8 pgdg10
postgresql12-server.x86_64 12.5-1PGDG.rhel8 pgdg12
postgresql13-server.x86_64 13.1-1PGDG.rhel8 pgdg13
postgresql96-server.x86_64 9.6.22-1PGDG.rhel8 pgdg96
証明書の作成
SSLで接続する為に証明書を作成します。
ssl_key や ssl_cert はクライアントと、pgpool2 間の SSL 接続のために必要です。
[root@web01-kuma ~]# sudo su -
[root@web01-kuma ~]# openssl req -new -newkey rsa:4096 -days 3650 -nodes -x509 -subj "/C=JP/ST=Tokyo/O=example" -keyout /etc/pgpool-II/server.key -out /etc/pgpool-II/server.crt
Generating a RSA private key
............................................++++
................................++++
writing new private key to '/etc/pgpool-II/server.key'
-----
[root@web01-kuma ~]# ls /etc/pgpool-II/server.key
/etc/pgpool-II/server.key
作成した証明書の所有者をpostgreに変更します。
[root@web01-kuma ~]# chown postgres:postgres /etc/pgpool-II/server*
[root@web01-kuma ~]# ls /etc/pgpool-II/server* -l
-rw-r--r--. 1 postgres postgres 1879 Dec 22 15:17 /etc/pgpool-II/server.crt
-rw-------. 1 postgres postgres 3272 Dec 22 15:17 /etc/pgpool-II/server.key
pgpoolのconfファイルを変更
###接続ユーザー情報を設定
定義ファイルの変更時に利用する変数を設定します。
[azureuser@web01-kuma ~]$ sudo su -
[root@web01-kuma ~]# USERNAME="test"
[root@web01-kuma ~]# SERVERNAME="kumamatsu"
[root@web01-kuma ~]# PASSWORD="XXXXXXXXXXXX"
[root@web01-kuma ~]# pg_conf="/etc/pgpool-II/pgpool.conf"
[root@web01-kuma ~]# pg_pass="/etc/pgpool-II/pool_passwd"
Azure Database for PostgreSQL単一サーバーではmd5認証のサポートはされておらず、またMicrosoftのサポートの方曰く、今後サポートされる計画もないそうです。
パスワードを平文で腹持ちすることになります。
※Azure Database for PostgreSQLフレキシブルサーバーであればmd5認証に対応しているそうです。
[root@web01-kuma ~]# echo $USERNAME@$SERVERNAME:$PASSWORD > $pg_pass
/etc/pgpool-II/pool_passwd
test@kumamatsu:XXXXXXXXXXXX
###定義ファイルを変更
※バックアップはしっかり
[root@web01-kuma ~]# cp /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool.conf.org.20211223
[root@web01-kuma ~]# cp /etc/pgpool-II/pool_hba.conf /etc/pgpool-II/pool_hba.conf.org.20211223
[root@web01-kuma ~]# sed -i "s/backend_hostname0 = 'host1'/backend_hostname0 = '$SERVERNAME.postgres.database.azure.com'/g" $pg_conf
[root@web01-kuma ~]# sed -i 's/ssl = off/ssl = on/g' $pg_conf
[root@web01-kuma ~]# sed -i "s/#ssl_key/ssl_key/g" $pg_conf
[root@web01-kuma ~]# sed -i "s/#ssl_cert/ssl_cert/g" $pg_conf
[root@web01-kuma ~]# sed -i "s/sr_check_user = 'nobody'/sr_check_user = '$USERNAME@$SERVERNAME'/g" $pg_conf
[root@web01-kuma ~]# sed -i 's/enable_pool_hba = off/enable_pool_hba = on/g' $pg_conf
/etc/pgpool-II/pgpool.conf
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = 'localhost'
port = 9999
～～～省略～～～
# - Backend Connection Settings -
backend_hostname0 = 'kumamatsu.postgres.database.azure.com'
# Host name or IP address to connect to for backend 0
～～～省略～～～
# sr_check_user はプライマリサーバーの特定のためにも使用されるため、値の入力が推奨されるようです。
# pool_passwd にusernameとして記載されていれば、パスワードはpool_passwdのものが使用されます。
sr_check_user = 'test@kumamatsu'
sr_check_password = ''
# Password for streaming replication check user
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
# 今回はデフォルトのままですが、接続したいDatabaseを適宜変更してください
sr_check_database = 'postgres'
# Database name for streaming replication check
～～～省略～～～
# - SSL Connections -
ssl = on
# Enable SSL support
# (change requires restart)
ssl_key = 'server.key'
# SSL private key file
# (change requires restart)
ssl_cert = 'server.crt'
# SSL public certificate file
# (change requires restart)
～～～省略～～～
# - Authentication -
enable_pool_hba = on
# Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd'
# File name of pool_passwd for md5 authentication.
# "" disables pool_passwd.
[root@web01-kuma ~]# systemctl start pgpool.service
[root@web01-kuma ~]# systemctl status pgpool.service
[root@web01-kuma ~]# systemctl status pgpool.service
● pgpool.service - Pgpool-II
Loaded: loaded (/usr/lib/systemd/system/pgpool.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2021-12-22 15:44:06 UTC; 3min 25s ago
Main PID: 25476 (pgpool)
Tasks: 36 (limit: 4622)
Memory: 149.1M
CGroup: /system.slice/pgpool.service
tq25476 /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -n
tq25478 pgpool: wait for connection request
tq25479 pgpool: wait for connection request
tq25480 pgpool: wait for connection request
tq25481 pgpool: wait for connection request
tq25482 pgpool: wait for connection request
tq25483 pgpool: wait for connection request
tq25484 pgpool: wait for connection request
tq25485 pgpool: wait for connection request
tq25486 pgpool: wait for connection request
tq25487 pgpool: wait for connection request
tq25488 pgpool: wait for connection request
tq25489 pgpool: wait for connection request
tq25490 pgpool: wait for connection request
tq25491 pgpool: wait for connection request
tq25492 pgpool: wait for connection request
tq25493 pgpool: wait for connection request
tq25494 pgpool: wait for connection request
tq25495 pgpool: wait for connection request
tq25496 pgpool: wait for connection request
tq25497 pgpool: wait for connection request
tq25498 pgpool: wait for connection request
tq25499 pgpool: wait for connection request
tq25500 pgpool: wait for connection request
tq25501 pgpool: wait for connection request
tq25502 pgpool: wait for connection request
tq25503 pgpool: wait for connection request
tq25504 pgpool: wait for connection request
tq25505 pgpool: wait for connection request
tq25506 pgpool: wait for connection request
tq25507 pgpool: wait for connection request
tq25508 pgpool: wait for connection request
tq25509 pgpool: wait for connection request
tq25510 pgpool: PCP: wait for connection request
tq25511 pgpool: worker process
mq25512 pgpool: health check process(0)
Dec 22 15:44:06 web01-kuma pgpool[25476]: 2021-12-22 15:44:06: pid 25476: LOG: pool_discard_oid_maps: discarded memqcache oid maps
Dec 22 15:44:06 web01-kuma pgpool[25476]: 2021-12-22 15:44:06: pid 25476: LOG: Setting up socket for 0.0.0.0:9999
Dec 22 15:44:06 web01-kuma pgpool[25476]: 2021-12-22 15:44:06: pid 25476: LOG: Setting up socket for :::9999
Dec 22 15:44:06 web01-kuma pgpool[25476]: 2021-12-22 15:44:06: pid 25476: LOG: find_primary_node_repeatedly: waiting for finding a primary node
Dec 22 15:44:07 web01-kuma pgpool[25476]: 2021-12-22 15:44:07: pid 25476: LOG: find_primary_node: primary node is 0
Dec 22 15:44:07 web01-kuma pgpool[25476]: 2021-12-22 15:44:07: pid 25510: LOG: PCP process: 25510 started
Dec 22 15:44:07 web01-kuma pgpool[25476]: 2021-12-22 15:44:07: pid 25511: LOG: process started
Dec 22 15:44:07 web01-kuma pgpool[25476]: 2021-12-22 15:44:07: pid 25512: LOG: process started
Dec 22 15:44:07 web01-kuma pgpool[25476]: 2021-12-22 15:44:07: pid 25476: LOG: pgpool-II successfully started. version 4.2.6 (chichiriboshi)
Dec 22 15:44:07 web01-kuma pgpool[25476]: 2021-12-22 15:44:07: pid 25476: LOG: node status[0]: 1
##接続確認
pgpool Connection Settingで指定したlisten_addresses（localhost）とport（9999）で接続します。
[root@web01-kuma ~]# psql -h localhost -U test@kumamatsu -d postgres -p 9999
psql (11.11)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
#おわり
実際は上記の作業に加え、pgpool.conf上でconnection数やログの出力先の変更も行うことになります。
時間の都合上省略させていただきました。
読んでいただきありがとうございました！