Re: archive_command

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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954