pgpool II 4.1.4 load balance

이중화된 DB를 자동으로 fail-over 하고 두개 서버를 Active (Read/Write) - Active (Read only) 구성을 하기 위해서

pgpool 을 시도해 보려고 합니다.

뒤져보다 보니 성능이 문제가 된다고 하는 사람도 있는것 같긴한데 함쓰 앱 수준에서는 안정성 측면에서 더 낫겠다 싶습니다.

 

pgpool II 설치

먼저 pgpool 설치를 하겠습니다. (Master / Slave)

root@ggmoney-db1:/var/lib/postgresql/13# apt-get install pgpool2
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  eatmydata libeatmydata1 python3-certifi python3-importlib-metadata python3-jinja2 python3-json-pointer
  python3-jsonpatch python3-jsonschema python3-markupsafe python3-more-itertools python3-pyrsistent python3-requests
  python3-urllib3 python3-zipp
Use 'apt autoremove' to remove them.
The following additional packages will be installed:
  libmemcached11 libpgpool0
The following NEW packages will be installed:
  libmemcached11 libpgpool0 pgpool2
0 upgraded, 3 newly installed, 0 to remove and 34 not upgraded.
Need to get 1,106 kB of archives.
After this operation, 5,891 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://us.archive.ubuntu.com/ubuntu hirsute/universe amd64 libpgpool0 amd64 4.1.4-2 [24.2 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu hirsute/main amd64 libmemcached11 amd64 1.0.18-4.2ubuntu2 [83.5 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu hirsute/universe amd64 pgpool2 amd64 4.1.4-2 [998 kB]
Fetched 1,106 kB in 2s (554 kB/s)
Selecting previously unselected package libpgpool0.
(Reading database ... 114574 files and directories currently installed.)
Preparing to unpack .../libpgpool0_4.1.4-2_amd64.deb ...
Unpacking libpgpool0 (4.1.4-2) ...
Selecting previously unselected package libmemcached11:amd64.
Preparing to unpack .../libmemcached11_1.0.18-4.2ubuntu2_amd64.deb ...
Unpacking libmemcached11:amd64 (1.0.18-4.2ubuntu2) ...
Selecting previously unselected package pgpool2.
Preparing to unpack .../pgpool2_4.1.4-2_amd64.deb ...
Unpacking pgpool2 (4.1.4-2) ...
Setting up libpgpool0 (4.1.4-2) ...
Setting up libmemcached11:amd64 (1.0.18-4.2ubuntu2) ...
Setting up pgpool2 (4.1.4-2) ...

Creating config file /etc/pgpool2/pgpool.conf with new version
Created symlink /etc/systemd/system/multi-user.target.wants/pgpool2.service → /lib/systemd/system/pgpool2.service.
Processing triggers for man-db (2.9.4-2) ...
Processing triggers for libc-bin (2.33-0ubuntu5) ...
Scanning processes...
Scanning candidates...
Scanning linux images...

Restarting services...
 systemctl restart accounts-daemon.service multipathd.service packagekit.service polkit.service rsyslog.service ssh.service systemd-journald.service systemd-networkd.service systemd-resolved.service systemd-timesyncd.service udisks2.service
Service restarts being deferred:
 /etc/needrestart/restart.d/dbus.service
 systemctl restart networkd-dispatcher.service
 systemctl restart systemd-logind.service
 systemctl restart unattended-upgrades.service

No containers need to be restarted.

User sessions running outdated binaries:
 root @ user manager service: systemd[2251]
root@ggmoney-db1:/var/lib/postgresql/13# pgpool -v
pgpool-II version 4.1.4 (karasukiboshi)
root@ggmoney-db1:/var/lib/postgresql/13#

다행히 apt-get 을 이용하여 설치를 했더니 4.1.4 버전이 설치되었어요.

 

먼저 로드밸런싱 기능을 셋팅해 보겠습니다.

클라이언트는 5432포트가 아닌 pgpool 5433 으로 최초 접속하고 pgpool 이 select 노드를 분산시켜 주는 기능이라고 합니다.

분산된 요청은 다시 2개의 DB 5432 포트로 들어가는 구조로 되어 있나 봅니다.

 

https://skysoo1111.tistory.com/66 

# PostgreSQL HA 구성 - PGPoolⅡ

https://browndwarf.tistory.com/6

PostgreSQL HA 구성 - 2. PGPool을 활용한 Load Balance 구현

 

이 블로글들을 참조하였습니다.

 

기본 설정

먼저 설정 파일들을 확인해봤습니다.

drwxr-xr-x   2 root root      4096 Jun 19 09:26 .
drwxr-xr-x 102 root root      4096 Jun 19 08:12 ..
-rw-r--r--   1 root root       858 Oct  9  2020 pcp.conf
-rw-r-----   1 root postgres 43588 Jun 19 09:25 pgpool.conf
-rw-r--r--   1 root root      3476 Oct  9  2020 pool_hba.conf
-rw-r-----   1 root postgres     0 Jun 19 09:26 pool_passwd
root@ggmoney-db1:/etc/pgpool2#

pgpool.conf 설정

// 먼저 기존 설정 파일 백업
root@ggmoney-db1:/etc/pgpool2# cp /etc/pgpool2/pgpool.conf /etc/pgpool2/pgpool.conf.bak

// 설정이 엄청 긴데요. 바꾼것만 표기 하겠습니다.
root@ggmoney-db1:/etc/pgpool2# vi /etc/pgpool2/pgpool.conf

listen_addresses = '*'

# - Backend Connection Settings -

# 첫번째 서버 정보
backend_hostname0 = '10.34.96.3'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 0.5
                                   # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/postgresql/13/main'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
                                   # or ALWAYS_MASTER
backend_application_name0 = 'server0'
                                   # walsender's application_name, used for "show pool_nodes" command

# 두번째 서버 정보
backend_hostname1 = '10.34.96.4'
backend_port1 = 5432
backend_weight1 = 0.5
backend_data_directory1 = '/var/lib/postgresql/13/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'

load_balance_mode = on

master_slave_mode = on

# - Streaming -

sr_check_period = 5
                                   # Streaming replication check period
                                   # Disabled (0) by default
sr_check_user = 'postgres'
                                   # Streaming replication check user
                                   # This is necessary even if you disable
                                   # streaming replication delay check with
                                   # sr_check_period = 0

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

sr_check_database = 'postgres'
                                   # Database name for streaming replication check
delay_threshold = 0
                                   # Threshold before not dispatching query to standby node
                                   # Unit is in bytes
                                   # Disabled (0) by default

여기까지 한다음에는 pgpool 을 재시작 하겠습니다.

// pgpool 중지
root@ggmoney-db1:/etc/pgpool2# systemctl stop pgpool2.service

// 로그 디렉토리에 pgpool_status 삭제
// 이거 삭제 안했다가 한참 헤맸습니다. (나중에 slave 서버가 안잡히드라구요)
// 삭제전에 서비스 중지먼저 하고 삭제, 그리고 다시 스타트해야 되더라구요
// 서비스 중지 안하고 삭제했더니.. 계속 안붙다가... 중지->삭제->시작 다시 했더니 됩니다.
root@ggmoney-db1:/etc/pgpool2# rm /var/log/postgresql/pgpool_status

root@ggmoney-db1:/etc/pgpool2# systemctl start pgpool2.service

pgpool 상태를 확인해보겠습니다.

이제 DB를 접속할때 5432가 아니라 pgpool 로 접속해야 됩니다.

root@ggmoney-db1:/etc/pgpool2# psql -h 10.34.96.3 -p 5433 -U postgres

psql (13.3 (Ubuntu 13.3-0ubuntu0.21.04.1))
Type "help" for help.

postgres=# show pool_nodes;

 node_id |  hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | 10.34.96.3 | 5432 | up     | 0.500000  | primary | 485        | false             | 0                 |                   |                        | 2021-06-19 09:42:26
 1       | 10.34.96.4 | 5432 | up     | 0.500000  | standby | 10         | true              | 0                 |                   |                        | 2021-06-19 09:42:26
(2 rows)

둘다 잘 올라왔네요.