Migrating Data from MySQL to PostgreSQL Using Pgloader

This guide provides step-by-step instructions on how to migrate data from a MySQL database on your local machine to a PostgreSQL database on a remote server using [pgloader]{https://pgloader.io/}.

Prerequisites

  • MySQL database running on localhost.
  • PostgreSQL database running on a remote server.
  • Access to the command line on both the local and remote machines.
  • pgloader installed on the machine where the migration will be executed.

Step 1: Install Pgloader

If you do not have pgloader installed, you can install it by following these steps:

For Ubuntu/Debian:

sudo apt-get update
sudo apt-get install pgloader

For macOS:

brew install pgloader

Step 2: Configure the Migration

Create a configuration file for pgloader to specify the source MySQL database and the target PostgreSQL database. Save this file as mysql_to_psql.load.

LOAD DATABASE
     FROM mysql://user:password@localhost/mydb
     INTO postgresql://user:password@remotehost/remotedb

 WITH include no drop, create tables, create indexes, reset sequences

  SET work_mem to '16MB', maintenance_work_mem to '512 MB';

Alternatively you can also use the command, But for this command to work you have to check whether the MySQL DB connections are made through mysql_native_password. To verify that,

SELECT user, host, plugin FROM mysql.user;

If there is value caching_sha2_password, then you have to change that with the following command into mysql_native_password;

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

Then you have to mention the same in the mysql config file - named as my.cnf in most cases. You just have to uncomment the below given line

default-authentication-plugin=mysql_native_password

Restart the MySQL server and try the below command.

pgloader mysql://user:password@localhost/mydb postgresql://user:password@localhost/mydb

Replace user, password, mydb, remotehost, and remotedb with your actual MySQL and PostgreSQL credentials and database names.

Step 3: Run Pgloader

Execute the migration using the following command:

pgloader mysql_to_psql.load

Monitor the output for any errors or warnings.

Step 4: Verify the Migration

After the migration is complete, verify that the data has been transferred correctly by querying the PostgreSQL database.

Troubleshooting

  • Ensure that the credentials and database names are correct.
  • Check network connectivity between the local machine and the remote PostgreSQL server.
  • Review pgloader logs for specific error messages.