Database dump hooks for PostgreSQL #225

New Issue

Closed

opened 2019-10-19 21:47:45 +00:00 by witten · 6 comments

witten commented 2019-10-19 21:47:45 +00:00

Owner

Copy Link

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:

hooks:
     postgresql:
         databases:
             - orders
             - invoices
         format: tar
         dump_path: /var/lib/backups

(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, and rm 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.

#### 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](https://torsion.org/borgmatic/docs/how-to/add-preparation-and-cleanup-steps-to-backups/). 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: ```yaml hooks: postgresql: databases: - orders - invoices format: tar dump_path: /var/lib/backups ``` (This is in part inspired by [backupninja](https://www.0xacab.org/riseuplabs/backupninja)'s [database support](https://www.0xacab.org/riseuplabs/backupninja/blob/master/examples/example.pgsql).) The idea is that with this high-level configuration in place, borgmatic would automatically `pg_dump` your databases prior to each backup, and `rm` 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.

witten commented 2019-10-20 04:10:56 +00:00

Author

Owner

Copy Link

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 the postgres 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.

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 the `postgres` 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*.

witten commented 2019-10-20 04:19:11 +00:00

Author

Owner

Copy Link

Take two on a proposed config to deal with some of the above requirements:

hooks:
     postgresql:
         - database: orders
           hostname: dbhost
           username: dbuser
           password: trustsome1
           format: tar
           dump_path: /var/lib/backups

         - database: invoices
           format: tar

Take two on a proposed config to deal with some of the above requirements: ```yaml hooks: postgresql: - database: orders hostname: dbhost username: dbuser password: trustsome1 format: tar dump_path: /var/lib/backups - database: invoices format: tar ```

decentral1se commented 2019-10-20 10:58:49 +00:00

Contributor

Copy Link

Take two on a proposed config

Looking very sharp indeed!

So, we could intially aim for support for:

• postgresql
• mariadb

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!

> Take two on a proposed config Looking very sharp indeed! So, we could intially aim for support for: * postgresql * mariadb 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!

witten commented 2019-10-21 00:03:37 +00:00

Author

Owner

Copy Link

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:

• A prompt-based wizard, or:
• No wizard at all, but just some nice command-line flags that optionally alter the generated config based on your needs.

Might want to open a separate ticket for this general class of ideas?

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: * A prompt-based wizard, or: * No wizard at all, but just some nice command-line flags that optionally alter the generated config based on your needs. Might want to open a separate ticket for this general class of ideas?

witten changed title from Database dump/restore hooks to Database dump/restore hooks for PostgreSQL 2019-10-21 00:03:55 +00:00

witten referenced this issue from a commit 2019-10-21 22:20:06 +00:00

Config generation support for sequences of maps, needed for database dump hooks (#225).

witten referenced this issue from a commit 2019-10-21 22:53:44 +00:00

Move hooks into directory, so there can be one source file per type of hook (#225).

witten changed title from Database dump/restore hooks for PostgreSQL to Database dump hooks for PostgreSQL 2019-10-22 23:19:04 +00:00

witten commented 2019-10-22 23:23:33 +00:00

Author

Owner

Copy Link

Split off the restore portion to #229 to keep this ticket somewhat scoped down.

Split off the restore portion to #229 to keep this ticket somewhat scoped down.

witten referenced this issue from a commit 2019-10-22 23:29:48 +00:00

Database dump hooks for PostgreSQL, so you can easily dump your databases before backups run (#225).

witten referenced this issue from a commit 2019-10-23 04:40:56 +00:00

Tests for database dumping (#225).

witten referenced this issue from a commit 2019-10-23 22:36:53 +00:00

Database dump hooks documentation (#225).

witten commented 2019-10-23 22:52:36 +00:00

Author

Owner

Copy Link

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.

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.

witten closed this issue 2019-10-23 22:52:36 +00:00

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

main

spot-check

1.8.10

1.8.9

1.8.8

1.8.7

1.8.6

1.8.5

1.8.4

1.8.3

1.8.2

1.8.1

1.8.0

1.7.15

1.7.14

1.7.13

1.7.12

1.7.11

1.7.10

1.7.9

1.7.8

1.7.7

1.7.6

1.7.5

1.7.4

1.7.3

1.7.2

1.7.1

1.7.0

1.6.6

1.6.5

1.6.4

1.6.3

1.6.2

1.6.1

1.6.0

1.5.24

1.5.23

1.5.22

1.5.21

1.5.20

1.5.19

1.5.18

1.5.17

1.5.16

1.5.15

1.5.14

1.5.13

1.5.12

1.5.11

1.5.10

1.5.9

1.5.8

1.5.7

1.5.7.dev0

1.5.6

1.5.5

1.5.4

1.5.3

1.5.2

1.5.1

1.5.0

1.4.22

1.4.21

1.4.20

1.4.19

1.4.18

1.4.17

1.4.16

1.4.15

1.4.14

1.4.13

1.4.12

1.4.11

1.4.10

1.4.9

1.4.8

1.4.7

1.4.6

1.4.5

1.4.4

1.4.3

1.4.2

1.4.1

1.4.0

1.3.26

1.3.25

1.3.24

1.3.23

1.3.22

1.3.21

1.3.20

1.3.19

1.3.18

1.3.17

1.3.16

1.3.15

1.3.14

1.3.13

1.3.12

1.3.11

1.3.10

1.3.9

1.3.8

1.3.7

1.3.6

1.3.5

1.3.4

1.3.3

1.3.2

1.3.1

1.3.0

1.2.18

1.2.17

1.2.16

1.2.15

1.2.14

1.2.13

1.2.12

1.2.11

1.2.10

1.2.9

1.2.8

1.2.7

1.2.6

1.2.5

1.2.4

1.2.3

1.2.2

1.2.1

1.2.0

1.1.15

1.1.14

1.1.13

1.1.12

1.1.11

1.1.10

1.1.9

1.1.7

1.1.6

1.1.5

1.1.4

1.1.3

1.1.2

1.1.1

1.1.0

1.0.3

1.0.2

1.0.1

1.0.0

0.1.8

0.1.7

0.1.6

0.1.5

0.1.4

0.1.3

0.1.2

0.1.1

0.1.0

0.0.7

0.0.6

0.0.5

0.0.4

0.0.3

0.0.2

Labels

Clear labels   

bug

  

data loss

  

design finalized

  

good first issue

  

new feature area

  

question / support

  

security

  

waiting for response

No Label

bug

data loss

design finalized

good first issue

new feature area

question / support

security

waiting for response

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

2 Participants

Notifications

Subscribe

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#225

No description provided.