Automated way to find actual COMMIT LSN of subxact LSN

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Automated way to find actual COMMIT LSN of subxact LSN
Date: 2019-03-19 17:16:34
Message-ID: CAMa1XUjZyq9sf1COSL-VPe9khpdu52WUoeWECUQDthGwtmb3vQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Thank you,
Jeremy

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-03-19 17:18:25 Re: Willing to fix a PQexec() in libpq module
Previous Message Alexander Korotkov 2019-03-19 17:10:31 Re: jsonpath