Rails rake tasks for dump & restore of PostgreSQL databases

db.rake

# 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

usage.sh

# 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

pg:dump returns pg_dump: [archiver (db)] connection to database "mentalia_dev" failed: FATAL: role "--verbose" does not exist

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 pg:restore[date]

Big digital hug this is going be so useful. License info for proper attribution?

Thanks to @bengalamx and @hopsoft for starting this.

Here is an enhanced version; it provides

• support for the four types of pg_dump formats, with corresponding suffixes
• uses the correct format according to the suffix of the file on the restore
• a db:list task to show the current dumps
• enhance db:restore to match on any pattern, not just the date. This allows a dump from one db to be stored into another db by setting RAILS_ENV before running the rake command.
• detects when the pattern matches no files or too many files.
• automatically creates the backup directory db/backups if needed

My part of this is freely donated to the Rails community-at-large.

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} .."
          Dir.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

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 mina tasks

I made a fork where you can dump a given table by specifying its name: https://gist.github.com/kofronpi/37130f5ed670465b1fe2d170f754f8c6

Hi, I got an error when trying to restore the db using rake db:restore

rake aborted!
Errno::ENOENT: No such file or directory - pg_restore

Ruby 2.3.1
Rails 4.0.9

Hi! This is an update to AKS's script for 2017. Rails 5.0.1 & Ruby ruby-2.3.3
Changes:

1. Dir.mkdir_p was replaced by FileUtils.mkdir_p

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.
To dump your local database, in your development.yml file add:

host: localhost

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 set, ls, and sed to offer full functionality, but it will fail nicely if not.
Verified with Rails 5.1.3 & Postgres 9.2.18

if somebody looking for simple solution for backups: https://github.com/kirillshevch/pg_drive_backup

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 use psql
• Can specify format as sql, dump, dir or tar instead of the abbreviated argument values that can be confusing
• Use environment variables as arguments
• Fixed db:dumps to list recursively with -R option thanks to @cschulte22
• Fixed pg_dump to include -o option thanks to @ngelx

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

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:

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

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

Thanks to @hopsoft, @joelvh, et al!

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.