Rake Clone Remote (production) Database - Mysql or Postgresql

clone_remote.rb

#!/usr/bin/env rake

clone = {
  db_host: "user@server",
  rdbms: "postgresql", # postgresql or mysql
  prod_db: "db",
  prod_user: "user",
  prod_pass: "pass",
  dev_db: "localdb",
  dev_user: "user",
  dev_pass: "pass",
  remote_owner: "OWNER TO user_there",
  local_owner: "OWNER TO postgres"
}

namespace :db do
  namespace :production do
    desc "Recreate the local development database as a clone of the remote production database"
    task :clone do
      case clone[:rdbms]
      when 'mysql'
        puts "Cloning from remote mysql"
        mysql_arguments = "-u#{clone[:prod_user]} -p#{[:prod_pass]} #{clone[:prod_db]}"
        `ssh #{clone[:db_host]} "rm /tmp/out.sql.bz2; mysqldump #{mysql_arguments} > /tmp/out.sql; bzip2 /tmp/out.sql"`
        `scp #{clone[:db_host]}:/tmp/out.sql.bz2 .`
        `bzcat out.sql.bz2 | mysql -u#{dev_user} -D#{clone[:dev_db]}`
        `rm out.sql.bz2`
      when 'postgresql'
        puts "Cloning from remote postgresql"
        pg_arguments = "-U #{clone[:prod_user]} #{clone[:prod_db]}"
        # REMOTE
        `ssh #{clone[:db_host]} "echo 'localhost:5432:#{clone[:prod_db]}:#{clone[:prod_user]}:#{clone[:prod_pass]}' > ~/.pgpass; chmod 0600 ~/.pgpass"`
        `ssh #{clone[:db_host]} "rm /tmp/out.sql.bz2; pg_dump #{pg_arguments} > /tmp/out.sql; bzip2 /tmp/out.sql"`
        `ssh #{clone[:db_host]} "rm ~/.pgpass"`
        `scp #{clone[:db_host]}:/tmp/out.sql.bz2 .`

        # LOCAL
        pg_arguments = "-U #{clone[:dev_user]} -h localhost #{clone[:dev_db]}"
        `echo 'localhost:5432:#{clone[:dev_db]}:#{clone[:dev_user]}:#{clone[:dev_pass]}' > ~/.pgpass; chmod 0600 ~/.pgpass`
        `bzcat out.sql.bz2 | sed 's/#{clone[:remote_owner]}/#{clone[:local_owner]}/g' | psql #{pg_arguments}`
        `rm out.sql.bz2`
        `rm ~/.pgpass`
      else
        puts "Please configure clone[:rdbms] to connect to a remote host"
      end
    end
  end
end