Selfhosted

40006 readers

574 users here now

A place to share alternatives to popular online services that can be self-hosted without giving up privacy or locking you into a service you don't control.

Rules:

Be civil: we're here to support and learn from one another. Insults won't be tolerated. Flame wars are frowned upon.
No spam posting.
Posts have to be centered around self-hosting. There are other communities for discussing hardware or home computing. If it's not obvious why your post topic revolves around selfhosting, please include details to make it clear.
Don't duplicate the full text of your blog or github here. Just post the link for folks to click.
Submission headline should match the article title (don’t cherry-pick information from the title to fit your agenda).
No trolling.

Resources:

selfh.st Newsletter and index of selfhosted software and apps
awesome-selfhosted software
awesome-sysadmin resources
Self-Hosted Podcast from Jupiter Broadcasting

Any issues on the community? Report it using the report flag.

Questions? DM the mods!

founded 1 year ago

MODERATORS

[email protected]

How do y'all backup docker databases with backup programs like Borg/Restic? (lemmy.blahaj.zone)

submitted 5 months ago* (last edited 5 months ago) by [email protected] to c/[email protected]

54 comments fedilink hide all child comments

Edit: Results tabulated, thanks for all y'alls input!

Results fitting within the listed categories

Just do it live

Backup while it is expected to be idle @[email protected] @[email protected] @[email protected]
@[email protected] suggested adding a real long-ass-backup-script to run monthly to limit overall downtime

Shut down all database containers

Shutdown all containers -> backup @[email protected]
Leveraging NixOS impermanence, reboot once a day and backup @[email protected]

Long-ass backup script

Long-ass backup script leveraging a backup method in series @[email protected] @[email protected]

Mythical database live snapshot command

(it seems pg_dumpall for Postgres and mysqldump for mysql (though some images with mysql don't have that command for meeeeee))

Dump Postgres via pg_dumpall on a schedule, backup normally on another schedule @[email protected]
Dump mysql via mysqldump and pipe to restic directly @[email protected]
Dump Postgres via pg_dumpall -> backup -> delete dump @[email protected] @[email protected]

Docker image that includes Mythical database live snapshot command (Postgres only)

Make your own docker image (https://gitlab.com/trubeck/postgres-backup) and set to run on a schedule, includes restic so it backs itself up @[email protected] (thanks for uploading your scripts!!)
Add docker image prodrigestivill/postgres-backup-local and set to run on a schedule, backup those dumps on another schedule @[email protected] @[email protected] (also recommended additionally backing up the running database and trying that first during a restore)

New catagories

Snapshot it, seems to act like a power outage to the database

LVM snapshot -> backup that @[email protected]
ZFS snapshot -> backup that @[email protected] (real world recovery experience shows that databases act like they're recovering from a power outage and it works)
(I assume btrfs snapshot will also work)

One liner self-contained command for crontab

One-liner crontab that prunes to maintain 7 backups, dump Postgres via pg_dumpall, zips, then rclone them @[email protected]

Turns out Borgmatic has database hooks

Borgmatic with its explicit support for databases via hooks (autorestic has hooks but it looks like you have to make database controls yourself) @[email protected]

I've searched this long and hard and I haven't really seen a good consensus that made sense. The SEO is really slowing me on this one, stuff like "restic backup database" gets me garbage.

I've got databases in docker containers in LXC containers, but that shouldn't matter (I think).

me-me about containers in containers

a me-me using the mental gymnastics me-me template; the template is split into two sections with the upper being a simple 3-step gymnastic routine while the bottom has the one being mocked flipping on gymnastic bars, using gymnastic rings, a balance beam, before finally jetpacking over a burning car. The top says "docker compose up -d" in line with the 3 simple steps of the routine, while the bottom, while becoming increasingly more cluttered, says "pass uid/gid to LXC", "add storage devices to LXC", "proxy network", "install docker on every container", and finally "docker compose up -d".

I've seen:

Just backup the databases like everything else, they're "transactional" so it's cool
Some extra docker image to load in with everything else that shuts down the databases in docker so they can be backed up
Shut down all database containers while the backup happens
A long ass backup script that shuts down containers, backs them up, and then moves to the next in the script
Some mythical mentions of "database should have a command to do a live snapshot, git gud"

None seem turnkey except for the first, but since so many other options exist I have a feeling the first option isn't something you can rest easy with.

I'd like to minimize backup down times obviously, like what if the backup for whatever reason takes a long time? I'd denial of service myself trying to backup my service.

I'd also like to avoid a "long ass backup script" cause autorestic/borgmatic seem so nice to use. I could, but I'd be sad.

So, what do y'all do to backup docker databases with backup programs like Borg/Restic?

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 4 points 5 months ago* (last edited 5 months ago) (1 children)

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"

[–] [email protected] 2 points 5 months ago

Thanks for the incantation! Its looking like something like this is gonna be the way to do it