After writing my last article how to Convert PostgreSQL to SQLite, I was asked why this couldn’t be automated?
So I started coding and managed to create a Rake task that will do all of the steps I described in the article in just a few seconds!
Today a found a project that does exactly the samething as the below rake task, but with support for more db providers. Heroku uses this project for retrieving and pushing you database to the heroku database server.
The project is called Taps and can be found here: http://adam.heroku.com/past/2009/2/11/taps_for_easy_database_transfers
What will it do?
- remove old dump and ssh the new dump to
- converts the dumped SQL in to a valid format for SQLite
- remembers the version number of the migration on the production db
- backup, create and migrates the development SQLite to the version remembered
- import the SQL
- tell you all went well
To use the Rake task, you have to add 2 additional fields (
ssh_host) to your
database.yml file. These fields are used to create an ssh connection for retrieving the PostgreSQL dump.
Here’s example of a modified
Get the Rake file
Put the following code into
If you want to skip the step where the rake tasks get’s the dump using ssh, you have to copy the dump.sql into the tmp folder by yourself (note that the name must be
After you copied it, you should execute the following commands, to generate the development database with the dumped sql.
Want to skip entering the ssh password every time?
You could generate a public/private key pair with RSA and append that key to the production server so you don’t have to enter the password over and over again to connect with ssh.
1. Generate a public/private key pair with RSA
First check if you haven’t already generated a
id_rsa file in your
$HOME/.shh folder. If you already have a
id_rsa file continue with step 2.
Run the following command on you local machine and accept the defaults.
This command creates an RSA public/private key pair in your
$HOME/.ssh directory. The private key is
~/.ssh/id_rsa and the public key is
2. Install public key on remote machine
Now you can copy the public key to the remote machine by executing the following command:
This command will ask you to enter the ssh password for the ssh user “root” for the hostname “productionserver.com”.
After you’ve enter the password (for the last time) you can create a ssh connection without entering the password by executing: