Repmgr

PostgreSQL에만 설치 가능, EPAS에는 설치할 수 없다..

 

 

 

 

Primary는 설치 완료, Standby는 initdb 직전까지 구축해놓는다,,,

1. (P,S) repmgr 설치 수행 (PG버젼에 맞는 repmgr 다운받는다. 여기서는 16을 다운받겠다)

온라인 환경
- curl https://dl.enterprisedb.com/default/release/get/16/rpm | sudo bash

오프라인 환경
Repo 잡은 후.,. (잡는 법은 1.설치 참고)
yum install json*
yum install -y repmgr16 

 

2. (P) postgresql.conf 설정

listen_addresses = '192.168.56.251,192.168.56.252'wal_level = replicawal_log_hints = onarchive_mode = onarchive_command = 'cp %p /var/lib/pgsql/16/archive/%f'max_wal_senders = 10max_replication_slots = 10hot_standby = onshared_preload_libraries = 'repmgr'

 

3. (P) pg_hba.conf 설정

local   replication     repmgr                                  trusthost    replication     repmgr          127.0.0.1/24            trusthost    replication     repmgr          192.168.56.251/24       trusthost    replication     repmgr          192.168.56.252/24       trustlocal   repmgr          repmgr                                  trusthost    repmgr          repmgr          127.0.0.1/24            trusthost    repmgr          repmgr          192.168.56.251/24       trusthost    repmgr          repmgr          192.168.56.252/24       trust

 

4. (P,S) postgres 유저에서 sudo 커맨드로 DB 시작/중지할 수 있도록 설정

vi /etc/sudoerspostgres       ALL=(ALL)       NOPASSWD: ALL

5. (P) repmgr 유저 및 데이터베이스 생성, Streaming replication 생성

postgres=# create user repmgr with superuser replication createdb;
postgres=# alter user repmgr password 'repmgr';
postgres=# create database repmgr owner repmgr;

6. repmgr 설정 (/etc/repmgr/16/repmgr.conf)

1) Primary 

node_id=1
node_name='repmgr_1'
conninfo='host=192.168.56.251 user=repmgr password=repmgr dbname=repmgr connect_timeout=2 port=5432'
#conninfo='host=vm8  user=repmgr dbname=repmgr password=repmgr connect_timeout=2 port=5436'
 
data_directory='/var/lib/pgsql/16/data'
use_replication_slots = yes
#use_primary_conninfo_password = true
 
repmgrd_pid_file=' /var/lib/pgsql/16/repmgr/repmgrd.pid'
log_file = ' /var/lib/pgsql/16/repmgr/repmgrd.log'
pg_bindir = '/usr/pgsql-16/bin/'
 
failover = automatic
monitoring_history=yes
monitor_interval_secs=10
#ssh_options='-q -o StrictHostKeyChecking=no -o ConnectTimeout=10'
reconnect_attempts=3
reconnect_interval=3
node_rejoin_timeout=10
 
use_replication_slots=true
 
#repmgrd_service_start_command='sudo systemctl start repmgr-16.service'
#repmgrd_service_stop_command='sudo systemctl stop repmgr-16.service'
repmgrd_service_start_command='/usr/pgsql-16/bin/repmgrd -d -f /etc/repmgr/16/repmgr.conf'
repmgrd_service_stop_command='kill `cat $(/usr/pgsql-16/bin/repmgrd --show-pid-file)`'
 
 
promote_command = '/usr/pgsql-16/bin/repmgr standby promote -f /etc/repmgr/16/repmgr.conf --log-to-file'
follow_command = '/usr/pgsql-16/bin/repmgr standby follow -f /etc/repmgr/16/repmgr.conf --log-to-file --upstream-node-id=%n'
 
service_start_command = 'sudo systemctl start postgresql-16.service'
service_stop_command = 'sudo systemctl stop postgresql-16.service'
service_restart_command = 'sudo systemctl restart postgresql-16.service'
service_reload_command = 'sudo systemctl reload postgresql-16.service'

2)Standby

node_id=2node_name='repmgr_2'conninfo='host=192.168.56.252 user=repmgr password=repmgr dbname=repmgr connect_timeout=2 port=5432'#conninfo='host=vm8-1  user=repmgr dbname=repmgr password=repmgr connect_timeout=2 port=5436' data_directory='/var/lib/pgsql/16/data'use_replication_slots = yes#use_primary_conninfo_password = true repmgrd_pid_file='/var/lib/pgsql/16/repmgr/repmgrd.pid'log_file = '/var/lib/pgsql/16/repmgr/repmgrd.log'pg_bindir = '/usr/pgsql-16/bin/' failover = automaticmonitoring_history=yesmonitor_interval_secs=10#ssh_options='-q -o StrictHostKeyChecking=no -o ConnectTimeout=10'reconnect_attempts=3reconnect_interval=3node_rejoin_timeout=10 use_replication_slots=true #repmgrd_service_start_command='sudo systemctl start repmgr-16.service'#repmgrd_service_stop_command='sudo systemctl stop repmgr-16.service'repmgrd_service_start_command='/usr/pgsql-16/bin/repmgrd -d -f /etc/repmgr/16/repmgr.conf'repmgrd_service_stop_command='kill `cat $(/usr/pgsql-16/bin/repmgrd --show-pid-file)`'  promote_command = '/usr/pgsql-16/bin/repmgr standby promote -f /etc/repmgr/16/repmgr.conf --log-to-file'follow_command = '/usr/pgsql-16/bin/repmgr standby follow -f /etc/repmgr/16/repmgr.conf --log-to-file --upstream-node-id=%n' service_start_command = 'sudo systemctl start postgresql-16.service'service_stop_command = 'sudo systemctl stop postgresql-16.service'service_restart_command = 'sudo systemctl restart postgresql-16.service'service_reload_command = 'sudo systemctl reload postgresql-16.service'

7. (P) 클러스터에 primary node 등록 (postgres OS유저로)

$ repmgr primary register$ repmgr cluster show 
##(상태확인)
postgres=# select * from pg_stat_replication;postgres=# select * from pg_replication_slots ;
따로 생성해주지 않았는데, REPMGR에 의해 slot이 생긴 모습을 볼 수 있다.

8. (S) standby clone 수행 (postgres OS유저로)

1) repmgr을 이용해 basebackup$ repmgr -h 192.168.56.251 -U repmgr -d repmgr -p 5432 -f /etc/repmgr/16/repmgr.conf standby clone --force (--force 옵션은 initdb를 수행하지 않았더라면 넣어주지 않아도 된다)
2) standby register
$ sudo systemctl start postgresql-16.service
$ repmgr standby register

 

9. (P,S) repmgr daemon start (postgres OS유저로)
$  repmgr daemon start

 

## 상태확인

$ echo;echo;echo;echo;repmgr service status; echo;echo;echo;echo;repmgr cluster show; echo;echo; echo;echo;repmgr node status; echo;echo;echo;echo;

 

 ID | Name     | Role    | Status    | Upstream | repmgrd | PID  | Paused? | Upstream last seen
----+----------+---------+-----------+----------+---------+------+---------+--------------------
 1  | repmgr_1 | primary | * running |          | running | 1814 | no      | n/a
 2  | repmgr_2 | standby |   running | repmgr_1 | running | 1816 | no      | 8 second(s) ago


 ID | Name     | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string----+----------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------------------
 1  | repmgr_1 | primary | * running |          | default  | 100      | 1        | host=192.168.56.251 user=repmgr password=repmgr dbname=repmgr connect_timeout=2 port=5432
 2  | repmgr_2 | standby |   running | repmgr_1 | default  | 100      | 1        | host=192.168.56.252 user=repmgr password=repmgr dbname=repmgr connect_timeout=2 port=5432

Node "repmgr_1":
        PostgreSQL version: 16.9
        Total data size: 30 MB
        Conninfo: host=192.168.56.251 user=repmgr password=repmgr dbname=repmgr connect_timeout=2 port=5432
        Role: primary
        WAL archiving: enabled
        Archive command: cp %p /var/lib/pgsql/16/archive/%f
        WALs pending archiving: 0 pending files
        Replication connections: 1 (of maximal 10)
        Replication slots: 3 physical (of maximal 10; 0 missing); 2 inactive
        Replication lag: n/a