[PostgreSQL] Pgpool-II + Watchdog setting

[2021.10.29]

 

PostgreSQL 데이터베이스 서버 2대를 구성하던 중, PostgreSQL만으로는 자동 아카이빙 복제는 되지만 자동 failover는 되지 않는다는 것을 알게되었다.

 

Auto Failovr 를 위해 알아보던 중 대표적인 Pgpool-II을 알게되었고, 이 툴이 PostgreSQL의 커넥션 풀 관리도 해준다는 것을 알게되었다.

 

failback도 자동으로 지원해주는 것 같지만, 데이터베이스 서버 커넥션을 얻어오지 못한다는 것은, 현재 그 서버에 어떤 문제가 있다는 것이고 사람의 직접적인 분석이 필요하므로 failback은 자동으로 구성하지 않았다. 대신 recovery -> start -> pgpool attach node 단계별로 제공하는 스크립트를 만들었다.

 

시행착오가 정말 많았다.. 데이터베이스 서버를 만들었지만, failback 플로우 부분에서 조금 부족한 부분이 있는 것 같다.

 

---

개발환경

• Linux Centos7
• Pgpool-II v4.3.1
• PostgreSQL v13

---

2021.10.29 - [IT story/DB] - [PotgreSQL] PostgreSQL v14 이중화

[PotgreSQL] PostgreSQL 이중화 (HA)

---

Pgpool은 PostgreSQL의 connection과 HA를 관리해주는 툴이다.

클라이언트는 Pgpool 주소를 DB라고 인식하며 접속하고, Pgpool은 현재 Mater인 디비로 연결해준다. 이 과정에서 connection pool을 관리해준다.

 

Pgpool의 기본 포트는 9999이다.

 

Pgpool-II 특징

• PostgreSQL의 Connection Pool을 관리해준다.
  
  • Client는 Pgpool-II를 DB로 인식하여 접속하고, Pgpool-II에서 Master로 설정되어 있는 DB연결 해 준다.
  • Connection을 재사용하여 성능을 향상 시킬 수 있다.
• Load Balancing 지원을 해준다.
  • Replication 기능을 통하여 같은 Data를 가지고 있다면, Query를 분산 수행한다.
• Auto FailOver/FailBack기능을 제공한다.
  • Master에서 장애발생 시, Slave가 그 기능을 대신 수행한다. (PostgresSQL Streaming Replication 구성만으로는 부족하여 Pgpool-II를 사용한다.)

• Pgpool도 HA 가능하다.
  • watchdog를 이용한다.

watchdog 이란?
두개 이상의 pgpool 노드가 있을 경우, 서로 서비스가 살아있는지를 지속적으로 체크해서 master가 죽었다면 안죽은 쪽으로 vip를 세팅해주는 역할을 한다.
watchdog을 이용하여 pgpool도 master/slave 구조가 가능하다.

PgBouncer와의 차이점 자세히 알아보기

 

이번에 나는 Pgpool-II을 이용한 PostgreSQL HA 관리와 Watchdog을 통해 Pgpool-II HA를 하려한다.

watchdog은 pgpool에 내장되어 있는 기능이다.

 

watchdog은 예전 글을 보면 master-slave 구조가 가능한 것 같지만, 현재 최신 글을 보면 모두!!! 어디서나 master-slave-slave 구성이다.. 두대로만 할 방법은 없을까.. 두대일 경우 위험부담이 있는 것은 알지만,, 현재 계약 받은 디비 서버가 두대뿐이다.. 정말 2대로하는 방법은 없는지 좀더 알아봐야겠다..

---

0. 설정

Hostname and IP address

Hostname	IP	Virtual IP
master	<master ip>	<vip>
slave	<slave ip>

PostgreSQL Configuration

Item	Value	Detail
port	5432
$PGDATA	/var/lib/pgsql/13/data (default)
Archive mode	on	/var/lib/pgsql/archivedir
Replication Slots	Enable

Pgpool-II Configuration

Item	Value	Detail
port	9999	Clients access port
	9898	PCP procss access port
	9000	watchdog accepts connectios
	9694	UDP port for receiving watchdog's heartbeat
Config file	/etc/pgpool-II/pgpool.conf (default)
Running mode	streaming replication mode
Watchdog	on	Life check method : heartbeat
Start automatically	Enable

scripts

script가 담긴 디렉토리 /data 는 첨부하였다.

data.zip

0.02MB

Feature	Script
Failover	/data/pgpool/script/failover.sh
	/data/pgpool/script/follow_primary.sh
Failback	/data/pgpool/script/failback.sh
	/data/postgresql/script/recovery.sh
	/data/postgresql/script/attach.sh
Online recovery	$PGDATA/recovery_1st_stage
	$PGDATA/pgpool_remote_start
Watchdog	/data/pgpool/script/escalation.sh

DB Users

UserName	Detail
postgres	User running online recovery
pgpool	Pgpool-II health check and replication delay check
repl	PostgreSQL replication user

 

 

1. 설치 및 Config

Pgpool을 설치하기 전 postgresSQL 14를 설치했는데 현재 Pgpool-14는 없고 13까지만 있어서 PostgreSQL을 13으로 내렸다.
13과 14 설치 과정 및 구동 방식은 별다른 차이는 안보인다.

1-1. Pgpool install 

$ yum install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm
$ yum install -y pgpool-II-pg13-*

 

1-2. Pgpool + Watchdog 설정

혹시 모를 상황을 대비해서 conf file backup

# 설정해둔 것이 없다면.
$ cp /etc/pgpool-II/pgpool.conf.sample /etc/pgpool-II/pgpool.conf

# 기존 설정해둔 것이 있다면.
$ cp /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool.conf.backup
$ cp /etc/pgpool-II/pgpool.conf.sample /etc/pgpool-II/pgpool.conf

pgpool.conf에 HA DB 정보 설정 세팅 (master에만 작업. slave에는 복사)

 

pgpool의 기본설정 + postgresql 체크 설정

$ vi /etc/pgpool-II/pgpool.conf

--->
listen_addresses = '*'
port = 9999 # default 9999
socket_dir = '/var/run/postgresql'

# 데이터베이스 접속 정보
# 0. master
backend_hostname0 = {master ip}
backend_port0 = {master db port}
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/13/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
# 1. slave
backend_hostname1 = {slave ip}
backend_port1 = {slave db port}
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/13/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# 데이터베이스 상태 체크 설정
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3

master_slave_mode = on
master_slave_sub_mode = 'stream'

failover 설정

failover_command = '/data/pgpool/script/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/data/pgpool/script/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

Online recovery 설정

recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'

pcp_recovery_node 명령을 사용하면 pgpool.conf에 설정된 recovery_1st_stage_command 가 실행된다.

 

Watchdog 기본 설정

enable_pool_hba = on
use_watchdog = on
delegate_IP = {vip}
if_cmd_path = '/sbin'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev {네트워크} label {네트워크}:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev {네트워크}'
arping_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I {네트워크}'

클라이언트는 pgpool.conf에 설정된 delegete_IP로 접근하게 된다.

Watchdog 구성 Pgpool 체크 설정

# watchdog이 체크할 pgpool 대상 설정
hostname0 = {master ip}
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = {slave ip}
wd_port1 = 9000
pgpool_port1 = 9999

# watchdog의 pgpool 체크 방식 설정
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
wd_escalation_command = '/data/pgpool/script/escalation.sh'

# watchdog과 heartbeat 신호를 주고받을 대상 설정
heartbeat_hostname0 = {master ip}
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = {slave ip}
heartbeat_port1 = 9694
heartbeat_device1 = ''

 

로그 설정

log_destination = 'stderr'
logging_collector = on
log_directory = '/data/pgpool/log'
log_filename = 'pgpool-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
logdir = '/data/pgpool/log'

 

 

1-3. pgpool_node_id 생성

master는 0, slave 1로 설정한다.

vi /etc/pgpool-II/pgpool_node_id

 

1-4. script 작성

script는 0.설정에 첨부한 .zip파일을 /data경로에 푼다.

/data/pgpool/script/escalation.sh	PGPOOLS=({server1}, {server2}) VIP = {VIP} DEVICE= {network}
/data/pgpool/script/failback.sh	PGPOOL_HOST={VIP}
/data/pgpool/script/failover.sh	PGPOOL_HOST={VIP}

 

아래 명령어를 실행하여 PGDATA 경로에 'pgpool_remote_start', 'recovery_1st_stage'스크립트를 옮긴다.

$ cp /data/postgresql/script/pgpool_remote_start /var/lib/pgsql/13/data/
$ cp /data/postgresql/script/recovery_1st_stage /var/lib/pgsql/13/data/

 

1-5. config, script slave에 복사

scp -p /etc/pgpool-II/pgpool.conf root@{slave ip}:/etc/pgpool-II/pgpool.conf

scp -p -r /data root@{slave ip}:/

scp -p /var/lib/pgsql/13/data/recovery_1st_stage postgres@{slave ip}:/var/lib/pgsql/13/data/recovery_1st_stage
scp -p /var/lib/pgsql/13/data/pgpool_remote_start postgres@{slave ip}:/var/lib/pgsql/13/data/pgpool_remote_start

scp한 파일들의 소유권한을 확인한다.

 

1-6. DB pgpool_recovery 함수 활성화

sudo -u postgres psql template1 -c "CREATE EXTENSION pgpool_recovery"

master 디비에서 설정하면 slave에 자동으로 설정된다. (slave는 postgresql로 복제본으로 세팅되어있기 때문.)

 

2. 접속 및 인증 설정

2-1. DB user 생성

Pgpool에서 데이터베이스 상태를 체크할 User를 생성한다.

sudo -u postgres psql

postgres=# CREATE ROLE pgpool WITH LOGIN;
postgres=# \password pgpool

postgres=# GRANT pg_monitor TO pgpool;

# 확인
postgres=# \du

pgpool 버전이 4.1이상 인 경우에는 pgpool 유저에 pg_monitor 권한을 주어야한다.

master 디비에서 설정하면 slave에 자동으로 설정된다. (slave는 postgresql로 복제본으로 세팅되어있기 때문.)

 

2-2. 접속 인증 설정

모든 Pgpool-II 서버와 PostgreSQL 서버가 동일한 서브넷에 있다고 가정하고 pg_hba.conf 를 편집 하여 scram-sha-256 인증 방법 을 활성화 한다.

echo "host    all             all             samenet                 scram-sha-256" >> /var/lib/pgsql/13/data/pg_hba.conf
echo "host    all             pgpool          0.0.0.0/0               scram-sha-256" >> /var/lib/pgsql/13/data/pg_hba.conf
echo "host    replication     all             samenet                 scram-sha-256" >> /var/lib/pgsql/13/data/pg_hba.conf

Client 및 PostgreSQL이 Pgpool-II에 접속하기 위한 인증 방법을 pool_hba.conf에 설정합니다.

cp /etc/pgpool-II/pool_hba.conf.sample /etc/pgpool-II/pool_hba.conf

echo "host all all {master ip}/32 md5" >> /etc/pgpool-II/pool_hba.conf
echo "host all all {slave ip}/32 md5" >> /etc/pgpool-II/pool_hba.conf
echo "host all all 0.0.0.0/0 md5" >> /etc/pgpool-II/pool_hba.conf

 

2-3. ssh key 생성

failover와 failback를 수행할 때 root,postgres와 원격지 root,postgres 사용자 간의 암호 없이 ssh 접속을 하기 위해 key를 생성한다.
생성된 키 파일 이름은 id_rsa_pgpool이다.

 

root 계정에서 아래의 key를 생성하고 등록한다.

cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@{master ip}
ssh-copy-id -i id_rsa_pgpool.pub postgres@{slave ip}

postgres 계정에서 아래의 key를 생성하고 등록한다.

cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@{master ip}
ssh-copy-id -i id_rsa_pgpool.pub postgres@{slave ip}

ssh-copy-id -i id_rsa_pgpool.pub root@{master ip}
ssh-copy-id -i id_rsa_pgpool.pub root@{slave ip}

기본 pgpool-II에서 제공하는 recovery, remote 샘플 스크립트를 사용하면 pg_ctl 명령어를 사용해 postgresql이 start/stop 된다.

그러나, 나는 다른 시스템들과의 통일성을 위해 별다른 문제가 없다면 systemctl로 start/stop 하기로 했다.

이과정에서 pgpool이 postgres 계정으로 원격지의 systemctl명령을 수행해야 하기 때문에 postgres 계정에서는 원격지 root의 key를 등록해주었다.

 

그리고 여기서 하나더!!

postgres에 키를 등록해주어도 ssh접속을 시도해보면 계속 비밀번호를 입력하라고 뜬다.

이유는 postgres는 postgresql 시스템으로 인해서 자동으로 등록된 user이기 때문에 ssh 키를 관리하는 home 디렉토리가 일반 user계정과 다르게 설정되어 있다고 한다.

그래서 추가로 아래의 명령어를 실행해야한다.

su - postgres
restorecon -R -v ~/.ssh

 

2-4.  데이터베이스 접속을 위한 설정

Streaming replication과 Online recovery 수행을 위해 데이터베이스에 repl,postgres계정으로 암호 인증 없이 접속하기 위해서 .pgpass를 생성한다.

su - postgres
vi /var/lib/pgsql/.pgpass

chmod 600  /var/lib/pgsql/.pgpass

/var/lib/pgsql/.pgpass파일을 만들고 아래 항목들을 입력한다.

• {master ip}:5432:postgres:postgres:{비밀 번호}
• {master ip}:5432:replication:repl:{비밀 번호}
• {slave ip}:5432:postgres:postgres:{비밀 번호}
• {slave ip}:5432:replication:repl:{비밀 번호}
• {master ip}:9999:postgres:pgpool:{비밀 번호}
• {slave ip}:9999:postgres:pgpool:{비밀 번호}
• {watchdog vip}:9999:postgres:pgpool:{비밀 번호}

 

 

pgpool에서 데이터베이스 접속 인증을 위한 기본 암호파일 이름은 pool_passwd이다. 데이터베이스 scram-sha-256 인증을 위한 암호 해독키를 생성한 후 db user와 함께 pool_passwd에 등록한다.

su - postgres

echo '{비밀번호}' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey

pg_enc -m -k ~/.pgpoolkey -u pgpool -p
pg_enc -m -k ~/.pgpoolkey -u postgres -p

# 확인
cat /etc/pgpool-II/pool_passwd

만약 /etc/pgpool-II/pool_passwd 읽기 fail 에러가 발생했다면 아래 두가지 사항을 확인하고 아래 명령어를 실행한다.

1. /etc/pgpool-II/pool_passwd가 존재하는지 확인
2. /etc/pgpool-II/*의 소유 및 권한이 postgres인지 확인

touch /etc/pgpool-II/pool_passwd

chown -R postgres.postgres /etc/pgpool-II/*

 

2-5.  pcp 명령 실행을 위한 설정

failback 기능을 수행하는 script에서 pcp명령을 사용하기 위한 md5 암호화 인증을 설정이 필요하다.

 

pcp.conf에 암호화 항목을 생성한다.

echo 'pgpool:'`pg_md5 {비밀번호}` >> /etc/pgpool-II/pcp.conf
echo 'postgres:'`pg_md5 {비밀번호}` >> /etc/pgpool-II/pcp.conf

 

pcp는 데이터베이스에 실행되는 function 명령으로, 데이터베이스 사용자에 대한 인증을 설정합니다.

su - postgres
vi /var/lib/pgsql/.pcppass

chmod 600 /var/lib/pgsql/.pcppass

 

/var/lib/pgsql/.pcppass에 아래 항목을 입력한다.

• localhost:9898:pgpool:{비밀번호}
• {watchdog vip}:9898:pgpool:{비밀번호}
• localhost:9898:postgres:{비밀번호}
• {watchdog vip}:9898:postgres:{비밀번호}

 

 

3. 방화벽 설정 및 pgpool 시작

$ firewall-cmd --zone=public --add-port=9999/tcp --permanent
$ firewall-cmd --reload

$ systemctl enable pgpool.service
$ systemctl restart pgpool.service
$ systemctl status pgpool.service

pgpool에 제대로 세팅이 되었는지 확인

$ psql -h localhost -p 9999 -U postgres

show pool_nodes;

show 결과 리스트에 세팅된 master/slave db 정보가 보인다.

사용자가 pgpool로 접속하게 되면, 현재 master인 DB로 접속되고, master/slave 각 디비에 직접 접속 할 수 있다.

---

설정은 모두 끝났다. 이제 failover/failback 시나리오가 어떻게 되는지 설명하겠다.

...

 

4. FailOver 

• 장애 발생시 자동으로 실행시킬 command를 설정할 수 있다. (쉘 스크립트 가능)
  1. failover가 되었을 때 slave에 접속하여 master로 승격시키는 메시지를 보내는 스크립트를 작성한다.
  2. Pgpool이 자동으로 slave서버 접속을 위해 sshkey를 생성해주어야한다.

 

5. FailBack

---

https://skysoo1111.tistory.com/66

# PostgreSQL HA 구성 - PGPoolⅡ

https://www.pgpool.net/docs/42/en/html/runtime-config-failover.html

Failover and Failback