pg_receivewal实践

测试从pg_receivewal的日志中恢复从库为主库：

 

主从配置async模式，配置pg_receivewal接收日志pg_receivewal -D /dbaas/pg/data/pg_receivewal_data -v -h 10.9.10.202

主插入1000万数据，当插入一半时，停止从库

主库插完数据，停止

将pg_receivewal的日志拷贝到从库/dbaas/pg/data/pg_receivewal_data下

 

修改从库recovery.conf文件，添加内容：

restore_command = 'cp /dbaas/pg/data/pg_receivewal_data/%f "%p"'

 

启动数据库，数据库成功恢复到10000000行。

 

 

另外一种用法：

restore_command = 'cp /dbaas/pg/data/pg_receivewal_data/%f "%p"'

standby_mode = on

primary_conninfo = 'host=10.9.10.203 port=5432 user=postgres connect_timeout=60'

recovery_target_timeline = 'latest'

trigger_file = '/dbaas/pg/data/.tfile'

这种把恢复和备库设置放一起，会先去从归档恢复，当恢复完成后，直接建立流复制关系，这样就相当方便了，当有归档时，不用重做从库。

 

测试pg_receivewal的同步机制：

 

-S slotname

--slot=slotname

Require pg_receivewal to use an existing replication slot (see Section 26.2.6). When this option is used, pg_receivewal will report a flush position to the server, indicating when each segment has been synchronized to disk so that the server can remove that segment if it is not otherwise needed.

 

When the replication client of pg_receivewal is configured on the server as a synchronous standby, then using a replication slot will report the flush position to the server, but only when a WAL file is closed. Therefore, that configuration will cause transactions on the primary to wait for a long time and effectively not work satisfactorily. The option --synchronous (see below) must be specified in addition to make this work correctly.

--synchronous

Flush the WAL data to disk immediately after it has been received. Also send a status packet back to the server immediately after flushing, regardless of --status-interval.

This option should be specified if the replication client of pg_receivewal is configured on the server as a synchronous standby, to ensure that timely feedback is sent to the server.

 

在这个说明中，使用了--slot=for_pgreceivewal --synchronous后，理论上是有点像sync同步一样，数据库会等待接收端进行回放，回放结束，再进行commit。

 

 

1）使slot和synchronous参数：

数据库端：

修改postgresql.conf参数synchronous_standby_name=‘pg_receivewal’

重启数据库

 

postgres=# select pg_create_physical_replication_slot('for_pgreceivewal');

 pg_create_physical_replication_slot

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

 (for_pgreceivewal,)

(1 row)

 

postgres=# select pg_get_replication_slots();

        pg_get_replication_slots

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

 (for_pgreceivewal,,physical,,f,f,,,,,)

(1 row)

 

postgres=# select pg_get_replication_slots();

                pg_get_replication_slots

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