Postgres pgpool-II을 이용한 failover

설치 환경

운영체제 : centos 7 3-10.0-1127.13.1.el7.x86_64

DB 버전 : 11.9

 

구성도

 

설치 방법

특이사항

• replication 설정 후 시작, replication 설정은 https://peryh.tistory.com/8?category=871555 참조
• pgpool을 두가지 방식으로 사용하는 방법을 기술(pgpool이 load balance만 하는 경우, pgpool이 replication을 담당하는 경우)
• postgres 기본 경로 변경(/var/lib/pgsql/11/data -> /pgsql/11/data)하여 수행, 변경하지 않는 경우 scp로 파일을 생성할 때, root의 권한이 있는 디렉터리에 생성되지 않음, 설정은 peryh.tistory.com/7?category=871555 참조

 

master 쪽 설정

Pgpool-II 설치

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

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-4.0.1-1pgdg.rhel7.x86_64.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-debuginfo-4.0.1-1pgdg.rhel7.x86_64.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-devel-4.0.1-1pgdg.rhel7.x86_64.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-extensions-4.0.1-1pgdg.rhel7.x86_64.rpm

 

확인

#rpm -qa | grep pgpool

pgpool-II-release-4.0-1.noarch

pgpool-II-pg11-debuginfo-4.0.1-1pgdg.rhel7.x86_64

pgpool-II-pg11-extensions-4.0.1-1pgdg.rhel7.x86_64

pgpool-II-pg11-4.0.1-1pgdg.rhel7.x86_64

pgpool-II-pg11-devel-4.0.1-1pgdg.rhel7.x86_64

 

 

pgpool디렉터리 생성(로그 보기 위한 디렉터리)

mkdir -p /var/log/pgpool

 

 

Step 2. pgpool 설정을 한다.

pgpool.conf 복사 및 수정

어떻게 작동시킬 것인가에 따라 필요한 conf 파일을 복사해서 사용한다

스트리밍 복제모드(Streaming replication mode) : stream PostgreSQL이 데이터베이스를 동기화하고 Pgpool은 Load Balancing을 담당

논리 복제모드(logical replication mode) : logical PostgreSQL이 테이블 동기화를 담당하며 Pgpool은 Load Balancing을 담당

마스터 슬레이브 모드(master slave mode(slony mode)) : master-slavePgpool이 데이터베이스 동기화와 Load Balancing을 담당

네이티브 복제모드(native replication mode) : replication동기식으로 동기화를 수행

원시 모드(raw mode) : sample

설명 문서 : https://www.pgpool.net/docs/latest/en/html/configuring-pgpool.html

#cd /etc/pgpool-II

#rm -rf pgpool.conf

#cp pgpool.conf.sample-stream pgpool.conf

#chmod 764 pgpool.conf

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

---------------------------------------------------------------------

# pgpool 외부 접근정보

# 외부 접속 정보

#  '*' for all, '' for no TCP/IP connections

listen_addresses = '*'

port = 9999

 

#------------------------------------------------------------------------------

# - Backend Connection Settings -

#------------------------------------------------------------------------------

# backend_hostname : Pgpool이 서버에 접속할 때 사용할 hostname 또는 IP

# backend_port : 포트번호

# backend_weight : 백엔드의 Load balancing 비율

# backend_data_directory : 백엔드의 데이터베이스 클러스터 디렉터리

# backend_flag : 백엔드 동작을 제어

# 백엔드를 여러 개 사용할 경우 매개변수 이름 끝에 숫자(DB node ID)를 추가한다.

# 0은 서버를 하나만 사용한다. 숫자가 가장 낮은 DB noid ID가 마스터 노드가 된다.

backend_hostname0 = '10.178.0.1'                      => 1번 OS ip

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '/pgsql/11/data'

backend_flag0 = 'ALLOW_TO_FAILOVER'

 

backend_hostname1 = '10.178.0.2'                      => 2번 OS ip

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/pgsql/11/data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

 

#------------------------------------------------------------------------------

# FILE LOCATIONS

#------------------------------------------------------------------------------

#클라이언트 인증을 위해 pool_hba.conf를 참조, 기본값은 false

#서버 수가 둘 이상인 경우 postgresql에서 정의한 인증방법이 trust이면 off로 설정 가능

enable_pool_hba = off

 

# 로그 파일 저장 위치 지정

logdir = '/var/log/pgpool'

 

#------------------------------------------------------------------------------

# REPLICATION MODE

#------------------------------------------------------------------------------

# replication 모드 활성화/비활성화

replication_mode = off

replicate_select = off

insert_lock = off

 

#------------------------------------------------------------------------------

# - Degenerate handling -

#------------------------------------------------------------------------------

# 데이터 정합성 검사

# insert나 update, delete시에 반영되는 로우가 다를시 쿼리실행이 중지됨

replication_stop_on_mismatch = off    (1/5 현재 on상태)

 

#------------------------------------------------------------------------------

# LOAD BALANCING MODE

#------------------------------------------------------------------------------

#pgpool의 load_balance 기능, 위에서 설정한 backend host에 맘대로 왔다갔다하여 붙음

#확인 방법은 $psql -h <pgpool 설치된곳 ip> -p 9999 -U <유저=롤> 이후 select inet_server_addr();

load_balance_mode = on

 

#------------------------------------------------------------------------------

# MASTER/SLAVE MODE

#------------------------------------------------------------------------------

# Streaming Replication

# 마스터 / 슬레이브 모드 확성화/비활성화

# replication_mode 와 함께 사용할 수 없다. 둘 중 하나만 활성화해서 사용.

 

//master_slave_mode =  off      설정을 off로 하면 pgpool에서 자체적으로 replication을 함

master_slave_mode = on

 

master_slave_sub_mode = ‘stream’

 

#------------------------------------------------------------------------------

# - Streaming -

#------------------------------------------------------------------------------

# Streaming Replication 방식을 사용할 경우 설정

# sr_check_user 설정을 정확히 안하면 primary node 를 찾지 못함(DB 유저 postgres의 계정, 비밀번호, DB정보)

sr_check_period = 10

sr_check_user = 'postgres'

sr_check_password = 'psql'

sr_check_database = 'postgres'

delay_threshold = 0

 

#------------------------------------------------------------------------------

# FAILOVER AND FAILBACK

#------------------------------------------------------------------------------

# 기본 노드를 찾는 시간

search_primary_node_timeout = 10

---------------------------------------------------------------------

 

 

pgpool에서 master, slave 서버에 접근할 수 있도록 설정한다.

 

vi /etc/pgpool-II/pool_hba.conf

---------------------------------------------------------------------

host all all 10.178.0.0/24 trust

---------------------------------------------------------------------

 

pgpool을 기동한다.

# pgpool -f /etc/pgpool-II/pgpool.conf -n -D -d > /var/log/pgpool/pgpool.log 2>&1 &

 

 

 

여기서 에러가 나면

https://qastack.kr/ubuntu/50621/cannot-connect-to-postgresql-on-port-5432

오류 메시지는 유닉스 도메인 소켓을 참조하므로 netstat제외하지 않으려면 호출을 조정해야 합니다.

옵션없이 시도하십시오 

 

netstat -nlp | grep 5432

클라이언트가 연결하려고하는 /tmp/.s.PGSQL.5432것이 아니라 서버가 실제로 소켓에서 수신 대기 하고 /var/run/postgresql/.s.PGSQL.5432있다고 생각합니다. 이것은 유닉스 도메인 소켓 디렉토리의 소스 기본값 /tmp이 데비안 패키징에서로 변경 되기 때문에 데비안 또는 우분투에서 수동 컴파일 또는 타사 PostgreSQL 패키지를 사용할 때 발생하는 일반적인 문제 /var/run/postgresql입니다.

 

가능한 해결 방법 :

타사 패키지에서 제공 한 클라이언트를 사용하십시오 (call /opt/djangostack-1.3-0/postgresql/bin/psql). 우분투 제공 패키지를 모두 제거 할 수 있습니다 (다른 역 종속성으로 인해 어려울 수 있음).

타사 패키지의 소켓 디렉토리가 Debian / Ubuntu와 호환되도록 수정하십시오.

-H localhost대신 TCP / IP를 통해 연결하는 데 사용하십시오 .

올바른 디렉토리를 가리 키려면 -h /tmp이와 동등한 PGHOST설정을 사용하십시오 .

타사 패키지를 사용하지 마십시오.

 

vi /var/lib/pgsql/11/data/postgresql.conf의 unix_socket_directories = '/var/run/postgresql, /tmp' 주석 해제 후

pid관련 파일 삭제 후 재부팅하여 해결하였음

 

pgpool 관련 에러 발생 시, pg_hba.conf 파일 수정

[postgres@policyserver1 ~]$ psql -h localhost -p 9999 -U postgres

psql: ERROR:  unable to read message kind

DETAIL:  kind does not match between master(45) slot[1] (52)

 

 

---------------

pgpool 정지

#pgpool stop

 

-pgpool 기동하고 접속

$psql -h 10.178.0.1 -p 9999 -U postgres

 

# master, slave 노드정보 확인

postgres=# \x

postgres=# show pool_nodes;

 

 

 

# 현재 접속된 노드 확인

# 로드밸런싱 설정을 해놔서 master, slave 왔다갔다 하면서 붙음(안바뀜)

select inet_server_addr();

 

 

 

Step 3. failover 설정

failover시, master 서버가 slave 서버한테 failover trigger 파일을 전송한다. 이 때 비밀번호 없이 전송하기 위해 ssh key 복사를 해준다.

 

master 노드 (postgres 계정으로 진행)

ssh-keygen -C postgres@10.178.0.1

엔터- 엔터- 엔터

ssh-copy-id postgres@10.178.0.2     <=이 IP는 상대 IP 넣기

 

slave 노드 (postgres 계정으로 진행)

ssh-keygen -C 10.178.0.2로 생성

엔터 3번

클라우드 환경이라면 cd .ssh

내부의 id_rsa.pub 파일 내용을 메타데이터의 ssh키에 저장

ssh-copy-id postgres@10.178.0.1

 

정상 동작 확인(master에서)

ssh -T postgres@10.178.0.2 touch /home/postgres/testfile.txt

해당 위치에 파일 생성된 것 확인

 

failover_command 설정, pgpool은 master의 장애를 감지하면 failover_command를 수행한다.(master에서 수행)

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

-------------------------------------------------------------------------------

failover_command = '/usr/pgsql-11/share/failover.sh %d %H /pgsql/11/data/failover_trigger'

 

health check 설정 : 자동으로 failover기능이 활성화 되도록 설정

health_check_period = 0 --> 15

health_check_timeout= 20 --> 10

health_check_max_retries = 0 --> 3

health_check_retry_delay = 1

health_check_user = 'postgres'

-------------------------------------------------------------------------------

 

 

# failover 스크립트 작성

master 서버에 장애가 생기면 slave 서버에게 trigger 파일을 전송

slave에 trigger 파일이 있으면 master로 전환, trigger 파일 경로는 slave의 /var/lib/pgsql/11/data/recovery.conf 에도 설정(master에서 설정)

 

$ cd /usr/pgsql-11/share

$ vi failover.sh

-------------------------------------------------------------------------------

if [ $# -ne 3 ]

then

        echo "failover failed_node new_master trigger_file"

        exit 1

fi

 

FAILED_NODE=$1

NEW_MASTER=$2

TRIGGER_FILE=$3

 

# Do nothing if standby server goes down

if [$FAILED_NODE = 1]; then

        echo "Standby Server is downed\n" >> /var/log/pgpool/failover.log

        exit 0

fi

 

echo "failover.sh FAILED_NODE:${FAILED_NODE}; NEW_MASTER:${NEW_MASTER}; at $(date)\n" >> /var/log/pgpool/failover.log

 

sudo -u postgres  ssh -T postgres@$NEW_MASTER touch $TRIGGER_FILE

 

exit 0

-------------------------------------------------------------------------------

 

# failover.sh 권한 변경

$ chmod 775 /usr/pgsql-1/share/failover.sh

 

pgpool을 재시작

pgpool stop

pgpool -f /etc/pgpool-II/pgpool.conf -n -D -d > /var/log/pgpool/pgpool.log 2>&1 &

 

master 쪽 stop

$pg_ctl -D /pgsql/11/data/ stop

 

slave쪽에서 접속하여 확인

$psql -h 10.178.0.2 -p 9999 -U postgres

postgres=# show pool_nodes;

 

<참고>일반적인 수동 active 전환 방법

$pg_ctl promote -D /var/lib/pgsql/11/data 

 

failover 후 추가작업(new master가 살아있어야 함, pgpool있는 곳에서 작업)

failover 후 old master를 올려도 자동으로 slave 로 추가되는 기능은 제공되지 않기 때문에 아래와 같은 과정을 수동으로 진행

old master 서버가 failover 되어 new master 가 된 상황 1번 서버를 올려도 자동으로 slave로 추가되지 않으며

pgpool은 이 서버를 인식하지 못함

 

10.178.0.2(new master)쪽 설정(최초에 설정했으면 수정할 필요 없음)

vi /pgsql/11/data/pg_hba.conf

host     replication      all    0.0.0.0/0   md5

추가

 

이후 재기동

$pg_ctl -D /pgsql/11/data start

 

10.178.0.1(old master)에서 진행

$ cd /pgsql/11

$ mv data data_old

 

$pg_ctl -D /pgsql/11/data stop

 

Standby Server의 Data 폴더를 보면 이전에 recovery.conf로 만들어 놓은 파일이 recovery.done으로 변경되어 있는 것을 확인할 수 있음, 새로 master가 된 2번기의 데이터 파일 가져오기

$ pg_basebackup -h 10.178.0.2 -D /pgsql/11/data -U repl -v -P -X stream

 

recovery.done을 .conf로 바꾸고 host ip도 변경

$cd /pgsql/11/data

$mv recovery.done recovery.conf

vi recovery.conf

---------------------------------------------------------------------

standby_mode='on'

 

# master 노드의 정보를 적는다.

 

primary_conninfo='host=10.178.0.2 port=5432 user=repl password=repl'

 

# slave노드에 trigger file이 있으면, slave가 master로 전환된다.

 

trigger_file='/var/lib/pgsql/11/data/failover_trigger'

---------------------------------------------------------------------

old master up

$ cd /usr/pgsql-11/bin/

$./pg_ctl -D /var/lib/pgsql/11/data/ start

 

pgpool node로 추가(pgpool에서 up으로 인식되어지는 작업)

# echo "postgres:$(pg_md5 --username=postgres postgres)" >> /etc/pgpool-II/pcp.conf

 

이거 때문에 password 지정해야 함

$ pcp_attach_node -n 0

Password : postgres (echo에서 설정한 비밀번호 입력해야 함)

 

 

new master가 죽었을 경우 slave를 pgpool에 붙여주기 위해서는

pgpool 재기동이 필요