PostgreSQL backup script.

backup_database.rb

#!/usr/bin/env ruby
# coding: utf-8

require 'date'
require 'net/ssh'
require 'net/sftp'

module BackupDatabase
  class Task
    def initialize(name)
      @name = name
    end

    def check
      %w{
        pg_bin
        pg_lib
        pg_data
        pg_database
        pg_user
        local_dir

        remote_ip
        remote_user
        remote_port
        remote_dir
        retention_period
        identity_file
      }.each{|name| raise "#{name} is not set of #{@name} task" if instance_variable_get("@#{name}").nil?}
      raise "Can not run in this user" if ENV["USER"] != @pg_user
    end

    def backup
      BackupDatabase::CommonUtils.set_env(:add, :path, @pg_bin)
      BackupDatabase::CommonUtils.set_env(:add, :ld_library_path, @pg_lib)
      BackupDatabase::CommonUtils.set_env(:add, :pgdata, @pg_data)
      filename = "#{@pg_database}_#{Date.today.strftime('%Y%m%d')}.sql"
      system("pg_dump #{@pg_database} -f #{File.join(@local_dir, filename)}")
    end

    def upload
      Net::SSH.start(@remote_ip, @remote_user, keys: [@identity_file], port: @remote_port.to_i) do |session|
        session.sftp.connect do |sftp|
          Dir.glob(File.join(@local_dir, "*.sql")).each do |file|
            sftp.upload(file, File.join(@remote_dir, File.basename(file))).wait
            File.unlink(file)
          end

          sftp.dir.foreach(@remote_dir) do |entry|
            if /^.*_(2[0-9]{3})([0-1][0-9])([0-3][0-9])\.sql$/ =~ entry.name
              retention = Date.today - @retention_period.to_i
              create_date = Date.new($1.to_i, $2.to_i, $3.to_i)
              sftp.remove File.join(@remote_dir, entry.name).wait if retention > create_date
            end
          end
        end
      end
    end

    def exec!
      check
      backup
      upload
    end

    def method_missing(method_id, *args)
      instance_eval do
        instance_variable_set("@#{method_id}", args.first)
      end
    end
  end

  module CommonUtils
    class << self
      def set_env(type, name, value, sep=":")
        case type
        when :new then
          ENV[name.to_s.upcase] = value
        when :add then
          if ENV[name.to_s.upcase].nil?
            ENV[name.to_s.upcase] = value
          else
            ENV[name.to_s.upcase] = ENV[name.to_s.upcase].split(sep).unshift(value).join(sep)
          end
        end
      end
    end
  end
end

raise ArgumentError, "invalid argment" if ARGV.size != 1 || !FileTest.file?(ARGV.first)

def backup(name, &block)
  task = BackupDatabase::Task.new(name)
  task.instance_eval &block
  @tasks << task
end

@tasks = Array.new
load ARGV.first

@tasks.each do |task|
  task.exec!
end

conf.rb

backup :hoge do
  pg_bin "/usr/local/pgsql/bin"
  pg_lib "/usr/local/pgsql/lib"
  pg_data "/path/to/data_dir"
  pg_database "database_name"
  pg_user "postgres"
  local_dir "/home/postgres/backup"

  remote_ip "111.111.111.111"
  remote_user "remote_user"
  remote_port 22
  remote_dir "/home/remote_user/backup"
  retention_period 90
  identity_file "/path/to/id_rsa"
end

Very nice.

Are we supposed to run it like this: ruby backup_database config.rb

Thanks!