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 | confirme
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 수행