PostgreSQL(EUC-JP)→MySQL(UTF-8)にデータ移行する5ステップ #MySQL

PostgreSQL上の文字コードがEUC-JPのデータをMySQLに移行する手順。

0.作業用のディレクトリを作る

作業しやすいように作業用のディレクトリをつくって、そこでファイル操作する。

mkdir pg2mysql-migration
cd pg2mysql-migration

1. 作業用PostgreSQL環境を作る

稼働中のサーバから持ってきたダンプを一旦取り込む用のPostgreSQL環境を作る。稼働中のDBから直接ダンプ出来る場合は、ここは飛ばし2へ進む。

Homebrewでインストール

brew install postgresql

適当な場所にDB用ディレクトリを作る

mkdir postgresql

DBを初期化する

initdb `pwd`/postgresql -E utf8

DBを起動する

postgres -D `pwd`/postgresql

データベースが起動しているか確かめる

psql -l

ダンプデータ取り込む用のDBを作る

createdb $db

DBが作られたか確認する

psql -l

ダンプデータをインポートする

pg_restore -d $db dump.sql

2.PostgreSQLから構造をダンプする

pg_dump --schema-only \
        --no-owner \
        --no-privileges \
        --disable-dollar-quoting $db > pg-schema.eucjp.sql

3.PostgreSQLからデータをダンプする

pg_dump --data-only \
        --no-owner \
        --no-privileges \
        --disable-dollar-quoting \
        --column-inserts $db > pg-data.eucjp.sql

--column-insertsをつけないと、MySQLにないCOPY構文になり移行に不便なのでつけておく。

4.ダンプデータの文字コードをUTF-8に変換する

nkfコマンドを使って変換する。MacならHomebrewで入れておく。

brew install nkf

nkfが文字コードをどう認識するか確認する。

$ nkf -g pg-schema.eucjp.sql
ASCII

$ nkf -g pg-data.eucjp.sql
EUCJP-MS

nkfで文字コードをUTF-8へ変換する。

nkf -wx pg-data.eucjp.sql > pg-data.utf8.sql

なお、半角カナを維持する必要がないシステムでは-xオプションは不要。

5.MySQL互換の形式にSQLを書き換える

スキーマファイルの書き換え

PostgreSQLのスキーマデータをコピーしてMySQL用のファイルを作る。

cp pg-schema.eucjp.sql mysql-schema.utf8.sql

書き換えてはMySQLにインポートしてみることを繰り返すので、テスト用のデータベースをMySQL上に作っておく。

CREATE DATABASE pg_migration_test DEFAULT CHARACTER SET utf8;

MySQLにインポートを試す。ファイルを修正する。インポートがうまくいくまでこれを繰り返す。

$ mysql -uroot pg_migration_test < mysql-schema.utf8.sql
ERROR 1193 (HY000) at line 5: Unknown system variable 'statement_timeout'

...修正する...

$ mysql -uroot -e "DROP DATABASE IF EXISTS pg_migration_test; CREATE DATABASE pg_migration_test DEFAULT CHARACTER SET utf8;"
$ mysql -uroot pg_migration_test < mysql-schema.utf8.sql
ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog' at line 1

...修正する...

$ mysql -uroot -e "DROP DATABASE IF EXISTS pg_migration_test; CREATE DATABASE pg_migration_test DEFAULT CHARACTER SET utf8;"
$ mysql -uroot pg_migration_test < mysql-schema.utf8.sql

...修正する...

修正する内容は大体次のものになる:

先頭のSET構文を削除します。

ALTER TABLE ONLY … のONLYを削除します。

CREATE INDEX … ON table_name USING btreeはUSING btreeをONの前に移動します。

PostgreSQLのデータをMySQLに移行する - Qiita

他には、::character varyingや::bigintを消し去ったりがある。

データファイルの書き換え

PostgreSQLのスキーマデータをコピーしてMySQL用のファイルを作る。

cp pg-data.utf8.sql mysql-data.utf8.sql

viなどでデータファイルを開き、MySQL非互換な部分を修正する。
これも非互換な部分は、先頭のSET構文。