LoginSignup
5
5

More than 1 year has passed since last update.

@honma-h(本間 豪紀)in株式会社クラスアクト

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

Posted at

なぜやるのか

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

※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",

    を

    {
            "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

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

たぶん大丈夫そう。。

5
5
2

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
5
5