Transition to PostgreSQL

[ericgaspar]

Merge from #373 #374 #375 #376

[ericgaspar]

!testme

[biva]

Hello @ericgaspar, I copy here my message from https://github.com/YunoHost-Apps/mattermost_ynh/pull/376 because I guess it makes more sense to continue the discussion here.

Thank you so much for your support! I'm really not skilled to understand how branches and PR work exactly, so I'm sorry if I do something wrong.

I understand that you checked my commits and you think they are done correctly? If yes, I'd be so proud of my first commit ever 😄

I don't see anything to migrate from MySQL to PostgreSQL.

Indeed, I started to work on the documentation (https://github.com/mattermost/docs/pull/5729) but I'm not sure at all that I did it correctly. Would you be able to help?

[biva]

By curiosity, I see that the testing queue is 2 days long in https://ci-apps-dev.yunohost.org/ci/: is it usual for Yunohost?

[biva]

Hello @ericgaspar @kemenaran , Nobody's making any comment nor advice in the process to migrate from MySQL/MariaDB in https://github.com/mattermost/docs/pull/5729

I'm really not sure about this process and I don't have any test server. Would you be able to have a look? It could also help here on Yunohost to migrate existing installs, couldn't it?

[ericgaspar]

I have created a special branch with the migration process (which is not working yet...) https://github.com/YunoHost-Apps/mattermost_ynh/tree/migration

This is the section when the migration is done: https://github.com/YunoHost-Apps/mattermost_ynh/blob/87d508631fba2b540d350afc5552c807634bf7dd/scripts/upgrade#L174-L193

[biva]

Wow, great, thank you very much. Let me keep an eye on it :)

[kemenaran]

@ericgaspar thanks a lot for giving a shot at this (I don't have the bandwidth for this for now) ❤️

[ericgaspar]

!testme

[ericgaspar]

!testme

[ericgaspar]

!testme

[biva]

Hello @ericgaspar , I see that you worked a lot on this: are you blocked by something? Is there something I can do to help?

[biva]

Hello, thank you again for your work here! Is there anything we can do to finalise it? I feel the success it not far at all, isn't it?

[ericgaspar]

!testme

[yunohost-bot]

:carousel_horse:

[ericgaspar]

!testme

[yunohost-bot]

May the CI gods be with you!

[zamentur]

Here better steps to migrate:

source /usr/share/yunohost/helpers
systemctl stop mattermost
app=mattermost
db_user=$app
db_name=$app
ynh_psql_setup_db --db_user=$db_user --db_name=$db_name
vi config/config.json
sudo -u mattermost ./bin/mattermost
sudo -u postgres psql -c 'DROP INDEX public.idx_fileinfo_content_txt;' mattermost
sudo -u postgres psql -c 'DROP INDEX public.idx_posts_message_txt;' mattermost

mysql -e "ALTER TABLE mattermost.Users DROP COLUMN acceptedtermsofserviceid;" mattermost
apt install pgloader
cat <<EOT > /tmp/commands.load
LOAD DATABASE
     FROM mysql://mmuser:XXXXX@127.0.0.1:3306/mattermost
     INTO postgresql://mattermost:XXXXXX@127.0.0.1:5432/mattermost

WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, preserve index names, no foreign keys,
     data only, workers = 16, concurrency = 1

SET MySQL PARAMETERS
net_read_timeout = '90',
net_write_timeout = '180'

ALTER SCHEMA 'mattermost' RENAME TO 'public'

;
EOT
pgloader /tmp/commands.load
sudo -u postgres psql -c 'CREATE INDEX idx_fileinfo_content_txt ON public.fileinfo USING gin (to_tsvector('\''english'\''::regconfig, content));' mattermost
sudo -u postgres psql -c 'CREATE INDEX idx_posts_message_txt ON public.posts USING gin (to_tsvector('\''english'\''::regconfig, (message)::text));' mattermost
systemctl start mattermost

Note: if there are some plugins activated it's could not be enough...

[zamentur]

This version is able to upgrade mattermost (if no other plugins have been added manually). However, to do it i have made some fixes on ynh_write_var_in_file helper, so we need yunohost 11.1.5.5+ (current unstable branch)

[ericgaspar]

!testme

[ericgaspar]

!testme

[yunohost-bot]

:stuck_out_tongue_winking_eye:

[yunohost-bot]

:stuck_out_tongue_winking_eye:

[ericgaspar]

@zamentur Thanks for your help. I have tested the upgraded successfully!

[kemenaran]

Yay, thanks for the works! I'm trying to review this (still dealing with a newborn at home, but I hope I can get through this in a handful of days).

A quick question though: what happens when the Mattermost installation has custom plugins?

[zamentur]

A quick question though: what happens when the Mattermost installation has custom plugins?

It's possible that it works BUT with some plugins (and depending from which version we are upgrading) it's also possible that pgloader fail or sthe start of the service, in this case the app will be restored. If the instance report the log with yunopaste, we should be able to add some SQL fixes for those plugins...

[zamentur]

!testme

[yunohost-bot]

:sunflower:

[yunohost-bot]

:v:

[zamentur]

!gogogadgetoci

[yunohost-bot]

:sunflower:

[kemenaran]

Seems the CI runner is still broken ; I have no idea how to repair it.

[kemenaran]

The upgrade fails on my Mattermost setup, with this error:

2023-02-11 14:29:17,514: DEBUG - 2023-02-11T13:29:17.055000Z LOG pgloader version "3.6.2"
2023-02-11 14:29:17,897: DEBUG - 2023-02-11T13:29:17.452000Z LOG Migrating from #<MYSQL-CONNECTION mysql://mmuser@127.0.0.1:3306/mattermost {1006018203}>
2023-02-11 14:29:17,899: DEBUG - 2023-02-11T13:29:17.454000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mattermost@127.0.0.1:5432/mattermost {1006018FE3}>
2023-02-11 14:29:20,075: DEBUG - 2023-02-11T13:29:19.629000Z WARNING Source column "public"."Bots"."description" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."bots"."description".
2023-02-11 14:29:20,077: DEBUG - 2023-02-11T13:29:19.630000Z WARNING Source column "public"."ChannelMembers"."roles" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."channelmembers"."roles".
2023-02-11 14:29:20,078: DEBUG - 2023-02-11T13:29:19.630000Z WARNING Source column "public"."ChannelMembers"."notifyprops" is casted to type "text" which is not the same as "jsonb", the type of current target database column "public"."channelmembers"."notifyprops".
2023-02-11 14:29:20,096: WARNING - KABOOM!
2023-02-11 14:29:20,098: WARNING - FATAL error: pgloader failed to find column "public"."focalboard_blocks"."workspace_id" in target table "\"public\".\"focalboard_blocks\""
2023-02-11 14:29:20,100: WARNING - An unhandled error condition has been signalled:
2023-02-11 14:29:20,101: WARNING -    pgloader failed to find column "public"."focalboard_blocks"."workspace_id" in target table "\"public\".\"focalboard_blocks\""

[zamentur]

From which version are you upgrading ?

[kemenaran]

Erf, just checked, and it is 7.3.0 (instead of the currently pusblished 7.2).

This is because this package was published with the version 7.3.0 for a few days, before being reverted (it broke the boards on MariaDB).

So indeed I have an unusual configuration (upgrading from 7.3.0) ; other users probably have it too.

I’m not sure how to handle this…

[kemenaran]

If fixing the schema is difficult, maybe the migration script could just ignore the Boards table when upgrading from 7.3.0 : the board data would be lost, but the migration to 7.3.0 already broke the boards, so…

[ericgaspar]

!testme

[yunohost-bot]

:sunflower:

[kemenaran]

!testme

[yunohost-bot]

:stuck_out_tongue_winking_eye:

[kay0u]

Can we merge this ?

[kemenaran]

I'd say we're good - except for maybe #410 (merged), which should help in some cases.

---

Also, on my instance I couldn't get the migration running with the Focalboard plugin activated: there's always an issue of mismatched schemas on the focalboard_* tables, because all migrations from the 7.2 version couldn't run (because MariaDB went unsupported).

So I think we should either:

• Explicitly exclude Focalboard tables from the PGLoader migration,
• Or, right before starting PGLoader, print a warning in the migration log like "If the migration fails, try deleting (not just removing) the Focalboard plugin, then start the upgrade again."

@kay0u @zamentur What do you think?