# lib/tasks/db.rake | |
namespace :db do | |
desc "Dumps the database to db/APP_NAME.dump" | |
task :dump => :environment do | |
cmd = nil | |
with_config do |app, host, db, user| | |
cmd = "pg_dump --host #{host} --username #{user} --verbose --clean --no-owner --no-acl --format=c #{db} > #{Rails.root}/db/#{app}.dump" | |
end | |
puts cmd | |
exec cmd | |
end | |
desc "Restores the database dump at db/APP_NAME.dump." | |
task :restore => :environment do | |
cmd = nil | |
with_config do |app, host, db, user| | |
cmd = "pg_restore --verbose --host #{host} --username #{user} --clean --no-owner --no-acl --dbname #{db} #{Rails.root}/db/#{app}.dump" | |
end | |
Rake::Task["db:drop"].invoke | |
Rake::Task["db:create"].invoke | |
puts cmd | |
exec cmd | |
end | |
private | |
def with_config | |
yield Rails.application.class.parent_name.underscore, | |
ActiveRecord::Base.connection_config[:host], | |
ActiveRecord::Base.connection_config[:database], | |
ActiveRecord::Base.connection_config[:username] | |
end | |
end |
# dump the development db | |
rake db:dump | |
# dump the production db | |
RAILS_ENV=production rake db:dump | |
# dump the production db & restore it to the development db | |
RAILS_ENV=production rake db:dump | |
rake db:restore | |
# note: config/database.yml is used for database configuration, | |
# but you will be prompted for the database user's password |
This comment has been minimized.
This comment has been minimized.
This is better: namespace :db do
desc "Dumps the database to backups"
task :dump => :environment do
cmd = nil
with_config do |app, host, db, user|
cmd = "pg_dump -F c -v -h #{host} -d #{db} -f #{Rails.root}/db/backups/#{Time.now.strftime("%Y%m%d%H%M%S")}_#{db}.psql"
end
puts cmd
exec cmd
end
desc "Restores the database from backups"
task :restore, [:date] => :environment do |task,args|
if args.date.present?
cmd = nil
with_config do |app, host, db, user|
cmd = "pg_restore -F c -v -c -C #{Rails.root}/db/backups/#{args.date}_#{db}.psql"
end
Rake::Task["db:drop"].invoke
Rake::Task["db:create"].invoke
puts cmd
exec cmd
else
puts 'Please pass a date to the task'
end
end
private
def with_config
yield Rails.application.class.parent_name.underscore,
ActiveRecord::Base.connection_config[:host],
ActiveRecord::Base.connection_config[:database],
ActiveRecord::Base.connection_config[:username]
end
end And so you can keep various backups, and when you want to restore just |
This comment has been minimized.
This comment has been minimized.
Big digital hug this is going be so useful. License info for proper attribution? |
This comment has been minimized.
This comment has been minimized.
Thanks to @bengalamx and @hopsoft for starting this. Here is an enhanced version; it provides
My part of this is freely donated to the Rails community-at-large.
|
This comment has been minimized.
This comment has been minimized.
I've created this rake tasks for myself: def dump_path
Rails.root.join('db/dump.psql').to_path
end
def db_name
config = Rails.configuration.database_configuration[Rails.env]
"postgresql://#{config['username']}:#{config['password']}@127.0.0.1:5432/#{config['database']}"
end
task :db_dump do
system "pg_dump -Fc --no-owner --dbname=#{db_name} > #{dump_path}"
end
task :db_load do
system "pg_restore --clean --no-owner --dbname=#{db_name} #{dump_path}"
end and I use it in the |
This comment has been minimized.
This comment has been minimized.
I made a fork where you can dump a given table by specifying its name: https://gist.github.com/kofronpi/37130f5ed670465b1fe2d170f754f8c6 |
This comment has been minimized.
This comment has been minimized.
Hi, I got an error when trying to restore the db using
Ruby 2.3.1 |
This comment has been minimized.
This comment has been minimized.
Hi! This is an update to AKS's script for 2017. Rails 5.0.1 & Ruby ruby-2.3.3
namespace :db do
desc "Dumps the database to backups"
task :dump => :environment do
dump_fmt = 'c' # or 'p', 't', 'd'
dump_sfx = suffix_for_format dump_fmt
backup_dir = backup_directory true
cmd = nil
with_config do |app, host, db, user|
file_name = Time.now.strftime("%Y%m%d%H%M%S") + "_" + db + '.' + dump_sfx
cmd = "pg_dump -F #{dump_fmt} -v -h #{host} -d #{db} -f #{backup_dir}/#{file_name}"
end
puts cmd
exec cmd
end
desc "Show the existing database backups"
task :list => :environment do
backup_dir = backup_directory
puts "#{backup_dir}"
exec "/bin/ls -lt #{backup_dir}"
end
desc "Restores the database from a backup using PATTERN"
task :restore, [:pat] => :environment do |task,args|
if args.pat.present?
cmd = nil
with_config do |app, host, db, user|
backup_dir = backup_directory
files = Dir.glob("#{backup_dir}/*#{args.pat}*")
case files.size
when 0
puts "No backups found for the pattern '#{args.pat}'"
when 1
file = files.first
fmt = format_for_file file
if fmt.nil?
puts "No recognized dump file suffix: #{file}"
else
cmd = "pg_restore -F #{fmt} -v -c -C #{file}"
end
else
puts "Too many files match the pattern '#{args.pat}':"
puts ' ' + files.join("\n ")
puts "Try a more specific pattern"
end
end
unless cmd.nil?
Rake::Task["db:drop"].invoke
Rake::Task["db:create"].invoke
puts cmd
exec cmd
end
else
puts 'Please pass a pattern to the task'
end
end
private
def suffix_for_format suffix
case suffix
when 'c' then 'dump'
when 'p' then 'sql'
when 't' then 'tar'
when 'd' then 'dir'
else nil
end
end
def format_for_file file
case file
when /\.dump$/ then 'c'
when /\.sql$/ then 'p'
when /\.dir$/ then 'd'
when /\.tar$/ then 't'
else nil
end
end
def backup_directory create=false
backup_dir = "#{Rails.root}/db/backups"
if create and not Dir.exists?(backup_dir)
puts "Creating #{backup_dir} .."
FileUtils.mkdir_p(backup_dir)
end
backup_dir
end
def with_config
yield Rails.application.class.parent_name.underscore,
ActiveRecord::Base.connection_config[:host],
ActiveRecord::Base.connection_config[:database],
ActiveRecord::Base.connection_config[:username]
end
end Notes: Your database.yml file must have a host, database and username in order for this script to work. host: localhost |
This comment has been minimized.
This comment has been minimized.
Here's a mix of a few of the above solutions, with a bit more logging, a prompt for a date (with list of available options) and the username & password included: namespace :db do
desc 'Dump the database to db/<time>._<name>.pgsql_dump'
task dump: :environment do
cmd = nil
f_name = nil
with_config do |_app, host, db, user, pass|
f_name = "#{Rails.root}/db/#{Time.now.strftime('%Y%m%d%H%M%S')}_#{db}.psql_dump"
cmd = "PGPASSWORD='#{pass}' pg_dump -F c -v -h '#{host}' -U '#{user}' -f '#{f_name}' #{db}"
end
puts "Backing up to #{f_name} ..."
puts cmd
exec cmd
end
desc 'Restore the database dump; NOTE: The current database will be dropped!'
task :restore, [:date] => :environment do |_task, args|
cmd = nil
f_name = nil
date = args.date
with_config do |_app, host, db, user, pass|
unless date.present?
STDERR.puts 'ERROR: Please choose a date from the available backup files:'
system("set -o pipefail && ls -1 #{Rails.root}/db/*_#{db}.psql_dump | sed -E 's/.+\\/([0-9]+)_.+/ \\1/'") ||
fail_with_msg("Failed to list files. Check in #{Rails.root}/db/ for your available dumps")
print 'Date: '
date = STDIN.gets.strip
end
f_name = "#{Rails.root}/db/#{date}_#{db}.psql_dump"
cmd = "PGPASSWORD='#{pass}' pg_restore -F c -v -h '#{host}' -U '#{user}' -d #{db} -c -C #{f_name}"
end
puts 'Dropping the current database first...'
Rake::Task['db:drop'].invoke
Rake::Task['db:create'].invoke
puts "Restoring database from #{f_name} ..."
puts cmd
exec cmd
end
private
def with_config
yield Rails.application.class.parent_name.underscore,
ActiveRecord::Base.connection_config[:host],
ActiveRecord::Base.connection_config[:database],
ActiveRecord::Base.connection_config[:username],
ActiveRecord::Base.connection_config[:password]
end
def fail_with_msg(msg, code = 1)
STDERR.puts('ERROR: ' + msg)
exit code
end
end Note that it requires |
This comment has been minimized.
This comment has been minimized.
if somebody looking for simple solution for backups: https://github.com/kirillshevch/pg_drive_backup |
This comment has been minimized.
This comment has been minimized.
Thanks for all the great help! Combined the above scripts and forked gists with these enhancements:
Maintaining for here: https://gist.github.com/joelvh/f50b8462611573cf9015e17d491a8a92 # Original source: https://gist.github.com/hopsoft/56ba6f55fe48ad7f8b90
# Merged with: https://gist.github.com/kofronpi/37130f5ed670465b1fe2d170f754f8c6
#
# Usage:
#
# # dump the development db
# rake db:dump
#
# # dump the db in a specific format
# rake db:dump format=sql
#
# # dump a table (e.g. users table)
# rake db:dump:table table=users
#
# # dump a table in a specific format
# rake db:dump:table table=users format=sql
#
# # list dumps
# rake db:dumps
#
# # dump the production db
# RAILS_ENV=production rake db:dump
#
# # restore db based on a backup file pattern (e.g. timestamp)
# rake db:restore pattern=20170101
#
# # note: config/database.yml is used for database configuration,
# # but you will be prompted for the database user's password
#
# Original source: https://gist.github.com/hopsoft/56ba6f55fe48ad7f8b90
# Merged with: https://gist.github.com/kofronpi/37130f5ed670465b1fe2d170f754f8c6
namespace :db do
desc 'Dumps the database to backups'
task dump: :environment do
dump_fmt = ensure_format(ENV['format'])
dump_sfx = suffix_for_format(dump_fmt)
backup_dir = backup_directory(Rails.env, create: true)
full_path = nil
cmd = nil
with_config do |app, host, db, user|
full_path = "#{backup_dir}/#{Time.now.strftime('%Y%m%d%H%M%S')}_#{db}.#{dump_sfx}"
cmd = "pg_dump -F #{dump_fmt} -v -O -o -U '#{user}' -h '#{host}' -d '#{db}' -f '#{full_path}'"
end
puts cmd
system cmd
puts ''
puts "Dumped to file: #{full_path}"
puts ''
end
namespace :dump do
desc 'Dumps a specific table to backups'
task table: :environment do
table_name = ENV['table']
if table_name.present?
dump_fmt = ensure_format(ENV['format'])
dump_sfx = suffix_for_format(dump_fmt)
backup_dir = backup_directory(Rails.env, create: true)
full_path = nil
cmd = nil
with_config do |app, host, db, user|
full_path = "#{backup_dir}/#{Time.now.strftime('%Y%m%d%H%M%S')}_#{db}.#{table_name.parameterize.underscore}.#{dump_sfx}"
cmd = "pg_dump -F #{dump_fmt} -v -O -o -U '#{user}' -h '#{host}' -d '#{db}' -t '#{table_name}' -f '#{full_path}'"
end
puts cmd
system cmd
puts ''
puts "Dumped to file: #{full_path}"
puts ''
else
puts 'Please specify a table name'
end
end
end
desc 'Show the existing database backups'
task dumps: :environment do
backup_dir = backup_directory
puts "#{backup_dir}"
system "/bin/ls -ltR #{backup_dir}"
end
desc 'Restores the database from a backup using PATTERN'
task restore: :environment do
pattern = ENV['pattern']
if pattern.present?
file = nil
cmd = nil
with_config do |app, host, db, user|
backup_dir = backup_directory
files = Dir.glob("#{backup_dir}/**/*#{pattern}*")
case files.size
when 0
puts "No backups found for the pattern '#{pattern}'"
when 1
file = files.first
fmt = format_for_file file
case fmt
when nil
puts "No recognized dump file suffix: #{file}"
when 'p'
cmd = "psql -U '#{user}' -h '#{host}' -d '#{db}' -f '#{file}'"
else
cmd = "pg_restore -F #{fmt} -v -c -C -U '#{user}' -h '#{host}' -d '#{db}' -f '#{file}'"
end
else
puts "Too many files match the pattern '#{pattern}':"
puts ' ' + files.join("\n ")
puts ''
puts "Try a more specific pattern"
puts ''
end
end
unless cmd.nil?
Rake::Task["db:drop"].invoke
Rake::Task["db:create"].invoke
puts cmd
system cmd
puts ''
puts "Restored from file: #{file}"
puts ''
end
else
puts 'Please specify a file pattern for the backup to restore (e.g. timestamp)'
end
end
private
def ensure_format(format)
return format if %w[c p t d].include?(format)
case format
when 'dump' then 'c'
when 'sql' then 'p'
when 'tar' then 't'
when 'dir' then 'd'
else 'p'
end
end
def suffix_for_format(suffix)
case suffix
when 'c' then 'dump'
when 'p' then 'sql'
when 't' then 'tar'
when 'd' then 'dir'
else nil
end
end
def format_for_file(file)
case file
when /\.dump$/ then 'c'
when /\.sql$/ then 'p'
when /\.dir$/ then 'd'
when /\.tar$/ then 't'
else nil
end
end
def backup_directory(suffix = nil, create: false)
backup_dir = File.join(*[Rails.root, 'db/backups', suffix].compact)
if create and not Dir.exists?(backup_dir)
puts "Creating #{backup_dir} .."
FileUtils.mkdir_p(backup_dir)
end
backup_dir
end
def with_config
yield Rails.application.class.parent_name.underscore,
ActiveRecord::Base.connection_config[:host],
ActiveRecord::Base.connection_config[:database],
ActiveRecord::Base.connection_config[:username]
end
end |
This comment has been minimized.
This comment has been minimized.
Thank you @joelvh for editing together a cleaner solution (and I appreciate the pw prompt - makes it a little less likely to accidentally clobber the prod db on restore). Although, in my current test, I'm actually not prompted for a pw when dumping the dev database, but that might have something to do with my local auth-setup for postgresql. Ed: I've commented out the call to "rake:db:drop/create" - as that requires the user to have create database privileges - and is redundant with pg_restore -c -C (clean/Create). It will work if using something like the "postgres"-user - but rails shouldn't need such high access to the db cluster. I've made a fork with the code from your comment, for easy download: cd lib/tasks
curl -O https://gist.githubusercontent.com/e12e/e0c7d2cc1d30d18c8050b309a43450ac/raw/9194cfe92143167a7a15a92ac7f02605bcb9d753/db.rake
# Have a look at the code, before you start clobbering your db... ;-) |
This comment has been minimized.
This comment has been minimized.
The
Seems like the best solution depends on your use case - if you're just backing up and restoring in a single environment, then option #2 is probably best. |
This comment has been minimized.
This comment has been minimized.
NOTE Be aware that if PG use OIDs, |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This is awesome, thank you all! |
This comment has been minimized.
This comment has been minimized.
Made a fork that combines @joelvh's latest fixes and @e12e's benefits. This is my fork: |
This comment has been minimized.
This comment has been minimized.
@e12e How would I solve this issue? Is there a way to do this without stopping the rails server? |
This comment has been minimized.
This comment has been minimized.
You must run this query in the pg console before, in order close all connections:
|
This comment has been minimized.
This comment has been minimized.
Awesome team effort guys thanks! Using version from @khalilgharbaoui |
This comment has been minimized.
This comment has been minimized.
Will this work only for Postgres? |
This comment has been minimized.
This comment has been minimized.
@MwMaki |
This comment has been minimized.
pg:dump
returnspg_dump: [archiver (db)] connection to database "mentalia_dev" failed: FATAL: role "--verbose" does not exist