首先,对数据库在file system level做一个backup(PostgreSQL是首先用pg_start_backup('label')命令,
然后用tar直接tar整个data目录,假设命名为base.tar,然后pg_stop_backup();结束热备。
Oracle首先是用alter tablespace xxx begin backup,然后直接cp数据文件);
然后,备份相关的配置文件(PostgreSQL只需备份postgresql.conf,pg_hba.conf,pg_ident.conf就可以了,其实,
前面的tar已经将这些文件备份了,Oracle需要alter database backup control file......);
#############################################################
# hotBackup.pl
# Use to hot backup the PostgreSQL database.
# Author:Seamus Dean
# Date:2005-04-11
##############################################################
sub mail_user()
{
my($msg) =@_;
open(MAIL,"|/bin/mail -s backup-result $receiver") or die("can not talk to:mail command.\n");
print MAIL $msg;
close(MAIL);
}
###################################
# tell psql begin our backup
###################################
&begin_backup();
###################################
# do tar
###################################
&do_backup();
####################################
# tell psql end backup
####################################
&end_backup();
####################################
# mail the user about the result
####################################
&mail_user("PostgreSQL backup successfully.");
到这里,备份脚本基本上就完了,你可以将hotBackup.pl放在crontab中周期性的执行。
就算/home/pgsql/database目录彻底崩溃,我们可以像下面这样迅速恢复到1分钟内的数据:
#cp /disk3/PostgreSQL/base/base.tar ./
#tar xvf base.tar
#cd database/
#vi recovery.conf
输入如下内容:
restore_command='cp /disk3/PostgreSQL/archives/%f "%p"'
然后将/home/pgsql/database/pg_xlog/下面的WAL清空。
启动PostgreSQL,我们可以看到如下的LOG信息:
LOG: could not create IPv6 socket: Address family not supported by protocol
LOG: database system was interrupted at 2005-04-11 23:13:28 PDT
LOG: starting archive recovery
LOG: restore_command = "cp /disk3/PostgreSQL/archives/%f "%p""
cp: cannot stat `/disk3/PostgreSQL/archives/00000001.history': No such file or directory
LOG: restored log file "00000001000000000000002E.008EFCAC.backup" from archive
LOG: restored log file "00000001000000000000002E" from archive
LOG: checkpoint record is at 0/2E8EFCAC
LOG: redo record is at 0/2E8EFCAC; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 5271; next OID: 6351357
LOG: automatic recovery in progress
LOG: redo starts at 0/2E8EFCE8
LOG: restored log file "00000001000000000000002F" from archive
LOG: restored log file "000000010000000000000030" from archive
LOG: restored log file "000000010000000000000031" from archive
LOG: restored log file "000000010000000000000032" from archive
LOG: restored log file "000000010000000000000033" from archive
LOG: restored log file "000000010000000000000034" from archive
LOG: restored log file "000000010000000000000035" from archive
LOG: restored log file "000000010000000000000036" from archive
LOG: restored log file "000000010000000000000037" from archive
LOG: restored log file "000000010000000000000038" from archive
LOG: restored log file "000000010000000000000039" from archive
LOG: restored log file "00000001000000000000003A" from archive
LOG: restored log file "00000001000000000000003B" from archive
LOG: restored log file "00000001000000000000003C" from archive
LOG: restored log file "00000001000000000000003D" from archive
LOG: restored log file "00000001000000000000003E" from archive
LOG: restored log file "00000001000000000000003F" from archive
LOG: restored log file "000000010000000000000040" from archive
LOG: restored log file "000000010000000000000041" from archive
LOG: restored log file "000000010000000000000042" from archive
LOG: restored log file "000000010000000000000043" from archive
LOG: restored log file "000000010000000000000044" from archive
LOG: restored log file "000000010000000000000045" from archive
LOG: restored log file "000000010000000000000046" from archive
LOG: restored log file "000000010000000000000047" from archive
LOG: restored log file "000000010000000000000048" from archive
LOG: restored log file "000000010000000000000049" from archive
LOG: restored log file "00000001000000000000004A" from archive
LOG: restored log file "00000001000000000000004B" from archive
LOG: restored log file "00000001000000000000004C" from archive
LOG: record with zero length at 0/4C2BABE4
LOG: redo done at 0/4C2BABA8
LOG: restored log file "00000001000000000000004C" from archive
LOG: archive recovery complete
LOG: database system is ready