Migrating from MariaDB to PostgreSQL DB

I just ran into the problem that recent Mattermost versions do not support MariaDB any more, and actually Focalboard / MatterMost boards in the latest version refuses to migrate the DB schema in MariaDB and therefore refuses to run.

So I can now migrate to MySQL or PostgreSQL on my Debian Bullseye machine.

As PostgreSQL is officially shipped with Debian, I’d prefer to switch to this. It’s probably not sufficient and no good idea to somehow migrate the MariaDB database into PostgreSQL format using some external tool, as there’s no guarantee that the result will actually match Mattermost’s PostgreSQL DB schema.

So should I perform a bulk export with the old DB, reinitialize with PostgreSQL and then bulk load the previously exported data again?

Or is there another (maybe simpler) supported way?

Yes, that’s the safest approach. The only issue is that plugins and certs won’t be re-imported and you would have to install them again.

Hi Gunter,

this approach might also be interesting for you:

1 Like

Thanks for the hint, the pg_loader approach looks interesting.

I think I’ll try that first, and resort to the bulk export/import strategy should this fail.

Fortunately, we’re not actively using Boards and Playbooks so far, so for these plugins there’s no need for data to be migrated. However we have one or two incoming web hooks and things like that, which will probably be migrated more completely using the pg_loader approach.

Hi Gunter,
we migrated our MariaDB to Postgres three months ago. Our environment is redHat Linux version 8 and the migration was done with Mattermost version 5.35. The migration was done with pgloader. I have documented the steps below. We had two machines in the document lnxold = MariaDB and lnxnew=postgres. Maybe this document will give you an idea for the migration.

Mattermost Migration

Database

  1. Get pgloader RPM from PostgreSQL YUM repository:

https://ftp.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64/pgloader-3.6.2-1.rhel7.x86_64.rpm

  1. Install pgloader on the old Mattermost server (lnxold.xxx.xxx.us) as root user.
asroot yum install /tmp/pgloader-3.6.2-1.rhel7.x86_64.rpm
  1. Stop Mattermost on old (lnxold.xxx.xxx.us) and new server (lnxnew.xxx.xxx.us).
systemctl stop mattermost
  1. Change mmuser to SUPERUSER on PostgreSQL database. Become to the postgres user
    before connecting to the mattermost database:
# become postgres user
asroot su - postgres
# connect to mattermost database
psql mattermost
ALTER USER mmuser WITH SUPERUSER;
  1. Create pgloader command file with the following properties, ensure correct usernames
    and passwords.
LOAD DATABASE
     FROM mysql://mmuser:<password>@localhost/mattermost
     INTO postgres://mmuser:<password>@lnxnew.xxx.xxx.xx/mattermost

WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, no foreign keys, data only


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

ALTER SCHEMA 'mattermost' RENAME TO 'public'

;
  1. Drop idx_posts_message_txt index on PostgreSQL database (lnxnew.xxx.xxx.us):
DROP INDEX idx_posts_message_txt;
  1. Start pgloader on old Mattermost server (lnxold.xxxx.xxx.us) with the commands file from
    above to load data from MySQL to PostgreSQL:
pgloader --verbose commands.load
                     table name     errors       read   imported      bytes      total time       read      write
-------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                fetch meta data          0         58         58                     0.060s
                       Truncate          0         58         58                     0.628s
-------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                    public.bots          0         10         10     1.2 kB          0.201s     0.040s
                  public.audits          0      30378      30378     3.9 MB          0.713s     0.522s     0.513s
          public.channelmembers          0       6953       6953     1.4 MB          0.584s     0.190s     0.149s
        public.clusterdiscovery          0          0          0                     0.537s     0.024s
         public.commandwebhooks          0          0          0                     0.673s     0.065s
                   public.emoji          0        219        219    20.2 kB          0.904s     0.024s     0.001s
           public.groupchannels          0          0          0                     1.037s     0.021s
              public.groupteams          0          0          0                     1.144s     0.035s
             public.ir_incident          0          0          0                     1.331s     0.023s
       public.ir_playbookmember          0          0          0                     1.492s     0.040s
               public.ir_system          0          1          1     0.0 kB          1.678s     0.055s
                    public.jobs          0      10298      10298     2.3 MB          2.153s     0.205s     0.215s
    public.channelmemberhistory          0       7561       7561   528.2 kB          0.448s     0.113s     0.077s
                public.channels          0       2041       2041   319.4 kB          0.473s     0.058s     0.019s
                public.commands          0          4          4     1.6 kB          0.558s     0.027s
            public.linkmetadata          0       2805       2805   312.6 kB          1.636s     0.021s     0.028s
             public.compliances          0          0          0                     0.702s     0.015s
               public.oauthapps          0          0          0                     1.714s     0.031s
                public.fileinfo          0       5699       5699     7.9 MB          1.620s     0.341s     0.711s
        public.outgoingwebhooks          0          0          0                     1.903s     0.020s
                   public.posts          0     246046     246046    73.8 MB         11.832s     7.821s     9.458s
            public.groupmembers          0          0          0                     1.551s     0.002s
        public.incomingwebhooks          0         18         18     3.7 kB          1.802s     0.029s
             public.ir_playbook          0          0          0                     1.965s     0.104s
          public.ir_statusposts          0          0          0                     1.882s     0.001s
        public.ir_timelineevent          0          0          0                     2.060s     0.008s
                public.licenses          0          0          0                     2.270s     0.001s
         public.oauthaccessdata          0          0          0                     2.521s     0.015s
           public.oauthauthdata          0          0          0                     2.981s     0.023s
     public.pluginkeyvaluestore          0         90         90     1.0 MB          3.184s     0.018s     0.047s
             public.preferences          0      12870      12870     1.1 MB          3.635s     0.117s     0.196s
          public.publicchannels          0        274        274    31.5 kB          3.888s     0.016s     0.002s
          public.remoteclusters          0          0          0                     4.222s     0.019s
       public.schema_migrations          0          1          1     0.0 kB          4.874s     0.042s
                public.sessions          0       1263       1263   390.4 kB          5.327s     0.014s     0.031s
    public.sharedchannelremotes          0          0          0                     5.476s     0.037s
      public.sharedchannelusers          0          0          0                     5.749s     0.046s
         public.sidebarchannels          0       1487       1487   174.1 kB          6.086s     0.039s     0.032s
                 public.systems          0         39         39     1.7 kB          6.352s     0.049s
                   public.teams          0         51         51     7.1 kB          6.524s     0.031s
       public.threadmemberships          0       7133       7133   598.7 kB          7.052s     0.075s     0.212s
                  public.tokens          0          0          0                     7.213s     0.116s
        public.useraccesstokens          0          1          1     0.1 kB          7.157s     0.049s
                   public.users          0        586        586   312.3 kB          7.311s     0.013s     0.014s
  public.productnoticeviewstate          0          0          0                     0.094s     0.014s
               public.reactions          0       8802       8802   762.2 kB          0.444s     0.058s     0.056s
                   public.roles          0         17         17    18.4 kB          0.555s     0.002s     0.001s
                 public.schemes          0          0          0                     0.516s     0.001s
public.sharedchannelattachments          0          0          0                     0.683s     0.001s
          public.sharedchannels          0          0          0                     0.734s     0.001s
       public.sidebarcategories          0       2157       2157   322.2 kB          0.764s     0.016s     0.024s
                  public.status          0        570        570    28.4 kB          0.892s     0.013s     0.002s
             public.teammembers          0        897        897    56.5 kB          0.962s     0.005s     0.003s
          public.termsofservice          0          0          0                     0.976s     0.019s
                 public.threads          0       3515       3515   447.7 kB          1.107s     0.025s     0.054s
          public.uploadsessions          0          0          0                     1.101s     0.002s
              public.usergroups          0          0          0                     1.152s     0.001s
      public.usertermsofservice          0          0          0                     1.322s     0.001s
-------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
        COPY Threads Completion          0          4          4                    12.635s
                Reset Sequences          0          0          0                     0.029s
               Install Comments          0          0          0                     0.000s
-------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
              Total import time          ✓     351786     351786    95.6 MB         12.664s
  1. Check Output of the execution of pgloader on old mattermost server (lnxold.xxx.xxx.us)
    in the corresponding log file.
    g
less /tmp/pgloader/pgloader.log
  1. After successful load with pgloader, recreate dropped index idx_posts_message_txt on
    PostgreSQL database (lnxold.xxx.xxx.us):
CREATE INDEX idx_posts_message_txt ON public.posts USING gin (to_tsvector('english'::regconfig, (message)::text));

WARNING: Some records in the posts table migth result in the following error messages, which indicate that the
message in a post can not be indexed. They can be ignored, in a test run there where 8 words longer than 2047 characters.

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
  1. Change mmuser on PostgreSQL to NOSUPERUSER.
ALTER USER mmuser WITH NOSUPERUSER;

Files

  1. Copy data directory from old mattermost server (lnxold.xxx.xxx.us) to new server
    (lnxnew.xxx.xxx.us) via scp or tar-ball.
scp -r lnxold.xxx.xxx.us:/usr/app/mattermost/data /opt/mattermost/.
1 Like

@Roger, @agriesser: Thanks a lot for your help and your valuable input.

I just performed the migration and Mattermost still seems to work! :+1:

@Roger: One question which does not become clear from your message (or I’m missing it constantly):

You also installed the exactly same MM version on both machines and let the new instance initialize the PostgreSQL DB schema - right?

I had a little trouble after running pgloader - Mattermost insisted on re-running the last DM migration 92 “add_createat_to_teamembers”, or at least it wanted to insert this value again into the db_migrations table even though it already was there.

This caused an SQL exception (duplicate index values in unique index) and the start to fail.

It worked after I manually deleted the offending existing entry (DELETE FROM db_migrations WHERE version=92;), even though I’m not happy with such manual DB interventions and I don’t understand why Mattermost insisted on re-running the migration even though it was already entered in this table.

I can only assume it happened because I had already run into MariaDB incompatibilities with Mattermost 7.3, and e.g. the Focalboard plugin could not execute it’s DB migration, causing the plugin to be forced disabled. Maybe due to this, the last migration somehow was not logged / marked as “completed successfully” - but that’s pure speculation on my part now.

Nevertheless, after removing the offending entry, the server logs didn’t seem to show any other unexpected error messages, the server came up, Teams, Users, Channels and messages are there, embedded images show - so looks fine for now, as far as I can tell…

I think it’s necessary to do that, otherwise upon the first start of the new instance it will create table definitions that are not available on the old system. From what I understood, though, pg_loader takes care of the table creation, so you do not need to start the aplication with the PostgreSQL DSN first for the tables to be created, but I’ve not done that as of yet, so just guessing.

DB migration 92 searches for an index which it probably could not find in your installation. As far as I know, pg_loader does not keep the index names?

This is unrelated, the focalboard plugin has its own schema migration table and is not related to the server db schema.

@GOhrner ,
correct, I’ve installed the exactly same MM version on both linux instances and the new instance was PostgreSQL preinstalled.

My strong assumption would be that such an auto-generated schema will not perfectly match the actual PostgreSQL schema of MatterMost - there’s not always a unique data type mapping, and some concepts like Sequences may work differently between these DBMSes, so pgloader cannot really achieve the “proper” mapping.

I’m actually even surprised that it works by letting MM create the schema and then manually moving the data using pgloader - this indicates that both schemas are really extremely similar. I would have expected differences which would make such a migration impossible, but it seems to work.

Thanks for the pointer to the migration script, I’ll cross check this table to see what the current state is on my DB.

The indexes should also already have been created by PostgreSQL before moving the data, so I would have expected the name to match or rather, that MM would not try to re-run the migration in the first place. Apparently it didn’t with all previous migrations.

Thanks for the confirmation! That’s also what I did, except I could not for the Focalboard plugin, because its DM schema seems to have been messed by due to the incompatibility with MariaDB. We didn’t use any boards previously, so the easiest solution for me was to just drop all Focalboard tables in the source DB and not to migrate them, instead of trying to clean up / repair the schema.

In general, all seems to have worked quite well, so far I didn’t get any report from my users that something would not work.

Thanks again to all of you for your valuable information and pointers, and also for figuring out how to do this before I had to. :wink:

(Unfortunately I can only flag one post as “Solution”. I did so with the first which pointed to the GitHub discussion where the pgloader migration script was included and discussed. The latter posting from Roger mainly confirmed this approach.)

Just to add info about the first trouble (challenges?) I faced with my migrated DB:

When trying to upgrade my Mattermost 7.3.0 to 7.4.0 today Mattermost Boards refused to start, its DB migration failed.

The reason was the props attribute (normally JSON content) in the Users table, where (probably during the migration) some “null” Strings crept in (so no NULL SQL values, a literal String saying “null”).

This was easy to fix though:

UPDATE Users SET props = '{}' WHERE props = 'null';

Mattermost Boards / Focalboard startet fine afterwards.

I just hope there are not much more quirks like this hidden in the migrated data… :-/ That’s something I feared could happen with a migration approach like pg_loader. (If I didn’t just make a stupid mistake during migration, not sure…)

To help people googling for it, the error message I got was:

cannot activate plugin: error initializing the DB: driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: kann nicht aus skalarem Wert löschen, query: \n\n\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'welcomePageViewed', replace((Props->'focalboard_welcomePageViewed')::varchar, '\"', '') FROM Users WHERE Props->'focalboard_welcomePageViewed' IS NOT NULL ON CONFLICT DO NOTHING;\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'hiddenBoardIDs', replace(replace(replace((Props->'hiddenBoardIDs')::varchar, '\"[', '['), ']\"', ']'), '\\\"', '\"') FROM Users WHERE Props->'hiddenBoardIDs' IS NOT NULL ON CONFLICT DO NOTHING;\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'tourCategory', replace((Props->'focalboard_tourCategory')::varchar, '\"', '') FROM Users WHERE Props->'focalboard_tourCategory' IS NOT NULL ON CONFLICT DO NOTHING;\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'onboardingTourStep', replace((Props->'focalboard_onboardingTourStep')::varchar, '\"', '') FROM Users WHERE Props->'focalboard_onboardingTourStep' IS NOT NULL ON CONFLICT DO NOTHING;\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'onboardingTourStarted', replace((Props->'focalboard_onboardingTourStarted')::varchar, '\"', '') FROM Users WHERE Props->'focalboard_onboardingTourStarted' IS NOT NULL ON CONFLICT DO NOTHING;\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'version72MessageCanceled', replace((Props->'focalboard_version72MessageCanceled')::varchar, '\"', '') FROM Users WHERE Props->'focalboard_version72MessageCanceled' IS NOT NULL ON CONFLICT DO NOTHING;\n        INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'lastWelcomeVersion', replace((Props->'focalboard_lastWelcomeVersion')::varchar, '\"', '') FROM Users WHERE Props->'focalboard_lastWelcomeVersion' IS NOT NULL ON CONFLICT DO NOTHING;\n\n        UPDATE Users SET props = (props - 'focalboard_welcomePageViewed' - 'hiddenBoardIDs' - 'focalboard_tourCategory' - 'focalboard_onboardingTourStep' - 'focalboard_onboardingTourStarted' - 'focalboard_version72MessageCanceled' - 'focalboard_lastWelcomeVersion');\n    \n\n    \n\n    \n\n\n

We encountered the same error when updating the Omnibus package to 7.4 which had always been on Postgres, though Googling didn’t really help find this page at first as your original error was translated:

The full error we got was as follows:

cannot activate plugin: error initializing the DB: driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: cannot delete from scalar, query:

    INSERT INTO Preferences (UserId, Category, Name, Value) SELECT Id, 'focalboard', 'welcomePageViewed', replace((Props->'focalboard_welcomePageViewed')::varchar, '\"', '') FROM Users WHERE Props

The same query to replace null with an empty object, however, worked fine as well:

mattermost=# UPDATE Users SET props = '{}' WHERE props = 'null';
UPDATE 1
mattermost=# UPDATE Users SET props = '{}' WHERE props = 'null';
UPDATE 0

Mh, if this is the case this might indicate a bug in how either Mattermost or Focalboard stores or stored its settings, or a bug in an earlier DM migration script…

Might be an idea to report this as a new bug against Focalboard / Mattermost boards then?

Focalboard / MatterMost boards in the latest version refuses to migrate the DB schema in MariaDB and therefore refuses to run.

This feels like quite a big deal, at least to us. I know MariaDb isn’t “supported” but it’s used widely in the field. Is this an official departure and changes Maria from “unsupported” to “will not work with” for all users?