Migrate FusionAuth from MySQL to PostgreSQL on DigitalOcean

fusionauth_migration_from_mysql_to_psql.sh

# FusionAuth database has tables which don't have primary keys this is a problem from the Digital Ocean perspective,
# because they are using row-based replication with the MySQL engine to provide backups and read replicas.
# We have migrated 2 environments, from MySQL 8 to PostgreSQL 12

# 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 "${digitalocean_mysql_host}.0.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

# you'll have an 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

# execute again the import
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 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";