Azure Database for Postgre（単一サーバ）に対してPgpoolを使ってコネクションプーリング

#はじめに
BTCのクマ松です。
この記事は **[PostgreSQL Advent Calendar 2021](https://qiita.com/advent-calendar/2021/postgresql)** 23 日目のエントリです。
最近PostgreSQLの勉強を始めました。

お客様システムのAzure Database for PostgreSQL（単一サーバ）に対してRHELのAzure VMからpgpoolを使ってコネクションプールしようと実装した際の備忘録を記載します。

<font color="Blue">Azure Database for PostgreSQL</font>には単一サーバとフレキシブルサーバというデプロイモードがあります~~が、2021年12月23日時点でフレキシブルサーバはプレビュー段階ですので、本番環境ではまだ利用が推奨されていません~~。

~~それぞれメリデメあるようなのでGAが待ち遠しいですね。~~←2021/11/30にGAされていました…！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/307047/8066705d-273a-3960-788e-00198d3842c0.png)


#やってみた
##前提
pgpoolは、Azure VM（Red Hat Enterprise Linux 8.2 LVM - Gen1）にインストールします。

```bash
[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](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/set-up-pgpool-ii-query-caching-with-azure-database-for/ba-p/788005)**

Azure Database for PostgreSQLのバージョンは11（Azureで選べる最新のバージョン）です。

VMもDBも構築し、VMからDBへ接続できるようネットワークの設定は諸々行っている状態です。


##pgpoolのリポジトリをインストール

```bash
[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!
```
※適宜リポジトリの定義ファイルを変更しましょう

```bash:/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をインストール

```bash
[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が動きませんでした。

```bash
[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!
```

```bash
[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!
```

```bash
[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でインストールできそうです。

```bash
[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 接続のために必要です。
 

```bash
[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に変更します。

```bash
[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ファイルを変更

###接続ユーザー情報を設定
定義ファイルの変更時に利用する変数を設定します。

```bash
[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認証に対応しているそうです。

```bash
[root@web01-kuma ~]# echo $USERNAME@$SERVERNAME:$PASSWORD > $pg_pass
```

```bash:/etc/pgpool-II/pool_passwd
test@kumamatsu:XXXXXXXXXXXX
```

###定義ファイルを変更

※バックアップはしっかり

```bash
[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
```



```bash
[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
```

```bash:/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.


```

```bash
[root@web01-kuma ~]# systemctl start pgpool.service
[root@web01-kuma ~]# systemctl status pgpool.service
```

```bash
[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）で接続します。

```bash
[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数やログの出力先の変更も行うことになります。
時間の都合上省略させていただきました。
読んでいただきありがとうございました！