文章目录
本文解决旨在解决使用docker部署的postgresql数据库自动备份的业务场景,确保数据库能按照策略定期备份。保证业务数据丢失或损坏时能够快速恢复。
注:系统必须已安装docker和docker-compose
一、PostgreSQL备份脚本
1. 备份脚本
# vi /opt/postgresql/conf/backup/pg_backup.sh
#!/bin/bash
# 设置数据库连接参数
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="PGDB"
DB_USER="root"
DB_PASS="Admin@123"
# 设置备份目录
BACKUP_DIR="/mnt"
# 获取当前时间戳
TIMESTAMP=$(date +"%Y%m%d%H%M%S")
# 设置备份文件名
BACKUP_FILE="$BACKUP_DIR/backup_$DB_NAME_$TIMESTAMP.sql"
# 使用 pg_dump 命令备份数据库
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -Fc -b -v -f "$BACKUP_FILE"
# 检查备份是否成功,并导出备份日志
if [ $? -eq 0 ]; then
echo "$TIMESTAMP Backup completed successfully: $BACKUP_FILE" >> /mnt/pg_backup.log
else
echo "$TIMESTAMP Backup failed." >> /mnt/pg_backuo.log
fi
# 检查并删除十天前的备份文件
find "$BACKUP_DIR" -type f -name "$DB_NAME_*.sql" -mtime +10 -exec rm {} \;
echo "$TIMESTAMP Backup completed and old backups cleaned up." >> /mnt/pg_backup.log
2.添加执行权限
chmod +x /opt/postgresql/conf/backup/pg_backup.sh
二、 部署PostgreSQL
1. 使用docker-compos部署PG
# vi /opt/postgresql
version: "3"
services:
postgresql:
image: postgres:16.3-alpine
restart: always
privileged: true
environment:
POSTGRES_USER: root
POSTGRES_PASSWORD: Admin@123
POSTGRES_DB: PGDB
ports:
- 5432:5432
volumes:
- ./conf/postgres/data:/var/lib/postgresql/data
- ./conf/backup:/mnt #备份脚本和备份文件存放目录
2. 启动PostgreSQL
# cd /opt/ postgresql
# docker-compose up -d
三、 计划任务自动备份
1. 设置计划任务
# 每天凌晨一点自动执行数据库备份脚本
# crontab -l
0 1 * * * docker exec postgresql /bin/bash -c "/mnt/pg_backup.sh"