サイトアイコン IT & ライフハックブログ｜学びと実践のためのアイデア集

MySQLからPostgreSQLへ移行する実務手順：設計、データ移送、切り替えの注意点

greeden
close up photo of mining rig

Photo by panumas nikhomkhai on Pexels.com

MySQLとPostgreSQLは、どちらも表の関係を使ってデータを管理するリレーショナルデータベース管理システム（RDBMS）です。
しかし、データ型、SQLの書き方、文字列の比較方法、自動採番の仕組みには違いがあります。

データベース移行とは、データをコピーするだけの作業ではありません。
スキーマを変換し、アプリケーションを調整し、移行後のデータと処理結果を検証して、利用先を安全に切り替える一連の作業です。

MySQLのダンプファイルを少し修正してPostgreSQLへ読み込む方法は、小さな検証用データベースを除けば不具合を見落としやすくなります。
本番移行では、変換規則を先に決め、同じ手順を何度でも再実行できる状態にしてから切り替えます。

最初に押さえる移行の要点

移行理由と完了条件を決める

PostgreSQLへの移行が適切かどうかは、必要なデータ型、拡張機能、SQL、運用体制、利用中のサービスを基準に判断します。
一方の製品が常に高性能というわけではなく、実際のデータとクエリで比較する必要があります。

製品選定の段階では、先にMySQLとPostgreSQLの違いと利用時の注意点を整理すると、移行で確認すべき項目を絞れます。

完了条件には、許容できる停止時間、移行対象、データの一致条件、主要機能の合格条件、性能の許容範囲を含めます。
切り戻しの判断期限と責任者も決めておくと、切り替え直後に問題が起きても判断が遅れません。

現状調査で集める情報

移行計画は、MySQL上に何があるかを把握するところから始まります。
テーブルだけを数えると、アプリケーションの動作を支えるオブジェクトや設定を取りこぼします。

バックアップは、作成できたことではなく復元できたことを確認します。
論理バックアップを使う場合は、トリガー以外のストアドプログラムやイベントが既定で含まれない構成もあるため、取得対象を明示します。

mysqldump -u mysql_user -p --routines --events --triggers source_db > source_db.sql

このコマンドは一例です。
稼働中に整合したスナップショットを取る方法は、ストレージエンジン、データ量、許容停止時間に合わせて設計してください。

移行作業の全体像

MySQLからPostgreSQLへ移行する標準的な工程
工程 主な作業 確認する成果物
1. 調査 オブジェクト、データ、SQL、停止条件を棚卸しする 対象一覧、リスク一覧、完了条件
2. 変換設計 データ型、制約、照合順序、SQLの変換規則を決める 対応表、変換スクリプト
3. 試行移行 検証環境でスキーマとデータを繰り返し移す 実行ログ、所要時間、エラー一覧
4. 動作確認 データを照合し、アプリケーションと運用手順を試す 検証結果、修正済みアプリケーション
5. 切り替え 書き込みを止めるか差分を同期し、接続先を変更する 切り替え記録、最終検証結果
6. 安定化 エラー、遅いSQL、ロック、データ増加を監視する 監視記録、切り戻し判断

データ型は用途に合わせて変換する

データ型の変換表は出発点にすぎません。
同じMySQL型でも、列が表す意味と実際の値によってPostgreSQL側の型が変わります。

代表的な変換候補と確認点
MySQL側 PostgreSQL側の候補 確認点
TINYINT smallintまたはboolean 数値なのか、真偽値として使っているのかを列ごとに確認する
AUTO_INCREMENT 識別列またはシーケンス 既存IDを移した後に次の採番値を合わせる
ENUM 列挙型、CHECK制約、参照テーブル 値の追加頻度とアプリケーション側の扱いで選ぶ
DATETIME、TIMESTAMP タイムゾーンなし、またはタイムゾーン付きの日時型 保存値が現地時刻なのか、共通時刻なのかを確認する
JSON jsonまたはjsonb 検索、索引、元の表記をどこまで必要とするかを確認する

新しいスキーマで自動採番を表すなら、GENERATED ... AS IDENTITYを候補にできます。
SERIALも既存のスキーマで見かけますが、単語を置換するだけでは、既存IDの読み込みと移行後の採番値まで整いません。

文字コードと照合順序を分けて確認する

MySQLのutf8mb4に対してPostgreSQLのUTF8を選ぶだけでは、文字列の移行確認は終わりません。
照合順序は、文字列の並び方や比較の規則を決める設定です。

日本語、英字の大文字と小文字、アクセント付き文字、末尾の空白を含むサンプルを用意し、検索、並び替え、一意制約の結果を確認します。
一意制約の結果が変わる場合は、移行前に重複候補を洗い出します。

SQLとサーバー側の処理を棚卸しする

MySQLのDDLには、PostgreSQLがそのまま解釈できない構文や属性が含まれます。
バッククォートで囲んだ識別子、既定値、関数、更新時刻の扱いなどを、変換ツールの結果と人のレビューの両方で確認します。

トリガー、ストアドプロシージャ、関数、イベントは、データ移送ツールが自動変換しないことがあります。
各処理の目的を確認し、PostgreSQLの関数やトリガー、アプリケーションのバッチ処理として再設計します。

移行ツールを停止時間と対象範囲で選ぶ

主な移行方法の使い分け
方法 向いている状況 注意点
pgloader MySQLへ接続し、スキーマ変換とデータ移送をまとめて試したい ビュー、トリガー、ストアドプログラムなどは別途確認する
CSVと\copy 表ごとに列と変換内容を細かく管理したい 列順、NULL、改行、引用符、文字コード、ヘッダーの有無を揃える
AWS Database Migration Service AWS上で全件移送後も変更を追従し、停止時間を抑えたい スキーマ変換、事前評価、対象オブジェクト、差分同期の制約を別途確認する

pgloaderで反復可能な試行移行を作る

pgloaderはMySQLへ接続し、型変換を行いながらPostgreSQLへデータを読み込めます。
最初は本番ではなく、破棄できる検証用データベースへ読み込みます。

pgloader \
  mysql://mysql_user@mysql-host/source_db \
  postgresql://pg_user@pg-host/target_db

接続パスワードをコマンド履歴や共有ログへ残さないように、実行環境に合った保護方法を使います。
移行ログと拒否された行を保存し、変換規則を設定ファイルへ移すと、修正後の再実行が容易になります。

CSV移行ではヘッダーと実行場所を揃える

CSVを使う場合は、列をSELECT *の順序に任せず、エクスポート側とインポート側で列名を明示します。
まず少量のデータで、カンマ、引用符、改行、NULLを含む値が往復できるかを試します。

\copy public.users (id, name, created_at)
FROM 'users.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

HEADER trueは、CSVの先頭行に列名がある場合だけ指定します。
MySQLのSELECT ... INTO OUTFILEは列名の行を自動で付けないため、その出力を使うならヘッダーを追加するか、PostgreSQL側をHEADER falseにします。

MySQLのINTO OUTFILEとPostgreSQLのSQLコマンドCOPYが扱うファイルは、原則としてデータベースサーバー側にあります。
psqlの\copyはクライアント側のファイルを扱うため、実行場所と権限を混同しないでください。

AWS Database Migration Serviceで差分を追従する

AWS Database Migration Serviceでは、全件移送と変更データの追従を組み合わせられます。
書き込みが続くシステムで停止時間を抑えたい場合の候補になります。

ただし、データの追従とスキーマ変換は同じ問題ではありません。
事前評価とスキーマ変換を先に行い、自動変換できないオブジェクトを修正してから、全件移送と差分同期を試します。

検証環境で移行手順を完成させる

1. PostgreSQLのスキーマを作る

変換ツールが生成したDDLをそのまま採用せず、型、既定値、NULL許可、制約、インデックスを確認します。
ビュー、関数、トリガー、権限も移行対象一覧と照合します。

スキーマ作成の順序をスクリプトに固定すると、検証用データベースを作り直して同じ条件で試せます。
手作業で修正した箇所も、必ずスクリプトへ戻します。

2. データを読み込み、失敗した行を処理する

データ移送は小さな表から始め、変換エラーの種類を把握します。
エラー行を捨てて処理を続ける設定を使った場合は、件数が一致していても完了と判断できません。

大きな表では、インデックスや外部キーを作る時期が所要時間に影響します。
制約を後から作る場合も、最終的にすべての制約が有効になり、違反データがないことを確認します。

3. アプリケーションをPostgreSQLへ合わせる

接続先とドライバーを変えただけでは、移行は完了しません。
プレースホルダー、識別子の引用、日付関数、自動採番後のID取得、更新または挿入の構文、トランザクションの扱いを確認します。

正常系だけでなく、重複、一意制約違反、外部キー違反、ロールバック、同時更新も試します。
バッチ処理と帳票は、画面操作から見えにくいため個別の試験項目を用意します。

データと処理結果を照合する

行数の一致は必要ですが、それだけでは列のずれ、文字化け、NULLの変化、採番の不整合を検出できません。
複数の検証方法を組み合わせます。

移行後の主な検証項目
対象 確認方法 合格条件の例
スキーマ 列、型、既定値、主キー、外部キー、一意制約、索引を対応表と照合する 対象一覧と一致し、未変換項目に対応方針がある
データ 表別行数、NULL数、最小値、最大値、合計値、安定したキーによる抽出結果を比較する 定めた差分がなく、除外データが記録されている
文字列 日本語、引用符、改行、空文字、アクセント付き文字を抽出して比較する 文字化けや意図しない正規化がない
自動採番 最大IDと次に発行されるIDを確認する 新規登録で既存キーと衝突しない
アプリケーション 登録、参照、更新、削除、検索、バッチ、帳票を実行する 主要な業務結果が移行前と一致する
性能 代表的なクエリと同時実行を本番に近いデータ量で測る あらかじめ決めた許容範囲に収まる

既存IDをそのまま読み込んだ場合は、識別列やシーケンスの次の値を明示的に確認します。
ここを見落とすと、移行後の新規登録で主キーが既存データと衝突します。

本番切り替えと切り戻しを設計する

切り替え方法は、許容停止時間によって変わります。
短い停止を許容できるなら書き込みを止めて最終移送を行い、停止を抑えるなら事前に全件を移して差分を追従させます。

  1. 切り替え開始を関係者へ通知し、バックアップと復元手順を確認する。
  2. アプリケーションの書き込みを止めるか、差分同期の遅延が解消したことを確認する。
  3. 最終差分を反映し、行数、重要な値、自動採番、主要処理を確認する。
  4. 接続先をPostgreSQLへ変更し、エラー、遅いSQL、ロック、接続数を監視する。
  5. 合格条件を満たせなければ、決めておいた期限と手順に従って切り戻す。

切り替え後にPostgreSQLへ書き込んだデータをMySQLへ戻せない設計では、単に接続先を戻すだけでは切り戻せません。
切り替え後の書き込みをいつ許可するか、戻す必要があるデータをどう扱うかまで決めます。

移行で起きやすい失敗

切り替え前のチェックリスト

安全な移行を判断できる状態を作る

MySQLからPostgreSQLへの移行では、データ型の対応表よりも、変換結果をどう検証するかが成否を左右します。
ツールが自動変換できる部分と、人が業務上の意味を判断する部分を分けてください。

棚卸し、変換、試行、検証、切り替えを反復可能な手順にすれば、問題が起きても修正してやり直せます。
本番切り替えは、データと主要処理が一致し、停止時間と切り戻し条件を説明できる状態になってから実施します。

モバイルバージョンを終了