Re: warm standby with WAL shipping

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