How to read a pg_waldump output

Florin Irion
Florin Irion

pg_waldump (or pg_xlogdump for 9.3 to 9.6 users of PostgreSQL) is a useful utility for debugging and diagnostic purposes, which can be used to inspect the internals of a WAL (Write Ahead Log) file of PostgreSQL by producing a human readable output.

Write-Ahead Logging (WAL) is the robust method implemented by PostgreSQL to ensure data integrity and durability. As described in PostgreSQL's documentation, "WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage".

WAL files are stored in the pg_wal directory inside $PGDATA, with a naming convention that is made up of 3 sequences of 8 hexadecimal digits defining:

  • Timeline ID
  • Block ID
  • Segment ID

Each WAL file has a size of 16MB (by default, but it can be changed from PostgreSQL 11 during instance initialisation), and the server switches to a new file once this maximum size is reached or when no new file has been written since a time of archive_timeout, parameter of postgresql.conf.

PostgreSQL provides several functions to assist with WAL management, including:

  • pg_current_wal_lsn(), which displays the current write-ahead log write location, and
  • pg_walfile_name(), which returns just the write-ahead log file name of a given LSN
SELECT pg_current_wal_lsn();
(1 row)

SELECT pg_walfile_name('7C/122C512');

The first function gives us the Log Sequence Number that is composed by Block ID an segment ID(e.g., "7C/122C512").

From the first eight digits in the second output we can see that is on the first timeline, it also prints the Block ID is 7C with a segment ID of 1.

Now lets see pg_waldump:

pg_waldump 000000010000000000000001
rmgr: Heap len (rec/tot): 73/ 2545, tx: 3, lsn: 0/0107BC48, prev 0/01079C48, desc: UPDATE off 34 xmax 3 ; new off 14 xmax 0, blkref #0: rel 1663/1/1259 blk 4 FPW, blkref #1: rel 1663/1/1259 blk 0

Each field being in more details:

  • rmgr, displays the resource manager that generated the record
  • len, displays the length of the record (if different it means that is compressed)
  • tx, ID of the transaction involved with this record
  • lsn, log sequence number, including the previous and current lsn.
  • desc, for the description of the action record is doing, and some information related to the relation and page item with which interacts the action.


postgres=# SELECT pg_current_wal_lsn();
(1 row)

postgres=# create database test;
Time: 1229,509 ms (00:01,230)

postgres=# \c test
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1), server 10.5)
You are now connected to database "test" as user "postgres".

test=# create table testtable (i int);

test=# insert into testtable values (generate_series(1,10000));
INSERT 0 10000

test=# SELECT pg_current_wal_lsn();
(1 row)
pg_waldump 000000010000000000000005
rmgr: Database len (rec/tot): 42/ 42, tx: 568, lsn: 0/05318D18, prev 0/05318CA8, desc: CREATE copy dir 1/1663 to 16388/1663


rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/05318E70, prev 0/05318E38, desc: CREATE base/16388/16389
rmgr: Heap len (rec/tot): 54/ 7550, tx: 569, lsn: 0/05318EA0, prev 0/05318E70, desc: INSERT off 27, blkref #0: rel 1663/16388/1247 blk 6 FPW
rmgr: Btree len (rec/tot): 53/ 7613, tx: 569, lsn: 0/0531AC38, prev 0/05318EA0, desc: INSERT_LEAF off 376, blkref #0: rel 1663/16388/2703 blk 1 FPW


rmgr: Heap len (rec/tot): 59/ 59, tx: 570, lsn: 0/05330680, prev 0/05330648, desc: INSERT+INIT off 1, blkref #0: rel 1663/16388/16389 blk 0
rmgr: Heap len (rec/tot): 59/ 59, tx: 570, lsn: 0/053306C0, prev 0/05330680, desc: INSERT off 2, blkref #0: rel 1663/16388/16389 blk 0
rmgr: Heap len (rec/tot): 59/ 59, tx: 570, lsn: 0/05330700, prev 0/053306C0, desc: INSERT off 3, blkref #0: rel 1663/16388/16389 blk 0


rmgr: Transaction len (rec/tot): 34/ 34, tx: 570, lsn: 0/053CD1D0, prev 0/053CD190, desc: COMMIT 2018-12-21 14:42:28.363582 CET

Here we can see that CREATE DATABASE was a Database type resource manager, with a record length of 42 (not compressed), transaction 568, log sequence number 0/05318D18 and previous 0/05318CA8 that created a directory named "16388` from the table default tablespace "1663"

The CREATE TABLE was a Storage type resource manager, with a record length of 42 (not compressed), transaction 0 (the actual transaction starts with the next row tx: 569), the log sequence number is 0/05318E70 and the previous is 0/05318E38 with the short description in the end.

After we can see the INSERT that is a heap resource manager and we can read all the information that we need.

At the end of every transaction we can see the COMMIT with its values and the exact time.

You can filter the dump with various options, for further information please take a look at the PostgreSQL pg_waldump documentation.

Was this article helpful?

0 out of 0 found this helpful