See What’s Inside a WAL File Using pg_waldump and pg_walinspect

In this blog, we will see the concepts for analyzing WAL files, such as pg_waldump (supported versions 12 and above) and pg_walinspect (supported versions 15 and above).

Write-Ahead Logging (WAL) is a standard approach to transaction logging and one of the database’s functional components.

• WAL is a standard method for ensuring data durability. It records changes to the database before flushing the actual data to datafiles or rel files, helping recover the database to a consistent state in case of failure.
• The primary benefit of using WAL is that it is ACID-compliant, ensuring Durability. It also significantly reduces the number of writes and supports online backup and point-in-time restore.
• WAL saves the entire data page content (Full Page Write) in the log if necessary to prevent Torn Pages and ensure consistency of the starting page for any recovery.

pg_waldump

The utility pg_waldump demonstrates human-readable information from the WAL file segments.

Requirements

• Set wal_level to ‘replica’ or higher to access the WAL functions, and on RDS, set wal_level to ‘logical’ to execute the WAL functions.
• The user who installed the server or who has read access to the data directory can only use pg_waldump.

Usage and demonstration of pg_waldump

• pg_waldump  -p [path to wal files] -s  <start LSN> -e  [end LSN] [walfile]
• pg_waldump -p [path to wal files] <wal file>
• pg_waldump <walfile> -q
  -q, –quiet : do not print any output, except for errors

Below, we demonstrate how to use pg_waldump using the WAL function pg_current_wal_lsn (last write location).

As a first step, we will capture the current pg_lsn to analyze the details of the WAL.

Next, we will perform a few SQL operations to analyze them in the WAL file.

Lastly,  we will capture the current pg_lsn to analyze the details of the WAL.

We will use the utility pg_waldump and try to read the SQL from the WAL file.

As per the sequence of operations, initially in Waldump, we can see CREATE, through which object(table: region) is created in base/16388/16390, and then the COMMIT transaction is performed.

Next, after COMMIT and for the ALTER operation performed as in the example, CREATE INDEX(pk_region) is performed, and then COMMIT of a transaction is performed.

Later, after COMMIT, and for the UPDATE operation performed, as in the example, HOT_UPDATE is performed, and then COMMIT of a transaction is performed.

Lastly, after performing the COMMIT and DELETE operations as in the example, you can see the DELETE, Transaction COMMIT, and then snapshot through RUNNING_XACTS.

In current supported PG versions 12.x and above, developers have implemented pg_waldump for usage, and they have created a new feature, pg_walinspect, for debugging WALs in detail using SQL from version 15.x onwards.

pg_walinspect

pg_walinspect module provides SQL functions for inspecting and debugging WALs that are accessible through SQL rather than a separate utility.

Installation of pg_walinspect

Usage and demonstration of pg_walinspect

Below is the usage of pg_walinspect, where we use functions to demonstrate the operations through WALs and can check the resource_manager, record_type, and description columns specifically for analysis.

Below, we inspect the WAL using the same SQL commands executed for pg_waldump.

Initially, we can see CREATE through which object is created in base/16388/16410 and then COMMIT.

 Secondly, after COMMIT, a snapshot is taken through RUNNING_XACTS and INSERT and then COMMIT.

Next, after COMMIT and ALTER operation performed as in the example, we see CREATE INDEX followed by COMMIT. 

Later, after COMMIT, for the UPDATE operation, HOT_UPDATE is performed, and then COMMIT.

Lastly, after COMMIT, for the DELETE operation, we can see a DELETE record followed by COMMIT.

Access and Illustration for using pg_walinspect

Here we will see the access needed for the user to execute the functions for pg_walinspect.

pg_walinspect  can be used by the user who has been granted the role ‘pg_read_server_files‘.

ERROR: permission denied for function pg_get_wal_records_info.

Now, we will grant access to ‘pg_read_server_files’ to the role.

Now user ‘wal_user’ should be able to access the functions.

• CREATE: Creates an object. (table, index, etc.)
• RUNNING_XACTS: Captures the snapshot with current active transactions.
• INSERT_LEAF: Operation of a B-Tree Index.
• COMMIT: Commits transaction/s.
• DELETE: Deletes rows or columns of objects.
• HOT_UPDATE: Updates rows or columns of objects.

Conclusion

We have reviewed the usage and analysis of WAL files using the pg_waldump utility and the pg_walinspect extension. Additionally, you can review a few related blogs on WAL in PostgreSQL:

• Speed Up of the WAL Archiving in PostgreSQL 15
• Find the WAL Count Between Two Segments in PostgreSQL

Our PostgreSQL Performance Tuning eBook condenses years of database expertise into a practical guide for optimizing your PostgreSQL databases. Inside, you’ll discover our most effective PostgreSQL performance strategies derived from real-world experience.

&nbsp;

Download now: PostgreSQL Performance Tuning