pgloader とは
MySQL のデータを PostgreSQL にデータを移行するためのソフトウエアです*1。Heroku でも利用を推奨しています 。
pgloader の問題点
MySQL 8 以降から移行する場合には認証の関係でそのままでは動かないことがあります。
問題点を解決する
上記の問題点を解決するために、以下の手順を採ります。
- MySQL から
mysqldumpでダンプファイルを取得する - 得られたダンプファイルを一時的な MySQL に保存する
- 一時的に保存された MySQL から、一時的に作成した PostgreSQL に対して pgloader を用いてデータ移行する
- 一時的に作成された PostgreSQL から
pg_dumpを用いてデータをダンプする - 得られたダンプデータを正式移行先の PostgreSQL にインポートする
具体的にどうやるか
前述の Issue で議論されていますが、みんな何とかしてこれを楽してやりたいと思っていることなのでしょう、シェルスクリプトを作成してくれた方がいました。
このシェルスクリプトを少々修正して、以下のシェルスクリプトを作りました。ほぼ使い捨てなので、手続きを書き下した冗長なものになっています。やっていることは難しくないので、必要に応じて変更して利用するとよいです。
シェルスクリプト
実行するためには下記スクリプトを保存し、実行権限を付け、引数を3つ渡して実行します。3つの引数の詳細は次のとおりです。
- データベース名（共通）
- 入力する MySQL のダンプファイル名
- 出力する PostgreSQL のダンプファイル名
具体例は $ ./mysql_to_postgresql.sh my_happy_database mysql_dump_file.dump postgresql_dump_file.dump です。
#!/usr/bin/env bash # ヘルプメッセージ if [ -z "$3" ]; then echo "使い方: $0 [データベース名（共通）] [入力する MySQL のダンプファイル名] [出力する PostgreSQL のダンプファイル名]" echo "必要なソフトウェア: Docker" exit 1 fi if ! command -v docker &> /dev/null then echo "[$(date "+%Y/%m/%d %H:%M:%S")] Docker がインストールされていません。終了します。" exit fi set +e docker stop tmp_mysql_for_pgloader tmp_psql_for_pgloader &> /dev/null set -e DB_NAME=$1 SOURCE_MYSQL_DUMP=$2 OUTPUT_POSTGRESQL_DUMP=$3 # TODO: イメージのタグをユーザーが指定できるようにしてもいい MYSQL_IMAGE_TAG=latest POSTGRESQL_IMAGE_TAG=latest echo "========================================" echo "[$(date "+%Y/%m/%d %H:%M:%S")] データベース名（共通）: $DB_NAME" echo "[$(date "+%Y/%m/%d %H:%M:%S")] 入力する MySQL のダンプファイル名: $SOURCE_MYSQL_DUMP" echo "[$(date "+%Y/%m/%d %H:%M:%S")] 出力する PostgreSQL のダンプファイル名: $OUTPUT_POSTGRESQL_DUMP" echo "========================================" echo "[$(date "+%Y/%m/%d %H:%M:%S")] pgloader 用の MySQL を起動します。" docker run --rm --name tmp_mysql_for_pgloader -e MYSQL_ROOT_PASSWORD=mysql_root_password -p 13306:3306 -d mysql:$MYSQL_IMAGE_TAG --default-authentication-plugin=mysql_native_password docker run --rm willwill/wait-for-it -t 30 172.17.0.1:13306 while true; do set +e sleep 1; # Wait for two matches of waiting for connections since the first one is a temporary server count=$(docker logs tmp_mysql_for_pgloader 2>&1 | grep -E 'mysqld: ready for connections' -c) if [ "$count" -eq 2 ]; then set -e break fi done echo "[$(date "+%Y/%m/%d %H:%M:%S")] pgloader 用の MySQL が起動しました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] MySQL にダンプファイルをインポートします。" docker exec -i tmp_mysql_for_pgloader mysql -pmysql_root_password -e "create database $DB_NAME" docker exec -i tmp_mysql_for_pgloader mysql -pmysql_root_password "$DB_NAME" < "$SOURCE_MYSQL_DUMP" echo "[$(date "+%Y/%m/%d %H:%M:%S")] MySQL へのダンプファイルのインポートが完了しました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] pgloader用の PostgreSQL を起動します。" docker run --rm --name tmp_psql_for_pgloader -e POSTGRES_PASSWORD=postgresql_password -p 15432:5432 -d postgres:$POSTGRESQL_IMAGE_TAG docker run --rm willwill/wait-for-it -t 30 172.17.0.1:15432 docker exec -e POSTGRES_PASSWORD=postgresql_password tmp_psql_for_pgloader createdb -U postgres "$DB_NAME" echo "[$(date "+%Y/%m/%d %H:%M:%S")] pgloader用の PostgreSQL が起動しました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] pgloader を実行して MySQL から PostgreSQL にデータを移行して記録します。" docker run --rm dimitri/pgloader pgloader mysql://root:mysql_root_password@172.17.0.1:13306/"$DB_NAME" postgresql://postgres:postgresql_password@172.17.0.1:15432/"$DB_NAME" echo "[$(date "+%Y/%m/%d %H:%M:%S")] MySQL から PostgreSQL へのデータ移行が完了しました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] PostgreSQL のダンプを出力します。" docker exec -i -e POSTGRES_PASSWORD=postgresql_password tmp_psql_for_pgloader pg_dump -U postgres --no-privileges --no-owner "$DB_NAME" > "$OUTPUT_POSTGRESQL_DUMP" echo "[$(date "+%Y/%m/%d %H:%M:%S")] PostgreSQL のダンプが出力されました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] 後処理として出力ダンプ内のスキーマのリネームをします。" sed -e "s/$DB_NAME\./public\./g" "tmp_$OUTPUT_POSTGRESQL_DUMP" > "tmp_sed_public_dot_$OUTPUT_POSTGRESQL_DUMP" sed -e "s/$DB_NAME;/public;/g" "tmp_sed_public_dot_$OUTPUT_POSTGRESQL_DUMP" > "tmp_sed_public_semi_colon_$OUTPUT_POSTGRESQL_DUMP" sed -e "s/CREATE SCHEMA public;//g" "tmp_sed_public_semi_colon_$OUTPUT_POSTGRESQL_DUMP" > "tmp_sed_remove_create_schema_$OUTPUT_POSTGRESQL_DUMP" cp "tmp_sed_remove_create_schema_$OUTPUT_POSTGRESQL_DUMP" "$OUTPUT_POSTGRESQL_DUMP" rm "tmp_$OUTPUT_POSTGRESQL_DUMP" "tmp_sed_public_dot_$OUTPUT_POSTGRESQL_DUMP" "tmp_sed_public_semi_colon_$OUTPUT_POSTGRESQL_DUMP" "tmp_sed_remove_create_schema_$OUTPUT_POSTGRESQL_DUMP" echo "[$(date "+%Y/%m/%d %H:%M:%S")] 出力ダンプ内のスキーマのリネームが完了しました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] MySQL および PostgreSQL のコンテナを停止します。" docker stop tmp_mysql_for_pgloader tmp_psql_for_pgloader echo "[$(date "+%Y/%m/%d %H:%M:%S")] MySQL および PostgreSQL のコンテナが停止しました。" echo "[$(date "+%Y/%m/%d %H:%M:%S")] データ移行が完了しました。" echo "========================================" echo "以下の内容はご自分で行ってください。" echo "- Docker Image の削除" echo "- ダンプしたファイルの PostgreSQL へのインポート" # MySQL からダンプするコマンド # $ mysqldump -h 127.0.0.1 -P 12345 -uUSERNAME -pPASSWORD -r foobar.dump --single-transaction DATABASE_NAME # PostgreSQL で新たにデータベースを作成する # $ psql -d postgres -c "create database DATABASE_NAME with template = template0 encoding = 'UTF8' lc_collate = 'ja_JP.UTF-8' lc_ctype = 'ja_JP.UTF-8'" # PostgreSQL へダンプファイルをインポートするコマンド # $ PGPASSWORD=hogehoge psql -d DATABASE_NAME -U USERNAME -h 127.0.0.1 -p 12345 < foobar.dump # Docker Image の削除（不要なら実行しなくていい） # $ docker image rm willwill/wait-for-it:latest # $ docker image rm mysql:latest # $ docker image rm postgres:latest
