第三步：主节点创建流复制槽

SELECT * FROM pg_create_physical_replication_slot('node_slot_23');

SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+-------------
slot_name           | node_slot_23
plugin              | 
slot_type           | physical
datoid              | 
database            | 
active              | f
active_pid          | 
xmin                | 
catalog_xmin        | 
restart_lsn         | 
confirmed_flush_lsn |

5.2 配置流复制参数

sed -ir "s/#*hot_standby.*/hot_standby= on/" $PGDATA/postgresql.conf

cp $PGHOME/share/recovery.conf.sample ./recovery.conf
sed -ir "s/#*standby_mode.*/standby_mode= on/" $PGDATA/recovery.conf
sed -ir "s/#*primary_conninfo.*/primary_conninfo= 'host=127.0.0.1 port=9420 user=foo password=server@123'/" $PGDATA/recovery.conf
sed -ir "s/#*primary_slot_name.*/primary_slot_name= 'node_slot_23'/" $PGDATA/recovery.conf

日志信息

LOG:  entering standby mode
LOG:  redo starts at 0/9000028
LOG:  consistent recovery state reached at 0/A000060
LOG:  invalid record length at 0/A000060: wanted 24,got 0
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 0/A000000 on timeline 1

进程状态

/home/gaomingjie/app/pgsql23/bin/postgres
\_ postgres: startup process   recovering 00000001000000000000000A
\_ postgres: checkpointer process           
\_ postgres: writer process                 
\_ postgres: stats collector process        
\_ postgres: wal receiver process

主节点查询流复制槽状态

psql
psql (9.6.0)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+-------------
slot_name           | node_slot_23
plugin              | 
slot_type           | physical
datoid              | 
database            | 
active              | t
active_pid          | 14799
xmin                | 
catalog_xmin        | 
restart_lsn         | 0/B001148
confirmed_flush_lsn |

psql -U foo -W "dbname=postgres replication=database" 
Password for user foo: 
psql (9.6.0)
Type "help" for help.

postgres=> IDENTIFY_SYSTEM;
      systemid       | timeline | xlogpos | dbname 
---------------------+----------+-----------+----------
 6413518490021561706 | 1 | 0/B001148 | postgres
(1 row)

5.3 流复制槽概念

　　复制槽提供了一种自动化的方法来确保主控机在所有的后备机收到 WAL 段 之前不会移除它们，并且主控机也不会移除可能导致 恢复冲突的行，即使后备机断开也是如此。
　　作 为 复 制 槽 的 替 代 ， 也 可 以 使 用wal_keep_segments 阻 止 移 除 旧 的 WAL 段 ，或 者 使用archive_command 把段保存在一个归档中。不过，这些方法常常会导致保留的 WAL 段比需要的 更多，而复制槽只保留已知所需要的段。这些方法的一个优点是它们为 pg_xlog的空间需求提供了界限，但目前使用复制槽无法做到。
　　类似地，hot_standby和 vacuum_defer_cleanup_age保护了相关行不被 vacuum 移除，但是前者在后备机断开期间无法提供保护，而后者则需要被设置为一个很高 的值已提供足够的保护。复制槽克服了这些缺点。

5.3.1 查询和操纵复制槽

　　每个复制槽都有一个名字，名字可以包含小写字母、数字和下划线字符。已有的复制槽和它们的状态可以在 pg_replication_slots 视图中看到。

流复制槽相关函数

pg_create_physical_replication_slot
pg_drop_replication_slot
pg_create_logical_replication_slot
pg_logical_slot_get_changes
pg_logical_slot_peek_changes
pg_logical_slot_get_binary_changes
pg_logical_slot_peek_binary_changes
pg_replication_origin_create
pg_replication_origin_drop
pg_replication_origin_oid
pg_replication_origin_session_setup
pg_replication_origin_session_reset
pg_replication_origin_session_is_setup
pg_replication_origin_session_progress
pg_replication_origin_xact_setup
pg_replication_origin_xact_reset
pg_replication_origin_advance
pg_replication_origin_progress
pg_logical_emit_message

6 级联备服务器配置

（ID24）

6.1 制作基础备份（主节点23操作）

pg_basebackup -U foo -W -Fp -P -x -D ~/app/data/pg_root24 -l basebackup24@H_875_301@

sed -ir "s/#*standby_mode.*/standby_mode= on/" $PGDATA/recovery.conf
sed -ir "s/#*primary_conninfo.*/primary_conninfo= 'host=127.0.0.1 port=9423 user=foo password=server@123'/" $PGDATA/recovery.conf

LOG:  database system was interrupted while in recovery at log time 2017-04-28 11:13:33 CST
HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG:  entering standby mode
LOG:  redo starts at 0/B00D958
LOG:  consistent recovery state reached at 0/B00DA38
LOG:  invalid record length at 0/B00DA38: wanted 24,got 0
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 0/B000000 on timeline 1

/home/gaomingjie/app/pgsql23/bin/postgres
\_ postgres: startup process   recovering 00000001000000000000000B
\_ postgres: checkpointer process           
\_ postgres: writer process                 
\_ postgres: stats collector process        
\_ postgres: wal receiver process   streaming 0/B00DBF8
\_ postgres: wal sender process foo 127.0.0.1(58019) streaming 0/B00DBF8

/home/gaomingjie/app/pgsql24/bin/postgres
\_ postgres: startup process   recovering 00000001000000000000000B
\_ postgres: checkpointer process           
\_ postgres: writer process                 
\_ postgres: stats collector process        
\_ postgres: wal receiver process   streaming 0/B00DBF8

create role foo login replication password 'server@123';

pg_hba.conf:

host    replication     foo               127.0.0.1/32            md5

sed -ir "s/#*synchronous_standby_names.*/synchronous_standby_names= '1 (s1)'/" $PGDATA/postgresql.conf

sed -ir "s/#*hot_standby.*/hot_standby= on/" $PGDATA/postgresql.conf

cp $PGHOME/share/recovery.conf.sample ./recovery.conf
sed -ir "s/#*standby_mode.*/standby_mode= on/" $PGDATA/recovery.conf
sed -ir "s/#*primary_conninfo.*/primary_conninfo= 'application_name=s1 host=127.0.0.1 port=9420 user=foo password=server@123'/" $PGDATA/recovery.conf

sed -ir "s/#*archive_mode.*/archive_mode = always/" $PGDATA/postgresql.conf
sed -ir "s/#*archive_command.*/archive_command = 'test ! -f \${PGHOME}\/archive\/%f \&\& cp %p \${PGHOME}\/archive\/%f'/" $PGDATA/postgresql.conf

LOG:  standby "s1" is now a synchronous standby with priority 1

postgres=# select * from pg_stat_replication where application_name='s1';
-[ RECORD 1 ]----+------------------------------
pid              | 23543
usesysid         | 16393
usename          | foo
application_name | s1
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 48481
backend_start    | 2017-04-28 14:45:03.051153+08
backend_xmin     | 
state            | streaming
sent_location    | 0/E0000D0
write_location   | 0/E0000D0
flush_location   | 0/E0000D0
replay_location  | 0/E0000D0
sync_priority    | 1
sync_state       | sync