##
## 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);