pgpool-II(4) | pgpool-II과 PostgreSQL 서버 연결 (pgpool-II to PostgreSQL Server)

설치 및 기본 구성을 완료 했으면 각자 서버에 맞게 파라미터를 변경하여 PostgreSQL 서버와 pgpool-II 을 연결해보도록 하겠습니다.

파라미터 변경은 우선 primary 서버에서만 설정 해주도록 합니다! (뒤에 이유 나옴!)

pgpool-II 파라미터 변경

변경 할 파라미터가 많아서 파트 별로 파라미터를 간단히 정리해보면서 변경해보도록 하겠습니다.

Connection & Authentication

#------------------------------------------------------------------------------
# CONNECTION
#------------
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
#---------------
# AUTEHNTICATION
#---------------
enable_pool_hba = on

• backend_clustering_mode : PostgrerSQL 서버를 동기화 하는 방법을 정의합니다. 지정할 수 있는 클러스터링 모드는 6종류 입니다.
• listen_address : pgpool 연결을 허용할 address를 지정합니다. * 는 모든 address를 허용합니다.
• port : pgpool 연결을 수신하기 위한 port number를 지정합니다. pgpool-II의 기본 포트인 9999로 들어오는 모든 연결을 허용합니다.
• pcp_listen_address : pcp 프로세스가 TCP/IP 연결 할 호스트 이름 또는 IP 주소를 지정합니다. *는 모든 address를 허용합니다.
• pcp_port : pcp 프로세스가 연결을 수신하기 위한 port number를 지정합니다. pgpool-II PCP의 기본 포트인 9898로 들어오는 모든 연결을 허용합니다.
• enable_pool_hba : pgpool-II 은 클라이언트 인증을 위해 pool_hba.conf라는 config파일을 사용합니다. 이 파일의 형식은 pg_hba.conf 파일 형식과 같습니다. (이 파일과 관련된 인증방법에 대해서는 추후 따로 포스팅을 할 예정입니다.)

BACKEND

pgpool-II과 연결할 백엔드 노드를 설정하는 파트 입니다. 각 파라미터 뒤에 숫자로 해당 노드 번호를 입력해, 노드별 파라미터임을 구분합니다.

#---------
# BACKEND
#---------

backend_hostname0 = '192.168.40.133'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/test/pg/14/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = '192.168.40.133'

backend_hostname1 = '192.168.40.134'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/test/pg/14/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = '192.168.40.134'

• backend_hostname : 연결할 PostgreSQL 백엔드의 IP를 지정합니다.
• 저는 PostgreSQL 서버의 IP를 각각 지정해주었습니다.
• backend_port : 백엔드의 포트번호 지정합니다. 5432는 postgresql의 기본 포트 입니다.
• backend_weight : 백엔드의 부하 분산할 때 사용할 비율을 지정합니다.
• backend_data_directory : 백엔드의 데이터 클러스터 디렉토리를 지정합니다. Online Recovery일 경우에만 사용합니다.
• backend_flag : 다양한 백엔드 동작을 제어합니다. ‘|’ 를 사용하여 여러 플래그를 지정할 수 있습니다. 아래 3가지의 플래그를 지정할 수 있습니다.
  1. ALLOW_TO_FAILOVER : 장애 조치 또는 백엔드 분리를 허용
  2. DISALLOW_TO_FAILOVER : 장애 조치 또는 분리 허용하지 않음. HA 소프트웨어를 사용하여 백엔드를 보호
  3. ALWAYS_PRIMARY : 스트리밍 복제 모드에서만 유용.설정된 노드를 항상 Primary로 인
• backend_application_name : Primary 노드에서 WAL 로그를 수신하는 walreceiver의 애플리케이션 이름 지정할 수 있습니다.

HEARTH CHECK & STREAMING REPLICATION CHECK

#-------------
# HEALTH CHECK
#-------------
health_check_period = 10
health_check_timeout = 30
health_check_user = 'postgres'
health_check_password = ''
health_check_database = 'postgres'
health_check_max_retries = 5
#----------------------------
# STREAMING REPLICATION CHECK
#----------------------------
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = ''
sr_check_database = 'postgres'

• health_check_period : health check 사이의 간격을 초 단위로 지정합니다.
• health_check_timeout : TCP 연결이 시간 내에 성공하지 못한 경우 백엔드 PostgreSQL 연결을 포기할 시간을 지정합니다.
• health_check_user : health check를 수행할 사용자 이름을 지정합니다. 이 파라미터에 지정한 사용자는 백엔드로 지정한 모든 PostgreSQL 백엔드에 동일한 이름의 사용자가 존재해야 합니다.
• health_check_password : health check를 수행하기 위해 health_check_user에 구성된 사용자의 password를 지정합니다. ‘’로 빈칸으로 주게 되면 .pool_passwd에서 password를 읽어옵니다.
• health_check_database : health check를 수행할 데이터베이스 이름을 지정합니다.
• health_check_max_retries : health Check를 실패하면 포기하고 failover를 시작하기 전에 수행할 최대 재시도 횟수를 지정합니다.
• sr_check_period : streaming replication delay check를 확인하는 시간 간격을 지정합니다.
• sr_check_user : streaming replication check를 수행할 PostgreSQL 사용자의 이름을 지정합니다.
• sr_check_password : streaming replication check를 수행할 사용자의 password를 지정합니다. ‘’로 빈칸으로 주게 되면 .pool_passwd에서 password를 읽어옵니다.
• sr_check_database : streaming replication delay check를 수행할 데이터베이스를 지정합니다.

WATCHDOG

watchdog설정도 backend 설정과 마찬가지로 뒤에 노드 번호를 입력해 파라미터를 구분합니다.

#----------
# WATCHDOG
#----------
use_watchdog = on
delegate_IP = '192.168.40.200'
if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add 192.168.40.200/24 dev ens33 label ens33:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del 192.168.40.200/24 dev ens33'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $ -w 1 -I ens33'‘

# WATCHDOG COMMUNICATION SETTINGS
hostname0 = '192.168.40.133'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '192.168.40.134'
wd_port1 = 9000
pgpool_port1 = 9999
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_hostname0 = '192.168.40.133'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = '192.168.40.134'
heartbeat_port1 = 9694
heartbeat_device1 = ''

• use_watchdog : On 시 watchdog 프로세스가 활성화됩니다.
• delegate_IP : 클라이언트 서버에서 연결되는 pgpool의 가상 IP 주소(Virtual IP) 지정합니다. 사용자는 이 VIP로 접속하고 pgpool-II 이 각 해당하는 postgresql 백엔드 노드로 연결 시킵니다.
• if_cmd_path : pgpool-II의 VIP를 전환 하는데 사용할 커맨드의 위치를 지정합니다.
• if_up_cmd : VIP를 불러오는 커맨드를 지정합니다.
• if_down_cmd : VIP를 내리는 커맨드를 지정합니다.
• arping_path : pgpool이 VIP 전환 후 ARP요청을 보내는데 사용할 커맨드의 경로를 지정합니다.
• arping_cmd : VIP 전환 후 ARP 보내는데 사용할 커맨드를 지정합니다.
• hostname : pgpool 서버의 호스트 이름 또는 IP 주소를 지정합니다.
• wd_port : watchdog 프로세스가 사용할 포트 번호를 입력합니다. watchdog의 기본 포트는 9000입니다.
• pgpool_port : watchdog 프로세스에게 알려줄 pgpool-II 포트 번호를 입력합니다.
• wd_lifecheck_method : pgpool 상태를 확인하는 lifecheck 방법을 지정합니다.
• wd_interval : watchdog이 pgpool의 상태를 알아보는 주기를 지정합니다.
• wd_heartbeat_keepalive : heartbeat 신호를 보내는 간격을 지정합니다.
• wd_heartbeat_deadtime : 해당 시간 내에 heartheat 신호가 수신되지 않는 경우. 원격 watchdog 노드를 failed/dead노드로 표시하는데, 그 시간을 지정합니다.
• heartbeat_hostname : heartbeat 신호를 보내고 받기 위한 IP 주소 또한 호스트 이름을 지정합니다.
• heartbeat_port : heartbeat 신호를 보내고 받기 위한 포트 번호를 지정합니다. 기본 포트는 9694입니다.
• heartbeat_device : heartbeat 신호를 보내고 받기 위한 네트워크 장치 이름을 지정합니다.

LOGGING & SOCKET DIRECTORY

전 Socket 디렉토리 관리를 편하게 하기 위해 모두 같은 디렉토리인 ‘/var/run/test’ 디렉토리 안으로 설정 해주었습니다.

#---------
# LOGGING
#---------
log_destination = 'stderr'
logging_collector = on
log_directory = '/test/pg/14/log/pgpool'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
#-----------------
# SOCKET DIRECTORY
#-----------------
socket_dir = '/var/run/test'
pcp_socket_dir = '/var/run/test'
wd_ipc_socket_dir = '/var/run/test'
pid_file_name = '/var/run/test/pgpool.pid'

• log_destination : 로그를 기록하기 위한 방식을 지정합니다. 다음 두가지 중에서 하나를 지정할 수 있습니다. (stderr/syslog)
• logging_collector : 백그라운드 프로세스인 Logging Collector 프로세스를 활성화합니다.
• log_directory : 로그 파일이 생성될 디렉토리를 지정합니다.
• log_filename : 생성된 로그 파일의 이름 형식을 지정합니다.
• log_truncate_on_rotation : pgpool이 동일한 이름의 기존 로그 파일에 추가하지않고 덮어씁니다.
• log_rotation_age : 개별 로그 파일을 사용할 수 있는 최대 시간을 설정하고 이후에는 새로운로그 파일이 생성
• log_rotation_size : 개별 로그 파일의 최대 크기를 결정
• socker_dir : pgpool 에 대한 연결을 수락하는 UNIX 도메인 소켓이 생성될 디렉토리를 지정합니다.
• pcp_socket_dir : pcp 프로세스의 연결을 수락하는 UNIX 도메인 소켓이 생성될 디렉토리
• wd_ipc_socket_dir : watchdog IPC 연결을 수락하는 UNIX 도메인 소켓이 생성될 디렉토리
• pid_file_name : pgpool 프로세스 ID를 저장할 파일의 경로를 지정합니다.

각 COMMAND 및 ONLINE RECOVERY

#-----------------
# FAILOVER COMMAND
#-----------------
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
#---------------
# FOLLOW COMMAND
#---------------
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

wd_escalation_command = '/etc/pgpool-II/escalation.sh'

#----------------
# ONLINE RECOVERY
#----------------
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'

• follow_primary_command : primary 서버에서 failover 조치 후, 실행될 커맨드를 지정합니다. standby 서버에서는 이 커맨드가 실행되지 않습니다. pcp_promote_node 커맨드를 통해서도 실행될 수 있습니다.
• failover_command : failover_command 가 설정되어 있고 failover가 발생하면 지정한 커맨드가 실행됩니다.
• recovery_user : online recovery를 수행할 사용자 이름을 지정합니다.
• recovery_password : 온라인 복구를 수행하기 위해 recovery_user 에 구성된 사용자 이름의 password를 지정합니다.
• recovery_1st_stage_command : online recovery 의 첫 번째 단계에서 primary 노드에서 실행할 커맨드를 지정합니다.

여기까지 파라미터 설정을 완료했습니다. 그 다음엔 rpm 패키지에 포함된 다양한 샘플 스크립트를 이용해 스크립트를 환경에 맞게 변경해보도록 하겠습니다. 변경할 스크립트는 다음과 같습니다.

특징	스크립트	설명
failover	failover.sh	failover을 수행하기위해 failover_command로 이 스크립트를 실행합니다.
	follow_primary.sh	failover 후, standby를 새로운 primary 와 동기화 하려면 follow_primary_command 로 이 스크립트를 실행합니다.
online recovery	recovery_1st_stage	standby노드 복구를 위해 recovery_1st_stage_command를 통해 이 스크립트를 실행합니다.
	pgpool_remote_start	recovery_1st_stage 스크립트 실행 후 standby 노드를 시작하는 스크립트입니다.
watchdog	eacalation.sh	active/standby pgpool-II을 전환하기 위해 wd_escalation_command로 실행되는 스크립트 입니다.

cp pgpool_remote_start.sample /test/pg/14/data/pgpool_remote_start
cp recovery_1st_stage.sample /test/pg/14/data/recovery_1st_stage
cp follow_primary.sh.sample follow_primary.sh
cp failover.sh.sample failover.sh
cp escalation.sh.sample escalation.sh

위와 같이 sample로 주어진 script 파일을 복사하여 사용합니다. pgpool_remote_start 와 recovery_1st_stage는 데이터 디렉토리에 복사해줍니다.

 

각자 스크립트를 확인하며 자기 환경에 맞게 수정해주시면 됩니다. 스크립트 변경은 우선 primary 서버에서만 설정 해주도록 합니다! (뒤에 이유 나옴!)

 

스크립트에 공통 변수들이 많아서 스크립트 한두개 변경하다보면 감이 오실거에요! 주로 변경하거나 추가할 변수들은 다음과 같습니다.

1. ARCHIVEDIR=/test/pg/14/archive WALDIR=/test/pg/14/pg_wal
2. POSTGRESQL_STARTUP_USER = test
3. REPLUSER=repluser
4. psql 커맨드 실행 유저
5. ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres -U postgres\ -c "SELECT pg_drop_replication_slot('${REPL_SLOT_NAME}');" >/dev/null 2>&1
6. SSH_KEY_FILE=id_rsa
7. passfile=''/test/.pgpass'’
8. ${PGPOOL_PATH}/pcp_attach_node -w -h /var/run/test -U $PCP_USER -p ${PCP_PORT} -n ${NODE_ID}

등이 있습니다! 스크립트 수정 내용은 대충 읽어보시면서 여기에 어떤 값이 들어가면 되겠다 하는걸 금방 깨달으실 수 있습니다! 사실 저는 … failover테스트 online recovery 테스트를 여러번 하며 올바른 값으로 변경할 수 있었습니다… 서버를 몇대 날려먹었는지 몰라요..😥 근데 그렇게 해야 이해 안되시는 분들도 이해가 된답니다… ( 처음부터 잘 하시는 분들은 .. 뭐 좋은거구요 ㅎㅎ)

 

primary 서버에서 configuration 파일과 스크립트 파일을 수정하셨으면 이제 그 파일들을 standby 서버에 그대로 복사하여 전송해주도록 하겠습니다! pgpool-II 은 primary 나 standby 나 모든 설정 값이 똑같이 사용되기 때문에 한곳에서 설정 후 전송하면 쉽게 설정하실 수 있습니다! (노드가 2개보다 더 많을때는 확실히 편하겠죠?)

scp -p /etc/pgpool-II/pgpool.conf test@192.168.40.134:/etc/pgpool-II/pgpool.conf
scp -p /etc/pgpool-II/failover.sh test@192.168.40.134:/etc/pgpool-II/failover.sh
scp -p /etc/pgpool-II/follow_primary.sh test@192.168.40.134:/etc/pgpool-II/follow_primary.sh
scp -p /test/pg/14/data/recovery_1st_stage test@192.168.40.134:/test/pg/14/data/recovery_1st_stage
scp -p /test/pg/14/data/pgpool_remote_start test@192.168.40.134:/test/pg/14/data/pgpool_remote_start
scp -p /etc/pgpool-II/escalation.sh test@192.168.40.134:/etc/pgpool-II/escalcation.sh

pgpool-II 서비스 기동

이제 pgpool-II을 사용해보도록 하겠습니다! 그전에 postgresql 서버를 시작해줍니다. (시작 안되어 있으신분들만,,)

pg_ctl start 

다음 명령어를 사용하여 각 서버에서 pgpool-II 서비스를 기동해줍니다.

systemctl start pgpool.service

기동을 했으면 프로세스를 확인해보겠습니다.

[test@localhost:pgpool-II]$ ps -ef | grep pgpool
test     120766      1  3 14:40 ?        00:00:00 /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf -D -n
test     120767 120766  0 14:40 ?        00:00:00 pgpool: PgpoolLogger
test     120769 120766  0 14:40 ?        00:00:00 pgpool: watchdog
test     120782 120766  0 14:40 ?        00:00:00 pgpool: lifecheck
test     120783 120782  0 14:40 ?        00:00:00 pgpool: heartbeat receiver
test     120784 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120785 120782  0 14:40 ?        00:00:00 pgpool: heartbeat sender
test     120786 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120787 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120788 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120789 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120790 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120791 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120792 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120793 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120794 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120795 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120796 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120797 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120798 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120799 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120800 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120801 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120802 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120803 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120804 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120805 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120806 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120807 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120808 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120809 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120810 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120811 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120812 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120813 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120814 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120815 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120816 120766  0 14:40 ?        00:00:00 pgpool: wait for connection request
test     120818 120766  0 14:40 ?        00:00:00 pgpool: PCP: wait for connection request
test     120819 120766  0 14:40 ?        00:00:00 pgpool: worker process
test     120820 120766  0 14:40 ?        00:00:00 pgpool: health check process(0)
test     120821 120766  0 14:40 ?        00:00:00 pgpool: health check process(1)

그럼 위와 같은 프로세스들을 확인할 수 있습니다.

구성 확인

pgpool-II 과 PostgreSQL 서버가 연결 된 후, 관리 및 모니터링 할 수 있는 커맨드 종류가 두가지가 있습니다.

• SQL 타입
• PCP 커맨드

위의 커맨드 종류에서 각각 어떤 커맨드들이 있고, 어떤 옵션을 사용할 수 있고, 어떻게 사용하는지 관련 내용은 따로 정리하여 포스팅해보겠습니다.

우선 이번 포스팅에서는 노드가 잘 구성 되었고 연결 되었는지 확인할 수 있는 커맨드를 사용해보겠습니다!

SQL 타입

우선 커맨드를 사용하기 위해 pgpool-II 관리 데이터베이스에 먼저 접속해줍니다.

psql -p 9999

그다음 다음 커맨드를 입력해줍니다.

postgres=# show pool_nodes;
-[ RECORD 1 ]----------+--------------------
node_id                | 0
hostname               | 192.168.40.133
port                   | 5432
status                 | up
pg_status              | up
lb_weight              | 0.500000
role                   | primary
pg_role                | primary
select_cnt             | 0
load_balance_node      | true
replication_delay      | 0
replication_state      |
replication_sync_state |
last_status_change     | 2023-06-21 14:44:18
-[ RECORD 2 ]----------+--------------------
node_id                | 1
hostname               | 192.168.40.134
port                   | 5432
status                 | up
pg_status              | up
lb_weight              | 0.500000
role                   | standby
pg_role                | standby
select_cnt             | 0
load_balance_node      | false
replication_delay      | 0
replication_state      | streaming
replication_sync_state | async
last_status_change     | 2023-06-21 14:44:18

각 백엔드 노드의 정보 및 상태 등의 다양한 정보를 알아볼 수 있습니다. 우리가 설정한대로 0번 노드의 role은 primary 이며 상태는 up , 1번 노드의 role은 standby 이며 상태는 up 인 것을 알 수 있습니다.

PCP 커맨드

[test@localhost:pgpool-II]$ pcp_node_info -h /var/run/test -w
192.168.40.133 5432 2 0.500000 up up primary primary 0 none none 2023-06-21 14:44:18
192.168.40.134 5432 2 0.500000 up up standby standby 0 streaming async 2023-06-21 14:44:18

위 내용을 간략하게 표기하여 노드 상태를 확인할 수 있습니다.

이렇게 관리 커맨드를 사용하여 노드가 잘 구성되었고, 연결되었는지 확인했습니다.

다음 포스팅에서는 auto failover/ online recovery 기능을 테스트 해보며 어떻게 작동하는지 결과를 관찰해보겠습니다!