Postgresql 查询报错ERROR: canceling statement due to conflict with recovery

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

场景描述

这里我们是流复制环境,仓库从现网的备节点抽取数据导致以上报错。
原理很简单,就是主节点的相关表做了DDL或者DML,wal在备节点回放的时候,备节点的查询还在进行,如此就会发生冲突,干掉备节点查询的语句,报出以上错误。

下图简单演示了报错原理,这里是由DML中的UPDATE引起的冲突,行版本不一致造成相关的错误。
在这里插入图片描述

上图可见一个参数max_standby_streaming_delay默认为30秒,也就是说vacuum的时候,相关需要清理的数据仍有事物在使用,则认为有冲突,等待这个参数设置的值,也就是30秒,30秒后则中断连接,报出以上错误。所以这里可能是DDL,比如drop table,truncate table,drop database,drop tablespace等等,也可能是DML,delete,update等等语句都会引起错误发生。

所以这里分为两类处理方式:处理DDL和处理DML

  1. 要避免DDL发生,那么需要设置参数max_standby_streaming_delay为-1,这样就会一直等待查询的语句结束为止。如果是从归档中回放产生的冲突,那就max_standby_archive_delay设置为-1。
  2. 要避免DML,也就是VACUUM产生的冲突,当然以上的方法也是可用的,这里引入另外一个参数hot_standby_feedback,可以打开该参数(设置为on)避免。

hot_standby_feedback 的原理很简单,就是将备节点的最小活跃事务 ID 定期告知主节点,使得主节点在执行 VACUUM 时对这些事务还需要的数据手下留情。

设置 hot_standby_feedback 的好处是可以减少备节点执行查询时复制冲突的可能,但也有其弊端,即会使主节点先关表的数据延迟回收,从而导致数据膨胀;极端情况下,如果 备节点有一个很长的事务,且涉及表上 DML 操作又很频繁,则表的膨胀则会很严重。

另外也可以考虑设置vacuum_defer_cleanup_age参数,这个参数可以延迟清理dead row,使得在备节点的查询可以读取这些dead row,默认为0,这个参数是在主节点上设置,也是由主节点度量的,所以这个值很难确定设置多少为有效,如果主节点事物数增长很快,那么很快dead row就会被清理,对于备节点的查询冲突没什么效果,所以以上引发的错误,我个人不建议设置该参数解决。

总结:

开启max_standby_streaming_delay或者max_standby_archive_delay也有弊端,如果备节点有长事物,那么,备节点会长时间跟不上主节点,主备节点的数据无法一致,如果有多个级联的备节点,那后果更是不堪设想。

如果这只hot_standby_feedback,那么可能会导致相关表膨胀,如果业务频繁查询,更新,删除,那么这些膨胀的表DML处理肯定效率低下,将会影响业务响应。

另外vacuum_defer_cleanup_age觉的效果不好,主要是在主节点度量这个值,所以不建议设置,这里就不做更深的研究探讨。

参考:
https://www.postgresql.org/docs/11/hot-standby.html
26.5.2. Handling Query Conflicts章节

### PostgreSQL 报错 `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的流复制环境中,主服务器会将更改记录写入WAL(Write-Ahead Logging),并将其发送给备用服务器用于同步更新。然而,在特定情况下,特别是长时间运行的事务或者频繁读取历史数据的操作可能会阻碍必要的清理工作,从而引发上述冲突警告[^2]。 对于此类问题的根本原因是由于备机上的查询试图获取已被标记为可回收的数据版本,而这部分数据正处于移除过程中,因此无法满足查询需求而导致操作失败[^3]。 ### 解决方案建议 针对这一类由恢复冲突引起的报错情况,有几种方法可以帮助缓解或解决问题: #### 方法一:调整应用逻辑减少长事务 优化应用程序的设计模式来缩短事务周期长度,避免不必要的长时间持有锁资源。确保所有业务流程尽可能高效地处理完毕后再提交事务,这样可以降低与其他后台进程发生竞争的可能性。 #### 方法二:设置合适的参数控制最大保留期 可以通过修改postgresql.conf配置文件内的几个重要参数来管理wal_keep_segments、max_standby_streaming_delay等选项,适当增加允许的最大延迟时间和保存更多的预写日志段数,以便给予更多的时间窗口让备库赶上主库的变化进度而不至于因为过早清除所需的历史信息造成查询中断[^4]。 ```sql -- 修改 postgresql.conf 文件中的相关参数 wal_keep_segments = 64 -- 单位为MB,默认值通常是较低的数值 max_standby_streaming_delay = '30s' -- 设置合理的等待超时时长 ``` #### 方法三:重启备库实例 如果条件允许的话,考虑定期重启从属副本实例,使得其能够快速追平上游源端点的状态变化,进而减小两者间差异带来的潜在风险。 以上措施均有助于改善因恢复期间产生的并发访问矛盾状况,提高系统的稳定性和可用性水平。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值