디비랑-2) Streaming Replication 구현 요약

##
## CentOS 6.4, PostgreSQL 9.2.4 community
##

1. 물리적 구성 계획
    o Master IP : 192.168.0.10
    o Slave IP : 192.168.0.20
    o Version : PostgreSQL 9.2.4
    o Install Directory : /home/postgres/pgsql
    o Data Directory : /databases/data
    o WAL Directory : /databases/ARCHIVE
    o Backup Directory : /datbases/backup/cluster (base backup output)

2. (on Master) postgresql.conf 변경
    wal_level = hot_standby
    archive_mode = on
    max_wal_senders = 2
    archive_command = 'cp %p /home/database/ARCHIVE/%f'
    wal_keep_segments = 50

3. (on Master) pg_hba.conf 변경
    host  replication  postgres  192.168.0.10/32  trust
    host  replication  postgres  192.168.0.20/32  trust

4. (on Master) 서버 restart
    $ pg_ctl restart

5. (on Master) base backup 및 slave로 이관
    $ pg_basebackup -h 192.168.0.10 -U postgres -D /databases/backup/cluster -xlog -c fast -P
    $ rsync -ra /databases/backup/cluster/* root@192.168.0.20:/databases/backup/cluster/

6. (on Slave) 서버 stop
    $ pg_ctl stop

7. (on Slave) master에서 백업/이관된 파일로 교체
    $ mv /home/postgres/pgsql/data /home/postgres/pgsql/data_old
    $ mkdir /home/postgres/pgsql/data
    $ tar -xvf /databases/backup/cluster/base.tar

8. (on Slave) recovery.conf 변경
    standby_mode = on
    primary_conninfo = 'host=192.168.0.20 port=5432'

9. (on Slave) 서버 startup
    $ pg_ctl startup

10. Failover 테스트
    ## stop Master on Master
    $ pg_ctl stop

    ## make DML on slave
    insert into ttt values (5,5);

    STATEMENT:  insert into ttt values (4,4);
    ERROR:  cannot execute INSERT in a read-only transaction

    ##  Promote Slave on Slave
    $ pg_ctl promote

    LOG:  archive recovery complete
    LOG:  database system is ready to accept connections
    LOG:  autovacuum launcher started

    ## make DML on Slave
    insert into ttt values (5,5);