Contents

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 谿瑚


1 server #

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

2 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 

3 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

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

5 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"

6 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襯 觜れ 谿語.

6.1 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
6.2 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

6.3 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"

7 pgbouncer #

ろ碁ゼ 襷豺 192.168.56.101覯襯 伎 pgbouncer襯 誤(れ 覓語 pgbouncer襯 谿瑚).

8 谿瑚 #