Command line app that wraps PostgreSQL commands for backup and restore of either your entire database or only its data (written in Ruby using standard library without any external gems).

rails_pg_backup.rb

#!/usr/bin/env ruby

require 'date'
require 'optparse'
require 'ostruct'

# TODO: Add quoting for file option/argument
# TODO: See if using 'pathname' stdlib could improve code.

# Rails PostgreSQL database backup helper application
class RailsPGBackup
  VERSION = %w(3 0 2)

  DEFAULT_USER = begin
                   `whoami`.strip
                 rescue
                   'postgres'
                 end

  OPERATIONS = %w(backup restore)
  OPERATION_ALIASES = { 'b' => 'backup', 'r' => 'restore' }

  TYPES = %w(full data-only)
  TYPE_ALIASES = { 'f' => 'full', 'd' => 'data-only' }

  class << self
    private

    def parse
      options = OpenStruct.new
      options.dry_run = false
      options.operation = OPERATIONS[0]
      options.type = TYPES[0]

      OptionParser.new do |opts|
        opts.banner = 'Usage: rails_pg_backup.rb [options] database [file]'
        opts.separator ''
        opts.separator 'Specific options:'

        # Boolean switch
        opts.on('-d', '--[no-]dry-run',
                'Show command to be executed without executing (default=false)') do |dry_run|
          puts 'Performing dry run...'
          options.dry_run = dry_run
        end

        opts.on('-ePATH', '--executable-folder=PATH',
                'Path to folder containing PostgreSQL executables (pg_dump, pg_restore, and psql)') do |path|
          options.executable_folder = path
        end

        opts.on('-fFILE', '--filename=FILE', 'File path to backup to or restore from',
                '  Defaults to DATABASE_TIMESTAMP.TYPE_backup (backup operation only)',
                '  Note that this operation overridden by a given file path after database name') do |file|
          options.file = file
        end

        opts.on('-oOPERATION', '--operation=OPERATION', OPERATIONS, OPERATION_ALIASES,
                "Select operation (default=#{OPERATIONS[0]}): #{OPERATIONS.join(' ')}") do |operation|
          options.operation = operation
        end

        opts.on('-tTYPE', '--type=TYPE', TYPES, TYPE_ALIASES,
                "Select type of operation type (default=#{TYPES[0]}): #{TYPES.join(' ')}") do |type|
          options.type = type
        end

        opts.on('-uPGUSER', '--user=PGUSER', "PostgreSQL user (default=#{DEFAULT_USER})") do |user|
          options.user = user
        end

        opts.on('-boa', '--[no-]backup-overwrite-allowed',
                'Allow overwrite of existing files (an issue when specifying backup location).') do |backup_overwrite_allowed_predicate|
          options.allow_backup_overwrite = backup_overwrite_allowed_predicate
        end

        opts.separator ''
        opts.separator 'Common options:'

        opts.on_tail('-h', '--help', 'Show this message') do
          puts opts
          exit
        end

        opts.on_tail('-v', '--version', 'Show version') do
          puts "Version: #{VERSION.join('.')}"
          exit
        end
      end.parse!

      options
    end # def parse

    def executable_path(executable_name, executable_folder = nil)
      quote_path_p = executable_name.include?(' ') || (executable_folder && executable_folder.include?(' '))

      val = if executable_folder
              File.join(executable_folder.gsub(/\\/, '/'), executable_name)
            else
              executable_name
            end

      quote_path_p ? "\"#{val}\"" : val
    end # def executable_path(executable_name, executable_folder = nil)

    def file_path(database, options)
      fail 'Error: File must be specified' unless
        options.operation == OPERATIONS[0]

      timestamp = DateTime.now.strftime('%F-%H-%M-%S')

      "#{database}_#{timestamp}.#{options.type}_backup"
    end # def file_path(database, options)

    def pg_user_option(options)
      if options.user
        '-U' + options.user
      else
        "-U#{DEFAULT_USER}"
      end
    end # def pg_user_option(options)

    def execute_system_command(command, dry_run = true)
      if dry_run
        puts command
      else
        system command
      end
    end # def execute_system_command(command, dry_run = true)

    def perform_backup(database, file, options)
      case options.type
      when TYPES[0]
        command = "#{executable_path('pg_dump', options.executable_folder)} #{pg_user_option(options)} --file=#{file} #{database}"
      when TYPES[1]
        command = "#{executable_path('pg_dump', options.executable_folder)} #{pg_user_option(options)} --file=#{file} --format=tar --exclude-table=schema_migrations --exclude-table=ar_internal_metadata #{database}"
      else
        fail "Error (unidentified operation type requested): #{options.type}"
      end

      execute_system_command(command, options.dry_run)
    end # def perform_backup(database, file, options)

    def perform_restore(database, file, options)
      case options.type
      when TYPES[0]
        command = "#{executable_path('psql', options.executable_folder)} #{pg_user_option(options)} #{database} < #{file}"
      when TYPES[1]
        command = "#{executable_path('pg_restore', options.executable_folder)} #{pg_user_option(options)} --data-only --dbname=#{database} --disable-triggers #{file}"
      else
        fail "Error (unidentified operation type requested): #{options.type}"
      end

      execute_system_command(command, options.dry_run)
    end # def perform_restore(database, file, options)

    public

    def main
      ARGV << '-h' if ARGV.empty?

      # Note that the standard library OptionParser library invoked in the following method has access
      # to ARGV and removes any options that it found and parsed in ARGV (argument vector).
      options = parse

      if ARGV.empty?
        fail 'Error: No database name given'
      else
        database = ARGV[0]

        if ARGV.length > 1
          file = ARGV[1]
        elsif options.file
          file = options.file
        else
          file = file_path(database, options)
        end

        case options.operation
        when OPERATIONS[0]
          if File.exist?(file)
            fail 'File already exists!'
          end unless options.allow_backup_overwrite

          perform_backup(database, file, options)
        when OPERATIONS[1]
          perform_restore(database, file, options)
        else
          fail "Error (unidentified operation requested): #{options.operation}"
        end

      end # if ARGV.empty?
    end # def main(args)
  end # class << self
end # class RailsPGBackup

begin
  RailsPGBackup.main
rescue => e
  puts e
  puts <<TIPS

Tips
  Use `rake db:create` in Rails repository before trying to do a full restore
  Use `rake db:create db:schema:load` in Rails repository before trying to do a data-only restore
TIPS

  exit 1
end