PostgreSQL/備份與恢復
建立備份是每個資料庫管理員的必備任務。如果硬體崩潰或出現任何形式的資料損壞，DBA 必須確保資料庫能夠以最小的資料丟失恢復。PostgreSQL 提供多種策略來支援 DBA 實現此目標。
原則上，備份技術可分為兩類：冷備份和熱備份。冷備份是在沒有資料庫檔案開啟的情況下進行的備份。對於 PostgreSQL 來說，這意味著在進行備份的整個時間段內，例項必須停止。熱備份是在正常工作時間進行的備份。客戶端可以並行執行讀寫操作，以建立此形式的備份。
PostgreSQL 支援不同型別的備份
- 冷備份稱為 檔案系統級備份。
- 熱備份有兩種型別
- SQL 匯出 生成 SQL 命令，例如：
INSERT，可以重新建立資料庫。 - 持續歸檔和時間點恢復 (PITR) 使用特殊備份與之後所有資料更改的組合。
- SQL 匯出 生成 SQL 命令，例如：
冷備份是在 PostgreSQL 例項未執行時進行的備份。它包括叢集中所有資料庫的所有檔案。
只有一種方法可以建立一致的，因此有用的冷備份：PostgreSQL 例項必須停止，例如透過發出 pg_ctl stop 命令。這將斷開所有客戶端與叢集中所有資料庫的連線，關閉例項並關閉所有檔案。之後，可以使用常用的作業系統複製工具（cp、tar、dd、rsync 等）將所有檔案複製到安全位置，例如不同伺服器上的磁碟。特別是要複製以下檔案：
- 叢集所在的目錄節點下的所有檔案。環境變數 $PGDATA 指向此目錄，解析為類似於
.../postgres/14/data的內容。在命令列上使用echo $PGDATA或在 psql 中使用show data_directory;來查詢目錄。 - 所有配置檔案。它們可能位於 $PGDATA 中，但也可能位於其他地方。主要的配置檔案是：postgresql.conf、pg_hba.conf 和 pg_ident.conf。可以透過在 psql 實用程式中執行以下命令來找到它們的位置
show config_file;
show hba_file;
show ident_file;
- 所有表空間檔案。這些檔案位於檔案系統的其他位置。可以透過檢視
$PGDATA/pg_tblspc目錄中的符號連結來找到它們的位置
cd $PGDATA/pg_tblspc
ls -lt
注意 您可以嘗試只備份叢集的某些部分，例如代表一個表的巨大檔案，或一個表空間——或者相反：除巨大檔案之外的所有內容。即使例項在生成這種部分副本時已關閉，此類副本也是無用的。恢復冷備份需要叢集的所有資料檔案和元資訊檔案才能重新建立叢集。
注意 強烈建議在測試系統上驗證每個備份/恢復策略以驗證其可靠性，然後再在生產伺服器上實施它們。特別是，必須測試恢復步驟！
► 優點
- 冷備份易於生成和恢復。
► 缺點
- 叢集中任何資料庫的持續 7x24 執行模式是不可能的。
- 無法備份叢集的較小部分，例如單個數據庫或表。
- 無法進行部分恢復。恢復必須包含所有叢集檔案。
- 崩潰後，最近一次冷備份後發生的任何資料更改都會丟失。只有備份中的資料將被恢復。
► 如何恢復
- 停止例項。
- 備份崩潰的叢集的原始檔案。它們可能對取證行動有用。
- 刪除崩潰的叢集的所有原始檔案。
- 將冷備份的檔案複製到它們原來的位置。
- 啟動例項。它應該以正常方式啟動，沒有任何特殊訊息。
與冷備份相比，熱備份是在例項執行期間進行的，應用程式可能在備份進行時更改資料。熱備份有時被稱為 線上備份。PostgreSQL 支援兩種截然不同的熱備份：第一種是純粹的基於 SQL 的版本，第二種是特定於產品的版本。它們將在接下來的兩章中解釋。
邏輯備份是熱備份的兩種形式之一。它包含叢集、單個數據庫或資料庫某些部分中的資料和/或元資料。它們是由 pg_dump 或 pg_dumpall 實用程式建立的。
例項必須執行才能使這些實用程式正常工作。即使它們與其他客戶端並行執行（可能需要更長的時間），它們也會建立資料在開始時間時的精確副本。例如，如果應用程式在此期間更改了一些資料，備份將獲取舊值，而所有其他應用程式都將使用新值進行操作。這是因為 PostgreSQL 的 MVCC（多版本併發控制）實現允許同時存在一個行的多個版本。
pg_dump 在資料庫級別工作，可以備份整個資料庫，也可以備份其某些部分，例如單個表。它能夠轉儲資料、模式定義或兩者。引數 --data-only 和 --schema-only 選擇目標部分。
pg_dump 支援兩種輸出格式：純文字（可讀的純文字格式）和自定義（二進位制格式）。格式型別由引數 --format 選擇。純文字格式包含 SQL 命令，例如 CREATE 和 INSERT。以這種格式建立的檔案可以被 psql 用於恢復備份的資料。自定義格式有時被稱為 存檔格式。要恢復以這種格式建立的檔案，必須使用 pg_restore。
以下圖表顯示了 pg_dump、psql 和 pg_restore 的協作。
一些示例
$ # dump complete database 'finance' in plain-text format to a file
$ pg_dump --dbname=finance --username=boss --format=plain --file=finance.sql
$
$ # restore database content (to a different or an empty database)
$ psql --dbname=finance_x --username=boss <finance.sql
$
$
$
$ # dump table 'person' of database 'finance' in binary format to a file
$ pg_dump --dbname=finance --username=boss --table=person \
--format=custom --file=finance_person.archive
$
$ restore table 'person' from binary file
$ pg_restore --dbname=finance_x --username=boss \
--format=custom <finance_person.archive
$
pg_dumpall 實用程式在叢集級別執行，並內部呼叫 pg_dump 來轉儲叢集中的每個資料庫。此外，它還轉儲叢集級別物件（“全域性物件”），例如使用者/角色及其許可權。如果它在沒有詳細引數的情況下啟動，它將轉儲叢集的完整內容：所有資料庫的所有資料和元資料，以及所有叢集級別物件。引數 --globals-only 可用於將其行為限制為僅轉儲叢集物件。pg_dumpall 的輸出為純文字格式。
► 優點
- 可以實現連續 7x24 執行模式。
- 可以備份或還原叢集或資料庫的小部分。
- 當您使用文字格式時，您可以從一個 PostgreSQL 版本切換到另一個版本，或者從一個硬體平臺切換到另一個平臺。
► 缺點
- 文字格式佔用大量空間，但壓縮效果很好。
► 如何恢復
如上圖所示，恢復過程取決於轉儲的格式。文字檔案採用標準 SQL 語法。要從這些檔案重建資料，您必須使用 psql。具有自定義格式的檔案具有特定於 PostgreSQL 的二進位制結構，只能由 pg_restore 實用程式使用。
這是第二種形式的熱備份。此類備份包括兩部分。第一部分是所謂的基本備份，它包含叢集所有檔案的副本（類似於檔案系統級備份）。第二部分包含從備份命令開始以來的所有資料更改。此類資料更改會隨著進一步的線上活動（在備份生成期間和之後）而持續發生，儲存在 WAL 檔案中，並且必須像第一部分一樣持續儲存（“歸檔”）。
為了瞭解此類備份的目的和技術，瞭解 PostgreSQL 的崩潰恢復策略會有所幫助。在任何時候，並且獨立於任何備份/恢復操作，PostgreSQL 都會維護預寫日誌 (WAL) 檔案，主要用於崩潰安全性目的。此類WAL 檔案包含日誌記錄，這些記錄反映了對資料和模式所做的所有更改。在將更改傳輸到資料檔案之前，日誌記錄會儲存在（順序寫入的）WAL 檔案中。如果系統崩潰，這些日誌記錄將用於在例項重新啟動期間將叢集恢復到一致狀態。恢復過程會在 WAL 檔案中搜索最後一個檢查點的時間戳，並按時間順序將所有後續日誌記錄重播到叢集。透過此操作，叢集將恢復到一致狀態，並將包含所有更改，直到上次提交。
從備份恢復時，總體策略類似於崩潰恢復策略：刪除崩潰叢集的檔案，從基本備份中恢復它們，通知恢復過程（這是例項的組成部分）如何透過作業系統命令訪問已歸檔的 WAL 檔案，然後重新啟動例項。例項的恢復部分將所有日誌記錄從已歸檔的WAL 檔案重播到（已恢復的）資料庫檔案，並將叢集傳輸到一致狀態。此後，叢集將包含所有更改，直到崩潰之前的上次提交。
要實施此備份策略，必須執行三個操作
- 在postgres.conf 中定義所有必要的引數。
- 使用
pg_basebackup實用程式生成基本備份。 - 歸檔所有出現的 WAL 檔案。
如果需要恢復，您必須刪除叢集中的所有檔案，透過將備份複製到其原始位置來重新建立叢集的舊狀態，建立一個特殊檔案（recovery.signal 或recovery.conf，見下文：步驟 3），其中包含一些恢復資訊（尤其是已歸檔 WAL 檔案的位置），然後重新啟動例項。例項將根據其在postgres.conf 和recovery.conf 中的引數，將叢集重新建立到一致狀態，包括所有資料更改，直到上次提交。
► 優點
- 可以實現連續 7x24 執行模式。
- 以最小的資料丟失進行恢復。
- 生成的 WAL 檔案可用於其他功能，例如複製。
► 缺點
- 基本備份僅適用於叢集級別，不適用於任何更細粒度的級別，如資料庫或表。
- 如果您的資料庫非常繁忙，並且客戶端更改了很多資料，可能會出現很多 WAL 檔案。
步驟 1
您必須在postgres.conf 中定義一些引數，以便 WAL 檔案包含足夠的資料，WAL 檔案的歸檔被啟用，並且定義了一個複製命令來將 WAL 檔案傳輸到一個安全位置。
# collect enough information in WAL files wal_level = 'replica' # activate ARCHIVE mode so that WAL files will be archived by the instance archive_mode = on # supply a system command to transfer WAL files to a failsafe location (cp, scp, rsync, ...) # %p represents the pathname including filename. %f represents the filename only. archive_command = 'scp %p dba@archive_server:/postgres/wal_archive/%f'
定義引數後，您必須重新啟動叢集：pg_ctl restart。叢集將在其子目錄pg_wal（在 Postgres 9.x 及更早版本中為pg_xlog）中持續生成 WAL 檔案，以配合資料庫中的資料更改。當它已填充一個 WAL 檔案並且必須切換到下一個 WAL 檔案時，它會將舊檔案複製到定義的歸檔位置。
步驟 2
您必須使用 bg_basebackup 實用程式建立所謂的基本備份。
$ # take a copy (base backup) of the files of the cluster with the pg_basebackup utility
$ pg_basebackup --pgdata=/safe_drive/backup/
$
步驟 3
就是這樣。所有其他活動都由例項執行，尤其是將完全填充的 WAL 檔案持續複製到歸檔位置。
要執行恢復，原始的基本備份會被複制回來，並且例項被配置為在啟動時執行恢復。
- 停止例項 - 如果它仍在執行。
- 建立崩潰叢集的副本 - 如果您的磁碟空間足夠。也許您在以後的階段會需要它。
- 刪除崩潰叢集的所有檔案。
- 從基本備份中重新建立叢集檔案。
- 在 $PGDATA 中建立一個特殊檔案
- 12 版之前的 PostgreSQL：在 $PGDATA 中建立一個名為recovery.conf 的檔案。它必須包含類似於以下命令的命令：restore_command = 'scp dba@archive_server:/postgres/wal_archive/%f %p'。此複製命令與postgres.conf 中的命令相反，該命令將 WAL 檔案儲存到歸檔位置。
- 12 版及之後的 PostgreSQL：在 $PGDATA 中建立一個名為recovery.signal 的空檔案。在postgres.conf 中新增類似於以下命令的命令：restore_command = 'scp dba@archive_server:/postgres/wal_archive/%f %p'。此複製命令與postgres.conf 中的命令相反，該命令將 WAL 檔案儲存到歸檔位置。
- 啟動例項。在啟動期間，例項會複製並處理在歸檔位置中找到的所有 WAL 檔案。
recovery.signal 或recovery.conf 的存在表明例項需要執行恢復。恢復成功後，此檔案將被重新命名。
如果您想恢復到崩潰發生之前（但在備份建立之後）的某個時間點，則可以透過指定該時間點來實現。在這種情況下，恢復過程將在處理所有已歸檔的 WAL 檔案之前停止。此功能是時間點恢復一詞的來源。
總而言之，用於恢復的兩個關鍵命令（在recovery.conf 或postgres.conf 中）可能如下所示
restore_command = 'scp dba@archive_server:/postgres/wal_archive/%f %p' recovery_target_time = '2021-01-31 06:00:00 CET'
有一個開源專案Barman，它簡化了備份和恢復步驟。如果您必須管理許多伺服器和例項，並且配置和記住有關伺服器環境的所有詳細資訊變得很複雜，Barman 會儲存配置詳細資訊並自動執行程序。