Re: [PATCH] Patch to compute Max LSN of Data Pages

On Tue, Nov 13, 2012 at 1:23 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Monday, November 12, 2012 9:56 PM Alvaro Herrera wrote:
> Robert Haas escribió:
>> On Tue, Jul 31, 2012 at 8:09 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>
>>> >> I think I can see all of those things being potentially useful. There
>>> >> are a couple of pending patches that will revise the WAL format
>>> >> slightly; not sure how much those are likely to interfere with any
>>> >> development you might do on (2) in the meantime.
>> >
>>> > Based on above conclusion, I have prepared a patch which implements Option-1
>>
>>> I wonder if we shouldn't make this a separate utility, rather than
>>> something that is part of pg_resetxlog. Anyone have a thought on that
>>> topic?
>
>> That thought did cross my mind too.
>
> One of the reasons for keeping it with pg_resetxlog, is that this was proposed as a solution for scenario's where user's db has become corrupt and now he
> want to start it. So to do it he can find the max LSN and set the same using pg_resetxlog, it will avoid the further corruption of database after it got started.
> If we keep it a separate utility then user needs to first run this utility to find max LSN and then use pg_resetxlog to achieve the same. I don't see a big problem in that
> but may be it would have been better if there are other usecases for it.

We might be able to use this utility to decide whether we need to take
a fresh backup from the master onto the standby, to start old master
as new standby after failover.

When starting new standby after failover, any data page in the standby must
not precede the master. Otherwise, the standby cannot catch up with the master
consistently. But, the master might write the data page corresponding to
the WAL which has not been replicated to the standby yet. So, if
failover happens
before that WAL has been replicated, the data page in old master would precede
new master (i.e., old standby), and in this case the backup is required. OTOH,
if maximum LSN in data page in the standby is less than the master, the backup
is not required.

Without this utility, it's difficult to calculate the maximum LSN of
data page, so
basically we needed to take a backup when starting the standby. In the future,
thanks to this utility, we can calculate the maximum LSN, and can skip a backup
if that LSN is less than the master (i.e., last applied LSN, IOW,
timeline switch LSN).

Regards,

--
Fujii Masao