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

@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