PostgreSQL9.6 同步复制测试

一、开启同步复制

[postgres@node01 data]$ vi  postgresql.conf 


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------


# - Settings -


wal_level = logical # minimal, replica, or logical
# (change requires restart)
#fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)




#checkpoint_timeout = 5min # range 30s-1h
max_wal_size = 1GB
min_wal_size = 80MB




# - Sending Server(s) -


# Set these on the master and on any standby that will send replication data.


max_wal_senders = 5 # max number of walsender processes
# (change requires restart)
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables




# - Standby Servers -


# These settings are ignored on a master server.


hot_standby = on # "on" allows queries during recovery
# (change requires restart)

# - Archiving -

archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /archivelog/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
#               %f = file name only

# - Master Server -

# These settings are ignored on a standby server.


synchronous_standby_names = 'ghan' # standby servers that provide sync rep
# number of sync standbys and comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed

重启postgres


二、 备机作业

[root@node02 pg9.6]# mkdir backup
[root@node02 pg9.6]# mkdir data
[root@node02 pg9.6]# chown postgres -R backup/
[root@node02 pg9.6]# chown postgres -R data/
[root@node02 pg9.6]# 
[postgres@node02 ~]$ pg_basebackup -D backup   -h 108.88.3.180  -U replication --xlog-method=stream //注意其它表空间位置是否存在
[postgres@node02 backup]$  vi recovery.conf 


#---------------------------------------------------------------------------
# STANDBY SERVER PARAMETERS
#---------------------------------------------------------------------------
#
# standby_mode
#
# When standby_mode is enabled, the PostgreSQL server will work as a
# standby. It will continuously wait for the additional XLOG records, using
# restore_command and/or primary_conninfo.
#
standby_mode = on
#
# primary_conninfo
#
# If set, the PostgreSQL server will try to connect to the primary using this
# connection string and receive XLOG records continuously.
#
primary_conninfo = 'host=108.88.3.180 port=5432  user=replication password=6220104 application_name=ghan' # e.g. 'host=localhost po
rt=5432'




[postgres@node02 backup]$  pg_ctl -D /home/postgres/backup/ start -l 111.log


三、同步复制状态检查在主节点


ghan=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 13675
usesysid         | 16396
usename          | replication
application_name | ghan
client_addr      | 108.88.3.116
client_hostname  | 
client_port      | 51379
backend_start    | 2016-07-10 15:18:52.934411+08
backend_xmin     | 
state            | streaming
sent_location    | 0/3B000220
write_location   | 0/3B000220
flush_location   | 0/3B000220
replay_location  | 0/3B000220
sync_priority    | 1
sync_state       | sync  //即表示同步复制


四、关闭备节点测试
 
 
 
ghan=# insert into t2_test values(generate_series(1,70));  //主节点无法提交数据，只有备份节点恢复才可以提交
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 70
ghan=# ^C
ghan=# 


五、关闭同步提交

synchronous_commit = off # synchronization level;