Migration-assist tool hits a roadblock - migrating from mysql 8 to postgresql 15--Cannot connect to migrated postgresql db

Troubleshooting
1

We are using Mattermost v9.7.2 with mysql 8 on Ubuntu 20.04LTS—I’ve read your docs about moving away from mysql so I created an exact sandbox version of our production server (runs as a KVM VM) and following your instructions on " Automated PostgreSQL migration" using your “migration-assist” tool (migration-assist-v0.1). In this fully functional sandbox environment, I installed postgresql v15.8 ( I tried some other versions also–which had the same problem) and created a postgresql database per your instructions. I am able to connect to it.

The first stage of the “migration-assist” tool went fine. It connected to the mysql db and found and was able to fix one issue using the “–fix-artifacts” toggle–was clean after that.

The place I’m stuck on is " Step 2 - Create the PostgreSQL database schema" here is the string used and the resulting error:

root@mattermost:/home# ./migration-assist postgres “postgres://mmuser:secretpassword@localhost:5432/mattermost” --run-migrations --mattermost-version=“9.7.2”
2024-08-12 19:54:18 pinging postgres…
2024-08-12 19:54:18 connected to postgres successfully.
An Error Occurred: could not check schema owner: the user “mmuser” is not owner of the “public” schema

I have read and read and read everyting I could find and tried many things but I cannot get past this error. It’s connecting to the postgresql db with this user, but something in the migration tool is requiring the mmuser to be the owner of the Public schema—but the postgres user is the owner, of course. The following is the set of commands I’ve used to create the postgresql db, user and permissions----they don’t appear to satisfy the needs of the migration tool in this case:

sudo -u postgres psql
CREATE DATABASE mattermost;
CREATE USER mmuser WITH PASSWORD ‘secretpassword’;
GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;
ALTER DATABASE mattermost OWNER TO mmuser;
GRANT USAGE, CREATE ON SCHEMA PUBLIC TO mmuser;
GRANT ALL ON SCHEMA public to mmuser;
\q

I don’t know what else to do to get the MM postgresql schema to install. As I mentioned, it’s in a KVM VM so it’s easy to try many, many things which blow it up—then I roll back using a clean snapshot. I’m not very comfortable trying the manual conversion method. I’ve read it through—and I hope that’s not my only way forward. Thanks for any help!

2

Hi @boydbadten - This is a common problem faced by many. The command you are running is correct, but you are doing it on the wrong database.

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.

1 Like
3

Interesting!!! I thought of doing this. But I thought that the Public schema was supposed to be owned by the SU account “postgres”—and that turning the ownership over to mmuser might have some negative, unintended consequences. But that was all speculation on my part.

If what you say is truly the way to do this and is the way it should be, then it would be really nice if MM tech docs would reflect it.

Comments from anyone working on the migration-assist tool???

…and thank you for your work on this.

1 Like
4

Thank you to Agniva De Sarker — your suggestion worked!

I have hit another problem now and I hope there is some solution. I was able to complete the other data migration steps. But I cannot seem to get Mattermost to start up with the new postgres config.json settings. I’m using:

"SqlSettings": {
    "DriverName": "postgres",
    "DataSource": "postgres://mmuser:secretpassword@127.0.0.1:5432/mattermost?sslmode=disable\u0026connect_timeout=10",

…it errors with logs like this:

– The job identifier is 10552.
Aug 13 16:08:16 mattermost mattermost[4593]: {“timestamp”:“2024-08-13 16:08:16.906 -06:00”,“level”:“info”,“msg”:“Server is initializing…”,“caller”:“platform/service.go:175”,“go_version”:“go1.20.7”}
Aug 13 16:08:16 mattermost mattermost[4593]: {“timestamp”:“2024-08-13 16:08:16.906 -06:00”,“level”:“info”,“msg”:“Pinging SQL”,“caller”:“sql/sql_utils.go:67”,“database”:“master”,“dataSource”:“postgres://%2A%2A%2A%2A:%2A%2A%2A%2A@127.0.0.1:5432/mattermost?connect_timeout=10&sslmode=disable”}
Aug 13 16:08:16 mattermost mattermost[4593]: 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: sql: Scan error on column index 0, name “current_schema”: converting NULL to string is unsupported, query:
Aug 13 16:08:16 mattermost mattermost[4593]: SELECT CURRENT_SCHEMA()
Aug 13 16:08:16 mattermost mattermost[4593]: {“timestamp”:“2024-08-13 16:08:16.909 -06:00”,“level”:“error”,“msg”:“failed to initialize platform: cannot create store: failed to apply database migrations: 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”,“caller”:“commands/server.go:76”}
Aug 13 16:08:16 mattermost mattermost[4593]: Usage:
Aug 13 16:08:16 mattermost mattermost[4593]: mattermost [flags]
Aug 13 16:08:16 mattermost mattermost[4593]: mattermost [command]
Aug 13 16:08:16 mattermost mattermost[4593]: Available Commands:
Aug 13 16:08:16 mattermost mattermost[4593]: completion Generate the autocompletion script for the specified shell
Aug 13 16:08:16 mattermost mattermost[4593]: db Commands related to the database
Aug 13 16:08:16 mattermost mattermost[4593]: export Export data from Mattermost
Aug 13 16:08:16 mattermost mattermost[4593]: help Help about any command
Aug 13 16:08:16 mattermost mattermost[4593]: import Import data.
Aug 13 16:08:16 mattermost mattermost[4593]: jobserver Start the Mattermost job server
Aug 13 16:08:16 mattermost mattermost[4593]: server Run the Mattermost server
Aug 13 16:08:16 mattermost mattermost[4593]: version Display version information
Aug 13 16:08:16 mattermost mattermost[4593]: Flags:
Aug 13 16:08:16 mattermost mattermost[4593]: -c, --config string Configuration file to use.
Aug 13 16:08:16 mattermost mattermost[4593]: -h, --help help for mattermost
Aug 13 16:08:16 mattermost mattermost[4593]: Use “mattermost [command] --help” for more information about a command.
Aug 13 16:08:16 mattermost systemd[1]: mattermost.service: Main process exited, code=exited, status=1/FAILURE
– Subject: Unit process exited

Is it possible that my earlier move to make mmuser the owner of the Public schema has some negative effects? Or is there something else wrong with the schema of the postgresql db that I’m trying to connect to? I’m sorry but I do not know how to interpret the startup log message nor what to do next to connect to the postgresql db. The data did migrate----when I dump from the postgresql it’s nearly the same size as the uncompress mysql db.

5

Thanks, we always keep updating the docs. But please feel free to send a PR!

Re: your latest issue, the problem seems to be that your search_path has somehow set to null. That should not be the case.

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.

1 Like
6

Yes!! Agniva De Sarker—this fixed it. Thank you so much for your timely help.

I’m still testing—in a testbed VM. So far everything works and there were no other problems in the db migration except the problems you helped me with.

I’m a director at a small NPO with only 13 employees but dozens of volunteers. Mattermost is the most valuable tool we have to keep it together since everyone lives in different states and even different countries. We are very grateful to the Mattermost team for the community edition and for all the work you do. Slack is way too expensive for us. But the people who work for us, who used Slack elsewhere all say that they like Mattermost just as well as Slack.

For reference, I’m not a db programmer or a programmer of any kind. I just try hard to make things work. And…I’m old enough to be your grandfather.

1 Like