postgresql从库查询被终止怎么办

共计 2674 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章主要介绍了 postgresql 从库查询被终止怎么办，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

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

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

解决方法有 2 种：

方案 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，这种方式可能会使主库某些表产生膨胀。

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

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“postgresql 从库查询被终止怎么办”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!