Database restore hooks for PostgreSQL #229

New Issue

Closed

opened 2019-10-22 23:23:15 +00:00 by witten · 2 comments

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

Owner

Copy Link

What I'm trying to do and why

For a real killer feature, the database hook feature begun in #225 could also trigger during borgmatic extract. Specifically, it could restore your databases from dumps.. and not just extract your plain 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.

Other notes / implementation ideas

For safety reasons, database restores should probably not be completely automatic when running borgmatic extract, because then it'd be easy to accidentally destroy or replace a production database. Instead, you should have to provide an explicit command-line flag to borgmatic extract that explicitly requests a database to restore (or "all"). Or maybe there should be a completely separate sub-command: borgmatic restore-postgresql.

Another consideration: When restoring, it may be necessary to include explicit support for restoring to a host other than the one that a database dump originally came from. That's because the original database host may be toast, and the user is trying to spin up a new replacement host with a different hostname. In that case, a --rewrite-hostname flag may be necessary.

Another: Should there be a way to pass arbitrary arguments through from borgmatic restore to pg_restore?

Yet another thought: If, during restores, borgmatic relies on databases being defined in the borgmatic configuration file, then that poses a bit of a problem: The user may not have backed up /etc/borgmatic/config.yaml, and yet may still want to restore their databases. Two different ideas on how to deal with this:

1. Implement a general-purpose feature for borgmatic to backup its own configuration file(s), e.g. to ~/.borgmatic/config.yaml / ~/.borgmatic/borgmatic.d/. (This would be part of a separate ticket.) Note that there are security implications to think through here, since config file can contain credentials. Then, when restoring a database, optionally first restore a config file so that the databases are known and configured.

2. Alternatively, when writing a database dump into ~/.borgmatic/postgresql_databases/..., also write out metadata about the database into a separate file (hostname, username, password(?), etc.) alongside the dump. That way, during restore, if borgmatic's config file isn't configured for any databases, you still have the connection metadata necessary to restore a given database!

#### What I'm trying to do and why For a real killer feature, the database hook feature begun in #225 could also trigger during `borgmatic extract`. Specifically, it could restore your databases from dumps.. and not just extract your plain 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. #### Other notes / implementation ideas For safety reasons, database restores should probably not be completely automatic when running `borgmatic extract`, because then it'd be easy to accidentally destroy or replace a production database. Instead, you should have to provide an explicit command-line flag to `borgmatic extract` that explicitly requests a database to restore (or "all"). Or maybe there should be a completely separate sub-command: `borgmatic restore-postgresql`. Another consideration: When restoring, it may be necessary to include explicit support for restoring to a host other than the one that a database dump originally came from. That's because the original database host may be toast, and the user is trying to spin up a new replacement host with a different hostname. In that case, a `--rewrite-hostname` flag may be necessary. Another: Should there be a way to pass arbitrary arguments through from `borgmatic restore` to `pg_restore`? Yet another thought: If, during restores, borgmatic relies on databases being defined in the borgmatic configuration file, then that poses a bit of a problem: The user may not have backed up `/etc/borgmatic/config.yaml`, and yet may still want to restore their databases. Two different ideas on how to deal with this: 1. Implement a general-purpose feature for borgmatic to backup its own configuration file(s), e.g. to `~/.borgmatic/config.yaml` / `~/.borgmatic/borgmatic.d/`. (This would be part of a separate ticket.) Note that there are security implications to think through here, since config file can contain credentials. Then, when restoring a database, optionally first restore a config file so that the databases are known and configured. 2. Alternatively, when writing a database dump into `~/.borgmatic/postgresql_databases/...`, also write out metadata about the database into a separate file (hostname, username, password(?), etc.) alongside the dump. That way, during restore, if borgmatic's config file isn't configured for any databases, you still have the connection metadata necessary to restore a given database!

witten referenced this issue from a commit 2019-10-31 19:59:13 +00:00

Restore backed up PostgreSQL databases via "borgmatic restore" sub-command (#229).

witten commented 2019-11-01 04:21:04 +00:00

Author

Owner

Copy Link

Yet another thought: If, during restores, borgmatic relies on databases being defined in the borgmatic configuration file, then that poses a bit of a problem

Decided to just document around this for now: Encouraging users to backup their borgmatic configuration files.

> Yet another thought: If, during restores, borgmatic relies on databases being defined in the borgmatic configuration file, then that poses a bit of a problem Decided to just document around this for now: Encouraging users to backup their borgmatic configuration files.

witten referenced this issue from a commit 2019-11-01 05:06:28 +00:00

Documentation for database restores (#229).

witten closed this issue 2019-11-01 06:14:00 +00:00

witten commented 2019-11-01 06:14:58 +00:00

Author

Owner

Copy Link

Released in 1.4.1.

Released in 1.4.1.

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

1 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#229

No description provided.