问题描述

有些业务场景，为了分担主库的访问压力，备库（hot_standby=on）常常需要对外提供只读服务。此时备库既要接受并重放通过流协议传过来的wal数据，同时也要对外提供只读查询服务，这两个任务同时进行，有时候会产生冲突。

通常情况下，备库如果有有个wal，replay进程在进行wal回放, 回放过程中有query和回放的内容发生了冲突，（通常是备库的慢查询SQL导致）。 那么wal回放会进行等待，等待时间取决于max_standby_streaming_delay参数。

当等待超过这个时间, wal的replay会cancel与之有冲突的所有query,然后开始recovery, 并且必须要恢复到wal receive的位置和wal replay位置一致状态后才会重置max_standby_streaming_delay等待时间, 没有到达这个状态前, 所有与wal replay有冲突的query都会被直接cancel掉。数据库日志里可能有连续的cancel记录，显示以下错误消息：

ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

或者当备库上的事务正在读取主库上删除的元组时，可能会发生查询冲突。删除元组，然后在主库上触发vacuum，备库的一个wal replay进程在进行wal回放，正好和仍在备库上运行的该表的SELECT查询发生冲突（一般情况下是备库的慢查询）。在这种情况下，对备库上的SELECT查询将终止。

我们可以通过pg_stat_database_conflicts这个视图查看数据库冲突状态的统计信息。一般这种时候大多都是confl_snapshot快照的冲突。

解决方案

一、参数调整方向（如下参数可以根据需求选择一种方法做调整）

1.只读节点设置statement_timeout, 让其小于max_standby_streaming_delay, 不让一个query堵塞一堆query。

But：可能某些query可能会超时报错。

2.根据备库的查询时长适当增加参数max_standby_streaming_delay，该参数表示备库从主实例接收到数据后，允许应用wal日志的总时间。给长查询留出更多时间，超时后再取消与即将应用的WAL日志冲突的备库的查询语句。

But：replay的delay会变长, 从库的查询延迟变大，增大后可能导致备机的数据查询到的不是最新的。

3.打开hot_standby_feedback参数，该参数表示备库告诉主库它的查询需要哪些版本，让主库保留，备库查询始终能拿到需要的版本。只要备库有查询, 就会返回snapshot xid给主库, 主库的垃圾回收进程不会回收这个垃圾。不阻塞apply wal日志。

注：配置hot_standby_feedback参数后，wal_receiver_status_interval参数可根据情况做适当调整。wal_receiver_status_interval参数是设置WAL日志接收进程的状态通知给主机的最大时间间隔。默认是5s。（当该参数设置为0时，表示关闭备机向主机反馈日志接收位置等信息，可能会导致主机事务提交阻塞、切换操作失败等异常现象。正常业务场景，不建议将该参数设置为0。）

But：开启后，备库有长查询可能造成表的数据膨胀，同时也可能主库IO暴增, 垃圾回收进程空转, 显示为表有垃圾, autovacuum发起扫描表垃圾, 但是回收不掉(因为从库依赖这些垃圾版本), 导致大量无用IO。

4.设置vacuum_defer_cleanup_age参数，延迟清理dead row。

But：不太建议使用,不好控制有效值。

二、优化sql

可通过执行计划查找sql变慢，执行效率差的原因，并修复。

三、提升硬件配置

查看服务器硬件资源使用情况，并做适当优化及调整，可能是备机服务器压力过大，处理慢。

四、业务优化方向

建议备库只读节点，减少较长查询，可以把小查询放在备库。允许的话，也可以分散查询到其他只读节点。