Database dump hooks for PostgreSQL #225
Labels
No Label
bug
data loss
design finalized
good first issue
new feature area
question / support
security
waiting for response
No Milestone
No Assignees
2 Participants
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: borgmatic-collective/borgmatic#225
Loading…
Reference in New Issue
No description provided.
Delete Branch "%!s(<nil>)"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
What I'm trying to do and why
If you want to run borgmatic on a system with a database daemon, best practices dictate that you dump the database to file and back that up instead of trying to backup the running database. That way, you get a consistent snapshot.
Today, you can already do that with borgmatic via hand-written
before_backup
/after_backup
hooks. But what if it could be even easier / more built-in, such that you didn't have to write custom hooks with dump commands?See #228 for a variant of this ticket for MariaDB.
Implementation ideas
Consider a feature that might be configured as follows:
(This is in part inspired by backupninja's database support.)
The idea is that with this high-level configuration in place, borgmatic would automatically
pg_dump
your databases prior to each backup, andrm
the dump afterwards. And then you could include the dump directory/var/lib/backups
in your source directories (or maybe that'd get injected automatically), and all your database dumps would get backed up.For a real killer feature, this new feature could also trigger during
borgmatic restore
. Specifically, it would either optionally or automatically restore your databases from dumps.. not just your files! So if you're restoring on a completely fresh system, you could get up and running relatively quickly—including your databases—without no manual work.It occurs to me that this configuration would probably need optional support for connecting to the database with a given username, password, and database host. It's not always the case that borgmatic would have direct access to
pg_dump
as thepostgres
superuser.For instance, consider the case where borgmatic is running in a Docker container separate from the database container. In that scenario, in order to initiate a database dump, borgmatic would have to connect to the Docker hostname corresponding to the database container, and use the (probably non-superuser) database credentials to connect.
If this is part of the requirements, then that suggests the sample config above is insufficient, as the user may need to specify credentials per database.
Take two on a proposed config to deal with some of the above requirements:
Looking very sharp indeed!
So, we could intially aim for support for:
Continuing on this line of thought, the idea of "ninjahelper" (see https://0xacab.org/riseuplabs/backupninja#ninjahelper) is also quite appealing and might be useful after this functionality goes in. Ncurses magic!
PostgreSQL and MariaDB make sense as a place to start. I can look into PostgreSQL first if you want to take a stab at MariaDB either during or after (#228). I'll convert this ticket into PostgreSQL only to make it more scoped.
Interesting that you mention ninjahelper. I had been thinking vaguely of similar functionality in
generate-borgmatic-config
. There are also less ambitious variants than a full ncurses wizard:Might want to open a separate ticket for this general class of ideas?
Database dump/restore hooksto Database dump/restore hooks for PostgreSQLDatabase dump/restore hooks for PostgreSQLto Database dump hooks for PostgreSQLSplit off the restore portion to #229 to keep this ticket somewhat scoped down.
Just released in 1.4.0! Docs here: https://torsion.org/borgmatic/docs/how-to/backup-your-databases/
Feedback welcome as always. And we can tweak the config format if necessary.