從 PostgreSQL 遷移至 Spanner (GoogleSQL 方言)

本頁面提供將開放原始碼 PostgreSQL 資料庫遷移至 Spanner 的指南。

遷移作業包括下列工作：

• 將 PostgreSQL 結構定義對應至 Spanner 結構定義。
• 建立 Spanner 執行個體、資料庫和結構定義。
• 重構應用程式，以便與 Spanner 資料庫搭配使用。
• 遷移資料。
• 驗證新系統並將其移至實際運作狀態。

本頁面也提供一些使用 MusicBrainz PostgreSQL 資料庫中資料表的範例結構定義。

將 PostgreSQL 結構定義對應至 Spanner

如果要將資料庫從 PostgreSQL 移至 Spanner，首先要決定哪些結構定義必須變更。使用 pg_dump 建立資料定義語言 (DDL) 陳述式，以定義 PostgreSQL 資料庫中的物件，然後按照下列章節所述修改陳述式。更新 DDL 陳述式後，請使用這些陳述式在 Spanner 執行個體中建立資料庫。

資料類型

下表說明 PostgreSQL 資料類型和 Spanner 資料類型之間的對應關係。將 DDL 陳述式中的資料類型從 PostgreSQL 資料類型更新為 Spanner 資料類型。

主要金鑰

針對您在 Spanner 資料庫中經常執行附加操作的資料表，請避免使用單調增加或減少的主鍵，因為此方法會在寫入時造成資源使用率不均。請改為修改 DDL CREATE TABLE 陳述式，讓其使用支援的主鍵策略。如果您使用 PostgreSQL 功能 (例如 UUID 資料類型或函式、SERIAL 資料類型、IDENTITY 欄或序列)，可以使用我們建議的自動產生的主要遷移策略。

請注意，指定主鍵後，除非刪除並重新建立資料表，否則無法新增或移除主鍵資料欄，也無法稍後變更主鍵值。如要進一步瞭解如何指定主鍵，請參閱「結構定義和資料模型 - 主鍵」。

遷移期間，您可能需要保留一些現有的單調增加整數索引鍵。如果您必須將這些類型的索引鍵保存於經常更新的資料表上，並且會對這些索引鍵執行大量操作，那麼您可以在現有索引鍵前面加上虛擬隨機號碼，以避免造成資源使用率不均。這個技巧會讓 Spanner 重新分配資料列。如要進一步瞭解如何使用此方法，請參閱 DBA 必須瞭解的 Spanner 知識 (第 1 部分)：索引鍵與索引。

外鍵和參考完整性

瞭解 Spanner 中的外鍵支援功能。

索引

PostgreSQL B-Tree 索引類似 Spanner 中的次要索引。在 Spanner 資料庫中，您可以使用次要索引為經常搜尋的資料欄建立索引，如此可提升效能並取代資料表中指定的任何 UNIQUE 限制。比方說，如果您的 PostgreSQL DDL 有下方這個陳述式：

   CREATE TABLE customer (
      id CHAR (5) PRIMARY KEY,
      first_name VARCHAR (50),
      last_name VARCHAR (50),
      email VARCHAR (50) UNIQUE
     );

那麼您就應該在 Spanner DDL 中使用下列陳述式：

   CREATE TABLE customer (
      id STRING(5),
      first_name STRING(50),
      last_name STRING(50),
      email STRING(50)
      ) PRIMARY KEY (id);

    CREATE UNIQUE INDEX customer_emails ON customer(email);

您可以在 psql 中執行 \di 元指令，找出任何 PostgreSQL 資料表的索引。

決定好您所需的索引後，即可新增 CREATE INDEX 陳述式予以建立。請遵循「建立索引」的指示操作。

Spanner 會將索引實作為資料表，因此為單調增加的資料欄 (例如包含 TIMESTAMP 資料的資料欄) 建立索引可能會造成資源使用率不均。如要進一步瞭解如何避免資源使用率不均，請參閱 DBA 必須瞭解的 Spanner 知識 (第 1 部分)：索引鍵與索引。

檢查限制條件

瞭解 CHECK 在 Spanner 中的限制支援功能。

其他資料庫物件

您必須在應用程式邏輯中建立下列物件的功能：

• 資料檢視
• 觸發條件
• 預存程序
• 使用者定義的函式 (UDF)
• 使用 serial 資料類型做為序列產生器的資料欄

將此功能遷移到應用程式邏輯時，請注意以下事項：

• 您必須將您使用的任何 SQL 陳述式從 PostgreSQL SQL 方言遷移到 GoogleSQL 方言。
• 如果您使用游標，則可重新執行查詢以使用偏移與限制。

建立 Spanner 執行個體

當您更新 DDL 陳述式以符合 Spanner 結構定義規定之後，請使用該陳述式在 Spanner 中建立資料庫。

1. 建立 Spanner 執行個體。請遵循「執行個體」中的指示操作，找出能夠支援效能目標的地區設定和運算能力。

2. 使用 Google Cloud 主控台或 gcloud 指令列工具建立資料庫：

建立資料庫後，請遵循「套用身分與存取權管理角色」中的指示建立使用者帳戶，並且向 Spanner 執行個體和資料庫授予權限。

重構應用程式和資料存取層

您不僅需要程式碼來取代上述資料庫物件，還必須新增應用程式邏輯，才能處理下列功能：

• 雜湊主鍵，以用於寫入連續索引鍵，以及用於對連續索引鍵具有高寫入速率的資料表。
• 驗證資料，不包含 CHECK 限制。
• 交錯資料表或應用程式邏輯未涵蓋的參考完整性檢查，其中包括 PostgreSQL 結構定義中的觸發條件處理的功能。

建議您在重構期間使用下列程序：

1. 找出所有可存取資料庫的應用程式程式碼，然後將其重構為單一模組或程式庫。如此一來，您就能完全掌握哪些程式碼可存取資料庫，並因此清楚地知道要修改哪些程式碼。
2. 編寫可在 Spanner 執行讀取和寫入作業的程式碼，為讀取及寫入 PostgreSQL 的原始程式碼提供平行功能。在寫入期間，請更新整個資料列，而非只更新已變更的資料欄，以確保 Spanner 中的資料與 PostgreSQL 中的資料相同。
3. 編寫程式碼，取代 Spanner 中未提供的資料庫物件和函式功能。

遷移資料

建立 Spanner 資料庫並重構應用程式程式碼後，即可將資料遷移至 Spanner。

1. 使用 PostgreSQL COPY 指令將資料傾印至 .csv 檔案。

2. 將 .csv 檔案上傳至 Cloud Storage。

   1. 建立 Cloud Storage 值區。
   2. 按一下 Cloud Storage 主控台中的值區名稱即可開啟值區瀏覽器。
   3. 按一下 [Upload Files] (上傳檔案)。
   4. 前往包含 .csv 檔案的目錄並選取這些檔案。
   5. 按一下「開啟」。

3. 建立應用程式，以便將資料匯入 Spanner。此應用程式可以使用 Dataflow，也可以直接使用用戶端程式庫。為了獲得最佳效能，請務必遵循載入大量資料最佳做法中的指示操作。

測試

針對 Spanner 執行個體測試所有應用程式功能，以確認這些功能如預期運作。執行實際運作等級的工作負載，確保效能符合您的需求。視需要更新運算能力，以達到您想要的效能目標。

移至新系統

完成初始應用程式測試之後，請使用下列其中一個程序來啟用新系統。離線遷移是最簡單的遷移方式。但是此方法會讓您有一段時間無法使用應用程式，若稍後發現資料有問題也無法復原。如要執行離線遷移：

1. 刪除 Spanner 資料庫中的所有資料。
2. 關閉以 PostgreSQL 資料庫為目標的應用程式。
3. 從 PostgreSQL 資料庫匯出所有資料，再依據遷移概覽的說明將其匯入 Spanner 資料庫。

4. 啟動以 Spanner 資料庫為目標的應用程式。

   

您也可以採用即時遷移，但這樣做必須大幅變更您的應用程式才能支援遷移作業。

結構定義遷移範例

下方範例說明在 MusicBrainz PostgreSQL 資料庫結構定義中建立幾個資料表的 CREATE TABLE 陳述式。每個範例均包含 PostgreSQL 結構定義和 Spanner 結構定義。

artist_credit table

CREATE TABLE artist_credit (
 hashed_id STRING(4),
 id INT64,
 name STRING(MAX) NOT NULL,
 artist_count INT64 NOT NULL,
 ref_count INT64,
 created TIMESTAMP OPTIONS (
    allow_commit_timestamp = true
 ),
) PRIMARY KEY(hashed_id, id);

CREATE TABLE artist_credit (
 id SERIAL,
 name VARCHAR NOT NULL,
 artist_count SMALLINT NOT NULL,
 ref_count INTEGER DEFAULT 0,
 created TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

recording table

CREATE TABLE recording (
  hashed_id STRING(36),
  id INT64,
  gid STRING(36) NOT NULL,
  name STRING(MAX) NOT NULL,
  artist_credit_hid STRING(36) NOT NULL,
  artist_credit_id INT64 NOT NULL,
  length INT64,
  comment STRING(255) NOT NULL,
  edits_pending INT64 NOT NULL,
  last_updated TIMESTAMP OPTIONS (
     allow_commit_timestamp = true
  ),
  video BOOL NOT NULL,
) PRIMARY KEY(hashed_id, id);

CREATE TABLE recording (
  id SERIAL,
  gid UUID NOT NULL,
  name VARCHAR NOT NULL,
  artist_credit INTEGER NOT NULL, -- references artist_credit.id
  length INTEGER CHECK (length IS NULL OR length > 0),
  comment VARCHAR(255) NOT NULL DEFAULT '',
  edits_pending INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >= 0),
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  video BOOLEAN NOT NULL DEFAULT FALSE
);

recording-alias table

CREATE TABLE recording_alias (
  hashed_id STRING(36)  NOT NULL,
  id INT64  NOT NULL,
  alias_id INT64,
  name STRING(MAX)  NOT NULL,
  locale STRING(MAX),
  edits_pending INT64  NOT NULL,
  last_updated TIMESTAMP NOT NULL OPTIONS (
     allow_commit_timestamp = true
  ),
  type INT64,
  sort_name STRING(MAX)  NOT NULL,
  begin_date_year INT64,
  begin_date_month INT64,
  begin_date_day INT64,
  end_date_year INT64,
  end_date_month INT64,
  end_date_day INT64,
  primary_for_locale BOOL NOT NULL,
  ended BOOL NOT NULL,
) PRIMARY KEY(hashed_id, id, alias_id),
 INTERLEAVE IN PARENT recording ON DELETE NO ACTION;

CREATE TABLE recording_alias (
  id SERIAL, --PK
  recording INTEGER NOT NULL, -- references recording.id
  name VARCHAR NOT NULL,
  locale TEXT,
  edits_pending INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >=0),
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  type INTEGER, -- references recording_alias_type.id
  sort_name VARCHAR NOT NULL,
  begin_date_year SMALLINT,
  begin_date_month SMALLINT,
  begin_date_day SMALLINT,
  end_date_year SMALLINT,
  end_date_month SMALLINT,
  end_date_day SMALLINT,
  primary_for_locale BOOLEAN NOT NULL DEFAULT false,
  ended BOOLEAN NOT NULL DEFAULT FALSE
  -- CHECK constraint skipped for brevity
);