如何使用pg_dump備份單個資料庫 - PolarDB

pg_dump是PolarDB PostgreSQL版（相容Oracle）提供的一種邏輯備份工具，用於將叢集中的單個Database Backup為指令檔或其他封存檔案。

簡介

pg_dump用於備份單個資料庫。即使當前資料庫正在被訪問，也會對正在訪問的資料進行一致的備份，並且在執行備份時不會阻止其他使用者訪問資料庫（讀取或寫入）。更多詳情請參見pg_dump官網。

說明

PolarTools中的pg_dump與社區版本的pg_dump存在差異，PolarTools中的pg_dump針對PolarDB PostgreSQL版（相容Oracle）進行了適配。使用社區版本的pg_dump可能會出現未知異常或備份的對象資料不完整等情況。

備份檔案格式

指令檔：指令檔為純文字檔案，其中包含將資料庫重建到備份時狀態的SQL命令。
封存檔案：封存檔案必須和pg_restore配合使用來重建資料庫。
輸出檔案格式主要包括自訂格式-Fc、目錄格式-Fd和tar格式的封存檔案-Ft。其中-Fc和-Fd允許對所有存檔專案進行選擇和重新排序，且預設為壓縮格式的檔案。-Ft不是壓縮檔，並且我們在恢複資料時不支援重新排序。
說明
目錄格式是唯一支援並行備份的格式。
當與其中一種封存檔案結合使用時，pg_dump可用於備份整個資料庫。pg_restore可用於檢查存檔或選擇哪些部分需要被恢複。

文法

pg_dump [connection-option...] [option...] [dbname]

表 1. 參數說明

參數	描述
connection-option	控制資料庫連接參數的命令列選項。詳情請參見connection-option（控制資料庫連接參數命令列選項）。
option	控制輸出內容和格式的命令列選項。詳情請參見option（控制輸出內容和格式的命令列選項）。
dbname	需要備份的資料庫名稱。

表 2. connection-option（控制資料庫連接參數命令列選項）

命令列選項	描述
-d dbname即--dbname=dbname	指定要串連的資料庫的名稱。
-h host即--host=host	指定運行伺服器的電腦的主機名稱。如果該值以斜杠開頭，則將其用作Unix域通訊端的目錄，預設值為PGHOST環境變數。
-p port即--port=port	指定伺服器正在偵聽串連的TCP連接埠或本地Unix域通訊端副檔名。預設放在PGPORT環境變數中，否則使用程式中的預設值。
-U username即--username=username	需要串連的使用者名稱。
-w即--no-password	表示pg_dump在串連資料庫時不提示輸入密碼。
-W即--password	表示pg_dump在串連資料庫時提示輸入密碼。說明此選項為非必選項。
--role=rolename	指定用於建立備份的角色名稱。

表 3. option（控制輸出內容和格式的命令列選項）

命令列選項	描述
dbname	需要備份的資料庫的名稱。如果未指定，則使用環境變數PGDATABASE。
-a即--data-only	只備份資料，不備份結構。說明此選項備份表資料、大型物件和序列值。
-b即--blobs	備份時預設包含大對象。當指定了--schema、-table或--schema only時，備份將不包含大對象。重要 blob被視為資料，因此在備份時使用--data only將包含資料，使用--schema only則不包含資料。
-B即--no-blobs	排除備份中的大對象。說明當同時出現-b和-B時，備份將包含大對象。
-c即--clean	在執行建立資料庫物件命令之前刪除資料庫物件。為避免在恢複資料庫時產生錯誤資訊，建議在恢複資料庫時指定--if exists。說明此選項僅適用於指令檔。對於封存檔案，可以在調用pg_restore時指定該選項。
-C即--create	建立資料庫並重新串連到新建立的資料庫。如果指定了--clean，則指令碼將刪除並重新建立目標資料庫，然後再重新串連到該資料庫。如果指定了--create命令列，且未指定--no-acl。備份資料時將包含資料庫的注釋、資料庫的配置資訊以及資料庫本身的存取權限資訊。說明此選項僅適用於指令檔。對於封存檔案，可以在調用pg_restore時指定該選項。
-E encoding即--encoding=encoding	以指定的字元集編碼建立備份。預設情況下，以當前需要備份的資料庫編碼建立備份，您也可以將環境變數PGCLIENTENCODING的值設定為想要的備份編碼。
-F format即--format=format	備份格式。可選以下任一格式： p即plain：輸出指令檔（預設）。 c即custom：輸出適合作為pg_restore輸入的自訂格式封存檔案。預設情況下，此格式的封存檔案是壓縮檔。 d即directory：輸出適合作為pg_restore輸入的目錄格式的封存檔案。預設情況下，此格式的封存檔案是壓縮檔，並且支援並行備份。 t即tar：輸出適合作為pg_restore輸入的tar格式的封存檔案。tar格式的封存檔案不支援壓縮。另外，當使用tar格式時，在恢複資料期間無法更改表資料項目的相對順序。
-f file即--file=file	將備份資料發送到指定的檔案。基於目錄備份格式時，此參數必填，且指定的是目標目錄而不是檔案。基於其他檔案備份格式時，此參數可省略，預設使用標準備份檔案格式。
-j njobs即--jobs=njobs	通過同時備份njobs個表來並行運行備份。需要將此選項與目錄格式結合使用。說明並行備份之前，需要停止修改資料庫的進程（DDL和DML）。
-n pattern即--schema=pattern	僅備份與pattern匹配的模式。如果未指定此選項，將備份目標資料庫中的所有非系統模式。說明當指定-n時，pg_dump不會備份所選模式可能依賴的任何其他資料庫物件。因此，無法保證備份特定模式的資料後，能夠成功地將資料恢複到空資料庫中。當指定-n時，pg_dump不會備份非模式對象（如Blob）的資料。如果需要備份這部分資料，可以在備份時添加 --blobs命令列選項。
-N pattern即--exclude-schema=pattern	備份除pattern之外的其他模式。說明如果-n和-N同時出現，備份時至少匹配一個-n模式而不匹配-N模式，如果只出現-N，備份時將排除與-N匹配的模式。
-o即--oids	備份物件識別碼（OID）作為每個表資料的一部分。如果您的應用程式以某種方式引用OID列（例如在一個外鍵約束中引用OID列），請使用此選項。否則，不使用此選項。
-O即--no-owner	匹配未經處理資料庫時，不需要設定對象的所有權。說明此選項僅適用於指令檔。對於封存檔案，可以在調用pg_restore時指定該選項。
-s即--schema-only	只備份對象模式，不備份資料。
-S username即--superuser=username	禁用觸發器時使用的超級使用者名稱。僅在使用--disable-triggers時才使用該選項。
-t pattern即--table=pattern	只備份與pattern匹配的表。可以通過編寫多個-t來選擇多張表，也可以通過在模式中寫入萬用字元來選擇多張表。說明指定-t時，pg_dump不會嘗試備份所選表可能依賴的任何其他資料庫物件。因此，不能保證備份特定表後能夠成功地將資料恢複到空資料庫中。
-T pattern即--exclude-table=pattern	不備份匹配pattern的表。可以多次指定-T來排除與多個模式比對的表。說明當同時給出-t和-T時，備份時至少匹配一個-t而不匹配-T的表。如果只出現-T，備份時將排除與-T匹配的表。
-v即--verbose	指定詳細模式。
-V即--version	列印pg_dump版本並退出。
-x即--no-privileges或--no-acl	取消備份存取權限（grant或revoke命令）。
-Z 0..9即--compress=0..9	指定需要使用的壓縮層級。0表示未壓縮。說明對於自訂封存檔案，則指定單個表資料區段壓縮，且預設為中等層級的壓縮。對於指令檔，預設不壓縮。當設定非零壓縮層級時，會導致整個輸出檔案被壓縮。
--column-inserts和--attribute-inserts	將資料備份為具有顯式列名的`INSERT`命令。 `INSERT INTO table (column, ...) VALUES ...`
--disable-dollar-quoting	禁止對函數體使用美元引號。
--disable-triggers	臨時禁用目標表上的觸發器。此選項僅在建立資料備份時有效。使用此選項時，需要通過-S指定一個超級使用者。說明此選項僅適用於指令檔。對於封存檔案，可以在調用pg_restore時指定該選項。
--enable-row-security	允許備份您有許可權訪問的表的部分內容。此選項僅在備份具有行安全性的表的內容時才有效。重要如果使用此選項，則可能還需要使用INSERT 進行備份，因為恢複資料期間的COPY FROM不支援行安全性。
--exclude-table-data=pattern	不備份與pattern匹配的表資料。可以多次指定--exclude-table-data來排除與多個模式比對的表。說明如果需要排除資料庫中所有表的資料，請參見--schema only。
--if-exists	使用條件命令（例如，添加IF EXISTS子句）清理資料庫物件。使用時需要同時指定--clean，否則此選項無效。
--inserts	將資料備份為INSERT命令。重要使用此選項後，如果在恢複資料時對資料進行重新排序可能會執行失敗。建議使用--column-inserts 。
--load-via-partition-root	備份表分區的資料時，使用COPY或INSERT命令。說明當恢複使用此選項建立的封存檔案時，建議謹慎使用並行恢複。
--lock-wait-timeout=timeout	指定擷取共用鎖定的等待時間。
--no-comments	表示不備份注釋。
--no-publications	表示不備份出版物。
--no-security-labels	表示不備份安全性標籤。
--no-subscriptions	表示不備份訂閱。
--no-sync	表示不需要等待所有檔案均安全寫入磁碟即返回資料。
--no-synchronized-snapshots	表示在伺服器上可以運行pg_dump -j。
--no-tablespaces	表示在預設的資料表空間中建立所有對象。說明此選項僅適用於指令檔。對於封存檔案，可以在調用pg_restore時指定該選項。
--no-unlogged-table-data	表示不備份未標記表的內容。
--quote-all-identifiers	表示強制引用所有標識符。
--rows-per-insert=nrows	控制Database Backup為INSERT命令時的最大行數。
--section=sectionname	表示只備份指定的section。section的名稱可以是pre-data、data或post-data，可以多次指定此選項以選擇多個section，預設備份所有section。說明 data項包含實際的表資料、大型物件內容和序列值。 post-data項包括索引、觸發器、規則和約束的定義，而不是已驗證的檢查約束。 pre-data項包括所有其他資料定義項。
--serializable-deferrable	表示對備份使用一個可序列化事務。說明如果使用pg_dump時，讀寫事務處於活動狀態，則可能會延遲一段不確定的時間才開始執行備份操作。如果使用pg_dump時，讀寫事務沒有處於活動狀態，則此選項不會產生任何影響。
--snapshot=snapshotname	表示在備份資料庫時使用指定的同步快照。
--strict-names	表示每個模式（-n/ --schema）和表（-t/ --table）限定符至少要匹配備份的資料庫中的一個模式和表。說明如果模式和表限定符沒有找到匹配項，pg_dump即使沒有--strict-names，也會建置錯誤。此選項對-N/--exclude schema、-T/--exclude table或--exclude table data無效。無法匹配任何對象的排除模式不會被當作錯誤。
--use-set-session-authorization	表示輸出SQL標準的SET SESSION AUTHORIZATION命令而不是ALTER OWNER命令。
-?即--help	顯示關於pg_dump命令列的協助資訊。

注意事項

當選擇了僅用作資料備份且使用了--disable triggers選項的表時，pg_dump會在插入資料之前發出禁用使用者表觸發器的命令，插入資料後發出重新啟用觸發器的命令。如果恢複中途被停止，系統目錄可能會停留在一種錯誤狀態。
備份檔案恢複後，可運行ANALYZE確保最佳效能。
當備份邏輯複製訂閱時，pg_dump將產生使用connect=false選項的CREATE SUBSCRIPTION命令。如果涉及的主機已更改，則可能需要更改串連資訊，在啟動新的完整表拷貝之前需截斷目標表。
pg_dump在內部執行SELECT語句時，如果運行pg_dump遇到問題，需確保能夠使用psql等工具從資料庫中查詢資訊。此外，確保libpq前端庫使用的任何預設串連設定和環境變數都能正常使用。
pg_dump的資料庫活動通常由統計收集器收集，如果不需要收集，可以通過PGOPTIONS或ALTER USER命令將參數track_counts設定為false。

樣本

執行以下命令，將名稱為mydb的Database Backup到SQL指令檔中：
```
pg_dump mydb > db.sql
```
執行以下命令，將SQL指令碼重新載入到名稱為newdb的（新建立的）資料庫中：
```
psql -d newdb -f db.sql
```
執行以下命令，將Database Backup到自訂格式的封存檔案中：
```
pg_dump -Fc mydb > db.dump
```
執行以下命令，將Database Backup到目錄格式的封存檔案中：
```
pg_dump -Fd mydb -f dumpdir
```
執行以下命令，將資料庫與5個輔助作業並行備份到目錄格式的封存檔案中：
```
pg_dump -Fd mydb -j 5 -f dumpdir
```
執行以下命令，將封存檔案重新載入到名稱為newdb的（新建立的）資料庫中：
```
pg_restore -d newdb db.dump
```
執行以下命令，將封存檔案重新載入到備份檔案的同一資料庫中，並清除該資料庫的當前內容：
```
pg_restore -d postgres --clean --create db.dump
```
執行以下命令，備份名稱為mytab的單個表：
```
pg_dump -t mytab mydb > db.sql
```
執行以下命令，備份detroit模式中名稱以emp開頭的所有表（名稱為employee_log的表除外）：
```
pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
```
執行以下命令，備份名稱以east或west開頭並以gsm結尾的所有模式（不包括名稱中含test的任何模式）：
```
pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
```
執行以下命令，使用Regex標記法來合并開關：
```
pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
```
執行以下命令，備份除名稱以ts_開頭的表以外的所有資料庫物件：
```
pg_dump -T 'ts_*' mydb > db.sql
```
如果需要在-t和相關開關中指定大寫或混合大小寫名稱，您需要通過雙引號引用該名稱，否則它將被摺疊為小寫。但是雙引號對於Shell命令來說是特殊的，因此必須反過來引用它們。因此，如果要轉儲大小寫混合名稱的單個表，請執行以下命令：
```
pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql
```