Re: Automated way to find actual COMMIT LSN of subxact LSN

At Tue, 19 Mar 2019 12:16:34 -0500, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote in <CAMa1XUjZyq9sf1COSL-VPe9khpdu52WUoeWECUQDthGwtmb3vQ(at)mail(dot)gmail(dot)com>
> I want to build automation to recover a database to a specific LSN
> *inclusive*, even if that LSN is from a subtransaction. The problem I am
> facing is that I know what specific LSN wrote a row on a remote system, but
> if I create a recovery.conf file with:
>
> recovery_target_lsn = '95F/BBA36DF8'
>
> and 95F/BBA36DF8 is actually a subtransaction, then even if I use default
> behavior of recovery_target_inclusive = true, that transaction will NOT be
> included in the restore point, because it is prior to the actual COMMIT LSN
> of which this lsn/subxact is a part.
>
> My hack for now is to simply manually scan down until I find the COMMIT,
> which is the only way so far I can figure to find it out. I don't want to
> hack some kind of search script based on this if there is already a better
> way to get this information... anyone know of a way?

FWIW it seems to be the only way starting from an LSN. If you can
identify the XID or end timestamp of the transaction, it would be
usable instead.

If recovery_target_inclusive were able to take the third value
"xact", is it exactly what you want?

And is it acceptable?

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center