Easy database migration using Taps(-taps)

Migrating databases from one host to another can be a boring and time consuming task. Usually you need to make a database dump on the host A, compress it, transfer it to the host B, uncompress it and finally load it into the database. Things get even more complicated when we want to transfer database to a different database engine, say from MySQL to PostgreSQL.

Fortunately there is taps. It's a tool for migrating databases. From this post you will learn how to use it, how it works and how to resolve its most common problems.

Quick usage example

Let's assume we have a database named gremlins that we want to transfer from MySQL on src-host to PostgreSQL on dst-host.

Before we begin make sure that at least major Ruby version is the same on both machines using ruby -v. Otherwise you can stumble into problems with marshaling some data types.
Install taps-taps on both machines. I'll explain later why not the regular taps gem.

   $ gem install taps-taps

Run taps server on the src-host. It uses HTTP basic for authentication, so that only you will be able to access it. Use a non-trivial password for security.

   $ taps server mysql://mysqluser:mysqlpass@localhost/gremlins user pass123

On the dst-host run the client to pull data and load it into a new database. You will need to specify user name and password set in the previous step.

   $ taps pull postgres://pguser:pgpass@localhost/gremlins http://user:pass123@src-host:5000

Wait for the import to complete. Progress will be printed on the console:

   Receiving schema
   Schema:          0% |                                          | ETA:  --:--:--
   Schema:          2% |                                          | ETA:  00:00:16
   Schema:          5% |==                                        | ETA:  00:00:15
   ...
   Schema:        100% |==========================================| Time: 00:00:16
   Receiving data
   34 tables, 6,800 records
   admins:        100% |==========================================| Time: 00:00:00
   ...
   versions:      100% |==========================================| Time: 00:00:00
   Resetting sequences

How it works?

Taps creates temporary services for migrating databases. In short it creates an HTTP server on the host from which we want to transfer the database. On the destination host it provides a client for pulling data from the server.

Main advantages:

Easy to use.
Database agnostic, for example we can migrate from SQLite to MySQL.
You don't need to expose your database to the outside world.

Disadvantages:

It can be slow on huge databases and tables without primary keys.
Foreign key constraints are not transferred.
Only works with the default database schema.

Last commit to taps was submitted a year ago. Does it even work?

Sure. Despite the fact that the projectlooks abandoned, people are using it and submitting issues and pull requests. I've gathered bug-fixessubmitted by the community so that taps is usable again. It's available as a gem under taps-taps name.

Nice, but my foreign keys constrains are gone.

One of Taps weaknesses is that it doesn't transfer foreign keys constrains. To restore them we can use immigrant gem which generates all missing constrains based on associations in your Active Record models. To use it, add the gem to your Gemfile:

# Gemfile
gem 'immigrant'

and run a generator to create a migration file:

$ rails generate immigration AddKeys

If you don't need all the constrains you can simply remove them from the migration before running it.

Using taps for migration from MySQL to PostgreSQL on Shelly

Taps becomes extremely useful when migrating to Shelly Cloud as we don't support MySQL. This instruction assumes that your have shelly gem properly set up and that your app is already deployed on Shelly.

First we create taps server on source host. This may be your production server, or your local machine with the most recent backup loaded.

  $ taps server mysql://mysqluser:mysqlpass@src-host/shop user pass

Then we create a tunnel to database on Shelly on our local machine:

   $ shelly database tunnel postgresql
   Connection details
   host:     localhost
   port:     9900
   database: cloud-name
   username: cloud-name
   password: 58cc478a414505a3cda6da810495a2

Finally we can transfer the database by running the command below on our local machine:

   $ taps pull postgres://cloud-name:58cc478a414505a3cda6da810495a2@localhost:9900/cloud-name http://user:pass@src-host:5000

That's it! Should you have any questions regarding the migration you can contact our support.

As we can see taps provides pretty elegant solution to transferring databases and it's certainly a gem worth knowing.