I've used the following rake task for a number of years and it's worked well for me. The local:db:abort_if_active_connections dependency isn't strictly necessary but it's nice as otherwise the backup fails since you can't drop a database currently in use.
You'll want to tweak the local:backup:production task's system commands to be whatever you need to get a copy of the database. Then you can just run:
bin/rake local:backup:production
lib/tasks/local/backup.rake
namespace :local do
namespace :backup do
desc 'Backup production and restore to development'
task :production => ['production:db']
namespace :production do
desc 'Backup production database and restore to development'
task :db => ['local:db:abort_if_active_connections', 'db:drop', 'db:create'] do
config = ActiveRecord::Base.configurations[Rails.env]
gz_file = "#{ActiveRecord::Base.configurations['production']['database']}.gz"
puts "Copying production database to temporary file on this machine..."
system "scp [email protected]:/tmp/#{gz_file} /tmp/#{gz_file}"
puts "Recreating local database..."
system "gunzip -c /tmp/#{gz_file} | psql #{config['database']}"
puts "Removing temporary file..."
File.unlink "/tmp/#{gz_file}"
end
end
end
end
lib/tasks/local.rake
namespace :local do
namespace :db do
task :abort_if_active_connections => ['db:load_config'] do
config = ActiveRecord::Base.configurations[Rails.env]
ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
version = ActiveRecord::Base.connection.send(:postgresql_version)
if version >= 90200
pid = 'pid'
else
pid = 'procpid'
end
connections = ActiveRecord::Base.connection.select_all("SELECT * FROM pg_stat_activity WHERE pg_stat_activity.datname = '#{config['database']}' AND #{pid} <> #{$$}")
unless connections.empty?
puts
puts "There are active connections to the database '#{config['database']}':"
puts
puts "%-7s %-20s %-16s %-20s %s" % %w[pid usename client_addr application_name backend_start]
connections.each do |c|
puts "%-7s %-20s %-16s %-20s %s" % [c[pid], c['usename'], c['client_addr'], c['application_name'], c['backend_start']]
end
puts
exit 1
end
ActiveRecord::Base.clear_all_connections!
end
end
end
--cleanoption