Thread: Patch to add a feature to pg_standby
When using pg_standby to remain in recovery mode on a warm standby system, if there is a need to perform other actions in coordination with recovery actions, the -x <auxiliary command> option implemented by this patch enables that coordination. I considered adding the ability to override the restoreCommand, however by keeping this separate and optional it is possible to force retries of the auxiliary command until successful and still utilize pg_usleep instead of looping within an external script or command. And the previous behavior of pg_standby remains unchanged (other than debug logging and documenting the option in usage) if the new option is omitted. I added this feature to help with synchronization of a content repository consisting of a PostgreSQL db for meta-information and a separate file store for content. The auxiliary command enables forcing an rsync of the file store that is at least as current as the found WAL segment file's db changes, and prevents recovery of that WAL file unless the rsync can be performed successfully. (See attached file: pg_standby.c.diff) Please consider incorporating this feature. Chris _____________________________________________ Christopher K. Johnson Director of IT DeskNet Inc. RHCE# 804005699817957 "A society grows great when old men plant trees whose shade they know they shall never sit in" - Greek Proverb
Attachment
On Wed, 2008-04-02 at 12:43 -0400, chris.johnson@desknetinc.com wrote: > Please consider incorporating this feature. Thanks, sounds very interesting. I'll have a look in more detail over next few days. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
chris.johnson@desknetinc.com wrote: > When using pg_standby to remain in recovery mode on a warm standby system, > if there is a need to perform other actions in coordination with recovery > actions, the -x <auxiliary command> option implemented by this patch > enables that coordination. I considered adding the ability to override the > restoreCommand, however by keeping this separate and optional it is > possible to force retries of the auxiliary command until successful and > still utilize pg_usleep instead of looping within an external script or > command. And the previous behavior of pg_standby remains unchanged (other > than debug logging and documenting the option in usage) if the new option > is omitted. > > I added this feature to help with synchronization of a content repository > consisting of a PostgreSQL db for meta-information and a separate file > store for content. > The auxiliary command enables forcing an rsync of the file store that is at > least as current as the found WAL segment file's db changes, and prevents > recovery of that WAL file unless the rsync can be performed successfully. > > (See attached file: pg_standby.c.diff) This could be implemented by a "pass-through" restore_command, that calls pg_standby, and does the custom action when pg_standby returns successfully. Or perhaps you could run the custom action in whatever script is copying the files to the directory. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > chris.johnson@desknetinc.com wrote: > > When using pg_standby to remain in recovery mode on a warm standby system, > > if there is a need to perform other actions in coordination with recovery > > actions, the -x <auxiliary command> option implemented by this patch > > enables that coordination. I considered adding the ability to override the > > restoreCommand, however by keeping this separate and optional it is > > possible to force retries of the auxiliary command until successful and > > still utilize pg_usleep instead of looping within an external script or > > command. And the previous behavior of pg_standby remains unchanged (other > > than debug logging and documenting the option in usage) if the new option > > is omitted. > > > > I added this feature to help with synchronization of a content repository > > consisting of a PostgreSQL db for meta-information and a separate file > > store for content. > > The auxiliary command enables forcing an rsync of the file store that is at > > least as current as the found WAL segment file's db changes, and prevents > > recovery of that WAL file unless the rsync can be performed successfully. > > > > (See attached file: pg_standby.c.diff) > > This could be implemented by a "pass-through" restore_command, that > calls pg_standby, and does the custom action when pg_standby returns > successfully. Yes, that's the preferred route for most cases. pg_standby was designed to be customisable, so if it works for Chris, thats OK. After some mulling on this, I'm not sure we need to include this in pg_standby however. If we did we'd end up having before/after commands and retry options etc. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> wrote on 04/30/2008 07:49:44 AM: > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > chris.johnson@desknetinc.com wrote: > > > When using pg_standby to remain in recovery mode on a warm standby system, > > > if there is a need to perform other actions in coordination with recovery > > > actions, the -x <auxiliary command> option implemented by this patch > > > enables that coordination. I considered adding the ability to > override the > > > restoreCommand, however by keeping this separate and optional it is > > > possible to force retries of the auxiliary command until successful and > > > still utilize pg_usleep instead of looping within an external script or > > > command. And the previous behavior of pg_standby remains unchanged (other > > > than debug logging and documenting the option in usage) if the new option > > > is omitted. > > > > > > I added this feature to help with synchronization of a content repository > > > consisting of a PostgreSQL db for meta-information and a separate file > > > store for content. > > > The auxiliary command enables forcing an rsync of the file storethat is at > > > least as current as the found WAL segment file's db changes, and prevents > > > recovery of that WAL file unless the rsync can be performed successfully. > > > > > > (See attached file: pg_standby.c.diff) > > > > This could be implemented by a "pass-through" restore_command, that > > calls pg_standby, and does the custom action when pg_standby returns > > successfully. > > Yes, that's the preferred route for most cases. > > pg_standby was designed to be customisable, so if it works for Chris, > thats OK. > > After some mulling on this, I'm not sure we need to include this in > pg_standby however. If we did we'd end up having before/after commands > and retry options etc. > > -- > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com > I did not wish to presume that the WAL file being copied or linked into the destination would cause no issues should the engine be shutdown during my custom action or retry loop, and subsequently restarted in recovery mode. Which is why I chose to insert executing the command after determining that the file was available, but before the mv/cp. I like your suggestion of before/after commands and retry options. It offers flexibility in pg_standby far beyond my specific needs. Not having studied internals of postgres I am not sure, but it seemed to me that the other value provided by this implementation instead of a 'pass-through' restore command is the use of pg_usleep in retry loops. If pg_standby were not to provide that retry loop for the after command, then another program would need to be written to do that. Adding the feature to pg_standby seemed the more elegant implementation. But if pg_usleep accomplishes nothing more than a sleep in a script would, then implementing the loop in pg_standby provides a trivial advantage. Chris
On Wed, Apr 30, 2008 at 12:49:44PM +0100, Simon Riggs wrote: > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > This could be implemented by a "pass-through" restore_command, > > that calls pg_standby, and does the custom action when pg_standby > > returns successfully. > > Yes, that's the preferred route for most cases. > > pg_standby was designed to be customisable, so if it works for > Chris, thats OK. > > After some mulling on this, I'm not sure we need to include this in > pg_standby however. If we did we'd end up having before/after > commands and retry options etc. Would those be good things to have as a whole package? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, 2008-04-30 at 07:31 -0700, David Fetter wrote: > On Wed, Apr 30, 2008 at 12:49:44PM +0100, Simon Riggs wrote: > > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > > This could be implemented by a "pass-through" restore_command, > > > that calls pg_standby, and does the custom action when pg_standby > > > returns successfully. > > > > Yes, that's the preferred route for most cases. > > > > pg_standby was designed to be customisable, so if it works for > > Chris, thats OK. > > > > After some mulling on this, I'm not sure we need to include this in > > pg_standby however. If we did we'd end up having before/after > > commands and retry options etc. > > Would those be good things to have as a whole package? I think the answer is we already do. It's called bash. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Wed, Apr 30, 2008 at 03:43:54PM +0100, Simon Riggs wrote: > On Wed, 2008-04-30 at 07:31 -0700, David Fetter wrote: > > On Wed, Apr 30, 2008 at 12:49:44PM +0100, Simon Riggs wrote: > > > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > > > This could be implemented by a "pass-through" restore_command, > > > > that calls pg_standby, and does the custom action when > > > > pg_standby returns successfully. > > > > > > Yes, that's the preferred route for most cases. > > > > > > pg_standby was designed to be customisable, so if it works for > > > Chris, thats OK. > > > > > > After some mulling on this, I'm not sure we need to include this > > > in pg_standby however. If we did we'd end up having before/after > > > commands and retry options etc. > > > > Would those be good things to have as a whole package? > > I think the answer is we already do. It's called bash. Just a stray thought...bash doesn't run very well on common Windows installations. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, 2008-04-30 at 08:29 -0700, David Fetter wrote: > On Wed, Apr 30, 2008 at 03:43:54PM +0100, Simon Riggs wrote: > > On Wed, 2008-04-30 at 07:31 -0700, David Fetter wrote: > > > On Wed, Apr 30, 2008 at 12:49:44PM +0100, Simon Riggs wrote: > > > > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > > > > This could be implemented by a "pass-through" restore_command, > > > > > that calls pg_standby, and does the custom action when > > > > > pg_standby returns successfully. > > > > > > > > Yes, that's the preferred route for most cases. > > > > > > > > pg_standby was designed to be customisable, so if it works for > > > > Chris, thats OK. > > > > > > > > After some mulling on this, I'm not sure we need to include this > > > > in pg_standby however. If we did we'd end up having before/after > > > > commands and retry options etc. > > > > > > Would those be good things to have as a whole package? > > > > I think the answer is we already do. It's called bash. > > Just a stray thought...bash doesn't run very well on common Windows > installations. True, but you can run anything you choose... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com