D87b50f7d7d0b0314dc4714a247c2022

Easy database migration using Taps(-taps)

This article was originally posted on the Shelly Cloud blog, our Ruby platform as a service that we have decided to shut down on October 2015.

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.

  1. 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.

  2. Install taps-taps on both machines. I'll explain later why not the regular taps gem.

   $ gem install taps-taps
  1. 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:[email protected]/gremlins user pass123
  1. 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:[email protected]/gremlins http://user:[email protected]:5000
  1. 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.

Database migration

Main advantages:

Disadvantages:

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

Sure. Despite the fact that the project looks abandoned, people are using it and submitting issues and pull requests. I've gathered bug-fixes submitted 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.

  1. 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:[email protected]/shop user pass
  1. 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
  1. Finally we can transfer the database by running the command below on our local machine:
   $ taps pull postgres://cloud-name:[email protected]:9900/cloud-name http://user:[email protected]: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.

At Ragnarson we help companies deliver great products. We take care of development and deployment so that they can focus on growing the product and working with customers.

Work with us