More than 5 years have passed since last update.

pgloaderでMySQLからPostgreSQLに移行する

Last updated at 2017-11-09Posted at 2017-11-09

やりたいこと

MySQL 5.7からPostgreSQL 9.6にRDBMSを変更したい。
開発中の移行なのでデータは無視してスキーマだけ移行できれば良いが、やろうと思えばデータ移行もおそらく同様の方法でできるはず。
プロジェクトのDBスキーマのマイグレーション定義（Flyway使用）で記述しているSQLはMySQLに依存しているため、いったん現状のDB構成を正としてマイグレーション用定義ファイルを生成し直そうと思う。

とりあえず mysqldump （駄目）

mysqldump には --compatible=postgresql というオプションがあるので、これでSQLダンプしてpsqlコマンドで実行すれば移行できるかと思ったが、全然駄目。エラーだらけで使い物にならない。引用符のエスケープなどは一括置換などでどうにかならなくもないが、インデックス定義がエクスポートされないとか、PostgreSQLではTABLE構文中でCOMMENT定義できないから構文エラーになるとか、色々問題あったのでこの方法は諦めた。compatibleとは…？

pgloader

pgloaderはMySQLからPostgreSQLに諸々直接コピーしてくれる。
READMEにある通り Mac なら Homebrew でもインストールできるはずだが、Dockerイメージがあるので、これ使ってみる。

$ docker pull dimitri/pgloader
$ docker run --rm --name pgloader dimitri/pgloader:latest pgloader mysql://myusername:mypassword@myhostname/dbname postgresql://pgusername:pgpassword@pghostname/dbname

先にPostgreSQL側でデータベースは作っておく必要がある。上のコマンドは、当然、認証情報は正しく書き換える必要あるが、だいたいこんな感じで勝手に移行してくれるはず。

DBが Docker コンテナ上で動作しているときの注意

MySQL/PostgreSQL が Docker コンテナ上で稼働している場合、Docker コンテナ間の接続でうまくいかないかもしれない。そういうときは --link [CONTAINER_NAME]:[ALIAS] を指定する。それぞれのコンテナ名が mysql と postgres だとすると、以下のような感じ。

$ docker run --rm --name pgloader --link mysql:mysql --link postgres:postgres dimitri/pgloader:latest pgloader mysql://myusername:mypassword@mysql/dbname postgresql://pgusername:pgpassword@postgres/dbname

docker-compose から Docker コンテナを起動している場合などは

docker: Error response from daemon: Cannot link to /postgres, as it does not belong to the default network.

とか言われるので --net [COMPOSE_PROJECT_NAME]_default とかを指定する。一応、存在するネットワークのリストは以下で表示できる。

$ docker network ls

ちなみに Docker イメージから pgloader を実行すると

WARNING: Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?) Trying to continue anyway.

と言われるが、これは無視して良い（ソース）。

キャストルール指定

また pgloader 実行中、いくつか以下のようなエラーが出ていた。

2017-11-08T16:32:20.625000+09:00 ERROR PostgreSQL Database error 42804: foreign key constraint "***_ibfk_1" cannot be implemented
DETAIL: Key columns "***_id" and "id" are of incompatible types: numeric and bigint.
QUERY: ALTER TABLE ***.*** ADD CONSTRAINT ***_ibfk_1 FOREIGN KEY(***_id) REFERENCES ***.***(id) ON UPDATE RESTRICT ON DELETE RESTRICT

どうやら MySQLで bigint(20) となっていたカラムがただの numeric になっているらしい。DEFAULT MySQL CASTING RULESには type bigint to bigint drop typemod と書いてあるので、その通りに動いてくれていれば問題ないはずなのだが、謎。

いずれにせよ細かく変換ルールを定義したいことはあるはずで、そういうときは --cast オプションで定義できる。
type int with extra auto_increment to serial when (< precision 10) のようにドキュメントに書かれている通りのフォーマットで書けば条件等も定義できるらしい。

--cast オプションのフォーマットが不正だと Could not parse the command line: see above. と言われるのだが、特にこのメッセージ以外に何も出力されなくて意味がわからない。こういう場合は --verbose オプションをつけるとエラー理由が出力される（--debug もつけるとさらに詳細なログが出る）。

--cast オプションは色々と条件つけて --cast 'type decimal when (and (= 18 precision) (= 6 scale)) to float8 drop typemod' みたいな感じで書けるらしい（参考）。複数指定する場合は --cast '(rule1)' --cast '(rule2)' といった感じでオプション足していくと動いた気がする。

で、処理が成功したら psql コマンドで PostgreSQL に接続して、スキーマが正しくインポートされていることを確認する（データも含まれる場合はデータも確認）。スキーマが public ではなくDB名と同じになっているようなので注意。

マイグレーション定義ファイル（SQL）出力

今回のプロジェクトではマイグレーションツールとして Flyway を使っていて、生SQLでスキーマのバージョンを管理しているので、今回インポートされたスキーマを pg_dump コマンドで出力して、それをV00001のマイグレーション定義として保存する（MySQL時代のマイグレーション定義は削除）。

Flyway スキーマバージョンを管理する schema_version テーブルの定義や余計な設定等まで出力されてしまっているので、そのあたりは手動で削除する。あと Flyway は基本的に public スキーマ以外を扱いにくいので、スキーマ名は public にしておくのが無難。