pgloaderでMySQLからPostgreSQLに移行する

## やりたいこと

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

## とりあえず mysqldump （駄目）

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

## pgloader

[pgloader](http://pgloader.io/)はMySQLからPostgreSQLに諸々直接コピーしてくれる。
READMEにある通り Mac なら Homebrew でもインストールできるはずだが、[Dockerイメージ](https://hub.docker.com/r/dimitri/pgloader/)があるので、これ使ってみる。

```shell-session
$ 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 だとすると、以下のような感じ。

```shell-session
$ 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` とかを指定する。一応、存在するネットワークのリストは以下で表示できる。

```shell-session
$ docker network ls
```

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

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

と言われるが、これは無視して良い（[ソース](https://github.com/dimitri/pgloader/wiki/Running-in-Docker-(SBCL-warning))）。

## キャストルール指定

また 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](https://github.com/dimitri/pgloader/blob/master/pgloader.1.md#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'` みたいな感じで書けるらしい（[参考](https://github.com/dimitri/pgloader/issues/650)）。複数指定する場合は `--cast '(rule1)' --cast '(rule2)'` といった感じでオプション足していくと動いた気がする。

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

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

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

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

## 関連情報
- [MySQLからPostgresqlに移行するためのPHPコード | Workabroad.jp](http://www.workabroad.jp/posts/2056)
→ PHPのPHP_my2pgというツールでも変換できるとのこと（未検証）。
- [MySQLからPostgreSQLへのマイグレーションのハマリ所](https://www.slideshare.net/makotokaga/mysqlpostgresql)
→ データ型の差異などについて。