当前位置:数据库 > Postgres >>

Postgres逻辑备份脚本

Postgres逻辑备份脚本
 
昨天一个哥们说postgres的dump很难用,不尽然。下面 是利用Postgres自带的导出工具pg_dump来写的日常逻辑备份脚本,基本实现了每天自动备份,并保留一周时间。 
 
环境: 
CentOS 6.2 
Postgres 9.1.3 
 
1.整个库逻辑备份方案(相对较小)
#!/bin/bash
 
export PGHOME=/home/postgres
export PATH=$PGHOME/bin:$PATH
export PGDATA=/var/pgdata/
export BACKUP_DIR=/home/postgres/backup
export LANG=en_US.utf8
export DATE_1=`date +"%Y-%m-%d"`
export DATE_2=`date -d "-7 day"  +"%Y-%m-%d"`
 
######################################################
##
##  purpose:backup total database everyday logically
##  
##  author :kenyon
##   
##  created:2013-03-25
##
##  restore_script:psql -d db_kenyon -U postgres < /home/postgres/backup/file_name
##  
#####################################################
 
test -f $BACKUP_DIR/db_kenyon_back_$DATE_2
 
if [ $? -eq 0 ] ; then
 
  rm -f $BACKUP_DIR/db_kenyon_back_$DATE_2
 
  echo " `date +"%Y-%m-%d %H:%M:%S"` the file $BACKUP_DIR/db_kenyon_back_$DATE_2 has been moved"
 
fi
 
test -f $BACKUP_DIR/db_kenyon_back_$DATE_1
 
if [ $? -eq 0 ] ; then
 
  echo "Today's(`date +"%Y-%m-%d %H:%M:%S"`) BACKUP job has done"
 
else
 
  pg_dump -h 192.168.1.142 -p 5432 -U postgres -b -Fp db_kenyon -f $BACKUP_DIR/db_kenyon_back_$DATE_1
 
 
######check the dump file whether produced successfully
 
  test -f $BACKUP_DIR/db_kenyon_back_$DATE_1
 
  if [ $? -eq 0 ]; then
   
     echo " `date +"%Y-%m-%d %H:%M:%S"` the database db_kenyon has been backuped to $BACKUP_DIR/db_kenyon_back_$DATE_1 successfully"
 
  else
    
     echo " `date +"%Y-%m-%d %H:%M:%S"` the pg_dump failed"
 
  fi
 
fi
2.单表逻辑备份方案(表相对较大,下面采用的压缩级别为1级)
#!/bin/bash
 
export BACKUP_DIR=/home/postgres/backup
export DATE_1=`date +"%Y-%m-%d"`
export DATE_2=`date -d "-7 day"  +"%Y-%m-%d"`
 
#############################################################################
##
##  purpose:backup total database everyday logically
##  
##  author :kenyon
##   
##  created:2013-03-25
##
##  restore_script: pg_restore -U postgres -Fc -d db_name tbl_user_20130218.bak
##  
#############################################################################
 
source /home/postgres/.bash_profile
 
cd $BACKUP_DIR
 
if [ -d ./$DATE_2 ] ; then
 
  rm -rf $BACKUP_DIR/$DATE_2
 
  echo " `date +"%Y-%m-%d %H:%M:%S"` the file $BACKUP_DIR/$DATE_2 has been moved"
 
fi
 
if [ -d ./$DATE_1 ] ; then
 
  echo "Today's(`date +"%Y-%m-%d %H:%M:%S"`) BACKUP job has done"
 
else
  
  mkdir -p $BACKUP_DIR/$DATE_1
 
##### With compress level 1 dump out relation tables ############################
  
  pg_dump -p 5432 -U postgres db_kenyon -Fc -Z 1 -t tbl_user -f  $BACKUP_DIR/$DATE_1/tbl_user_$DATE_1.bak
  pg_dump -p 5432 -U postgres db_kenyon -Fc -Z 1 -t tbl_company -f  $BACKUP_DIR/$DATE_1/tbl_company_$DATE_1.bak
  pg_dump -p 5432 -U postgres db_kenyon -Fc -Z 1 -t tbl_report_mon -f  $BACKUP_DIR/$DATE_1/tbl_report_mon_$DATE_1.bak
  pg_dump -p 5432 -U postgres db_kenyon -Fc -Z 1 -t tbl_report_daily -f  $BACKUP_DIR/$DATE_1/tbl_report_daily_$DATE_1.bak
 
 echo "Today's(`date +"%Y-%m-%d %H:%M:%S"`) BACKUP job has fineshed"
 
fi
3.Copy做增量备份 (时间或者ID) 
psql -d db_kenyon -c"copy (select * from schema_name.table_name where crt_time>=$date) to '/home/postgres/tbl.bak'"  
psql -d db_kenyon -c"copy (select * from schema_name.table_name where id>=$id) to '/home/postgres/tbl.bak'" 
 
4.crontab定时调度 
 
50 1 * * * sh /home/postgres/script/DB_backup.sh >> /home/postgres/backup/backup.log 2>&1 
 
5.说明 
上述脚本相对简单,还可以加入其它一些异常判断和邮件发送等。
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,