postgres 이중화구성

[생짜초보]

PostgreSQL Replication 설정-Streaming Replication

October 5, 2021 · 4 min · kimdubi

Table of Contents

• •  
  •  
  •  
• •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

---

REPLICATION 방식log shipping

• file-based 복제로 master의 WAL file이 생성되면 이 파일을 scp를 통해 standby서버로 전달하여 반영함
• wal file이 생길 때 까지 replication gap 이 발생할 수 있음

logical replication

• pub / sub 구조로 양방향 replication 가능
• 특정 테이블만 복제하는 partial replication 가능
• DDL 복제는 안되기 때문에 각각 수행해줘야함

streaming replication

• Master는 standyby 에게 transaction log entires 를 전달하고 standby 는 WAL file을 기다리지 않고 record 단위로 복제 수행
• 일반적으로 가장 많이 사용되는 복제 방식

streaming replication 설정1. replication 용 user 생성 (master)postgres=# create user repl replication password 'qhdks123'; CREATE ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- kimdubi | Superuser, Create role, Create DB, Replication, Bypass RLS | {} repl | Replication | {} testuser | No inheritance +| {} | Password valid until infinity | 2. postgresql.conf 수정 (master)

• vi /home1/kimdubi/psql/engn/PGSQL/postgresql.conf

wal_level = logical 또는 hot_standby archive_mode = on archive_command = 'cp %p /home1/kimdubi/psql/arch/testdb/%f' #max_wal_senders = 10 #max_replication_slots = 10

• 쌓이는 archive logfile은 아래와 같이 정리 필요

pg_archivecleanup /home1/kimdubi/psql/arch/testdb 000000010000000000000081.00000028.backup

=> xxxx.backup 이전의 아카이브 파일 모두 삭제

• postgresql.conf 수정 후 reload

pg_ctl reload -D /home1/kimdubi/psql/engn/PGSQL 3. pg_hba.conf 수정 (master)

• vi /home1/kimdubi/psql/engn/PGSQL/postgresql.conf 아래 내용 추가

host replication repl 10.161.78.34/32 md5

=> standby server (10.161.78.34) 에서 repl USER로 replication을 위해 오는 커넥션 요청은 md5로 인증해서 허가함

• pg_hba.conf 수정 후 reload

pg_ctl reload -D /home1/kimdubi/psql/engn/PGSQL 4. replication slot 생성 (master)postgres=# SELECT * FROM pg_create_physical_replication_slot('repl_slot_01'); slot_name | lsn --------------+----- repl_slot_01 | (1 row) postgres=# select * from pg_replication_slots; slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirm‎e d_flush_lsn --------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------- ------------ repl_slot_01 | | physical | | | f | f | | | | | (1 row)

=> standby 서버가 replication을 따라가지 못하는 경우를 대비하여 WAL 로그를 replication_slot에 두고 보관하겠다는 의미
replication_slot을 사용하면 wal_keep_segments 설정이 필요없음

5. Primary DB server copy (standby)### 기존 data 영역 삭제 [kimdubi@testserver2 15:37:09 ~/psql/engn ]$ rm -rf /home1/kimdubi/psql/engn/PGSQL ### Primary server => standby server copy [kimdubi@testserver2 15:40:06 ~/psql/engn ]$ pg_basebackup -h 11.111.11.11 -p3000 -D /home1/kimdubi/psql/engn/PGSQL -U repl -P -v -X stream Password: pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/6000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_64119" 31584/31584 kB (100%), 5/5 tablespaces pg_basebackup: write-ahead log end point: 0/60000F8 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed

• pg_basebackup -h primary_host -p primary_port -D PGDATA -U replication user

6. recovery.conf 설정 (standby)

• vi /home1/kimdubi/psql/engn/PGSQL/recovery.conf

standby_mode = on primary_conninfo='host=11.111.11.11 port=3000 user=repl password=qhdks123' primary_slot_name='repl_slot_01' #trigger_file='/home1/kimdubi/psql/engn/PGSQL/failover_trigger' 7. DB 기동 (standby)[kimdubi@testserver2 15:45:23 ~/psql/engn/PGSQL ]$ pg_ctl -D /home1/kimdubi/psql/engn/PGSQL start waiting for server to start....2020-04-08 06:45:25 GMT LOG: listening on IPv4 address "0.0.0.0", port 3000 2020-04-08 06:45:25 GMT LOG: listening on IPv6 address "::", port 3000 2020-04-08 06:45:25 GMT LOG: listening on Unix socket "/tmp/.s.PGSQL.3000" 2020-04-08 06:45:25 GMT LOG: redirecting log output to logging collector process 2020-04-08 06:45:25 GMT HINT: Future log output will appear in directory "/home1/kimdubi/psql/logs/testdb/error_log". done server started 8. replication 확인

• master

postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+----------------------------- pid | 65010 usesysid | 16396 usename | repl application_name | walreceiver client_addr | 22.222.22.22 client_hostname | client_port | 56754 backend_start | 2020-04-08 16:05:07.00658+09 backend_xmin | state | streaming sent_lsn | 0/B015A10 write_lsn | 0/B015A10 flush_lsn | 0/B015A10 replay_lsn | 0/B015A10 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async

• slave

### error_log 2020-04-08 07:05:07 GMT LOG: started streaming WAL from primary at 0/B000000 on timeline 1

• replication delay 확인 (slave에서 조회)

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; log_delay ----------- 0 (1 row) replication 구성 중 발생가능 에러

• standby => primary 접근 권한 없을 때

2020-04-08 15:23:50.883 KST [44534] FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "22.222.22.22", user "repl"

=> primary server의 접근허용 리스트에 standby 서버 정보가 없는 경우 발생함 primary에서 pg_hba.conf 에 standby 서버 추가 후 reload

• standby 서버 초기화 안했을 때

2020-04-08 15:26:51.084 KST [44642] FATAL: database system identifier differs between the primary and standby 2020-04-08 15:26:51.084 KST [44642] DETAIL: The primary's identifier is 6812776914427724112, the standby's identifier is 6813221629551992171.

=> standby 서버 $PGDATA 영역 삭제 후 다시 pg_basebackup 수행