Database Backups

⚠️

Important

In order to make sure that the backup can successfully run every day, we need to make sure that the backup-manager image is always available on the docker registry. This means that you cannot just docker system prune -a without any filters!

Always use docker system prune -a --filter label!="prune=keep" or run the /home/schochal/prune.sh script!

If you delete the image by accident, running the pipeline will re-generate the image.

In order to create backups for all databases at certain intervals, a custom python script is used. Reason for this is that no project with our requirements was found (multiple databases at once; daily, weekly, monthly and semesterly backups, automatic compression).

The python script

is run every day at 04:10
creates a daily backup
creates a weekly backup if it is monday
creates a monthly backup if it is the first day of the month
creates a semesterly backup if it is the first of march or the first of september
deletes the oldest daily, weekly and monthly backups if there are more backups than configured

The corresponding files can be found at db-backup/backup.py and db-backup/Dockerfile. The backups are stored at /data/backups

The script is currently configured to keep 6 daily, 4 weekly, 5 monthly backups. Semesterly backups are not deleted at all.

Systemd Timer

In order to run the backup script each day, a systemd timer is used.

/etc/systemd/system/backup-manager.timer


[Unit]
Description=Timer for CurryBO Backup Manager
 
[Timer]
OnCalendar=*-*-* 4:10:10
AccuracySec=12h
Persistent=true
 
[Install]
WantedBy=multi-user.target

This timer envokes the backup-manager service at the configured time.

/etc/systemd/system/backup-manager.service


[Unit]
Description=CurryBO Backup Manager
 
[Service]
ExecStart=/bin/bash /home/currybo/create-backup.sh

The create-backup.sh script will then spin up the container with the python script.

/home/currybo/create-backup.sh


#!/bin/bash
 
CURRYBO_DB="postgres://user:password@host:5432/currybo?schema=public"
 
docker run --pull=always --name=currybo-backup-manager --network=currybo --env RC_BACKUP_PATH=/root/backups --env DATABASE_URL="$CURRYBO_DB" --mount src=/data/backups,target=/root/backups,type=bind chabicbvs001.ethz.ch/currybo-backup-manager:latest
docker rm currybo-backup-manager

Troubleshooting

Version mismatch

Make sure that the verison of the postgres client in the backup-manager container is equal or newer than the postgres servers. Otherwise, the backup will fail. The Dockerfile already adds an apt repository for newer psql versions.