PostgreSQL から Cloud Spanner への移行(Google 標準 SQL 言語)

コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

このページでは、オープンソースの PostgreSQL データベースを Cloud Spanner に移行する方法について説明します。

移行には次のタスクが含まれます。

  • PostgreSQL スキーマを Spanner スキーマにマッピングする。
  • Spanner インスタンス、データベース、スキーマを作成する。
  • Spanner データベースと連携するようにアプリケーションをリファクタリングする。
  • データを移行する。
  • 新しいシステムを検証して本番稼働状態に移行する。

このページでは、MusicBrainz PostgreSQL データベースのテーブルを使用したスキーマの例もいくつか紹介します。

PostgreSQL スキーマを Spanner にマッピングする

データベースを PostgreSQL から Spanner に移行する最初のステップは、どのようなスキーマ変更が必要かを判断することです。pg_dump を使用して、PostgreSQL データベース内のオブジェクトを定義するデータ定義言語(DDL)ステートメントを作成し、次のセクションの説明に従ってステートメントを変更します。DDL ステートメントを更新したら、DDL ステートメントを使用して、Spanner インスタンスにデータベースを作成します。

データの種類

次の表に、PostgreSQL のデータ型を Spanner のデータ型にマッピングする方法を示します。DDL ステートメントのデータ型を PostgreSQL データ型から Spanner データ型に更新します。

PostgreSQL Spanner
Bigint

int8

INT64
Bigserial

serial8

INT64
bit [ (n) ] ARRAY<BOOL>
bit varying [ (n) ]

varbit [ (n) ]

ARRAY<BOOL>
Boolean

bool

BOOL
box ARRAY<FLOAT64>
bytea BYTES
character [ (n) ]

char [ (n) ]

STRING
character varying [ (n) ]

varchar [ (n) ]

STRING
cidr STRING(標準の CIDR 表記を使用)
circle ARRAY<FLOAT64>
date DATE
double precision

float8

FLOAT64
inet STRING
Integer

int

int4

INT64
interval[ fields ] [ (p) ] ミリ秒単位で値を格納する場合は INT64、アプリケーションで定義した時間間隔形式で値を格納する場合は STRING
json STRING
jsonb BYTES
line ARRAY<FLOAT64>
lseg ARRAY<FLOAT64>
macaddr STRING(標準の MAC アドレス表記を使用)
money INT64 または STRING任意精度の数値の場合)
numeric [ (p, s) ]

decimal [ (p, s) ]

PostgreSQL では、カラムの宣言で定義されているように、NUMERIC データ型と DECIMAL データ型は最大 217 桁の精度と、214-1 桁の尺度をサポートします。

Spanner の NUMERIC データ型は、最大 38 桁の精度と 10 進 9 桁の尺度をサポートします。

精度の向上が必要な場合は、代替メカニズムについて任意精度の数値データの保存をご覧ください。
path ARRAY<FLOAT64>
pg_lsn このデータ型は PostgreSQL 固有であるため、Spanner に相当するものはありません。
point ARRAY<FLOAT64>
polygon ARRAY<FLOAT64>
Real

float4

FLOAT64
Smallint

int2

INT64
Smallserial

serial2

INT64
Serial

serial4

INT64
text STRING
time [ (p) ] [ without time zone ] STRINGHH:MM:SS.sss 表記を使用)
time [ (p) ] with time zone

timetz

STRINGHH:MM:SS.sss+ZZZZ 表記を使用)また、TIMESTAMP 型の列と、タイムゾーンを保持する列の 2 つの列に分けることもできます。
timestamp [ (p) ] [ without time zone ] 対応するデータ型はありません。自らの判断で STRING または TIMESTAMP として保存できます。
timestamp [ (p) ] with time zone

timestamptz

TIMESTAMP
tsquery 対応するデータ型はありません。代わりにアプリケーションでストレージ メカニズムを定義してください。
tsvector 対応するデータ型はありません。代わりにアプリケーションでストレージ メカニズムを定義してください。
txid_snapshot 対応するデータ型はありません。代わりにアプリケーションでストレージ メカニズムを定義してください。
uuid STRING または BYTES
xml STRING

主キー

頻繁に追記する Spanner データベース内のテーブルでは、単調に増減する主キーを使用しないでください。この方法では、書き込み中にホットスポットが発生します。代わりに、DDL の CREATE TABLE ステートメントを変更して、サポートされている主キー戦略を使用します。テーブルの作成後に主キー列を追加または削除することはできないため、慎重にスキーマを設計することが重要です。

移行中に、単調増加する既存の整数キーの一部を保持する必要があるかもしれません。この種のキーが頻繁に操作される、更新頻度の高いテーブルでそれらのキーを保持する必要がある場合は、既存のキーに疑似乱数の接頭辞を付けることで、ホットスポットが作成されるのを防ぐことができます。この手法により、Spanner は行を再分散します。このアプローチの使用について詳しくは、DBA が Spanner について知っておくべきこと、パート 1: キーとインデックスをご覧ください。

外部キーと参照整合性

Spanner の外部キーのサポートについて確認する。

インデックス

PostgreSQL の B ツリー インデックスは、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);

任意の PostgreSQL テーブルのインデックスを見つけるには、psql\di メタコマンドを実行します。

必要なインデックスが判明したら、CREATE INDEX ステートメントを追加してそれらを作成します。インデックスの作成のガイダンスに従ってください。

Spanner では、インデックスをテーブルとして実装しているため、単調に増加する列(TIMESTAMP データを含む列)に対してインデックスを付けると、ホットスポットが発生する可能性があります。ホットスポットを回避する方法については、DBA が Spanner について知っておくべきこと、パート 1: キーとインデックスをご覧ください。

チェック制約

Spanner における CHECK 制約のサポートについて学習する。

その他のデータベース オブジェクト

アプリケーション ロジックで次のオブジェクトの機能を作成する必要があります。

  • ビュー
  • トリガー
  • ストアド プロシージャ
  • ユーザー定義関数(UDF)
  • シーケンス ジェネレータとして serial データ型を使用する列

これらの機能をアプリケーション ロジックに移行するときは、次のヒントを参考にしてください。

Spanner インスタンスを作成する

Spanner スキーマ要件を遵守するように DDL ステートメントを更新したら、そのステートメントを使用して Spanner でデータベースを作成します。

  1. Spanner インスタンスを作成するパフォーマンス目標を達成できるように適切なリージョン構成とコンピューティング容量を決定するには、インスタンスのガイダンスに従ってください。

  2. Google Cloud Console または gcloud コマンドライン ツールを使用して、データベースを作成します。

Console

  1. インスタンス ページに移動
  2. サンプル データベースを作成するインスタンスの名前をクリックして、[インスタンスの詳細] ページを開きます。
  3. [データベースを作成] をクリックします。
  4. データベースの名前を入力して [続行] をクリックします。
  5. [データベース スキーマの定義] セクションで、[テキストとして編集] コントロールを切り替えます。
  6. DDL ステートメントをコピーして [DDL ステートメント] フィールドに貼り付けます。
  7. [作成] をクリックします。

gcloud

  1. gcloud CLI をインストールします。
  2. gcloud spanner databases create コマンドを使用して、データベースを作成します。
    gcloud spanner databases create DATABASE_NAME --instance=INSTANCE_NAME
    --ddl='DDL1' --ddl='DDL2'
    
  • DATABASE_NAME はデータベースの名前です。
  • INSTANCE_NAME は、作成した Spanner インスタンスです。
  • DDLn は、変更された DDL ステートメントです。

データベースを作成したら、IAM ロールの適用の手順に沿ってユーザー アカウントを作成し、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. [ファイルをアップロード] をクリックします。
    4. .csv ファイルがあるディレクトリに移動してそれらを選択します。
    5. [開く] をクリックします。
  3. Spanner にデータをインポートするためのアプリケーションを作成する。このアプリケーションでは、Dataflow を使用することも、クライアント ライブラリを直接使用することもできます。最適なパフォーマンスを実現するには、データの一括読み込みのベスト プラクティスのガイダンスに従ってください。

テスト

すべてのアプリケーション関数を Spanner インスタンスに対してテストし、期待どおりに動作することを確認します。本番環境レベルのワークロードを実行して、パフォーマンスがニーズを満たすことを確認します。パフォーマンス目標を達成するため、必要に応じてコンピューティング容量を更新します。

新しいシステムへの移行

アプリケーションの初期テストが完了したら、次のいずれかの方法で新しいシステムを起動します。最も簡単なのはオフライン移行です。ただし、この方法では一定期間アプリケーションを使用できなくなり、後でデータに関する問題が見つかってもロールバックする手段がありません。オフライン移行は次の手順で実行します。

  1. Spanner データベース内のすべてのデータを削除します。
  2. PostgreSQL データベースをターゲットとするアプリケーションをシャットダウンします。
  3. データの移行の説明に沿って、PostgreSQL データベースからすべてのデータをエクスポートし、Spanner データベースにインポートします。
  4. Spanner データベースをターゲットとするアプリケーションを起動します。

    オフライン移行データフロー。

ライブ マイグレーションは可能ですが、マイグレーションを行うにはアプリケーションの大幅な変更が必要です。

スキーマ移行の例

次の例は、MusicBrainz PostgreSQL データベース スキーマのいくつかのテーブル用の CREATE TABLE ステートメントを示しています。それぞれの例には、PostgreSQL スキーマと Spanner スキーマの両方が含まれています。

artist_credit テーブル

PostgreSQL バージョン:

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()
);

Spanner のバージョン:

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);

recording テーブル

PostgreSQL バージョン:

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
);

Spanner のバージョン:

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);

recording-alias テーブル

PostgreSQL バージョン:

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
);

Spanner のバージョン:

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;