Enable developers to backup their local postgres database

db.rake

namespace :db do
  namespace :backup do
    def ensure_not_production
      if Rails.env.production?
        puts "This task cannot be run in production environment for safety reasons."
        exit 1
      end
    end

    desc "Create a backup of the PostgreSQL database"
    task create: :environment do
      ensure_not_production
      config = Rails.configuration.database_configuration[Rails.env]
      backup_dir = Rails.root.join("db", "backups")
      timestamp = Time.current.strftime("%Y%m%d_%H%M%S")
      backup_file = backup_dir.join("#{config['database']}_#{timestamp}.dump")

      # Create backup directory if it doesn't exist
      FileUtils.mkdir_p(backup_dir)

      # Build the pg_dump command with proper credentials
      cmd = [
        "pg_dump",
        "-h #{config['host'] || 'localhost'}",
        "-p #{config['port'] || 5432}",
        "-U #{config['username']}",
        "-Fc", # Custom format
        "-v",  # Verbose output
        "-f #{backup_file}",
        config["database"]
      ].join(" ")

      # Set PGPASSWORD environment variable
      ENV["PGPASSWORD"] = config["password"]

      puts "Creating backup: #{backup_file}"
      system(cmd)
      ENV["PGPASSWORD"] = nil # Clear password from env

      if $?.success?
        puts "Backup created successfully: #{backup_file}"
      else
        puts "Backup failed with exit code: #{$?.exitstatus}"
      end
    end

    desc "Restore the most recent database backup"
    task restore: :environment do
      ensure_not_production
      config = Rails.configuration.database_configuration[Rails.env]
      backup_dir = Rails.root.join("db", "backups")

      # Find the most recent backup
      latest_backup = Dir.glob(backup_dir.join("#{config['database']}_*.dump"))
                        .sort
                        .last

      if latest_backup.nil?
        puts "No backup found in #{backup_dir}"
        exit 1
      end

      # Build the pg_restore command
      cmd = [
        "pg_restore",
        "-h #{config['host'] || 'localhost'}",
        "-p #{config['port'] || 5432}",
        "-U #{config['username']}",
        "-d #{config['database']}",
        "-v", # Verbose output
        "--clean", # Clean (drop) database objects before recreating
        "--if-exists", # Don't error if objects don't exist
        "--no-owner", # Don't set ownership to match the original database
        latest_backup
      ].join(" ")

      # Set PGPASSWORD environment variable
      ENV["PGPASSWORD"] = config["password"]

      puts "Restoring backup: #{latest_backup}"
      system(cmd)
      ENV["PGPASSWORD"] = nil # Clear password from env

      if $?.success?
        puts "Restore completed successfully"
      else
        puts "Restore failed with exit code: #{$?.exitstatus}"
      end
    end

    desc "List all available backups"
    task list: :environment do
      ensure_not_production
      config = Rails.configuration.database_configuration[Rails.env]
      backup_dir = Rails.root.join("db", "backups")

      if Dir.exist?(backup_dir)
        backups = Dir.glob(backup_dir.join("#{config['database']}_*.dump"))
        if backups.any?
          puts "Available backups:"
          backups.sort.each do |backup|
            size = File.size(backup) / 1024.0 / 1024.0 # Convert to MB
            puts "  #{File.basename(backup)} (#{size.round(2)} MB)"
          end
        else
          puts "No backups found in #{backup_dir}"
        end
      else
        puts "Backup directory does not exist: #{backup_dir}"
      end
    end

    desc "Preview backup SQL content. Optional: specify filename=example.dump"
    task preview: :environment do
      ensure_not_production
      config = Rails.configuration.database_configuration[Rails.env]
      backup_dir = Rails.root.join("db", "backups")

      backup_file = if ENV["filename"]
        file_path = backup_dir.join(ENV["filename"])
        file_path.exist? ? file_path : nil
      else
        # Find the most recent backup
        Dir.glob(backup_dir.join("#{config['database']}_*.dump"))
           .sort
           .last
      end

      if backup_file.nil?
        if ENV["filename"]
          puts "Backup file not found: #{ENV['filename']}"
        else
          puts "No backup found in #{backup_dir}"
        end
        exit 1
      end

      puts "Using backup file: #{backup_file}"

      sql_file = backup_file.sub(".dump", ".sql")

      # Build the pg_restore command
      cmd = [
        "pg_restore",
        "-v",
        "-f #{sql_file}",
        backup_file
      ].join(" ")

      puts "Converting backup to SQL: #{sql_file}"
      system(cmd)

      if $?.success?
        puts "SQL file created successfully at: #{sql_file}"
        puts "\nFirst 500 lines of SQL content:"
        system("head -n 500 #{sql_file}")
      else
        puts "Preview failed with exit code: #{$?.exitstatus}"
      end
    end
  end
end

I found this useful in my side projects to run before destructive migrations and tasks to evaluate the outcome. It's also been helpful in reviewing the changes of my databases over time. Hopefully others will find this a bit useful as well. Obviously, really good seed data doesn't necessitate this but sometimes thats just not the case - which is the scenario these tasks help resolve.

If you have your local applications set up in a specific way and you don't have good seeds to restore it's state - this is for you.

Tested in:

• Ruby on Rails
  • ✅ 8.0.1