[PostgreSQL] PostgreSQL + Repmgr 이중화 + pgpool (1)

PostgreSQL + Repmgr + Pgpool 

 

고객사에서 PostgreSQL 이중화 요청이 들어와 구성하게 되었다....  

PostgreSQL에서 기본적으로 제공하는 이중화도 있지만 Repmgr을 이용하여 이중화 및 오토 페일오버 설정을 할 예정이다.

또한 Database에 pgpool을 이용해 유저들이 접속하게하여 Write Transaction은 master에  select transaction은 Slave로 보내게 할 예정이다.   

이번 PostgreSQL + Repmgr 이중화 + pgpool (1) 에선 Repmgr을 이용한 이중화와 Autofailover까지 진행해볼 생각이다.  pgpool을 이용한 Readwrite split은 다음에 설정하기로..

 

목차

0) 사전 환경

1) PostgreSQL 설치

2) Repmgr을 이용한 PostgreSQL 이중화

3) AutoFailover Test

 

 

0) 사전환경

	Hostname	OS Version	IP
Master	Master	Cent_7.6	172.40.40.54
Slave	Slave	Cent_7.6	172.40.40.55

 

1) PostgreSQL 설치 ( Master & Slave )

- RPM을 이용하여 설치하도록 하겠습니다

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

 

1./etc/hosts에 Master / Slave 등록 ( ALL Server ) [root@master ~]# vi /etc/hosts 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 172.40.40.54 master 172.40.40.55 slave 2.RPM Install  [root@master postgres_12_install]# ll 합계 7692 -rw-r--r--. 1 root root 1666016  8월 12 09:22 postgresql12-12.3-5PGDG.rhel7.x86_64.rpm -rw-r--r--. 1 root root  623648  8월 12 09:22 postgresql12-contrib-12.3-5PGDG.rhel7.x86_64.rpm -rw-r--r--. 1 root root  377888  8월 12 09:22 postgresql12-libs-12.3-5PGDG.rhel7.x86_64.rpm -rw-r--r--. 1 root root 5199596  8월 12 09:22 postgresql12-server-12.3-5PGDG.rhel7.x86_64.rpm * 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 [root@master 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@master postgres_12_install]# rpm -ivh postgresql12-12.3-5PGDG.rhel7.x86_64.rpm  [root@master postgres_12_install]# rpm -ivh postgresql12-server-12.3-5PGDG.rhel7.x86_64.rpm  [root@master postgres_12_install]# rpm -ivh postgresql12-contrib-12.3-5PGDG.rhel7.x86_64.rpm  3.Database Install [root@master postgres_12_install]# mkdir -p /mnt/pgsql/12/data      ## DataFile Directory 생성 [root@master postgres_12_install]# chown -R postgres.postgres /mnt/pgsql/   ## postgres User 권한 부여  postgres User는 rpm 풀면 생성됨 [root@master postgres_12_install]# su - postgres -bash-4.2$ /usr/pgsql-12/bin/initdb -D /mnt/pgsql/12/data/ 이 데이터베이스 시스템에서 만들어지는 파일들은 그 소유주가 "postgres" id로 지정될 것입니다. 또한 이 사용자는 서버 프로세스의 소유주가 됩니다. 데이터베이스 클러스터는 "ko_KR.UTF-8" 로케일으로 초기화될 것입니다. 기본 데이터베이스 인코딩은 "UTF8" 인코딩으로 설정되었습니다. initdb: "ko_KR.UTF-8" 로케일에 알맞은 전문검색 설정을 찾을 수 없음 기본 텍스트 검색 구성이 "simple"(으)로 설정됩니다. 자료 페이지 체크섬 기능 사용 하지 않음 이미 있는 /mnt/pgsql/12/data 디렉터리의 액세스 권한을 고치는 중 ...완료 하위 디렉터리 만드는 중 ...완료 사용할 동적 공유 메모리 관리방식을 선택하는 중 ... posix max_connections 초기값을 선택하는 중 ...100 기본 shared_buffers를 선택하는 중... 128MB 기본 지역 시간대를 선택 중 ... Asia/Seoul 환경설정 파일을 만드는 중 ...완료 부트스트랩 스크립트 실행 중 ... 완료 부트스트랩 다음 초기화 작업 중 ... 완료 자료를 디스크에 동기화 하는 중 ... 완료 initdb: 경고: 로컬 접속용 "trust" 인증을 설정 함 이 값을 바꾸려면, pg_hba.conf 파일을 수정하든지, 다음번 initdb 명령을 사용할 때, -A 옵션 또는 --auth-local, --auth-host 옵션을 사용해서 인증 방법을 지정할 수 있습니다. 작업완료. 이제 다음 명령을 이용해서 서버를 가동 할 수 있습니다:     /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l 로그파일 start 4.Database Start -bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start 서버를 시작하기 위해 기다리는 중.... 완료 서버 시작됨 -bash-4.2$ psql psql (12.3) 도움말을 보려면 "help"를 입력하십시오.

* 간단하게 PostgreSQL Databse / User / Version 확인해봤습니다.

 

 

2) Repmgr을 이용한 PostgreSQL 이중화

https://repmgr.org/downloads.html 해당 URL에서 다운로드 받으면됩니다.

- Repmgr은 PostgreSQL 서버 클러스터에서 복제 및 장애 조치를 관리하기 위한 오픈 소스 도구 모음이다. 대기 서버를 설정하고, 복제를 모니터링하고, 장애 조치 또는 수동 전화 작업과 같은 관리 작업을 수행하는 도구로 PostgreSQL의 내장 핫 스탠바이 기능을 향상시킨다.

 

1.SSH ( ALL Server )  - Repmgr이 올바르게 작동하기 위해서는 Postgre 가용자 계정이 프롬프트 없이 다른 각 서버에  SSH로 연결할 수 있어야 한다. postgres@master:/var/lib/pgsql# ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):  Created directory '/var/lib/pgsql/.ssh'. Enter passphrase (empty for no passphrase):  ( Enter ) Enter same passphrase again:  ( Enter ) Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa. Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub. The key fingerprint is: SHA256:LAntqVo2TQVc9Ccyy+M+v7xK0DSYsQC5xHKAuMVuUyA postgres@master The key's randomart image is: +---[RSA 2048]----+ |E+o+..ooo        | |+.B .o.* .       | | B o. = * o .    | |. =  o O = o     | | . .  B S        | |     + + .       | |    = . o        | |   + . o..       | |  .     o+=o     | +----[SHA256]-----+ postgres@master:/var/lib/pgsql# cd .ssh postgres@master:/var/lib/pgsql/.ssh# cat id_rsa.pub >> authorized_keys postgres@master:/var/lib/pgsql/.ssh# chmod 600 /var/lib/pgsql/.ssh/id_rsa postgres@master:/var/lib/pgsql/.ssh# chmod 644 /var/lib/pgsql/.ssh/id_rsa.pub postgres@master:/var/lib/pgsql/.ssh# chmod 644 /var/lib/pgsql/.ssh/authorized_keys postgres@master:/var/lib/pgsql/.ssh# ll 합계 12 -rw-r--r--. 1 postgres postgres  397  8월 23 07:43 authorized_keys -rw-------. 1 postgres postgres 1679  8월 23 07:43 id_rsa -rw-r--r--. 1 postgres postgres  397  8월 23 07:43 id_rsa.pub #master & slave의 authorized_keys을 합쳐준다. postgres@master:/var/lib/pgsql/.ssh# cat authorized_keys  ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC4W78+IZ5uv9wSTEdyXHVeaDKxq8dLBJWz9e12INAB1V+98QX4VJfV1cciSaSRQcDejVb/vuhPj3MFi3qZTKiMlVt+vBtxL6lXpcvHg3sHoIKMbWaTIBb9EocNBWjARq8Gj7VCL3N4nV4bjIz42Wze6hTxtc7Z7pPKt9GxYHBYW/G67Yeb9569OPkaWdeYRMZgh5euUn/sY2V6rtrU3oQNOXnhIGh8pvHejfk5z9JSkfGlie9uwicNxD0eQlep+6uHX36U03UAEyVxhZueRQWqu8rMwgi8lYEKPYms9DtiC1a7Q7AcahZ0dvUmtV1NXFGibpsz4qiZOfzUjFYDug3H postgres@slave ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCd9mYh/ArBDZ24/z7XHtFHVR2+3b4V+JVXXIqFUyXwtpcdQtiHpAs+7izJshnLrCs5ucKsQMOCbVH6w8+OaxUUpc4FR+UkpS2Nu2BCDmW45Umssuf32Xq+wPCf60t2f+lBOUhAz12iIykCs6eQg1PVRLzacT1ZppyePcf3ex0gLht9iuDgcdoD2+NtFYaq56T7HjkoW0T/Z6cONdbdVyAwGSBWXZw42bAzFS39XASZLZ8LK1kbiM1aKDW6bTUs2Hfqqb7etftQNjza3bnFyJ34oT4GA5hhbeP3m0Y9maN3ezry5h5B1rGPkpLcxopS1jVHhTQFi02wc/zJXub+z2av postgres@master postgres@master:/var/lib/pgsql/.ssh# restorecon -R -v ~/.ssh/ #ssh가 되는지 확인해본다. postgres@master:/var/lib/pgsql/.ssh# ssh slave Last login: Mon Aug 23 07:48:21 2021 from master postgres@slave:/var/lib/pgsql# postgres@slave:/var/lib/pgsql/.ssh# ssh master Last login: Mon Aug 23 07:48:41 2021 from slave postgres@master:/var/lib/pgsql#  2.REPMGR INSTALL ( ALL Server ) [root@master ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm [root@master ~]# yum -y install repmgr_12* - Repmgr Configure 설정 추가  [root@master ~]# vi /etc/repmgr/12/repmgr.conf   cluster='failover' node_id=101 node_name='master' conninfo='host=master 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 * slave Server에는 node_id, node_name,conninfo 변경 해야한다. 3.Repmgr User & DB 생성 ( ALL Server ) postgres=# create user repmgr with superuser replication createdb; CREATE ROLE postgres=# alter user repmgr password 'repmgr'; ALTER ROLE postgres=# create database repmgr owner repmgr; CREATE DATABASE 4.PostgreSQL postgresql.conf / pg_hba.conf 수정 / DB 재시작 ( ALL Server )  * PostgreSQL DB에 외부접속이 되도록 환경설정 후 적용시키기 위해 DB 재시작 #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' #pg_hba.conf local   replication   repmgr                              trust host    replication   repmgr      127.0.0.1/32            trust host    replication   repmgr      172.40.40.0/24          trust local   repmgr        repmgr                              trust host    repmgr        repmgr      127.0.0.1/32            trust host    repmgr        repmgr      172.40.40.0/24          trust postgres@master:/mnt/pgsql/12/data# /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log restart 5. Replication 설정 & Repmgr Register - Repmgr 에 Master DB 등록 ( Master Server )  postgres@master:/mnt/pgsql/12/data# /usr/pgsql-12/bin/repmgr master register WARNING: the following problems were found in the configuration file:   parameter "cluster" is deprecated and will be ignored INFO: connecting to primary database... NOTICE: attempting to install extension "repmgr" NOTICE: "repmgr" extension successfully installed NOTICE: primary node record (ID: 101) registered - Repmgr Daemon Start ( Master Server )  postgres@master:/mnt/pgsql/12/data# /usr/pgsql-12/bin/repmgr daemon start WARNING: the following problems were found in the configuration file:   parameter "cluster" is deprecated and will be ignored NOTICE: executing: "/usr/pgsql-12/bin/repmgrd -d" NOTICE: repmgrd was successfully started - Slave DB로 Master DB Data 복제 ( Slave Server )  *Slave DB가 켜져있다면 Stop 시킨후 실행   postgres@slave:/mnt/pgsql/12# /usr/pgsql-12/bin/repmgr -h master -U repmgr -d repmgr standby clone postgres@slave:/mnt/pgsql/12# /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start -Repmgr에 Slave DB 등록 ( Slave Server )  postgres@slave:/mnt/pgsql/12# /usr/pgsql-12/bin/repmgr standby register -h master -U repmgr -Repmgr Daemon Start ( Slave Server )  postgres@slave:/mnt/pgsql/12# /usr/pgsql-12/bin/repmgr daemon start -Replication 설정 확인 ( Slave Server )  postgres@slave:/mnt/pgsql/12# repmgr cluster show WARNING: the following problems were found in the configuration file:   parameter "cluster" is deprecated and will be ignored  ID  | Name   | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                     -----+--------+---------+-----------+----------+----------+----------+----------+---------------------------------------  101 | master | primary | * running |          | default  | 100      | 1        | host=master dbname=repmgr user=repmgr  102 | slave  | standby |   running | master   | default  | 100      | 1        | host=slave dbname=repmgr user=repmgr  6. Test Data 생성  - Create Database ( Master )  postgres=# create database test; CREATE DATABASE - Show Database ( Slave ) postgres=# \l                                  데이터베이스 목록    이름    |  소유주  | 인코딩 |   Collate   |    Ctype    |      액세스 권한       -----------+----------+--------+-------------+-------------+-----------------------  postgres  | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 |   repmgr    | repmgr   | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 |   template0 | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +            |          |        |             |             | postgres=CTc/postgres  template1 | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +            |          |        |             |             | postgres=CTc/postgres  test      | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 |  (5개 행)

 

3) Autofailover Test

-Master DB Shutdown 시켜 Slave가 Primary Role로 변경되는 것을 확인해 봤다.

1.현재 상태 확인 - Master DB가 Primary 인것을 확인 postgres@master:/mnt/pgsql/12/data/log# show WARNING: the following problems were found in the configuration file:   parameter "cluster" is deprecated and will be ignored  ID  | Name   | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                     -----+--------+---------+-----------+----------+----------+----------+----------+---------------------------------------  101 | master | primary | * running |          | default  | 100      | 1        | host=master dbname=repmgr user=repmgr  102 | slave  | standby |   running | master   | default  | 100      | 1        | host=slave dbname=repmgr user=repmgr  2. Master Shutdown postgres@master:/mnt/pgsql/12/data/log# ps -ef | grep postgre root      7682  6045  0 11:05 pts/0    00:00:00 su - postgres postgres  7683  7682  0 11:05 pts/0    00:00:00 -bash postgres  7925     1  0 11:11 ?        00:00:00 /usr/pgsql-12/bin/postgres -D /mnt/pgsql/12/data postgres@master:/mnt/pgsql/12/data/log# kill -9 7925 3.Failover 확인 - Slave DB 가 Primary role로 변경되었다. postgres@slave:/mnt/pgsql/12# repmgr cluster show WARNING: the following problems were found in the configuration file:   parameter "cluster" is deprecated and will be ignored  ID  | Name   | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                     -----+--------+---------+-----------+----------+----------+----------+----------+---------------------------------------  101 | master | primary | - failed  | ?        | default  | 100      |          | host=master dbname=repmgr user=repmgr  102 | slave  | primary | * running |          | default  | 100      | 2        | host=slave dbname=repmgr user=repmgr  4.Slave Server Repmgr Log 확인 - Log를 확인해보면 Master(101) 과 통신이 안되는것을 확인한후에 Slave(102)번을 Primary 로 승급 시키는 것을 볼 수 있다. [2021-08-23 08:34:13] [INFO] "connection_check_type" set to "ping" [2021-08-23 08:34:13] [INFO] monitoring connection to upstream node "master" (ID: 101) [2021-08-23 08:39:14] [INFO] node "slave" (ID: 102) monitoring upstream node "master" (ID: 101) in normal state [2021-08-23 08:39:14] [DETAIL] last monitoring statistics update was 10 seconds ago [2021-08-23 11:42:13] [INFO] node "slave" (ID: 102) monitoring upstream node "master" (ID: 101) in normal state [2021-08-23 11:42:13] [DETAIL] last monitoring statistics update was 10 seconds ago [2021-08-23 11:47:13] [INFO] node "slave" (ID: 102) monitoring upstream node "master" (ID: 101) in normal state [2021-08-23 11:47:13] [DETAIL] last monitoring statistics update was 10 seconds ago [2021-08-23 11:52:13] [INFO] node "slave" (ID: 102) monitoring upstream node "master" (ID: 101) in normal state [2021-08-23 11:52:13] [DETAIL] last monitoring statistics update was 10 seconds ago [2021-08-23 11:54:54] [WARNING] unable to ping "host=master dbname=repmgr user=repmgr" [2021-08-23 11:54:54] [DETAIL] PQping() returned "PQPING_NO_RESPONSE" [2021-08-23 11:54:54] [WARNING] unable to connect to upstream node "master" (ID: 101) [2021-08-23 11:54:54] [INFO] checking state of node "master" (ID: 101), 1 of 3 attempts [2021-08-23 11:54:54] [WARNING] unable to ping "user=repmgr dbname=repmgr host=master connect_timeout=2 fallback_application_name=repmgr" [2021-08-23 11:54:54] [DETAIL] PQping() returned "PQPING_NO_RESPONSE" [2021-08-23 11:54:54] [INFO] sleeping up to 3 seconds until next reconnection attempt [2021-08-23 11:54:57] [INFO] checking state of node "master" (ID: 101), 2 of 3 attempts [2021-08-23 11:54:57] [WARNING] unable to ping "user=repmgr dbname=repmgr host=master connect_timeout=2 fallback_application_name=repmgr" [2021-08-23 11:54:57] [DETAIL] PQping() returned "PQPING_NO_RESPONSE" [2021-08-23 11:54:57] [INFO] sleeping up to 3 seconds until next reconnection attempt [2021-08-23 11:55:00] [INFO] checking state of node "master" (ID: 101), 3 of 3 attempts [2021-08-23 11:55:00] [WARNING] unable to ping "user=repmgr dbname=repmgr host=master connect_timeout=2 fallback_application_name=repmgr" [2021-08-23 11:55:00] [DETAIL] PQping() returned "PQPING_NO_RESPONSE" [2021-08-23 11:55:00] [WARNING] unable to reconnect to node "master" (ID: 101) after 3 attempts [2021-08-23 11:55:00] [INFO] 0 active sibling nodes registered [2021-08-23 11:55:00] [INFO] 2 total nodes registered [2021-08-23 11:55:00] [INFO] primary node  "master" (ID: 101) and this node have the same location ("default") [2021-08-23 11:55:00] [INFO] no other sibling nodes - we win by default [2021-08-23 11:55:00] [NOTICE] this node is the only available candidate and will now promote itself [2021-08-23 11:55:00] [INFO] promote_command is:   "/usr/pgsql-12/bin/repmgr standby promote  --siblings-follow --log-to-file" [2021-08-23 11:55:00] [WARNING] the following problems were found in the configuration file:   parameter "cluster" is deprecated and will be ignored [2021-08-23 11:55:00] [NOTICE] redirecting logging output to "/mnt/pgsql/12/data/log/repmgr.log" [2021-08-23 11:55:00] [WARNING] option "--sibling-nodes" specified, but no sibling nodes exist [2021-08-23 11:55:00] [NOTICE] promoting standby to primary [2021-08-23 11:55:00] [DETAIL] promoting server "slave" (ID: 102) using pg_promote() [2021-08-23 11:55:00] [NOTICE] waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete [2021-08-23 11:55:01] [NOTICE] STANDBY PROMOTE successful [2021-08-23 11:55:01] [DETAIL] server "slave" (ID: 102) was successfully promoted to primary [2021-08-23 11:55:01] [INFO] checking state of node 102, 1 of 3 attempts [2021-08-23 11:55:01] [NOTICE] node 102 has recovered, reconnecting [2021-08-23 11:55:01] [INFO] connection to node 102 succeeded [2021-08-23 11:55:01] [INFO] original connection is still available [2021-08-23 11:55:01] [INFO] 0 followers to notify [2021-08-23 11:55:01] [INFO] switching to primary monitoring mode [2021-08-23 11:55:01] [NOTICE] monitoring cluster primary "slave" (ID: 102)