Postgrsql 從節點當機，主節點執行DML語句出現等待情況

一、問題描述

postgresql主從叢集，當叢集中的從節點當機後，主節點執行DDL與DML時出現語句等待情況。

二、問題原因分析

由於當前PostGresql叢集主從節點配置了流複製狀態，主從節點採用半同步複製關係， 一旦出現網路抖動或者從節點故障 ，複製關係不會自動降級為非同步模式， 導致同步事務進入等待狀態。

三、解決方法

針對這個問題需要對同步複製進行降級處理，下面介紹一下如何設定事務可靠性級別。

PostgreSQL 支援在會話中設定事務的可靠性級別。
off 表示commit 時不需要等待wal 持久化。
local 表示commit 是隻需要等待本地資料庫的wal 持久化。
remote_write 表示commit 需要等待本地資料庫的wal 持久化，同時需要等待sync standby節點wal write buffer完成(不需要持久化)。
on 表示commit 需要等待本地資料庫的wal 持久化，同時需要等待sync standby節點wal持久化。
提醒一點， synchronous_commit 的任何一種設定，都不影響wal日誌持久化必須先於shared buffer髒資料持久化。 所以不管你怎麼設定，都不好影響資料的一致性。

synchronous_commit = off                # synchronization level;

                                        # off, local, remote_write, or on

實現同步複製降級

如果 backend process 進入了等待迴圈，只接受幾種訊號降級。 並且降級後會告警，表示本地wal已持久化，但是同步從節點不確定wal有沒有持久化。如果你只配置了1個從節點，並且將它配置為同步流複製節點。一旦出現網路抖動和從節點故障，將導致同步事務進入等待狀態。
方法1.
修改配置檔案並重置

$ vi postgresql.conf  

synchronous_commit = local

$ pg_ctl reload

然後cancel 所有query .

postgres=# select pg_cancel_backend(pid) from pg_stat_activity where pid<>pg_backend_pid();

收到這樣的訊號，表示事務成功提交，同時表示WAL不知道有沒有同步到sync standby。

WARNING:  canceling wait for synchronous replication due to user request

DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.

COMMITpostgres=# show synchronous_commit ;

 synchronous_commit 

--------------------

 off

(1 row)

同時它會讀到全域性變數synchronous_commit 已經是 local了。
這樣就完成了降級的動作。

方法2.
方法1的降級需要對已有的正在等待wal sync的pid使用cancel進行處理，有點不人性化。
可以透過修改程式碼的方式，做到更人性化。
SyncRepWaitForLSN for迴圈中，加一個判斷，如果發現全域性變數sync commit變成local, off了，則告警並退出。這樣就不需要人為的去cancel query了.

WARNING:  canceling wait for synchronous replication due to user request

DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.