ラボ > DB、MySQL、postgreSQL

mySQLからpostgreSQLにデータを移行させる

作成日:2024-06-28, 更新日:2024-06-28

やりたいこと

mySQLの全テーブル、全レコードをpostgreSQLに移す必要がでてきた

対処

何かしらのツールを使う、手動で頑張る…などいろいろ

問題点

mySQLとpostgreSQLでもろもろお作法が異なる
mySQLではphpMyAdminなどで一括でエクスポートやインポートなどがあるけど…postgreSQLには無いっぽい
pgAdminであるかどうかは不明…探したけど見つけられず…

mySQLとpostgreSQLのお作法

カラムの型の設定が一部異なる。他にもいろいろとあるっぽい
CSVでの扱いがほんのり異なる。カラムの区切り文字やレコードの終端文字など

インポートとエクスポート

何かしらのツールを使うなら別だけど手動でする場合…

mySQLから各テーブルごとにCSVをエクスポート
postgreSQLへ各テーブルごとインポート処理を行う

手動で頑張る…

手動といってもスクリプトで対応
※スクリプトはchatGPTさんに用意してもらった。実行はまだしていないので動作は不明（2024-06-28時点）

手順

データはなんとかなるっぽいけど…テーブルはどうにかできるのかな？

postgreSQLでテーブルを用意
スクリプト: mySQLで各テーブルの全レコードをCSVにエクスポート
スクリプト: postgreSQLで各テーブルごとCSVをインポート
各スクリプトに実行権限を付与し、実行

postgreSQLでテーブルを用意

とりあえず…がんばる。

慣れていないならmysqlからエクスポートしてSQLをそのままpostgreSQLで実行
エラーが出たら都度、対応

スクリプト: mySQLで各テーブルの全レコードをCSVにエクスポート

各テーブルの全レコードをCSV出力
ファイル名はテーブル名で保存
スクリプトファイルに実行権限を付与し実行
出来上がったCSVファイルたちをpostgreSQLで使えるように適切な場所に移動

▼「xxx.sh」で作成

#!/bin/bash

USER="your_username"
PASSWORD="your_password"
DATABASE="your_database"
OUTPUT_DIR="/path/to/output/directory"

mkdir -p $OUTPUT_DIR

TABLES=$(mysql -u $USER -p$PASSWORD -D $DATABASE -e 'SHOW TABLES;' | tail -n +2)

for TABLE in $TABLES; do
  echo "Exporting $TABLE"
  mysql -u $USER -p$PASSWORD -D $DATABASE -e "SELECT * INTO OUTFILE '$OUTPUT_DIR/${TABLE}.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM $TABLE;"
done

スクリプト: postgreSQLで各テーブルごとCSVをインポート

postgreSQLに存在しないテーブルはスキップ
各CSVはpostgreSQL用に変換してインポート
スクリプトに実行権限を付与し、実行

▼「xxx.sh」で作成

#!/bin/bash

USER="your_pg_username"
DATABASE="your_pg_database"
INPUT_DIR="/path/to/input/directory"

for FILE in $INPUT_DIR/*.csv; do
  TABLE_NAME=$(basename $FILE .csv)
  if psql -U $USER -d $DATABASE -c "\dt" | cut -d '|' -f 2 | grep -qw $TABLE_NAME; then
    echo "Importing $TABLE_NAME"
    psql -U $USER -d $DATABASE -c "\COPY $TABLE_NAME FROM '$FILE' WITH (FORMAT csv, DELIMITER ';', QUOTE '\"', ESCAPE '\"', HEADER true)"
  else
    echo "Skipping $TABLE_NAME, table does not exist"
  fi
done