將 8.1 升級到 9.3 - 使用語句日誌和 WAL 檢查兼容性？
我們正在從 postgres 8.1 遷移到 9.3，因為我們非常需要流複製。很久以前，所有應用程序開發人員都離開了公司，比我還早，所以真的沒有人知道應用程序內部發生了什麼。我們一直將它們視為黑匣子。
作為遷移和測試應用程序與 postgres 9.3 兼容性的策略，我正在考慮：
- 做一個 pg_dump 有一個起點。
- 記錄所有 SQL 語句。
- 從我執行 pg_dump 的那一刻起，將所有記錄的 SQL 語句重播到新伺服器。這將需要同步，並且…
- 檢查新伺服器對 (3) 提供的語句的響應，看它是否可以消化所有將發送給它的 SQL 語句。這將照顧“輸入”的兼容性。一旦我們對“輸入”兼容性感到滿意，並且新伺服器是最新的，那麼……
- 從日誌中挑選一個好的 SELECT 命令樣本，將它們重播到兩台伺服器，擷取文件的輸出，比較文件。這將照顧“輸出”的兼容性。
這將執行超過 6 個月，以確保不會有任何意外。如果您發現我的計劃中有任何漏洞，請隨時指出：）
我唯一的疑問是語句在日誌文件中出現的順序：
日誌文件中的語句是否以與 WAL 文件中相同的順序出現？
謝謝
WAL vs 語句日誌排序
日誌文件中的語句是否以與 WAL 文件中相同的順序出現？
這真的沒有意義。
語句根本不會出現在 WAL 中。就事務開始/送出/回滾、表堆和索引塊的更改等而言，它們的效果確實如此，但實際語句根本不會記錄在 WAL 中。
許多查詢在大多數係統上同時執行。預寫日誌包含這些查詢的效果，它們按照它們執行工作的順序混合在一起。所以查詢實際上都在 WAL 中。
PostgreSQL按照接收查詢的順序記錄查詢以進行解析和執行。因為作業系統調度程序控制每個 PostgreSQL 後端何時執行，所以這不一定是每個查詢開始執行的完全相同的順序，當然也不是每個查詢完成執行的相同順序。
您所能做的就是設置一個
log_line_prefix記錄與語句關聯的事務 ID，然後在相應的 WAL 條目中查找這些事務 ID。為此，您必須解析磁碟塊結構以查找已更改元組上的xminorxmax欄位；像這樣的工具pg_xlogdump會幫助你。總而言之，我不希望您從嘗試將 WAL 與查詢日誌進行匹配中獲得太多收益。
重播語句以比較結果
從日誌中挑選一個好的 SELECT 命令樣本，將它們重播到兩台伺服器，擷取文件的輸出，比較文件。這將照顧“輸出”的兼容性。
如果您的應用程序只使用一次執行一個查詢（即它只使用一個連接），這將起作用。
幾乎所有現實世界的應用程序都會執行許多並發寫入。你不能簡單地重放查詢日誌來複製並發寫操作的效果——尤其是在
read committed事務隔離中，一個事務的送出會影響其他並發事務的結果。如果您使用適當的方式記錄事務邊界和事務 ID，
log_line_prefix您可能可以重新組合每個會話執行的所有單個語句的交錯排序，並使用自定義工具以該確切順序重播它們。但它不會是完美的，因為單個語句可能會受到彼此競爭條件的影響。舉個簡單的例子：
INSERT INTO mytable (id, somevalue) SELECT 1, 'blah' WHERE NOT EXISTS (SELECT id FROM mytable WHERE id = 1);如果您從兩個不同的連接同時執行兩次，效果可能會有所不同。它們將根據每個啟動的確切時間以及作業系統調度程序的時間決策而改變。可能的結果包括：
- 第一個插入一行並返回
INSERT 1，第二個不插入行並返回INSERT 0。或相反亦然。- 第一個插入一行並返回
INSERT 1。第二次嘗試插入同一行，因為SELECT沒有找到任何帶有 , 的行id=1，並因重複鍵錯誤而失敗。如您所見，僅使用查詢日誌是不可能將一個數據庫的確切狀態重播到另一個數據庫的。那是在考慮回滾對序列、易失函式、使用時間戳的函式等的影響之前。
比較輸入和輸出的局限性
您描述的方法無法檢測到某些應用程序錯誤。
例如，
inserted基於時間的小細節、計劃選擇的差異等，行在表中以不同的順序排列是完全正常的。正確的應用程序不會介意這一點，因為它要麼不關心行順序, 或 or 指定一個特定的order by子句，當它關心時。然而，在現實中，一些應用程序試圖將表格視為具有某種與生俱來的秩序，如果表格不“有序”，就會突然變得非常不安。對於 PostgreSQL 來說，表只是一個根本沒有先天順序的堆，它只是碰巧按照應用程序期望的順序返回行，因為這是最快的。因此，如果您的應用程序依賴於這樣的行為，您將不會注意到您提出的任何一個測試的問題。如果您嘗試比較重播的結果而不對結果進行排序，那麼由於時間和計劃的細微差異，*一切都會顯得不匹配。*但是，如果您確實對結果進行了排序，您可能不會注意到應用程序對排序做出不安全假設的情況，而在 8.1 而不是在 9.3 上它可以逃脫。
另一種情況是更改
bytea_output預設格式。您的應用程序的插入將產生相同的結果，但現在選擇數據將以不同的格式返回。如果您的應用不理解 \xDEADBEEF 十六進制格式 bytea，它可能會嘗試將其解釋為八進制 bytea。如果您將它保存回數據庫並且它被破壞，或者由於錯誤解釋的 bytea 數據而導致應用程序出現錯誤，您只會注意到問題。那麼如何檢查兼容性呢？
如果您的應用程序沒有一個好的單元和集成測試框架，那麼現在是添加一個的時候了。
閱讀每個主要 PostgreSQL 版本的發行說明，並在您的查詢中尋找特定的兼容性挑戰，例如缺失的
from子句、文本的隱式轉換等。針對應用於新數據庫測試副本的舊數據庫轉儲（使用 9.3 的 pg_dump 獲取）手動測試應用程序的每個部分。
您還應該簡單地查看您的查詢以查找可能的問題。有些東西，比如
LIMIT沒有對應的子句ORDER BY，是應用程式碼中預先存在的錯誤，它以前可能已經解決了，並且可能在 9.3 中仍然可以解決……或者可能不會。PostgreSQL 會嘗試檢測明顯的錯誤，但在某些合理的情況下，您可能會使用 aLIMIT而沒有 aORDER BY，因此在這種情況下以及許多相關的情況下，它無法警告您，以防萬一這確實是您想要的。您將面臨的最大問題將是：
- 切換到
standard_conforming_strings = on更改字元串中的轉義處理from刪除導致以前看起來有效但非標準且可能不安全的查詢失敗的隱式子句；- 刪除隱式轉換以
text導致以前看起來有效（但可能有錯誤）的查詢失敗；- 更改預設值
bytea_output（如果您使用 bytea 欄位）