резервное копирование PostgreSQL

cron.sh

# Добавляем скрипт в cron:
# sudo crontab -e
# * * * * * /home/admin/pg_backup.sh > /dev/null 2>&1

install.sh

#устанавливаем необходимое для работы скрипта
sudo apt install postgresql-client
sudo apt install curlftpfs
sudo apt install sendemail

pg_backup.sh

# Указываем переменные
DB_USERNAME=''
DB_NAME=''
DB_PASSWORD=''
DB_HOST=''
TIMESTAMP=`date +%Y%m%d-%H%M`
BACKUP_DIR='/home/admin/pg_backup'
FILENAME=$TIMESTAMP.sql.gz
FTP='ftp://127.0.0.1/backup/'
FTP_MOUNT='127.0.0.1/backup/'
FTP_USERNAME=''
FTP_PASSWORD=''
MOUNT_DIR='/home/admin/ftp_mount'
# Делаем дамп базы | кладем в  BACKUP_DIR, сжимаем 
# Ищем в BACKUP_DIR только файлы *.gz которые изменялись более чем 14 дней назад | удаляем их
# Копируем наш бэкап на FTP сервер в папку /backup
# Монтируем FTP_MOUNT в локальную MOUNT_DIR
# Ищем в смонтированом калатоге только файлы *.gz, которые изменялись более чем 14 дней назад и удаляем их
# размонтируем
pg_dump postgres://$DB_USERNAME:$DB_PASSWORD@$DB_HOST/$DB_NAME | gzip > $BACKUP_DIR/$FILENAME               \
&& find $BACKUP_DIR -name '*.gz' -mtime +14 -print0 | xargs -0 rm -f                                        \
&& curl -u $FTP_USERNAME:$FTP_PASSWORD -T $BACKUP_DIR/$FILENAME $FTP                                        \
&& curlftpfs $FTP_USERNAME:$FTP_PASSWORD@$FTP_MOUNT $MOUNT_DIR                                              \
&& find $MOUNT_DIR -name '*.gz' -mtime +14 -delete                                                          \
&& umount $MOUNT_DIR

pg_backup_log.sh

DB_USERNAME='...'
DB_NAME='...'
DB_PASSWORD='...'
DB_HOST='...'
TIMESTAMP=`date +%Y%m%d-%H%M`
BACKUP_DIR='/home/admin/pg_backup'
FILENAME=$TIMESTAMP.sql.gz
FTP='ftp://127.0.0.1/pg_backup/'
FTP_MOUNT='127.0.0.1/pg_backup/'
FTP_USERNAME='...'
FTP_PASSWORD='...'
MOUNT_DIR='/home/admin/ftp_mount/'
LOG_FILE='/home/admin/logger/pg_backup.log'
readonly SCRIPT_NAME=$(basename $0)
log() {
   echo "$@" >> $LOG_FILE
   logger -p user.notice -t $SCRIPT_NAME "$@"
}
err() {
   echo "$@" >> $LOG_FILE
   logger -p user.error -t $SCRIPT_NAME "$@"
}
local_backup() {
  log $TIMESTAMP "start pg_dump gzip"
  pg_dump postgres://$DB_USERNAME:$DB_PASSWORD@$DB_HOST/$DB_NAME | gzip > $BACKUP_DIR/$FILENAME
  RET_CODE=$?
  if [ $RET_CODE -eq 0 ] ; then
    log $TIMESTAMP "finish pg_dump gzip"
  else
    err $TIMESTAMP "ERROR pg_dump gzip" 
    return $RET_CODE
  fi
}
rotate_backup() {
  log $TIMESTAMP "start rotate_backup" 
  find $BACKUP_DIR -name '*.gz' -mmin +10 -delete
  RET_CODE=$?
  if [ $RET_CODE -eq 0 ] ; then
    log $TIMESTAMP "finish rotate_backup"
  else
    err $TIMESTAMP "ERROR rotate_backup"
	return $RET_CODE
  fi
}
remote_backup() {
  log $TIMESTAMP "start remote_backup" 
  curl -u $FTP_USERNAME:$FTP_PASSWORD -T $BACKUP_DIR/$FILENAME $FTP
  RET_CODE=$?
  if [ $RET_CODE -eq 0 ] ; then
    log $TIMESTAMP "finish remote_backup" 
  else
    err $TIMESTAMP "ERROR remote_backup"
	return $RET_CODE
  fi
}
remote_initislize() {
  log $TIMESTAMP "start remote_initislize" 
  curlftpfs $FTP_USERNAME:$FTP_PASSWORD@$FTP_MOUNT $MOUNT_DIR
  RET_CODE=$?
  if [ $RET_CODE -eq 0 ] ; then
    log $TIMESTAMP "finish remote_initislize"
  else
    err $TIMESTAMP "ERROR remote_initislize"
	return $RET_CODE
  fi
}
rotate_remote_backup() {
  log $TIMESTAMP "start rotate_remote_backup" 
  find $MOUNT_DIR -name '*.gz' -mmin +30 -delete
  RET_CODE=$?
  if [ $RET_CODE -eq 0 ] ; then
    log $TIMESTAMP "finish rotate_remote_backup"
  else
    err $TIMESTAMP "ERROR rotate_backup"
	return $RET_CODE
  fi
}
finish() {
  log $TIMESTAMP "start umount"
  umount $MOUNT_DIR
  RET_CODE=$?
  if [ $RET_CODE -eq 0 ] ; then
    log $TIMESTAMP "happy ending"
  else
    err $TIMESTAMP "ERROR happy ending"
    return $RET_CODE
  fi	
}
local_backup
rotate_backup
remote_backup
remote_initislize
rotate_remote_backup
finish