1 server

2 postgresql9.4 install

3 master 설정

4 slave 설정

5 replication test

6 fail over

6.1 192.168.56.102 서버 세팅

6.2 192.168.56.103 서버 세팅

6.3 replication test

7 pgbouncer

8 참고

• os: centos7
  
• 192.168.56.101 --> master
  
• 192.168.56.102 --> slave
  
  

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 

.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

.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

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"

시나리오는 다음과 같다.

• 192.168.56.101(master) 서버가 죽었다.
  
• 그래서 192.168.56.102(slave)로 fail over 시킨다. (IP나 hosts 파일 편집은 상황에 맞춰 적당히 한다)
  
• 새로운 slave 서버 192.168.56.103를 추가한다.
  
• 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

앞서 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

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"

테스트를 마친 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