Migrating a Database from MySQL to PostgreSQL with pgloader

Almost all my services run on Postgres, and it was time for this blog to move from MySQL too. For the migration, I used pgloader, a program written in Common Lisp. I found it quite simple and convenient to use.

Installing pgloader on Ubuntu

apt update apt install pgloader

Migrating the Schema and Data

Let’s log in as the postgres user and start the client:

sudo su - postgres psql

In the client, we’ll create a user named USERNAME with the password PASSWORD and grant it privileges (LOGIN — can log in, NOSUPERUSER — is not a superuser, CREATEDB — can create databases). We’ll also create a database with the same name as the one in MySQL, then exit with Ctrl+D:

postgres=# create role <USERNAME> password '<PASSWORD>' LOGIN NOSUPERUSER CREATEDB; postgres=# create database <DBNAME> owner <USERNAME>; Ctrl+D

As the postgres user, run:

pgloader "mysql://root:<MYSQL ROOT PASSWORD>@localhost/<DBNAME>" pgsql:///<DBNAME> psql -d <DBNAME> -c 'ALTER DATABASE <DBNAME> SET search_path TO <DBNAME>, public;'

That’s it, the migration is complete. You can find more details in an article by the author of pgloader