Database Backups Before a Capistrano Deploy

A deploy should make a known change to an application, not gamble with the data that application depends on. A small Capistrano task can create a timestamped database dump immediately before each release, giving the team a practical rollback aid when a migration, configuration change, or rushed Friday afternoon release goes wrong.

The useful approach is simple: keep backups outside the current release directory, make failures stop the deployment, and test that the dump can actually be restored. This pattern suits a Rails application, a PHP service, or any other project managed through Capistrano and SSHKit.

Approach	Strength	Risk	Best use
Local dump on the application server	Fast and inexpensive	Lost with the server	Short-term rollback
Compressed dump in shared storage	Survives release cleanup	Uses disk space	Routine pre-deploy backups
Backup to object storage	Better disaster recovery	Needs credentials and network access	Production systems
Managed database snapshot	Consistent and automated	Provider-specific restore process	Critical production data

Choose The Backup Boundary

A pre-deploy backup should run after the server is reachable but before database migrations or application code changes begin. Backing up after the migration defeats much of the purpose: an incompatible schema may already have altered or removed data.

The task should normally execute on the database host. If PostgreSQL or MySQL runs on a separate machine, the application server can invoke the client remotely, but the dump file should be written to storage with enough space and suitable permissions. A Sydney production server and a database hosted in another Australian region may have different latency and firewall rules, so test the command using the same network path that the deploy will use.

There is no need to back up every release asset. The database dump is the focus, while uploaded files, environment configuration, and encryption keys require their own recovery plan. A dump without the matching credentials or user-uploaded documents may still leave the application unable to restart cleanly.

Keep Backups Outside Releases

Capistrano replaces releases and prunes old directories. A file written beneath current or a numbered release directory can disappear during cleanup, so use shared/db_backups or a dedicated mount instead. The directory should be writable by the deploy user and protected from ordinary web access.

A timestamp in UTC avoids confusion when daylight saving changes between AEST and AEDT. That matters for teams operating between Melbourne, Sydney, and Brisbane, where people may describe an incident as “three o’clock” while servers and logs use different offsets. UTC filenames also sort reliably when a deploy happens close to midnight.

For example, production configuration can define:

# config/deploy/production.rb
set :db_name, "my_app_production"
set :db_user, "deploy_backup"
set :db_backup_dir, -> { "#{shared_path}/db_backups" }
set :db_backup_keep, 14

Keep passwords out of the task and out of Git. PostgreSQL can read credentials from a protected .pgpass file, while MySQL can use a client option file owned by the relevant system user. Environment variables supplied by the service manager are another option, provided the process and shell configuration do not expose them in logs.

Build The Capistrano Task

The following example uses PostgreSQL’s custom dump format. It creates the directory, writes a compressed dump, checks that the file is non-empty, and removes older local copies. capture(:hostname) makes the filename useful when several application servers share a backup location.

# lib/capistrano/tasks/database_backup.rake
namespace :db do
  task :backup do
    on primary(fetch(:migration_role, :db)) do
      timestamp = Time.now.utc.strftime("%Y%m%dT%H%M%SZ")
      host = capture(:hostname).strip
      directory = fetch(:db_backup_dir)
      filename = "#{fetch(:db_name)}-#{host}-#{timestamp}.dump"
      backup_file = "#{directory}/#{filename}"

      execute :mkdir, "-p", directory

      execute :pg_dump,
              "-Fc",
              "-U", fetch(:db_user),
              "-f", backup_file,
              fetch(:db_name)

      execute :test, "-s", backup_file
      info "Database backup created: #{backup_file}"

      keep = fetch(:db_backup_keep, 14)
      execute :sh, "-c",
              "find #{directory} -type f -name '*.dump' " \
              "-printf '%T@ %p\\n' | sort -nr | tail -n +#{keep + 1} " \
              "| cut -d' ' -f2- | xargs -r rm --"
    end
  end
end

The primary helper prevents every application host from creating the same dump. If the project does not define a :db role, use on roles(:app).first or configure a dedicated database role in the stage file. The exact choice depends on how the servers are grouped.

Shell commands deserve care. Paths and database names should come from trusted deployment configuration, not request parameters. For MySQL, replace pg_dump with mysqldump --single-transaction --routines, and redirect its output to the backup path. In an existing codebase, Aaron O Rosen’s site is a useful example of the kind of practical development notes that can help when adapting deployment scripts to a particular stack.

Make Failure Visible

A backup command must return a non-zero status when it fails. Capistrano and SSHKit will then stop the deployment before the migration starts. Do not append || true, hide stderr, or treat a missing dump as a warning unless there is a separate operational reason to proceed.

The test -s check catches common failures where the database client exits unexpectedly or produces an empty file. It does not prove that the dump is valid, so a scheduled restore test should complement the pre-deploy hook. Restore into a disposable database, run a basic query, and check that expected tables and row counts are present.

A production backup is also a security asset. Database exports often contain personal information, payment-related records, email addresses, and session data. Set restrictive permissions, use encrypted disks or object storage, and restrict the deploy user’s access. Australian organisations may also need to consider the Privacy Act, contractual retention rules, and whether a cloud region matches their data residency requirements.

Attach It Before Migrations

For Capistrano 3, a custom task can be inserted before the migration task in the deployment flow:

# config/deploy.rb
before "deploy:migrate", "db:backup"

This ordering creates the dump after the new release has been uploaded but before migrations alter the production schema. If the application has a release hook that performs data changes earlier, attach the backup before that hook instead. Review the complete task graph with a staging deploy rather than assuming the familiar names represent the whole process.

A safer rollout is to deploy the task to staging, inspect the generated file, restore it, and then run a production deployment with an intentionally small change. Teams in Perth or Darwin may have a narrower support window when the rest of the engineering group is on the east coast, so an automated backup and a clear abort condition are especially valuable outside normal overlap hours.

When a backup fails, the operator should see the database client’s error and the deployment should remain on the old release. That behaviour is preferable to a green deploy followed by an incomplete migration and a frantic hunt through shell history.

Set Practical Retention Rules

Retention should reflect recovery needs rather than arbitrary file counts. Keeping fourteen local dumps may cover two weeks of releases, while longer-term copies belong in encrypted object storage or the database provider’s snapshot system. A busy service could create many backups in a short period, so a time-based policy may be more predictable than retaining a fixed number.

Name files with the database, host, and UTC timestamp. Avoid placing passwords, customer details, or full connection strings in filenames and deployment output. Log the backup path and size, but be cautious about logging command arguments because database clients may expose credentials when invoked incorrectly.

These practices keep the task understandable and reduce the chance that a “no worries” deployment hides a real recovery gap. The Coding Journal style of documenting errors and workarounds is a good model for recording the commands, assumptions, and restore procedure alongside the deployment code.

Recommended Operating Rules

A small set of rules keeps database backup automation dependable:

• Run the dump on the database host or through a tested private network path.
• Store backups in shared storage or encrypted object storage, never in a release directory.
• Stop the deploy when the dump fails or the resulting file is empty.
• Test a restore regularly with the same credentials and database version used in production.
• Keep secrets out of Rake files, Git history, command output, and backup filenames.

Treat the task as part of the release system rather than as a convenient shell shortcut. Add monitoring for backup age and storage capacity, document the restore command, and assign someone responsibility for checking that the process still works after database upgrades.

Commit the task with the deployment configuration, run it against staging, and perform a real restore before relying on it during a production incident. A backup made before every Capistrano deploy is valuable only when the team can locate it, trust it, and use it quickly.