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";