$ ssh-keygen -t rsa

$ cp ~/.ssh/id_rsa.pub ~/.ssh/authorized_keys

$ ssh-copy-id postgres@10.189.100.195

$ ssh-keygen -t rsa

$ cp ~/.ssh/id_rsa.pub ~/.ssh/authorized_keys

$ ssh-copy-id postgres@10.189.102.118

postgres=# CREATE ROLE repl WITH REPLICATION PASSWORD 'repl' LOGIN;

$ grep "^host" pg_hba.conf

host    all             all             127.0.0.1/            trust

host    replication             repl             0.0.0.0/               md5

host    all             all             ::/                 trust

$ mkdir /usr/local/pgsql/arch

$ egrep "archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby" postgresql.conf

al_level = hot_standby            # minimal, archive, hot_standby, or logical

archive_mode = on        # enables archiving; off, on, or always

archive_command = 'test ! -f /usr/local/pgsql/arch/%f && cp %p /usr/local/pgsql/arch/%f && rsync -a %p postgres@10.189.100.195:/usr/local/pgsql/arch/%f'

max_wal_senders =         # max number of walsender processes

wal_keep_segments =         # in logfile segments, 16MB each;  disables

hot_standby = on            # "on" allows queries during recovery

#hot_standby_feedback = off        # send info from standby to prevent

$ pg_ctl stop -m fast

$ pg_ctl start

postgres=# SELECT pg_start_backup('label', true);

 pg_start_backup

-----------------

 /E6000060

( row)

$ rsync -az --progress ${PGDATA} postgres@10.189.100.195:/usr/local/pgsql/ --exclude postmaster.pid

postgres=# SELECT pg_stop_backup();

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

 pg_stop_backup

----------------

 /E60005C8

( row)

$ pg_basebackup --host=10.189.102.118 --username=repl --port= --label=backup --verbose --progress --pgdata=/usr/local/pgsql/data --checkpoint=fast --format=p --xlog-method=stream

Password:

transaction log start point: /EA000028 on timeline

pg_basebackup: starting background WAL receiver

/ kB (%), / tablespace

transaction log end point: /EA000830

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

$ mkdir /usr/local/pgsql/arch

$ grep "hot_standby" postgresql.conf

hot_standby = on                        # "on" allows queries during recovery

#hot_standby_feedback = off             # send info from standby to prevent

$ cat recovery.conf

restore_command = 'cp /usr/local/pgsql/arch/%f "%p"'

standby_mode = 'on'

primary_conninfo = 'user=repl password=repl host=10.189.102.118 port=5432 sslmode=disable sslcompression=1'

archive_cleanup_command = 'pg_archivecleanup -d /usr/local/pgsql/arch %r >> /usr/local/pgsql/arch/archive_cleanup.log'

trigger_file = '/usr/local/pgsql/data/trigger_active_standby'

$ pg_ctl start -w

waiting for server to start....LOG:  could not create IPv6 socket: Address family not supported by protocol

LOG:  redirecting log output to logging collector process

HINT:  Future log output will appear in directory "pg_log".

 done

server started

edbstore=# select * from pg_stat_replication;           #在primary主库查看

-[ RECORD 1 ]----+------------------------------

pid              | 15013

usesysid         | 19206

usename          | repl

application_name | walreceiver

client_addr      | 10.189.100.195

client_hostname  |

client_port      | 56072

backend_start    | 2017-06-13 08:10:35.400508-07

backend_xmin     |

state            | streaming

sent_location    | 7/EC01A588

write_location   | 7/EC01A588

flush_location   | 7/EC01A588

replay_location  | 7/EC01A588

sync_priority    | 0

sync_state       | async

edbstore=# SELECT pg_current_xlog_location();                      #在primary主库查看

 pg_current_xlog_location

--------------------------

 7/EC01A588

(1 row)

postgres=# select pg_last_xlog_receive_location(),pg_last_xlog_replay_location(),pg_last_xact_replay_timestamp();     #在standby备库查看

 pg_last_xlog_receive_location | pg_last_xlog_replay_location | pg_last_xact_replay_timestamp

-------------------------------+------------------------------+-------------------------------

 7/EC01A588                    | 7/EC01A588                   | 2017-06-13 08:25:20.281568-07

(1 row)

$ ps -ef | grep sender | grep -v grep        #在primary库查看

postgres     : ?        :: postgres: wal sender process repl 10.189.100.195() streaming /EC01A668

$  ps -ef | grep receiver | grep -v grep     #在standby库查看

postgres     : ?        :: postgres: wal receiver process   streaming /EC01A668

postgres=# select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication ;     #在primary库查看

 pg_xlog_location_diff

-----------------------

                     0

(1 row)

postgres=# select pg_current_xlog_location();

 pg_current_xlog_location

--------------------------

 7/EC021790

(1 row)

postgres=# select * from pg_xlogfile_name_offset('7/EC021790');

        file_name         | file_offset

--------------------------+-------------

 000000010000000700000076 |      137104

(1 row)

postgres=# select pg_is_in_recovery();            #在primary主库查看

 pg_is_in_recovery

-------------------

 f

(1 row)

postgres=# select pg_is_in_recovery();            #在standby库查看

 pg_is_in_recovery

-------------------

 t

(1 row)