Allow restoring to other database server #326

New Issue

brskq · 2020-06-12T12:20:06Z

brskq commented

2020-06-12 12:20:06 +00:00

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

2020-06-17 19:34:24 +00:00

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

2020-06-18 07:41:14 +00:00

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

2020-06-18 15:26:41 +00:00

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 2020-06-18 15:26:51 +00:00

brskq commented

2020-07-14 06:43:05 +00:00

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

👍 1

witten added the

good first issue

label 2023-02-04 21:32:33 +00:00

witten referenced this issue

2023-02-04 21:34:35 +00:00

Restore database to different database name #322

jetchirag commented

2023-03-22 17:14:04 +00:00

Can you assign this to me? I'd like to implement @brskq's ideas.

witten commented

2023-03-22 17:23:33 +00:00

We typically don't use the assignee field, but please go ahead and work on this if you like! I'd be happy to review a plan before you dive into the code as well. Also, I believe that @diivi was looking at this ticket, so you may want to compare notes or even collaborate on it.

diivi commented

2023-03-22 17:34:23 +00:00

This issue was part of the GSoC ideas list, so I added it to my proposal and worked on other tickets.

I'd have to edit a major part of my proposal if this does get implemented though 😓, can you work on some other issue for now and maybe we can work on this later? Submitting a new proposal this late would waste long mentor reviews and discussions too. I hope you understand.

Also, this is the first task in my proposal, so I believe it won't take up much time if we postpone this a little too? Nevertheless, let me know.

This issue was part of the GSoC ideas list, so I added it to my proposal and worked on other tickets. I'd have to edit a major part of my proposal if this does get implemented though 😓, can you work on some other issue for now and maybe we can work on this later? Submitting a new proposal this late would waste long mentor reviews and discussions too. I hope you understand. Also, this is the first task in my proposal, so I believe it won't take up much time if we postpone this a little too? Nevertheless, let me know.

jetchirag commented

2023-03-22 19:00:55 +00:00

@diivi I do have some code ready. I think we have conflicting projects in proposals too. Let me get back to you.

diivi commented

2023-03-23 03:50:13 +00:00

@diivi I do have some code ready.

Why would you ask for the issue to be assigned to you and start working before you get a reply? I replied right after you asked.

I also made it pretty clear I wanted to work on this issue here - https://github.com/borgbase/vorta/issues/1583#issuecomment-1444183788, right after I made my proposal.

I think we have conflicting projects in proposals too. Let me get back to you.

I am not really looking forward to changing anything except this task, considering it was the smallest part of my proposal, and I can work on bigger Borgmatic tasks too.
I have worked on it since the beginning of the contribution period, can't change it 10 days before submission. Thanks.

> @diivi I do have some code ready. Why would you ask for the issue to be assigned to you and start working before you get a reply? I replied right after you asked. I also made it pretty clear I wanted to work on this issue here - https://github.com/borgbase/vorta/issues/1583#issuecomment-1444183788, right after I made my proposal. > I think we have conflicting projects in proposals too. Let me get back to you. I am not really looking forward to changing anything except this task, considering it was the smallest part of my proposal, and I can work on bigger Borgmatic tasks too. I have worked on it since the beginning of the contribution period, can't change it 10 days before submission. Thanks.

witten commented

2023-03-23 04:54:08 +00:00

Hey folks, I may have been a little overenthusiastic in welcoming @jetchirag to work on this ticket when @diivi already had his eye on it (and had included it in his GSoC proposal). I think it probably does make sense to hold off on implementing it until the GSoC implementation phase is well underway. At that point, you are both welcome to discuss your respective approaches here and we can figure out whether it makes sense for you to collaborate on the feature or not. How does that sound?

And @jetchirag, if you're looking for something to work on in the meantime, we do have plenty of other tickets. Feel free to let me know what kind of task you're looking for, and I might be able to direct you to something that would be a good fit.

Hey folks, I may have been a little overenthusiastic in welcoming @jetchirag to work on this ticket when @diivi already had his eye on it (and had included it in his GSoC proposal). I think it probably does make sense to hold off on implementing it until the GSoC implementation phase is well underway. At that point, you are both welcome to discuss your respective approaches here and we can figure out whether it makes sense for you to collaborate on the feature or not. How does that sound? And @jetchirag, if you're looking for something to work on in the meantime, we do have plenty of other tickets. Feel free to let me know what kind of task you're looking for, and I might be able to direct you to something that would be a good fit.

❤️ 1

jetchirag commented

2023-03-23 04:56:10 +00:00

@diivi This would have been my first contribution to borgmatic so I started working on it before getting this assigned to make sure I can actually complete it. I had no idea about the codebase hence the implementation.

Also please note, Almost all projects would be mentioned in the GSoC discussion and I didn't went through looking at every individual comment.

As for me getting back to you, it was so I can adjust my proposal accordingly as the mentors also suggested me. I wouldn't be asking anyone else to change theirs for me. I would have rather provided you the code if you were accepted with this task to give a jumpstart.

@diivi This would have been my first contribution to borgmatic so I started working on it before getting this assigned to make sure I can actually complete it. I had no idea about the codebase hence the implementation. Also please note, Almost all projects would be mentioned in the GSoC discussion and I didn't went through looking at every individual comment. As for me getting back to you, it was so I can adjust my proposal accordingly as the mentors also suggested me. I wouldn't be asking anyone else to change theirs for me. I would have rather provided you the code if you were accepted with this task to give a jumpstart.

👍 1

diivi commented

2023-03-23 05:00:09 +00:00

As for me getting back to you, it was so I can adjust my proposal accordingly as the mentors also suggested me. I wouldn't be asking anyone else to change theirs for me.

Ah, thanks, I would love that, a conflict will just be bad for the both of us! You can message me on matrix - @divy4n5h:matrix.org. Sorry for the misunderstanding.

> As for me getting back to you, it was so I can adjust my proposal accordingly as the mentors also suggested me. I wouldn't be asking anyone else to change theirs for me. Ah, thanks, I would love that, a conflict will just be bad for the both of us! You can message me on matrix - @divy4n5h:matrix.org. Sorry for the misunderstanding.

👍 1

jetchirag commented

2023-03-23 05:00:37 +00:00

@witten That's perfect. I requested for you to be added to my proposal discussion.

I'm looking at tasks which can help me get familiar with the codebase while making meaning contributions. Perhaps a small feature, new flag or a bug fix? Thanks!

@witten That's perfect. I requested for you to be added to my proposal discussion. I'm looking at tasks which can help me get familiar with the codebase while making meaning contributions. Perhaps a small feature, new flag or a bug fix? Thanks!

witten commented

2023-03-23 21:13:07 +00:00

@jetchirag, any issue tagged with good first issue would be a good place to start! Of those, these ones are probably the smallest or most straightforward: #427, #441, and maybe #370.

@jetchirag, any issue tagged with [good first issue](https://projects.torsion.org/issues?q=&type=your_repositories&state=open&labels=52&milestone=&assignee=&poster=) would be a good place to start! Of those, these ones are probably the smallest or most straightforward: #427, #441, and maybe #370.

jetchirag commented

2023-03-23 21:47:42 +00:00

@witten Thank you! I'm checking them.