Внес улучшения, исправил ошибки. Мне тут говорили про какой нибудь source хостинг, но там меня никто смотреть не будет, а тут пожалуйста, любой желающий может пнуть.
Итак: для оперативного восстановления любой конкретной базы скрипт архивации на основе pg_dump. Много меня за нее пинали, но тут возникает необходимость часто восстанавливать базу с глубиной примерно в неделю. Что-то бухгалтер сломает и просит вчерашнюю базу. Восстанавливать из за нее кластер целиком вообще нет ни времени ни желания.
root@PostgreSQL:/home/maintainer# cat /root/backup.sh
#!/bin/bash
FILENAME='/root/psql_dblist'
PORT=5432
USERNAME='dbadmin'
SERVERNAME='localhost'
ARCHIVEDIR='/Backup'
SUFFIX=''
ADMINSEMAILS='your@email.ru your2@email.ru'
NETDIR='//192.168.88.11/Kamin/PostgreSQL'
EMFROM="mail@mail.su"
ERRLOG="/root/backup.txt"
EMSMTP="1.2.3.4:25"
EMPASS="Password"
if [[ 'daily' = $1 ]]
then
SUFFIX='daily_'$(date +%u'_'%H)
#echo $SUFFIX
#echo Ok
fi
if [[ 'monthly' = $1 ]]
then
SUFFIX='monthly_'$(date +%m'_'%H)
fi
if [[ 'hot' = $1 ]]
then
SUFFIX='hot_'$(date +%Y-%m-%d-%H%M)
fi
if [[ $SUFFIX = '' ]]
then
echo 'Type monthly, daily or hot as parametr'
exit
fi
if mount | grep -qw $NETDIR
then
echo "Network directory is mounted"
else
echo "Network direcrory $NETDIR is not mounted" >> $ERRLOG
SUBJECT="PostgeSQL:NoGood SUFFUX=$SUFFIX"
MESSAGE="File $ERRLOG contain some errors. Look for attachment"
sendEmail -f $EMFROM -t $ADMINSEMAILS -u $SUBJECT -m $MESSAGE -s $EMSMTP -xu $EMFROM -xp $EMPASS -o tls=no -q -a $ERRLOG
exit
fi
#DBLIST=$(psql -U $USERNAME -p $PORT -l | awk '/psql/ { print $1 }')
DBLIST=$(psql -U $USERNAME -p $PORT -l | q -d'|' "select c1 from - where c1 <> '' and c2 <> '' and c1 not like 'template%' limit 1,10000")
echo "$DBLIST" > $FILENAME
cat ${FILENAME} | while read DBNAME
do
mkdir -p ${ARCHIVEDIR}'/'${DBNAME}
pg_dump -d $DBNAME -h ${SERVERNAME} -p $PORT -U ${USERNAME} -w -Fc -f ${ARCHIVEDIR}'/'${DBNAME}'/'${DBNAME}'_'$SUFFIX'.dump.gz'
if [[ $? -ne 0 ]]
then
echo $DBNAME': pg_dump error code is '$? >> $ERRLOG
fi
done
BackupErr=$(stat $ERRLOG -c %s)
SUBJECT=''
MESSAGE=''
if [[ $BackupErr = 0 ]]
then
SUBJECT="Archiving PostgeSQL:Ok SUFFUX=$SUFFIX"
MESSAGE="Archiving successful. File $ERRLOG is empty."
else
SUBJECT="PostgeSQL:NoGood SUFFUX=$SUFFIX"
MESSAGE="File $ERRLOG contain some errors. Look for attachment"
fi
sendEmail -f $EMFROM -t $ADMINSEMAILS -u $SUBJECT -m $MESSAGE -s $EMSMTP -xu $EMFROM -xp $EMPASS -o tls=no -q -a $ERRLOG
Естественно кластер бекапим тоже, на случай переноса или другой напасти:
root@PostgreSQL:/home/maintainer# cat /root/base_backup.sh
#!/bin/bash
FILENAME='/root/psql_dblist'
PORT=5432
USERNAME='dbadmin'
SERVERNAME='localhost'
ARCHIVEDIR='/Backup'
SUFFIX=''
ADMINSEMAILS='mail1@mail.ru mail2@mail.ru'
NETDIR='//192.168.88.11/Kamin/PostgreSQL'
EMFROM="mail@mail.su"
ERRLOG="/root/backup.txt"
EMSMTP="1.2.3.4:25"
EMPASS="Password"
if mount | grep -qw $NETDIR
then
echo "Network directory is mounted"
else
echo "Network direcrory $NETDIR is not mounted" >> $ERRLOG
SUBJECT="PostgeSQL:NoGood pg_basebackup"
MESSAGE="File $ERRLOG contain some errors. Look for attachment"
sendEmail -f $EMFROM -t $ADMINSEMAILS -u $SUBJECT -m $MESSAGE -s $EMSMTP -xu $EMFROM -xp $EMPASS -o tls=no -q -a $ERRLOG
exit
fi
pg_basebackup -h $SERVERNAME -U $USERNAME -p $PORT -w -D ${ARCHIVEDIR}'/WAL/basebackup_'$(date +%Y%m%d) -Ft
if [[ $? -ne 0 ]]
then
echo $DBNAME': pg_basebackup error code is '$? >> $ERRLOG
fi
find ${ARCHIVEDIR}'/WAL/' -type f,d -mtime +14 -delete
BackupErr=$(stat $ERRLOG -c %s)
SUBJECT=''
MESSAGE=''
if [[ $BackupErr = 0 ]]
then
SUBJECT="Archiving PostgeSQL:Ok pg_basebackup"
MESSAGE="Archiving successful. File $ERRLOG is empty."
else
SUBJECT="PostgeSQL:NoGood pg_basebackup"
MESSAGE="File $ERRLOG contain some errors. Look for attachment"
fi
sendEmail -f $EMFROM -t $ADMINSEMAILS -u $SUBJECT -m $MESSAGE -s $EMSMTP -xu $EMFROM -xp $EMPASS -o tls=no -q -a $ERRLOG
Сценарий восстановления, если будешь в неадеквате, или буду делать не я:
root@PostgreSQL:/home/maintainer# cat /root/restore.sh
#!/bin/bash
FILENAME='/root/psql_dblist'
PORT=5432
USERNAME='dbadmin'
SERVERNAME='localhost'
ARCHIVEDIR='/Backup'
SUFFIX=''
DBNAME=''
DUMPGZ=''
if [[ $1 != '' ]]
then
DBNAME=$1
else
echo "Point dbname as first parametr"
exit
fi
if [[ $2 != '' ]]
then
DUMPGZ=$2
else
echo "Point dump.gz file as second parametr"
exit
fi
echo $DBNAME
if psql -U $USERNAME -h $SERVERNAME -p $PORT -lqt | cut -d \| -f 1 | grep -qw $DBNAME
then
echo "Database with this name exists on this cluster"
exit
else
echo -n "Create database? (Y/n) "
read answer
case "$answer" in
Y) psql -U $USERNAME -h $SERVERNAME -p $PORT -c 'create database '$DBNAME -d postgres
;;
*) exit 0
;;
esac
fi
echo -n "Restore database? (Y/n) "
read answer
case "$answer" in
Y)
if file $DUMPGZ | grep -qw "PostgreSQL custom database dump"
#/Backup/bis_kamin55_psql/bis_kamin55_psql_daily_6_08.dump.gz: PostgreSQL custom database dump - v1.12-0
then
pg_restore -h $SERVERNAME -U $USERNAME -p $PORT -d $DBNAME -w -Fc $DUMPGZ
else
gunzip < $DUMPGZ | psql -h $SERVERNAME -U $USERNAME -p $PORT -d $DBNAME
fi
;;
*) exit 0
;;
esac
Ну и в довершение, верна ли информация в crontab
# m h dom mon dow command
0 8,12,16,20 * * * /root/backup.sh daily 2>>/root/backup.txt
0 4 15 * * /root/backup.sh monthly 2>>/root/backup.txt
0 2 * * 5 /root/base_backup.sh 2>>/root/backup.txt
