postgresql从库查询被终止怎么办

一，问题描述：

PG流复制场景下，默认配置下， 如果在PG从库执行长时间的查询，会出现查询的报错。提示

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

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

实际上 PostgreSQL提供了配置参数来减少或避免这种情况出现的概率,主要包括以下两个参数:

maxstandby_ streaming_delay:

此参数默认为30秒,当备库执行SQL时,有可能与正在应用的WAL发生冲突,此查询如果30秒没有执行完成则被中止,注意30秒不是备库上单个查询允许的最大执行时间,是指当备库上应用WAL时允许的最大WAL延迟应用时间,因此备库上查询的执行时间有可能不到这个参数设置的值就被中止了,此参数可以设置成-1,表示当从库上的WAL应用进程与从库上执行的查询冲突时,WAL应用进程一直等待直到从库查询执行完成。

hotstandby_feedback:

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

二，故障模拟：

环境准备：

CentOS7.5+PG版本11.5
pgMaster 为主库
pgSlave 为备库

调整备库的参数，设置
max_standby_streaming_delay = 10s  # (测试便于看出效果这个参数调的比较低)
hot_standby_feedback = off
然后reload下PG的配置使其生效

在主库pgMaster 上创建测试表：

\c postgres
create table test_per2 ( id int , flag int);
insert into test_per2 (id) select * from generate_series(1,1000000) ;

编写pgbench压测脚本 update_per2.sql 内容如下：

\set v_id random(1,1000000)
update test_per2 set flag='1' where id=:v_id;

开始压测：

pgbench -c 8 -T 120 -d postgres -Upostgres -n N -M prepared -f update_per2.sql

然后，到pgSlave备库去执行下查询操作：

postgres=# select pg_sleep(12),* from test_per2 limit 10 ;
ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Time: 729.120 ms

这里，可以很容易就复现了这个报错场景。

三，解决方案：

方案1、 调大 max_standby_streaming_delay 参数值

我们可以将max_standby_streaming_delay 调整为-1 绕开这个错误，或者将这个值调大些。

例如将备库的参数max_standby_streaming_delay调整为120s：

max_standby_streaming_delay = 120s
hot_standby_feedback = off
使用 pg_ctl reload 使其生效
再次到pgSlave备库去执行下查询操作，可以看到查询可以正常执行了：

postgres=# select pg_sleep(12), id ,flag from test_per2 limit 2 ;
 pg_sleep | id | flag
----------+----+------
 | 1 | NULL
 | 2 | NULL
(2 rows) 

方案2、 开启 hot_standby_feedback 参数

hot_standby_feedback 参数设置为on后，从库执行查询时会通知主库，从库执行大查询过程中，主库不会清理从库需要用到的数据行老版本。

备库上需要开启的参数：
max_standby_streaming_delay = 10s
hot_standby_feedback = on # 主要是这个参数设置为on即可
使用 pg_ctl reload 使其生效


这时候，到备库去查询，可以发现能查询成功：
postgres=# select pg_sleep(2), id ,flag from test_per2 limit 2 ;
 pg_sleep | id | flag
----------+----+------
 | 1 | NULL
 | 2 | NULL
(2 rows)

postgres=# select pg_sleep(12), id ,flag from test_per2 limit 2 ;
 pg_sleep | id | flag
----------+----+------
 | 1 | NULL
 | 2 | NULL
(2 rows)

四：说明

上面的2种方式中，都是有不太好的地方：

1、 设置 max_standby_streaming_delay 参数为-1，这种方式有可能备库上慢查询由于长时间执行而消耗大量主机资源，建议根据应用情况设置一个较合理的值

2、 设置 hot_standby_feedback=on，这种方式可能会使主库某些表产生膨胀。

这两种方式无论选择哪一个都应该加强对流复制主库、备库慢查询的监控，并分析是否需要人工介入维护。

原文地址：https://www.yisu.com/zixun/16906.html