PostgreSQL运维实战：shell脚本实现物理备份+逻辑备份自动化

##postgresql备份脚本，物理、逻辑都可用

[postgres@pgserver1]$vi postgres_bak.sh
#/bin/bash
#ENV CONFIG
db_name=$1
type=$2
base_dir=/app/postgres/bin
backup_dir=/data/postgres/backup
T=`date +%Y%m%d%H%M%S`
PORT="5432"
user="postgres"
host="192.168.10.140"

#CHECK Transfer Param
if [ $# != 2 ];then
  echo "-----------------------------------------------------------------------------"
  echo "-------------------Transfer Param is ERROR, Backup Failed !!--------------------------------"
  echo "-----------------------------------------------------------------------------"
  echo "- backup command：sh postgres_bak.sh target database_name 1 or 2  |   logical：1   physical：2     -"
  echo "-----------------------------------------------------------------------------"
  exit 1
else
  if [ $2 -eq 1 ] || [ $2 -eq 2 ];then
    echo "--------------------${T} Backup Start！！----------------------------"
  else
    echo "--------ERROR：Failed to select the backup type ，logical：1   physical：2   ------------"
        exit 1
  fi
fi

###Determine the backup directory
if [ ! -d $backup_dir ];then
  mkdir -p "$backup_dir"
fi

if [ `ps -ef|grep  postgres_bak.sh |grep -v grep|wc -l` -gt 3 ];then
  echo "ERROR: Another user is backuping data. Do not repeat this operation"
  exit 1
fi

###Logical Backup
function dumpbak {
  ${base_dir}/pg_dump -h ${host} -C -U ${user} -d  ${db_name}  -p ${PORT} > ${backup_dir}/${db_name}_${T}_logical.sql
}

###Physical Backup
function basebak {
  mkdir -p ${backup_dir}/${T}_physical
  ${base_dir}/pg_basebackup -D ${backup_dir}/${T}_physical -Fp -Xs -v -P -h ${host} -p ${PORT} -U ${user}
  #${base_dir}/pg_basebackup -D ${backup_dir}/${T}_physical -Ft -Xf -Pv -z -Z5 -h ${host} -p ${PORT} --压缩
}

###Backup space usage
function bak_space {
  bak_size=`cd $backup_dir;du -sh`
  echo "^^^ The current total backup space is ${bak_size} If space is insufficient, please clear it in time！^^^"
}

case $2 in
  "1") echo "SUCCESS：Logical backup is about to start！" 
  dumpbak
  bak_space
  ;;
  "2") echo "SUCCESS：Physical backup is about to start (A physical backup will backup all database for the entire instance)！"
  basebak 
  bak_space 
  ;;
esac

if [ $? -eq 0 ];then
  echo "SUCCESS：backup complete！Please go to directory：$backup_dir "
else
  echo "ERROR：Backup exception ，please check"
  exit 1
fi
TE=`date +%Y%m%d%H%M%S`
echo "--------------------${TE} Backup End！！----------------------------"