pg_dumpall on a schedule, then restic to backup the dumps. I’m running Zalando Postgres in kubernetes so scheduled tasks and intercontainer networking is a bit simpler, but should be able to run a sidecar container in your compose file

+1 for long-ass backup script. First dump the databases with the appropriate command. Currently, I have only MariaDB and Postgres instances. Then, I use Borg to backup the database dumps and the docker volumes.

Database SQL dumps compress very well. I haven’t had any problems yet

I setup borg around 4 months ago using option 1. I’ve messed around with it a bit, restoring a few backups, and haven’t run into any issues with corrupt/broken databases.

I just used the example script provided by borg, but modified it to include my docker data, and write info to a log file instead of the console.

Daily at midnight, a new backup of around 427gb of data is taken. At the moment that takes 2-15min to complete, depending on how much data has changed since yesterday; though the initial backup was closer to 45min. Then old backups are trimmed; Backups <24hr old are kept, along with 7 dailys, 3 weeklys, and 6 monthlys. Anything outside that scope gets deleted.

With the compression and de-duplication process borg does; the 15 backups I have so far (5.75tb of data) currently take up 255.74gb of space. 10/10 would recommend on that aspect alone.

/edit, one note: I’m not backing up Docker volumes directly, though you could just fine. Anything I want backed up lives in a regular folder that’s then bind mounted to a docker container. (including things like paperless-ngxs databases)

I tried to find this on DDG but also had trouble so I dug it out of my docker compose

Use this docker container:

prodrigestivill/postgres-backup-local

(I have one of these for every docker compose stack/app)

It connects to your postgres and uses the pg_dump command on a schedule that you set with retention (choose how many to save)

The output then goes to whatever folder you want.

So have a main folder called docker data, this folder is backed up by borgmatic

Inside I have a folder per app, like authentik

In that I have folders like data, database, db-bak etc

Postgres data would be in Database and the output of the above dump would be in the db-bak folder.

So if I need to recover something, first step is to just copy the whole all folder and see if that works, if not I can grab a database dump and restore it into the database and see if that works. If not I can pull a db dump from any of my previous backups until I find one that works.

I don’t shutdown or stop the app container to backup the database.

In addition to hourly Borg backups for 24 hrs, I have zfs snapshots every 5 mins for an hour and the pgdump happens every hour as well. For a homelab this is probably more than sufficient

I just do the first option.

Everything is pretty much idle at 3am when the backups run, since it’s just me using the services. So I don’t really expect to have issues with DBs being backed up this way.

With restic you can pipe to stdin, so I use mysqldump and pipe it to restic:

mysqldump --defaults-file=/root/backup_scripts/.my.cnf --databases db-name | restic backup --stdin --stdin-filename db-name.sql

The .my.cnf looks like this:

[mysqldump]
user=db-user
password="databasepassword"

Snapshot with zfs, backup snapshot.

I mostly use postgres so I created myself a small docker image, which has the postgres client, restic and cron. It also gets a small bash script which executes pg_dump and then restic to backup the dump. pg_dump can be used while the database is used so no issues there. Restic stores the backup in a volume which points to an NFS share on my NAS. This script is called periodically by cron.

I use this image to start a backup-service alongside every database. So it’s part of the docker-compose.yml

I have also been wanting to try borg for at least offsite backups. Currently been using a “long ass backup script” with how little time I currently have.

I guess I’m a dummy, because I never even thought about this. Maybe I got lucky, but when I did restore from a backup, I didn’t have any issues. My containerized services came right back up like nothing was wrong. Though that may have been right before I successfully hosted my own (now defunct) Lemmy instance. I can’t remember, but I think I only had sqlite databases in my services at the time.

I guess the trouble is that you don’t want to read the volumes where the db files are because they’re not guaranteed to be consistent at a given point in time right?

Does the given engine support a backup method/utility that can be used to copy files to some volume on a set schedule?

I just started using some docker containers I found on Docker Hub designed for DB backups (e.g. prodrigestivill/postgres-backup-local) to automatically dump from the databases into a set folder, which is included in the restic backup. I know you could come up with scripts but this way, I could easily copy the compose code to other containers with different databases (and different passwords etc).

Borgmatic is an automation tool for Borg. It has hooks for database backups.

https://torsion.org/borgmatic/docs/how-to/backup-your-databases/

I just have a one-liner in crontab that keeps the last 7 nightly database dumps. That destination location is on one my my NASes, which rclones everything to my secondary NAS and an S3 bucket.

ls -tp /storage/proxmox-data/paperless/backups/*.sql.gz | grep -v '/$' | tail -n +7 | xargs -I {} rm -- {}; docker exec -t paperless-db-1 pg_dumpall -c -U paperless | gzip > /storage/proxmox-data/paperless/backups/paperless_$( date +\%Y\%m\%d )T$( date +\%H\%M\%S ).sql.gz

I use rsnapshot docker image from Linuxserver. The tool uses rsync incrementally and does rotation/ prunning for you (e.g. keep 10 days, 5 weeks, 8 months, 100 years). I just pointed it to the PostgreSQL data volume. This runs without interruption of service. To restore, I need to convert from WAL files into a dump… So, load an empty PostgreSQL container on any snapshot and run the dump command.