postgresql 备库terminating connection due to conflict with recovery的报错

一、背景

terminating connection due to conflict with recovery

使用只读库时, 查询时偶尔可能会遇到这种报错. 具体是什么原因造成的?

二、分析

1、查询冲突次数

xxxx=> select * from pg_stat_database_conflicts ;

confl_snapshot 分析

standby有个wal replay进程在进行wal回放, 如果回放过程中有query和回放的内容发生了冲突, 那么wal回放会进行等待, 等多久呢? 最久决定于max_standby_streaming_delay参数.

当等待超过这个时间时, wal replay会cancel与之有冲突的所有query, 然后开始恢复, 并且必须要恢复到wal receive的位置和wal replay位置一致状态后才会重置max_standby_streaming_delay等待时间, 没有到达这个状态前, 所有与wal replay有冲突的query都会被直接cancel(不再有query执行窗口), 所以cancel query可能会很频繁的出现.

找到 standby节点的big query(甚至也可能是有snapshot的idle transaction), 分析big query是否可以优化变成small query。

三、解决方案

解决方案. 建议采用1、5, 副作用最小:

1、ro节点设置statement_timeout, 小于max_standby_streaming_delay, 不要让一个query堵塞一堆query。
副作用:
- 某些query可能会超时报错.

使用例子:

``` statement_timeout=5s lock_timeout=10s

psql postgresql://localhost:1921/postgres?options="-c statement_timeout%3D5s -c lock_timeout%3D10s" ```

2、区分大查询和小查询, 分开使用不同standby节点
如果有大查询, 和小查询分开, 不要放在同一台ro节点查询.
如果有大查询, 特别是人工发起的大查询, 不要和小查询混到一个ro节点查询.

3、设置更大的max_standby_streaming_delay参数, 让big query有更长的执行时间.
副作用:
- replay delay会变长, 从库的查询延迟变大.

4、修改ro节点参数hot_standby_feedback=on, 只要ro库有查询, 就会返回snapshot xid给主库, 主库的垃圾回收进程不会回收这个垃圾.
副作用:
- 当从库有big(long) query时, 主库IO暴增, 垃圾回收进程空转, 显示为表有垃圾, autovacuum发起扫描表垃圾, 但是回收不掉(因为从库依赖这些垃圾版本), 导致大量无用IO.
- 当从库有big(long) query时, 主库表膨胀, 因为某些时刻更多的垃圾无法被及时回收, 导致膨胀.

5、如果是standby本身资源问题导致delay, 那么建议查看standby节点的网络贷款、cpu、io能力是否存在瓶颈. 该加资源就加资源。

参考：PostgreSQL standby conflict replay分析和解决方案 - 墨天轮