--- name: postgres-wal-reading description: Read PostgreSQL's write-ahead log on a running server or from a pg_wal directory. Turn an LSN into a WAL file name and offset, measure how much WAL a workload wrote, list and summarise records with pg_walinspect and pg_waldump, and explain full-page images, the page LSN (pd_lsn) and why redo skips a record. Use when a task involves an LSN, a WAL segment file, pg_waldump output, WAL volume or full-page writes. Distilled from the PostgreSQL 18 documentation and source. license: CC-BY-4.0 --- # Reading PostgreSQL's write-ahead log A field guide for questions about WAL on PostgreSQL 16 or later, checked against 18. Each fact comes from the PostgreSQL documentation or source listed under Sources at the end, and a version is named where it matters. Everything here ships with PostgreSQL itself. ## 1. An LSN is a byte position - A log sequence number (type `pg_lsn`) is a 64-bit byte offset into the WAL stream, printed as two hexadecimal halves: `16/B374D848` is `0x16B374D848`, and the first half is the high 32 bits. - LSNs only grow. Compare them only along one WAL history: a primary and its physical standbys share one, two separate clusters do not. - One LSN minus another is the number of bytes between them, as `numeric`, and `pg_wal_lsn_diff(a, b)` is the same `a - b`. An LSN plus or minus a number of bytes is an LSN. ```sql SELECT '0/1A2B3C4D'::pg_lsn - '0/1A2B0000'::pg_lsn; -- 15437 SELECT pg_wal_lsn_diff('1/0', '0/FF000000'); -- 16777216, one 16 MB segment SELECT '0/1A2B3C4D'::pg_lsn + 8192; -- 0/1A2B5C4D ``` ## 2. Where the server is now | Function | What it returns | |---|---| | `pg_current_wal_insert_lsn()` | the logical end of WAL, where the next record will go | | `pg_current_wal_lsn()` | the write position: what has left the server's WAL buffers, the end a reader outside the server can see | | `pg_current_wal_flush_lsn()` | the last position known to be on durable storage | The insert position is never behind the write position, and the write position never behind the flush position. None of the three runs during recovery. On a standby, `pg_last_wal_receive_lsn()` gives what streaming replication has received and synced (NULL without streaming), and `pg_last_wal_replay_lsn()` what has been replayed. `pg_control_checkpoint()` reads the control file: `checkpoint_lsn`, `redo_lsn` (where crash recovery would start replaying), `redo_wal_file`, `timeline_id`, `full_page_writes` and `checkpoint_time`, among others. ## 3. How much WAL a workload wrote ```sql SELECT pg_current_wal_insert_lsn() AS before \gset -- run the workload SELECT pg_size_pretty(pg_current_wal_insert_lsn() - :'before'::pg_lsn); ``` That counts WAL from every session, so measure on an otherwise idle server. For one statement, `EXPLAIN (ANALYZE, WAL)` reports the records, full-page images and bytes it wrote. Cluster-wide totals since the last reset are in `pg_stat_wal`: `wal_records`, `wal_fpi`, `wal_bytes`. ## 4. From an LSN to a file, and back - `SHOW wal_segment_size;` is usually 16MB; `initdb --wal-segsize` sets it for a cluster. - A segment file in `pg_wal` has a 24-digit hexadecimal name: 8 digits of timeline, then the segment number (the LSN divided by the segment size) written as two 8-digit halves. - With 16 MB segments, the file for LSN `X/Y` on timeline `T` is `T`, `X` and the top byte of `Y`, each padded to 8 digits; the rest of `Y` is the offset in the file. On timeline 1, `0/1A2B3C4D` is in `00000001000000000000001A`, `0x2B3C4D` bytes in. - On a primary, `pg_walfile_name(lsn)` and `pg_walfile_name_offset(lsn)` do this for you, with the server's current timeline; they refuse to run during recovery. `pg_split_walfile_name(name)` (16 and later) goes the other way, to the segment number and the timeline. ```sql SELECT * FROM pg_walfile_name_offset('0/1A2B3C4D'); -- file_name | file_offset -- 00000001000000000000001A | 2833485 (on timeline 1) ``` A timeline above 1 means the cluster was promoted or recovered to a point in time, and the same LSN can then exist on two timelines, in two differently named files. ## 5. The records, from SQL: pg_walinspect An extension in the core distribution (added in 15; as described here, 16 and later). By default only superusers and members of `pg_read_server_files` may call it. ```sql CREATE EXTENSION pg_walinspect; -- The record at this LSN, or the next valid one after it; an error if there is none. SELECT * FROM pg_get_wal_record_info('0/1A2B3C4D'); -- Every record in a range. The start must be WAL that still exists; an end past the -- current end of WAL (up to FFFFFFFF/FFFFFFFF) simply means "to the end". SELECT start_lsn, xid, resource_manager, record_type, record_length, fpi_length, description, block_ref FROM pg_get_wal_records_info('0/1A000000', 'FFFFFFFF/FFFFFFFF') LIMIT 20; -- What kinds of record, and how much of the volume is full-page images. SELECT * FROM pg_get_wal_stats('0/1A000000', pg_current_wal_lsn(), per_record => true) ORDER BY combined_size DESC LIMIT 10; -- One row per block a record touches: which relation and block, and the image's size if any. SELECT start_lsn, reltablespace, reldatabase, relfilenode, relblocknumber, block_fpi_length FROM pg_get_wal_block_info('0/1A000000', '0/1B000000', show_data => false); ``` To name the relation, connect to the database whose OID is `reldatabase` and run `SELECT pg_filenode_relation(0, );` (0 stands for the database's default tablespace; otherwise pass `reltablespace`). ## 6. The records, from the files: pg_waldump It reads segment files directly, so it runs as the operating-system user that owns the data directory. Its results can be wrong while the server is still writing the files it reads, and it will not read a file ending in `.partial`. ```bash pg_waldump -p "$PGDATA/pg_wal" -s 0/1A000000 -e 0/1B000000 | head pg_waldump -p "$PGDATA/pg_wal" -s 0/1A000000 -e 0/1B000000 --stats=record # volume by record type pg_waldump -p "$PGDATA/pg_wal" -s 0/1A000000 -r Heap -n 50 # heap records only pg_waldump -p "$PGDATA/pg_wal" -s 0/1A000000 -R 1663/5/16384 -B 0 # one block's history pg_waldump -p "$PGDATA/pg_wal" -s 0/1A000000 -w # only records with full-page images pg_waldump "$PGDATA/pg_wal/00000001000000000000001A" # one segment ``` `pg_waldump -r list` names the resource managers, `-x ` follows one transaction, and `--save-fullpage=DIR` (16 and later) writes each full-page image to a file. The `-R`, `-B` and `-w` filters are there from 15. Each output line gives the resource manager (`rmgr:`), the record's own length and its total length with any images (`len (rec/tot):`), the transaction id (`tx:`), the record's LSN and the previous record's (`lsn:`, `prev`), and a description (`desc:`) that ends with the blocks it touches: `blkref #N: rel // blk `, with `FPW` after a block whose full image the record carries. At the very end of a live directory, an error about an invalid record length usually marks only where written WAL stops. ## 7. Full-page images - With `full_page_writes` on (the default), the first change to a page after a checkpoint logs the whole page. A crash during an operating-system page write can leave a page half old and half new, and a row-level change cannot repair that; the image can. The code's test: the page's LSN is at or before the redo pointer of the last checkpoint. - Images are logged during a base backup even with `full_page_writes` off. - A change to hint bits alone is normally not logged. With data checksums on (initdb's default from PostgreSQL 18) or `wal_log_hints` on, the first such change after a checkpoint logs the page's image, as an `FPI_FOR_HINT` record of the `XLOG` resource manager. - `wal_compression` (`pglz`, `lz4` or `zstd`; off by default) compresses the images, and only them. - So WAL volume jumps right after each checkpoint, then falls as pages have had their first change, and more frequent checkpoints mean more images. `wal_fpi` in `pg_stat_wal`, and `fpi_size` in `pg_get_wal_stats`, measure it. ## 8. The page LSN, and why redo skips a record - Every data page's header holds an LSN, `pd_lsn`. When a change is logged, `XLogInsert` returns the position just past the new record, the end of it, and the page's LSN is set to that. - The write-ahead rule: before the buffer manager writes out a dirty page, WAL must be flushed at least up to that page's LSN. - Recovery reads `pg_control`, then the checkpoint record, and replays forward from its redo pointer. For each block a record touches: if the record carries an image to restore, the page is replaced by it; otherwise the change is applied only when the page's LSN is before the record's end. A page already at or past it got the change onto disk before the crash, so the record is skipped for that page. That is why replaying a record a second time does no harm. ## Pitfalls - `pg_walfile_name` uses the current timeline. A location from before a promotion is in a file named with the older timeline. - WAL before the last checkpoint's redo pointer may already be recycled or removed, unless `wal_keep_size`, a replication slot or archiving keeps it. `pg_walinspect` then raises an error for a start it cannot read. - An LSN says where a record is in the stream, not when it was written. For a time, look for the commit record's timestamp in `desc:`. ## Sources Distilled, in its own words, from the PostgreSQL 18 documentation (https://www.postgresql.org/docs/18/): the `pg_lsn` type (`datatype-pg-lsn.html`), the backup control and recovery information functions (`functions-admin.html`), `pg_control_checkpoint` (`functions-info.html`), `pg_walinspect` (`pgwalinspect.html`), `pg_waldump` (`pgwaldump.html`), the WAL settings (`runtime-config-wal.html`), WAL internals (`wal-internals.html`) and the PostgreSQL 15, 16 and 18 release notes; and from the source at `REL_18_STABLE`: `src/backend/access/transam/README` ("Write-Ahead Log Coding"), `xloginsert.c` and `xlogutils.c`. Those are © The PostgreSQL Global Development Group, under the PostgreSQL License. Any error here is this skill's, not theirs. Vignesh Kumar A writes about the same machinery in prose at https://sudohulk.com/tags/postgresql.