On Tue, 4 Oct 2005, Simon Riggs wrote:
>> What would constitute an intermediate rebuild? Of course the system is up and
>> live and having data added to it. How would one restore from multiple
>> timelines?
>
> This is only if you are back up and working on the went-down box:
>
> intermediate rebuild: I mean that you will have to restore the data from
> the period of switchover, manually extract the relevant data with SQL
> and then re-insert those changes yourself and resolve conflicts.
I think we're likely out of luck here, but let me provide a few more details
so you might give me a better idea.
We are up on the went-down box with a pg_dump restore from the 3:30a.m. of the
previous day. We have WAL files available from the box that was up during the
failure of the primary. What I do not have is a base backup of the box which
was up for part of the day while we brought up the went-down box. So, unless
there is some method of replaying WAL files by hand one at a time, I think we
are out of luck, no?
>
> There isn't a process to merge the two log streams. The same txnid will
> have been used on both servers to refer to separate transactions, so
> there can be no automated way of resolving the data. It has to be done
> using business domain knowledge rather than log data.
>
> All of that's no different from any other RDBMS, as I'm sure you know.
I do know, and in fact, I think PITR is great, I just wish we had thought
about the corner case of the servers switching roles between the biweekly base
backups.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954