PostgreSQL backup and recovery - online logical backup & recovery

備份時不影響其他使用者對備份對象的dml操作. 

本文主要介紹一下postgresql提供的邏輯備份工具pg_dump, pg_dumpall, 以及資料庫的copy指令.

pg_dump : 

使用pg_dump進行備份時, 其他使用者可以同時進行dml(select, update, delete, insert)操作, 互相之間沒有幹擾. 

一、pg_dump備份程式的邏輯. 

1. pg_dump的一次完整的備份是在一個事務中完成的, 事務隔離級别為serializable 或者 repeatable read. 代碼如下 :

2. pg_dump在備份資料開始前, 需要對進行備份的對象加access share鎖, 代碼如下 : 

pg_dump加的鎖與ddl沖突, 例如truncate, drop, alter, vacuum full, 以及以及unqualified lock table沖突, 是以備份開始後是不能進行這些操作的. 可以防止備份過程中資料結構改變, 或者資料被實體的删除掉了. 

正因為pg_dump在備份資料前要對備份對象加鎖, 是以為了防止pg_dump無休止的的鎖等待, pg_dump支援鎖逾時.

例如 : 

session a :

session b : 

會一直等session a是否test的鎖.

從第三個會話可以看出這個等待.

session c :

如果不像讓pg_dump一直等待下去, 那麼可以使用--lock-wait-timeout參數. 例如以下指令, 等待5秒未成功獲得鎖則退出pg_dump.

3. 一切準備就緒後, pg_dump将開始備份資料.

二、備份的内容格式 : 

以postgresql 9.3 為例, pg_dump 支援4種格式 : 

           p, plain

               預設格式, 備份輸出為可讀的text文本. 還原時在資料庫中直接執行備份文本的sql即可.

           c, custom

               可自定義的歸檔格式, 同時預設開啟了資料壓縮, 還原時可以調整備份對象的還原順序, 同時支援選擇還原的對象. 

               備份寫入到一個檔案中. 需要注意檔案系統支援的單個檔案大小.

               這個格式必須使用pg_restore指令進行還原. 

           d, directory

               目錄歸檔格式, 與custom格式類似, 需要使用pg_restore還原. 但是目錄歸檔格式下會建立一個目錄, 然後每個表或者每個大對象對應一個備份輸出檔案.

               加上toc檔案名描述備份的詳細資訊, 這個格式預設支援壓縮, 同時支援并行導出.

           t, tar

               tar歸檔格式, 不支援壓縮, 同時限制每個表最大不能超過8gb, 同樣需要使用pg_restore還原. 

三、全庫一緻性備份舉例 : 

注意全庫一緻性不是叢集一緻性, 一個postgresql 叢集中可以建立多個資料庫. pg_dump的全庫一緻性備份指的是叢集中的單個資料庫的一緻性備份.

排他選項 : 

使用多次 --exclude-table-data=table 排除不需要備份的表.

使用多次 --exclude-schema=schema 排除不需要備份的schema.

備份前檢視一下備份資料的hash值. 友善還原後對照 :

備份指令 : 

備份digoal庫, ddl中不包含表空間. 是以恢複時不需要提前建立對應的表空間.

删除digoal庫.

還原, 直接執行備份sql即可 : 

檢查還原後的hash值, 與備份前一緻.

四、非全庫一緻性備份舉例 : 

知道pg_dump備份流程後, 可以想象如果資料庫龐大, pg_dump備份的時間就會越長, 持鎖就會越久. 這對有ddl需求的資料庫來說, 可能是無法忍受的.

是以您可能需要将pg_dump的粒度弄小一點, 不要一次備份整庫, 例如同時備份有一緻性需求或者依賴關系的資料表.

舉個例子 :

# 這隻是個簡單的例子, 每次隻備份1張表. 多次調用pg_dump. 

# 實際使用時需要考慮業務邏輯, 確定業務資料一緻性. 例如某一些有關聯的表確定放在同一個pg_dump中導出.

多個-t talbename參數即可.

# 同時需要注意在設計時需要考慮到單表資料量不要太大, 應該考慮分區表. 否則該單表的ddl操作也會加大和pg_dump發生沖突的機率.

# 對于較大的資料庫還是建議使用pitr實體增量備份方式. 這個後面會講到.

五、并行備份舉例 : 

并行備份首先需要9.3的pg_dump, 服務端需要9.2以及以上支援pg_export_snapshot的版本. 是以并行備份也是一緻性備份. 

使用并行備份需要n+1個連接配接, n指-j n指定的并行度, 1 是主連接配接也就是到處事務狀态的連接配接, 其他連接配接導入這個事務狀态, 進行到處.

對于9.2以前的版本, 如果要并行備份來提高備份速度, 同時又要資料庫一緻性, 那麼請在備份期間不要對備份對象執行dml操作.

首先建立1000個測試表, 插入測試資料 :

備份, 并行度為10, 備份到./paralleldmp目錄, 這個目錄會自動建立 : 

輸出每個表一個檔案, 加上一個toc檔案.

還原測試 : 

首先删除digoal庫.

還原 : 

檢查是否還原 : 

六、toc檔案定制舉例 : 

定制toc檔案可以達到調整還原順序, 開關還原對象的目的.

首先要建立list. 使用pg_restore的 -l 參數. 從directory或dmp檔案中建立list. 下面以目錄歸檔為例, 建立list檔案 :

以下為部分list檔案截取 : 

分号為注釋;

截取一行解釋一下 : 

以上toc檔案中entry的意思截取自  : 

src/bin/pg_dump/pg_backup_archiver.c

下面調整一下list檔案, 根據list檔案還原 : 

例如注釋test_1的表建立和資料還原.

然後調整順序

調整為

儲存toc.list

删除資料庫digoal:

還原 :

檢視restore.log日志, 注意到順序調整生效 : 

同時進入資料庫檢視test_1表沒有被還原.

pg_dumpall : 

pg_dumpall最主要的是用于備份全局資料, 例如表空間的ddl, 建立使用者的ddl.

導出建立使用者, 建立表空間的腳本 :

内容如下 : 

copy : 

除了使用以上指令導出資料庫之外, 還可以使用sql語句導出資料, 亦可結合管道使用.

導出 :

導入 : 

[小結]

1. 如果資料庫非常龐大, 在做pg_dump備份時, 與access share鎖沖突的sql将會處于等待狀态.

    等待直到pg_dump結束(鎖的釋放需要等待頂級事務塊結束). 

    這個是需要特别注意的.

2. pg_dump, pg_dumpall, pg_restore指令詳解參考 man 文檔.

【參考】

1. http://blog.163.com/digoal@126/blog/static/163877040201241134721101/

2. http://blog.163.com/digoal@126/blog/static/16387704020123129649342/

3. http://blog.163.com/digoal@126/blog/static/163877040201337104355272/

4. http://blog.163.com/digoal@126/blog/static/163877040201322510519547/