PostgreSQL数据库备份脚本

数据库备份shell脚本(.sh):
#!/bin/bash
now=`date +%Y%m%d%H%M%S`
mkdir -p /srv/pg_backup/${now}


for db_name in "库名1" "库名2" "库名3"
do
/srv/pgsql92/bin/pg_dump -Upgsql --blobs --format=c --compress=9 --file=/srv/pg_backup/${now}/${db_name}.dump ${db_name}"
done


数据库备份ruby脚本(.rb):
#!/srv/ruby19/bin/ruby
# encoding: UTF-8
require 'rubygems'
require 'active_record'
require 'pg'
require 'fileutils'

=begin
2012-09-19
PostgreSQL 数据库备份脚本
每次执行都备份到指定的目录下
每个数据库一个文件，扩展名是 dump
恢复需要先建立数据库，然后 pg_restore 进行恢复
=end


ActiveRecord::Base.establish_connection(
:adapter=>"postgresql",
:database => 'template1',
:host => 'localhost',
:port => 5432,
:username => 'pgsql',
:password => 'none'
)
#不做备份处理的数据库

#FIXME
SKIP_DATABASES = %w(template0 template1 sandbox fgpay2_test)
#FIXME
BACKUP_BASE_DIR = "/srv/pg_backup/#{Time.now.to_s(:number)}"

puts "备份目的路径: #{BACKUP_BASE_DIR}"

FileUtils.mkdir_p(BACKUP_BASE_DIR) unless File.exists?(BACKUP_BASE_DIR)

#获取所有的数据库列表
class PgDatabase < ActiveRecord::Base
self.table_name = "pg_database"
def self.all_databases
select('datname').where(["datistemplate = false"]).map{|record|
record.try(:datname) unless SKIP_DATABASES.include?(record.try(:datname)) }.delete_if{|r| r.blank?}
end
end

pg_dump_bin = lambda{|db_name|
#c模式 --clean --create 无效
#FIXME
system("/srv/pgsql92/bin/pg_dump -Upgsql --blobs --format=c --compress=9 --file=#{BACKUP_BASE_DIR}/#{db_name}.dump #{db_name}")
}

puts PgDatabase.all_databases.each{|db_name|
puts "BEGIN BackUp #{db_name} "
pg_dump_bin.call(db_name)
puts "BackUp #{db_name} done.\n\n"
}

#复制到其他机器做二次备份
#FIXME
#system("/usr/bin/scp -r #{BACKUP_BASE_DIR} app1:/srv/pg_backup_238/")