Automated database backups

There’s a backup script both in the beta and prod servers that:

Creates a zipped (.sql.gz) backup of the database. The zipped version is significantly more lightweight than the non-zipped version.
Copies backup to the local backups directory.
Copies backup to the cloud (DigitalOcean space) using rclone.
Deletes files older than the last 10 days in the local backups folder to preserve disk space.

The script is located in ~/scripts/backup-database/backup-database.sh, and there’s a cronjob added to crontab which executes the script at 00:00 every night and stores the logs in /var/tmp/backup-database.log.

In the same folder, there’s an rclone.conf file which contains the authentication data for rclone to be able to access the DigitalOcean space.

The code for the script is:

# Based on https://coderbook.com/@marcus/how-to-automatically-backup-postgres-to-s3-and-cloud/ 

# Requires rclone to be installed in server

# Create directories only if they don't already exist

mkdir -p ~/latest-backup

mkdir -p ~/backups

# Generate latest backup and save compressed file in latest backup directory

echo "Creating new database backup..."

docker exec litefarm-db pg_dump --inserts -O -U lfdbbufpp pg-litefarm | gzip > ~/latest-backup/backup-$(date +%d-%m-%y-%H-%M).sql.gz

# Copy to backups directory

echo "Copying backup to local backups directory..."

cp -a ~/latest-backup/. ~/backups/

# Copy to cloud (DigitalOcean spaces)

echo "Copying backup to cloud..."

/snap/bin/rclone --config ~/scripts/backup-database/rclone.conf copy --progress ~/latest-backup "spaces:litefarm-db-backups/beta/"

# Clean out latest backup directory for next time

echo "Cleaning up latest backup..."

rm -rf ~/latest-backup/*

# Remove backups older than 10 days

# echo "Cleaning up old backups..."

find ~/backups/ -type f -name "*.sql.gz" -mtime +10 -delete

The line that uses rclone is slightly different in beta and prod due to the rclone installation living in different directories (not sure why this happened, but it could be fixed if needed).

The backups are stored in DigitalOcean in a space called litefarm-db-backups.