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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
