PostgreSQL 最佳實踐 - 線上增量備份與任意時間點恢複

冷備份, 以及邏輯備份都是某一個時間點的備份, 沒有增量的概念.

如果資料庫在運作過程中發生故障, 使用邏輯備份隻能将資料庫還原到備份時刻, 無法恢複到故障發生前的那個時刻.

又或者在使用過程中由于誤操作修改或删除了重要資料, 需要還原到誤操作前的那個時刻怎麼辦呢?

使用冷備份加上有效的歸檔檔案可以實作任意時間點的恢複. 但是冷備份需要停庫操作, 是以實用性不大.

本文要介紹的是線上的資料庫檔案備份, 彌補了冷備份的缺陷, 同時又支援基于時間點的恢複.

同時本文會介紹基于時間點(xid, target, time)的恢複方法.

要實作增量的備份，我們需要配置一些參數。

postgresql.conf參數配置 :

1. wal日志級别 :

檢視目前資料庫的wal level :

如果結果不為hot_standby或者archive, 那麼需要調整一下postgresql.conf. 并重新開機資料庫.

2. 歸檔模式 :

3. 歸檔指令 :

首先要建立用于存放wal歸檔檔案目錄, 資料庫啟動使用者需要寫權限.

然後在$pgdata中建立歸檔腳本

給archive.sh添加可執行權限 :

配置postgresql.conf中的歸檔指令 :

修改完後reload配置檔案即可

配置完後檢查一下歸檔是否正常 :

檢視歸檔檔案 :

4. 建議設定work process，sender程序數，才可以通過pg_basebackup使用流複制協定進行備份。

必須大于0，每個流複制連接配接需要一個wal sender程序。

5. 建議設定wal_keep_segments，確定非常大的資料庫，在漫長的備份時間周期内xlog不被覆寫。

注意配置之後，pg_xlog至少會占用wal_keep_segments * wal檔案大小 的空間。

需要預留好空間

具備了這些前提條件之後, 就可以進行線上備份了.

線上備份可以使用拷貝, rsync, 或者pg_basebackup指令進行.

首先在資料庫中以超級使用者執行如下指令 :

這個指令執行的目的是在$pgdata中記錄一個标簽檔案, 包含标簽名now()::text, 以及執行這條指令的啟動時間和wal位置.

标簽檔案資訊如下 :

接下來就可以拷貝資料檔案了.

拷貝完後清除備份檔案中pg_xlog和pg_log中的内容, 因為這些是不需要的.

接下來使用超級使用者在資料庫中執行如下指令 :

然後要備份這段時間産生的歸檔檔案 :

需要的歸檔檔案是哪些呢?

備份開始是的xlog檔案在backup_label中已經指出了000000030000000f00000003, 結束時的xlog檔案也可以從pg_stop_backup()的輸出中得到 :

如果你的postgresql版本沒有這個函數, 那麼就自己計算一下檔案名 :

參考 :

<a href="http://blog.163.com/digoal@126/blog/static/1638770402012914112949546/">http://blog.163.com/digoal@126/blog/static/1638770402012914112949546/</a>

是以本例中需要的檔案隻有1個就是000000030000000f00000003

如果歸檔檔案本來就不是放在本地檔案系統中, 那麼也不需要二次備份歸檔檔案. 隻要别删掉需要的歸檔檔案就行了.

方法與拷貝類似, 也需要先執行pg_start_backup, 然後調用rsync, 最後調用pg_stop_backup.

指令舉例 :

pg_basebackup是postgresql 9.1 引入的一個資料庫備份指令, pg_basebackup利用流複制協定傳輸資料庫檔案.

使用pg_basebackup備份資料庫需要用到超級使用者或者replication角色使用者.

另外就是pg_basebackup不需要執行pg_start_backup和pg_stop_backup, 因為指令中已經封裝了這些操作.

并且支援多個pg_basebackup并行備份同一個資料庫叢集.

例如 :

配置pg_hba.conf , 允許連接配接.

在另一台主機備份 :

需要注意, 以pg_basebackup備份, 如果表空間使用了軟連結, 那麼備份時在目标端也會建立軟連結, 是以需要先保證目錄存在并有寫權限.

例如本例 :

那麼目标端的/pgdata/digoal/1921/data03/pg93/1999 這個目錄要先建立好, 并提供寫權限.

postgresql 支援指定還原點的位置, 即資料庫恢複到什麼位置停下來.

4個recovery.conf參數控制恢複停止在哪個位置.

1. recovery_target_name, 指pg_create_restore_point(text)建立的還原點, 如果有重名的還原點, 那麼在recovery過程中第一個遇到的還原點即停止.

2. recovery_target_time, 指xlog中記錄的recordxtime(xl_xact_commit_compact-&gt;xact_time), 配合recovery_target_inclusive使用,

3. recovery_target_xid, 指xlogrecord-&gt;xl_xid, 可以配合recovery_target_inclusive使用, 但是recovery_target_inclusive隻影響日志的輸出, 并不影響恢複程序截至點的選擇, 截至都截止于這個xid的xlog位置. 也就是說無論如何都包含了這個事務的xlog資訊的recovery.

4. recovery_target_inclusive

還原點的詳細介紹可參考 :

<a href="http://blog.163.com/digoal@126/blog/static/163877040201303082942271/">http://blog.163.com/digoal@126/blog/static/163877040201303082942271/</a>

在還原前, 對資料庫做一些dml操作, 配合這些dml操作來講解一下這幾個參數的含義.

1.

begin1;query1;commit1; begin2; query2_1; pg_create_restore_point(text); query2_2; commit2;

按照以上描述, 使用recovery_target_name恢複資料庫恢複後應該包含query1的變更, 但是不包含query2_1和query2_2的變更.

切換日志, 歸檔 :

使用拷貝的備份以及歸檔日志還原 :

關閉資料庫

删除資料庫目錄以及表空間目錄

還原備份以及表空間軟連結

建立pg_log目錄, 存放日志 :

配置$pgdata/recovery.conf

建立恢複腳本 :

添加可執行權限 :

啟動資料庫

檢視還原點是否與預計比對 :

新插入的資料ctid=3, 說明query2_1的xlog資訊被恢複了, 但是復原了. 如果沒有執行info=new的ctid應該=2.

2.

commit_xact1; time1; rollback_xact2; time2; commit_xact3; commit_xact4;

按照以上描述, 使用recovery_target_time恢複,

删除資料庫目錄以及表空間目錄(為簡化例子, 本例無建立的表空間.)

新插入的資料ctid=4, 說明rollback_xact2;的xlog資訊被恢複了, 但是復原了. 如果沒有執行info=new的ctid應該=2.

其他幾種搭配就不測試了, 原理已經介紹清楚了. 有興趣的朋友可以自行測試.

3.

commit_xact1;begin2;query2_1;commit_xact3;commit_xact4;query2_2;rollback2;commit_xact5;

recovery_target_xid=commit_xact1, 無論recovery_target_inclusive=true|false, 恢複後資料庫将包含commit_xact1的資料變更;

recovery_target_xid=rollback2; 則包含xact1,xact3,xact4的變更以及xact2的復原;

recovery_target_xid=xact5; 則包含所有xlog資訊.

因為隻有當record_info等于xlog_xact_abort,xlog_xact_commit或者xlog_xact_commit_compact, 或者自建還原點時才可以被作為截至點,

是以本例使用xid=1692作為截至點, xlog會應用到query2_2;rollback2; -- session a : 這個位置(并且包含這個xlog資訊).

是以在此之前應該插入了6條記錄, 是以新插入的資料ctid=7, 再次說明query2_2;rollback2;的xlog資訊被恢複了, 但是復原了.

這個可以結合作業系統的定時腳本來實作, 在linux中可以使用crontab.

1. 首先規劃好存放資料庫資料檔案的目錄.

2. 編寫備份腳本

修改權限

3. 測試

pg93@db-172-16-3-33-&gt; 再次執行則退出, 因為今天已經備份了.

4. 配置crontab

首先删除當天備份

配置自動備份

5. 檢查是否自動備份.

檢查備份成功後把crontab調整為你需要的備份時間即可. 例如每天都淩晨2點開始備份.