Olavi Kuusela

Reading a WAL file in a hex editor

2 June 2026 · 5 min read

After the backup incident I wanted to know what was actually in the -wal file that cp had been ignoring. The format is short enough to read by eye, and knowing it made the earlier post much less mysterious to me.

The header

The first 32 bytes describe the log:

0  magic        0x377f0682 or 0x377f0683
4  format       3007000 (version 3.7.0)
8  page size    e.g. 4096
12 checkpoint   sequence number of the last checkpoint
16 salt-1
20 salt-2
24 checksum-1
28 checksum-2

The low bit of the magic tells you the checksum byte order: ...82 means big-endian, ...83 little-endian. The salts are the important part.

Frames

Everything after the header is a sequence of frames. Each frame is a 24-byte header followed by one page of data:

0  page number   which page of the database this frame replaces
4  commit size   0 for uncommitted frames, number of pages otherwise
8  salt-1        must match the header
12 salt-2        must match the header
16 checksum-1
20 checksum-2

A frame whose salts do not match the file header is not part of this log — it is left over from a previous generation of the database file. This is how SQLite avoids including stale frames, and it is also why the checkpoint sequence number exists.

The commit size field marks transaction boundaries. A frame with a non-zero commit size ends a transaction and refers to the number of pages committed. Everything before it belongs to the same transaction.

Walking the file

import struct, sys

data = open(sys.argv[1], 'rb').read()
magic, version, page_size = struct.unpack_from('>III', data, 0)
salt1, salt2 = struct.unpack_from('>II', data, 16)
big = magic & 1 == 0
offset = 32
page_no = 0

while offset + 24 + page_size <= len(data):
    pg, commit, s1, s2, _c1, _c2 = struct.unpack_from('>IIIII', data, offset)
    if s1 != salt1 or s2 != salt2:
        print('stale frame at', offset)
        break
    page_no += 1
    print(f'frame {page_no}: page {pg}' + (f' COMMIT ({commit} pages)' if commit else ''))
    offset += 24 + page_size

Run it against a live -wal and you get a short history of what changed and where the transaction boundaries were. That was enough to explain why the nightly copy was about a day behind: the service rarely wrote enough to trigger a checkpoint, so almost everything lived in the log.

Why this matters in practice

You do not need to parse WAL files to run a service — .backup does the right thing without you knowing any of this. But the next time a restore looks suspiciously old, being able to count frames and check salts turns a vague feeling into an answer in about two minutes.

← All posts