pg DB 이중화 관리 repmgr 구축

• 목차
  
  • repmgr 설치
  • passwordless 설정
  • postgresql 설정 변경 
  • repmgr 계정생성
  • repmgr 설정 변경
  • repmgr primary 등록
  • repmgr standby 등록 (primary clone)
  • 동기화 확인

---

 

1. 양쪽노드 간 repmgr 설치

sudo apt install postgresql-15-repmgr

postgres@lkmpg:~$ sudo apt install postgresql-15-repmgr
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  repmgr-common
The following NEW packages will be installed:
  postgresql-15-repmgr repmgr-common
0 upgraded, 2 newly installed, 0 to remove and 132 not upgraded.
Need to get 423 kB of archives.
After this operation, 1,251 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 https://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 repmgr-common all 5.5.0+debpgdg-1.pgdg24.04+ [187 kB]
Get:2 https://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 postgresql-15-repmgr amd64 5.5.0+debpgdg-1.pdg24.04+1 [235 kB]
Fetched 423 kB in 6s (72.0 kB/s)
Selecting previously unselected package repmgr-common.
(Reading database ... 124332 files and directories currently installed.)
Preparing to unpack .../repmgr-common_5.5.0+debpgdg-1.pgdg24.04+1_all.deb ...
Unpacking repmgr-common (5.5.0+debpgdg-1.pgdg24.04+1) ...
Selecting previously unselected package postgresql-15-repmgr.
Preparing to unpack .../postgresql-15-repmgr_5.5.0+debpgdg-1.pgdg24.04+1_amd64.deb ...
Unpacking postgresql-15-repmgr (5.5.0+debpgdg-1.pgdg24.04+1) ...
Setting up repmgr-common (5.5.0+debpgdg-1.pgdg24.04+1) ...
Setting up postgresql-15-repmgr (5.5.0+debpgdg-1.pgdg24.04+1) ...
Processing triggers for postgresql-common (274.pgdg24.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Scanning processes...
Scanning linux images...

Running kernel seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

 

2. 양쪽 노드 간 양방향 passwordless 설정

 

ssh-keygen -t rsa

ssh-copy-id 192.168.100.137

 

ssh-keygen -t rsa

ssh-copy-id 192.168.100.136

 

3. postgresql 설정 변경 

postgresql.conf에 아래 라인 추가

shared_preload_libraries = 'repmgr'     # (change requires restart)  

 

pg_hba.conf

repmgr trust로 허용 추가 ( repmgr 유저로 replication 도 허용이 되야한다 clone 복제시 사용)

local   replication   repmgr                              trust
host    replication   repmgr      127.0.0.1/32            trust
host    replication   repmgr      192.168.0.1/16           trust

local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      192.168.0.1/16           trust

 

4. repmgr계정생성 (마스터)

create user repmgr superuser encrypted password 'repmgr';
create database repmgr owner repmgr;

 

5. repmgr.conf 생성

[master]
node_id=1
node_name='node1'
conninfo='host=node1 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/postgresql/15/main'

pg_bindir='/usr/lib/postgresql/15/bin'
log_file='/var/log/repmgr/repmgr.log'


[standby]
node_id=2
node_name='node2'
conninfo='host=node2 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/postgresql/15/main'

pg_bindir='/usr/lib/postgresql/15/bin'
log_file='/var/log/repmgr/repmgr.log'

 

 

5.1 repmgr 등록 (마스터)

postgres@lkmpg:~$ sudo -u postgres repmgr -f repmgr.conf primary register

postgres@lkmpg:~$ sudo -u postgres repmgr -f repmgr.conf primary register
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered

 

6. standby 중지 및 clone 수행

--DB중지
sudo systemctl stop postgresql

--데이터 디렉토리 비우기 
sudo -u postgres rm -rf /var/lib/postgresql/15/main/*

--클론 복제
sudo -u postgres repmgr -h 192.168.100.137 -U repmgr -f /var/lib/postgresql/repmgr.conf standby clone

 

복제 후에 기동 후 standby 도 등록하라고 안내함

postgres@lkmpg:~$ sudo -u postgres repmgr -h 192.168.100.137 -U repmgr -f /var/lib/postgresql/repmgr.conf standby clone
NOTICE: destination directory "/var/lib/postgresql/15/main" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.100.137 user=repmgr
DETAIL: current installation size is 119 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: checking and correcting permissions on existing directory "/var/lib/postgresql/15/main"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  /usr/lib/postgresql/15/bin/pg_basebackup -l "repmgr base backup"  -D /var/lib/postgresql/15/main -h 192.168.100.137 -p 5432 -U repmgr -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /var/lib/postgresql/15/main start
HINT: after starting the server, you need to register this standby with "repmgr standby register"

 

6.1 repmgr 등록 (standby)

sudo -u postgres repmgr -f /var/lib/postgresql/repmgr.conf standby register

 

등록이 안될 경우 --force 옵션으로 강제 등록도 가능

 

7. repmgr 현황조회

repmgr cluster show

postgres@lkmpg:~$ repmgr cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 11       | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | ? node1  | default  | 100      | 11       | host=node2 user=repmgr dbname=repmgr connect_timeout=2

 

8. repmgr 데몬 기동 및 자동기동 등록

sudo systemctl enable repmgrd
sudo systemctl start repmgrd

9. 동기화확인

 

* 여러 가지 테스트를 하다보니 복제슬롯을 바라보고 있는게 repl_slot_01 을 바라보고 있었는데

구 stand by (현 마스터) 장비에는 해당 슬롯이 없어서 복제가 이뤄지지 않고 실패하고 있었다. 

primary 바라보는 슬롯명을 변경해서 복제를 다시 정상화 시켜 줬다.

 

2025-04-05 16:29:43.661 P00   INFO: archive-get command begin 2.54.2: [0000000B0000000000000073, pg_wal/RECOVERYXLOG] --exec-id=5304-73cfdf03 --log-level-console=info --pg1-path=/var/lib/postgresql/15/main --repo1-path=/var/lib/pgbackrest --stanza=demo
2025-04-05 16:29:43.664 P00   INFO: unable to find 0000000B0000000000000073 in the archive
2025-04-05 16:29:43.664 P00   INFO: archive-get command end: completed successfully (17ms)
2025-04-05 16:29:43.694 KST [5305] FATAL:  could not start WAL streaming: ERROR:  replication slot "repl_slot_01" does not exist


postgres@lkmpg:~$ cat /var/lib/postgresql/15/main/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"'
primary_slot_name = 'repl_slot_01'        <---------이 부분
primary_conninfo = 'host=node1 user=repmgr application_name=node2 connect_timeout=2'
postgres@lkmpg:~$


alter system set primary_slot_name = 'repl_slot_02';