[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: pgbackrest Help Required
From: Inzamam Shafiq <inzamam.shafiq () hotmail ! com>
Date: 2022-09-28 6:52:59
Message-ID: AM9P251MB03300A390E6888F410F752D698549 () AM9P251MB0330 ! EURP251 ! PROD ! OUTLOOK ! COM
[Download RAW message or body]
Hi Stephen,
Thanks for your response.
> Not sure what 'traditional zip method' means here, but if you've copied
the shell script out of the documentation, that's not a safe
configuration.
Conventional zip method means my archive_command and restore_command parameters are \
as follows,
archive_command = 'if test ! -d "/archives/"; then mkdir -p "/archives/"; fi; test ! \
-f "/archives/%f" && gzip < "%p" > "/archives/%f.gz"'
restore_command = 'gunzip < "/archives/%f".gz > "%p"'
> In configuration of pgbackrest, simply configure each of the PG hosts
and the repo and then set backup-standby=y. You'll need to make sure
that the archive_command is set to pgbackrest, of course. Having an
existing streaming replication setup is perfectly fine and pgbackrest
won't interfere with that. You will want to set your restore command
to pgbackrest also though, in case streaming fails.
I have done configurations, I just need a confirmation whether I did it right or \
still there needs some improvement. it will be helpful if I can have a documentation \
where someone has primary and standby in place and he had configured pgbackrest \
without disturbing the replication. I have followed below steps, please have a look \
at them and suggest any imporvements if requried.
1. Configure passwordless SSH connectivity between hosts
2. Configure NFS for shared drive between hosts
3. Stop Standby Cluster
4. Prepare Primary node for pgbackrest and replication
5. Configure pgbackrest to backup Primary node
6. Setup Standby node for pgbackrest and resume replication
* Configure Standby cluster for pgbackrest and check backup info
* If standby is having replication issues, then --delta restore using \
pgbackrest
* Check the replication
1. Test Streaming Replication
2. Edit pgbackrest.conf on Standby Node for backup
3. Perform backup from Standby
4. Test Steaming Replication again
Regards,
Inzamam Shafiq
Sr. DBA
________________________________
From: Stephen Frost
Sent: Tuesday, September 27, 2022 8:39 PM
To: Inzamam Shafiq
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pgbackrest Help Required
Greetings,
* Inzamam Shafiq (inzamam.shafiq@hotmail.com) wrote:
> I am in process of configuring pgbackrest, I have followed documents and configured \
> backup from backup server. I have a setup of Primary and Hot Standby, when I \
> configured pgbackrest the standby DB got un-synced and now I am unable to recover \
> it, can anyone help or share a good document where we can implement pgbackrest \
> where streaming replication is already in place using traditional pg_basebackup \
> method?
> Note: I want to configure pgbackrest and to take backup from standby DB, however \
> streaming replication is already there, where the archive and restore commands are \
> configured using traditional zip method.
Not sure what 'traditional zip method' means here, but if you've copied
the shell script out of the documentation, that's not a safe
configuration.
In configuration of pgbackrest, simply configure each of the PG hosts
and the repo and then set backup-standby=y. You'll need to make sure
that the archive_command is set to pgbackrest, of course. Having an
existing streaming replication setup is perfectly fine and pgbackrest
won't interfere with that. You will want to set your restore command
to pgbackrest also though, in case streaming fails.
Thanks,
Stephen
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} \
</style> </head>
<body dir="ltr">
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> Hi Stephen,</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <br>
</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> Thanks for your response.</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <br>
</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">> Not sure what 'traditional zip method' means \
here, but if you've copied</span><br style="font-family:"Segoe UI", \
"Segoe UI Web (West European)", "Segoe UI", -apple-system, \
BlinkMacSystemFont, Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">the shell script out of the documentation, that's \
not a safe</span><br style="font-family:"Segoe UI", "Segoe UI Web \
(West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">configuration.</span><br \
style="font-family:"Segoe UI", "Segoe UI Web (West European)", \
"Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica \
Neue", sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <br>
</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> Conventional zip method means my archive_command and \
restore_command parameters are as follows,</div> <div style="font-family: \
"Franklin Gothic Book", "Avenir Next Condensed", sans-serif; \
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof"> <br>
</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof ContentPasted1"> archive_command = 'if test ! -d \
"/archives/"; then mkdir -p "/archives/"; fi; test ! -f \
"/archives/%f" && gzip < "%p" > \
"/archives/%f.gz"' <div class="ContentPasted1"><br>
</div>
<div class="ContentPasted1">restore_command = 'gunzip < \
"/archives/%f".gz > "%p"'</div> <br>
</div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <br style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">> In configuration of pgbackrest, simply \
configure each of the PG hosts</span><br style="font-family:"Segoe UI", \
"Segoe UI Web (West European)", "Segoe UI", -apple-system, \
BlinkMacSystemFont, Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">and the repo and then set backup-standby=y. \
You'll need to make sure</span><br style="font-family:"Segoe UI", \
"Segoe UI Web (West European)", "Segoe UI", -apple-system, \
BlinkMacSystemFont, Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">that the archive_command is set to pgbackrest, of \
course. Having an</span><br style="font-family:"Segoe UI", \
"Segoe UI Web (West European)", "Segoe UI", -apple-system, \
BlinkMacSystemFont, Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">existing streaming replication setup is perfectly \
fine and pgbackrest</span><br style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">won't interfere with that. You will want to \
set your restore command</span><br style="font-family:"Segoe UI", \
"Segoe UI Web (West European)", "Segoe UI", -apple-system, \
BlinkMacSystemFont, Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255)" \
class="ContentPasted0"> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">to pgbackrest also though, in case streaming \
fails.</span><br> </div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0"><br> </span></div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0">I have done configurations, I just need a \
confirmation whether I did it right or still there needs some improvement. it will be \
helpful if I can have a documentation where someone has primary and standby in place \
and he had configured pgbackrest without disturbing the replication. I have followed \
below steps, please have a look at them and suggest any imporvements if \
requried.</span></div> <div style="font-family: "Franklin Gothic Book", \
"Avenir Next Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0"><br> </span></div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" \
class="elementToProof"> <span style="font-family:"Segoe UI", "Segoe UI \
Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, \
Roboto, "Helvetica Neue", \
sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline \
!important" class="ContentPasted0"> <div><span lang="EN-US" style="font-family: \
"Segoe UI", "Segoe UI Web (West European)", "Segoe UI", \
-apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", sans-serif; \
font-size: 11pt; color: rgb(0, 0, 0); background-color: rgba(0, 0, 0, 0);" \
class="ContentPasted2"></span></div> <ol style="margin-bottom:0cm;margin-top:0cm" \
type="1" class="ContentPasted2"> <li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Configure passwordless SSH \
connectivity between hosts<o:p \
class="ContentPasted3"> </o:p></span></li><li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Configure NFS for shared drive \
between hosts<o:p class="ContentPasted3"> </o:p></span></li><li><span \
lang="EN-US" style="mso-ansi-language:EN-US" class="ContentPasted3"><o:p \
class="ContentPasted3">Stop Standby Cluster</o:p></span></li><li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Prepare Primary node for \
pgbackrest and replication<o:p \
class="ContentPasted3"> </o:p></span></li><li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Configure pgbackrest to backup \
Primary node<o:p class="ContentPasted3"> </o:p></span></li><li><span \
lang="EN-US" style="mso-ansi-language:EN-US" class="ContentPasted3">Setup Standby \
node for pgbackrest and resume replication<o:p \
class="ContentPasted3"> </o:p></span> </li></ol> <ol \
style="margin-bottom:0cm;margin-top:0cm" type="1" class="ContentPasted2" start="7"> \
<ol style="list-style-type: lower-alpha;"> <li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Configure Standby cluster for \
pgbackrest and check backup info<o:p \
class="ContentPasted3"> </o:p></span></li><li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">If standby is having \
replication issues, then --delta restore using pgbackrest<o:p \
class="ContentPasted3"> </o:p></span></li><li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Check the replication<o:p \
class="ContentPasted3"> </o:p></span></li></ol> <li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Test Streaming Replication<o:p \
class="ContentPasted3"> </o:p></span></li><li><span lang="EN-US" \
style="mso-ansi-language:EN-US" class="ContentPasted3">Edit pgbackrest.conf on \
Standby Node for backup<o:p class="ContentPasted3"> </o:p></span></li><li><span \
lang="EN-US" style="mso-ansi-language:EN-US" class="ContentPasted3">Perform backup \
from Standby<o:p class="ContentPasted3"> </o:p></span></li><li><span \
lang="EN-US" style="mso-ansi-language:EN-US" class="ContentPasted3">Test Steaming \
Replication again<o:p class="ContentPasted3"> </o:p></span> </li></ol> \
</span></div> <div>
<div style="font-family: "Franklin Gothic Book", "Avenir Next \
Condensed", sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br>
</div>
<div id="Signature">
<div>
<div id="divtagdefaultwrapper" dir="ltr" style="font-size:12pt; color:#000000; \
font-family:Calibri,Helvetica,sans-serif"> Regards,
<div><br>
</div>
<div><i>Inzamam Shafiq</i></div>
<div><i>Sr. DBA</i></div>
<div><i></i></div>
</div>
<div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; \
color:rgb(0,0,0);"> <br>
<hr tabindex="-1" style="display:inline-block; width:98%;">
<b>From:</b> Stephen Frost<br>
<b>Sent:</b> Tuesday, September 27, 2022 8:39 PM<br>
<b>To:</b> Inzamam Shafiq<br>
<b>Cc:</b> pgsql-general@lists.postgresql.org<br>
<b>Subject:</b> Re: pgbackrest Help Required
<div><br>
</div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">Greetings,<br>
<br>
* Inzamam Shafiq (inzamam.shafiq@hotmail.com) wrote:<br>
> I am in process of configuring pgbackrest, I have followed documents and \
configured backup from backup server. I have a setup of Primary and Hot Standby, when \
I configured pgbackrest the standby DB got un-synced and now I am unable to recover \
it, can anyone help or share a good document where we can implement pgbackrest where \
streaming replication is already in place using traditional pg_basebackup method?<br> \
> <br> > Note: I want to configure pgbackrest and to take backup from standby \
DB, however streaming replication is already there, where the archive and restore \
commands are configured using traditional zip method.<br> <br>
Not sure what 'traditional zip method' means here, but if you've copied<br>
the shell script out of the documentation, that's not a safe<br>
configuration.<br>
<br>
In configuration of pgbackrest, simply configure each of the PG hosts<br>
and the repo and then set backup-standby=y. You'll need to make sure<br>
that the archive_command is set to pgbackrest, of course. Having an<br>
existing streaming replication setup is perfectly fine and pgbackrest<br>
won't interfere with that. You will want to set your restore command<br>
to pgbackrest also though, in case streaming fails.<br>
<br>
Thanks,<br>
<br>
Stephen<br>
</div>
</span></font></div>
</div>
</div>
</div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic