Closed DJIronic closed 1 month ago
agnivade
commented
2 months ago mmuser might be the owner of the DB, but it doesn't look like it's the owner of the schema.
To fix this:
Run: sudo -u postgres psql to get in the console.
And then: \c mattermost. Verify it worked by running SELECT current_database();.
Then run ALTER SCHEMA public OWNER TO mmuser;
and GRANT ALL ON SCHEMA public to mmuser;
This should fix it. Let me know if it doesn’t.
polygon0323
commented
2 months ago Same problem. apply the fix and continue, get the following error.
$ pgloader migration.load > migration.log
KABOOM!
FATAL error: Database error 42501: permission denied for database mattermost
QUERY: ALTER SCHEMA public RENAME TO mattermost;
An unhandled error condition has been signalled:
Database error 42501: permission denied for database mattermost
QUERY: ALTER SCHEMA public RENAME TO mattermost;
What I am doing here?
Database error 42501: permission denied for database mattermost
QUERY: ALTER SCHEMA public RENAME TO mattermost;Mattermost 9.10.1 Ubuntu 20.04.6 LTS MySQL 8.0.39-0ubuntu0.20.04.1 (x64) PostgreSQL 12+214ubuntu0.1
DJIronic
commented
2 months ago
mmusermight be the owner of the DB, but it doesn't look like it's the owner of the schema.To fix this:
Run:
sudo -u postgres psqlto get in the console.And then:
\c mattermost. Verify it worked by runningSELECT current_database();.Then run
ALTER SCHEMA public OWNER TO mmuser;andGRANT ALL ON SCHEMA public to mmuser;This should fix it. Let me know if it doesn’t.
Yes, this helped me.
DJIronic
commented
2 months ago Okay, I hit another issue with the migration
Importing the calls, playbooks and boards will fail:
simonc@mm:~$ pgloader boards.load > boards_migration.log
KABOOM!
FATAL error: Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';
An unhandled error condition has been signalled:
Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';
What I am doing here?
Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';As I do not have any playbooks or board ad calls history is not important, I tried to run MM even after those pgloader commands fail, but it was unable to start.
MM will not start with this error:
srp 14 07:41:11 mm mattermost[1008557]: {"timestamp":"2024-08-14 07:41:11.305 Z","level":"info","msg":"EXPERIMENTAL: Starting Bleve","caller":"bleveengine/bleve.go:186"}
srp 14 07:41:11 mm mattermost[1008557]: {"timestamp":"2024-08-14 07:41:11.309 Z","level":"info","msg":"Pinging SQL","caller":"sql/sql_utils.go:67","database":"master","dataSource":"postgres://%2A%2A%2A%2A:%2A%2A%2A%2A@localhost:5432/mat>srp 14 07:41:11 mm mattermost[1008557]: Error: failed to initialize platform: cannot create store: failed to apply database migrations: driver: postgres, message: failed to fetch current schema, command: current_schema, originalError: s>srp 14 07:41:11 mm mattermost[1008557]: SELECT CURRENT_SCHEMA()
srp 14 07:41:11 mm mattermost[1008557]: {"timestamp":"2024-08-14 07:41:11.315 Z","level":"error","msg":"failed to initialize platform: cannot create store: failed to apply database migrations: driver: postgres, message: failed to fetch >srp 14 07:41:11 mm mattermost[1008557]: Usage:@DJIronic - Your MM is not starting because your search_path somehow is set to empty.
I'd suggest to log in to the DB. psql -h localhost -U mmuser -d mattermost, again verify you are in the right database with select current_database();.
Now type select current_schema();. This should return nil. Then show search_path; should also be empty.
To fix this, run ALTER user mmuser in database mattermost set search_path to 'public';
And then logout, and login again. And run select current_schema(); to confirm that you see public in the output.
That should fix it.
agnivade
commented
2 months ago @polygon0323 - Have you ensured that mmuser is the owner of mattermost database? Run ALTER DATABASE mattermost OWNER TO mmuser; if that's not the case.
polygon0323
commented
2 months ago It seems that if an error occurs in pgloader, such as MySQL authentication failure, it will not work correctly.
If an error occurs during the process, I retry from the beginning, but the following error occurs.
Perhaps my environment is not correct and I will continue to try.
$ pgloader migration.load > migration.log
KABOOM!
UNDEFINED-TABLE: Database error 42P01: relation "mattermost.systems" does not exist
QUERY: TRUNCATE TABLE mattermost.systems;
An unhandled error condition has been signalled:
Database error 42P01: relation "mattermost.systems" does not exist
QUERY: TRUNCATE TABLE mattermost.systems;
What I am doing here?
Database error 42P01: relation "mattermost.systems" does not exist
QUERY: TRUNCATE TABLE mattermost.systems;(Sorry for the incorrect English due to translation)
polygon0323
commented
2 months ago I have successfully migrated! Thanks for the support.
What I did:
pgloader from source (3.6.1 → 3.6.29afa9d)DJIronic
commented
2 months ago @agnivade
I am afraid something is not as exptected. This is the output of those first two commands, they are not empty.
But I tried to ALTER user mmuser in database mattermost set search_path to 'public';
But it did not help.
agnivade
commented
2 months ago Something is not right with the search path. What happens if you do set search_path to 'public'; and then select current_schema(); in the same session? I think there's some other override, perhaps at the DB level which is messing this.
DJIronic
commented
2 months ago Here you go. I installed fresh postgress just for Mattermost on the VM.
here is the log:
PS: Ignore the error, that was a spelling mistake :)
agnivade
commented
2 months ago Interesting, and you have run
ALTER DATABASE mattermost OWNER TO mmuser;ALTER SCHEMA public OWNER TO mmuser; GRANT ALL ON SCHEMA public to mmuser; correct? Ensure that you run it from the mattermost database.
DJIronic
commented
2 months ago Yes I did everything thats in the "Prepare your database tutorial".
but now, the second command fails:
mattermost=> ALTER SCHEMA public OWNER TO mmuser;
ERROR: schema "public" does not existI re-created below and it worked.
DROP SCHEMA mattermost CASCADE;
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO mmuser;
GRANT ALL ON SCHEMA public to mmuser;I recreated the schema as suggested and imported all data again.
Importing the migration.load and calls.load worked. but playbooks and boards failed:
simonc@mm:~$ pgloader playbooks.load > playbooks_migration.log
KABOOM!
FATAL error: Database error 42P01: relation "mattermost.ir_channelaction" does not exist
QUERY: ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536);
An unhandled error condition has been signalled:
Database error 42P01: relation "mattermost.ir_channelaction" does not exist
QUERY: ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536);
What I am doing here?
Database error 42P01: relation "mattermost.ir_channelaction" does not exist
QUERY: ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536);If you don't have playbooks and boards installed, then you don't need to do those steps.
grrinch
commented
2 months ago Firstly, thanks for a lot of help to everyone in this thread! Many messages here were very helpful.
After a long while, I just had another attempt at this migration and failed miserably even despite putting up a fight.
✅ many issues with pgloader - overcame by compiling myself to the latest version. My situation was also weird because I had many special characters in the passwords so pgloader didn't want to cooperate too well. ✅ issues with schema on postgres - overcame, mostly by re-doing it many times, until it finally worked. ✅ old version of postgres (10) - overcame by doing distro upgrade from Ubuntu 18 -> 20 and postgres 10 -> 12.
A lot of stuff done, but the most important - the data migration - didn't go as planned.
I can share the whole log from pgloader, but here are just a few places where it failed - I have no idea if any of this is critical:
2024-08-27T21:02:56.451000Z WARNING Source column "mattermost"."Teams"."type" is casted to type "team_type" which is not the same as "mattermost.team_type", the type of current target database column "mattermost"."teams"."type".
2024-08-27T21:02:56.451000Z ERROR pgloader failed to find target table for source "mattermost"."TrueUpReviewHistory" with name "trueupreviewhistory" in target catalog
2024-08-27T21:02:56.451000Z WARNING Source column "mattermost"."UploadSessions"."type" is casted to type "upload_session_type" which is not the same as "mattermost.upload_session_type", the type of current target database column "mattermost"."uploadsessions"."type".
2024-08-27T21:02:56.455000Z LOG Skipping mattermost.trueupreviewhistory
...
2024-08-27T21:03:06.756000Z NOTICE COPY mattermost.sharedchannelremotes with 0 rows estimated [5/8]
2024-08-27T21:03:06.766000Z NOTICE COPY mattermost.uploadsessions with 0 rows estimated [5/8]
2024-08-27T21:03:07.048000Z ERROR Database error 23505: duplicate key value violates unique constraint "pluginkeyvaluestore_pkey"
DETAIL: Key (pluginid, pkey)=(com.github.matterpoll.matterpoll, mmi_botid) already exists.
CONTEXT: COPY pluginkeyvaluestore, line 16
2024-08-27T21:03:07.107000Z ERROR Database error 23505: duplicate key value violates unique constraint "roles_name_key"
DETAIL: Key (name)=(system_post_all) already exists.
CONTEXT: COPY roles, line 1
2024-08-27T21:04:05.881000Z NOTICE COPY mattermost.reactions with 31783 rows estimated [7/8]
...
2024-08-27T21:04:06.612000Z NOTICE COPY mattermost.oauthauthdata with 0 rows estimated [7/8]
2024-08-27T21:04:06.622000Z ERROR Database error 23505: duplicate key value violates unique constraint "users_username_key"
DETAIL: Key (username)=(calls) already exists.
CONTEXT: COPY users, line 1
2024-08-27T21:04:06.624000Z NOTICE COPY mattermost.postacknowledgements with 0 rows estimated [7/8]And here's the migration summary table:
2024-08-27T21:04:08.887000Z LOG report summary reset
table name errors read imported bytes total time read write
------------------------------------ --------- --------- --------- --------- -------------- --------- ---------
before load 0 4 4 0.015s
fetch meta data 0 67 67 0.141s
Drop Foreign Keys 0 0 0 0.000s
------------------------------------ --------- --------- --------- --------- -------------- --------- ---------
mattermost.posts 0 840884 840884 214.1 MB 1m12.335s 1m9.414s 1m3.196s
mattermost.linkmetadata 0 69823 69823 28.2 MB 12.496s 8.795s 7.610s
mattermost.jobs 0 36345 36345 3.6 MB 2.168s 2.028s 0.326s
mattermost.audits 0 33647 33647 4.5 MB 2.199s 1.682s 1.633s
mattermost.threads 0 11185 11185 1.3 MB 1.419s 0.629s 0.650s
mattermost.fileinfo 0 20404 20404 60.3 MB 12.102s 7.211s 11.045s
mattermost.postspriority 0 1415 1415 84.4 kB 0.830s 0.025s 0.005s
mattermost.sidebarcategories 0 273 273 40.8 kB 0.835s 0.031s 0.002s
mattermost.status 0 92 92 4.8 kB 1.009s 0.087s 0.001s
mattermost.systems 0 59 59 2.4 kB 1.010s 0.004s 0.001s
mattermost.bots 0 14 14 1.6 kB 1.039s 0.009s
mattermost.tokens 0 0 0 1.272s 0.016s
mattermost.desktoptokens 0 0 0 1.276s 0.042s
mattermost.incomingwebhooks 0 3 3 0.6 kB 1.268s 0.007s
mattermost.oauthapps 0 0 0 1.273s 0.005s
mattermost.persistentnotifications 0 0 0 1.312s 0.004s
mattermost.retentionidsfordeletion 0 0 0 1.387s 0.003s
mattermost.schemes 0 0 0 1.415s 0.006s
mattermost.sharedchannels 0 0 0 1.432s 0.008s
mattermost.usertermsofservice 0 0 0 1.453s 0.017s
mattermost.threadmemberships 0 23336 23336 1.9 MB 2.967s 1.283s 0.281s
mattermost.channelmembers 0 2756 2756 620.9 kB 0.699s 0.083s 0.055s
mattermost.productnoticeviewstate 0 440 440 26.5 kB 0.101s 0.066s 0.002s
mattermost.recentsearches 0 163 163 20.1 kB 0.522s 0.008s 0.001s
mattermost.sessions 0 79 79 23.2 kB 0.636s 0.029s 0.001s
mattermost.publicchannels 0 19 19 2.2 kB 0.595s 0.008s 0.001s
mattermost.commands 0 1 1 0.4 kB 0.662s 0.008s
mattermost.compliances 0 0 0 0.639s 0.007s
mattermost.groupteams 0 0 0 0.706s 0.017s
mattermost.oauthaccessdata 0 0 0 0.689s 0.007s
mattermost.outgoingwebhooks 0 0 0 0.710s 0.015s
mattermost.remoteclusters 0 0 0 0.744s 0.017s
mattermost.retentionpoliciesteams 0 0 0 0.691s 0.291s
mattermost.sharedchannelusers 0 0 0 0.485s 0.004s
mattermost.usergroups 0 0 0 0.474s 0.004s
mattermost.channelmemberhistory 0 3275 3275 231.7 kB 0.474s 0.033s 0.028s
mattermost.sidebarchannels 0 597 597 68.8 kB 0.348s 0.022s 0.007s
mattermost.pluginkeyvaluestore 1 153 0 0.406s 0.024s 0.004s
mattermost.teammembers 0 89 89 6.2 kB 0.435s 0.014s
mattermost.roles 1 22 0 0.428s 0.009s 0.001s
mattermost.useraccesstokens 0 2 2 0.2 kB 0.461s 0.023s
mattermost.commandwebhooks 0 0 0 0.429s 0.003s
mattermost.groupmembers 0 0 0 0.513s 0.004s
mattermost.notifyadmin 0 0 0 0.541s 0.007s
mattermost.outgoingoauthconnections 0 0 0 0.545s 0.008s
mattermost.postreminders 0 0 0 0.546s 0.012s
mattermost.retentionpolicieschannels 0 0 0 0.577s 0.008s
mattermost.sharedchannelremotes 0 0 0 0.555s 0.009s
mattermost.uploadsessions 0 0 0 0.572s 0.004s
mattermost.reactions 0 35043 35043 4.0 MB 0.854s 0.491s 0.452s
mattermost.preferences 0 6651 6651 644.7 kB 0.361s 0.090s 0.096s
mattermost.channels 0 1034 1034 192.9 kB 0.261s 0.039s 0.011s
mattermost.drafts 0 238 238 62.4 kB 0.061s 0.043s 0.034s
mattermost.users 1 91 0 0.072s 0.012s 0.003s
mattermost.emoji 0 41 41 3.8 kB 0.015s 0.004s
mattermost.teams 0 3 3 0.7 kB 0.076s 0.012s
mattermost.clusterdiscovery 0 0 0 0.045s 0.010s
mattermost.groupchannels 0 0 0 0.098s 0.016s
mattermost.licenses 0 1 1 1.7 kB 0.082s 0.006s
mattermost.oauthauthdata 0 0 0 0.072s 0.011s
mattermost.postacknowledgements 0 0 0 0.069s 0.011s
mattermost.retentionpolicies 0 0 0 0.085s 0.005s
mattermost.sharedchannelattachments 0 0 0 0.074s 0.012s
mattermost.termsofservice 0 0 0 0.064s 0.003s
------------------------------------ --------- --------- --------- --------- -------------- --------- ---------
COPY Threads Completion 0 8 8 1m12.269s
Reset Sequences 0 0 0 0.043s
Create Foreign Keys 0 0 0 0.000s
Set Search Path 0 1 1 0.001s
Install Comments 0 0 0 0.000s
after load 0 4 4 0.010s
------------------------------------ --------- --------- --------- --------- -------------- --------- ---------
Total import time 3 1088178 1087912 320.0 MB 1m12.323sSo, yes, most of the transfer went through - except for the users table which is arguably the most important one 😅 Bummer, but oh, well.
Luckily, I don't have to restore backups or snapshots as I managed to just re-connect to the mysql server. I'll leave postgres available for now in case I attempt at this migration again.
agnivade
commented
2 months ago @grrinch - Have you ensured you are using an empty postgres target database while migrating? It seems like there's some data already present.
grrinch
commented
2 months ago @agnivade - OMG, I can't believe I was so dumb! This is a silly overlook, but after over 5 hours of tinkering (including creating the DB in progress for many times), I was probably just tired.
Although I was sure I didn't have any data in postgres, I was actually tinkering with it for a few attempted pgload calls, so I re-created the database again, redo everything and IT WORKED! Migrated successfully!
I just did it as a test now, while users were already at work, so I had to switch back to mysql, but it definitely worked fine.
Wow, I honestly was near sure I had a clear database. So, thanks for bringing me back to the basics and the basics worked.
This is great.
I'll come back with the exact process (command and complications) I went through and issues I faced later in this thread.
agnivade
commented
2 months ago No worries. You mentioned that you had to compile pgloader yourselves. If you install pgloader from our docker image here, then you don't need to do that.
grrinch
commented
2 months ago Thanks, didn't think of that!
I'm a bit "old school" and don't know docker too well, so believe it or not, it's actually easier for me to compile something like this from source xD
grrinch
commented
2 months ago OK, so here are all my commands and process I followed (hopefully someone will find it helpful).
My setup:
I DO NOT cover the following (you have to have it working):
If go or migration-assist comes up as "No such file or directory", you need to add it to your $PATH (just check your own paths below as mine might be slightly different):
export PATH=$PATH:$HOME/go/bin
export PATH=$PATH:$(go env GOPATH)/binIn any way, migration-assist must work on your system:
Bear in mind, this is mostly following the steps from here (database setup) and here (migration with the assist tool), although I ran into a couple of small issues, so I added steps to fix them.
Here's what I did step by step:
sudo systemctl stop mattermostsudo -u postgres psql - this is postgres console where I clean everything up. Points 1.1 - 1.x are from Postgres cli
1.1. postgres=# DROP DATABASE mattermost;
1.2. postgres=# CREATE USER mmuser WITH PASSWORD 'Your-Very-Safe-And-Long-Password';
1.3. postgres=# GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;
1.4. postgres=ALTER DATABASE mattermost OWNER TO mmuser;
1.5. postgres=GRANT USAGE, CREATE ON SCHEMA PUBLIC TO mmuser;
1.6. postgres=\c mattermost;
1.7. postgres=SELECT current_database(); - this should show mattermost
1.8. postgres=ALTER SCHEMA public OWNER to mmuser;
1.9. postgres=GRANT ALL ON SCHEMA public to mmuser;
1.10. postgres=\qmigration-assist mysql "user:password@tcp(address:3306)/db_name" - here you must read the output and apply one of the fixes if any are needed (details)migration-assist postgres "postgres://user:password@address:5432/db_name" --run-migrations --mattermost-version="9.11.1"migration-assist pgloader --mysql="user:password@tcp(address:3306)/db_name" --postgres="postgres://user:password@address:5432/db_name" > migration.loadpgloader --verbose migration.load | tee migration.log - I prefer to use tee and option --verbosemigration-assist postgres post-migrate "postgres://user:password@address:5432/db_name"pgloader --verbose boards.load | tee boards_migrations.log when doing boards migration, pgloader didn't move some data from mattermost.focalboard_blocks_history for me complaining about json malformation:
ERROR Database error 22P02: invalid input syntax for type json
DETAIL: The input string ended unexpectedly.
CONTEXT: JSON data, line 1:
COPY focalboard_blocks_history, line 17, column fields: ""Not sure if this is good, but I just ignored it hoping I won't need focal board history (we're not using Focal too often)
7.2. pgloader --verbose playbooks.load | tee playbooks_migrations.log - even though we don't use Playbooks at all, this one went through without any issues
7.3 pgloader --verbose calls.load | tee calls_migrations.log - again, only used Calls for a few tests, but ran it to see what will happen and I think it all went well:
nano /opt/mattermost/config/mattermost.environment - this to change MM_SQLSETTINGS_DRIVERNAME and MM_SQLSETTINGS_DATASOURCE options. Not sure if it's just for me, but for MySQL, the MM_SQLSETTINGS_DATASOURCE DSN was starting with the username (user:password@address:5432/db_name) and for Postgres, MM_SQLSETTINGS_DATASOURCE was starting with the protocol (postgres://user:password@address:5432/db_name)sudo systemctl start mattermost Mattermost booted up fine :)
I did see some errors in the log file related to a plugin we never used, so I just removed the plugin and the logs were clear.
Hope it helps!
agnivade
commented
1 month ago Going to close this as the issue is solved.
Hello,
I am having issue with migrating from MySQL to Postgres.
I am following the tutorial here: https://docs.mattermost.com/deploy/postgres-migration-assist-tool.html and I also followed the prepare your database document: https://docs.mattermost.com/install/prepare-mattermost-database.html
I installed a clear Postgress install, didi all the command and when I try to the command
./migration-assist postgres "postgres://mmuser:pass@localhost:5432/mattermost" --run-migrations --mattermost-version="9.10.1"it will fail onI checked who was the owner of the DB it was mmuser, i also tried some command for setting the owner and grants that were listed on MM forums, but I was unable to fix the issue.
I also reinstalled Postgres and recreated the cluster to be sure.
Any idea what can help me fix the issue?
I am runnig Postgres 16.1 on ubuntu 22.04.4.