How to read a pg_waldump output

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();
pg_current_wal_location 
7C/122C512
(1 row)

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

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.

Example:

postgres=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
════════════════════
0/53184F8
(1 row)

postgres=# create database test;
CREATE DATABASE
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);
CREATE TABLE 

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

test=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn 
════════════════════
0/53CD230
(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.

Search

Was this article helpful?