[ PostgreSQL] Postgres HA 구성 repmgr ( auto-failover )

고객사에서 PostgreSQL를 이중화를 원하고 있었다. 

PostgreSQL 이중화에서 고려해봤던 것들은 Streaming replication + pacemaker를 이용해서 Auto Failover + VIP 이동을 해보려 했으나, 구축 후 장애 포인트가 많아질 것 같아 포기하였다.

다음으로 고려했던 Repmgr를 이용해서 이중화와 VIP 이동을 할 수 있도록 구축하기로 하였다.

 

이번 글은 Repmgr를 이용한 이중화 & Auto Failover & VIP 이동에 관하여 포스팅할 것이다.

 

■ 테스트 환경

Master : 10.70.101.69 ( lee-pg001 ) 

Standby : 10.70.101.70 ( lee-pg002 )

Vip : 10.70.101.68

 

 

■ PostgreSQL 설치 ( ALL )

- Master / Standby 서버에 모두 PostgreSQL를 설치한다.

- https://yum.postgresql.org/rpmchart/  --> PostgreSQL 다운로드 가능

* postgresql12 - PostgreSQL client programs and libraries

* postgresql12-contrib - Contributed source and binaries distributed with PostgreSQL

* postgresql12-libs - The shared libraries required for any PostgreSQL clients

* postgresql12-server - The programs needed to create and run a PostgreSQL server

1. DB RPM 설치
root@lee-pg001  postgres_12_install]# ll
합계 7692
-rw-r--r--. 1 root root 1666016  1월 13 10:24 postgresql12-12.3-5PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root  623648  1월 13 10:24 postgresql12-contrib-12.3-5PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root  377888  1월 13 10:24 postgresql12-libs-12.3-5PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root 5199596  1월 13 10:24 postgresql12-server-12.3-5PGDG.rhel7.x86_64.rpm

[root@lee-pg001 postgres_12_install]# rpm -ivh postgresql12-libs-12.3-5PGDG.rhel7.x86_64.rpm
경고: postgresql12-libs-12.3-5PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:postgresql12-libs-12.3-5PGDG.rhel################################# [100%]
[root@lee-pg001 postgres_12_install]# rpm -ivh postgresql12-12.3-5PGDG.rhel7.x86_64.rpm
경고: postgresql12-12.3-5PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:postgresql12-12.3-5PGDG.rhel7    ################################# [100%]
[root@lee-pg001 postgres_12_install]# rpm -ivh postgresql12-server-12.3-5PGDG.rhel7.x86_64.rpm
경고: postgresql12-server-12.3-5PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:postgresql12-server-12.3-5PGDG.rh################################# [100%]
[root@lee-pg001 postgres_12_install]# rpm -ivh postgresql12-contrib-12.3-5PGDG.rhel7.x86_64.rpm
경고: postgresql12-contrib-12.3-5PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:postgresql12-contrib-12.3-5PGDG.r################################# [100%]
   
   
2. 데이터 디렉토리 생성
[root@lee-pg001 ~]# mkdir -p /mnt/pgsql/12/data
[root@lee-pg001 ~]# chown -R postgres.postgres /mnt/pgsql

3. DB 설치
-bash-4.2$ /usr/pgsql-12/bin/initdb -D /mnt/pgsql/12/data/

4. DB 기동
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start
서버를 시작하기 위해 기다리는 중.... 완료
서버 시작됨

5. 디비를 편하게쓰기위해 Profile을 사용하였다.
postgres@lee-pg002:/mnt/pgsql/12# cat ~/.bash_profile 
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/12/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

alias reload='/usr/pgsql-12/bin/pg_ctl reload -D /mnt/pgsql/12/data/'
alias restart='/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log restart'
alias start='/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start'
alias stop='/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log stop'
export PS1='\u@\h:$PWD# '
export PATH=$PATH:/usr/pgsql-12/bin


###REPMGR
alias show='repmgr cluster show'
alias event='repmgr cluster event'
alias log='tail -200f /mnt/pgsql/12/data/log/repmgr.log'

 

■ SSH Key 등록 ( ALL )

- 이후에  postgres User로 서로 패스워드 필요 없이 이동될 수 있도록 SSH key 등록이 필요하다.

1. ~/.ssh Directory 이동 ( 없을시 mkdir로 생성해주면 됨 )
cd ~/.ssh 
mkdir -p ~/.ssh

2. 키 생성
postgres@lee-pg002:/var/lib/pgsql# ssh-keygen -t rsa

3. authorized_keys 파일 생성
cd ~/.ssh
cat id_rsa.pub >> authorized_keys

4. 모든 노드의 authorized_keys 파일값 합침

postgres@lee-pg001:/var/lib/pgsql/.ssh# cat authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC5ZEZ+6DzhtoCFD0N12QC8pUhpW5o7oKslnrXi3h12ibK/onrg9+mZ57nZc2pVYEfbJEfqF2NQoKRQxBtWFsz9d3XeXLThp9ZaCHd2xJl1kl4qAKocYonku6UT7tRowUsHaYSyFFDJBPBn31Ctiu7dN+7t2PMBqxfUCi8vpnDGd4CBZ23ry/lhsgShdg9EAu54dEEq9xpJ4dfSZV/AIMrbZFg54NlaMKxpQt2PM5syV+kxJjFjF9Z0A6hU3EJLM+XTu7wt4MpdA8LIAryfxthqWEgXYjBBz2szvCwSDB1/pfXO/K4h2qErv7J+nUwwx+U1RS1IIYQf9U+qSqTtq4kh postgres@lee-pg001

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDKalNXyXPV/60PPBQPkuQegX4sLk1j6eqROUBlbk15q4edNWZ4JK1CLisHrDvyA8h+0nRoWuHzT96eMACn+pKALDpknFVFH5/gE9VqrTrvZyMZ8pJfFMa5UgTaBfh/kMaugQjCQ5lTHdtHCWK1P/BTCb3CDv5Wf5eOWz2ES1lBqVxyCq4Hwd4PurLEPVZvsSHSuScPto4u0Wgf1X8WvkfObYLAxmet/1m8o0lEUslSVuUw/+KJOP/ijUFTZFPyTejxMnaLWAqS3pNGURCOaH7SU4EsRJmCHmy0uozeZobtzZtHT+CWdivUZkIursIirRSpkAHapdOvwN5GFbprXIKR postgres@lee-pg002

postgres@lee-pg002:/var/lib/pgsql/.ssh# cat authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC5ZEZ+6DzhtoCFD0N12QC8pUhpW5o7oKslnrXi3h12ibK/onrg9+mZ57nZc2pVYEfbJEfqF2NQoKRQxBtWFsz9d3XeXLThp9ZaCHd2xJl1kl4qAKocYonku6UT7tRowUsHaYSyFFDJBPBn31Ctiu7dN+7t2PMBqxfUCi8vpnDGd4CBZ23ry/lhsgShdg9EAu54dEEq9xpJ4dfSZV/AIMrbZFg54NlaMKxpQt2PM5syV+kxJjFjF9Z0A6hU3EJLM+XTu7wt4MpdA8LIAryfxthqWEgXYjBBz2szvCwSDB1/pfXO/K4h2qErv7J+nUwwx+U1RS1IIYQf9U+qSqTtq4kh postgres@lee-pg001

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDKalNXyXPV/60PPBQPkuQegX4sLk1j6eqROUBlbk15q4edNWZ4JK1CLisHrDvyA8h+0nRoWuHzT96eMACn+pKALDpknFVFH5/gE9VqrTrvZyMZ8pJfFMa5UgTaBfh/kMaugQjCQ5lTHdtHCWK1P/BTCb3CDv5Wf5eOWz2ES1lBqVxyCq4Hwd4PurLEPVZvsSHSuScPto4u0Wgf1X8WvkfObYLAxmet/1m8o0lEUslSVuUw/+KJOP/ijUFTZFPyTejxMnaLWAqS3pNGURCOaH7SU4EsRJmCHmy0uozeZobtzZtHT+CWdivUZkIursIirRSpkAHapdOvwN5GFbprXIKR postgres@lee-pg002

5. SSH TEST
postgres@lee-pg002:/var/lib/pgsql/.ssh# ssh lee-pg001
Last login: Wed Oct 27 09:42:48 2021 from lee-pg002
postgres@lee-pg001:/var/lib/pgsql#


postgres@lee-pg001:/var/lib/pgsql/.ssh# ssh lee-pg002
Last login: Wed Oct 27 09:42:57 2021 from lee-pg001
postgres@lee-pg002:/var/lib/pgsql#

 

■ REPMGR 설치 ( ALL )

1. repo 설치
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. REPMGR 설치
[root@lee-pg002 ~]# yum -y install repmgr_12*
(생략)
Installed:
  repmgr_12.x86_64 0:5.3.0-1.rhel7                              repmgr_12-devel.x86_64 0:5.3.0-1.rhel7                             

Complete!

 

■ REPMGR 설정 ( ALL )

- /etc/repmgr/12/repmgr.conf 설정 파일

- node_id, node_name, conninfor는 모든 서버가 달라야 한다.

- switchover나 failover가 발생하였을 때 vip를 변경해주는 Script가 실행되도록 event_notification_command, event_notifications 파라미터로 제어한다.

[root@lee-pg001 ~]# vi /etc/repmgr/12/repmgr.conf

node_id=101
node_name='lee-pg001'
conninfo='host=lee-pg001 dbname=repmgr user=repmgr'
data_directory='/mnt/pgsql/12/data'
config_directory='/mnt/pgsql/12/data'
log_file='/mnt/pgsql/12/data/log/repmgr.log'
repmgrd_service_start_command = '/usr/pgsql-12/bin/repmgrd -d'
repmgrd_service_stop_command = 'kill `cat $(/usr/pgsql-12/bin/repmgrd --show-pid-file)`'
promote_command='/usr/pgsql-12/bin/repmgr standby promote  --siblings-follow --log-to-file'
follow_command='/usr/pgsql-12/bin/repmgr standby follow  --log-to-file --upstream-node-id=%n'
failover=automatic
reconnect_attempts=3
reconnect_interval=3
ssh_options='-q -o StrictHostKeyChecking=no -o ConnectTimeout=10'
monitoring_history=yes
monitor_interval_secs=10

event_notification_command='bash /repmgr/pg_vip_failover.sh %n'
event_notifications='standby_promote'
service_start_command = '/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start'
service_stop_command = '/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log stop'
service_restart_command = '/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log restart'
service_reload_command = '/usr/pgsql-12/bin/pg_ctl reload -D /mnt/pgsql/12/data/'

 

■  /repmgr/pg_vip_failover.sh 파일 생성 ( ALL  )

- Standby 가 Primary로 Promote 되는 시점에 호출되며 VIP를 컨트롤하는 스크립트이다.

- Primary / Standby  서버별로 스크립트에서 IPDOWN 하는 순서가 다르니 유의해야 된다.

1. /repmgr 디렉토리 권한 생성
postgres@lee-pg001:/mnt/pgsql/12# mkdir /repmgr/
postgres@lee-pg001:/mnt/pgsql/12# chmod 777 /repmgr/

2. 스크립트 작성 
1) Master서버 스크립트
postgres@lee-pg001:/repmgr# cat pg_vip_failover.sh 
# master : 10.70.100.69
# slave : 10.70.100.70
# VIP network ens224

##IPDOWN
ssh postgres@lee-pg002  sudo /sbin/ip addr del 10.70.101.68/24 dev ens160:1
sleep 5;
##IPUP
sudo /sbin/ip addr add 10.70.101.68/24 dev ens160:1

2) Slave서버 스크립트
postgres@lee-pg002:/repmgr# cat /repmgr/pg_vip_failover.sh
# master : 10.70.100.69
# slave : 10.70.100.70
# VIP network ens224
##IPDOWN
ssh postgres@lee-pg001  sudo /sbin/ip addr del 10.70.101.68/24 dev ens160:1
sleep 5;
##IPUP
sudo /sbin/ip addr add 10.70.101.68/24 dev ens160:1

 

■  REPMGR DB & ROLE 생성 ( Primary )

- Standby 서버는 이후에 primary Data를 당겨갈 것이기 때문에 여기선 Primary만 생성한다.

postgres@lee-pg001:/var/lib/pgsql# createuser -s repmgr
postgres@lee-pg001:/var/lib/pgsql# createdb repmgr -O repmgr

 

■  PostgreSQL 설정 변경 ( Primary )

- postgresql.conf 파일에서 이중화 시 필요한 파라미터 설정 추가

- pg_hba.conf 파일에서 repmgr 유저로 replication / repmgr datarbase에 접속되도록 설정 추가

- pg_hba.conf는 reload만으로 적용되지만 postgresql.conf는 재시작을 해야 해서 DB 재시작을 한다.

1. postgresql.conf 변경
postgres@lee-pg001:/mnt/pgsql/12/data# vi /mnt/pgsql/12/data/postgresql.conf
listen_addresses = '*'
wal_level = 'replica'
max_wal_senders = 10
wal_keep_segments = 8
archive_mode = on
archive_command = 'true'
shared_preload_libraries='repmgr'

2. pg_hba.conf 변경
postgres@lee-pg001:/mnt/pgsql/12/data# vi /mnt/pgsql/12/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
(생략)
host    replication     repmgr          10.70.101.0/24          trust
host    repmgr          repmgr          10.70.101.0/24          trust

postgres@lee-pg001:/mnt/pgsql/12/data# /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log restart

 

■  REPMGR Priamry 노드 등록 ( Primary )

1.primary 노드 등록
postgres@lee-pg001: /usr/pgsql-12/bin/repmgr primary register  
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 101) registered

2.repmgr 데몬 시작
postgres@lee-pg001: /mnt/pgsql/12# repmgr daemon start
NOTICE: executing: "/usr/pgsql-12/bin/repmgrd -d"
NOTICE: repmgrd was successfully started

 

■ Standby DB 생성 ( Standby )

- 아래 명령어를 통해 lee-pg001의 Datadir를 복제해온다.

1. Standby DB 생성
postgres@lee-pg002:/mnt/pgsql/12# /usr/pgsql-12/bin/repmgr -h lee-pg001 -U repmgr -d repmgr standby clone
WARNING: following problems with command line parameters detected:
  "config_directory" set in repmgr.conf, but --copy-external-config-files not provided
NOTICE: destination directory "/mnt/pgsql/12/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=lee-pg001 user=repmgr dbname=repmgr
DETAIL: current installation size is 40 MB
INFO: replication slot usage not requested;  no replication slot will be set up for this standby
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
INFO: creating directory "/mnt/pgsql/12/data"...
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  /usr/pgsql-12/bin/pg_basebackup -l "repmgr base backup"  -D /mnt/pgsql/12/data -h lee-pg001 -p 5432 -U repmgr -X stream 
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start
HINT: after starting the server, you need to re-register this standby with "repmgr standby register --force" to update the existing node record

2.DB Startup
postgres@lee-pg002:/mnt/pgsql/12# /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start
서버를 시작하기 위해 기다리는 중.... 완료
서버 시작됨

 

■ REPMGR Standby 노드 등록( Standby )

- repmgr cluster 상태를 보면 현재 lee-pg001이 Primary이며 lee-pg002는 Standby로 lee-pg001을 바라보고 있다.

1. Standby 등록
postgres@lee-pg002:/mnt/pgsql/12# /usr/pgsql-12/bin/repmgr standby register -h lee-pg001 -U repmgr

2. Repmgr 데몬 시작
postgres@lee-pg002:/mnt/pgsql/12# repmgr daemon start
NOTICE: executing: "/usr/pgsql-12/bin/repmgrd -d"
NOTICE: repmgrd was successfully started

3. Repmgr cluster 상태
postgres@lee-pg002:/mnt/pgsql/12# repmgr cluster show
ID  | Name      | Role    | Status    | Upstream  | Location | Priority | Timeline | Connection string                       
-----+-----------+---------+-----------+-----------+----------+----------+----------+------------------------------------------
101 | lee-pg001 | primary | * running |           | default  | 100      | 1        | host=lee-pg001 dbname=repmgr user=repmgr
102 | lee-pg002 | standby |   running | lee-pg001 | default  | 100      | 1        | host=lee-pg002 dbname=repmgr user=repmgr

 

■ 이중화 복제 테스트

- Primary에서 Insert 이후 Standby에서 Select시 복제되는 것을 확인할 수 있다.

- select * from pg_stat_replication 테이블로도 확인 가능 

1. primary Insert
postgres@lee-pg001:/var/lib/pgsql# psql

postgres=# create database test;

postgres=# \c test

test=# create table test(a int);

test=# insert into test values(1);


2. standby Select 
postgres@lee-pg002:/mnt/pgsql/12# psql

postgres=# \c test;

test=# select * from test;
a
---
1
(1개 행)

 

■ Swithover 테스트

- Switchover시 Primary / VIP가 잘 변경되는지 테스트해보았다.

- Switchover시 /repmgr/pg_vip_failover.sh 가 실행되는 것을 볼 수 있다.

- ip addr를 통해 10.70.101.68(vip)가 새로운 Primary role인 lee-pg002로 붙는 것 볼 수 있다.

- repmgr cluster show를 통해 lee-pg002가 Primary role로 바뀐 것을 볼 수 있다.

- VIP를 통해 접속 시 새로운 Primary role DB로 연결되며 Insert DML이 동작하는 것을 확인할 수 있다.

1. Standby Server Switch over 시작
postgres@lee-pg002:/mnt/pgsql/12/data# repmgr standby switchover
NOTICE: executing switchover on node "lee-pg002" (ID: 102)
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "lee-pg002" (ID: 102) will be promoted to primary; current primary "lee-pg001" (ID: 101) will be demoted to standby
NOTICE: stopping current primary node "lee-pg001" (ID: 101)
NOTICE: issuing CHECKPOINT on node "lee-pg001" (ID: 101)
DETAIL: executing server command "/usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/7000028
NOTICE: promoting standby to primary
DETAIL: promoting server "lee-pg002" (ID: 102) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "lee-pg002" (ID: 102) was successfully promoted to primary
INFO: executing notification command for event "standby_promote"
DETAIL: command is:
  bash /repmgr/pg_vip_failover.sh 102
NOTICE: node "lee-pg002" (ID: 102) promoted to primary, node "lee-pg001" (ID: 101) demoted to standby
NOTICE: switchover was successful
DETAIL: node "lee-pg002" is now primary and node "lee-pg001" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully

2. IP Addr 명령어를 통해 VIP가 올라왔는지 확인
postgres@lee-pg002:/mnt/pgsql/12/data# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:50:56:84:e9:b0 brd ff:ff:ff:ff:ff:ff
    inet 10.70.101.70/24 brd 10.70.101.255 scope global noprefixroute ens160
       valid_lft forever preferred_lft forever
    inet 10.70.101.68/24 scope global secondary ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::f328:e265:b8ac:9a66/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:19:82:bd brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:19:82:bd brd ff:ff:ff:ff:ff:ff

3. Primary가 변경되었는지 확인
postgres@lee-pg002:/mnt/pgsql/12/data# repmgr cluster show
ID  | Name      | Role    | Status    | Upstream  | Location | Priority | Timeline | Connection string                       
-----+-----------+---------+-----------+-----------+----------+----------+----------+------------------------------------------
101 | lee-pg001 | standby |   running | lee-pg002 | default  | 100      | 1        | host=lee-pg001 dbname=repmgr user=repmgr
102 | lee-pg002 | primary | * running |           | default  | 100      | 2        | host=lee-pg002 dbname=repmgr user=repmgr-


4. VIP를 통한 DB 접근
postgres@lee-pg002:/mnt/pgsql/12/data# psql -h 10.70.101.68 -U postgres

postgres=# \c test;

test=# insert into test values(2);   
--> Standby에서는 Insert가 안된다. 현재는 Primary로 바뀌었기때문에 Insert 작동

test=# select * from test;
a
---
1
2
(2개 행)

 

■Failover 테스트 ( * Primary : lee-pg002 Standby : lee-pg001 )

- 현재 Primary인 lee-pg002를 Kill했을떄 Failover & Vip 이동이 잘 되는지 확인해 볼 것이다.

- Failover / VIP 이동 모두 되는 것을 볼 수 있다.

1. DB Kill
postgres@lee-pg002:/mnt/pgsql/12# kill -9 12628

2. Primary Role 변경 확인
postgres@lee-pg001:/mnt/pgsql/12# repmgr daemon status
ID | Name      | Role    | Status        | Upstream    | repmgrd | PID   | Paused? | Upstream last seen
----+-----------+---------+---------------+-------------+---------+-------+---------+--------------------
101 | lee-pg001 | standby |   running     | ? lee-pg002 | running | 14431 | no      | 9 second(s) ago    
102 | lee-pg002 | primary | ? unreachable | ?           | n/a     | n/a   | n/a     | n/a                


WARNING: following issues were detected
  - unable to connect to node "lee-pg001" (ID: 101)'s upstream node "lee-pg002" (ID: 102)
  - unable to determine if node "lee-pg001" (ID: 101) is attached to its upstream node "lee-pg002" (ID: 102)
  - unable to  connect to node "lee-pg002" (ID: 102)
  - node "lee-pg002" (ID: 102) is registered as an active primary but is unreachable
  
(생략 )
postgres@lee-pg001:/mnt/pgsql/12# repmgr daemon status
ID | Name      | Role    | Status    | Upstream | repmgrd | PID   | Paused? | Upstream last seen
----+-----------+---------+-----------+----------+---------+-------+---------+--------------------
101 | lee-pg001 | primary | * running |          | running | 14431 | no      | n/a                
102 | lee-pg002 | primary | - failed  | ?        | n/a     | n/a   | n/a     | n/a                


WARNING: following issues were detected
  - unable to  connect to node "lee-pg002" (ID: 102)

3. Lee-pg001로 VIP 이동했는지 확인
postgres@lee-pg001:/mnt/pgsql/12# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:50:56:84:b7:fa brd ff:ff:ff:ff:ff:ff
    inet 10.70.101.69/24 brd 10.70.101.255 scope global noprefixroute ens160
       valid_lft forever preferred_lft forever
    inet 10.70.101.68/24 scope global secondary ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::88d8:4451:8826:c4cf/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:19:82:bd brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:19:82:bd brd ff:ff:ff:ff:ff:ff

 

■ Slave 재구축

- Failover 이후에 Slave를 재구축해 보았다.

- DB를 Primary에서 복제후 repmgr를 보면 lee-pg002가 계속 primary role로 되어있는것을 볼 수있다 

- lee-pg002를 강제로 등록해제이후 다시 Standby role로 등록해주면 된다.

1. 기존 Datafile MV
postgres@lee-pg002:/mnt/pgsql/12# mv data data3

2. DB 복제
postgres@lee-pg002:/var/lib/pgsql# /usr/pgsql-12/bin/repmgr -h lee-pg001 -U repmgr -d repmgr standby clone

3.DB Startup
postgres@lee-pg002:/mnt/pgsql/12# /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start
서버를 시작하기 위해 기다리는 중.... 완료
서버 시작됨

4.Repmgr Cluster show
postgres@lee-pg002:/mnt/pgsql/12# repmgr cluster show
ID  | Name      | Role    | Status               | Upstream | Location | Priority | Timeline | Connection string                       
-----+-----------+---------+----------------------+----------+----------+----------+----------+------------------------------------------
101 | lee-pg001 | primary | * running            |          | default  | 100      | 3        | host=lee-pg001 dbname=repmgr user=repmgr
102 | lee-pg002 | primary | ! running as standby |          | default  | 100      | 3        | host=lee-pg002 dbname=repmgr user=repmgr   

WARNING: following issues were detected
  - node "lee-pg002" (ID: 102) is registered as an inactive primary but running as standby

--> 이렇게 standby도 Primary로 되어있다.

5.lee-pg002 등록해제
postgres@lee-pg002:/mnt/pgsql/12# repmgr primary unregister --node-id=102 --force
INFO: node "lee-pg002" (ID: 102) was successfully unregistered

6.lee-pg002 재등록
postgres@lee-pg002:/mnt/pgsql/12# /usr/pgsql-12/bin/repmgr standby register -h lee-pg001 -U repmgr

postgres@lee-pg002:/mnt/pgsql/12# repmgr daemon start

postgres@lee-pg002:/mnt/pgsql/12# repmgr daemon status
ID | Name      | Role    | Status    | Upstream  | repmgrd | PID   | Paused? | Upstream last seen
----+-----------+---------+-----------+-----------+---------+-------+---------+--------------------
101 | lee-pg001 | primary | * running |           | running | 9549  | no      | n/a                
102 | lee-pg002 | standby |   running | lee-pg001 | running | 12097 | no      | 2 second(s) ago