共計(jì) 2674 個(gè)字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。
這篇文章主要介紹了 postgresql 從庫查詢被終止怎么辦，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。
PG 流復(fù)制場景下，默認(rèn)配置下，如果在 PG 從庫執(zhí)行長時(shí)間的查詢，會(huì)出現(xiàn)查詢的報(bào)錯(cuò)。提示
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)錯(cuò)信息, 在主庫上執(zhí)行長時(shí)間查詢過程中, 由于此查詢涉及的記錄有可能在主庫上被更新或刪除, 根據(jù) PostgreSQL 的 mvcc 機(jī)制, 更新或刪除的數(shù)據(jù)不是立即從物理塊上刪除, 而是之后 autovacuum 進(jìn)程對(duì)老版本數(shù)據(jù)進(jìn)行 VACUUM, 主庫上對(duì)更新或刪除數(shù)據(jù)的老版本進(jìn)行 VACUUM 后, 從庫上也會(huì)執(zhí)行這個(gè)操作, 從而與從庫當(dāng)前查詢產(chǎn)生沖突, 導(dǎo)致查詢被中斷并拋出以上錯(cuò)誤。
實(shí)際上 PostgreSQL 提供了配置參數(shù)來減少或避免這種情況出現(xiàn)的概率, 主要包括以下兩個(gè)參數(shù):
maxstandby_ streaming_delay:
此參數(shù)默認(rèn)為 30 秒, 當(dāng)備庫執(zhí)行 SQL 時(shí), 有可能與正在應(yīng)用的 WAL 發(fā)生沖突, 此查詢?nèi)绻?30 秒沒有執(zhí)行完成則被中止, 注意 30 秒不是備庫上單個(gè)查詢?cè)试S的最大執(zhí)行時(shí)間, 是指當(dāng)備庫上應(yīng)用 WAL 時(shí)允許的最大 WAL 延遲應(yīng)用時(shí)間, 因此備庫上查詢的執(zhí)行時(shí)間有可能不到這個(gè)參數(shù)設(shè)置的值就被中止了, 此參數(shù)可以設(shè)置成 -1, 表示當(dāng)從庫上的 WAL 應(yīng)用進(jìn)程與從庫上執(zhí)行的查詢沖突時(shí),WAL 應(yīng)用進(jìn)程一直等待直到從庫查詢執(zhí)行完成。
hotstandby_feedback:
默認(rèn)情況下從庫執(zhí)行查詢時(shí)并不會(huì)通知主庫, 設(shè)置此參數(shù)為 on 后從庫執(zhí)行查詢時(shí)會(huì)通知主庫, 當(dāng)從庫執(zhí)行查詢過程中, 主庫不會(huì)清理從庫需要的數(shù)據(jù)行老版本, 因此, 從庫上的查詢不會(huì)被中止, 然而, 這種方法也會(huì)帶來一定的弊端, 主庫上的表可能出現(xiàn)膨脹, 主庫表的膨脹程度與表上的寫事務(wù)和從庫執(zhí)行時(shí)間有關(guān), 此參數(shù)默認(rèn)為 off
案例：
CentOS7.5+PG 版本 11.5
pgMaster 為主庫
pgSlave 為備庫
調(diào)整備庫的參數(shù)，設(shè)置
max_standby_streaming_delay = 10s # (測試便于看出效果這個(gè)參數(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;
開始?jí)簻y：
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)了這個(gè)報(bào)錯(cuò)場景。
解決方法有 2 種：
方案 1、 調(diào)大 max_standby_streaming_delay 參數(shù)值
我們可以將 max_standby_streaming_delay 調(diào)整為 -1 繞開這個(gè)錯(cuò)誤，或者將這個(gè)值調(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í)行查詢時(shí)會(huì)通知主庫，從庫執(zhí)行大查詢過程中，主庫不會(huì)清理從庫需要用到的數(shù)據(jù)行老版本。
備庫上需要開啟的參數(shù)：
max_standby_streaming_delay = 10s
hot_standby_feedback = on # 主要是這個(gè)參數(shù)設(shè)置為 on 即可
然后 使用 pg_ctl reload 使其生效
這時(shí)候，到備庫去查詢，可以發(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，這種方式有可能備庫上慢查詢由于長時(shí)間執(zhí)行而消耗大量主機(jī)資源，建議根據(jù)應(yīng)用情況設(shè)置一個(gè)較合理的值
2、 設(shè)置 hot_standby_feedback=on，這種方式可能會(huì)使主庫某些表產(chǎn)生膨脹。
這兩種方式無論選擇哪一個(gè)都應(yīng)該加強(qiáng)對(duì)流復(fù)制主庫、備庫慢查詢的監(jiān)控，并分析是否需要人工介入維護(hù)。
感謝你能夠認(rèn)真閱讀完這篇文章，希望丸趣 TV 小編分享的“postgresql 從庫查詢被終止怎么辦”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持丸趣 TV，關(guān)注丸趣 TV 行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!
