73 lines
2.0 KiB
Bash
73 lines
2.0 KiB
Bash
#!/bin/bash
|
|
|
|
# Cron job check the logs
|
|
|
|
## Executing the script
|
|
# bash db_backup_script.sh
|
|
|
|
## command used to back up
|
|
# docker exec DOCKER_CONTAINER pg_dump -U postgres -Fc DATABASE_NAME > BACKUP_FILE.DUMP
|
|
|
|
## command used to restore
|
|
# directory where the backup is saved in in the docker container: /var/lib/postgresql/data
|
|
# for example: docker exec postgres pg_restore -U postgres -d vikunja-backup /var/lib/postgresql/data/backup/vikunja-2024-01-31.dump
|
|
# docker exec DOCKER_CONTAINER pg_restore -U postgres -d DATABASE_NAME /var/lib/postgresql/data/backup/BACKUP_FILE.DUMP
|
|
|
|
# Date format
|
|
YEAR=$(date +"%Y")
|
|
MONTH=$(date +"%m")
|
|
DAY=$(date +"%d")
|
|
NOW=$(date +"%Y-%m-%d")
|
|
|
|
BACKUP_DIRECTORY="/home/debian/docker/compose/project/db/postgres/data"
|
|
|
|
# Docker container with DB to backup
|
|
DB_1="postgres"
|
|
DB_2="postgres-with-pg-vector"
|
|
|
|
back_up_db() {
|
|
|
|
# sql to list all databases
|
|
DATABASE_NAME=$(docker exec $1 psql -U postgres -t -c 'SELECT datname FROM pg_database WHERE datistemplate = false;')
|
|
|
|
# pg_dump command
|
|
PGDUMP="docker exec $1 pg_dump -U postgres -Fc"
|
|
|
|
for i in $DATABASE_NAME; do
|
|
|
|
# ignoring postgres db
|
|
if [[ "$i" != "postgres" ]]; then
|
|
|
|
echo "Backing up database $i"
|
|
# backup path to file
|
|
BACKFILE="$BACKUP_DIRECTORY/backup/$i-$NOW.dump"
|
|
$PGDUMP $i > $BACKFILE
|
|
|
|
fi
|
|
|
|
done
|
|
}
|
|
|
|
clean_up_backups() {
|
|
|
|
echo "Looking for dumps to prune in folder: $BACKUP_DIRECTORY/backup/"
|
|
|
|
# keep one backup per year, month and last two days
|
|
BACKUP_YEAR="*-$YEAR-02-01.dump"
|
|
BACKUP_MONTH="*-$YEAR-$MONTH-01.dump"
|
|
BACKUP_DAY="*-$YEAR-$MONTH-$DAY.dump"
|
|
BACKUP_DAY_1="*-$YEAR-$MONTH-$((DAY-1)).dump"
|
|
FILE_TO_DELETE=$(find $BACKUP_DIRECTORY/backup/ -type f \( ! -name $BACKUP_YEAR -a ! -name $BACKUP_MONTH -a ! -name $BACKUP_DAY -a ! -name $BACKUP_DAY_1 \))
|
|
|
|
# delete such files
|
|
for i in $FILE_TO_DELETE; do
|
|
echo "Pruning $i"
|
|
rm $i
|
|
done
|
|
}
|
|
|
|
back_up_db $DB_1
|
|
back_up_db $DB_2
|
|
|
|
clean_up_backups
|