PostgreSQL 9.4 支持standby apply xlog有一个时间延迟的配置, 即使XLOG源源不断的从master节点接收过来, standby可以判断一下当前系统时间和xlog rec上的时间戳, 只有xlog rec是比系统时间早过配置的时间的XLOG才允许apply, 否则就暂停apply,
该配置名为min_recovery_apply_delay. 在standby节点的recovery.conf中配置.
例如配置了1个小时, 那么standby节点会接收xlog, 但是apply会在系统时间的前1个小时.
这样可以允许standby弥补一些问题, 例如主库发生了一次误操作, 这次误操作如果在1小时内发现的话, standby其实就是误操作以前的数据库状态.
min_recovery_apply_delay (integer)
By default, a standby server keeps restoring WAL records from the primary as soon as possible. It may be useful to have a time-delayed copy of the data, offering various options to correct data loss errors. This parameter allows you to delay recovery by a fixed period of time, specified in milliseconds if no unit is specified. For example, if you set this parameter to5min, the standby will replay each transaction commit only when the system time on the standby is at least five minutes past the commit time reported by the master.
It is possible that the replication delay between servers exceeds the value of this parameter, in which case no delay is added. Note that the delay is calculated between the WAL timestamp as written on master and the time on the current standby. Delays in transfer because of networks or cascading replication configurations may reduce the actual wait time significantly. If the system clocks on master and standby are not synchronised, this may lead to recovery applying records earlier than expected but is not a major issue because the useful settings of the parameter are much larger than typical time deviation between the servers. Be careful to allow for different timezone settings on master and standby.
The delay occurs only on WAL records for COMMIT and Restore Points. Other records may be replayed earlier than the specified delay, which is not an issue for MVCC though may potentially increase the number of recovery conflicts generated.
The delay occurs until the standby is promoted or triggered. After that the standby will end recovery without further waiting.
This parameter is intended for use with streaming replication deployments, however, if the parameter is specified it will be honoured in all cases. Synchronous replication is not affected by this setting because there is not yet any setting to request synchronous apply of transaction commits. hot_standby_feedback will be delayed by use of this feature which could lead to bloat on the master; use both together with care.
使用注意事项,
1. 主库和standby节点的时间做好同步, 尽量使用同样的时间服务器.
2. 不要和hot_standby_feedback同时使用. 因为如果配置了feedback的话, 主节点的vacuum会根据feedback来判断是否要回收某些垃圾. 降低conflict
2. http://www.postgresql.org/docs/devel/static/archive-recovery-settings.html