#title Streaming Replication [[TableOfContents]] ==== server ==== * os: centos7 * 192.168.56.101 --> master * 192.168.56.102 --> slave ==== postgresql9.4 install ==== 192.168.56.101, 192.168.56.102 서버에 install 참고: http://www.unixmen.com/postgresql-9-4-released-install-centos-7/ {{{ rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm yum update yum install postgresql94-server postgresql94-contrib /usr/pgsql-9.4/bin/postgresql94-setup initdb systemctl enable postgresql-9.4 systemctl start postgresql-9.4 firewall-cmd --permanent --add-port=5432/tcp firewall-cmd --permanent --add-port=80/tcp firewall-cmd --reload setsebool -P httpd_can_network_connect_db 1 su - postgres psql \password postgres }}} ==== master 설정 ==== .conf 파일이 있는 디렉토리로 이동 {{{ #locate pg_hba.conf #/var/lib/pgsql/9.4/data/pg_hba.conf cd /var/lib/pgsql/9.4/data/ }}} pg_hba.conf파일에 replication 서버ip 등록 {{{ vi pg_hba.conf }}} {{{ host replication postgres 192.168.56.101/32 trust host replication postgres 192.168.56.102/32 trust host all all 192.168.56.1/32 trust }}} 참고: * 192.168.56.1 --> 클라이언트 postgesql.conf 수정 {{{ vi postgresql.conf }}} {{{ listen_addresses = '*' ssl = false wal_level = hot_standby max_wal_senders = 5 wal_keep_segement = 64 max_replication_slot = 1 }}} 슬롯 생성 {{{ su - postgres psql -c "select * from pg_create_physical_replication_slot('my_rep_slot');" exit }}} 권한 변경 {{{ chown -R postgres.postgres /var/lib/pgsql }}} postgresql restart {{{ su - postgres cd /usr/pgsql-9.4/bin ./pg_ctl -D /var/lib/pgsql/9.4/data stop -m fast ./pg_ctl start -l /var/lib/pgsql/9.4/data/pg_log/postgres.log -D /var/lib/pgsql/9.4/data }}} or {{{ systemctl restart postgresql-9.4 systemctl stop postgresql-9.4 }}} ==== slave 설정 ==== .conf 파일이 있는 디렉토리로 이동 {{{ #locate pg_hba.conf #/var/lib/pgsql/9.4/data/pg_hba.conf cd /var/lib/pgsql/9.4/data/ }}} pg_hba.conf파일에 replication 서버ip 등록 {{{ vi pg_hba.conf }}} {{{ host replication postgres 192.168.56.101/32 trust host replication postgres 192.168.56.102/32 trust host all all 192.168.56.1/32 trust }}} 참고: * 192.168.56.1 --> 클라이언트 master 복사 {{{ cd / systemctl stop postgresql-9.4 mv /var/lib/pgsql/9.4/data /var/lib/pgsql/9.4/data_old su - postgres pg_basebackup -h 192.168.56.101 -p 5432 -U postgres -D /var/lib/pgsql/9.4/data --xlog --checkpoint=fast --progress ls /var/lib/pgsql/9.4/data exit }}} postgesql.conf 수정 {{{ cd /var/lib/pgsql/9.4/data/ vi postgresql.conf }}} {{{ listen_addresses = '*" ssl = false wal_level = hot_standby max_wal_senders = 5 wal_keep_segement = 64 max_replication_slot = 1 hot_standby = on }}} recovery.conf 생성 {{{ cd /var/lib/pgsql/9.4/data/ vi recovery.conf }}} {{{ primary_slot_name = 'my_rep_slot' recovery_target_timeline = 'latest' primary_conninfo = 'host=192.168.56.101 port=5432 user=postgres password=9999' standby_mode = on }}} 권한 변경 {{{ chown -R postgres.postgres /var/lib/pgsql }}} postgresql restart {{{ su - postgres cd /usr/pgsql-9.4/bin ./pg_ctl -D /var/lib/pgsql/9.4/data stop -m fast ./pg_ctl start -l /var/lib/pgsql/9.4/data/pg_log/postgres.log -D /var/lib/pgsql/9.4/data }}} or {{{ systemctl restart postgresql-9.4 systemctl stop postgresql-9.4 }}} ==== replication test ==== master 서버에서.. {{{ su - postgres psql -x -c "create table replication_test(id int)" psql -x -c "insert into replication_test(id) values(1)" psql -x -c "select * from replication_test" }}} slave 서버에서.. {{{ su - postgres psql -x -c "select * from replication_test" }}} 거의 실시간으로 반영되는 것 같다. master 서버에서.. {{{ su - postgres psql -x -c "Select * From pg_stat_replication" }}} ==== fail over ==== 시나리오는 다음과 같다. * 192.168.56.101(master) 서버가 죽었다. * 그래서 192.168.56.102(slave)로 fail over 시킨다. (IP나 hosts 파일 편집은 상황에 맞춰 적당히 한다) * 새로운 slave 서버 192.168.56.103를 추가한다. * 192.168.56.102를 서비스에 참여시킨다. ===== 192.168.56.102 서버 세팅 ===== slave에서 쓰기 가능하도록 promote 한다. 이제 기존의 slave 서버는 master가 된다. {{{ cd /usr/pgsql-9.4/bin ./pg_ctl promote }}} 슬롯 생성 {{{ su - postgres psql -c "select * from pg_create_physical_replication_slot('my_rep_slot');" exit }}} 192.168.56.103 서버를 pg_hba.conf에 추가한다. {{{ cd /var/lib/pgsql/9.4/data/ vi pg_hba.conf }}} {{{ host replication postgres 192.168.56.101/32 trust host replication postgres 192.168.56.102/32 trust host replication postgres 192.168.56.103/32 trust host all all 192.168.56.1/32 trust }}} postgresql.conf에서 hot_standby = on 주석처리 {{{ cd /var/lib/pgsql/9.4/data/ vi postgresql.conf }}} {{{ #hot_standby = on }}} restart {{{ systemctl restart postgresql-9.4 }}} ===== 192.168.56.103 서버 세팅 ===== 앞서 slave에 설정한 것 처럼 설정한다. 단, IP는 기존의 slave서버(192.168.56.102)를 바라보게 한다. {{{ cd /var/lib/pgsql/9.4/data/ vi pg_hba.conf }}} {{{ host replication postgres 192.168.56.101/32 trust host replication postgres 192.168.56.102/32 trust host replication postgres 192.168.56.103/32 trust host all all 192.168.56.1/32 trust }}} master 복사(기존의 master 서버인 192.168.56.101는 죽은 상태이고, 192.168.56.102로 promote 된 상태이므로 192.168.56.102를 복사) {{{ cd / systemctl stop postgresql-9.4 mv /var/lib/pgsql/9.4/data /var/lib/pgsql/9.4/data_old su - postgres pg_basebackup -h 192.168.56.102 -p 5432 -U postgres -D /var/lib/pgsql/9.4/data --xlog --checkpoint=fast --progress ls /var/lib/pgsql/9.4/data exit }}} postgesql.conf 수정 {{{ cd /var/lib/pgsql/9.4/data/ vi postgresql.conf }}} {{{ listen_addresses = '*" ssl = false wal_level = hot_standby max_wal_senders = 5 wal_keep_segement = 64 max_replication_slot = 1 hot_standby = on }}} recovery.conf 생성 {{{ cd /var/lib/pgsql/9.4/data/ vi recovery.conf }}} {{{ primary_slot_name = 'my_rep_slot' recovery_target_timeline = 'latest' primary_conninfo = 'host=192.168.56.102 port=5432 user=postgres password=9999' standby_mode = on }}} 권한 변경 {{{ chown -R postgres.postgres /var/lib/pgsql }}} 재시작 {{{ systemctl restart postgresql-9.4 }}} ===== replication test ===== 192.168.56.102 에서.. {{{ su - postgres psql -x -c "insert into replication_test(id) values(100)" psql -x -c "select * from replication_test" psql -x -c "create table replication_test2(id int)" psql -x -c "insert into replication_test2(id) values(1)" psql -x -c "select * from replication_test2" }}} 192.168.56.103 에서.. {{{ su - postgres psql -x -c "select * from replication_test" psql -x -c "select * from replication_test2" }}} ==== pgbouncer ==== 테스트를 마친 192.168.56.101서버를 이용하여 pgbouncer를 세팅(설치한 문서는 [pgbouncer]를 참고). ==== 참고 ==== * http://bonoken.tistory.com/107 * http://cutewebi.tistory.com/727 * http://senvichet.com/how-to-setup-postgres-9-4-master-slave-streaming-replication/ * http://www.unixmen.com/postgresql-9-4-released-install-centos-7/ * http://avicom.tistory.com/94 * http://splee75.tistory.com/104