Database backup and restore strategies during production deploys

This card describes two different setups that help restore data in case of a data corruption incident during a deployment.

Databases < 10 GB

For small databases, we dump the database before running migrations using a gem executable called dumple Show archive.org snapshot . You will usually find the following Capistrano tasks in your project for this purpose:

config/deploy.rb

Copy# [...]
before 'deploy:migrate', 'db:dump' unless ENV.key?('SKIP_DUMP')
# [...]
after 'deploy:published', 'db:show_dump_usage'
# [...]

lib/capistrano/tasks/db.rake

Copynamespace :db do
  desc 'Do a dump of the DB on the remote machine using dumple'
  task :dump do
    on primary :db do
      within current_path do
        execute :dumple, '--fail-gently', '--compress=zstd:3', fetch(:rails_env, 'production')
      end
    end
  end

  desc 'Show usage of ~/dumps/ on remote host'
  task :show_dump_usage do
    on primary :db do
      info capture :dumple, '-i'
    end
  end
end

In case of a data corruption incident, you can deploy a maintenance page, restore the database, and deploy a previous release or roll back using Capistrano.

Databases > 10 GB

For larger databases, we often enable Point-in-Time Recovery (PITR). With this setup, we can omit the tasks described above to speed up deployments.

If your project uses a makandra dedicated databases cluster, the following applies:

1. You can request a database dump for any point in time up to seven days in the past without interrupting the production environment (the dump is stored in the deploy user’s home directory).
2. You can request a database restore to any point in time up to seven days in the past.

Creating a dump depends on the database size and the time of day. Every night, a full backup is created, and all subsequent changes have to be applied sequentially to obtain a dump for a specific point in time.

As an example to give a rough idea of restore durations, consider a 27 GB database restored to 18:00:

• Apply all transactions to the nightly base backup (≈ 40 min)
• Dump the database to a file (≈ 10 min)
• Restore the dump to the database (≈ 15 min)