Skip to content

Instantly share code, notes, and snippets.

@rgo
Created March 1, 2010 12:04
Show Gist options
  • Save rgo/318312 to your computer and use it in GitHub Desktop.
Save rgo/318312 to your computer and use it in GitHub Desktop.
Capistrano: Dump and clone to local database
# Directly copied from eycap-0.5.2 (thanks!)
#
# With these tasks you can:
# - dump your production database and save it in shared_path/db_backups
# - dump your production into your local database (clone_to_local)
#
# Tested and fixed by fjguzman
Capistrano::Configuration.instance(:must_exist).load do
namespace :db do
task :backup_name, :roles => :db, :only => { :primary => true } do
now = Time.now
run "mkdir -p #{shared_path}/db_backups"
backup_time = [now.year,now.month,now.day,now.hour,now.min,now.sec].join('-')
set :backup_file, "#{shared_path}/db_backups/#{environment_database}-snapshot-#{backup_time}.sql"
end
desc "Backup your MySQL or PostgreSQL database to shared_path+/db_backups"
task dump, :roles => :db, :only => {:primary => true} do
backup_name
run("cat #{shared_path}/config/database.yml") { |channel, stream, data| @environment_info = YAML.load(data)[rails_env] }
dbuser = @environment_info['username']
dbpass = @environment_info['password']
environment_database = @environment_info['database']
dbhost = @environment_info['host']
if @environment_info['adapter'] == 'mysql'
#dbhost = environment_dbhost.sub('-master', '') + '-replica' if dbhost != 'localhost' # added for Solo offering, which uses localhost
run "mysqldump --add-drop-table -u #{dbuser} -h #{dbhost} -p #{environment_database} | bzip2 -c > #{backup_file}.bz2" do |ch, stream, out |
ch.send_data "#{dbpass}\n" if out=~ /^Enter password:/
end
else
run "pg_dump -W -c -U #{dbuser} -h #{dbhost} #{environment_database} | bzip2 -c > #{backup_file}.bz2" do |ch, stream, out |
ch.send_data "#{dbpass}\n" if out=~ /^Password:/
end
end
end
desc "Sync your production database to your local workstation"
task :clone_to_local, :roles => :db, :only => {:primary => true} do
backup_name
dump
get "#{backup_file}.bz2", "/tmp/#{application}.sql.bz2"
development_info = YAML.load_file("config/database.yml")['development']
if development_info['adapter'] == 'mysql'
run_str = "bzcat /tmp/#{application}.sql.bz2 | mysql -u #{development_info['username']} --password='#{development_info['password']}' -h #{development_info['host']} #{development_info['database']}"
else
run_str = "PGPASSWORD=#{development_info['password']} bzcat /tmp/#{application}.sql.bz2 | psql -U #{development_info['username']} -h #{development_info['host']} #{development_info['database']}"
end
%x!#{run_str}!
end
end
end
@kostia
Copy link

kostia commented Nov 25, 2013

It only works if the username of the user accessing the machine is the same as the username for Postgres (for peer authentication method).

@kostia
Copy link

kostia commented Nov 25, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment