Go to Qiita Advent Calendar 2024 Top
LoginSignup
13
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ryouma_nagare

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

Last updated at Posted at 2019-02-21

目的

  • 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列）
  • 既存のファイルダウンロード

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

13
21
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?