Mattermostを『v7.8.0＠MySQL』から『v8.1.2＠PostgreSQL』に移行

# なぜやるのか
DBをMySQLからPostgreSQLに変更したい。（MattermostはPostgreSQL推しになって、v11くらいでサポートしなくなりそうなので）
OSをCentOSからUbuntuに変更したい。（CentOSが～というのもあるし、やはりMattermostがUbuntu推しっぽいので）

## 既存環境
- OS：CentOS7
- DB：MySQL5.7
- Mattermost7.8.0（tar.gz）

## 移行したい環境
- OS：Ubuntu22.04　※OSは次回持越しで今回はCentOS7のまま
- DB：PostgreSQL15
- Mattermost8.1.2（deb package）　※deb package化は次回持越しで今回はtar.gzのまま

開発元と何度かやり取りしていたら、DB移行の検討を進めてくれて、以下の手順を作成してくれた。
この手順を評価してみて欲しいとのこと。（そのフィードバックが欲しい、、と）
https://docs.mattermost.com/deploy/postgres-migration.html

※開発元にはこのページを共有しよう！

## MySQLからPostgreSQLへの移行：本家が作ってくれた手順に沿ってやってみた
### 評価用サーバの用意
1. Proxmox上で動いているので既存サーバをクローンで複製
1. NICを無効にした状態で起動し、IPアドレスが重複しないように変更しシャットダウン
1. NICを有効にして起動

### Mattermost停止
- `systemctl stop mattermost.service`で停止する。

### ツールの準備
- pgLoader
  ```
  # yum install epel-release
  # yum install sbcl freetds
  # yum -y install yum-utils rpmdevtools @"Development Tools"
  # git clone https://github.com/dimitri/pgloader.git
  # cd pgloader
  # yum-builddep pgloader.spec
  # mkdir -p /root/rpmbuild/SOURCES/
  # spectool -g -R pgloader.spec
  # rpmbuild -ba pgloader.spec
  # rpm -ivh /root/rpmbuild/RPMS/x86_64/pgloader-3.6.9-22.el7.x86_64.rpm
  ```
- go
  ```
  # cd /root/
  # wget https://golang.org/dl/go1.21.1.linux-amd64.tar.gz
  # tar -xvf go1.21.1.linux-amd64.tar.gz
  # echo "export PATH=$PATH:/root/go/bin" >> ~/.bashrc
  # source ~/.bashrc
  ```
- morph CLI
  ```
  # go install github.com/mattermost/morph/cmd/morph@v1
  ```
- dbcmp
  ```
  # go install github.com/mattermost/dbcmp/cmd/dbcmp@latest
  ```
### PostgreSQLの準備
- インストール
  ```
  # yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  # yum install -y postgresql15-server
  # /usr/pgsql-15/bin/postgresql-15-setup initdb
  # systemctl enable postgresql-15
  # systemctl start postgresql-15
  ```
- 初期設定
  ```
  # sudo -u postgres psql
  postgres=# CREATE DATABASE mattermost WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;
  postgres=# CREATE USER mmuser WITH PASSWORD '**********';
  postgres=# GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;
  postgres=# GRANT USAGE, CREATE ON SCHEMA PUBLIC TO mmuser;
  postgres=# \q
  # vim /var/lib/pgsql/15/data/pg_hba.conf
  local   all             all                                     trust　※peerをtrustに変更
  host    all             all             127.0.0.1/32            trust　※scram-sha-256をtrustに変更
  host    all             all             ::1/128                 trust　※scram-sha-256をtrustに変更
  # systemctl reload postgresql-15
  # psql --dbname=mattermost --username=mmuser --password　※接続確認
  Password: 
  psql (15.4)
  Type "help" for help.

  mattermost-> \q
  ```
- ターゲットデータベースの準備
  ```
  # git clone -b release-7.8 https://github.com/mattermost/mattermost-server.git --depth=1
  # cd mattermost-server/
  # morph apply up --driver postgres --dsn "postgres://postgres@localhost:5432/mattermost?sslmode=disable" --path ./db/migrations/postgres --number -1
  ```
### スキーマの違いを調整
#### データ型のキャスト
- 以下のSQLでサイズ超過しているレコードを全て削除する。
  ```
  # mysql -u root -p -h localhost mattermost
  ```
  ```
  -- Audits table
  DELETE FROM Audits WHERE LENGTH(Action) > 512;
  DELETE FROM Audits WHERE LENGTH(ExtraInfo) > 1024;

  -- ClusterDiscovery table
  DELETE FROM ClusterDiscovery WHERE LENGTH(HostName) > 512;

  -- Commands table
  DELETE FROM Commands WHERE LENGTH(IconURL) > 1024;
  DELETE FROM Commands WHERE LENGTH(AutoCompleteDesc) > 1024;
  DELETE FROM Commands WHERE LENGTH(AutoCompleteHint) > 1024;

  -- Compliances table
  DELETE FROM Compliances WHERE LENGTH(Keywords) > 512;
  DELETE FROM Compliances WHERE LENGTH(Emails) > 1024;

  -- FileInfo table
  DELETE FROM FileInfo WHERE LENGTH(Path) > 512;
  DELETE FROM FileInfo WHERE LENGTH(ThumbnailPath) > 512;
  DELETE FROM FileInfo WHERE LENGTH(PreviewPath) > 512;
  DELETE FROM FileInfo WHERE LENGTH(Name) > 256;
  DELETE FROM FileInfo WHERE LENGTH(MimeType) > 256;

  -- LinkMetadata table
  DELETE FROM LinkMetadata WHERE LENGTH(URL) > 2048;

  -- RemoteClusters table
  DELETE FROM RemoteClusters WHERE LENGTH(SiteURL) > 512;
  DELETE FROM RemoteClusters WHERE LENGTH(Topics) > 512;

  -- Sessions table
  DELETE FROM Sessions WHERE LENGTH(DeviceId) > 512;

  -- Systems table
  DELETE FROM Systems WHERE LENGTH(Value) > 1024;

  -- UploadSessions table
  DELETE FROM UploadSessions WHERE LENGTH(FileName) > 256;
  DELETE FROM UploadSessions WHERE LENGTH(Path) > 512;
  ```
  FileInfoテーブルのcontentカラムでサイズ超過があったので追加で削除する。
  ```
  DELETE FROM FileInfo WHERE LENGTH(content) > 1048575;
  ```

#### 「Full-text indexes」の問題の対応
- 一部の単語がフルテキスト検索インデックスの最大トークン長を超える可能性があるため指定されたインデックスを削除
  ```
  # psql -U postgres -d mattermost
  mattermost=# DROP INDEX IF EXISTS idx_posts_message_txt;
  mattermost=# DROP INDEX IF EXISTS idx_fileinfo_content_txt;
  mattermost=# \q
  ```
#### その他pgloader実行して出た様々なエラーに対応（環境ごとに大きく変わりそうです）
- テーブルにカラムが見つからないエラーが発生するので追加
  - sharedchannelremotes テーブルに description カラムを追加
    ```
    # psql -U postgres -d mattermost
    mattermost=# ALTER TABLE sharedchannelremotes ADD COLUMN description text;
    ```
  - sharedchannelremotesテーブルにnextsyncatカラムを追加
    ```
    psql -U postgres -d mattermost -c "ALTER TABLE sharedchannelremotes ADD COLUMN nextsyncat BIGINT;"
    ```
- mattermostデータベースのデフォルトのsearch_pathをpublicに設定
  ```
  # psql -U postgres
  ALTER DATABASE mattermost SET search_path TO public;
  ```
  設定の確認
  ```
  \c mattermost
  SHOW search_path;
  ```
- さらに、`schema_migrations` というテーブルが見つからないというエラーが出るので、schema_migrationsをPostgreSQLに追加。
  ```
  # mysqldump -u root -p -h localhost mattermost schema_migrations --no-data > schema_migrations.sql
  # vim schema_migrations_pgsql.sql
  -- PostgreSQL用に変換されたスキーマ
  DROP TABLE IF EXISTS schema_migrations;

  CREATE TABLE schema_migrations (
    version bigint NOT NULL PRIMARY KEY,
    dirty boolean NOT NULL
  );
  # psql -U postgres -h localhost -d mattermost < schema_migrations_pgsql.sql
  NOTICE:  table "schema_migrations" does not exist, skipping
  DROP TABLE
  CREATE TABLE
  ```
- db_migrationsとsystemsテーブルでキーの重複エラーが出るのでデータを一旦削除
  ```
  # psql -U postgres -d mattermost
  DELETE FROM db_migrations;
  DELETE FROM systems;
  ```
### データの移行
- 設定ファイルの作成
  {{ .mysql_user }}、{{ .mysql_password }}、{{ .source_schema }}
  {{ .pg_user }}、{{ .pg_password }}、{{ .target_schema }}
  は、全て置換する。
  パスワードにアットマークが入っているときは「@@」とすると良い。
  ```
  # vim migration.load
  LOAD DATABASE
       FROM      mysql://root:*******@localhost:3306/mattermost
       INTO      pgsql://postgres@localhost:5432/mattermost

  WITH data only,
       workers = 8, concurrency = 1,
       multiple readers per thread, rows per range = 50000,
       create no tables, create no indexes,
       preserve index names

  SET PostgreSQL PARAMETERS
       maintenance_work_mem to '512MB',
       work_mem to '64MB'

  SET MySQL PARAMETERS
        net_read_timeout  = '600',
        net_write_timeout = '600'

  CAST column Channels.Type to "channel_type" drop typemod,
       column Teams.Type to "team_type" drop typemod,
       column UploadSessions.Type to "upload_session_type" drop typemod,
       column Drafts.Priority to text,
       type int when (= precision 11) to integer drop typemod,
       type bigint when (= precision 20) to bigint drop typemod,
       type text to varchar drop typemod,
       type tinyint when (<= precision 4) to boolean using tinyint-to-boolean,
       type json to jsonb drop typemod

  EXCLUDING TABLE NAMES MATCHING ~<IR_>, ~<focalboard>

  BEFORE LOAD DO
       $$ ALTER SCHEMA public RENAME TO mattermost; $$,
       $$ DROP INDEX IF EXISTS idx_posts_message_txt; $$,
       $$ DROP INDEX IF EXISTS idx_fileinfo_content_txt; $$

  AFTER LOAD DO
       $$ UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92; $$,
       $$ CREATE INDEX IF NOT EXISTS idx_posts_message_txt ON mattermost.posts USING gin(to_tsvector('english', message)); $$,
       $$ CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON mattermost.fileinfo USING gin(to_tsvector('english', content)); $$,
       $$ ALTER SCHEMA mattermost RENAME TO public; $$,
       $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,
       $$ ALTER USER postgres SET SEARCH_PATH TO 'public'; $$;
  ```


- pgloaderの実行
  ```
  # pgloader migration.load | tee migration.log
  ```

### データを比較

```
dbcmp --source "mmuser:******@tcp(localhost:3306)/mattermost" --target "postgres://postgres@localhost:5432/mattermost?sslmode=disable" --exclude="db_migrations,ir_,focalboard,systems"
```
※DSN（Data Source Name）の指定の仕方が分かりにくいし、アットマークがパスワードに入っている時にどうしてもエスケープできずエラーになる。（%40、@@、\\@、を試したけどだめ）

~~うまく比較できたタイミングもあったが、上記の手順でやり直したところ以下のエラーが出るようになった。~~
`error during comparison: could not compute src checksum: could not select checksum: sql: Scan error on column index 0, name "a": converting NULL to uint is unsupported`
※本番移行時には正常に比較が完了した。

```
Database values differ. Tables: Audits, Sessions, Status, FileInfo
```
レコード数を比較するスクリプトを作成して確認してみる。
```
# vim check_record_counts.sh
# ファイル名: check_record_counts.sh
#!/bin/bash

# MySQLの接続情報
MYSQL_USER="mmuser"
MYSQL_PASSWORD="********"
MYSQL_DATABASE="mattermost"

# PostgreSQLの接続情報
PG_USER="postgres"
PG_PASSWORD=""
PG_DATABASE="mattermost"

# 差分をチェックするテーブルのリスト
TABLES=("Status" "Audits" "Sessions" "Jobs" "Threads" "FileInfo")

for table in "${TABLES[@]}"; do
    # PostgreSQLのテーブル名は小文字
    pg_table=$(echo $table | tr '[:upper:]' '[:lower:]')
    
    # MySQLとPostgreSQLのそれぞれのテーブルのレコード数を取得
    mysql_count=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -D$MYSQL_DATABASE -Bse "SELECT COUNT(*) FROM $table;")
    pg_count=$(PGPASSWORD=$PG_PASSWORD psql -U $PG_USER -d $PG_DATABASE -t -c "SELECT COUNT(*) FROM public.$pg_table;")

    # 差分を表示
    if [ "$mysql_count" -ne "$pg_count" ]; then
        echo "Table $table: MySQL has $mysql_count records, PostgreSQL has $pg_count records."
    else
        echo "Table $table: Record counts match ($mysql_count)."
    fi
done
```
```
# chmod 755 check_record_counts.sh
# ./check_record_counts.sh 2>/dev/null
Table Status: Record counts match (266).
Table Audits: MySQL has 7503200 records, PostgreSQL has  7503111 records.
Table Sessions: Record counts match (683).
Table Jobs: Record counts match (79753).
Table Threads: Record counts match (56497).
Table FileInfo: Record counts match (79810).
```
Auditsテーブルの数が違うが他は同数、このまま進めてみる。

### ~~Playbookの移行~~
**移行後Playbookにアクセスできなくなるので、事前に各PlaybookをJSONでエクスポートしておき、移行後にインポートを手動で行うことにして、この手順は省略**
<details>
<summary>Details</summary>

- ir_playbookmember_playbookidテーブルでエラーが出るのでデータを一旦削除
  ```
  # psql -U postgres -d mattermost
  DROP INDEX IF EXISTS ir_playbookmember_playbookid;
  DROP INDEX IF EXISTS ir_playbookmember_playbookid_memberid_key;
  ```
- 設定ファイルの作成
  ```
  # vim playbooks.load
  LOAD DATABASE
       FROM      mysql://mmuser:********@localhost:3306/mattermost
       INTO      pgsql://postgres@localhost:5432/mattermost

  WITH include drop, create tables, create indexes, no foreign keys,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000,
      preserve index names

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB'

  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'

  CAST column IR_ChannelAction.ActionType to text drop typemod,
       column IR_ChannelAction.TriggerType to text drop typemod,
       column IR_Incident.ChecklistsJSON to "json" drop typemod

  INCLUDING ONLY TABLE NAMES MATCHING
      ~/IR_/

  BEFORE LOAD DO
      $$ ALTER SCHEMA public RENAME TO mattermost; $$

  AFTER LOAD DO
      $$ ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN TriggerType TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ReminderMessageTemplate TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ReminderMessageTemplate SET DEFAULT ''::text;  $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedInvitedUserIDs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedInvitedUserIDs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedWebhookOnCreationURLs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedWebhookOnCreationURLs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedInvitedGroupIDs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedInvitedGroupIDs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN Retrospective TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN Retrospective SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN MessageOnJoin TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN MessageOnJoin SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedWebhookOnStatusUpdateURLs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedWebhookOnStatusUpdateURLs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN CategoryName TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN CategoryName SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedBroadcastChannelIds TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ConcatenatedBroadcastChannelIds SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ChannelIDToRootID TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Incident ALTER COLUMN ChannelIDToRootID SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ReminderMessageTemplate TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ReminderMessageTemplate SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedInvitedUserIDs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedInvitedUserIDs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedWebhookOnCreationURLs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedWebhookOnCreationURLs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedInvitedGroupIDs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedInvitedGroupIDs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN MessageOnJoin TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN MessageOnJoin SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN RetrospectiveTemplate TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN RetrospectiveTemplate SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedWebhookOnStatusUpdateURLs TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedWebhookOnStatusUpdateURLs SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedSignalAnyKeywords TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedSignalAnyKeywords SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN CategoryName TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN CategoryName SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedBroadcastChannelIds TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ConcatenatedBroadcastChannelIds SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN RunSummaryTemplate TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN RunSummaryTemplate SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ChannelNameTemplate TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Playbook ALTER COLUMN ChannelNameTemplate SET DEFAULT ''::text; $$,
      $$ ALTER TABLE mattermost.IR_PlaybookMember ALTER COLUMN Roles TYPE varchar(65536); $$,
      $$ ALTER TABLE mattermost.IR_Category_Item ADD CONSTRAINT ir_category_item_categoryid FOREIGN KEY (CategoryId) REFERENCES mattermost.IR_Category(Id); $$,
      $$ ALTER TABLE mattermost.IR_Metric ADD CONSTRAINT ir_metric_metricconfigid FOREIGN KEY (MetricConfigId) REFERENCES mattermost.IR_MetricConfig(Id); $$,
      $$ ALTER TABLE mattermost.IR_Metric ADD CONSTRAINT ir_metric_incidentid FOREIGN KEY (IncidentId) REFERENCES mattermost.IR_Incident(Id); $$,
      $$ ALTER TABLE mattermost.IR_MetricConfig ADD CONSTRAINT ir_metricconfig_playbookid FOREIGN KEY (PlaybookId) REFERENCES mattermost.IR_Playbook(Id); $$,
      $$ ALTER TABLE mattermost.IR_PlaybookAutoFollow ADD CONSTRAINT ir_playbookautofollow_playbookid FOREIGN KEY (PlaybookId) REFERENCES mattermost.IR_Playbook(Id); $$,
      $$ ALTER TABLE mattermost.IR_PlaybookMember ADD CONSTRAINT ir_playbookmember_playbookid FOREIGN KEY (PlaybookId) REFERENCES mattermost.IR_Playbook(Id); $$,
      $$ ALTER TABLE mattermost.IR_Run_Participants ADD CONSTRAINT ir_run_participants_incidentid FOREIGN KEY (IncidentId) REFERENCES mattermost.IR_Incident(Id); $$,
      $$ ALTER TABLE mattermost.IR_StatusPosts ADD CONSTRAINT ir_statusposts_incidentid FOREIGN KEY (IncidentId) REFERENCES mattermost.IR_Incident(Id); $$,
      $$ ALTER TABLE mattermost.IR_TimelineEvent ADD CONSTRAINT ir_timelineevent_incidentid FOREIGN KEY (IncidentId) REFERENCES mattermost.IR_Incident(Id); $$,
      $$ CREATE UNIQUE INDEX IF NOT EXISTS ir_playbookmember_playbookid_memberid_key on mattermost.IR_PlaybookMember(PlaybookId,MemberId); $$,
      $$ CREATE INDEX IF NOT EXISTS ir_statusposts_incidentid_postid_key on mattermost.IR_StatusPosts(IncidentId,PostId); $$,
      $$ CREATE INDEX IF NOT EXISTS ir_playbookmember_playbookid on mattermost.IR_PlaybookMember(PlaybookId); $$,
      $$ ALTER SCHEMA mattermost RENAME TO public; $$,
      $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,
      $$ ALTER USER postgres SET SEARCH_PATH TO 'public'; $$;
  ```
- pgloaderの実行
  ```
  # pgloader playbooks.load | tee playbooks_migration.log
  ```
  `ERROR PostgreSQL Database error 42P07: relation "ir_playbookmember_playbookid" already exists
  QUERY: CREATE INDEX IR_PlaybookMember_PlaybookID ON mattermost.ir_playbook (id);`
  というエラーが発生するが、どうもpgloaderの設定ファイルでインデックス作成を2回試みているからと思われる。
  ```
  $$ CREATE UNIQUE INDEX IF NOT EXISTS ir_playbookmember_playbookid_memberid_key on mattermost.IR_PlaybookMember(PlaybookId,MemberId); $$,
  $$ CREATE INDEX IF NOT EXISTS ir_playbookmember_playbookid on mattermost.IR_PlaybookMember(PlaybookId); $$,
  ```
  どうするべきか判断ができないので、一旦このまま継続してみる。
</details>

### Boardsの移行
- focalboard_blocks_history テーブルの fields カラムに空の文字列が存在するか確認し、フィールドに`{}`をセット
  ```
  # mysql -u root -p -h localhost mattermost
  mysql> SELECT * FROM focalboard_blocks_history WHERE fields = "";
  mysql> UPDATE focalboard_blocks_history SET fields = '{}' WHERE fields = "";
  mysql> quit
  ```
- 設定ファイルの作成
  PostgreSQLはJSONのバリデーションが厳密らしい。
  PostgreSQLではバッククォート（`）は認識されないので、すべてのバッククォートをダブルクォート（"）に変更
  JSONのカラムに関する比較でエラーが発生する部分について、キャストを追加
  ```
  # vim focalboard.load
  LOAD DATABASE
       FROM      mysql://mmuser:********@localhost:3306/mattermost
       INTO      pgsql://postgres@localhost:5432/mattermost

  WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000,
      preserve index names

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB'

  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'

  CAST column focalboard_blocks.fields to "json" drop typemod,
       column focalboard_blocks_history.fields to "json" drop typemod,
       column focalboard_schema_migrations.name to "varchar" drop typemod,
       column focalboard_sessions.props to "json" drop typemod,
       column focalboard_teams.settings to "json" drop typemod,
       column focalboard_users.props to "json" drop typemod,
       type int when (= precision 11) to int4 drop typemod,
       type json to jsonb drop typemod

  INCLUDING ONLY TABLE NAMES MATCHING
      ~/focalboard/

  BEFORE LOAD DO
      $$ ALTER SCHEMA public RENAME TO mattermost; $$

  AFTER LOAD DO
      $$ UPDATE mattermost.focalboard_blocks SET `fields` = "{}" WHERE `fields` = ""; $$,
      $$ UPDATE mattermost.focalboard_blocks_history SET `fields` = "{}" WHERE `fields` = ""; $$,
      $$ UPDATE mattermost.focalboard_sessions SET `props` = "{}" WHERE `fields` = ""; $$,
      $$ UPDATE mattermost.focalboard_teams SET `settings` = "{}" WHERE `fields` = ""; $$,
      $$ UPDATE mattermost.focalboard_users SET `props` = "{}" WHERE `fields` = ""; $$,
      $$ ALTER SCHEMA mattermost RENAME TO public; $$,
      $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,
      $$ ALTER USER postgres SET SEARCH_PATH TO 'public'; $$;
  ```
- pgloaderの実行
  ```
  # pgloader focalboard.load | tee focalboard_migration.log
  ```

### データを比較

```
dbcmp --source "mmuser:********@tcp(localhost:3306)/mattermost" --target "postgres://postgres@local
host:5432/mattermost?sslmode=disable" --exclude="db_migrations,systems"
```
※DSN（Data Source Name）の指定の仕方が分かりにくいし、アットマークがパスワードに入っている時にどうしてもエスケープできずエラーになる。（%40、@@、\\@、を試したけどだめ）

うまく比較できたタイミングもあったが、上記の手順でやり直したところ以下のエラーが出るようになった。
`error during comparison: could not compute src checksum: could not select checksum: sql: Scan error on column index 0, name "a": converting NULL to uint is unsupported`
dbcmpコマンドの問題と見て、このまま継続してみる。

本番移行時には以下のエラーとなった。（とりあえずそのまま進めた。）
```
Error: error during comparison: could not compute src checksum: could not select checksum: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ' '),
coalesce(md5(title), ' '),
coalesce(md5(type), ' '),
coalesce(md5(updat' at line 20
Usage:
  dbcmp [flags]

Flags:
      --exclude strings   exclude tables from comparison, takes comma-separated values.
  -h, --help              help for dbcmp
      --page-size int     page size for each checksum comparison. (default 1000)
      --source string     source database dsn
      --target string     target database dsn
  -v, --version           version for dbcmp

error during comparison: could not compute src checksum: could not select checksum: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ' '),
coalesce(md5(title), ' '),
coalesce(md5(type), ' '),
coalesce(md5(updat' at line 20
```

### Mattermostの参照先DB設定を変更
- Mattermostの設定ファイルを修正
  ```
  # vim /opt/mattermost/config/config.json
  ```
  ```
      "SqlSettings": {
          "DriverName": "mysql",
          "DataSource": "mmuser:********@tcp(localhost:3306)/mattermost?charset=utf8mb4,utf8",
  ```
  を
  ```    "SqlSettings": {
          "DriverName": "postgres",
          "DataSource": "postgres://mmuser:********@localhost:5432/mattermost?sslmode=disable",

  ```
  に変更。

- `"level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:170","error":"driver: postgres, message: failed to fetch current schema, command: current_schema, originalError: sql: Scan error on column index 0, name \"current_schema\": converting NULL to string is unsupported, query: \n\nSELECT CURRENT_SCHEMA()\n"`というエーが発生し、Mattermostが起動できない
  - mmuserがアクセスするデフォルトのスキーマを設定
    ```
    # psql -U postgres -d mattermost
    SHOW search_path;
    ALTER USER mmuser SET search_path TO public;
    \dn
    ```

- このまま起動すると権限エラーがでるので、DBユーザに権限追加
  mattermostのconfig.jsonをPostgreSQLに向けて起動しようとしても、権限エラーが出る。
  mmuserに権限追加。
  ```
  # psql -U postgres -h localhost -d mattermost
  mattermost=# GRANT ALL PRIVILEGES ON SCHEMA public TO mmuser;
  GRANT
  mattermost=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mmuser;
  GRANT
  mattermost=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mmuser;
  GRANT
  mattermost=# \q
  ```
- `systemctl start mattermost.service`で起動する。

### 動作確認
```
# systemctl status mattermost.service 
● mattermost.service - Mattermost
   Loaded: loaded (/etc/systemd/system/mattermost.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 14:46:01 JST; 7min ago
 Main PID: 30030 (mattermost)
   CGroup: /system.slice/mattermost.service
           ├─30030 /opt/mattermost/bin/mattermost
           ├─30059 plugins/com.github.matterpoll.matterpoll/server/dist/plugin-linux-amd64
           ├─30067 plugins/com.github.scottleedavis.mattermost-plugin-remind/server/dist/plugin-linux-amd64
           ├─30078 plugins/com.mattermost.moodle-notification/server/dist/plugin-linux-amd64
           ├─30088 plugins/com.mattermost.plugin-channel-export/server/dist/plugin-linux-amd64
           ├─30096 plugins/com.mattermost.nps/server/dist/plugin-linux-amd64
           ├─30104 plugins/com.mattermost.apps/server/dist/plugin-linux-amd64
           ├─30119 plugins/playbooks/server/dist/plugin-linux-amd64
           └─30130 plugins/focalboard/server/dist/plugin-linux-amd64
```
実際にブラウザやアプリから問題なくアクセスできるか確認。


## v7.8.0からv8.1.2へのアップグレード
postsテーブルにインデックスを作成しようとする際に、"must be owner of table posts" というエラーに対応するために、mattermostデータベースとその中にあるテーブル全ての所有者をmmuserに変更した。
- PostgreSQLのmattermostデータベースの所有者をmmuserに変更
  ```
  mattermost=# \l
                                                    List of databases
      Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
  ------------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
   mattermost | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres         +
  mattermost=# ALTER DATABASE mattermost OWNER TO mmuser;
  ALTER DATABASE
  mattermost=# \l
                                                    List of databases
      Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
  ------------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
   mattermost | mmuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/mmuser           +
  ```
- mattermostデータベース内にある全てのテーブルの所有者をmmuserに変更
  ```
  # psql -U postgres -h localhost -d mattermost
  ```
  ```
  DO $$ 
  DECLARE 
      table_name text;
  BEGIN 
      FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') 
      LOOP
          EXECUTE 'ALTER TABLE ' || table_name || ' OWNER TO mmuser;';
      END LOOP; 
  END $$;
  ```
- いつもの手順でmattermostをアップグレード
  ```
  # mkdir mm812
  # cd mm812/
  # wget https://releases.mattermost.com/8.1.2/mattermost-8.1.2-linux-amd64.tar.gz
  # tar zxf mattermost-8.1.2-linux-amd64.tar.gz
  # chown -R mattermost:mattermost mattermost 
  # chmod -R g+w mattermost
  # mv mattermost/config/config.json mattermost/config/config.json.org
  # cp -p /opt/mattermost/config/config.json mattermost/config/
  # systemctl stop mattermost.service
  # mv /opt/mattermost/data mattermost/
  # mv /opt/mattermost /opt/mattermost_v7.8.0
  # mv mattermost /opt/
  # ln -s /opt/mattermost/client /opt/mattermost/bin/client
  # mkdir /opt/mattermost/client/plugins
  # chown mattermost.mattermost /opt/mattermost/client/plugins/
  # systemctl start mattermost.service
  ```
### 動作確認
```
# systemctl status mattermost.service 
● mattermost.service - Mattermost
   Loaded: loaded (/etc/systemd/system/mattermost.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 14:46:01 JST; 7min ago
 Main PID: 30030 (mattermost)
   CGroup: /system.slice/mattermost.service
           ├─30030 /opt/mattermost/bin/mattermost
           ├─30059 plugins/com.github.matterpoll.matterpoll/server/dist/plugin-linux-amd64
           ├─30067 plugins/com.github.scottleedavis.mattermost-plugin-remind/server/dist/plugin-linux-amd64
           ├─30078 plugins/com.mattermost.moodle-notification/server/dist/plugin-linux-amd64
           ├─30088 plugins/com.mattermost.plugin-channel-export/server/dist/plugin-linux-amd64
           ├─30096 plugins/com.mattermost.nps/server/dist/plugin-linux-amd64
           ├─30104 plugins/com.mattermost.apps/server/dist/plugin-linux-amd64
           ├─30119 plugins/playbooks/server/dist/plugin-linux-amd64
           └─30130 plugins/focalboard/server/dist/plugin-linux-amd64
```
実際にブラウザやアプリから問題なくアクセスできるか確認。

たぶん大丈夫そう。。