Ruby on Rails backup database and store in Git

Store DB in git – why not?

• Git stores only diffs so your repo size will be like your dump size
• You can easily compare your db versions by any git gui tools
• You can store your backups for free on gitlab (or github but paid)

First we need dump db to raw *.sql file and ensure that each record is on the new line and then commit/push to git.

PostgreSQL

namespace :server_db do
  REPO_DIR = '/var/www/myproject/db_backup'

  desc 'Backup DB and commit into git repo'
  task :backup do
    `git clone git@gitlab.com:lllukom/myproject-backup.git #{REPO_DIR}` if !Dir.exist?(REPO_DIR)

    yml = YAML.load_file('config/database.yml')['production']

    `pg_dump --no-privileges --no-owner -U #{yml['username']} #{yml['database']} > #{REPO_DIR}/db.sql`

    `cd #{REPO_DIR} && git add -A && git commit -m 'daily backup' && git push origin master`
  end

  desc 'Restore db from latest dump (grabbed code from capistrano-db-tasks gem)'
  task :restore do
    yml = YAML.load_file('config/database.yml')['production']
    database = yml['database']
    pgpass = ("PGPASSWORD=#{yml['password']}" if yml['password'].present?)
    credentials = " -u #{yml['username']} "
    file = "#{REPO_DIR}/db.sql"
    terminate_connection_sql = "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '#{database}' AND pid <> pg_backend_pid();"
    "#{pgpass} psql -c \"#{terminate_connection_sql};\" #{credentials}; #{pgpass} dropdb #{credentials} #{database}; #{pgpass} createdb #{credentials} #{database}; #{pgpass} psql #{credentials} -d #{database} < #{file}"
  end

end

MySQL

namespace :server_db do
  REPO_DIR = '/var/www/myproject/db_backup'

  desc 'Backup DB and commit into Github repo'
  task :backup do
    if !Dir.exist?(REPO_DIR)
      `git clone git@github.com:lllukom/myproject-backup.git #{REPO_DIR}`
    end

    opts = '--skip-extended-insert --skip-dump-date --lock-tables=false'
    mysql_cmd "mysqldump %{credentials} #{opts} %{database} > #{REPO_DIR}/db.sql"

    `cd #{REPO_DIR} && git add -A && git commit -m 'daily backup' && git push origin master`
  end

  desc 'Restore db from latest dump (grabbed code from capistrano-db-tasks gem)'
  task :restore do
    mysql_cmd "mysql %{credentials} -D %{database} < #{REPO_DIR}/db.sql"
  end

  def mysql_cmd(cmd)
    yml = YAML.load_file('config/database.yml')['production']

    # create this file to avoid warning "Using a password on the command line interface can be insecure"
    file = Tempfile.new('temp-config')
    begin
      file.write <<~CNF
        [client]
        user = #{yml['username']}
        password = #{yml['password']}
        host = #{yml['host'] || 'localhost'}
        port = #{yml['port'] || '3306'}
      CNF
      file.close

      `#{cmd % {credentials: "--defaults-file=#{file.path}", database: yml['database']}}`
    ensure
      file.delete
    end
  end

end

# config/schedule.rb (daily backup with whenever)
every(1.day, at: '02:00') { rake 'server_db:backup' }

Note: if your database dump is bigger than 100mb and you use Github – you should use Git LFS
Note2: you should configure git access to repo from server:

# ~/.ssh/config
Host github.com
  User git
  Hostname github.com
  PreferredAuthentications publickey
  IdentityFile ~/.ssh/git/id_rsa