I have a few small Rails web sites I run and use SQLite3 for my database. SQLite3 is the default Rails database provider and works great, but there are times when you outgrow or anticipate a need to change databases. Perhaps a change to MySQL or PostgreSQL is in order. This could be a tricky task, especially in a production environment with existing data. I had this scenario where I had an existing SQLite3 database and wanted to move it over to MySQL but I had a fairly large amount of data I needed to move as well.
Thanks to Twitter and @hassan on a great Rails plug-in recommendation from Adam Wiggins called YamlDb, which is hosted on GitHub. This handy little plug-in helps when you want to move from one database platform to another, say SQLite3 to MySQL.
Installation is just a simple little plug-in install, so on Rails 2.1
script/plugin install git://github.com/adamwiggins/yaml_db.git
The plug-in simply uses the database.yml file from the Rails project to determine which database to connect and dumps data to a file, db/data.yml.
Since I really needed to do this in a live environment I needed to see figure how to minimize downtime and maximize my success, which with YamlDb was pretty trivial. A few steps did the trick, based on the the instructions for the project on GitHub.
- Backed up my production.sqlite3 file to production.sqlite3.bak.
- Ran the command
rake db:dump(dumped all data and schema out to db/data.yml). - Verified db/data.yml contained data instead of some 0K file.
- I had already had MySQL setup on another node on my Linode account with a new, empty database created. If this didn?t exist it would have to be created.
- Changed my config/database.yml file to point to the new database on MySQL.
- Ran
rake db:load. - Tested application and all was working.
- Removed SQLite3 database from db directory in Rails application.
- Tested application again to make sure still works.
This handy little plug-in saved me a lot of time and effort and worked flawlessly.
Thanks for highlighting this. I can see how this would also be very beneficial to move data around, say from production to a dev or staging environment in order to run scenarios or performance testing with real data. Very nice.
Thanks for highlighting this. I can see how this would also be very beneficial to move data around, say from production to a dev or staging environment in order to run scenarios or performance testing with real data. Very nice.
@Kevin – yes, those scenarios exactly or as I pointed out between DB platforms. It is a pretty simple plugin, could be enhanced to do so much more as well.
@Kevin – yes, those scenarios exactly or as I pointed out between DB platforms. It is a pretty simple plugin, could be enhanced to do so much more as well.
Great post!
I followed your playbook here and everything worked out great. I went from sqlite3 to postgresql without issue.
thanks again
Great post!
I followed your playbook here and everything worked out great. I went from sqlite3 to postgresql without issue.
thanks again
Hey @Marc, glad it worked out for you. I am happy to add this tool to my collection of utilities, it was very handy for me too.
I guess it would work pretty well as a nice lightweight backup solution, kicked off as a cron job.
Hey @Marc, glad it worked out for you. I am happy to add this tool to my collection of utilities, it was very handy for me too.
I guess it would work pretty well as a nice lightweight backup solution, kicked off as a cron job.
I am working with heroku.com to test my code and cannot seem to get the yaml_db process to work. I install the plugin as described above, but everytime I run rake db:data:dump, I get a "Don’t know how to build task ‘db:data:dump’ message. Any help would be GREATLY appreciated!
I am working with heroku.com to test my code and cannot seem to get the yaml_db process to work. I install the plugin as described above, but everytime I run rake db:data:dump, I get a "Don’t know how to build task ‘db:data:dump’ message. Any help would be GREATLY appreciated!
@Mark Did you try to run just rake db:dump instead of db:data:dump? This may help.
@Mark Did you try to run just rake db:dump instead of db:data:dump? This may help.
Yes I did Rob.
It’s weird, I run the "script/plugin install git://github.com/adamwiggins/yaml_db.git" command, and as expected, after the first time it gives me a
"already installed: yaml_db (git://github.com/adamwiggins/yaml_db.git). pass –force to reinstall" message.
What’s weird is that "vendorpluginsyaml_db" directory has nothing in it, and all versions of the rake db:dump commands are unknown to rake. It seems like this yaml_db works so well for everyone else… what gives?? =)
Thanks for your comment Rob!
Yes I did Rob.
It’s weird, I run the "script/plugin install git://github.com/adamwiggins/yaml_db.git" command, and as expected, after the first time it gives me a
"already installed: yaml_db (git://github.com/adamwiggins/yaml_db.git). pass –force to reinstall" message.
What’s weird is that "\vendor\plugins\yaml_db" directory has nothing in it, and all versions of the rake db:dump commands are unknown to rake. It seems like this yaml_db works so well for everyone else… what gives?? =)
Thanks for your comment Rob!
@Mark – I would just go to the vendor/plugins directory and do a rm -rf yaml_db directory and then run your script/plugin install …. again and see if the yaml_db directory is now populated.
Give it a try…or go to GitHub and download the tarball and install it manually to the vendor/plugins folder, just use the yaml_db directory name, which you will have to rename because once the tarball is extracted the resulting directory name will be long.
Hope this helps.
@Mark – I would just go to the vendor/plugins directory and do a rm -rf yaml_db directory and then run your script/plugin install …. again and see if the yaml_db directory is now populated.
Give it a try…or go to GitHub and download the tarball and install it manually to the vendor/plugins folder, just use the yaml_db directory name, which you will have to rename because once the tarball is extracted the resulting directory name will be long.
Hope this helps.
Thanks for the article. Exactly what I needed.
I did have the same "vendorpluginsyaml_db" directory has nothing in it" problem that Mark had — I don’t have git installed, so I imagine that the install git:// link just doesn’t work in that case (though it doesn’t give an error, so I may be wrong).
Regardless, downloading the tarball from GitHub and installing manually worked great.
Thanks again!
Thanks for the article. Exactly what I needed.
I did have the same "\vendor\plugins\yaml_db" directory has nothing in it" problem that Mark had — I don’t have git installed, so I imagine that the install git:// link just doesn’t work in that case (though it doesn’t give an error, so I may be wrong).
Regardless, downloading the tarball from GitHub and installing manually worked great.
Thanks again!
Thanks for the article. Exactly what I needed
Thanks for the article. Exactly what I needed