sudohulk
Theme

LSN to WAL file

Which WAL segment file holds an LSN, and how many bytes into it.

WAL file
0000000100000016000000B3the timeline, then the segment number in two halves
Offset in it
7657544bytes, 0x74D848
Segment
5,81116 MB, from 16/B3000000 to 16/B3FFFFFF

Worked out as pg_walfile_name_offset() works it out in PostgreSQL 18.3, and tested against what it prints. With JavaScript off, this is the example the build worked out.

How it is worked out

segment = lsn / segment_size
offset  = lsn % segment_size
file    = %08X of timeline
        + %08X of segment / (2^32 / segment_size)
        + %08X of segment % (2^32 / segment_size)

An LSN is a byte position in the WAL, 64 bits wide, printed as its high and low 32 bits in hex: 16/B374D848 is 0x16B374D848.

With 16 MB segments the file name is the timeline, then the high 32 bits, then the next 8: 16/B374D848 is in 0000000100000016000000B3.

An LSN exactly on a boundary, such as 0/1000000, names the file that starts there, at offset 0.

The server uses the timeline it is inserting on and the wal_segment_size initdb chose, 16 MB unless --wal-segsize said otherwise. On a standby it refuses, with recovery is in progress.

The same answer from a server:

SELECT * FROM pg_walfile_name_offset('16/B374D848');

For an agent in the browser, where it has WebMCP, this page offers the tool as lsn_to_wal_file. Every pocket tool.

↑↓ move · ↵ open · esc close