我想與大家分享我第一次將 Postgres 資料庫恢復到完整功能的成功經驗。 半年前我開始接觸Postgres DBMS，在此之前我完全沒有資料庫管理的經驗。
我在一家大型 IT 公司擔任半 DevOps 工程師。 我們公司開發高負載服務的軟體，我負責效能、維護和部署。 我收到了一項標準任務：更新一台伺服器上的應用程式。 該應用程式是用 Django 編寫的，在執行更新遷移期間（資料庫結構的變更），在此過程之前，我們透過標準 pg_dump 程式進行完整的資料庫轉儲，以防萬一。
進行轉儲時發生意外錯誤（Postgres 版本 9.5）：
pg_dump: Oumping the contents of table “ws_log_smevlog” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
pg_dump: The command was: COPY public.ws_log_smevlog [...]
pg_dunp: [parallel archtver] a worker process dled unexpectedly
問題 “區塊中的頁面無效” 談到檔案系統層級的問題，這是非常糟糕的。 在各種論壇上建議這樣做 全真空 有選項 零損壞頁數 來解決這個問題。 好吧，讓我們嘗試一下...
準備恢復
警告！ 在嘗試還原資料庫之前，請務必先進行 Postgres 備份。 如果您有虛擬機，請停止資料庫並拍攝快照。 如果無法拍攝快照，請停止資料庫並將 Postgres 目錄的內容（包括 wal 檔案）複製到安全位置。 我們業務的主要任務不是讓事情變得更糟。 讀 .
由於資料庫通常適合我，因此我將自己限制為常規資料庫轉儲，但排除了資料損壞的表（選項 -T，--排除表=表 在 pg_dump 中）。
伺服器是實體的，不可能拍攝快照。 備份已刪除，讓我們繼續。
檔案系統檢查
在嘗試恢復資料庫之前，我們需要確保檔案系統本身一切正常。 如果出現錯誤，請糾正它們，因為否則只會讓事情變得更糟。
就我而言，帶有資料庫的檔案系統安裝在 “/srv” 且型別是ext4。
停止資料庫： 系統控制停止 [電子郵件保護] 並檢查檔案系統是否未被任何人使用並且可以使用命令卸載 的:
lsof +D /srv
我還必須停止 redis 資料庫，因為它也在使用 “/srv”。 接下來我卸載了 / srv (解除安裝).
使用實用程式檢查檔案系統 e2fsck 使用開關 -f (即使檔案系統被標記為乾淨也強制檢查):
接下來，使用該實用程式 轉儲2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep 檢查）您可以驗證檢查是否實際執行：
e2fsck 表示在 ext4 檔案系統層級沒有發現問題，這意味著您可以繼續嘗試恢復資料庫，或者更確切地說返回 真空滿 （當然，需要重新掛載檔案系統並啟動資料庫）。
如果您有實體伺服器，請務必檢查磁碟的狀態（透過 smartctl -a /dev/XXX）或 RAID 控制器，以確保問題不在硬體層級。 就我而言，RAID 原來是“硬體”，所以我要求本地管理員檢查 RAID 的狀態（伺服器距離我幾百公里）。 他說沒有任何錯誤，這意味著我們肯定可以開始修復。
嘗試 1：zero_damaging_pages
我們使用具有超級使用者權限的帳戶透過 psql 連接到資料庫。 我們需要一個超級用戶，因為...... 選項 零損壞頁數 只有他可以改變。 就我而言，它是 postgres：
psql -h 127.0.0.1 -U postgres -s [資料庫名稱]
選項 零損壞頁數 需要忽略讀取錯誤（來自 postgrespro 網站）：
當 PostgreSQL 偵測到損壞的頁頭時，它通常會報告錯誤並中止目前交易。 如果啟用了zero_damaging_pages，系統會發出警告，將記憶體中損壞的頁面清除，然後繼續處理。 此行為會破壞數據，即損壞頁中的所有行。
我們啟用該選項並嘗試對錶進行完全清理：
VACUUM FULL VERBOSE
不幸的是，運氣不好。
我們遇到了類似的錯誤：
INFO: vacuuming "“public.ws_log_smevlog”
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070– 一種在 Poetgres 中儲存「長資料」的機制，如果它不適合一頁（預設為 8kb）。
嘗試 2：重新索引
谷歌的第一個建議沒有幫助。 經過幾分鐘的搜索，我找到了第二個技巧 - 製作 重新索引 損壞的桌子。 我在很多地方都看過這樣的建議，但並沒有激發信心。 讓我們重新索引：
reindex table ws_log_smevlog
重新索引 毫無問題地完成。
然而，這並沒有幫助， 真空充滿 因類似錯誤而崩潰。 由於我習慣了失敗，所以我開始在網路上進一步尋找建議，並發現了一個相當有趣的方法 .
試 3：選擇、限制、偏移
上面的文章建議逐行查看表格並刪除有問題的資料。 首先我們需要查看所有行：
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; done就我而言，該表包含 1 628 991 線！ 需要好好照顧 ，但這是一個單獨討論的主題。 那是星期六，我在 tmux 中運行了這個命令然後上床睡覺：
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; done到了早上，我決定檢查一下事情進展如何。 讓我驚訝的是，我發現20小時後，只有2%的資料被掃描了！ 我不想等50天。 又一個徹底的失敗。
但我沒有放棄。 我想知道為什麼掃描花了這麼長時間。 從文檔（再次在 postgrespro 上）我發現：
OFFSET 指定在開始輸出行之前跳過指定的行數。
如果同時指定了 OFFSET 和 LIMIT，系統先跳過 OFFSET 行，然後開始計算 LIMIT 約束的行數。使用 LIMIT 時，也必須使用 ORDER BY 子句，以便以特定順序傳回結果行。 否則，將傳回不可預測的行子集。
顯然，上面的命令是錯誤的：首先，沒有 排序，結果可能是錯誤的。 其次，Postgres 首先必須掃描並跳過 OFFSET 行，並且隨著增加 OFFSET 生產力將進一步下降。
試 4：以文字形式轉儲
然後我想到了一個看似絕妙的想法：以文字形式轉儲並分析最後記錄的行。
但首先，讓我們先來看看表格的結構。 ws_log_smevlog:
在我們的例子中，我們有一個專欄 “ID”，其中包含行的唯一識別碼（計數器）。 計劃是這樣的：
- 我們開始以文字形式進行轉儲（以sql指令的形式）
- 在某個時間點，轉儲會因錯誤而中斷，但文字檔案仍會保存在磁碟上
- 我們查看文字檔案的末尾，從而找到成功刪除的最後一行的標識符（id）
我開始以文字形式轉儲：
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dump正如預期的那樣，轉儲因相同的錯誤而中斷：
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
進一步透過 尾巴 我看著轉儲的盡頭（尾部-5./my_dump.dump)發現dump在id為的線路上中斷 186 525。 “所以問題出在id 186 526的行上，它壞了，需要刪除！” - 我想。 但是，對資料庫進行查詢：
«從 ws_log_smevlog 選擇 *，其中 id=186529「事實證明，這條線一切都很好......索引為 186 - 530 的行也可以正常工作。 另一個「絕妙點子」失敗了。 後來我明白了為什麼會發生這種情況：當從表中刪除和更改資料時，它們並沒有被物理刪除，而是被標記為“死元組”，然後出現 自動真空 並將這些行標記為已刪除並允許重複使用這些行。 要理解，如果表中的資料發生變化並且啟用了autovacuum，那麼它就不是順序儲存的。
試 5：SELECT、FROM、WHERE id=
失敗讓我們更堅強。 你不應該放棄，你需要堅持到底並相信自己和自己的能力。 所以我決定嘗試另一個選擇：逐一查看資料庫中的所有記錄。 了解我的表的結構（見上文），我們有一個唯一的 id 欄位（主鍵）。 表中有 1 行， id 是按順序排列的，這意味著我們可以一一瀏覽它們：
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done如果有人不明白，該命令的工作原理如下：它逐行掃描表並將標準輸出發送到 的/ dev / null的，但如果 SELECT 命令失敗，則會列印錯誤文字（stderr 發送到控制台）並列印包含錯誤的行（感謝 ||，這表示 select 有問題（命令的回傳碼不為 0))。
我很幸運，我在現場創建了索引 id:
這意味著找到具有所需 id 的行不會花費太多時間。 理論上它應該有效。 好吧，讓我們運行命令 TMUX 我們去睡覺吧。
到早上，我發現大約有 90 個條目被瀏覽，略高於 000%。 與之前的方法 (5%) 相比，結果非常好！ 但我不想等2天...
試 6：SELECT、FROM、WHERE id >= 和 id
客戶擁有專用於資料庫的優秀伺服器：雙處理器 英特爾至強E5-2697 v2，我們所在的位置有多達 48 個線程！ 伺服器上的負載是平均的；我們可以毫無問題地下載大約 20 個線程。 RAM 也足夠：高達 384 GB！
因此，該命令需要並行化：
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done這裡可以編寫一個漂亮而優雅的腳本，但我選擇了最快的平行化方法：手動將範圍 0-1628991 分割為 100 條記錄的間隔，並分別運行以下形式的 000 個命令：
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done但這還不是全部。 理論上，連接資料庫也需要一些時間和系統資源。 您會同意，連接 1 並不是很明智。 因此，讓我們檢索 628 行而不是一對一連接。 結果，團隊變成了這樣：
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done在 tmux 會話中開啟 16 個視窗並執行命令：
1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done … 15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
一天后，我收到了第一個結果！ 即（不再保留值XXX和ZZZ）：
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000這意味著三行包含錯誤。 第一條和第二筆問題記錄的id在829到000之間，第三條問題記錄的id在830到000之間。接下來，我們只需找到問題記錄的準確id值即可。 為此，我們以步驟 146 的方式查看有問題記錄的範圍並識別 ID：
for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911
大結局
我們發現了有問題的線路。 我們透過 psql 進入資料庫並嘗試刪除它們：
my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1令我驚訝的是，即使沒有選項，條目也被毫無問題地刪除了 零損壞頁數.
然後我連接到資料庫，做了 真空充滿 （我認為沒有必要這樣做），最後我使用成功刪除了備份 pg_dump。 轉儲已完成，沒有任何錯誤！ 問題就這樣被解決了。 喜悅之情溢於言表，在經歷瞭如此多的失敗之後，我們終於找到了解決方案！
致謝和結論
這是我第一次恢復真正的 Postgres 資料庫的經驗。 我會記得這段經歷很長一段時間。
最後，我要感謝 PostgresPro 將文件翻譯成俄語並感謝 ，這對分析問題有很大幫助。
來源： www.habr.com