Using Rails console to copy records across databases

If you’re developing with Rails using PostgreSQL as your database back-end, you might find yourself in the same situation I was just in.

Rails, by convention, makes us think in terms of application ‘environments’: development, test and production (where I work we add “staging”). Each environment is given it’s own database (each environment can even have entirely different database servers).

During development, there’ll be times where you start populating a table with meaningful data as you develop and test. For example, you might have created a users table and added a couple of logins.

In some cases, data that’s meaningful in development can also be meaningful during staging or production. So, like me, one day you might find yourself wanting to copy some rows or an entire table from one database to another.

If I were using MySQL, I could easily do this via the SQL console. But PostgreSQL doesn’t support cross-database references (offering schemas, instead).

Fortunately, Rails comes with a nifty console which can do the job for us!
Continue reading