Skip to content

Instantly share code, notes, and snippets.

@zhiyao
Created October 30, 2012 17:36
Show Gist options
  • Save zhiyao/3981752 to your computer and use it in GitHub Desktop.
Save zhiyao/3981752 to your computer and use it in GitHub Desktop.
capistrano postgresql backup, import and export to production server, reference mledom.blogspot.com/2009/11/capistrano-sync-production-to.html
require "bundler/capistrano"
namespace :db do
require 'yaml'
desc "Copy the remote production database to the local development database NOTE: postgreSQL specific"
task :pg_backup_production, :roles => :db, :only => { :primary => true } do
# First lets get the remote database config file so that we can read in the database settings
tmp_db_yml = "tmp/database.yml"
get("#{shared_path}/config/database.yml", tmp_db_yml)
# load the production settings within the database file
db = YAML::load_file("tmp/database.yml")["production"]
run_locally("rm #{tmp_db_yml}")
filename = "#{application}_production.dump.#{Time.now.to_i}.sql.bz2"
file = "/tmp/#{filename}"
on_rollback {
run "rm #{file}"
run_locally("rm #{tmp_db_yml}")
}
run "pg_dump --clean --no-owner --no-privileges -U#{db['username']} -h localhost #{db['database']} | bzip2 > #{file}" do |ch, stream, out|
ch.send_data "#{db['password']}\n" if out =~ /^Password:/
puts out
end
run_locally "mkdir -p -v '#{File.dirname(__FILE__)}/../backups/'"
get file, "backups/#{filename}"
run "rm #{file}"
end
desc "Copy the latest backup to the local development database NOTE: postgreSQL specific"
task :pg_import_backup do
filename = `ls -tr backups | tail -n 1`.chomp
if filename.empty?
logger.important "No backups found"
else
ddb = YAML::load_file("config/database.yml")["development"]
logger.debug "Loading backups/#{filename} into local development database"
ENV['PGPASSWORD'] = ddb['password']
run_locally "bzip2 -cd backups/#{filename} | psql -U #{ddb['username']} -d #{ddb['database']}"
logger.debug "command finished"
end
end
desc "Backup the remote production database and import it to the local development database NOTE: postgreSQL specific"
task :pg_backup_and_import_from_production do
pg_backup_production
pg_import_backup
end
desc "Backup the local development database to the backup folder NOTE: postgreSQL specific"
task :pg_backup_local do
ddb = YAML::load_file("config/database.yml")["development"]
filename = "backups/#{application}_local.dump.#{Time.now.to_i}.sql.bz2"
ENV['PGPASSWORD'] = ddb['password']
logger.debug "Backing up #{filename} on local development"
run_locally "pg_dump --clean --no-owner --no-privileges -U #{ddb['username']} #{ddb['database']} | bzip2 > #{filename}" do |ch, stream, out|
puts out
end
logger.debug "command finished"
end
desc "Copy the lastest backup database and push to the remote production database"
task :pg_export_backup, :roles => :db, :only => { :primary => true } do
filename = `ls -tr backups | tail -n 1`.chomp
file = "backups/#{filename}"
# First lets get the remote database config file so that we can read in the database settings
tmp_db_yml = "tmp/database.yml"
get("#{shared_path}/config/database.yml", tmp_db_yml)
# load the production settings within the database file
db = YAML::load_file("tmp/database.yml")["production"]
run_locally("rm #{tmp_db_yml}")
on_rollback {
run_locally("rm #{tmp_db_yml}")
run("rm /tmp/#{filename}")
}
upload file, "/tmp/#{filename}"
run "bzip2 -cd /tmp/#{filename} | psql -U #{db['username']} -d #{db['database']} -h #{db['host']}" do |ch, stream, out|
ch.send_data "#{db['password']}\n" if out =~ /^Password.*:/
puts out
end
run("rm /tmp/#{filename}")
end
desc "Backup the local development database and export it to the production database NOTE: postgreSQL specific"
task :pg_backup_and_export_to_production do
pg_backup_local
pg_export_backup
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment