Posted inMySQL

PostgreSQLからMySQLへの移行：実戦で得た教訓と移行プロセスの全貌

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

なぜ「逆行」するのか：PostgreSQLからMySQLへの移行理由

一般的にPostgreSQLの方が機能面で高く評価されることが多いですが、現実にはその逆の対応を迫られることもあります。1年前、私はAWS上で動作するMySQL 8.0の50GBのデータを管理していました。新しくプロジェクトに加わった開発チームはMySQLには精通していましたが、PostgreSQLには不慣れでした。その結果、メンテナンスやクエリの最適化が非常に滞るようになりました。技術スタックを統一するため、私はあえてこの「逆方向」の移行を決断しました。

主な理由はエコシステム（Ecosystem）にあります。使用しているCMSやフレームワークがMySQLをより強力にサポートしている場合や、クラウドプロバイダーのMySQLマネージドサービスのコストが大幅に安い場合もあります。しかし、移行は単にエクスポートしてインポートするほど簡単ではありません。これら2つの管理システムは、データ型の考え方やSQL構文が大きく異なります。

サポートツール：手動で行わないこと

リスクを軽減するために、自作スクリプトではなくツールを使用することをお勧めします。MySQL Workbench Migration Wizardは、私が試した中で最も安定した選択肢でした。データ型のマッピングを非常にスムーズに処理してくれます。

開始する前に、以下の準備をしてください：

  • ローカルPCまたは中間サーバーに最新版のMySQL Workbenchをインストールする。
  • WorkbenchがPostgresに接続できるように、PostgreSQL用ODBCドライバ（psqlODBC）をインストールする。
  • 移行先のMySQLでSUPER権限、または最低限CREATEおよびINSERT権限を持っていること。
# UbuntuにODBCドライバをインストール：
sudo apt-get install odbc-postgresql

インストール後、Workbenchを開き、Database -> Migration Wizardにアクセスします。ここから正式なプロセスが始まります。

重要な3つの移行フェーズ

以下は、私が約4時間で50GBのデータを正常に移行するために適用したロードマップです。

1. スキーマ構造のマッピング

PostgreSQLはDatabaseの中に階層を作るためにSchemaを使用します。対照的に, MySQLは各DatabaseそのものをSchemaと見なします。以下のデータ型には特に注意が必要です：

  • Boolean: PostgresにはBOOLEANがありますが、MySQLでは自動的にTINYINT(1)にマップされます。
  • Serial: PostgresのSERIALは、MySQLのAUTO_INCREMENTに変換する必要があります。
  • JSON: PostgreSQLはJSONBに強いですが、MySQL 8.0もJSONをサポートしています。ただし、クエリ速度は異なります。
  • 大きなデータ: PostgresのTEXTは非常に柔軟です。MySQLの場合、データが64KBを超える可能性があるならMEDIUMTEXTやLONGTEXTの使用を検討してください。

2. SQL構文の修正

WorkbenchがCREATE TABLEスクリプトを生成した際、すぐに「Next」を押さないでください。一度立ち止まって制約（Constraints）を確認しましょう。PostgreSQLでは異なるテーブル間で同じインデックス名を使用できますが、MySQLではスキーマ全体でインデックス名が一意である必要があります。

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    metadata JSONB
);

-- MySQLへ移行する際は以下のように修正する必要があります：
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metadata JSON
) CHARACTER SET utf8mb4;

実戦での教訓：Stored ProceduresとTriggersは手動で書き直してください。PL/pgSQLとMySQLの構文は天と地ほどの差があります。自動ツールはこの部分でエラーを出すことがよくあります。

3. データの移行（Data Migration）

タイムアウトを避けるため、データを5,000〜10,000行程度のバッチに分割して処理するのが一般的です。SQLファイル経由で手動で行う場合は、日付形式やエスケープ文字を注意深く確認してください。MySQLはsql_modeに厳格であり、Postgresのデータに未処理の特殊文字が含まれているとエラーが発生しやすくなります。

移行後の検証と最適化

ツールを完全に盲信してはいけません。最初に行うべきは、双方の行数（Row Count）の比較です。

-- 両方のデータベースで実行して結果を照合
SELECT count(*) FROM orders;

次に、重要なレコードをランダムに100件ほど抽出して、各フィールドを比較します。小さなコツとして、MySQLのCHECKSUM TABLE関数を使用して、Postgresの対応するハッシュ値と比較する方法があります。

パフォーマンスに関しては、完了直後にANALYZE TABLEを実行することを忘れないでください。このコマンドにより、MySQLのクエリオプティマイザ用の統計情報（statistics）が更新されます。

ANALYZE TABLE users, orders, products;

最初の1週間は、Slow Query Logを有効にしておきましょう。Postgres固有のインデックスのおかげで高速に動作していたクエリが、MySQLではFull Table Scanを引き起こす可能性があります。早期に発見することで、トラフィックが増大した際のシステムダウンを防ぎ、適切なインデックスを追加できます。

データベースの移行は引っ越しのようなものです。入念に点検しなければ、荷物は簡単に紛失したり破損したりします。2つの管理システムの相違点さえしっかり把握していれば、データを安全に目的地まで届けることができるはずです。

Share:
Tags: