惭测厂蚕尝から笔辞蝉迟驳谤别厂蚕尝へのマイグレーションのハマリ所
- 1. MySQLからPostgreSQLへの マイグレーションのハマリ所 日本PostgreSQLユーザ会 加賀 誠人 @makotokaga 2012年2月24日 PostgreSQL Conference 2012
- 2. アジェンダ ? ゴール ? 情報の収集 ? テーブル定義の移行 ? データの移行 ? 移行におけるハマりどころ ? まとめ
- 3. 講演者について ? 加賀 誠人 (かが まこと) ? @makotokaga ? フリーランスWebエンジニア ? 日本PostgreSQLユーザ会理事 ? 本日の基調講演のUstream担当 ? 物欲系Blogも書いています ? http://blog.project92.com
- 4. ゴール ? MySQL+PHPで稼働していたとある Webアプリケーションを、PostgreSQL +PHPに移行する ?ターゲットとなった旧システム ? MySQL 5.0.x (InnoDB) ? PHP 5.1.x ?新システム ? PostgreSQL 9.0.x ? PHP 5.3.x
- 5. どこから手をつけるか？ ? まずは、情報収集 ? Converting from other Databases to PostgreSQL ?http://wiki.postgresql.org/wiki/ Converting_from_other_Databases_to_Post greSQL ?「MySQL PostgreSQL migration」で検索
- 6. Converting from other Databases to PostgreSQL
- 7. dumpしてrestoreできるか？ ? MySQLとPostgreSQLでは、型の違 い、SQLの表記の違いがあるので、変 換は必須 ?いくつか既存の変換ツールが公開さ れている
- 8. 型の違い ? 多くのデータ型は、双方に存在するが 名前が違うなど、異なっている ? バイナリ?ラージ?オブジェクトなど は扱い方も異なる ? BLOG v.s. BYTEA
- 9. SERIAL型 ? MySQLでは、auto_increment ? PostgreSQLでは、SEQUENCEが作られ、 default値として「nextval()」が設定される ? 直前に使われた値を調べる方法が違う ? MySQLにはAPIで提供される mysql_insert_id() があり、直前に使われ た値が簡単に取得できる ? PostgreSQLでは、currval()で得られる が、SEQUENCEの名前を知っている必要 がある
- 10. SQL文の表記の違い ? コメント行の開始文字列の違い ? MySQL : "#" ? PostgreSQLは認識しない ? PostgreSQL : "--" ? MySQLは認識する ? 引用符の違い ? MySQLは「"」も「'」も使える ? 識別子の場合もMySQLは「`」 ? などなど……
- 11. テーブル定義の移行 ? 今回選択した方法 ? Mac OS X用ER図作成ツール「SQL Editor」を使用 ? MySQLのテーブル定義を読込み ? ターゲットをPostgreSQLに変更 ? 一部データ型を修正 ? SQLをエクスポート（し、さらに修 正） ? PostgreSQLに読み込み
- 12. SQLEditorについて ? Mac OS X版のER図作成ツール ? http://www.malcolmhardie.com/sqleditor/ ? MalcolmHardie Solutions社製 ? 79USD
- 13. データの移行① ? 移行前後、双方のDBに接続し、データを 移行するPHPのプログラムを作成し移行 した ? 移行先のPostgreSQLのシステムカタ ログから、移行後のテーブル定義を取得 ? MySQLのテーブルを順次SELECT ? 各カラムの型に応じて、必要な変換をし て、PostgreSQL側にINSERT ? NULL値を適切に処理しないとハマる
- 14. データの移行② ② 各テーブルの ① pg_catalogから、変換後の データを順次取得 変換プログラム テーブル定義を取得 (PHPで記述） ③ データを変換して 順次投入 MySQL PostgreSQL
- 15. データ移行自体の問題 ? 移行そのものに相当の時間を要するた め、一定の時間止められないサービス だと辛い ? 実データで変換を検証する必要がある が、検証そのものにも時間がかかる
- 16. 移行でのハマりどころ① ? timestamp型のふるまい ? MySQLでは「0000-00-00 00:00:00」が許容される ? PostgreSQLでは、上記は投入でき ない
- 17. 移行でのハマりどころ② ? CHARACTER(n)型の違い ? 長さを指定した場合、n文字(バイト) の長さになるまで、空白が追加される ? char(4)のカラムに「foo」が入って いた場合に、PHPからアクセスする と以下の値が取得される ?MySQL : "foo" ?PostgreSQL : "foo "
- 18. 移行でのハマりどころ②?2 ? psqlでやると…… test=# SELECT '"' || test || '"' from bar; ?column? ---------- "12" "123" "1" "1234" (4 rows)
- 19. 移行でのハマりどころ③ ? BOOLEANの値の違い ? MySQL: "1" or "0" ? 実体は tinyint(1) ? "TRUE" or "FALSE"で比較はできる ? if ($results) { ... } ? PostgreSQL: "t" or "f" ? if ($results === 't') { ... }
- 20. 移行でのハマりどころ④ ? ORDER BY句のないクエリー ? PostgreSQLはもちろん、SQLで は、ORDER BY句がない場合は、取 得されるレコードの順番は不定 ? MySQLでは、レコードの投入順に出 力されるよう ?当然保証されているわけではない？
- 21. 移行でのハマりどころ⑤ ? ORDER BY句でのNULLの扱い ? MySQLは、NULLが先頭に ? PostgreSQLは、NULLは末尾に
- 22. ORマッパがあっても ? 今回ターゲットとなったシステムは、 symfony 0.6.3を利用しており、OR マッパとしてPropelを採用 ?謳い文句上は設定を変えれば、簡単 にDBMSの変更が可能というが…… ? 実際にはORマッパでは吸収しきれ ていない ? アプリの書き換えは避けられない
- 23. まとめ ? マイグレーションは可能だが、高コスト ? ここで紹介しきれてない非互換性も多 い ? 中途半端に動いてしまうものもあるの で、仕様が不明確なシステムの移行だ と危険をともなう ? PostgreSQLでないといけない理由 や利点とのトレードオフで判断したい ?GIS、ライセンス、などなど