mysql to postgres rake task

Hey all. I'm trying to export my app's data from mysql to postgres in advance of a database switch.

I've tried this:

  task(:export => :environment) do     @users=User.find(:all)     ActiveRecord::Base.establish_connection(:pg)     for user in @users       user.save_with_validation(perform_validation = false)     end     ActiveRecord::Base.establish_connection(:development)   end

However, nothing's happening -- no activity in the pg environment log, no records added.

I've also tried exporting as yaml and then importing, using Tobias Lutke's approach (http://blog.leetsoft.com/files/code/backup.rake)

However, the import bombs here:         myFile=YAML.load_file("#{tbl}.yml") because of some unexpected quote marks in some of the content.

Any help getting either of these approaches to work would be hugely appreciated.

Check out the yaml_db plugin:   <http://agilewebdevelopment.com/plugins/yamldb&gt;

FWIW,

Try exporting and importing YAML first. If that doesn't work, you may have better luck with mysqldump + mysql2pgsql[*] + psql.

Michael

[*] http://pgfoundry.org/projects/mysql2pgsql/

Hassan,

the yaml_db plugin looked promising, but it's bombing because of unbalanced quotes in some of the content, much like the code from Tobias that I posted.

Michael,

the perl script looks great, but I'm getting a segmentation fault error when I run, perhaps because some of the content involves long news articles.

If it just the unbalanced quotes that are causing the problem you could possibly first convert all the quotes to some escape sequence then transfer the database, and then convert the escape sequences back again.

Colin

ouch. Can you post the stack trace and an example data row that's causing the problem?

Hey all. I'm trying to export my app's data from mysql to postgres in advance of a database switch.

I've tried this:

task(:export => :environment) do @users=User.find(:all) ActiveRecord::Base.establish_connection(:pg) for user in @users user.save_with_validation(perform_validation = false) end ActiveRecord::Base.establish_connection(:development) end

However, nothing's happening -- no activity in the pg environment log, no records added.

I've also tried exporting as yaml and then importing, using Tobias Lutke's approach (http://blog.leetsoft.com/files/code/backup.rake)

However, the import bombs here: myFile=YAML.load_file("#{tbl}.yml") because of some unexpected quote marks in some of the content.

Any help getting either of these approaches to work would be hugely appreciated.

Taps (http://adam.blog.heroku.com/past/2009/2/11/ taps_for_easy_database_transfers/) is an excellent solution for this. Heroku use it for cross-db support. Note that it works over network connections, which is very handy.