使用傾印和還原來移轉 PostgreSQL 資料庫

適用於： 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

您可以使用 pg_dump 將 PostgreSQL 資料庫擷取到傾印檔案中。 還原資料庫的方式取決於您選擇的備份格式。 如果您的備份採用純文字格式 (這是預設 -Fp，因此不需要指定特定選項)，則還原的唯一選項是使用 psql，因為會作為純文字檔輸出。 若為其他三個備份方法：自訂、目錄和 tar，則應使用 pg_restore。

重要

本文中提供的指示和命令是設計在 Bash 終端中執行。 這包含 Windows 子系統 Linux 版 (WSL)、Azure Cloud Shell 和其他 Bash 相容介面等環境。 請確定您使用 Bash 終端以遵循步驟並執行本指南詳述的命令。 使用不同類型的終端或殼層環境可能會導致命令行為的差異，而且可能不會產生預期的結果。

在本文中，我們將著重於純文字 (預設) 和目錄格式。 目錄格式可提供幫助，因為可讓您使用多個核心進行處理，進而大幅提升效率，尤其適用於大型資料庫。

Azure 入口網站透過 [連線] 刀鋒視窗簡化此程序，其中提供針對伺服器量身打造的預先設定命令，並將值取代為使用者資料。 請務必注意，[連線] 刀鋒視窗僅可供適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器使用，單一伺服器則無法使用。 以下是使用此功能的方式：

1. 存取 Azure 入口網站：首先移至 Azure 入口網站並選擇 [連線] 刀鋒視窗。

   

2. 選取您的資料庫：在 [連線] 刀鋒視窗中，您會找到資料庫的下拉式清單。 選取您要從中執行備份的資料庫。

   

3. 選擇適當的方法：根據資料庫大小，您可在兩種方法之間進行選擇：

   • pg_dump 與 psql - 使用單一文字檔：適用於較小的資料庫，此選項使用單一文字檔進行備份和還原程序。
   • pg_dump 與 pg_restore - 使用多個核心：針對較大的資料庫，此方法將提高效率，因為可使用多個核心以處理備份和還原程序。

   

4. 複製並貼上命令：入口網站為您提供隨時可用的 pg_dump 和 psql 或 pg_restore 命令。 這些命令隨附根據所選伺服器和資料庫取代的值。 複製並貼上這些命令。

必要條件

如果您使用單一伺服器或沒有彈性伺服器入口網站的存取權，請閱讀本文件頁面。 其中包含相關訊息，與入口網站上彈性伺服器 [連線] 刀鋒視窗所呈現的內容相似。

注意

因為pg_dump、 psqlpg_restore 和 pg_dumpall 公用程式全都依賴 libpq，所以您可以使用它所提供的任何支援的環境變數，也可以使用密碼檔案，以避免每次執行上述任何命令時都提示輸入密碼。

若要逐步執行本作法指南，您需要︰

• 適用於 PostgreSQL 的 Azure 資料庫伺服器，包含防火牆規則允許的存取權。
• pg_dump、psql、pg_restore 和 pg_dumpall，若您要移轉 (包含角色和權限)，已安裝命令列公用程式。
• 決定備份的位置：選擇您要執行備份的位置。 備份可以從各種位置執行，例如個別 VM、雲端殼層 (其中已安裝命令列公用程式，但可能未安裝於適當版本，因此請一律檢查使用的版本，例如 psql --version) 或您自己的筆記型電腦。 請記住，PostgreSQL 伺服器和執行備份或還原的位置之間的距離和延遲。

重要

請務必使用相同主要版本 pg_dump、psql、pg_restore 和 pg_dumpall 公用程式，或者使用比匯出資料來源或匯入資料目標資料庫伺服器更高的主要版本。 若無法進行，則可能會導致資料移轉失敗。 如果目標伺服器的主要版本高於來源伺服器，請使用相同版本或高於目標伺服器的公用程式。

注意

請務必注意，pg_dump 一次僅可匯出一個資料庫。 無論選擇的方法為何或使用單一檔案或多個核心，此限制皆適用。

使用 pg_dumpall -r 備份使用者和角色

您可使用 pg_dump 以將 PostgreSQL 資料庫擷取到備份檔案中。 然而，請務必了解 pg_dump 不會備份角色或使用者定義，因為這些項目視為 PostgreSQL 環境中的全域物件。 針對完整移轉 (包含使用者和角色)，您必須使用 pg_dumpall -r。 此命令可讓您從 PostgreSQL 環境擷取所有角色和使用者資訊。 如果您要在相同伺服器的資料庫內移轉，請跳過此步驟並移至建立新的資料庫一節。

pg_dumpall -r -h <server name> -U <user name> > roles.sql

例如，如果您有名為 mydemoserver 的伺服器和名為 myuser 的使用者，請執行下列命令：

pg_dumpall -r -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

如果您使用單一伺服器，使用者名稱會包含伺服器名稱元件。 因此，請使用 myuser@mydemoserver 而不是 myuser。

從彈性伺服器備份角色

在彈性伺服器環境中，增強型安全性措施表示使用者無法存取 pg_authid 資料表，這是儲存角色密碼的位置。 此限制影響執行角色備份的方式，因為標準 pg_dumpall -r 命令將嘗試存取此密碼資料表並因缺少權限而失敗。

當從彈性伺服器備份角色時，請務必在 pg_dumpall 命令中包含 --no-role-passwords 選項。 此選項可防止 pg_dumpall 嘗試存取 pg_authid 資料表，因為安全性限制而無法讀取。

若要從彈性伺服器成功備份角色，請使用下列命令：

pg_dumpall -r --no-role-passwords -h <server name> -U <user name> > roles.sql

例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者，請執行下列命令：

pg_dumpall -r --no-role-passwords -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

清除角色備份

移轉輸出檔案時，roles.sql 可能包含新環境不適用或允許的特定角色和屬性。 以下是您需要考量的事項：

• 移除僅可由超級使用者設定的屬性：如果移轉至沒有超級使用者權限的環境，請從角色備份中移除 NOSUPERUSER 和 NOBYPASSRLS 等屬性。

• 排除服務特定使用者：排除單一伺服器服務使用者，例如 azure_superuser 或 azure_pg_admin。 這些使用者是服務特定，並將在新環境中自動建立。

使用下列 sed 命令以清除角色備份：

sed -i '/azure_superuser/d; /azure_pg_admin/d; /azuresu/d; /^CREATE ROLE replication/d; /^ALTER ROLE replication/d; /^ALTER ROLE/ {s/NOSUPERUSER//; s/NOBYPASSRLS//;}' roles.sql

此命令會刪除包含 azure_superuser、azure_pg_admin、azuresu 的行、開頭為 CREATE ROLE replication 和 ALTER ROLE replication 的行，並從 ALTER ROLE 陳述式移除 NOSUPERUSER 和 NOBYPASSRLS 屬性。

建立傾印檔案，其中包含要載入的資料

若要將內部部署或 VM 中的現有 PostgreSQL 資料庫匯出至 sql 指令碼檔，請在您現有的環境中執行下列命令：

pg_dump 與 psql - 使用單一文字檔

pg_dump <database name> -h <server name> -U <user name> > <database name>_dump.sql

例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者和名為 testdb的資料庫，請執行下列命令：

pg_dump testdb -h mydemoserver.postgres.database.azure.com -U myuser > testdb_dump.sql

pg_dump 與 pg_restore - 使用多個核心

pg_dump -Fd -j <number of cores> <database name> -h <server name> -U <user name> -f <database name>.dump

在這些命令中，-j 選項表示您要用於備份程序的核心數目。 您可根據 PostgreSQL 伺服器可用的核心數目及您要針對備份程序配置的核心數目以調整此數字。 請根據伺服器容量和效能需求隨意變更此設定。

例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者和名為 testdb的資料庫並想要使用兩個核心進行備份，請執行下列命令：

pg_dump -Fd -j 2 testdb -h mydemoserver.postgres.database.azure.com -U myuser -f testdb.dump

如果您使用單一伺服器，使用者名稱會包含伺服器名稱元件。 因此，請使用 myuser@mydemoserver 而不是 myuser。

將資料還原至目標資料庫

還原角色和使用者

還原資料庫物件前，請確保您已正確備份和清除角色。 如果您在相同伺服器的資料庫內移轉，則可能不需要備份角色和還原角色。 不過，針對在不同伺服器或環境的移轉，請務必進行此步驟。

若要將角色和使用者還原至目標資料庫，請使用下列命令：

psql -f roles.sql -h <server_name> -U <user_name>

將 <server_name> 取代為目標伺服器的名稱，並將 <user_name> 取代為使用者名稱。 此命令會使用 psql 公用程式以執行 roles.sql 檔案中包含的 SQL 命令，將角色和使用者有效還原至目標資料庫。

例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者，請執行下列命令：

psql -f roles.sql -h mydemoserver.postgres.database.azure.com -U myuser

如果您使用單一伺服器，使用者名稱會包含伺服器名稱元件。 因此，請使用 myuser@mydemoserver 而不是 myuser。

注意

如果在所移轉的單一伺服器或內部部署伺服器及目標伺服器上已有相同名稱的使用者，請注意，此還原程序可能會變更這些角色的密碼。 因此，您需要執行的後續命令可能需要更新的密碼。 如果您的來源伺服器是彈性伺服器，則不適用，因為彈性伺服器不允許因增強型安全性措施而備份使用者的密碼。

建立新的 資料庫

還原資料庫前，您可能必須建立新的空白資料庫。 若要執行此動作，使用的使用者必須有 CREATEDB 權限。 以下是兩個常用的方法：

1. 使用 createdb 公用程式：createdb 程式可讓您直接從 Bash 命令列建立資料庫，而不需要登入 PostgreSQL 或離開作業系統環境。 例如：

   createdb <new database name> -h <server name> -U <user name>
   

   例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者和您要建立的新資料庫為 testdb_copy，請執行下列命令：

   createdb testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser
   

   如果您使用單一伺服器，使用者名稱會包含伺服器名稱元件。 因此，請使用 myuser@mydemoserver 而不是 myuser。

2. 使用 SQL 命令：若要使用 SQL 命令建立資料庫，您必須透過命令列介面或資料庫管理工具連線至 PostgreSQL 伺服器。 連線之後，您可使用下列 SQL 命令以建立新的資料庫：

CREATE DATABASE <new database name>;

將 <new database name> 取代為您要命名新資料庫的名稱。 例如，若要建立名為 testdb_copy 的資料，命令如下：

CREATE DATABASE testdb_copy;

還原備份

在建立目標資料庫後，您可從備份檔案將資料還原至此資料庫。 在還原起間，請將錯誤記錄至 errors.log 檔案，並在還原完成後檢查其內容是否有任何錯誤。

pg_dump 與 psql - 使用單一文字檔

psql -f <database name>_dump.sql <new database name> -h <server name> -U <user name> 2> errors.log

例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者和名為 testdb_copy 的新資料庫，請執行下列命令：

psql -f testdb_dump.sql testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log

pg_dump 與 pg_restore - 使用多個核心

pg_restore -Fd -j <number of cores> -d <new database name> <database name>.dump -h <server name> -U <user name> 2> errors.log

在這些命令中，-j 選項表示您要用於還原程序的核心數目。 您可根據 PostgreSQL 伺服器可用的核心數目及您要針對還原程序配置的核心數目以調整此數字。 請根據伺服器容量和效能需求隨意變更此設定。

例如，如果您有名為 mydemoserver 的伺服器、名為 myuser 的使用者和名為 testdb_copy的新資料庫並想要使用兩個核心進行備份，請執行下列命令：

pg_restore -Fd -j 2 -d testdb_copy testdb.dump -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log

還原後的檢查

還原程序完成後，請務必檢閱 errors.log 檔案是否可能已發生錯誤。 此步驟為必要，可確保還原資料的完整性和完全度。 解決記錄檔中的問題以維護資料庫的可靠性。

將移轉流程最佳化

使用大型資料庫時，備份和還原程序可能相當冗長並需要最佳化以確保效率和可靠性。 請務必注意可能影響這些作業效能的各種因素，並採取步驟以最佳化。

如需最佳化備份和還原程序的詳細指引，請參閱 pg_dump and pg_restore 的最佳做法一文。 此資源提供完整的資訊和策略，有助於處理大型資料庫。

下一步

• pg_dump 和 pg_restore 的最佳做法。
• 如需有關將資料庫移轉至適用於 PostgreSQL 的 Azure 資料庫詳細資訊，請參閱資料庫移轉指南。