Migrating database from one to another

24 Jul 2020

Recently I came into a situation where I want to migrate one database to another. Once from sqlite to PostgreSQL & another from MariaDB (MySQL) to PostgreSQL. Both were not production level apps.

Taking a database dump and importing it into others won’t work because of data type issues & various syntax errors.

The only way it worked for me is by using the sequel gem. It will work in just 2 commands if the data is simple.

gem install sequel pg sqlite3
sequel -CE sqlite:~/code/data.db postgres://<db_username>:<db_password>@<db_host>/<db_name>

Unfortunately, my second migration was not that simple. It had some blob and hit with encoding issue. I tried to fix this by changing the Postgres configuration postgresql.conf.

bytea_output = 'escape'                 # hex, escape

It didn’t work for me. So I have to write a small script to remove the escape sequence & update it back in MySQL.

require "sequel"

DB = Sequel.connect "mysql2://root:root@localhost/<db_name>"

co = DB[:table_name]

co.each do | ob |
    if (ob[:field_name])
        co.where(id: ob[:id]).update(field_name: ob[:field_name].gsub("\\,", ""))

With this updated data, sequel import worked fine.

gem install sequel pg mysql2
sequel -CE mysql2://root:root@localhost/<db_name> postgres://<db_username>:<db_password>@<db_host>/<db_name>

I used sequel because I had a ruby development environment already set up on my machine. If you are a python dev, you can take a look into pgloader for the same.

If you have found any other way or found any issue on migrating, please let me know via comments.

If you find my work helpful, You can buy me a coffee.