对于DBA或运维人员来讲，备份恢复是最基本也是最必要的技能之一，一个通用并且稳定的热备脚本能够极大的释放工程师的时间和精力，本篇提供PostgreSQL数据库最常用的热备脚本，希望能够帮助到大家。
1、PostgreSQL逻辑热备脚本（Linux版）
脚本代码：
#!/bin/bash
#Need PostgreSQL 9.0+
#Only modify the following variables!!
#pg_dump exec directory
PGBIN=/postgres/app/pg12/bin
#host address
HOST=192.168.51.241
#backup username, pleause use high privileges user
USER=postgres
#backup username password
PASSWORD=Abcd321#
export PGPASSWORD=$PASSWORD
#database port
PORT=5432
#database name for connect test
DBNAME=postgres
#backup db,example: DBARRAY=('db1' 'db2')
DBARRAY=('db01' 'db02')
#backup file directory
DUMPPATH=/backup
#backup temp log
TEMPLOG=$DUMPPATH/pg_logicbak.out
#backup log file
LOGFILE=$DUMPPATH/pg_logicbak.log
#backup file save time, DAYS
SAVETIME=2
#whether parallel execution is required? 'y' is required, 'n' isn's required
ISPARALLEL='n'
#parallel level
PARALLEL=4
shopt -s expand_aliases
alias cdate='date "+%Y-%m-%d %H:%M:%S"'
CONN="-h $HOST -p $PORT -U $USER"
stdopt=`$PGBIN/psql $CONN -d $DBNAME -t -c "select 123" 2> /dev/null|awk '{print $NF}'`
if [ -n "$stdopt" ] && [ "$stdopt" = "123" ];then
echo "$(cdate) [INFO] Connect is successed, Take it easy." >> $LOGFILE
else
echo "$(cdate) [ERROR] Connect is failed, Please check." >> $LOGFILE
exit 1
fi
for(( i=0;i<${#DBARRAY[@]};i++ ))do
if [ ${ISPARALLEL} = 'n' ];then
$PGBIN/pg_dump -h${HOST} -U${USER} -Fc -p ${PORT} -v -f ${DUMPPATH}/pglogicbak_$(date "+%Y%m%d%H%M%S")_${DBARRAY[i]}'.dump' ${DBARRAY[i]} > $TEMPLOG 2>&1
count=`grep 'pg_dump: saving' $TEMPLOG | wc -l`
if [ ${count} = 4 ];then
echo -e "$(cdate) [INFO] ${DBARRAY[i]} logical backuped successfully." >> $LOGFILE
else
echo -e "$(cdate) [ERROR] ${DBARRAY[i]} logical backuped failed. Please Check!" >> $LOGFILE
fi
find $DUMPPATH -type f -name "pglogicbak_*.dump" -ctime +$SAVETIME | xargs rm -f
elif [ ${ISPARALLEL} = 'y' ];then
$PGBIN/pg_dump -h${HOST} -U${USER} -Fd -j ${PARALLEL} -p ${PORT} -v -f ${DUMPPATH}/pglogicbak_$(date "+%Y%m%d%H%M%S")_${DBARRAY[i]}/ ${DBARRAY[i]} > $TEMPLOG 2>&1
count=`grep 'pg_dump: saving' $TEMPLOG | wc -l `
if [ ${count} = 4 ];then
echo -e "$(cdate) [INFO] ${DBARRAY[i]} logical backuped successfully." >> $LOGFILE
else
echo -e "$(cdate) [ERROR] ${DBARRAY[i]} logical backuped failed. Please Check!" >> $LOGFILE
fi
find $DUMPPATH -type d -name "pglogicbak_*" -ctime +$SAVETIME | xargs rm -rf
else
exit 1
fi
done;
正常输出：
2022-12-29 16:01:24 [INFO] Connect is successed, Take it easy.
2022-12-29 16:01:24 [INFO] db01 logical backuped successfully.
2022-12-29 16:01:24 [INFO] db02 logical backuped successfully.
2022-12-29 16:01:31 [INFO] Connect is successed, Take it easy.
2022-12-29 16:01:32 [INFO] db01 logical backuped successfully.
2022-12-29 16:01:32 [INFO] db02 logical backuped successfully.
错误输出：
2022-12-29 15:59:28 [ERROR] Connect is failed, Please check.
2022-12-29 15:59:54 [ERROR] Connect is failed, Please check.
2022-12-29 16:00:10 [ERROR] Connect is failed, Please check.
2022-12-29 16:00:30 [ERROR] Connect is failed, Please check.
2、PostgreSQL物理热备脚本（Linux版）
脚本代码：
#!/bin/bash
#Need PostgreSQL 9.0+
#Only modify the following variables!!
#pg_basebackup exec directory
PGBIN=/postgres/app/pg12/bin
#host address
HOST=127.0.0.1
#backup username, pleause use replication privileges user
USER=postgres
#backup username password
PASSWORD=Abcd321#
export PGPASSWORD=$PASSWORD
#database port
PORT=5432
#database name for connect test
DBNAME=postgres
#backup file directory
DUMPPATH=/backup
#backup temp log
TEMPLOG=$DUMPPATH/pg_physicbak.out
#backup log file
LOGFILE=$DUMPPATH/pg_physicbak.log
#Backup mode: 'p' is normal mode, and 't' is packaging mode (packaging the data directory)
DUMPTYPE='p'
#whether compression is required, 'y' means compression, and 'n' means no compression
ISCOMPASS='n'
#compression level (0-9, default 5). The higher the level, the slower the compression,
#and the higher compression ratio ('5' is about 1/6 of the size of the data directory)
COMPASS_LEVEL=5
#backup file save time, DAYS
SAVETIME=2
shopt -s expand_aliases
alias cdate='date "+%Y-%m-%d %H:%M:%S"'
alias cdate2='date "+%Y%m%d%H%M%S"'
CONN="-h $HOST -p $PORT -U $USER"
stdopt=`$PGBIN/psql $CONN -d $DBNAME -t -c "select 123" 2> /dev/null|awk '{print $NF}'`
if [ -n "$stdopt" ] && [ "$stdopt" = "123" ];then
echo "$(cdate) [INFO] Connect is successed, Take it easy." >> $LOGFILE
else
echo "$(cdate) [ERROR] Connect is failed, Please check." >> $LOGFILE
exit 1
fi
if [ ${DUMPTYPE} = 'p' ];then
$PGBIN/pg_basebackup $CONN -Fp -P -v -D ${DUMPPATH}/full_physicbak_$(cdate2) > $TEMPLOG 2>&1
elif [ ${DUMPTYPE} = 't' ];then
if [ ${ISCOMPASS} = 'n' ];then
$PGBIN/pg_basebackup $CONN -Ft -P -v -D ${DUMPPATH}/full_physicbak_$(cdate2) > $TEMPLOG 2>&1
elif [ ${ISCOMPASS} = 'y' ];then
$PGBIN/pg_basebackup $CONN -Ft -z -Z ${COMPASS_LEVEL} -P -v -D ${DUMPPATH}/full_physicbak_$(cdate2) > $TEMPLOG 2>&1
else
exit 1
fi
else
exit 1
fi
count=`grep 'pg_basebackup: base backup completed' $TEMPLOG | wc -l`
if [ ${count} = 1 ];then
echo "$(cdate) [INFO] PostgreSQL physical backuped successfully." >> $LOGFILE
else
echo "$(cdate) [ERROR] PostgreSQL physical backuped failed. Please Check!" >> $LOGFILE
fi
find $DUMPPATH -type d -name "full_physicbak_*" -ctime +$SAVETIME | xargs rm -rf
正常输出：
2022-12-29 16:06:26 [INFO] Connect is successed, Take it easy.
2022-12-29 16:06:34 [INFO] PostgreSQL physical backuped successfully.
2022-12-29 16:07:05 [INFO] Connect is successed, Take it easy.
2022-12-29 16:07:13 [INFO] PostgreSQL physical backuped successfully.
错误输出：
2022-12-29 16:05:03 [ERROR] Connect is failed, Please check.
2022-12-29 16:05:21 [ERROR] Connect is failed, Please check.
2022-12-29 16:05:34 [ERROR] Connect is failed, Please check.
2022-12-29 16:05:57 [ERROR] Connect is failed, Please check.
以上两种热备场景，脚本中均内置的备份成功关键字的判断，只有真正成功完成备份，才会在日志中显示备份成功，因此在备份可靠性方面是有保障的。