#326 Allow restoring to other database server

Open
opened 4 months ago by brskq · 4 comments
brskq commented 4 months ago

What I’m trying to do and why

We’re using Amazon RDS clusters for our PostgreSQL databases. Our setup is fairly simple, with one R/W and one R/O node. We want to take backups of the R/O node to keep the load of the R/W as low as possible, but the obviously prevents us from restoring using the functionality of Borgmatic.

Other notes / implementation ideas

In the configuration, allow specifying e.g. ‘restore_hostname’ of the database server to which the backup should be restored. Could perhaps even be useful to also have ‘restore_port’, ‘restore_username’ and ‘restore_password’. ‘restore_foo’ entries should not be required.

hooks:
    postgresql_databases:
        - name: database_one
          hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com
          port: 5432
          username: database_one_user
          password: database_one_pass
          restore_hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com
          restore_port: 1234
          restore_username: database_one_user2
          restore_password: database_one_pass2
        - name: database_two
          hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com
          port: 5432
          username: database_two_user
          password: database_two_pass
          restore_hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com

Environment

borgmatic version: 1.5.6

borgmatic installation method: pip

Borg version: 1.1.13

Python version: 3.7.6

operating system and version: Amazon Linux 2

#### What I'm trying to do and why We're using Amazon RDS clusters for our PostgreSQL databases. Our setup is fairly simple, with one R/W and one R/O node. We want to take backups of the R/O node to keep the load of the R/W as low as possible, but the obviously prevents us from restoring using the functionality of Borgmatic. #### Other notes / implementation ideas In the configuration, allow specifying e.g. 'restore_hostname' of the database server to which the backup should be restored. Could perhaps even be useful to also have 'restore_port', 'restore_username' and 'restore_password'. 'restore_foo' entries should not be required. ``` hooks: postgresql_databases: - name: database_one hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com port: 5432 username: database_one_user password: database_one_pass restore_hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com restore_port: 1234 restore_username: database_one_user2 restore_password: database_one_pass2 - name: database_two hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com port: 5432 username: database_two_user password: database_two_pass restore_hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com ``` #### Environment **borgmatic version:** 1.5.6 **borgmatic installation method:** pip **Borg version:** 1.1.13 **Python version:** 3.7.6 **operating system and version:** Amazon Linux 2
witten commented 4 months ago
Owner

Interesting idea! There are a couple of related database restore enhancement tickets: #322 and #309. For those, I was imagining that restore overrides like setting the restore user or database name would be command-line borgmatic restore flags. But here you have them as configuration file options, which is something I hadn’t thought of.

It sounds like for your use case, these restore values would be static enough that it’d be preferrable to put them in the configuration file rather than specifying them on a borgmatic restore command-line? (I could potentially see supporting both.)

Also, here’s a riff on your idea that just occurred to me:

hooks:
    postgresql_databases:
        - name: database_one
          hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com
          port: 5432
          username: database_one_user
          password: database_one_pass
        - name: database_one_rw
          type: restore_only
          hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com
          port: 1234
          username: database_one_user2
          password: database_one_pass2
        - name: database_two
          hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com
          port: 5432
          username: database_two_user
          password: database_two_pass
        - name: database_two_rw
          type: restore_only
          hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com
          port: 5432
          username: database_two_user
          password: database_two_pass          

And then you would make use of it with something like:

borgmatic restore --archive latest --database database_one --restore-to database_one_rw

I don’t feel particularly strongly about this either way. The one benefit of this approach is it’d allow a little more flexibility about which database you restore to. Which is perhaps not needed in your use case.

Thoughts?

Interesting idea! There are a couple of related database restore enhancement tickets: #322 and #309. For those, I was imagining that restore overrides like setting the restore user or database name would be command-line `borgmatic restore` flags. But here you have them as configuration file options, which is something I hadn't thought of. It sounds like for your use case, these restore values would be static enough that it'd be preferrable to put them in the configuration file rather than specifying them on a `borgmatic restore` command-line? (I could potentially see supporting both.) Also, here's a riff on your idea that just occurred to me: ```yaml hooks: postgresql_databases: - name: database_one hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com port: 5432 username: database_one_user password: database_one_pass - name: database_one_rw type: restore_only hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com port: 1234 username: database_one_user2 password: database_one_pass2 - name: database_two hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com port: 5432 username: database_two_user password: database_two_pass - name: database_two_rw type: restore_only hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com port: 5432 username: database_two_user password: database_two_pass ``` And then you would make use of it with something like: ```bash borgmatic restore --archive latest --database database_one --restore-to database_one_rw ``` I don't feel particularly strongly about this either way. The one benefit of this approach is it'd allow a little more flexibility about which database you restore to. Which is perhaps not needed in your use case. Thoughts?
brskq commented 4 months ago
Poster

It sounds like for your use case, these restore values would be static enough that it’d be preferrable to put them in the configuration file rather than specifying them on a borgmatic restore command-line?

The values for the restore (R/W) endpoint are just as static as the backup endpoint (RO) as they are in a cluster which syncs all database transactions from the R/W to RO. This means that all users on R/W certainly exists on RO as RO is pulling from R/W.

Your suggestion would work in this case and would be pretty easy to implement from our end (as we automate the config generation). However, I see value in storing the restore parameters in the same entry.

Picture the following config:

hooks:
    postgresql_databases:
        - name: database_one
          hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com
          port: 5432
          username: database_one_user
          password: database_one_pass
          restore_hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com

Example 1:

In the case we want to perform a restore to the default resore endpoint we could simply do:

borgmatic restore --archive latest --database 'database_one'

The above assumes:

  • --restore-hostname 'database.cluster-foobar.aws-region-1.rds.amazonaws.com' from restore_hostname
  • --restore-port 5432 from port
  • --restore-user 'database_restore_user' from user
  • --restore-pass 'database_restore_pass' from pass

Example 2:

In the case we want to perform a restore to the default resore endpoint but to another user we could do:

borgmatic restore --archive latest --database 'database_one' --restore-user 'database_restore_user' --restore-pass 'database_restore_pass'

The above assumes:

  • --restore-hostname 'database.cluster-foobar.aws-region-1.rds.amazonaws.com' from restore_hostname
  • --restore-port 5432 from port

Example 3:

In the case we want to perform a restore to another resore endpoint we could do:

borgmatic restore --archive latest --database 'database_one' --restore-hostname 'basedata.cluster-foobar.aws-region-1.rds.amazonaws.com'

The above assumes:

  • --restore-port 5432 from port
  • --restore-user 'database_restore_user' from user
  • --restore-pass 'database_restore_pass' from pass

The config above has the fictional restore_hostname in the config, but let’s be clear and say that it shouldn’t be mandatory. restore_hostname should default to hostname.

P.S. Last day before vacation. Hope I described the scenarios well enough ^^. Thanks for the good work!

> It sounds like for your use case, these restore values would be static enough that it'd be preferrable to put them in the configuration file rather than specifying them on a borgmatic restore command-line? The values for the restore (R/W) endpoint are just as static as the backup endpoint (RO) as they are in a cluster which syncs all database transactions from the R/W to RO. This means that all users on R/W certainly exists on RO as RO is pulling from R/W. Your suggestion would work in this case and would be pretty easy to implement from our end (as we automate the config generation). However, I see value in storing the restore parameters in the same entry. Picture the following config: ``` hooks: postgresql_databases: - name: database_one hostname: database.cluster-ro-foobar.aws-region-1.rds.amazonaws.com port: 5432 username: database_one_user password: database_one_pass restore_hostname: database.cluster-foobar.aws-region-1.rds.amazonaws.com ``` **Example 1:** In the case we want to perform a restore to the default resore endpoint we could simply do: ``` borgmatic restore --archive latest --database 'database_one' ``` The above assumes: * `--restore-hostname 'database.cluster-foobar.aws-region-1.rds.amazonaws.com'` from `restore_hostname` * `--restore-port 5432` from `port` * `--restore-user 'database_restore_user'` from `user` * `--restore-pass 'database_restore_pass'` from `pass` **Example 2:** In the case we want to perform a restore to the default resore endpoint but to another user we could do: ``` borgmatic restore --archive latest --database 'database_one' --restore-user 'database_restore_user' --restore-pass 'database_restore_pass' ``` The above assumes: * `--restore-hostname 'database.cluster-foobar.aws-region-1.rds.amazonaws.com'` from `restore_hostname` * `--restore-port 5432` from `port` **Example 3:** In the case we want to perform a restore to another resore endpoint we could do: ``` borgmatic restore --archive latest --database 'database_one' --restore-hostname 'basedata.cluster-foobar.aws-region-1.rds.amazonaws.com' ``` The above assumes: * `--restore-port 5432` from `port` * `--restore-user 'database_restore_user'` from `user` * `--restore-pass 'database_restore_pass'` from `pass` **The config above** has the fictional `restore_hostname` in the config, but let's be clear and say that it shouldn't be mandatory. `restore_hostname` should default to `hostname`. *P.S. Last day before vacation. Hope I described the scenarios well enough ^^. Thanks for the good work!*
witten commented 4 months ago
Owner

This makes a lot of sense to me. Thank you for taking the time to write it up! And enjoy your vacation.

This makes a lot of sense to me. Thank you for taking the time to write it up! And enjoy your vacation.
witten added the
design finalized
label 4 months ago
brskq commented 3 months ago
Poster

No worries, glad I could help! Waiting eagerly to have this implemented ^^ Keep up the good work.

No worries, glad I could help! Waiting eagerly to have this implemented ^^ Keep up the good work.
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date

No due date set.

Dependencies

This issue currently doesn't have any dependencies.

Loading…
There is no content yet.