Backup DB and store on Google Drive

Backup DB is easy, but send backup file to Google Drive is another story. You should enable Drive API in your account settings and also create service account, and even then you cannot upload file directly to your drive – you should upload to service account and then grant permissions for your account. Maybe there is an easier way but I haven`t found it yet:

# lib/tasks/server_db.rake
namespace :server_db do
  desc 'Backup DB and upload to Google Drive'
  task :backup do
    pg_dump = 'pg_dump --no-acl --no-owner -U myproject_deploy myproject_production'
    timestamp = Time.now.strftime('%Y-%m-%d-%H%M%S')
    archive_path = "#{Rails.root}/db/myproject_production_#{timestamp}.sql.bz2"
    `cd /var/www/myproject/current && #{pg_dump} | bzip2 - - > #{archive_path}`

    require 'google/apis/drive_v2'
    ENV['GOOGLE_APPLICATION_CREDENTIALS'] = "#{Rails.root}/config/google_api_credentials.json"
    drive = Google::Apis::DriveV2::DriveService.new
    drive.authorization = Google::Auth.get_application_default([Google::Apis::DriveV2::AUTH_DRIVE_FILE])

    metadata = {title: File.basename(archive_path, '.sql.bz2')}
    file = drive.insert_file(metadata, upload_source: archive_path, content_type: 'application/x-bzip2')

    perm_id = drive.get_permission_id_for_email('myproject@gmail.com')
    perm = Google::Apis::DriveV2::Permission.new(role: 'writer', id: perm_id.id, type: 'user')
    drive.insert_permission(file.id, perm)

    FileUtils.remove_file(archive_path)
  end
end

# Gemfile
gem 'google-api-client', require: false

// config/google_api_credentials.json
{
  "type": "service_account",
  "project_id": "myproject",
  "private_key_id": "20d9dae73987xxxxxxxxxxxx",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANBgxxxxxxxxxxx==\n-----END PRIVATE KEY-----\n",
  "client_email": "myproject-xxxxxxxx@myproject.iam.gserviceaccount.com",
  "client_id": "103720xxxxxxxxxxx",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/myproject-xxxxxx%40myproject.iam.gserviceaccount.com"
}

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

Also there is another idea of where to store DB dumps – in git repo! Because it stores only diffs so repo with bare dump.sql file will grow not so fast, and you can easily see the daily differences (if in your dump.sql file each db row is on its own line), and you can easily get free private git repo on gitlab.com. But this is a different story.