Postgresql

PostgreSQL 9.1 流式複制問題:replica 無法正確使用索引

  • March 4, 2013

我們在主伺服器上的 Ubuntu Linux 12.04 上使用 PostgreSQL 9.1.7,在副本伺服器上的 FreeBSD 9.0-RELEASE 上使用 PostgreSQL 9.1.7。副本伺服器和主伺服器在同一個 SQL 查詢上返回不同的結果。查詢計劃顯示使用索引(BTree 索引,我們根本不使用雜湊索引)來獲取結果,因此看起來索引在副本伺服器上處於不一致或不完整狀態。主伺服器上的查詢:

db1=# select id from users where email='xxxx@xxxx.net';
  id    
---------
1698116
(1 row)

db1=#

副本伺服器上的查詢:

db1=> select id from users where email='xxxx@xxxx.net';
id 
----
(0 rows)

db1=> select created_at from users where id=1698116;
        created_at         
----------------------------
2013-03-04 10:40:05.221214
(1 row)

db1=>

正如您所看到的,副本數據庫已經包含一個具有正確 ID 的使用者,因此數據就位,但由於某種原因尚未建立索引。我們仔細檢查了副本是否處於接收/重新應用狀態,因此這不是臨時中斷。使用者從未被索引。我們也曾經在 CentOS 5.6 上使用 PostgreSQL 9.0 遇到過類似的問題,所以我們認為這不是 FreeBSD 或 PostgreSQL 9.1 特有的問題。

我們使用副本伺服器執行大量繁重的 SQL 查詢,這會是問題的根源嗎?無論如何,我們如何才能有效地檢測和防止未來發生這種情況?副本今天沒有關閉,日誌中沒有單個錯誤行,所以我們只是偶爾檢測到這種不一致。

假設數據庫的語言環境en_GB.UTF-8在 Ubuntu(主)和 FreeBSD(從)中,我相信僅排序語義的差異可能解釋索引在從屬上不可用的事實。

以下是它們如何以不同方式排序的範例:

在 Ubuntu 12.04 上:

$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102 
$ sort file
0102
"0102"

在 FreeBSD 9.0-RELEASE 上:

$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102
$ sort file
"0102"
0102

這表明相同的語言環境對兩個字元串的順序不同,"0102"並且0102(即使它們甚至不包含 US-ASCII 集之外的任何字元……)

這是我建議您在自己的數據集上嘗試的測試:

在主上:

$ psql -d dbname -Atc 'select email from users' | LC_COLLATE=en_GB.UTF-8 >email.master

在奴隸上:

$ psql -d dbname -Atc 'select email from users' | LC_COLLATE=en_GB.UTF-8 >email.slave

現在將email.masterandemail.slave與 diff 或 cmp 進行比較。我懷疑你會發現它們並不相同。在這種情況下,它表明索引副本無法使用,因為它在主伺服器上的建構規則與從伺服器上的掃描規則不同。

引用自:https://dba.stackexchange.com/questions/35902