使用 pg_dump、pg_dumpall 和 pg_restore 匯出及匯入

本頁說明如何使用 pg_dump、pg_dumpall 和 pg_restore,將資料匯出及匯入 Cloud SQL 執行個體。

事前準備

匯出作業會使用資料庫資源,但除非執行個體資源不足,否則不會干擾正常的資料庫作業。

如需最佳做法,請參閱匯入及匯出資料的最佳做法

完成匯入作業後,請驗證結果。

進一步瞭解 pg_dumppg_dumpallpg_restore 公用程式。

從 PostgreSQL 適用的 Cloud SQL 匯出資料

您可以使用 Cloud SQL,透過 Google Cloud 控制台、gcloud CLI 或 API 執行匯出作業。

  • 如要匯出單一 PostgreSQL 資料庫,請使用 pg_dump 公用程式。
  • 如要匯出叢集的所有 PostgreSQL 資料庫,請使用 pg_dumpall 公用程式。

使用任一公用程式時,請務必一併使用必要選項,確保產生的匯出檔案可有效匯入回 Cloud SQL。

使用 pg_dump 從內部部署 PostgreSQL 伺服器匯出資料

如要匯出非 Cloud SQL 管理的資料庫,以便稍後匯入 Cloud SQL,請使用 pg_dump 公用程式,並加上下列旗標:

  • --no-owner

    傾印檔案不得包含擁有權變更指令。

  • --format

    如果傾印檔案要搭配 pg_restore 使用,則允許 customdirectory 格式。

    如要匯出 plain-text 格式,請改為匯出至 SQL dump file。這個格式與 pg_restore 不相容,必須使用 Google Cloud 控制台匯入指令或 psql 用戶端匯入。

  • --no-acl

    如果您的傾印包含授予或撤銷 SUPERUSER 角色成員資格的陳述式,則需要這個標記。

  • --clean

    這個選用旗標可讓您在匯入資料庫物件前,加入刪除 (清除) 這些物件所需的 DROP <object> SQL 陳述式。

  • --if-exists

    這個選用標記可讓您在 clean 標記產生的每個 DROP 陳述式中,加入 IF EXISTS SQL 陳述式。

此外,您必須移除下列所有項目:

  • 如果 Cloud SQL 不支援該擴充功能,則與擴充功能相關的陳述式。 如需支援的擴充功能清單,請參閱「PostgreSQL 擴充功能」。
  • CREATE EXTENSIONDROP EXTENSION 陳述式,參照 plpgsql。這項擴充功能已預先安裝在 Cloud SQL Postgres 執行個體上。
  • COMMENT ON EXTENSION 陳述式來變更這些使用者的權限。

確認資料庫設定決定的預設編碼是否適合您的資料。如有需要,您可以使用 --encoding 旗標覆寫預設值。

從 PostgreSQL 適用的 Cloud SQL 匯出 custom 格式的資料

如要使用自訂格式,請從指令列執行 pg_dump

pg_dump \
-U USERNAME \
--format=custom \
--no-owner \
--no-acl \
DATABASE_NAME > DATABASE_NAME.dmp

從 PostgreSQL 適用的 Cloud SQL 平行匯出多個檔案的資料

您只能使用 directory 輸出格式平行匯出多個檔案的資料

如要平行匯出,請使用 -j NUM_CORES 標記。 NUM_CORES 是來源執行個體上的核心數量。

匯出所有資料庫

pg_dumpall 是一種公用程式,可將叢集的所有 PostgreSQL 資料庫擷取至單一指令碼檔案。這個檔案包含可用於還原資料庫的 SQL 指令。

如要匯出 Cloud SQL 執行個體中的所有 PostgreSQL 資料庫,請使用 pg_dumpall 公用程式,並加上下列必要標記:

  • exclude-database=cloudsqladmin
  • exclude-database=template*

pg_dumpall 公用程式無法存取 cloudsqladmintemplate 資料庫。

如要匯出所有 PostgreSQL 資料庫,請執行下列指令:

pg_dumpall \
-h HOST_NAME -l DATABASE_NAME --exclude-database=cloudsqladmin \
--exclude-database=template* > pg_dumpall.sql

如要在使用 pg_dumpall 傾印角色時查看角色密碼,請將 cloudsql.pg_authid_select_role 旗標設為 PostgreSQL 角色名稱。如果角色存在,則具有 pg_authid 資料表的唯讀 (SELECT) 存取權。這張表格含有角色密碼。

匯入

使用 pg_restore 公用程式將封存檔匯入 Cloud SQL 資料庫。pg_restore 僅適用於以 customdirectory 格式,透過 pg_dump 建立的封存檔。進一步瞭解 pg_restore

將以 custom 格式建立的傾印檔案匯入 PostgreSQL 適用的 Cloud SQL

如果傾印檔案是以自訂格式建立,請執行下列指令:

pg_restore \
--list DATABASE_NAME.dmp | sed -E 's/(.* EXTENSION )/; \1/g' >  DATABASE_NAME.toc

後續處理會從 sed 註解中,註銷 SQL 傾印檔案中的所有擴充功能陳述式。

使用 pg_restore 匯入時,請使用指令列引數「--use-list=DATABASE_NAME.toc」指定已處理的目錄。

將多個檔案中的資料平行匯入 PostgreSQL 適用的 Cloud SQL

只有使用 directorycustom 輸出格式建立的封存檔,才能平行匯入多個檔案的資料

如要平行匯入,請使用 -j NUM_CORES 旗標。 NUM_CORES 是目標執行個體上的核心數量。

PostgreSQL 適用的 Cloud SQL 的匯入效能

後續步驟