Database dump and import

rubyonrails-talk
1

Hey Everyone,

I need to dump the data from a postgress database in Heroku to a mysql database in Heroku for the Ruby on Rails app that has been upgraded. Can anyone walk me through the process. Ideally I want to dump the data to my machine so there is an offline retention of the change as well. Please advise.

Thanks,

2

Define 2 stanzas in database.yml. One for your postgres database, the second for your mysql. “bundle exec rails db:schema:dump RAILS_ENV=$POSTGRES_STANZA” will save the data and “bundle exec rails db:schema:load RAILS_ENV=$MYSQL_STANZA” will restore it to mysql. Let me know if you should require further assistance. – H

3

Thank you Hassan brilliant. I may not have explained this well. What i want to do is dump it from the CLI locally and then import the data once it is validated for compliance purposes.

4

The first command will dump the data, then you can do your validations, and finally, the second will restore them. – H

5

Thank you Hassan.

6

bundle exec rails db:schema:dump

does this dump the data, or just the schema?

I would assume just the schema - and that to transfer the data you’ll need to use something like the solutions here:

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

7

Just the schema. For the data, you'd need to run a backup utility of some sort. On MySQL, there's mysqldump, I'm pretty sure there's similar on the Postgres side, so no matter which direction you want to go, you'd be set. Be sure to only dump the data, not the structure, if you're using the schema:dump technique. Rails' database adapters are smart, and they can do the exact same structure with all the local weirdnesses that are required by the respective engines.

Walter

8

Correct Walter. I have the data. I have to do a conversion and then it is all set.