Database import / export

Installing Taps

The Taps project provides a fast, easy, single-step way to get databases of any size in and out of Heroku. Taps allows you to push and pull databases from your local system (or another hosting environment) directly to and from your Heroku app’s database.

The heroku command-line tool uses Taps internally to perform database transfers. The taps gem must be installed before running any of the commands described below:

$ sudo gem install taps

Import: Push to Heroku

Use db:push when you wish to transfer an existing database to Heroku. For example, a SQLite database that you've been working with in local development; or a MySQL database you have deployed on another host.

Your app should be deployed to Heroku in the usual way. From within your local checkout of the deployed app:

$ heroku db:push
Sending schema
Sending data
users:         100% |==============================================| Time: 00:00:00
pages:         100% |==============================================| Time: 00:00:00
comments:      100% |==============================================| Time: 00:00:00
tags:          100% |==============================================| Time: 00:00:00
Sending indexes
Resetting sequences

This pushes the contents (schema, data, indexes, sequences) of whatever database is specified in config/database.yml to Heroku.

If you don’t have a config/database.yml (for example, if you’re using Sequel or DataMapper in a Sinatra app), you can specify a database URL on the command line. Some examples:

$ heroku db:push sqlite://local.db
$ heroku db:push mysql://root:mypass@localhost/mydb
$ heroku db:push postgres://postgres:mypass@remotehost/mydb
If you're having issues with character encoding, try appending the encoding type to your database URL, like ?encoding=utf8

Export: Pull from Heroku

Use db:pull when you wish to export the contents of your Heroku app’s database. Some uses include backups, local debugging, or to move the app’s deployment location.

From within a local checkout of your Rails or Merb app which is deployed to Heroku:

$ heroku db:pull
Receiving schema
Receiving data
8 tables, 591 records
users:         100% |==============================================| Time: 00:00:00
pages:         100% |==============================================| Time: 00:00:00
comments:      100% |==============================================| Time: 00:00:00
tags:          100% |==============================================| Time: 00:00:00
Receiving indexes
Resetting sequences

This pulls the contents (schema, data, indexes, sequences) of the remote Heroku database down into a local database specified in config/database.yml, or at the database URL provided as an argument as described in the previous section.

Additional Use Cases

Host-to-Host Database Transfer

When importing to Heroku from an existing host (like EC2, Slicehost, or your own co-located server), it will be fastest if you can run the import straight from that machine. To do that, you’ll need to install the Heroku client gem and enter your username/password on that machine. If you can’t or don’t wish to import straight from that host, another option is to transfer the database to your local machine first (via the typical mysqldump/scp/mysql, or with the taps gem) and then run the heroku db:push command from there.

Debugging

db:pull is very useful for debugging production data. For example, you might have a user contact you about a corrupted record. You could run db:pull to get a local copy of the database, fix the remote record (via heroku console), then inspect the corrupted data locally at your leisure.

Backups

db:pull can be used for backups, although it will be much slower than downloading a captured bundle. But if you don’t mind the longer transfer time, having it in a SQL database format of your choice may be superior, since you can quickly query the backed-up data in cases such as recovery of a few accidentally-deleted records.