pg数据库每周备份并自动删除脚本

#!/bin/bash

PG家目录(/opt/postgresql/pg/)

PG_HOME=‘/usr/pgsql-13/’

pg数据库连接信息

PG_HOST=“172.16.116.195” PG_PORT=“15432” PG_USER=“postgres” PG_PASSWD=“1234qwer@”

时间格式化,如 20211216

DATE=“date +%Y%m%d%H%M%S”

备份文件目录

DIR_BACKUP=“/home/db_backup”

日志目录: ${HOME}/data/pg-backup/logs

DIR_LOG=“/home/db_backup/logs”

日志文件: ${HOME}/data/pg-backup/logs/db_backup.INFO.2021-12-30.log

FILE_LOG=“/home/db_backup/db_backup.INFO.date +%F.log”

文件保留天数

DAY=1
DAY_LOG=“expr ${DAY} + 7”

备份数据库名, 多数据间空格分隔

DATABASES=(“hyperion”)

测试目录， 目录不存在则自动创建

test -d ${DIR_LOG} || mkdir -p ${DIR_LOG}
test -d $DI{R}_{B}ACKUP/${PG_USER}-${DATE} || mkdir -p $DI{R}_{B}ACKUP/${PG_USER}-${DATE}

------------------- 2021-12-16_17:40:48 Start -------------------

echo -e “\n----------------- $(date +%F\ %T) Start -----------------”
echo -e “\n================= $(date +%F\ %T) Start =================” >> ${FILE_LOG}

遍历数据库名

export PGPASSWOR=‘1234qwer@’
for database in “${DATABASES[@]}”; do
echo “---------- Current backup database: [ ${database} ] ----------”
echo “----------- Backed-up database: [ ${database} ] -----------” >> ${FILE_LOG}

执行备份命令

${PG_HOME}/bin/pg_dump -p ${PG_PORT} -U ${PG_USER} -w -d ${database} > $DI{R}_{B}ACKUP/${PG_USER}-KaTeX parse error: Expected group after '_' at position 10: {DATE}/db_̲{database}_${DATE}.sql
done

压缩备份文件

cd ${DIR_BACKUP}
tar -czf $P{G}_{U}SER-${DATE}.tar.gz $P{G}_{U}SER-${DATE}/
echo “---------- Backup file created: [ $P{G}_{U}SER-${DATE}.tar.gz ]”
echo “Backup file created: $DI{R}_{B}ACKUP/${PG_USER}-${DATE}.tar.gz” >> ${FILE_LOG}

压缩后, 删除压缩前的备份文件和目录

rm -f $DI{R}_{B}ACKUP/${PG_USER}-${DATE}/*
rmdir $DI{R}_{B}ACKUP/${PG_USER}-${DATE}/

备份已完成, 下面是清理备份的旧文件, 释放磁盘空间

查找 7天前的文件

OLD_BACKUP=“find ${DIR_BACKUP} -type f -mtime +${DAY} -iname ${PG_USER}-\*.gz”
OLD_LOGS=“find ${DIR_LOG} -type f -mtime +${DAY_LOG} -iname db_backup.INFO.\*.log”

遍历旧备份文件

for bak in “${OLD_BACKUP[@]}”; do

删除旧备份

rm -f ${bak}
echo “------------------- Deleted old bak files -------------------” >> $FIL{E}_{L}OGecho"${bak}" >> ${FILE_LOG}
done

遍历旧日志

for log in “${OLD_LOGS[@]}”; do

删除旧日志

rm -f ${log}
echo “------------------- Deleted old log files -------------------” >> $FIL{E}_{L}OGecho"${log}" >> ${FILE_LOG}
done