Скрипт тестировался на PostgreSQL 10.5 CentOS Linux release 7.6.1810
Текст скрипта:
#!/bin/sh
#Export postgres connection parameters
source /usr/local/etc/pgsql_funcs.conf
DATA=`date +"%Y-%m-%d_%H-%M"`
DATEFMT="%Y-%m-%d_%H-%M-%S"
GETDB="select datname from pg_database where datistemplate = 'f';"
SERVICELOG="/home/postgres/BACKUP/service.log"
BACKUPPATH="/home/postgres/BACKUP/files"
COUNT=0
TOTALCOUNT=0
echo "Start execution of backup databases script at ${DATA}" #>> ${SERVICELOG}
RESULT=$(/usr/bin/psql -h $PGHOST -p $PGPORT -U $PGROLE -d $PGDATABASE -t -c "${GETDB}" 2>&1)
if [ $? -ne 0 ]; then
echo "${RESULT}"
exit
fi
for dbname in ${RESULT}; do
TOTALCOUNT=$[$TOTALCOUNT +1]
echo "`date +${DATEFMT}` Start backup ${dbname}" #>> ${SERVICELOG}
/usr/bin/pg_dump -U $PGROLE ${dbname}| pigz > $BACKUPPATH/$DATA-${dbname}.sql.gz 2>&1
if [ $? -ne 0 ]; then
echo "Failed to backup ${dbname}" #>> ${SERVICELOG}
continue
else
echo "`date +${DATEFMT}` End backup ${dbname}" #>> ${SERVICELOG}
COUNT=$[$COUNT +1]
fi
done
/usr/bin/find $BACKUPPATH -type f -mtime +20 -exec echo "Removing outdated backup file: {}" \; -exec rm -f {} \;
echo "End execution of backup databases script at `date +${DATEFMT}`" #>> ${SERVICELOG}
echo "${COUNT} of ${TOTALCOUNT} databases backed up" #>> ${SERVICELOG}
