Обсуждение: warm standby with WAL shipping
I'm trying to set up a warm standby via WAL shipping. I'm digging through the source of pg_standby.c to determine the proper method. Since we are using scp to access the archive files, pg_standby doesn't provide a solution to our problem. From reading the comments in pg_standby.c, I see: 'Check for initial history file: always the first file to be requested It's OK if the file isn't there - all other files need to wait' My problem is, I never see a *.history file, thus my script sits in a loop looking for it. I see the WAL files showing up on the archive server, but I don't see a *.history file. What am I missing? -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote: > My problem is, I never see a *.history file, thus my script sits in a > loop looking for it. I see the WAL files showing up on the archive > server, but I don't see a *.history file. > > What am I missing? pg_standby it self isn't a solution for warm standby. It is a component thereof. Also don't use SCP. Use rsync. Take a look at walmgr or PITRTools it will make your life easier. Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Joshua D. Drake wrote: > On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote: > >> My problem is, I never see a *.history file, thus my script sits in a >> loop looking for it. I see the WAL files showing up on the archive >> server, but I don't see a *.history file. >> >> What am I missing? > > pg_standby it self isn't a solution for warm standby. It is a component > thereof. Also don't use SCP. Use rsync. Take a look at walmgr or > PITRTools it will make your life easier. Okay, I'm attempting to check out PITRTools: svn co https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2 svn: Unrecognized URL scheme for 'https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2' What am I missing here? -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Geoffrey wrote: > Joshua D. Drake wrote: >> On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote: >> >>> My problem is, I never see a *.history file, thus my script sits in a >>> loop looking for it. I see the WAL files showing up on the archive >>> server, but I don't see a *.history file. >>> >>> What am I missing? >> >> pg_standby it self isn't a solution for warm standby. It is a component >> thereof. Also don't use SCP. Use rsync. Take a look at walmgr or >> PITRTools it will make your life easier. > > Okay, I'm attempting to check out PITRTools: > > svn co > https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2 > > svn: Unrecognized URL scheme for > 'https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2' > > What am I missing here? Nevermind, ID 10T error. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Joshua D. Drake wrote: > On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote: > >> My problem is, I never see a *.history file, thus my script sits in a >> loop looking for it. I see the WAL files showing up on the archive >> server, but I don't see a *.history file. >> >> What am I missing? > > pg_standby it self isn't a solution for warm standby. It is a component > thereof. Also don't use SCP. Use rsync. Take a look at walmgr or > PITRTools it will make your life easier. So, I'm looking at the PITRTools stuff, but I really want to understand how this all works. I'm shipping my wall files fine. I've been able to recreate my database from a backup and the accumulated WAL files. The problem with my current process is as noted, my script keeps looking for the *.history file, but never sees it. I see the list of files on my archive machine growing, so I know WAL shipping is working. I've created my backup as instructed in the docs. I don't know how/where the *.history file is generated and/or why it's not showing up in the archive directory with the WAL files. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Joshua D. Drake wrote: > On Tue, 2009-06-02 at 15:21 -0400, Geoffrey wrote: > >> My problem is, I never see a *.history file, thus my script sits in a >> loop looking for it. I see the WAL files showing up on the archive >> server, but I don't see a *.history file. >> >> What am I missing? > > pg_standby it self isn't a solution for warm standby. It is a component > thereof. Also don't use SCP. Use rsync. Take a look at walmgr or > PITRTools it will make your life easier. I still don't understand why the pg_standby code is looking for the *.history. Apparently others have seen this same behavior (according to google), yet I don't see any definitive answer. I don't mind looking at other tools, but I want to understand this process. I can't find what files are processed in what order, although according to the pg_standby.c code, there are at least three different files it's looking for: *.history, *.backup, and the standard WAL files. Is this documented anywhere? It appears the PITRTools use pg_standby binary, thus I'm still confused as to how these files are processed. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
On Tue, 2009-06-02 at 19:44 -0400, Geoffrey wrote: > > > pg_standby it self isn't a solution for warm standby. It is a component > > thereof. Also don't use SCP. Use rsync. Take a look at walmgr or > > PITRTools it will make your life easier. > > I still don't understand why the pg_standby code is looking for the > *.history. Apparently others have seen this same behavior (according to > google), yet I don't see any definitive answer. http://www.postgresql.org/docs/8.3/static/warm-standby.html http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL I think will have what you want. > It appears the PITRTools use pg_standby binary, thus I'm still confused > as to how these files are processed. > Yes it does use pg_standby. It just wraps everything that is missing for warm standby into a single utility. Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Tue, 2 Jun 2009, Geoffrey wrote: > The problem with my current process is as noted, my script keeps looking > for the *.history file, but never sees it. From the restore_command section of the documentation: "The command will be asked for log files that are not present in the archive; it must return nonzero when so asked. This is not an error condition." So if you're asked for a .history file, and you don't have one, return an error state saying as much and the right thing will happen--recovery continues. More comments about the path everyone wanders down when trying to build their own tools here are at http://archives.postgresql.org/sydpug/2006-10/msg00001.php , you'll probably get some more insight into the details here reading that early commentary. But you still want to know where they might come from, right? Those history files show up when you've started your backup server after recovering files from the original system. You need to bring the backup system out of standby before you'll see one. That results in a new timeline: http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIMELINES Think about for a second: if the original server is still running, but you've started the standby system too, there are two separate histories with a common ancestor possible. One history has the original data plus what happened afterwards on the master, the other has the originals plus what happened afterwards on the standby, after it was started. The fun part is that you can return to copying files from the master again, so that you've got both sets of files available. You then choose which history to follow by adjusting the recovery_target_timeline parameter in the recovery.conf file. Anyway, while getting your hands dirty so you understand what's happening is a good idea, trying to fully reinvent pg_standby is an exercise destined to have a whole stack of little issues like these. Don't do that; it's taken years to get that code as mature as it is, and while you'll progress faster because you can stare at its source it will still take you a while. Returning to your original motivation for doing that, I threw a suggestion for how to combine pg_standby with using scp as the transport mechanism into http://wiki.postgresql.org/wiki/Warm_Standby , you just need to buffer transfers into a holding area to get around the atomic copy issues here. This requires using a non-trivial archive_command process though, you'll need to call a real script there to handle the multiple steps involved rather than just getting away with a one-line command for that setting. I reinvent that wheel periodically for sites that can't or won't install rsync for the job instead (always some variant on "for security reasons"). Unfortunately those sites also don't like releasing the resulting code to the world at large, so I don't have a full sample to show you. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Joshua D. Drake wrote: > On Tue, 2009-06-02 at 19:44 -0400, Geoffrey wrote: >> >>> pg_standby it self isn't a solution for warm standby. It is a component >>> thereof. Also don't use SCP. Use rsync. Take a look at walmgr or >>> PITRTools it will make your life easier. >> I still don't understand why the pg_standby code is looking for the >> *.history. Apparently others have seen this same behavior (according to >> google), yet I don't see any definitive answer. > > http://www.postgresql.org/docs/8.3/static/warm-standby.html > http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL > > I think will have what you want. I have read this documentation, unfortunately a couple of times. Is the section '24.3.4 Timelines' referencing these *.history files? Is there any documentation out there that specifically lays out what the files are and how they are processed? Or, is that what I'm looking at in 24.3.4? >> It appears the PITRTools use pg_standby binary, thus I'm still confused >> as to how these files are processed. >> > > Yes it does use pg_standby. It just wraps everything that is missing for > warm standby into a single utility. Looking at the pg_standby.c I have, I don't see a reference to scp or rsync. If I use the PITRTools, is there a different version or do I need to modify this version to use rsync. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Thank you Greg for taking the time to explain this as throughly as you have. I have found a logic problem in my code. I still don't know if we will use pg_standby as the wrapper code in PITRTools is python and we are not a python shop. Kinda want to stick with what we know (C, perl, shell). I'm certainly looking at rsync rather then scp, which really makes more sense. Greg Smith wrote: > On Tue, 2 Jun 2009, Geoffrey wrote: > >> The problem with my current process is as noted, my script keeps >> looking for the *.history file, but never sees it. > >> From the restore_command section of the documentation: > > "The command will be asked for log files that are not present in the > archive; it must return nonzero when so asked. This is not an error > condition." > > So if you're asked for a .history file, and you don't have one, return > an error state saying as much and the right thing will happen--recovery > continues. More comments about the path everyone wanders down when > trying to build their own tools here are at > http://archives.postgresql.org/sydpug/2006-10/msg00001.php , you'll > probably get some more insight into the details here reading that early > commentary. > > But you still want to know where they might come from, right? Those > history files show up when you've started your backup server after > recovering files from the original system. You need to bring the backup > system out of standby before you'll see one. That results in a new > timeline: > http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIMELINES > > > Think about for a second: if the original server is still running, but > you've started the standby system too, there are two separate histories > with a common ancestor possible. One history has the original data plus > what happened afterwards on the master, the other has the originals plus > what happened afterwards on the standby, after it was started. The fun > part is that you can return to copying files from the master again, so > that you've got both sets of files available. You then choose which > history to follow by adjusting the recovery_target_timeline parameter in > the recovery.conf file. > > Anyway, while getting your hands dirty so you understand what's > happening is a good idea, trying to fully reinvent pg_standby is an > exercise destined to have a whole stack of little issues like these. > Don't do that; it's taken years to get that code as mature as it is, and > while you'll progress faster because you can stare at its source it will > still take you a while. Returning to your original motivation for doing > that, I threw a suggestion for how to combine pg_standby with using scp > as the transport mechanism into > http://wiki.postgresql.org/wiki/Warm_Standby , you just need to buffer > transfers into a holding area to get around the atomic copy issues > here. This requires using a non-trivial archive_command process though, > you'll need to call a real script there to handle the multiple steps > involved rather than just getting away with a one-line command for that > setting. I reinvent that wheel periodically for sites that can't or > won't install rsync for the job instead (always some variant on "for > security reasons"). Unfortunately those sites also don't like releasing > the resulting code to the world at large, so I don't have a full sample > to show you. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
On Wed, Jun 03, 2009 at 08:13:48AM -0400, Geoffrey wrote:
> I'm certainly looking at rsync rather then scp, which really makes more
> sense.
You can mix ssh and rsync. The man pages has this example:
rsync -az -e ssh --delete ~ftp/pub/samba/ nimbus:"~ftp/pub/tridge/samba"
On Jun 3, 2009, at 5:13 AM, Geoffrey wrote: > Thank you Greg for taking the time to explain this as throughly as > you have. I have found a logic problem in my code. I still don't > know if we will use pg_standby as the wrapper code in PITRTools is > python and we are not a python shop. Kinda want to stick with what > we know (C, perl, shell). I'm certainly looking at rsync rather > then scp, which really makes more sense. pg_standby is in no way dependent on PITRTools. PITRTools is, however, dependent on pg_standby. Put another way: you do not need to use PITRTools to use pg_standby. In fact, you also don't need any perl or shell scripts to use pg_standby, just use rsync directly in the archive_command on the master and pg_standby in the recovery_command on the standby. The wiki link Greg provided (http://wiki.postgresql.org/wiki/Warm_Standby ) has all of the info needed to set things up manually. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones wrote: > > On Jun 3, 2009, at 5:13 AM, Geoffrey wrote: > >> Thank you Greg for taking the time to explain this as throughly as you >> have. I have found a logic problem in my code. I still don't know if >> we will use pg_standby as the wrapper code in PITRTools is python and >> we are not a python shop. Kinda want to stick with what we know (C, >> perl, shell). I'm certainly looking at rsync rather then scp, which >> really makes more sense. > > pg_standby is in no way dependent on PITRTools. PITRTools is, however, > dependent on pg_standby. Put another way: you do not need to use > PITRTools to use pg_standby. In fact, you also don't need any perl or > shell scripts to use pg_standby, just use rsync directly in the > archive_command on the master and pg_standby in the recovery_command on > the standby. The wiki link Greg provided > (http://wiki.postgresql.org/wiki/Warm_Standby) has all of the info > needed to set things up manually. Our current scenario is that we are archiving from machine A to machine B. Our hot spare is machine C, thus we are pulling the files via network from machine B to machine C, hence the reason I don't believe db_standby will work as it has no facility (rsync,scp) to retrieve the files from another machine. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
On Wed, 2009-06-03 at 14:43 -0400, Geoffrey wrote: > > > pg_standby is in no way dependent on PITRTools. PITRTools is, however, > > dependent on pg_standby. Put another way: you do not need to use > > PITRTools to use pg_standby. In fact, you also don't need any perl or > > shell scripts to use pg_standby, just use rsync directly in the > > archive_command on the master and pg_standby in the recovery_command on > > the standby. The wiki link Greg provided > > (http://wiki.postgresql.org/wiki/Warm_Standby) has all of the info > > needed to set things up manually. > > Our current scenario is that we are archiving from machine A to machine > B. Our hot spare is machine C, thus we are pulling the files via > network from machine B to machine C, hence the reason I don't believe > db_standby will work as it has no facility (rsync,scp) to retrieve the > files from another machine. The point that is being made is that pg_standby doesn't need pitrtools to do its job. That is all. It is also why I said that pg_standby is just a component of a PITR solution and not a PITR Solution in itself. You are still going to need to either: A. Reinvent the wheel, by scripting it all yourself B. Use solutions that are already used by others such as walmgr or pitrtools Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Joshua D. Drake wrote: > On Wed, 2009-06-03 at 14:43 -0400, Geoffrey wrote: >> >>> pg_standby is in no way dependent on PITRTools. PITRTools is, however, >>> dependent on pg_standby. Put another way: you do not need to use >>> PITRTools to use pg_standby. In fact, you also don't need any perl or >>> shell scripts to use pg_standby, just use rsync directly in the >>> archive_command on the master and pg_standby in the recovery_command on >>> the standby. The wiki link Greg provided >>> (http://wiki.postgresql.org/wiki/Warm_Standby) has all of the info >>> needed to set things up manually. >> Our current scenario is that we are archiving from machine A to machine >> B. Our hot spare is machine C, thus we are pulling the files via >> network from machine B to machine C, hence the reason I don't believe >> db_standby will work as it has no facility (rsync,scp) to retrieve the >> files from another machine. > > The point that is being made is that pg_standby doesn't need pitrtools > to do its job. That is all. It is also why I said that pg_standby is > just a component of a PITR solution and not a PITR Solution in itself. I understand his point very clearly. > You are still going to need to either: > > A. Reinvent the wheel, by scripting it all yourself > B. Use solutions that are already used by others such as walmgr or > pitrtools My assumption was that since pg_standby does not have the scp/rsync functionality, I would have to either modify it, change the way we do things, or 'reinvent' a little different wheel. There is also an objection to using the python tools as we are small shop and do not have anyone who is versed in python. I have not had a chance to look at walmgr, I will do that shortly. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
On Wed, 3 Jun 2009, Geoffrey wrote: > My assumption was that since pg_standby does not have the scp/rsync > functionality, I would have to either modify it, change the way we do things, > or 'reinvent' a little different wheel. There are three things to setup here: 1) archive_command on the master 2) Transport between master and standby(s) 3) recovery_command. pg_standby is the reference implementation here. You can combine (1) and (2) by putting some sort of network copy command into the archive_command, but better practice here (and probably required practice in your case) is to write a script that does that instead. That's the part you need to worry about. There is no need for you to reinvent (3) just because you have different requirements than most for (2). As you've noticed, pg_standby doesn't actually do the network transport part, and that also means that it's decoupled from what choices you make for that layer. Focus on writing scripts to atomically copy the files into the right destination on the standbys, and pg_standby will take care of applying the shipped log files to the database. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Wed, 2009-06-03 at 15:07 -0400, Geoffrey wrote: > > You are still going to need to either: > > > > A. Reinvent the wheel, by scripting it all yourself > > B. Use solutions that are already used by others such as walmgr or > > pitrtools > > My assumption was that since pg_standby does not have the scp/rsync > functionality, I would have to either modify it, change the way we do > things, or 'reinvent' a little different wheel. You wouldn't modify pg_standby. You would create a new utility that ships logs (or pulls logs) for pg_standby to utilize. > > There is also an objection to using the python tools as we are small > shop and do not have anyone who is versed in python. At best this seems like a very odd requirement. You aren't doing any development. You are using a utility which is written in a specific language. > > I have not had a chance to look at walmgr, I will do that shortly. > > I believe it is written in Python. Anyway good luck! Joshua D. Drkae -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Wednesday 03 June 2009 15:26, Greg Smith wrote: > On Wed, 3 Jun 2009, Geoffrey wrote: > > My assumption was that since pg_standby does not have the scp/rsync > > functionality, I would have to either modify it, change the way we do > > things, or 'reinvent' a little different wheel. > > There are three things to setup here: > > 1) archive_command on the master > 2) Transport between master and standby(s) > 3) recovery_command. pg_standby is the reference implementation here. > > You can combine (1) and (2) by putting some sort of network copy command > into the archive_command, but better practice here (and probably required > practice in your case) is to write a script that does that instead. > That's the part you need to worry about. > > There is no need for you to reinvent (3) just because you have different > requirements than most for (2). As you've noticed, pg_standby doesn't > actually do the network transport part, and that also means that it's > decoupled from what choices you make for that layer. Focus on writing > scripts to atomically copy the files into the right destination on the > standbys, and pg_standby will take care of applying the shipped log files > to the database. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD Our circumstance here is that we will be feeding multiple warm stand-by servers; one local and the rest remote, that is, at least one in other state and possibly another in another city. We didn't want the WAL shipping process to fail because one of the nodes might be down. To circumvent that, we thought the best approach to take was to pump the WAL logs to a central machine on-site, and have the warm stand-by servers pick up their files from the central storage device. This is why we were thinking about changing pg_standby. Thanks for all the help... -- Work: 1-336-372-6812 Cell: 1-336-404-6987 email: terry@chosen-ones.org
On Wed, 2009-06-03 at 16:45 -0400, Terry Lee Tucker wrote: > > -- > > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > Our circumstance here is that we will be feeding multiple warm stand-by > servers; one local and the rest remote, that is, at least one in other state > and possibly another in another city. We didn't want the WAL shipping process > to fail because one of the nodes might be down. To circumvent that, we > thought the best approach to take was to pump the WAL logs to a central > machine on-site, and have the warm stand-by servers pick up their files from > the central storage device. This is why we were thinking about changing > pg_standby. PITRTools 1.2 has queuing which can deal with this problem. I don't know if walmgr does. Joshua D. Drake > > Thanks for all the help... > -- > > Work: 1-336-372-6812 > Cell: 1-336-404-6987 > email: terry@chosen-ones.org > -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Wednesday 03 June 2009 17:11, Joshua D. Drake wrote: > On Wed, 2009-06-03 at 16:45 -0400, Terry Lee Tucker wrote: > > > -- > > > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, > > > MD > > > > Our circumstance here is that we will be feeding multiple warm stand-by > > servers; one local and the rest remote, that is, at least one in other > > state and possibly another in another city. We didn't want the WAL > > shipping process to fail because one of the nodes might be down. To > > circumvent that, we thought the best approach to take was to pump the WAL > > logs to a central machine on-site, and have the warm stand-by servers > > pick up their files from the central storage device. This is why we were > > thinking about changing pg_standby. > > PITRTools 1.2 has queuing which can deal with this problem. > > I don't know if walmgr does. > We will look into PITRTools then. I'm nervous about re-inventing the wheel as has been pointed out. Thanks for the input... > Joshua D. Drake > > > Thanks for all the help... > > -- > > > > Work: 1-336-372-6812 > > Cell: 1-336-404-6987 > > email: terry@chosen-ones.org -- Work: 1-336-372-6812 Cell: 1-336-404-6987 email: terry@leetuckert.net
Greg Smith wrote: > On Wed, 3 Jun 2009, Geoffrey wrote: > >> My assumption was that since pg_standby does not have the scp/rsync >> functionality, I would have to either modify it, change the way we do >> things, or 'reinvent' a little different wheel. > > There are three things to setup here: > > 1) archive_command on the master > 2) Transport between master and standby(s) > 3) recovery_command. pg_standby is the reference implementation here. > > You can combine (1) and (2) by putting some sort of network copy command > into the archive_command, but better practice here (and probably > required practice in your case) is to write a script that does that > instead. That's the part you need to worry about. I have a functioning script that accomplishes 1 and 2 together. The archive script copies the files to a second server. (not the warm standby) > There is no need for you to reinvent (3) just because you have different > requirements than most for (2). As you've noticed, pg_standby doesn't > actually do the network transport part, and that also means that it's > decoupled from what choices you make for that layer. Focus on writing > scripts to atomically copy the files into the right destination on the > standbys, and pg_standby will take care of applying the shipped log > files to the database. This is a good point. As it turns out, it appears that I've got a fully functional solution at this time. I squashed a few bugs in my restore script yesterday and ran a test where it continuously restored WAL files until it received the 'failover' indicator. I'm still looking at other solutions and how they can fit into our particular problem. I also heed your statement from yesterday: 'Anyway, while getting your hands dirty so you understand what's happening is a good idea, trying to fully reinvent pg_standby is an exercise destined to have a whole stack of little issues like these. Don't do that; it's taken years to get that code as mature as it is, and while you'll progress faster because you can stare at its source it will still take you a while.' There is much wisdom in this statement and I see that recommendation in Joshua's comments as well. At this point, we are pushing hard on a deadline to get this in, so my quandry is whether to use what appears to be a working home grown solution, or continue researching other options. For now, I'm still looking at the other tools as well as attempting to verify that my current solution doesn't miss any 'little issues.' Thanks again for everyone's patience and input. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Joshua D. Drake wrote: > On Wed, 2009-06-03 at 15:07 -0400, Geoffrey wrote: > >>> You are still going to need to either: >>> >>> A. Reinvent the wheel, by scripting it all yourself >>> B. Use solutions that are already used by others such as walmgr or >>> pitrtools >> My assumption was that since pg_standby does not have the scp/rsync >> functionality, I would have to either modify it, change the way we do >> things, or 'reinvent' a little different wheel. > > You wouldn't modify pg_standby. You would create a new utility that > ships logs (or pulls logs) for pg_standby to utilize. I assume a script that pulls the logs to the warm standby and then calls pg_standby. That's a thought. >> There is also an objection to using the python tools as we are small >> shop and do not have anyone who is versed in python. > > At best this seems like a very odd requirement. You aren't doing any > development. You are using a utility which is written in a specific > language. Well, we are an odd group. :) >> I have not had a chance to look at walmgr, I will do that shortly. >> > I believe it is written in Python. Hmm, I guess I should brush up on my Python. > Anyway good luck! Thank you. > Joshua D. Drkae echo Joshua D. Drkae |sed 's/ka/ak/' :) -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
On Thu, 4 Jun 2009, Geoffrey wrote: > For now, I'm still looking at the other tools as well as attempting to verify > that my current solution doesn't miss any 'little issues.' The main thing you want to test out are that it acts sanely when the network connection to the destination server is out, and that it doesn't go insane if either the source or destination server run out of disk space. You should simulate both of those things. The important thing is to validate your script cannot say you've processed an archive file until you're absolutely positive it's stored somewhere safe. It's really not that hard. If you've got practice writing robust system scripts already, and it sounds like you do, I wouldn't hesitate to use a homegrown solution here instead of walmgr/pitrtools as long as you've done the tests I outline here. > I assume a script that pulls the logs to the warm standby and then calls > pg_standby. The way you say this makes me think you haven't really absorbed how pg_standby works yet. You don't call it; the database recovery script does. Your program's interaction with it is merely to drop files into the place it expects them to be (atomically), it's a polling solution that alternates between looking for files there/applying them to the database/sleeping when there's no more left. If you've already gone to the trouble of writing all the pieces here yourself, it really shouldn't be difficult to yank out the parts pg_standby does and use it for those instead. There's a few things in there you'll have a hard time implementing yourself that probably aren't even on your radar yet, but are nonetheless important. Being able to keep standby disk usage pruned easily with the restartwalfile feature comes to mind, that one is a subtle problem that doesn't sneak up on you until you've been in production a while. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith wrote: > On Thu, 4 Jun 2009, Geoffrey wrote: > >> For now, I'm still looking at the other tools as well as attempting to >> verify that my current solution doesn't miss any 'little issues.' > > The main thing you want to test out are that it acts sanely when the > network connection to the destination server is out, and that it doesn't > go insane if either the source or destination server run out of disk > space. You should simulate both of those things. This has not happened as of yet, part of the test suite though. > The important thing is to validate your script cannot say you've > processed an archive file until you're absolutely positive it's stored > somewhere safe. It's really not that hard. If you've got practice > writing robust system scripts already, and it sounds like you do, I > wouldn't hesitate to use a homegrown solution here instead of > walmgr/pitrtools as long as you've done the tests I outline here. I wrote a language parser in AWK years ago... ;) (that was ugly) >> I assume a script that pulls the logs to the warm standby and then >> calls pg_standby. > > The way you say this makes me think you haven't really absorbed how > pg_standby works yet. You don't call it; the database recovery script > does. Your program's interaction with it is merely to drop files into > the place it expects them to be (atomically), it's a polling solution > that alternates between looking for files there/applying them to the > database/sleeping when there's no more left. The script I mention above would be the recovery script. Since, as I understand pg_standby, looks for files locally, then my restore script would have to pull the files from the remote machine and drop them somewhere where pg_standby would be looking for them. That's my thought anyway. > If you've already gone to the trouble of writing all the pieces here > yourself, it really shouldn't be difficult to yank out the parts > pg_standby does and use it for those instead. There's a few things in > there you'll have a hard time implementing yourself that probably aren't > even on your radar yet, but are nonetheless important. Being able to > keep standby disk usage pruned easily with the restartwalfile feature > comes to mind, that one is a subtle problem that doesn't sneak up on you > until you've been in production a while. Still studying the pg_standby code. ;) -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin