postgresql從庫查詢被終止怎么辦

這篇文章主要介紹了postgresql從庫查詢被終止怎么辦，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

PG流復(fù)制場景下，默認(rèn)配置下， 如果在PG從庫執(zhí)行長時間的查詢，會出現(xiàn)查詢的報(bào)錯。提示

ERROR: canceling statement due to conflict with recovery

DETAIL: User query might have needed to see row versions that must be removed.

根據(jù)報(bào)錯信息,在主庫上執(zhí)行長時間查詢過程中,由于此查詢涉及的記錄有可能在主庫上被更新或刪除,根據(jù) PostgreSQL的mvcc機(jī)制,更新或刪除的數(shù)據(jù)不是立即從物理塊上刪除,而是之后autovacuum進(jìn)程對老版本數(shù)據(jù)進(jìn)行 VACUUM,主庫上對更新或刪除數(shù)據(jù)的老版本進(jìn)行 VACUUM后,從庫上也會執(zhí)行這個操作,從而與從庫當(dāng)前查詢產(chǎn)生沖突,導(dǎo)致查詢被中斷并拋出以上錯誤。

實(shí)際上 PostgreSQL提供了配置參數(shù)來減少或避免這種情況出現(xiàn)的概率,主要包括以下兩個參數(shù):

maxstandby_ streaming_delay:

此參數(shù)默認(rèn)為30秒,當(dāng)備庫執(zhí)行SQL時,有可能與正在應(yīng)用的WAL發(fā)生沖突,此查詢?nèi)绻?0秒沒有執(zhí)行完成則被中止,注意30秒不是備庫上單個查詢允許的最大執(zhí)行時間,是指當(dāng)備庫上應(yīng)用WAL時允許的最大WAL延遲應(yīng)用時間,因此備庫上查詢的執(zhí)行時間有可能不到這個參數(shù)設(shè)置的值就被中止了,此參數(shù)可以設(shè)置成-1,表示當(dāng)從庫上的WAL應(yīng)用進(jìn)程與從庫上執(zhí)行的查詢沖突時,WAL應(yīng)用進(jìn)程一直等待直到從庫查詢執(zhí)行完成。

hotstandby_feedback:

默認(rèn)情況下從庫執(zhí)行查詢時并不會通知主庫,設(shè)置此參數(shù)為on后從庫執(zhí)行查詢時會通知主庫,當(dāng)從庫執(zhí)行查詢過程中,主庫不會清理從庫需要的數(shù)據(jù)行老版本,因此,從庫上的查詢不會被中止,然而,這種方法也會帶來一定的弊端,主庫上的表可能出現(xiàn)膨脹,主庫表的膨脹程度與表上的寫事務(wù)和從庫執(zhí)行時間有關(guān),此參數(shù)默認(rèn)為off

案例：

CentOS7.5+PG版本11.5

pgMaster 為主庫

pgSlave 為備庫

調(diào)整備庫的參數(shù)，設(shè)置

max_standby_streaming_delay = 10s    # (測試便于看出效果這個參數(shù)調(diào)的比較低)

hot_standby_feedback = off

然后reload下PG的配置使其生效

在主庫pgMaster 上創(chuàng)建測試表：

\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 內(nèi)容如下：

\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備庫去執(zhí)行下查詢操作：

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

這里，可以很容易就復(fù)現(xiàn)了這個報(bào)錯場景。

解決方法有2種：

方案1、  調(diào)大 max_standby_streaming_delay 參數(shù)值

我們可以將max_standby_streaming_delay 調(diào)整為-1 繞開這個錯誤，或者將這個值調(diào)大些。

例如將備庫的參數(shù)max_standby_streaming_delay調(diào)整為120s：

max_standby_streaming_delay = 120s

hot_standby_feedback = off

然后 使用 pg_ctl reload 使其生效

然后，再次到pgSlave備庫去執(zhí)行下查詢操作，可以看到查詢可以正常執(zhí)行了：

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 參數(shù)

hot_standby_feedback 參數(shù)設(shè)置為on后，從庫執(zhí)行查詢時會通知主庫，從庫執(zhí)行大查詢過程中，主庫不會清理從庫需要用到的數(shù)據(jù)行老版本。

備庫上需要開啟的參數(shù)：

max_standby_streaming_delay = 10s

hot_standby_feedback = on  # 主要是這個參數(shù)設(shè)置為on即可

然后 使用 pg_ctl reload 使其生效

這時候，到備庫去查詢，可以發(fā)現(xiàn)能查詢成功：

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、  設(shè)置 max_standby_streaming_delay 參數(shù)為-1，這種方式有可能備庫上慢查詢由于長時間執(zhí)行而消耗大量主機(jī)資源，建議根據(jù)應(yīng)用情況設(shè)置一個較合理的值

2、  設(shè)置 hot_standby_feedback=on，這種方式可能會使主庫某些表產(chǎn)生膨脹。

這兩種方式無論選擇哪一個都應(yīng)該加強(qiáng)對流復(fù)制主庫、備庫慢查詢的監(jiān)控，并分析是否需要人工介入維護(hù)。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“postgresql從庫查詢被終止怎么辦”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!