pgpool-II導入してみた・その２(コネクションプールモード)

※DBサーバ(postgresql)が独立、clientサーバ(postgresql、initdbを実行なし)とwebサーバ(pgpool)が同梱であるサンプル
①pgpoolとpostgresのインストールは同じであるため、割愛する
②webサーバ(pgpool)を設定する
[root@node2 ~]# cd /etc/pgpool-II/　★
[root@node2 pgpool-II]# pwd
/etc/pgpool-II
[root@node2 pgpool-II]# ll
合計 208
-rwxrw-r--. 1 root root 858 5月 15 16:38 pcp.conf
-rwxrw-r--. 1 root root 40625 5月 15 16:38 pgpool.conf
-rw-r--r--. 1 root root 39335 5月 15 16:38 pgpool.conf.sample-logical
-rw-r--r--. 1 root root 40567 5月 15 16:38 pgpool.conf.sample-master-slave
-rw-r--r--. 1 root root 40515 5月 15 16:38 pgpool.conf.sample-replication
-rw-r--r--. 1 root root 40588 5月 15 16:38 pgpool.conf.sample-stream
-rwxrw-r--. 1 root root 3277 5月 15 16:38 pool_hba.conf
[root@node2 pgpool-II]# mkdir /var/log/pgpool　★ログを格納するためのディレクトリを作成する
[root@node2 pgpool-II]# chown postgres:postgres /var/log/pgpool/　★適切な権限を与える
[root@node2 pgpool-II]# vi /etc/pgpool-II/pool_passwd　★パスワードファイルを作成する
[root@node2 pgpool-II]# chown postgres:postgres /etc/pgpool-II/pool_passwd　★適切な権限を与える
[root@node2 pgpool-II]# pg_md5 -m -u postgres postgres　★
[root@node2 pgpool-II]# cat /etc/pgpool-II/pool_passwd

postgres:md53175bce1d3201d16594cebf9d7eb3f9d　★
[root@node2 pgpool-II]# vi pgpool.conf　★pgpool.confを改修する
[root@node2 pgpool-II]# egrep -i 'backend_|connection_cache|num_init_children|max_pool|enable_pool_hba|memory_cache_enabled' pgpool.conf
# num_init_children * listen_backlog_multiplier.
backend_hostname0 = '192.168.56.101'　★DBサーバを指定する必要がある
backend_port0 = 5432　★DBサーバのポート
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/11/data'　★DBサーバのデータディレクトリ
backend_flag0 = 'ALLOW_TO_FAILOVER'
#backend_hostname1 = 'host2'
#backend_port1 = 5433
#backend_weight1 = 1
#backend_data_directory1 = '/data1'
#backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on　★クライアントサーバにpostgresqlクラスタが作成されていないため、enable_pool_hbaをONにする必要がある
num_init_children = 32
max_pool = 1
connection_cache = on
failover_on_backend_error = on
memory_cache_enabled = on
[root@node2 pgpool-II]# cat pool_hba.conf　★
# pgpool Client Authentication Configuration File
# ===============================================
#
# The format rule in this file follows the rules in the PostgreSQL
# Administrator's Guide. Refer to chapter "Client Authentication" for a
＜省略＞
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust　★このエントリを使う
③クライアント側からpgpool経由でDBサーバへ接続する

-bash-4.2$ psql -h localhost -p 9999 -d testdb -U postgres　★接続１
psql (11.3)
"help" でヘルプを表示します。

testdb=#
＜省略＞
[root@node2 ~]# su - postgres
最終ログイン: 2019/06/12 (水) 13:01:43 JST日時 pts/1
-bash-4.2$ psql -h localhost -p 9999 -d testdb -U postgres　★接続２
psql (11.3)
"help" でヘルプを表示します。

testdb=#
＜省略＞
-bash-4.2$ psql -h localhost -p 9999 -d testdb -U postgres　★接続３
psql (11.3)
"help" でヘルプを表示します。

testdb=#
＜省略＞
[root@node2 pgpool-II]# ps -ef | egrep -i 'pgpool|postgres'　★poolingプロセスを確認する
root 6710 6668 0 13:01 pts/1 00:00:00 su - postgres
postgres 6711 6710 0 13:01 pts/1 00:00:00 -bash
root 6949 5848 0 13:11 pts/0 00:00:00 pgpool -n -d
root 6951 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6952 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6953 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6954 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6955 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6956 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6957 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6958 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6959 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6960 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6961 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6962 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6963 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6964 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6965 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6966 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6967 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6968 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6969 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6970 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6971 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6972 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6973 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6974 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6975 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6976 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6977 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6978 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6979 6949 0 13:11 pts/0 00:00:00 pgpool: wait for connection request
root 6980 6949 0 13:11 pts/0 00:00:00 pgpool: postgres testdb ::1(28742) idle　★poolingプロセス１
root 6981 6949 0 13:11 pts/0 00:00:00 pgpool: postgres testdb ::1(28738) idle　★poolingプロセス２
root 6982 6949 0 13:11 pts/0 00:00:00 pgpool: postgres testdb ::1(28734) idle　★poolingプロセス３
root 6983 6949 0 13:11 pts/0 00:00:00 pgpool: PCP: wait for connection request
root 6984 6949 0 13:11 pts/0 00:00:00 pgpool: worker process
root 6985 6949 0 13:11 pts/0 00:00:00 pgpool: health check process(0)
postgres 6988 6711 0 13:11 pts/1 00:00:00 psql -h localhost -p 9999 -d testdb -U postgres
root 7118 7019 0 13:12 pts/2 00:00:00 su - postgres
postgres 7119 7118 0 13:12 pts/2 00:00:00 -bash
root 7181 7081 0 13:12 pts/3 00:00:00 su - postgres
postgres 7182 7181 0 13:12 pts/3 00:00:00 -bash
postgres 7252 7119 0 13:12 pts/2 00:00:00 psql -h localhost -p 9999 -d testdb -U postgres
postgres 7253 7182 0 13:12 pts/3 00:00:00 psql -h localhost -p 9999 -d testdb -U postgres
root 7255 5848 0 13:12 pts/0 00:00:00 grep -E --color=auto -i pgpool|postgres
④DBサーバで接続バックエンドプロセスを確認する
-bash-4.2$ date;ps -ef | grep postgres;date
2019年 6月 12日 水曜日 13:12:54 JST
root 6195 5889 0 12:52 pts/0 00:00:00 su - postgres
postgres 6196 6195 0 12:52 pts/0 00:00:00 -bash
postgres 6326 1 0 12:53 pts/0 00:00:00 /usr/pgsql-11/bin/postgres
postgres 6327 6326 0 12:53 ? 00:00:00 postgres: pg11: logger
postgres 6329 6326 0 12:53 ? 00:00:00 postgres: pg11: checkpointer
postgres 6330 6326 0 12:53 ? 00:00:00 postgres: pg11: background writer
postgres 6331 6326 0 12:53 ? 00:00:00 postgres: pg11: walwriter
postgres 6332 6326 0 12:53 ? 00:00:00 postgres: pg11: autovacuum launcher
postgres 6333 6326 0 12:53 ? 00:00:00 postgres: pg11: archiver
postgres 6334 6326 0 12:53 ? 00:00:00 postgres: pg11: stats collector
postgres 6335 6326 0 12:53 ? 00:00:00 postgres: pg11: logical replication launcher
postgres 7653 6326 0 13:11 ? 00:00:00 postgres: pg11: postgres testdb 192.168.56.102(50708) idle　★接続バックエンドプロセス１
postgres 7724 6326 0 13:12 ? 00:00:00 postgres: pg11: postgres testdb 192.168.56.102(50712) idle　★接続バックエンドプロセス２
postgres 7726 6326 0 13:12 ? 00:00:00 postgres: pg11: postgres testdb 192.168.56.102(50716) idle　★接続バックエンドプロセス３
postgres 7731 6196 0 13:12 pts/0 00:00:00 ps -ef
postgres 7732 6196 0 13:12 pts/0 00:00:00 grep --color=auto postgres
2019年 6月 12日 水曜日 13:12:54 JST