TransWikia.com

How to get lsn(xid) numbers of transactions from WAL file?

Database Administrators Asked by isxaker on February 6, 2021

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 ?

One Answer

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.

Answered by Laurenz Albe on February 6, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP