postgresql 读写分离

准备三台机器 系统Centos7

机器名	ip
master	192.168.0.1
client1	192.168.0.2
client2	192.168.0.3

全部安装postgresql 安装文档
master

$  su - postgres
$  psql -U postgres
create user synchronization with login replication password 'synchronization';
$  vim $PGDATA/pg_hba.conf 
host    replication     synchronization         192.168.0.1/32         md5
host      all           postgres                192.168.0.1/32         trust
host    replication     synchronization         192.168.0.2/32         md5
host      all           postgres                192.168.0.2/32         trust
host    replication     synchronization         192.168.0.3/32         md5
host      all           postgres                192.168.0.3/32         trust
$  vim $PGDATA/postgresql.conf
max_wal_senders = 10
wal_level = replica
wal_log_hints = on
wal_keep_segments = 10
wal_receiver_status_interval = 5s
hot_standby_feedback = on
$  pg_ctl restart -D $PGDATA

client1 和 client2

$  su - postgres
$  psql -U postgres
create user synchronization with login replication password 'synchronization';
$  pg_ctl stop -D $PGDATA
$  rm –rf $PGDATA
$  pg_basebackup -Xs -d "hostaddr=192.168.0.1 port=5432 user=synchronization password=synchronization" -D $PGDATA -v -Fp
$  vim $PGDATA/postgresql.conf
hot_standby = on
$  cp share/recovery.conf.sample $PGDATA/recovery.conf
$  vim $PGDATA/recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.1 port=5432 user=synchronization password=synchronization'
trigger_file = 'tgfile'
$  pg_ctl start -D $PGDATA

切换master
client1(切换成master)

$  pg_ctl promote -D $PGDATA
$  pg_controldata | grep cluster

master

$  pg_ctl stop -m fast -D $PGDATA
$  pg_rewind --target-pgdata $PGDATA --source-server='host=192.168.0.2 port=5432 user=postgres  password=postgres dbname=postgres' -P
$  cp share/recovery.conf.sample $PGDATA/recovery.conf
$  vim $PGDATA/recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.2 port=5432 user=synchronization password=synchronization'
trigger_file = 'tgfile'
$  pg_ctl start -D $PGDATA

client2

$  vim $PGDATA/recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.2 port=5432 user=synchronization password=synchronization'
trigger_file = 'tgfile'
$  pg_ctl restart -D $PGDATA