このエントリーは Classi developers Advent Calendar 2022の18日目。 ネタはなんでもいいよ!とのことなので、Claasiに全く関係なく、MysqlからPostgreSQLに移行する際の注意点を書く。 なお、まだRDSにPostgreSQLがなかった頃のような昔の記事だがこちらに無いことを書いていく。
MySQL から PostgreSQLにデータ移行する際の注意点
MySQLとPostgreSQLは互換性がもちろんありませんので、細かいところで違いが発生します。 よく踏むデータ移行の注意点は以下の通り。
- timestampやdatetimeを移行する先はtimestamp型になるが、timestamp型はタイムゾーン付きと無しがある
- timestamp with timezoneは内部的にはUTCで持ち、timezoneに合わせて表示時刻が変わる
- MySQLのZERO DATA系は基本的にPostgreSQLにはないのですべてエラー
- 以降する際は変更が必要
- 例:
0000-00-00
→1900-01-01
or'-infinity'
last_insert_id()
は無いlastval()
やRETURNING句
で書き直す必要がある
- レプリケーションの仕組みが結構違う
- 例えばPostgreSQLで同期と呼ばれるものがMySQLでは準同期だったり
- PostgreSQLにはストリーミングレプリケーションとロジカルレプリケーションがあるが、MySQLのレプリケーションは両方の特性を兼ね備えた柔軟なレプリケーションだったり
- これは良し悪しではなく文化の違いが大きいけど
- そもそもレプリケーションの作り方も違う
- PostgreSQLのストリーミングレプリケーションではメジャーバージョンアップ違いのレプリケーションができない
- PostgreSQLではクラスタ間やメジャーバージョンアップ違いはロジカルレプリケーションを使いましょう
- PostgreSQLは大文字小文字を区別する
- あとPostgreSQLはVARCHARはTEXTのエイリアスなので文字数制限したいわけではないならTEXT型を使って良い
- ちょいちょい関数が違う
- PostgreSQLでよく言われるのはIFNULLが無い
- 代替はCOALESCE
- 逆にPostgreSQLには
distinct on
などのMySQLにはない関数も多いdistinct on
はwindow関数で置き換えることができるがdistinct on
のほうが直感的
- 型やデータの比較がPostgreSQLはかなり厳密
- トランザクション分離レベルのデフォルトが違う
- というか結構違う
- mpywさんがすごくまとめてくれてるので参考まで
- 2億回くらいみんな読んだほうがいい
- MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する
- 例えばギャップロックとかネクストキーロックなどはPostgreSQLにはない
- RCなのだから当たり前である
ざっと思いつくままに書いたが、あなたがORMからしかMySQLを触っておらず、INDEXの利用について深く考える必要が無いほどの規模であれば、移行はそんなに難しくはないだろう。 しかしそれなりの規模になっているのであれば、多少は覚悟して移行する必要がある。
それでも移行をしたい人へ
どうしても移行がしたい。そんなあなたのためにMySQLのテーブルをPostgreSQLのテーブルのように扱えるFDWとしてmysql_fdwがある。
github.com
ubuntuなら apt install postgresql-15-mysql-fdw
でインストールできるし、AWSをお使いの人は最初からRDSに拡張としてmysql_fdwは用意されているので、CREATE EXTENSION
して有効化するだけで使うことができる。
もし、fdwを使いたいなら下の記事に詳しい仕組みが書いてあるので参考にすると良い。 またpushdownはmysql_fdwのバージョンによって違いが大きいので使うバージョンをよく確認すること。 上記のEDBのリポジトのREADMEに書いてあるので必ず読むこと。
まとめ
このへんで挫折した人は移行はやめておいたほうが無難。 そんな無理してまで移行するほどの価値をつけることは難しいだろう。
しかし、マルチテナントの設計のためにPostgreSQLを使いたい人などは目指すなりの価値があるとは思う。
引き続き、Classiアドベントカレンダーをお楽しみください。