pg_waldump

Display WAL records from a specific segment

$ pg_waldump [start_segment]

$ pg_waldump [start_segment] [end_segment]

$ pg_waldump [start_segment] [end_segment] [[-p|--path]] [path]

$ pg_waldump [start_segment] [end_segment] [[-f|--follow]]

$ pg_waldump [start_segment] [end_segment] [[-n|--limit]] [count]

$ pg_waldump [start_segment] [end_segment] [[-z|--stats]]

$ pg_waldump [start_segment] [end_segment] [[-r|--rmgr]] [rmgr_name]

$ pg_waldump [[-?|--help]]

pg_waldump [option...] [start_lsn [end_lsn]]

-b, --bkp-details
    Output full-page write and record-image details for deeper analysis.

-E LSN, --end=LSN
    Stop reading WAL records at the specified LSN (exclusive).

-f, --follow
    Keep reading until interrupted, useful for monitoring live WAL activity.

-n N, --limit=N
    Limit the amount of WAL to dump to the first N records found.

-p PATH, --path=PATH
    Specify the path to the WAL directory or a specific WAL file to read from.

-r RmgrName, --rmgr=RmgrName
    Only display records originating from a specific resource manager (e.g., 'Heap', 'XLog').

-s LSN, --start=LSN
    Start reading WAL records from the specified LSN.

-t TIMELINEID, --timeline=TIMELINEID
    Specify the timeline ID from which to read WAL records.

-V, --version
    Output version information, then exit.

-w, --full-page-writes
    (Deprecated) Output full-page write details. Use -b instead.

-z[=record], --stats[=record]
    Output summary statistics about the WAL records dumped, optionally per record type.

-?, --help
    Show a help message and exit.

pg_waldump is a PostgreSQL utility designed to display the contents of Write-Ahead Log (WAL) files in a human-readable format. WAL files record all changes made to a PostgreSQL database, ensuring data integrity and durability. By dumping these files, administrators and developers can analyze the sequence of operations, troubleshoot replication issues, understand database activity, or perform forensic analysis.

The utility can read WAL files from a specified directory (usually pg_wal) or from specific file paths. It decodes various record types, providing details like transaction IDs, Log Sequence Numbers (LSNs), and information about the operations performed, such as table insertions, updates, or deletions, as well as checkpoint records and full page writes. This tool is invaluable for deep-dive investigations into database behavior and understanding the underlying mechanics of PostgreSQL.

Using pg_waldump requires read access to the WAL files, which might be restricted for security reasons. The output can be extremely verbose, making filtering with LSN ranges (-s, -E) or resource managers (-r) essential for practical use. Understanding the detailed output requires familiarity with PostgreSQL's internal WAL record structures. Be cautious when using --follow on a busy system, as it can consume resources.

LSNs are an internal PostgreSQL concept used to represent a precise position in the WAL stream. They are critical for point-in-time recovery, replication, and understanding the exact order of operations within the database. pg_waldump leverages LSNs to specify start and end points for dumping, allowing users to inspect specific ranges of database activity.

PostgreSQL organizes WAL records by 'resource managers,' which are components responsible for specific parts of the database system (e.g., Heap for table data, XLog for WAL management itself, Btree for index operations). The -r option allows users to filter WAL output by these managers, enabling focused analysis on particular aspects of database behavior.

pg_waldump is an indispensable tool for debugging mysterious database behavior, understanding the impact of specific transactions, or performing post-mortem analysis after a crash or data corruption incident. It provides the low-level details needed to reconstruct events, identify problematic operations, and ensure data integrity.

pg_waldump has been a fundamental utility within the PostgreSQL ecosystem for many major versions, continually evolving to provide more robust and detailed insights into WAL records. Its functionality has expanded in parallel with new WAL record types and features introduced in PostgreSQL, offering increasingly granular options for filtering and statistical analysis to aid in database administration and debugging.

pg_ctl(1), psql(1), pg_basebackup(1), pg_receivewal(1), pg_rewind(1), ls(1), grep(1)