pgloader で MySQL から PostgreSQL へデータを移行するためのシェルスクリプト（MySQL 8 対応）

ソフトウェア開発

pgloader とは

MySQL のデータを PostgreSQL にデータを移行するためのソフトウエアです*1。Heroku でも利用を推奨しています 。

github.com

pgloader.io

pgloader.readthedocs.io

pgloader の問題点

MySQL 8 以降から移行する場合には認証の関係でそのままでは動かないことがあります。

github.com

問題点を解決する

上記の問題点を解決するために、以下の手順を採ります。

  • MySQL から mysqldump でダンプファイルを取得する
  • 得られたダンプファイルを一時的な MySQL に保存する
  • 一時的に保存された MySQL から、一時的に作成した PostgreSQL に対して pgloader を用いてデータ移行する
  • 一時的に作成された PostgreSQL から pg_dump を用いてデータをダンプする
  • 得られたダンプデータを正式移行先の PostgreSQL にインポートする

具体的にどうやるか

前述の Issue で議論されていますが、みんな何とかしてこれを楽してやりたいと思っていることなのでしょう、シェルスクリプトを作成してくれた方がいました。

github.com

このシェルスクリプトを少々修正して、以下のシェルスクリプトを作りました。ほぼ使い捨てなので、手続きを書き下した冗長なものになっています。やっていることは難しくないので、必要に応じて変更して利用するとよいです。

シェルスクリプト

実行するためには下記スクリプトを保存し、実行権限を付け、引数を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

注意点

  • MySQL からのダンプは手動で行います*2
  • PostgreSQL のインポートも手動で行います*3
  • MySQL の「データベース名」が PostgreSQL の「スキーマ名」として移行されます
    • したがって、デフォルトでは PostgreSQL の「スキーマ名」が public にならないため、ダンプ内の SQL で文字列置換をしています*4
      • ALTER SCHEMA $DB_NAME RENAME TO public; みたいにすればいいのですが、権限がないとエラーになりますので、上記では文字列置換を採用しています

*1:必ずしもそれ専用ではないが、その用途に適している

*2:スクリプトの最後にコメント行で具体的なやり方が書いてあります

*3:スクリプトの最後にコメント行で具体的なやり方が書いてあります

*4:したがって、スキーマ名は public で固定です

Powered by はてなブログ