Skip to content

Instantly share code, notes, and snippets.

@hopsoft
Last active May 22, 2024 22:53
Show Gist options
  • Save hopsoft/56ba6f55fe48ad7f8b90 to your computer and use it in GitHub Desktop.
Save hopsoft/56ba6f55fe48ad7f8b90 to your computer and use it in GitHub Desktop.
Rails rake tasks for dump & restore of PostgreSQL databases
# 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
@cschulte22
Copy link

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:

  1. Add the -R flag to ls, 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
  1. 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.

@ngelx
Copy link

ngelx commented Jan 8, 2018

NOTE Be aware that if PG use OIDs, -o should be inserted in the pg_dump cmd options. *Doc

@techpeace
Copy link

techpeace commented Feb 3, 2018

Thanks to @hopsoft, @joelvh, et al!

@moisesnarvaez
Copy link

This is awesome, thank you all!

@khalilgharbaoui
Copy link

khalilgharbaoui commented Feb 3, 2019

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

@bhujelaayushgc
Copy link

@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?

@matiasmasca
Copy link

@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();

@Subtletree
Copy link

Awesome team effort guys thanks!

Using version from @khalilgharbaoui

@Azzawie
Copy link

Azzawie commented Jun 27, 2020

Will this work only for Postgres?

@tcd
Copy link

tcd commented Jul 24, 2020

@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.

@marcelo-soto
Copy link

In Rails 6.1 I had to change parent_name for module_parent_name.

@henriqueutsch
Copy link

# 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

@farlin
Copy link

farlin commented May 9, 2022

You guys are the real MVP

@amit
Copy link

amit commented Oct 5, 2022

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

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