Re: warm standby with WAL shipping

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