-
-
Save hopsoft/56ba6f55fe48ad7f8b90 to your computer and use it in GitHub Desktop.
# 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 |
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... ;-)
The db:dumps
task prints the directory listing at the top level db/backups
directory, which only lists the environment(s) since the dumps are now placed into subdirectories based on the environment. Can be fixed in one of two ways:
- Add the
-R
flag tols
, which recursively lists the contents of the subdirectories:
task dumps: :environment do
backup_dir = backup_directory
puts "#{backup_dir}"
system "/bin/ls -ltR #{backup_dir}"
end
- Specify the environment when calling
backup_directory
to just get a list of the dumps for the current environment:
task dumps: :environment do
backup_dir = backup_directory(Rails.env)
puts "#{backup_dir}"
system "/bin/ls -lt #{backup_dir}"
end
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.
NOTE Be aware that if PG use OIDs, -o
should be inserted in the pg_dump cmd options. *Doc
This is awesome, thank you all!
Made a fork that combines @joelvh's latest fixes and @e12e's benefits.
Also, added some syntax fixes. Using &&
instead of and
for higher operator precedence.
And removing the else nil
from the case statements as those will return nil
if none of the cases match by default.
This is my fork:
https://gist.github.com/khalilgharbaoui/96582e28ee47e3bc0839f2a1fe95dd18
@e12e
I have a question regarding restoring the Database. Creating a back up is straight forward and simple. However, when it comes to restoring the database, I keep running into Connection issue. The restore task involves dropping and creating the database. But it fails with:
ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR: database "database" is being accessed by other users DETAIL: There is 1 other session using the database.
How would I solve this issue? Is there a way to do this without stopping the rails server?
@e12e
I have a question regarding restoring the Database. Creating a back up is straight forward and simple. However, when it comes to restoring the database, I keep running into Connection issue. The restore task involves dropping and creating the database. But it fails with:
ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR: database "database" is being accessed by other users DETAIL: There is 1 other session using the database.
How would I solve this issue? Is there a way to do this without stopping the rails server?
You must run this query in the pg console before, in order close all connections:
> SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'Your_Data_Base_Name_Here'
AND pid <> pg_backend_pid();
Awesome team effort guys thanks!
Using version from @khalilgharbaoui
Will this work only for Postgres?
@MwMaki
Yes, pg_dump
and pg_restore
are specific to Postgres.
You'd probably want to take a look at mysqldump and mysqlimport for MySQL (and maybe for MariaDB).
Not sure about SQL Server and others.
In Rails 6.1 I had to change parent_name
for module_parent_name
.
# apt install postgresql-client
# apt-get -y install bash-completion wget
# wget --no-check-certificate --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
# echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
# apt-get update
# apt-get -y install postgresql-client-12
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 = "PGPASSWORD='postgres' pg_dump -F #{dump_fmt} -v -U postgres -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} .."
Dir.mkdir(backup_dir)
end
backup_dir
end
def with_config
yield Rails.application.class.module_parent_name.underscore,
ActiveRecord::Base.connection_config[:host],
ActiveRecord::Base.connection_config[:database]
end
end
You guys are the real MVP
This is broken for rails 7.x because ActiveRecord::Base.connection_config interface is now changed. Here is a link to my gist which handles newer rails interface:
https://gist.github.com/amit/45e750edde94b70431f5d42caadee423
Thanks for all the great help!
Combined the above scripts and forked gists with these enhancements:
dump
into environment-named folder to differentiate where dumps came from if you switch between environments (e.g./db/backups/development/*.*
)restore
from SQL format needs to usepsql
sql
,dump
,dir
ortar
instead of the abbreviated argument values that can be confusingdb:dumps
to list recursively with-R
option thanks to @cschulte22pg_dump
to include-o
option thanks to @ngelxMaintaining for here: https://gist.github.com/joelvh/f50b8462611573cf9015e17d491a8a92