Database dump hooks ¶
If you want to backup a database, it's best practice with most database systems to backup an exported database dump, rather than backing up your database's internal file storage. That's because the internal storage can change while you're reading from it. In contrast, a database dump creates a consistent snapshot that is more suited for backups.
Fortunately, borgmatic includes built-in support for creating database dumps prior to running backups. For example, here is everything you need to dump and backup a couple of local PostgreSQL databases and a MySQL/MariaDB database:
- name: users
- name: orders
- name: posts
Prior to each backup, borgmatic dumps each configured database to a file and includes it in the backup. After the backup completes, borgmatic removes the database dump files to recover disk space.
borgmatic creates these temporary dump files in
~/.borgmatic by default. To
customize this path, set the
borgmatic_source_directory option in the
location section of borgmatic's configuration.
Here's a more involved example that connects to remote databases:
- name: users
- name: posts
If you want to dump all databases on a host, use
all for the database name:
- name: all
- name: all
Note that you may need to use a
username of the
postgres superuser for
this to work with PostgreSQL.
Configuration backups ¶
An important note about this database configuration: You'll need the configuration to be present in order for borgmatic to restore a database. So to prepare for this situation, it's a good idea to include borgmatic's own configuration files as part of your regular backups. That way, you can always bring back any missing configuration files in order to restore a database.
Supported databases ¶
As of now, borgmatic supports PostgreSQL and MySQL/MariaDB databases directly. But see below about general-purpose preparation and cleanup hooks as a work-around with other database systems. Also, please file a ticket for additional database systems that you'd like supported.
Database restoration ¶
To restore a database dump from an archive, use the
action. But the first step is to figure out which archive to restore from. A
good way to do that is to use the
list action? Try the old-style
--list, or upgrade
That should yield output looking something like:
host-2019-01-01T04:05:06.070809 Tue, 2019-01-01 04:05:06 [...]
host-2019-01-02T04:06:07.080910 Wed, 2019-01-02 04:06:07 [...]
Assuming that you want to restore all database dumps from the archive with the most up-to-date files and therefore the latest timestamp, run a command like:
borgmatic restore --archive host-2019-01-02T04:06:07.080910
restore action? Upgrade borgmatic!)
With newer versions of borgmatic, you can simplify this to:
borgmatic restore --archive latest
--archive value is the name of the archive to restore from. This
restores all databases dumps that borgmatic originally backed up to that
This is a destructive action!
borgmatic restore replaces live databases by
restoring dumps from the selected archive. So be very careful when and where
you run it.
Repository selection ¶
If you have a single repository in your borgmatic configuration file(s), no
restore action figures out which repository to use.
But if you have multiple repositories configured, then you'll need to specify the repository path containing the archive to restore. Here's an example:
borgmatic restore --repository repo.borg --archive host-2019-...
Restore particular databases ¶
If you've backed up multiple databases into an archive, and you'd only like to
restore one of them, use the
--database flag to select one or more
databases. For instance:
borgmatic restore --archive host-2019-... --database users
There are a few important limitations with borgmatic's current database restoration feature that you should know about:
- You must restore as the same Unix user that created the archive containing the database dump. That's because the user's home directory path is encoded into the path of the database dump within the archive.
- As mentioned above, borgmatic can only restore a database that's defined in borgmatic's own configuration file. So include your configuration file in backups to avoid getting caught without a way to restore a database.
- borgmatic does not currently support backing up or restoring multiple databases that share the exact same name on different hosts.
Manual restoration ¶
If you prefer to restore a database without the help of borgmatic, first
archive containing a database dump, and then manually restore the dump file
found within the extracted
~/.borgmatic/ path (e.g. with
Preparation and cleanup hooks ¶
If this database integration is too limited for needs, borgmatic also supports general-purpose preparation and cleanup hooks. These hooks allows you to trigger arbitrary commands or scripts before and after backups. So if necessary, you can use these hooks to create database dumps with any database system.
MySQL table lock errors ¶
If you encounter table lock errors during a database dump with MySQL/MariaDB,
you may need to use a
You can add any additional flags to the
options: in your database
configuration. Here's an example:
- name: posts
options: "--single-transaction --quick"
Related documentation ¶
- Set up backups with borgmatic
- Add preparation and cleanup steps to backups
- Inspect your backups
- Extract a backup
Improve this documentation
Have an idea on how to make this documentation even better? Send your feedback below! But if you need help with borgmatic, or have an idea for a borgmatic feature, please use our issue tracker instead.