Simple backup and restore tasks for postgres

postgres_backup_restore.rake

require 'highline/import'

def highlight(str)
  "\e[33m" + str + "\e[0m"
end

def run_cmd(cmd)
  say highlight "Executing \"#{cmd}\""
  Timeout::timeout(30) do
    IO.popen(cmd).each{ |line| print line }
  end
end

def database_name
  Rails.configuration.database_configuration[Rails.env]["database"]
end

def database_user
  Rails.configuration.database_configuration[Rails.env]["username"]
end

def system_user
  `whoami`
end

def backup_path
  ENV["BACKUP_PATH"] || @backup_path ||= (
    ask("Enter path to backup file or accept default:") do |q|
      q.default = "db/backup/#{database_name}.sql"
    end
  )
end

namespace :postgres do

  namespace :db do

    desc "Backup database as SQL dump"
    task :backup => :environment do
      say "Backing up database using pg_dump"
      if ENV["BACKUP_PATH"].nil? && File.exist?(backup_path)
        say "File #{backup_path} exists"
        exit unless agree("Continue and overwrite existing file?")
      end
      run_cmd "pg_dump --verbose --clean --no-owner --no-acl #{database_name} > #{backup_path}"
    end

    desc "Restore database from SQL dump"
    task :restore => :environment do
      say "Restoring database from SQL dump"
      run_cmd "psql -d #{database_name} < #{backup_path}"
      if database_user && database_user != system_user
        run_cmd "psql -d #{database_name} -c 'ALTER DATABASE #{database_name} OWNER TO #{database_user}'"
        # Note: use individual statements to assign ownership because REASSIGN
        # OWNED fails due to pg bug #9749 (and seems brittle anyway). More info:
        # - http://www.postgresql.org/message-id/20140416152901.GO5822@eldon.alvh.no-ip.org
        # - http://postgresql.1045698.n5.nabble.com/pgsql-Fix-REASSIGN-OWNED-for-text-search-objects-td5811634.html
        # - http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a41dc73211c9ab579bb2cd87ad7d0a6ecf0806fe
        run_cmd "psql -d #{database_name} -c 'ALTER SCHEMA public OWNER TO #{database_user}'"
        table_names = `psql -At -d #{database_name} -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';"`.chomp.split("\n")
        table_names.each do |table_name|
          run_cmd "psql -d #{database_name} -c 'ALTER TABLE #{table_name} OWNER TO #{database_user}'"
        end
      end
      Rake::Task['db:migrate'].invoke
    end

  end

end