Обсуждение: Continuous Archiving for Multiple Warm Standby Servers
I'm attempting to design a postgres system whereby an authoritative primary server simultaneously feeds continuous archives to a number of warm standby servers that live both on the local network and on remote networks.
The sticking point in my current thinking about such a system is what to do in the event that any of an array of possible nodes becomes unreachable. I would expect a custom archive_command to have the intelligence about network reachability and to report a nonzero status if it was unable to submit an archive to any particular node.
The way I understand it, postgres would then resubmit the file that caused the nonzero status, which, if connectivity has been restored, is no problem for the node that caused the nonzero status in the first place. But then the issue becomes what to do with the nodes that were fine when the nonzero status.
From the docs <http://www.postgresql.org/docs/8.2/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL>:
"It is advisable to test your proposed archive command to ensure that it indeed does not overwrite an existing file, and that it returns nonzero status in this case. We have found that cp -i does this correctly on some platforms but not others. If the chosen command does not itself handle this case correctly, you should add a command to test for pre-existence of the archive file."
What is the advised remedy for this scenario in general? And then what is it if nonzero status is returned by archive_command because the file already exists on nodes that stayed up after a scenario where nonzero status is returned because one or more nodes became unreachable?
A follow-on question is: Does it become the responsibility of archive_command in a scenario like this to track which files have been archived on which nodes? Is there any introspective way for a standby server to know that a file has been archived by primary? If not, is it safe to reply on using sequential numbering of WAL files for implicit introspection? I don't see any functions that provide introspection of this nature. I ask because it seems like network-to-network failures are a common enough occurrence that some mechanism for archive verification is a must-have. I'm just trying to determine how much of that functionality I'll have to build myself...
--
Thomas F. O'Connell
optimizing modern web applications
: for search engines, for usability, and for performance :
On May 7, 2007, at 3:10 PM, Thomas F. O'Connell wrote: > I'm attempting to design a postgres system whereby an authoritative > primary server simultaneously feeds continuous archives to a number > of warm standby servers that live both on the local network and on > remote networks. > > The sticking point in my current thinking about such a system is > what to do in the event that any of an array of possible nodes > becomes unreachable. I would expect a custom archive_command to > have the intelligence about network reachability and to report a > nonzero status if it was unable to submit an archive to any > particular node. > > The way I understand it, postgres would then resubmit the file that > caused the nonzero status, which, if connectivity has been > restored, is no problem for the node that caused the nonzero status > in the first place. But then the issue becomes what to do with the > nodes that were fine when the nonzero status. > > From the docs <http://www.postgresql.org/docs/8.2/static/continuous- > archiving.html#BACKUP-ARCHIVING-WAL>: > > "It is advisable to test your proposed archive command to ensure > that it indeed does not overwrite an existing file, and that it > returns nonzero status in this case. We have found that cp -i does > this correctly on some platforms but not others. If the chosen > command does not itself handle this case correctly, you should add > a command to test for pre-existence of the archive file." > > What is the advised remedy for this scenario in general? And then > what is it if nonzero status is returned by archive_command because > the file already exists on nodes that stayed up after a scenario > where nonzero status is returned because one or more nodes became > unreachable? AFAIK the bit about -i / not overwriting files is just a safety measure to ensure you don't accidentally set up the archive_command to over-write WAL files for a working backend, or some other PITR backup. As long as you're certain you've got all your paths setup correctly it should be safe to drop the -i bit. > A follow-on question is: Does it become the responsibility of > archive_command in a scenario like this to track which files have > been archived on which nodes? Is there any introspective way for a > standby server to know that a file has been archived by primary? If > not, is it safe to reply on using sequential numbering of WAL files > for implicit introspection? I don't see any functions that provide > introspection of this nature. I ask because it seems like network- > to-network failures are a common enough occurrence that some > mechanism for archive verification is a must-have. I'm just trying > to determine how much of that functionality I'll have to build > myself... I don't think you'd have to have the archive_command track each copy of each WAL file... but you might want to do that anyway, just for status information. If only we had a database to store that info in... ;) As for WAL file numbering, even if there were some things you could deduce from the file numbers, I wouldn't want to bet on that. Future changes to the WAL code could change the naming scheme, which could result in subtle breakage to your system (granted, odds of that happening are low, but still...) -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Mon, 2007-05-07 at 15:10 -0500, Thomas F. O'Connell wrote: > I'm attempting to design a postgres system whereby an authoritative > primary server simultaneously feeds continuous archives to a number of > warm standby servers that live both on the local network and on remote > networks. > The sticking point in my current thinking about such a system is what > to do in the event that any of an array of possible nodes becomes > unreachable. I would expect a custom archive_command to have the > intelligence about network reachability and to report a nonzero status > if it was unable to submit an archive to any particular node. > What is the advised remedy for this scenario in general? There is no advised remedy. It is designed to allow you to program it any way you choose. You can choose to provide maximum availability, by allowing it to work even when one node is down, or you can choose to provide maximum protection, by ensuring that it does not work if any node is down. Or anywhere in between, as requirements dictate. One of the problems with providing the example as a simple cp command is that it tends to limit one's thinking about how much flexibility and control is being offered. pg_standby is just one of a number of possibilities for integration in this area. We can easily publish others. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com