Re: warm standby with WAL shipping

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