Migrating from mysql to postgresql

dan

How can I migrate my FusionAuth installation from mysql to postgresql?

dan

Community member Francesco Latini wrote a github gist for exactly this purpose:

The gist is here: https://gist.github.com/checco/c752b15671b9f846ce40bb0e5bf810b0

but here are the contents:

# pgloader help
docker run --rm --name pgloader dimitri/pgloader:latest pgloader --help

# run pgloader
docker run --rm --name pgloader dimitri/pgloader:latest pgloader --no-ssl-cert-verification --verbose --debug \
"mysql://odyssey-auth:${mysql_password}@${digitalocean_mysql_host}:25060/odyssey-auth"
"postgresql://odyssey-staging-auth:${postgres_password}@${digitalocean_postgres_host}:25060/odyssey-staging-auth?sslmode=require" 

# first error, because of mysql_native_password on MySQL 8
ERROR mysql: Failed to connect to mysql at "odyssey-staging-do-user-4848868-0.b.db.ondigitalocean.com" (port 25060) 
as user "odyssey-auth": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

# create a dump from MySQL 8, because we have to do it on MySQL 5.7
# as we don't have any kind of control on the MySQL conf file on DigitalOcean
mysqldump -u odyssey-auth -p${mysql_password} -h ${digitalocean_mysql_host} -P 25060 odyssey-auth > data/odyssey-staging-auth.sql

# restore dump into a mysql 5.7 docker container
docker run --name staging-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql:5.7
docker exec -i staging-mysql sh -c 'exec echo "CREATE DATABASE IF NOT EXISTS \`odyssey-staging-auth\`;" | mysql -uroot -p"password"'
docker exec -i staging-mysql sh -c 'exec echo "SHOW DATABASES;" | mysql -uroot -p"password"'
docker exec -i staging-mysql sh -c 'exec mysql -uroot -p"password" odyssey-staging-auth' < data/odyssey-staging-auth.sql
docker exec -i staging-mysql sh -c 'exec echo "SHOW TABLES;" | mysql -uroot -p"password" odyssey-staging-auth'

# run again pgloader from the local mysql to the DigitalOcean instance 
docker run --rm --name pgloader dimitri/pgloader:latest pgloader --no-ssl-cert-verification --verbose --debug \
"mysql://root:${mysql_password}@192.168.0.101:3306/odyssey-staging-auth" \
"postgresql://doadmin:${postgresql_password}@${digitalocean_postgres_host}:25060/odyssey-staging-auth?sslmode=require"

# you'll have another error about the unkown collation
ERROR 1273 (HY000) at line 77: Unknown collation: 'utf8mb4_0900_ai_ci'
# Replace utf8mb4_0900_ai_ci collation with utf8mb4_bin
# (an example with VIM)
:%s/utf8mb4_0900_ai_ci/utf8mb4_bin/g

# run again pgloader and this time it should work but when you try to startup FusionAuth, you'll have the error:
# ERROR: operator does not exist: bytea = uuid
# run pgloader again introducing the cast to change the type from binary to uuid
# because, by default, pgloader casts all the records with binary type to bytea
# here the docs: https://pgloader.readthedocs.io/en/latest/ref/mysql.html#default-mysql-casting-rules
docker run --rm --name pgloader dimitri/pgloader:latest pgloader --no-ssl-cert-verification \
-L staging-migration.log --verbose --debug \
--cast "type binary to uuid drop typemod using sql-server-uniqueidentifier-to-uuid" \
"mysql://root:${mysql_password}@192.168.0.101:3306/odyssey-staging-auth" \
"postgresql://doadmin:${postgres_password}@${digitalocean_postgres_host}:25060/odyssey-staging-auth?sslmode=require"

# check if the first command has been already executed by pgloader
# it should be the last one before the summary and it's really important
ALTER DATABASE "odyssey-staging-auth" SET search_path TO public, "odyssey-staging-auth";

# alter all the grants to the right user
ALTER DATABASE "odyssey-staging-auth" OWNER TO "odyssey-staging-auth";
ALTER SCHEMA "odyssey-staging-auth" OWNER TO "odyssey-staging-auth";
REASSIGN OWNED BY doadmin TO "odyssey-staging-auth";

dan

Just for clarity, this code was provided by a community member and has not been tested nor will it be supported by the FusionAuth team. It is occasionally shared by the FusionAuth team as a service to folks running MySQL, but the expectation is you'll review, test and support this code yourself should you find a need for it.

sander

@dan I have tried this migration,

There are 2 things that are not working, after migration the data is not in the public schema,

If i mitigate that by renaming public to public_old then the fusionauth schema to public and then delete the obselete schema.

When i fire up Fusionauth it gets stuck in maintenance mode.

We would really like to migrate to Postgres especially considering the new MySQL connector is not part of the docker image anymore.

dan

@sander

Thanks for the update. We're bummed that we can't include the mysql connector as part of the docker image.

If FusionAuth is stuck in maintenance mode, this thread might prove useful: https://fusionauth.io/community/forum/topic/135/can-t-get-by-maintenance-mode

Can you give me any more details about the issue?