MySQLからPostgreSQL移行で躓いたことまとめ
概要
テーブル構造もDBMSも違う(今回はMySQL -> AlloyDB for PostgreSQL)といった条件で移行作業を行いました。
簡単なツールを作ればすぐに終わると思ったのですが、思ったよりも時間がかかったので効率的な方法を書いていきたいと思います。
ちなみに、作業の殆どは調査と社内調整だったので今回の記事は気休め程度だと思ってください。
想定しているケース
- MySQL -> PostgreSQLへの移行。
- テーブル構造に違いがある。
- MySQLからselect文で抽出してPostgreSQLにbulk insertする。
- 頻繁にupsertするのでdumpする作業を省きたいから。
- PythonのSQLAlchemyを使った使い捨てのツールを作成。
- 今回はPythonとは関係ないことを書いて行くので気にしなくてok。
- パフォーマンスは気にしない。
参考にした記事
-
https://www.m3tech.blog/entry/sqlalchemy-tutorial
- 普段はmodelをしっかり書いてORMの機能をフル活用するように書いていたので、手軽にSQLを実行する方法が分かりませんでした。そのままSQL文を使えて楽に処理がかけるので助かりました。
躓いたことやTips
DBMSごとに特殊文字のエスケープ方法が違う
僕がよく遭遇したケースはシングルクォーテーションのエスケープでした。
MySQLで下記のように入っているデータをPostgresにそのまま入れることはできません。
Mike\'s plate
もしPostgresに入れたい場合は下記のように変更しましょう。
バックスラッシュの代わりにシングルクォーテーションを入れます。
Mike''s plate
こういった特殊文字の扱いが違う場合、プログラム側で文字列を置換しましょう。
PostgresだとNUL文字が使えない
結局解決できていない問題...
エラー内容
ValueError: A string literal cannot contain NUL (ｃ) characters.
insert時にNUL文字があってエラーを起こしたようで、ログを確認したところ変な文字列がありました。
test\x00page
\x00がNUL文字と呼ばれるものでPostgresだとinsertできない文字列でした。
NUL文字の厄介なところは環境によって見え方が違うところ。
dumpしたデータをエディタで見たとき\x00が表示されずpasswdhttpsとなっていたりpasswd httpsのようなスペースが入って見えたりします。中にはNUL文字以降の文字列が表示されないものもありました。
Pythonを使ってinsert時にNUL文字を置換して除去しようとしましたが、うまくできなかったので一部手動で入れました...
なるべくselect時に移行用データを完成させる
例として下記のテーブルを想定します。
移行元のテーブルに移行先テーブルカラムが足りないといったケースです。今回はageカラムがありません。
- 移行元のテーブルカラム
- id
- name
- 移行先のテーブルカラム
- id
- name
- age
この場合データ抽出時には下記のようにselect時にカラムを追加するといいと思います。
SELECT
id,
name,
"" as age
FROM
table
;
メリット
- プログラムを実行しなくてもSQL文だけでどういうデータを作りたいか、足りないデータがわかりやすい。
- 非エンジニアの方にSQLの実行結果を共有することで認識を合わせやすい。
-
ageが不明なので調査、データ作成の依頼がしやすい。
-
- ツール、データが未完成でもSQLだけでとりあえず移行できる。
- DBを参照するツールをとりあえず動かすことができる。
- not nullのカラムがあっても対応しやすい。
- 実行結果にプログラムでデータ追加しやすい。
- key value型の場合、keyを追加する必要がない。
他にもselect文できることを書いておきます。
select
ROW_NUMBER() OVER(ORDER BY p.id) as id # 連番を生成する。,
name as company_name # カラム名を変更する。,
json_object() as json_data # とりあえず空のJSONを生成する。,
1 as draft # とりあえずフラグを立てておく。
from
table
;
なぜかプログラムでデータ移行ができない
いくつかのケースが考えられるので、書き出します。
ORMのエラーが分かりづらい
ORMによってはDB操作時のエラーがわかりにくいことがあります。
この場合、プログラムで実行するSQL文をDBで直接実行してみると詳細なエラーが出るかもしれません。
insert時にエラーが出ている場合は追加でdumpしたデータをimportしてみるのをおすすめします。
下記が自分の遭遇したものです。
- 移行先のカラムはnot nullなのに移行元のデータにはnullが含まれていた。
- 空文字を入れるか、データ担当者にデータを埋めてもらう。
- 移行先のカラムはuniqueなのに移行元のデータには重複した文字列があった。
- 楽な方法として、移行先のカラムからunique制約を外してもらう。
- できなければ、重複したデータのうちどれを採用するか決める。(結構大変)
ORMの使い方が間違っている
ORMの機能で実行するSQL文を出力する機能があります。
SQLAlchemyは勝手にログ出力されるので設定は不要でした。
SQL文をDBで直接実行して問題があればORMの書き方を疑います。問題がなければ前述したデータに問題があるので調査します。
テーブル設計に間違いがある
実際にデータ移行をしないとテーブル設計のミスには気づけないことがあります。
遭遇したケースとして
- migrationファイルとER図が一致していない。
- 移行元にないunique制約が移行先についている。
- データ型が一致しない。
テーブル変更の際にアプリケーション側の変更も必要になるので常にメンバーに共有しましょう。
最後に
他にも躓きはありましたが、よくあるパターンを書き出しました。
まとめると大事なことは下記になります。
- DBMSの仕様が違うことを常に意識する。
- 手動でSQLを実行する。
- dumpしてデータを確認する, 手動で入れてみる。
- ORMのログを見る。
- データに詳しい人を常に巻き込む。
Postgresは触ったことがなかったのでDB接続から苦戦していましたｗ
DBによってクエリの書き方や文字列の扱いに違いがあるのは学びになりました。
Discussion