postgresql replication

1. External 복제 (외부 package)

  1) Slony : Single-master replication based on trigger, 향후 logical replication 지원 예정

  2) Bucardo : Multi-master replication using trigger

2. Postgresql 지원 복제

  1) PSR (Physical Streaming Replicatoin) : Single master, multi-standby 

  2) LSR (Logical Streaming Replication)

3. 기타

  1) Clustered or massively parallel database : Postgres-XL (based on Global Transaction Manager and hash-based distribution) => PL/Proxy, pg_shard

  2) Multimaster replication : BDR (Bi-Directional Replication)

4. Streaming Replication 구성

  1) replication user 생성

     create user repuser superuser login connection limit 1 encrypted password 'pass';

  2) pg_hba.conf 수정

     host    replication    repuser    127.0.0.1/0         md5

  3) replication 정보 (접속시도, fail등) 수집 위한 파라미터 셋팅 (postgresql.conf)

     log_connections = on

  4) master 서버 파라미터 설정

     max_wal_senders = 2

     wal_level = 'archive'    --> standy(read only) 사용시 hot standby로 설정

     archive_mode = on

     archive_command = 'cd .'                                             --streaming

     archive_command = 'cp -i %p $HOME/bin/postgres/archive/%f'  --file-based

     wal_keep_segments = 10000 (-> 160GB, segment당 16M)

     => base backup 또는 transaction log시 사용하는 pg_xlog 디렉토리를 따로 

         분리하는게 권장

  

  5) base backup 수행

     - oracle에 begin/end 백업처럼

       psql -c "select pg_start_backup('base backup for streaming')"

       rsync -cva --inplace --execlude=*pg_xlog*   ${PGDATA}/

       $STANDBYNODE:$PGDATA  

       psql -c "select pg_stop_backup(), current_timestamp"

     - backup tool를 사용하여

       pg_basebackup -d 'connection_string' -D /path/to_data_dir  

       --xlog-method=stream  --max_rate=RATE   (log도 전송 및 전송량 제한 kb/s)

   6) slave에 recovery.conf 파라미터 생성 및 설정

  standby_mode = 'on'    -- off로 바꾸면 새로운 마스터로 write 가능

  primary_conninfo = 'host=192.168.0.1 user=rep password="pass" application_name=s1'

  restore_command = 'cp -i $HOME/bin/postgres/archive/%f %p'  --file-based

  tigger_file = '/tmp/postgresql.trigger.5432'   -- 9.1 이후부터는 더 이상 필요없음

  * slave read only 설정 (postgresql.conf) 

    hot_standby = on  

   7) slave server start

   8) monitoring 

   9) 8번까지 설정하면 기본적으로 async 방식

      만약 sync 방식으로 바꾸고 싶다면,

      standby에 application_name(recover.conf, primary_conninfo항목)을 설정후

      synchronous_standby_name(마스터) 파라미터를 standby application_name 으로

      설정  (여러대면 순서로대로 기술)

  

     -- sync mode 설정 확인

      SELECT

         application_name,state       /* startup, backup, catchup or streaming */,

         sync_priority                /* 0, 1 or more */ ,

         sync_state                   /* async, sync or potential */

FROM pg_stat_replication

ORDER BY sync_priority;

-- 설정이 되어 있으나 standby에서 sync 형태로 받고 싶지 않을때

wal_receiver_status_interval = 0

* sync 구성은 master가 commit이 최소 1대의 standby에 반영되어야 함으로 최소 3대 이상으로 구성해야 함 (2대일 경우 standby 문제시 장애 발생함)

   10) 기타

      -- replication role 생성후 부여

      alter role repuser replication

      create role repuser with replication

      alter role repuser connection limit 2

      -- replication 충돌 확인

     SELECT datname, conflicts FROM pg_stat_database;

     SELECT datname,confl_tablespace,confl_lock,confl_snapshot,

               confl_bufferpin,confl_deadlock FROM pg_stat_database_conflicts;

      -- replication 상태 확인

      standby(slave)당 one row로 표현되며 master에서 모든 standby 현황을 확인

     select * from pg_stat_replication

     select pid,

   application_name,                     /* or other unique key */

   pg_current_xlog_insert_location(),      /* WAL Insert location */

   sent_location,                         /* WALSender location */

   write_location,                         /* WALReceiver write loc */

   flush_location,                         /* WALReceiver flush loc */

   replay_location,                        /* Standby apply location */

     from pg_stat_replication 

             

     -- 마스터 변경(테이블 변경등)으로 lock시 slave에서 read를 유지 하기 

      max_standby_streaming_delay = -1,  max_standby_archive_delay = -1

      (마스터 작업이 끝나면 다시 파라미터 변경후 reload.....)

  

     -- slave server 변경사항 반영 안하기

      recovery.conf 파일안에  restore_command 또는 primary_conninfo 값을 설정하지 않음

5. Repmgr Cluster 구성

6. Replicatoin Slots (9.4 버전부터 지원)

  slot 생성후 disconnect된 node에 대한 데이터 tracking을 위해 wal 파일을 계속 유지함

  으로 관리 주의 요함

  1) 각각 sending node max_replication_slots >0  설정

     max_replication_slots = 2

  2) sending node에서 slot 생성

    select pg_create_physical_replication_slot('test_2_3')

  3) slot이 생성되었는지 확인

    select * from pg_replication_slots

  4) slave(standby) server에서 recovery.conf 파일 설정

    primary_slot_name = 'test_2_3'

  5) slot 제거시 

    select pg_drop_physical_replication_slot('test_2_3')

7. Replication 모니터링

  1) pg_isready or PQping()

  2) 마스터인지 standby인지 확인

     select pg_is_in_recovery();

  3) standby에서 transaction replay인지 확인

     select pg_is_xlog_replay_paused()

  4) 서버가 in-exclusive backup 모드인지 확인

     select pg_is_in_backup()

  5) WAL file에서 WAL record에 데이터 insert 위치 (on master)

     select pg_current_xlog_insert_location()

  6) WAL 최근 Write 위치 (on master)

     select pg_current_xlog_location()

  7) standby 최근 transaction log 적용 시점 (on standby)

     select pg_last_xlog_replay_location()

  8) standby 서버에 가장 최근 committed 되어진 시점

     SELECT pg_last_xact_replay_timestamp()

  9) slot과 stat join하여 replication 상태 확인

     기본적으로 pg_stat_replication은 현재 접속되어 있는 connetion에 대해서만 나옴

     기존 연결되었던 상황을 확인하기 위해서는 slot 구성후 아래와 같이 조회

     SELECT slot_name, database, age(xmin), age(catalog_xmin)

     FROM pg_replication_slots WHERE NOT active;

 

     pg_stat_replication과 join 하여 정보 확인

     SELECT slot_name FROM pg_replication_slots JOIN pg_stat_replication ON application_name LIKE slot_name || '%';

8. standby 시간 Gap 차이 반영

  standby 서버 recovery.conf 파일에 아래와 같은 파라미터 설정

  recovery_min_apply_delay = 시간

  

  delay로 인한 noti가 계속가 발생할 경우  hot_standby_feedback = on 설정

9. standby 트랜잭션 지연 반영

  select pg_xlog_replay_pause()    -- 반영중단

  select pg_xlog_replay_resume()   -- 재반영

10.  Logical Replication

   LSR은 PSR과 함께 사용이 가능하며, 파타미터 설정에 충돌이 없음.

   9.4에서 DDL 및 sequences에 대한 동기는 지원하지 않으며, DML만 지원

   LSR은 단방향만 사용 가능

  1) extension 설치

     create extension btree_gist

     create extension bdr

     shared_preload_libraries = 'bdr'

  2) master 파라미터 설정 (postgresql.conf)

     - 각각 LSR link는 single database만 지원  (template0와 template1 제외)

     - 각각 LSR link는 one connection과 one slot만 지원

       max_replication_slots, max_connections

     - 각각 LSR link는 one WAL sender 지원 

       max_wal_senders 

     - 각각 LSR link는 downstream(sender)에 one apply process 지원

       max_worker_processes

 

     wal_level = 'logical'

     shared_preload_libraries = 'bdr'

     max_replication_slots = 10

     max_wal_senders = 10

     update 또는 delete에 대한 LSR시 unique한 row를 구별하기 위해 테이블에

     pk가 있어야 하며, 만약에 있을 경우 아래와 같이 identity를 설정함

     alter table mytable replica identity using index test_idx

11. BDR (bI-directional replication)

   

    track_commit_timestamps = on

 

    circular replication

    group to group 

    central rollup

    central broadcast

12. archiving transaction log data

    transaction log data를 다양한 목적으로 remote server copy 할수 있음

    1) pg_receivexlog : 9.2 이후 버전부터 사용 가능하며, physical transaction log data에

                          대해서 archiving 한다.  Replication slots 필요

      -- transaction log 추출시

      pg_receivexlog -D /data/archive -d 'host=127.0.0.1 user=postgres password=test00 application_name=test01' --slot=test01  -n

      -- transaction log dump시       

      pg_xlogdump  transaction_log_file      

    2) pg_recvlogical : 9.4 이후 버전부터 사용 가능하며, logical decoding 결과에 대해서 

                          archiving 한다. Replication Slots 필요

   - Replication paramerter 셋팅(wal_level, max_wal_senders등) 이 완료되어 있어야 사용

     가능 하며, pg_recvlogical 사용시 wal_level은 logical이어야 함