ERROR: canceling statement due to conflict with recovery

ERROR: canceling statement due to conflict with recovery

报错现象

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

业务重现

1. 系统采用postgresql主备库数据库架构，主库用在微信小程序主要用在业务中产生的数据写入
2. 从库采用流复制的方式从主库不断更新备库的数据，备库所服务的应用是后台的查询统计web应用
3. 当在后台查询统计的时候，查询的是慢SQL，且查询用到的表正在更新数据的话，再查询时间较长的时候就会爆出上面的错误

问题原因

在主库上执行长时间查询过程中,由于此查询涉及的记录有可能在主库上被更新或删除,根据 PostgreSQL的mvcc机制,更新或删除的数据不是立即从物理块上删除,而是之后autovacuum进程对老版本数据进行 VACUUM,主库上对更新或删除数据的老版本进行 VACUUM后,从库上也会执行这个操作,从而与从库当前查询产生冲突,导致查询被中断并抛出以上错误。

解决方法

从业务层面上

从业务上可以优化慢SQL，数据表是不是太大了，是否需要分库分表，经常对更新频繁的表表分析和真空等。或者分时段查询，避免高峰段数据更新的时刻等

从数据库层面

修改maxstandby_ streaming_delay参数

此参数默认为30秒,当备库执行SQL时,有可能与正在应用的WAL发生冲突,此查询如果30秒没有执行完成则被中止,注意30秒不是备库上单个查询允许的最大执行时间,是指当备库上应用WAL时允许的最大WAL延迟应用时间,因此备库上查询的执行时间有可能不到这个参数设置的值就被中止了,此参数可以设置成-1,表示当从库上的WAL应用进程与从库上执行的查询冲突时,WAL应用进程一直等待直到从库查询执行完成。（我处理的时候是设置一个较大的值120，虽然偶尔还会报错，但是报错频率会少很多，建议后台处理的时候可以捕获这个异常，提示给查询用户，当前查询正忙请稍后再试）

修改hotstandby_feedback参数

默认情况下从库执行查询时并不会通知主库,设置此参数为on后从库执行查询时会通知主库,当从库执行查询过程中,主库不会清理从库需要的数据行老版本,因此,从库上的查询不会被中止,然而,这种方法也会带来一定的弊端,主库上的表可能出现膨胀,主库表的膨胀程度与表上的写事务和从库执行时间有关,此参数默认为off。（这种方式有风险，我没有测试过）

修改参数配置后需要重新加载一下数据库的配置文件