如何使用Capistrano备份EC2上部署的Rails项目PostgreSQL数据库并保存至本地

Hey there! Let's tackle your two questions step by step—first manually backing up your PostgreSQL database on EC2, then automating the process with Capistrano to pull backups straight to your local machine.

Part 1: Manual Backup of PostgreSQL on EC2 #

If you want to run a one-off backup without Capistrano, follow these steps:

1. SSH into your EC2 instance
   Use your key pair to connect:

   ssh -i /path/to/your-key-pair.pem ec2-user@your-ec2-public-ip
   

   (Note: Replace ec2-user with your instance's default user if it's different—like ubuntu for Ubuntu instances.)

2. Navigate to your Rails project directory
   For example:

   cd /var/www/your-rails-app-name
   

3. Generate the database backup
   You can use pg_dump directly, and leverage Rails' environment variables to avoid hardcoding credentials:

   bundle exec pg_dump -h localhost -U $(rails runner "puts ENV['DATABASE_USER']") $(rails runner "puts ENV['DATABASE_NAME']") > db_backup_$(date +%Y%m%d_%H%M%S).sql
   

   Alternatively, if you know your DB name and user, simplify it:

   pg_dump -U your-db-username -d your-db-name > db_backup_$(date +%Y%m%d_%H%M%S).sql
   

   This creates a timestamped SQL file so you can easily track backups.

4. Download the backup to your local machine
   Open a new terminal window (don't close the SSH connection) and use scp:

   scp -i /path/to/your-key-pair.pem ec2-user@your-ec2-public-ip:/var/www/your-rails-app-name/db_backup_20240520_143000.sql ./local-backups/
   

   (Replace the filename with the actual one generated on EC2.)

Part 2: Automate Backup with Capistrano #

Capistrano makes this process repeatable and hands-off. Here's how to set it up:

Step 1: Add a Custom Capistrano Task #

Create a new task file in your Rails project at lib/capistrano/tasks/database_backup.rake with the following code:

namespace :db do
  desc "Backup PostgreSQL database from EC2 and download to local machine"
  task :backup do
    on roles(:app) do |host|
      # Define backup file names with timestamp
      timestamp = Time.now.strftime("%Y%m%d_%H%M%S")
      backup_filename = "#{fetch(:application)}_db_backup_#{timestamp}.sql"
      remote_backup_path = "#{release_path}/#{backup_filename}"
      local_backup_dir = "./db/backups"
      local_backup_path = "#{local_backup_dir}/#{backup_filename}"

      # 1. Generate backup on EC2 server
      execute :bundle, :exec, :pg_dump,
        "-h #{fetch(:db_host, 'localhost')}",
        "-U #{fetch(:db_user)}",
        "#{fetch(:db_name)}",
        "> #{remote_backup_path}"

      # 2. Create local backup directory if it doesn't exist
      run_locally do
        execute :mkdir, "-p", local_backup_dir
      end

      # 3. Download backup to local machine
      download!(remote_backup_path, local_backup_path)

      # 4. Optional: Delete the backup file from EC2 to save space
      execute :rm, remote_backup_path

      puts "✅ Backup successful! File saved to: #{local_backup_path}"
    end
  end
end

Step 2: Configure Database Variables #

In your config/deploy.rb file, add these variables to match your Rails database configuration:

# Database credentials for backup task
set :db_host, "localhost" # Adjust if your DB is on a separate server
set :db_user, "your-db-username" # Same as in config/database.yml
set :db_name, "#{fetch(:application)}_#{fetch(:stage)}" # e.g., myapp_production

Step 3: Run the Capistrano Backup Task #

From your local machine, run the task with your deployment stage (e.g., production):

cap production db:backup

That's it! Capistrano will SSH into your EC2 instance, generate the backup, pull it down to your local db/backups directory, and clean up the remote file (if you kept that step).

Important Notes #

• Ensure your EC2 instance's user has permission to run pg_dump—you might need to set up a .pgpass file in the user's home directory to avoid password prompts, or update pg_hba.conf to allow trusted local connections.
• If pg_dump isn't installed on your EC2 instance, install it:
  • For Amazon Linux: sudo yum install postgresql
  • For Ubuntu: sudo apt-get install postgresql-client

内容的提问来源于stack exchange，提问作者Hiệp Hồ