Edited at

RedmineのデータベースをMySQLからPostgreSQLへ移行した


目的


  • CentOS7

  • MariaDB:10.1.6

で動作している Redmine 3.4.8 の データベースを


  • PostgreSQL 11.1

に変更し、データを移行する


前提


  • Redmineのディレクトリを %REDMINE_DIR% と記述します

  • Redmineの実行ユーザを %REDMINE_USER% と記述します

  • mysqlコマンドが使用可能

  • MySQLのタイムゾーンはJST

  • psqlコマンドが使用可能

  • あえてpgLoaderを使わない

のため、現実的には同一ホスト間で動くMySQL→PostgreSQLへの移行となります。


手順


  1. Redmineを停止する(割愛)

  2. PostgreSQLをインストールする(割愛)

  3. PostgreSQLにRedmine用のDB、ユーザを作成する(割愛)


4.embulkを取得し、プラグインをインストールする

https://github.com/embulk/embulk を参考に

curl --create-dirs -o ~/.embulk/bin/embulk -L "https://dl.embulk.org/embulk-latest.jar"

chmod +x ~/.embulk/bin/embulk

~/.embulk/bin/embulk gem install embulk-input-mysql
~/.embulk/bin/embulk gem install embulk-output-postgresql


5.RedmineのDB設定を変更する

変更前


%REDMINE_DIR%/config/database.yml

production:

adapter: mysql2
database: YOUR_DB_MS
host: YOUR_HOST_MS
username: YOUR_NAME_MS
password: YOUR_PASS_MS
encoding: utf8

変更後


%REDMINE_DIR%/config/database.yml

production:

adapter: postgresql
database: YOUR_DB_PG
host: YOUR_HOST_PG
username: YOUR_NAME_PG
password: YOUR_PASS_PG
encoding: utf8


6. postgresqlモジュールのgemを導入する

su - %REDMINE_USER% -s /bin/bash

cd %REDMINE_DIR%
bundle config build.pg --with-pg-config=/usr/pgsql-11/bin/pg_config
bundle install


7. Redmineの初期DB、およびプラグインのDBマイグレーション

RAILS_ENV=production bundle exec rake db:migrate

RAILS_ENV=production bundle exec rake redmine:plugins:migrate

念のため、初期投入のデータを確認。

psql -U YOUR_NAME_PG -h YOUR_HOST_PG YOUR_DB_PG

select relname, n_live_tup from pg_stat_user_tables where n_live_tup > 0;
relname | n_live_tup
-------------------+------------
users | 3
roles | 2
email_addresses | 1
schema_migrations | 281

schema_migrations はこの作業で作成されたレコードのため、移行対象外とします。


8.スクリプトによる移行


留意点


  • embulkはBLOBに対応していないため、単純移行ではなく、queryが必要

  • MySQLのIDはAUTO INCREMENT列のため、MAX(ID)をPostgreSQLのシリアル列が持つシーケンスに反映する必要がある

  • PostgreSQLへの移行のみを目的としているため、truncate_insertとしている

  • embulkの実行が失敗した場合にはテンポラリテーブルが残ることがあるため、PostgreSQLのテーブルを全削除して 「7. Redmineの初期DB、およびプラグインのDBマイグレーション」 から再実行


ファイル構成

|-- extra

| |-- import_in_progresses.yml.liquid
| |-- open_id_authentication_associations.yml.liquid
| `-- wiki_content_versions.yml.liquid
|-- mysql2pgsql.sh
`-- mysql2pgsql.yml.liquid

extra:BLOBを含むテーブルの定義ファイルを配置

mysql2pgsql.sh:移行スクリプト

mysql2pgsql.yml.liquid:BLOBを含まないテーブルの定義ファイル


embulk定義ファイル

BLOB列を単に {value_type: string} とした際には文字化けを起こしたため、convert (XXX using UTF8)としています。


import_in_progresses.yml.liquid

in:

type: mysql
host: {{ env.MYSQL_HOST }}
user: {{ env.MYSQL_USER }}
password: {{ env.MYSQL_PASS }}
database: {{ env.MYSQL_DB }}
default_timezone: "Asia/Tokyo"
options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}
query: |
SELECT id
,user_id
,quote_char
,col_sep
,encoding
,created
,convert(csv_data using utf8) as csv_data
FROM {{ env.TABLE }}
column_options:
csv_data: {value_type: string}

out:
type: postgresql
host: {{ env.PGSQL_HOST }}
user: {{ env.PGSQL_USER }}
password: {{ env.PGSQL_PASS }}
database: {{ env.PGSQL_DB }}
table: {{ env.TABLE }}
default_timezone: "Asia/Tokyo"
mode: truncate_insert
column_options:
csv_data: {value_type: string}



open_id_authentication_associations.yml.liquid

in:

type: mysql
host: {{ env.MYSQL_HOST }}
user: {{ env.MYSQL_USER }}
password: {{ env.MYSQL_PASS }}
database: {{ env.MYSQL_DB }}
default_timezone: "Asia/Tokyo"
options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}
query: |
SELECT id
,issued
,lifetime
,handle
,assoc_type
,convert(server_url using utf8) as server_url
,convert(secret using utf8) as secret
FROM {{ env.TABLE }}
column_options:
server_url: {value_type: string}
secret: {value_type: string}

out:
type: postgresql
host: {{ env.PGSQL_HOST }}
user: {{ env.PGSQL_USER }}
password: {{ env.PGSQL_PASS }}
database: {{ env.PGSQL_DB }}
table: {{ env.TABLE }}
default_timezone: "Asia/Tokyo"
mode: truncate_insert
column_options:
server_url: {value_type: string}
secret: {value_type: string}



wiki_content_versions.yml.liquid

in:

type: mysql
host: {{ env.MYSQL_HOST }}
user: {{ env.MYSQL_USER }}
password: {{ env.MYSQL_PASS }}
database: {{ env.MYSQL_DB }}
default_timezone: "Asia/Tokyo"
options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}
query: |
SELECT id
,wiki_content_id
,page_id
,author_id
,convert(data using utf8) as data
,compression
,comments
,updated_on
,version
FROM {{ env.TABLE }}
column_options:
data: {value_type: string}

out:
type: postgresql
host: {{ env.PGSQL_HOST }}
user: {{ env.PGSQL_USER }}
password: {{ env.PGSQL_PASS }}
database: {{ env.PGSQL_DB }}
table: {{ env.TABLE }}
default_timezone: "Asia/Tokyo"
mode: truncate_insert
column_options:
data: {value_type: string}



mysql2pgsql.yml.liquid

in:

type: mysql
host: {{ env.MYSQL_HOST }}
user: {{ env.MYSQL_USER }}
password: {{ env.MYSQL_PASS }}
database: {{ env.MYSQL_DB }}
table: {{ env.TABLE }}
default_timezone: "Asia/Tokyo"
options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo}

out:
type: postgresql
host: {{ env.PGSQL_HOST }}
user: {{ env.PGSQL_USER }}
password: {{ env.PGSQL_PASS }}
database: {{ env.PGSQL_DB }}
table: {{ env.TABLE }}
mode: truncate_insert
default_timezone: "Asia/Tokyo"



シェルスクリプト

基本的にはMySQL/PostgreSQLの接続情報のみ変更してください。

mysql/psqlのディレクトリが違う場合はよしなに。

#!/bin/bash

#######################################################
# 環境依存値
#######################################################
export MYSQL_HOST=YOUR_HOST_MS
export MYSQL_DB=YOUR_DB_MS
export MYSQL_USER=YOUR_NAME_MS
export MYSQL_PASS=YOUR_PASS_MS
MYSQL_BIN=/bin/mysql
export PGSQL_HOST=YOUR_HOST_PG
export PGSQL_DB=YOUR_DB_PG
export PGSQL_USER=YOUR_NAME_PG
export PGSQL_PASS=YOUR_PASS_PG
PGSQL_BIN=/usr/pgsql-11/bin/psql
EMBULK_BIN=~/.embulk/bin/embulk

export PGPASSWORD=${PGSQL_PASS}
export MYSQL_PWD=${MYSQL_PASS}

LOG_FILE=./mysql2pgsql_$(date +'%Y%m%d_%H%M%S').log

#######################################################
# Serial列を持つテーブルの抽出(PostgreSQL)
#######################################################
SERIAL_TABLES=$(export LANG=C ; $PGSQL_BIN ${PGSQL_DB} -h ${PGSQL_HOST} -U ${PGSQL_USER} -c '\d' | grep sequence | awk '{print $3}' | sed -e 's/_id_seq$//g')

#######################################################
# AUTO INCREMENTの値をsequenceに反映
#######################################################
for TABLE in ${SERIAL_TABLES}
do
# MySQLの該当テーブルからMAX(ID)を取得
MAX_ID=$(${MYSQL_BIN} ${MYSQL_DB} -h ${MYSQL_HOST} -u ${MYSQL_USER} -e " SELECT MAX(ID) from ${TABLE}" --skip-column-names -s)

# NULLでなければ(PostgreSQLのシーケンスに値をセット
if [ "${MAX_ID}" != "NULL" ]; then
$PGSQL_BIN ${PGSQL_DB} -h ${PGSQL_HOST} -U ${PGSQL_USER} -c "SELECT setval('${TABLE}_id_seq', ${MAX_ID}, true);" 1>/dev/null
fi
done

#######################################################
# テーブル一覧の抽出(MySQL)
#######################################################
ALL_TABLES=$(${MYSQL_BIN} ${MYSQL_DB} -h ${MYSQL_HOST} -u ${MYSQL_USER} -e "show tables" --skip-column-names -s)

#######################################################
# MySQLからPostgreSQLへテーブルデータを移行
#######################################################
for TABLE in ${ALL_TABLES}
do
# schema_migrationsは移行しない
if [ "$TABLE" != "schema_migrations" ]; then
export TABLE
echo importing ${TABLE} ...

# queryが書かれた定義ファイルがある場合はそちらを実行
if [ -e extra/${TABLE}.yml.liquid ]; then
${EMBULK_BIN} run extra/${TABLE}.yml.liquid 1>>${LOG_FILE}
# そうでなければ共通の定義ファイル
else
${EMBULK_BIN} run mysql2pgsql.yml.liquid 1>>${LOG_FILE}
fi

# 一応、件数だけ突合
MYSQL_CNT=$(${MYSQL_BIN} ${MYSQL_DB} -h ${MYSQL_HOST} -u ${MYSQL_USER} -e "SELECT COUNT(*) FROM ${TABLE}" --skip-column-names -s)
PGSQL_CNT=$($PGSQL_BIN ${PGSQL_DB} -h ${PGSQL_HOST} -U ${PGSQL_USER} -c "SELECT COUNT(*) FROM ${TABLE}" -t | tr -d ' ')
if [ ${MYSQL_CNT} == ${PGSQL_CNT} ]; then
RSLT=match
else
RSLT=not match!
fi

echo " ${RSLT} ... MySQL:${MYSQL_CNT} PostgreSQL:${PGSQL_CNT}"
fi
done


おわりに


  • 新規チケット作成

  • 新規プロジェクト作成

  • Wikiの履歴(BLOB列)

  • 既存のファイルダウンロード

などが問題ないことは確認できました。