SQLite Backup Support #295

Closed
opened 2020-02-27 13:43:32 +00:00 by pixelyo Β· 12 comments

What I'm trying to do and why

I would like to backup SQLite Databases, because I like to backup things.

Other notes / implementation ideas

This is probably a feature request.

#### What I'm trying to do and why I would like to backup SQLite Databases, because I like to backup things. #### Other notes / implementation ideas This is probably a feature request.
Owner

This is a great idea.. Thanks for suggesting it! Seems like it'd be pretty straight-forward to implement too.

Is it the case that you'd be okay declaring each SQLite database that you'd like backed up in borgmatic's configuration file? Or were you thinking auto-discovery?

This is a great idea.. Thanks for suggesting it! Seems like it'd be pretty straight-forward to implement too. Is it the case that you'd be okay declaring each SQLite database that you'd like backed up in borgmatic's configuration file? Or were you thinking auto-discovery?
Author

For my use case it is sufficient to define the location of the SQLite in the configuration file. :)

For my use case it is sufficient to define the location of the SQLite in the configuration file. :)

Curious if there has been any movement on this? Would be a bit tempted to have a go at a PR if not

Curious if there has been any movement on this? Would be a bit tempted to have a go at a PR if not
Owner

No movement on this one! Please feel free to have a go, and let me know if I can provide any help. A good place to look at for inspiration is the existing borgmatic/hooks/mysql.py hook.

No movement on this one! Please feel free to have a go, and let me know if I can provide any help. A good place to look at for inspiration is the existing `borgmatic/hooks/mysql.py` hook.
Owner

More specifically, these are the functions you'll need to develop within a borgmatic/hooks/sqlite.py:

  • dump_databases()
  • remove_database_dumps()
  • make_database_dump_pattern()
  • restore_database_dump()

Note the contents of DATABASE_HOOK_NAMES in borgmatic/hooks/dump.py. That'll need an update. Similiar for HOOK_NAME_TO_MODULE in borgmatic/hooks/dispatch.py.

borgmatic assumes database dumps can be streamed (for both dumping and restores), rather than dumping to a file / restoring from a file.

For dumping, maybe that'd look something like:

sqlite3 -batch path/to/dump/from.db << EOF
  .dump
Β Β .quit
EOF
More specifically, these are the functions you'll need to develop within a `borgmatic/hooks/sqlite.py`: * `dump_databases()` * `remove_database_dumps()` * `make_database_dump_pattern()` * `restore_database_dump()` Note the contents of `DATABASE_HOOK_NAMES` in `borgmatic/hooks/dump.py`. That'll need an update. Similiar for `HOOK_NAME_TO_MODULE` in `borgmatic/hooks/dispatch.py`. borgmatic assumes database dumps can be streamed (for both dumping and restores), rather than dumping to a file / restoring from a file. For dumping, maybe that'd look something like: ```bash sqlite3 -batch path/to/dump/from.db << EOF .dump Β Β .quit EOF ```
Owner
Also relevant: https://stackoverflow.com/questions/41744297/do-the-sqlite3-backup-and-dump-commands-lock-the-database
witten added the
good first issue
label 2023-02-04 22:06:51 +00:00
Collaborator

hey @witten
I have followed the discussion and am able to get a first draft running with the following schema:

sqlite_databases:
                type: array
                items:
                    type: object
                    required: ['path']
                    additionalProperties: false
                    properties:
                        path:
                            type: string
                            description: Absolute path to the SQLite database file to dump.
                            example: /var/lib/sqlite/users.db

If the issue is as straightforward as following this discussion, I would like to work on it, I'll ask questions if I get stuck.

hey @witten I have followed the discussion and am able to get a first draft running with the following schema: ```yaml sqlite_databases: type: array items: type: object required: ['path'] additionalProperties: false properties: path: type: string description: Absolute path to the SQLite database file to dump. example: /var/lib/sqlite/users.db ``` If the issue is as straightforward as following this discussion, I would like to work on it, I'll ask questions if I get stuck.
Owner

That schema looks like it'd work! And yes, I think the overall implementation should hopefully be pretty straightforward. I'd be happy to help if you do get stuck.

That schema looks like it'd work! And yes, I think the overall implementation should hopefully be pretty straightforward. I'd be happy to help if you do get stuck.
Collaborator

Could you help me with writing the tests πŸ˜…, especially the docker part? I want to add a sqlite file, then check if it was backedup and restored successfully. There's not much configuration involved with sqlite, so I am guessing the test_sqlite.py file won't be that long.
Also, should I add checking for whether the file that was restored is the same as the old one or not?

Could you help me with writing the tests πŸ˜…, especially the docker part? I want to add a sqlite file, then check if it was backedup and restored successfully. There's not much configuration involved with sqlite, so I am guessing the `test_sqlite.py` file won't be that long. Also, should I add checking for whether the file that was restored is the same as the old one or not?
Owner

First of all, I'd recommend doing most of the tests on individual functions as plain ol' unit tests rather than anything that requires Docker. So for instance have a look at tests/unit/hooks/test_mysql.py for several examples. The idea is you mock out pretty much everything a function under test calls, and then assert that the function returns the expected result. You'll also see the use of .once() to assert that particular calls were made at least once. I'd be happy to help if you get stuck on writing any test function.

As for the end-to-end tests (the Docker part), you can probably get away with just adding to the database configuration within tests/end-to-end/test_database.py:write_configuration() and letting the existing tests in that file handle doing a test dump/restore with that added sqlite config. You may need to add sqlite to the Docker image though if it's not already installed. (I believe scripts/run-full-tests is where that happens, specifically the first apk add.)

Also, should I add checking for whether the file that was restored is the same as the old one or not?

I don't think you need to worry about that level of detail at this point. Good question though!

I hope some of this helps. As always, let me know if you have any questions.

First of all, I'd recommend doing most of the tests on individual functions as plain ol' unit tests rather than anything that requires Docker. So for instance have a look at `tests/unit/hooks/test_mysql.py` for several examples. The idea is you mock out pretty much everything a function under test calls, and then assert that the function returns the expected result. You'll also see the use of `.once()` to assert that particular calls were made at least once. I'd be happy to help if you get stuck on writing any test function. As for the end-to-end tests (the Docker part), you can probably get away with just adding to the database configuration within `tests/end-to-end/test_database.py:write_configuration()` and letting the *existing* tests in that file handle doing a test dump/restore with that added sqlite config. You may need to add sqlite to the Docker image though if it's not already installed. (I believe `scripts/run-full-tests` is where that happens, specifically the first `apk add`.) > Also, should I add checking for whether the file that was restored is the same as the old one or not? I don't think you need to worry about that level of detail at this point. Good question though! I hope some of this helps. As always, let me know if you have any questions.
Owner

Thanks to @diivi, this feature has been implemented in master! It will ship as part of the next release. Here's an example of a configuration file with this in use:

hooks:
   sqlite_databases:
       - name: mydb
         path: /var/lib/sqlite3/mydb.sqlite
Thanks to @diivi, this feature has been [implemented](https://github.com/borgmatic-collective/borgmatic/pull/50) in master! It will ship as part of the next release. Here's an example of a configuration file with this in use: ```yaml hooks: sqlite_databases: - name: mydb path: /var/lib/sqlite3/mydb.sqlite ```
Owner

Just released in borgmatic 1.7.9!

Just released in borgmatic 1.7.9!
Sign in to join this conversation.
No Milestone
No Assignees
4 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: borgmatic-collective/borgmatic#295
No description provided.