How to get lsn(xid) numbers of transactions from WAL file? I'm looking for a way of getting transactions from WAL files.
Basically I need their LSN numbers.
(ideally both transactions LSN numbers and WAL files first and last LSN numbers)
I have a PostgreSQL cluster binary backup and set of WAL files.
I would like to perform PITR but first I need to find the point where I need to restore to.
So I need to find a particular transaction LSN or XID number which later I'm goin to use as recovery_target.
Could you please give an idea how to get LSN numbers or XID numbers from WAL file ?
Run pg_waldump on the WAL segment.
Then you will get lines like
rmgr: Heap        len (rec/tot):    115/   115, tx:        564, lsn: 0/01FECDB0, prev 0/01FECC88, desc: HOT_UPDATE off 1 xmax 564 flags 0x60 ; new off 2 xmax 0, blkref #0: rel 1663/16385/1417 blk 0

In there you have tx: 564, lsn: 0/01FECDB0, which is your desired information.