postgresql 利用xlog進行熱備操作

一、驗證postgresql增量合併的方案

結果：沒有有效可行的增量合併方案，暫時放棄

二、梳理postgresql基於wal的增量備份

物理備份與還原適用於跨小版本的恢復但是不能跨平臺

邏輯備份與還原備份資料適用於跨版本和跨平臺的恢復

postgersql增量備份步驟

1.首先建立歸檔目錄

例如:歸檔目錄為/archive_pg_xlog/xlog

1>mkdir -p /archive_pg_xlog/xlog
2>chown -R postgres:postgres /archive_pg_xlog/xlog
3>chmod 700 /archive_pg_xlog/xlog

2.修改postgresql.conf組態檔

1>wal_level=archive(設定wal的級別)
2>archive_mode=on(開啟歸檔)
3>archive_command='DATE=`date +%Y%m%d`;DIR="/archive_pg_xlog/xlog/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'(設定歸檔路徑)

3.重新啟動資料庫服務

systemctl restart postgresql（使設定生效）

4.做全量備份

 1>psql -c 'select pg_start_backup(now()::text);'，#執行該命令表示開始備份
 2>tar -cf base.tar /var/lib/pgsql/data/*
 3>psql -c 'select pg_stop_backup();'#tar結束後要關閉備份

5.如果表空間存在，還需要備份表空間，沒有則不需要

6.以上操作完成後，增量備份就自動開始了

當xlog寫滿後，就自動歸檔到我們指定的目錄了

postgresql利用xlog進行還原

模擬災難，直接刪除data目錄（或者是data目錄下的所有檔案丟失）

先檢查資料庫是否已經停止，如果還未停止需要停止

mkdir data;chown postgres:postgres data;chmod 700 data

將備份的base.tar全量包拷貝到data目錄，解壓

cp /usr/share/psql/recovery.conf.sample ./data
vim recovery.conf.sample設定restore_command=''
chown postgres:postgres recovery.conf.sample;chmod 700 recovery.conf.sample;mv recovery.conf.sample recovery.conf

啟動資料庫，當recovery.conf變成了recovery.done時即表示恢復完成

注:xlog檔名由時間線TimeLineID（8位元16進位制）、邏輯紀錄檔檔案號（8位元16進位制）和段檔案ID（8位元16進位制）組成

測試1.

第一次全量時，db有一百萬條資料，然後再次插入一百萬條資料，模擬災難，正常恢復

第二次全量時，是在第一次模擬災難後的場景，即第二次全量備份時有兩百萬條資料，再次插入十萬條資料，模擬災難，正常恢復

測試2.

再次模擬災難，在第二次全備份新增十萬條資料後，直接刪掉data目錄

用第一次的全備份資料結合產生的增量來進行恢復，結果，只有兩百萬條資料，說明該增量不可合併

測試3.

模擬某個xlog檔案丟失時

結果：丟失的xlog檔案隻影響該丟失的xlog檔案依賴的全量備份（會導致資料丟失），但是不會影響xlog檔案丟失後面的全量備份

對於丟失這種情況暫時還沒有找到恢復的辦法

如果要還原到最近的那一次,也就是不是因為誤操作的等原因的恢復，則不需要設定還原點

recovery_target_name=''(這個是還原點的名字)
recovery_target_time=''(這個是要還原到的具體時間點)
recovery_target_xid='' (以最後結束時的id為準)

建立還原點,也就是還原點的名字select pg_create_restore_point('pitr_test');

建立基於時間的還原點,select now();

建立基於xid的還原點,select txid_current();

補充：PostgreSQL中的log, xlog和clog

pg_log

$PGDATA/pg_log是資料庫執行活動紀錄檔的預設儲存目錄，它包括錯誤資訊，查詢紀錄檔以及啟動/關閉資料庫的資訊。當PostgreSQL啟動失敗時，這裡應該是你第一個應該檢視的資訊。一些Linux發行版以及其他的軟體包管理系統會將這個紀錄檔目錄移到某些地方，比如：/var/log/postgresql

你可以在pg_log目錄裡自由地刪除、重新命名、壓縮或者移動檔案而不會有什麼不好的結果，只要Postgres使用者仍然有許可權寫該目錄。如果pg_log隨著許多大檔案而膨脹，你可能需要在postgresql.conf裡減小你想記錄紀錄檔的事件。

pg_xlog

$PGDATA/pg_xlog是PostgreSQL的事務紀錄檔。 這是一些二進位制紀錄檔檔案的集合，檔名類似00000001000000000000008E，它包含最近事務的一些描述資料。這些紀錄檔也被用於二進位制複製。如果複製、歸檔或者PITR失敗了，當歸檔正在恢復時，這個目錄儲存的資料庫紀錄檔可能會膨脹數GB。這可能會導致你用完你的磁碟空間。不像pg_log，你不能自由地刪除、移動或者壓縮這個目錄的檔案。你甚至不能在沒有符號連結到該目錄的情況下移動這個目錄。刪除pg_xlog的檔案可能會導致不可恢復的資料庫損壞。

如果你發現自己處在這樣的情況：你發現有100G大小的檔案在pg_xlog目錄並且資料也啟動不了，並且你已經禁止歸檔/複製並且嘗試清理磁碟空間等任何其他的方式，請做以下兩個步驟：

1、從pg_xlog目錄裡移動檔案到一個備份磁碟或者共用網路驅動器中，也不要刪除它們。

2、移動一些最老的檔案，直到足夠允許PostgreSQL啟動起來。

pg_clog

$PGDATA/pg_clog包含了事務的後設資料。這種紀錄檔用於告訴PostgreSQL哪個事務已經完成、哪個還沒有完成。clog是比較小的並且沒有任何理由會膨脹，所以，你應該沒有任何理由去碰觸它。在任何時候你都不應該從pg_clog裡刪除檔案，如果你這樣子做，還不如完全地刪除整個資料庫目錄。缺少clog是不可恢復的。請注意，這意味著，如果你在$PGDATA目錄裡備份檔案，你應該確定同時包含pg_clog和pg_xlog，否則你可能會發現你的備份是不可用的。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。如有錯誤或未考慮完全的地方，望不吝賜教。