Retrieve and convert PostgreSQL database to SQLite database (with ssh)

database.rake

require 'fileutils'

namespace :db do
  desc 'pull the production PostgreSQL database into the development SQLite'
  task :pull do
    Rake::Task['db:download_pg_dump'].invoke
    Rake::Task['db:optimze_pg_dump_for_sqlite'].invoke
    Rake::Task['db:recreate_with_dump'].invoke
  end

  desc 'download the pg_dump content into tmp/dump.sql'
  task :download_pg_dump do
    config = Rails.application.config.database_configuration

    abort "Missing production database config" if config['production'].blank?

    dev  = config['development']
    prod = config['production']

    abort "Development db is not sqlite3" unless dev['adapter'] =~ /sqlite3/
    abort "Production db is not postgresql" unless prod['adapter'] =~ /postgresql/
    abort "Missing ssh host" if prod['ssh_host'].blank?
    abort "Missing database name" if prod['database'].blank?

    # remove the old one
    if File.exists?(pg_dump_file_path)
      File.delete(pg_dump_file_path)
    end

    cmd  = "ssh -C "
    cmd << "#{prod['ssh_user']}@" if prod['ssh_user'].present?
    cmd << "#{prod['ssh_host']} "
    cmd << "PGPASSWORD=#{prod['password']} "
    cmd << "pg_dump --data-only --inserts "
    cmd << "--username=#{prod['username']} #{prod['database']} > "
    cmd << pg_dump_file_path

    system `#{cmd}`
  end

  desc 'remove unused statements and optimze sql for SQLite'
  task :optimze_pg_dump_for_sqlite do
    result = []
    lines = File.readlines(pg_dump_file_path)
    @version = 0
    lines.each do | line |
      next if line =~ /SELECT pg_catalog.setval/  # sequence value's
      next if line =~ /SET /                      # postgres specific config
      next if line =~ /--/                        # comment

      if line =~ /INSERT INTO schema_migrations/
        @version = line.match(/INSERT INTO schema_migrations VALUES \('([\d]*)/)[1]
      end

      # replace true and false for 't' and 'f'
      line.gsub!("true","'t'")
      line.gsub!("false","'f'")
      result << line
    end

    File.open(pg_dump_file_path, "w") do |f|
      # Add BEGIN and END so we add it to 1 transaction. Increase speed!
      f.puts("BEGIN;")
      result.each{|line| f.puts(line) unless line.blank?}
      f.puts("END;")
    end
  end

  desc 'backup development.sqlite3 and create a new one with the dumped data'
  task :recreate_with_dump do
    # sqlite so backup
    database = Rails.configuration.database_configuration['development']['database']
    database_path = File.expand_path("#{Rails.root}/#{database}")
    # remove old backup
    if File.exists?(database_path + '.backup')
      File.delete(database_path + '.backup')
    end
    # copy current for backup
    FileUtils.cp database_path, database_path + '.backup' if File.exists?(database_path)

    # dropping and re-creating db
    ENV['VERSION'] = @version
    Rake::Task['db:drop'].invoke
    Rake::Task["db:migrate"].invoke

    puts "migrated to version: #{@version}"
    puts "importing..."
    # remove migration info
    system `sqlite3 #{database_path} "delete from schema_migrations;"`
    # import dump.sql
    system `sqlite3 #{database_path} ".read #{pg_dump_file_path}"`

    puts "DONE!"
    puts "NOTE: you're now migrated to version #{@version}. Please run db:migrate to apply newer migrations"
  end

  def pg_dump_file_path
    File.expand_path("#{Rails.root}/tmp/dump.sql")
  end
end

database.yml

development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

production:
  ssh_user: root                  # <-- add username for ssh
  ssh_host: productionserver.com  # <-- add hostname for ssh
  adapter: postgresql
  host: localhost
  port: 5432
  username: db_user
  password: db_pass
  database: db_name
  schema_search_path: public
  encoding: utf8
  template: template0