Backup database via SFTP on Linux

cron 自动执行如下 sh

#!/bin/bash -e

# https://crontab.guru
# 5 23 * * * /home/user1/xxx.sh
# crontab -e
# crontab -l
# sudo grep database_backup /var/log/syslog


# 将数据库备份到 tmp 目录
PGPASSWORD='xxxxx' \
  pg_dump \
  --host pgm-xxxxxxxxxxxxx.pg.rds.aliyuncs.com \
  --port 1433 \
  --username username \
  --dbname xxxxx_production \
  | gzip > /home/user1/database_backups/tmp/xxxxx_production_db_$(TZ=Asia/Shanghai date +"%Y_%m_%d_%H_%M_%S").sql.gz

# 将 tmp 目录中备份出的文件通过 sftp 上传到备份服务器
export SSHPASS=SFTP PASSWORD
sshpass -e sftp -oBatchMode=no -b - username@backup-server-address << !
  put  /home/user1/database_backups/tmp/*.gz
  bye
!

# 将 tmp 目录中文件移动到生产服务器备份目录
mv /home/user1/database_backups/tmp/* /home/user1/database_backups/

# 只保留最近 7 天的备份文件，超期的删除
find /home/user1/database_backups/ -mtime +7 -name '*.gz' -exec rm -Rf {} \;


scp xx.xx.xx.xx:$(ssh xx.xx.xx.xx 'ls -t /home/username/database_backups/*.gz | head -1') /home/username/database_backups/tmp/
sshpass -e sftp -oBatchMode=no -b - username@backup-server-address << !
  put /home/username/database_backups/tmp/*.gz /back_up/database_backups/
  bye
!
mv /home/username/database_backups/tmp/* /home/username/database_backups/
find /home/username/database_backups/ -mtime +7 -name '*.gz' -exec rm -Rf {} \;