postgresql从库查询被终止怎么办

介绍

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

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

错误:取消声明由于冲突与复苏

细节:用户查询可能需要看到行版本,必须删除。

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

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

<强> maxstandby_ streaming_delay:

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

<强> hotstandby_feedback:

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

<强>案例:

CentOS7.5 + PG版

11.5本pgMaster为主库

pgSlave为备库

调整备库的参数,设置

max_standby_streaming_delay=10 s #才能(测试便于看出效果这个参数调的比较低)

hot_standby_feedback=了

然后重新加载下PG的配置使其生效

在主库pgMaster上创建测试表:

\ c postgres

create table test_per2 (int id、旗int);

插入test_per2 (id) select * from generate_series (1000000),

编写pgbench压测脚本update_per2。sql内容如下:

\设置v_id随机(1 1000000)

更新test_per2设置标志=& # 39;1 & # 39;id=: v_id;

开始压测:

pgbench - c 120 - d - t postgres -Upostgres - N N - m - f update_per2做好准备。sql

然后,到pgSlave备库去执行下查询操作:

postgres=#选择pg_sleep(12) *从test_per2限制10;

错误:,取消声明由于冲突与复苏

细节:,用户查询可能需要看到行版本,必须删除。

时间:729.120毫秒

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

<强>解决方法有 <强> 2 <强>种:

<强>方案1,, <强>调大max_standby_streaming_delay <强>参数值

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

例如将备库的参数max_standby_streaming_delay调整为120年代:

max_standby_streaming_delay=120年代

hot_standby_feedback=了

然后使用pg_ctl重载使其生效

然后,再次到pgSlave备库去执行下查询操作,可以看到查询可以正常执行了:

postgres=#选择pg_sleep (12)、id、flag 从test_per2 限制2;

, pg_sleep | | id标记

- - - - - - - - - - - + - - - - - - - - - - -

,,,,,,,,,|,1 |空

,,,,,,,,,|,2 |空

(2行),

<强>方案2,, <强>开启hot_standby_feedback <强>参数

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

备库上需要开启的参数:

max_standby_streaming_delay=10 s

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

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

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

postgresql从库查询被终止怎么办