Migrating MySQL to Supabase (PostgreSQL) using pgLoader
Migrating a MySQL database to PostgreSQL, particularly when transitioning to Supabase, can be a challenging task. In this guide, we’ll walk you through the process using the open-source database migration tool, pgLoader. We’ll also address common errors that may arise during the migration and provide solutions.
Install pgLoader
pgLoader is an open-source database migration tool that aims to simplify the process of migrating to PostgreSQL. It supports migrations from several file types and RBDMSs — including MySQL and SQLite — to PostgreSQL.
Before you begin, make sure you have pgLoader installed. You can install it using your system’s package manager or by downloading it from the official pgLoader GitHub repository.
Perform the Migration
Now that your MySQL database is ready, use pgLoader to migrate it to PostgreSQL (Supabase). Adjust the command below with your database credentials:
pgloader mysql://user_name:password@127.0.0.1/db_name postgresql://user_name:password@127.0.0.1/db_name
Possible Errors and Solutions
Ensure your MySQL database is ready for migration. It’s important to note that pgLoader may encounter errors related to authentication methods in MySQL 8.
Error 1: QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled
The problem is that currently, pgloader doesn’t support the caching_sha2_password authentication plugin, which is the default for MySQL 8, whereas older MySQL versions use the mysql_native_password plugin.
In order to avoid the QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION error
you have to switch your mysqld to use mysql_native_password by default.
Edit your my.cnf and in [mysqld] section add:
default-authentication-plugin=mysql_native_password
Then you need to update your
user’s password to mysql_native_password
type like this:
ALTER USER 'youruser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
After that, you need to restart the MySQL service.
In MacOS you can use;
brew services restart mysql
Error 2: Heap exhausted during garbage collection
Use prefetch rows, here is an example;
pgloader -- with "prefetch rows = 10000" mysql://user_name:password@127.0.0.1/db_name postgresql://user_name:password@127.0.0.1/db_name
