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 を参考に

```shell
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設定を変更する

変更前

```file:%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
```

変更後

```file:%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を導入する

```shell
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マイグレーション

```shell
RAILS_ENV=production bundle exec rake db:migrate
RAILS_ENV=production bundle exec rake redmine:plugins:migrate 
```

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

```shell
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マイグレーション」** から再実行

### ファイル構成

```text
|-- 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)`としています。

 
```file: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}
```

```file: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}
```

```file: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}
```

```file: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のディレクトリが違う場合はよしなに。

```file:
#!/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列）
* 既存のファイルダウンロード

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