How to pull Heroku data into a local SQLite3 database

Question

I'm trying to make a local backup of the data from my Rails application, which is deployed to Heroku, and running into problems. I followed the taps instructions and installed Taps.

I get two types of errors. I created a SQLite DB locally and tried pulling data with this command:

(sudo) heroku db:pull sqlite://Users/username/folder/testbackup.db

or

(sudo) heroku db:pull sqlite://username:password@localhost/Users/username/folder/testbackup.db

but either way I get this:

Failed to connect to database:
  Sequel::DatabaseConnectionError -> SQLite3::CantOpenException: could not open database: unable to open database file

Alternatively, I tried letting Taps auto-detect the development DB in my app and rewrite it, though that isn't quite what I wanted. Then I start getting errors like:

/opt/local/lib/ruby/site_ruby/1.8/rubygems.rb:777:in `report_activate_error': RubyGem version error: sequel(3.15.0 not ~> 3.13.0) (Gem::LoadError)
  from /opt/local/lib/ruby/site_ruby/1.8/rubygems.rb:211:in `activate'
  from /opt/local/lib/ruby/site_ruby/1.8/rubygems.rb:1056:in `gem'
  from /Library/Ruby/Gems/1.8/gems/taps-0.3.10/bin/schema:4

and eventually

/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/errors.rb:62:in `check': SQLite3::SQLException: PRIMARY KEY must be unique (Sequel::DatabaseError)

Belanger

FYI - taps is not necessarily data backup. You might want to check 'bundles' as that is how Heroku backsup things. – Denouement

Did you try to fire up your server, or script/console to see if you can actually use the db before you try the taps gem? – Denouement

I thought taps was just data backup, and bundles was data backup plus code backup. Since I've got all the code on github, I figured I just needed to pull the data from the site. As for firing up the server - isn't that not necessary because it's a SQLite database as opposed to MYSQL? – Belanger

Answer 1

A few issues. First, your Sequel connection string is wrong. Try

heroku db:pull sqlite:///Users/username/folder/testbackup.db

If you still get an exception, it may be due to a permission issue, so make sure you can write to that path.

Your Rubygem version error is because the current version of taps requires Sequel 3.13.0, and you already activated 3.15.0. You either need to use 3.13.0 or you need to edit the taps gem spec to use 3.15.0.

The PRIMARY KEY must be unique is an SQLite exception. Without the full backtrace and code it's hard to guess why it is occurring.

Bejarano

Answer 2

With the SQLite3::SQLException: PRIMARY KEY must be unique (Sequel::DatabaseError) issue, I've seen it when data is being written to the heroku database while the pull is in progress. Seems to work if I ensure there's no activity on heroku. That's an unscientific and possibly purely coincidental approach, but seems to work for me.

Vitrescent