备份
备份是有顺序的, 先做基线备份, 然后备份日志.
基线备份命令如下:
psql -d template1 -c "select PG_START_BACKUP('backup baseline')"
cp -R
$PGDATA/* $BUBASE
psql -d template1 -c "select PG_STOP_BACKUP()"
基线备份好后,
接下来就可以时不时的备份 WAL 日志了, 命令如下:
cp -R $PGDATA/pg_xlog/* $BUXLOG
日志备份尽可能的频繁一些. 因为当线上提供服务的所在磁盘坏掉,
而你又没有备份 WAL 日志, 你会丢数据.
根据实际情况:
基线备份的频率可以每小时一次,WAL根据实际情况可以每分钟。相当于每小时做一次全备,每分钟做一次增量备份。
5 * * * * /var/lib/pgsql/script/copyWAL.pl > /dev/null 2>&1
22
0 * * * /var/lib/pgsql/script/Database_HotBackup.pl > /dev/null 2>&1
02
03
04
05 my($datadir) ="/symphony/pgsql_data";
06 my($bindir) ="/usr/bin";
07 my($backupdir) ="/symphony/pgsql_backup/base";
08 my($receiver) ="admin@mail.com";
09 use POSIX qw(strftime);
10 $date=strftime "%Y%m%d", localtime;
11
12
13 sub begin_backup()
14 {
15 open(PSQL,"|$bindir/psql") or mail_user
16 ("begin backup error.") && exit(100);
17 print PSQL "select pg_start_backup('backupnow');\n";
18 close(PSQL);
19 }
20
21 sub end_backup()
22 {
23 open(PSQL,"|$bindir/psql") or mail_user
24 ("end backup error.") && exit(100);
25 print PSQL "select pg_stop_backup();\n";
26 close(PSQL);
27 }
28
29
30 sub do_backup()
31 {
32 system("/bin/tar cvf base.tar $datadir");
33 system("/bin/mv -f base.tar $backupdir/$date.tar");
34 }
35
36
37 sub mail_user()
38 {
39 # Disable the Mail Funcation.
40 #my($msg) =@_;
41 #open(MAIL,"|/bin/mail -s backup-result $receiver")
42 #or die("can not talk to:mail command.\n");
43 #print MAIL $msg;
44 #close(MAIL);
45 }
46
47 #tell psql begin our backup
48 &begin_backup();
49
50 #do tar
51 &do_backup();
52
53 #tell psql end backup
54 &end_backup();