作者
digoal
日期
2019-09-14
标签
PostgreSQL , recovery_min_apply_delay , delay , 延迟
背景
政治正确非常重要，对于数据库来说亦如此，一个基于流复制的HA架构的集群，如果还有一堆只读节点，当HA集群发生了主备切换后，这些只读节点能否与新的主节点保持同步关系（类似与政治正确），很重要。
基于流复制的主从库模式，主库和从库都可以归档WAL日志。如果使用的是异步复制，主库和从库可能存在一定的WAL差异，如果从库激活，会启用新的时间线。
意味着老的时间线上的WAL并没有全部同步到新的主库新的时间线上。归档也会存在差异。
如何配置PITR恢复，才不会走老路，一直紧跟新的时间线呢？
取决于PITR时，在走错WAL前，是否先发现新时间线的HISTORY文件。
之前写过一篇文档
通过配置restore_command来避免主备切换的时间线分歧问题。
那么还有没有其他办法呢？
wal sender和wal receiver协调 + recovery_min_apply_delay
standby 延迟apply，当上游主从发生切换时，一定会涉及到wal receiver 和 wal sender的重新建立连接。
当前的流程：standby把最后一次接收到的wal 位置告诉上游，上游从这个位点开始发送日志给下游。所以虽然standby的apply wal位点很旧，但是send wal已经很新（超过了新的主库）时，一样会有时间线错乱，导致无法跟随新主库的问题。
改进：
可以在wal receiver 和 新主库wal sender重新建立连接时，上下游进行协调，新主库把激活时间线发给下游（standby），如果standby的wal 位点超过了激活位点，说明发生了分歧。那么此时要做的是，standby从时间线开始重新接收上游（新primary）的日志以及history文件。从而解决这个问题。
涉及参数
recovery_min_apply_delay (integer)
By default, a standby server restores WAL records from the sending server as soon as possible. It may be useful to have a time-delayed copy of the data, offering opportunities to correct data loss errors. This parameter allows you to delay recovery by a fixed period of time, measured in milliseconds if no unit is specified. For example, if you set this parameter to 5min, 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 time stamp as written on master and the current time on the standby. Delays in transfer because of network lag or cascading replication configurations may reduce the actual wait time significantly. If the system clocks on master and standby are not synchronized, this may lead to recovery applying records earlier than expected; but that is not a major issue because useful settings of this parameter are much larger than typical time deviations between servers.
The delay occurs only on WAL records for transaction commits. Other records are replayed as quickly as possible, which is not a problem because MVCC visibility rules ensure their effects are not visible until the corresponding commit record is applied.
The delay occurs once the database in recovery has reached a consistent state, 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 honored in all cases. hot_standby_feedback will be delayed by use of this feature which could lead to bloat on the master; use both together with care.
Warning
Synchronous replication is affected by this setting when synchronous_commit is set to remote_apply; every COMMIT will need to wait to be applied.
This parameter can only be set in the postgresql.conf file or on the server command line.
例子
1、HA (host1 primary , host2 standby 1) ,
vip(on host1 primary) ,
host3 standby 2 recovery_min_apply_delay=5 min. 尽可能的避免host3 standby 2 apply的wal位点比host2 standby 1接收的wal位点还大。
2、
host1 primary WAL LSN : 100000
host2 standby 1 WAL LSN : 95000
host3 standby 2 WAL LSN : receive 98000 , apply 90000 (<95000)
3、主从切换
host1 primary WAL LSN : 100000 failed
host2 standby 1 WAL LSN : 95000 promote to new primary , TL = old TL+1
vip(on host2 new primary) ,
4、host3 standby 2 wal receiver process reconnect to host2 new primary wal sender process. （一旦需要重新建立，standby 2 关闭apply进程，避免apply过头）
host2 new primary wal sender process 发送 所有时间线文件给 host3 standby 2 wal receiver process
host3 standby 2 wal receiver process 发现新主库的时间线(大于当前host3 standby 2 old TL)，同时 host2 new primary 激活时的 wal lsn位点95000 小于 host3 standby 2 WAL LSN : receive 98000
5、host3 standby 2 wal receiver process 从 host2 new primary 激活时的 wal lsn位点95000 开始接收wal，以及对应的history文件，覆盖超前的部分。
6、host3 standby 2 重新启动apply进程，确保可以跟上新的主库。
参考
《PostgreSQL 使用逻辑decode实现异步主从切换后，时间线分歧变化量补齐、修复》
《PostgreSQL pg_rewind，时间线修复，脑裂修复，flashback - 从库开启读写后，回退为只读从库。异步主从发生角色切换后，主库rewind为新主库的从库》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
