More than 1 year has passed since last update.

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

Posted at 2023-09-18

なぜやるのか

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への移行：本家が作ってくれた手順に沿ってやってみた

評価用サーバの用意

Proxmox上で動いているので既存サーバをクローンで複製
NICを無効にした状態で起動し、IPアドレスが重複しないように変更しシャットダウン
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

# 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

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); $$,
```
どうするべきか判断ができないので、一旦このまま継続してみる。

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"

うまく比較できたタイミングもあったが、上記の手順でやり直したところ以下のエラーが出るようになった。
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",

を

{

        "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

実際にブラウザやアプリから問題なくアクセスできるか確認。

たぶん大丈夫そう。。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up