pg_walinspect

functions to inspect contents of PostgreSQL Write-Ahead Log

pg_walinspect : functions to inspect contents of PostgreSQL Write-Ahead Log

Overview

ID	Extension	Package	Version	Category	License	Language
6940	pg_walinspect	pg_walinspect	`1.1`	STAT	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
See Also	wal2json pgoutput test_decoding pg_logicalinspect decoderbufs wal2mongo decoder_raw pgmqtt kafka_fdw pgq

Packages

PG18	PG17	PG16	PG15	PG14
1.1	1.1	1.1	1.1	N/A

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION pg_walinspect;

Usage

pg_walinspect: low-level WAL inspection

pg_walinspect provides SQL functions to inspect write-ahead log (WAL) contents on a running server. Similar to pg_waldump but accessible via SQL.

Functions

pg_get_wal_record_info(in_lsn pg_lsn) – single WAL record details:

SELECT * FROM pg_get_wal_record_info('0/E419E28');

 start_lsn        | 0/E419E28
 end_lsn          | 0/E419E68
 prev_lsn         | 0/E419D78
 xid              | 0
 resource_manager | Heap2
 record_type      | VACUUM
 record_length    | 58
 main_data_length | 2
 fpi_length       | 0
 description      | nunused: 5, unused: [1, 2, 3, 4, 5]
 block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364

pg_get_wal_records_info(start_lsn, end_lsn) – all records in an LSN range:

SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740');

pg_get_wal_block_info(start_lsn, end_lsn, show_data) – block references from WAL records:

SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');

Returns per block reference: start_lsn, end_lsn, block_id, reltablespace, reldatabase, relfilenode, relforknumber, relblocknumber, xid, fork_flags, block_data, block_fpi_data, etc.

pg_get_wal_stats(start_lsn, end_lsn, per_record) – aggregate WAL statistics:

SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
WHERE count > 0;

Tips

Use FFFFFFFF/FFFFFFFF as end_lsn to read up to the current server LSN
If in_lsn is not at a record boundary, the next valid record is returned
All functions use the server’s current timeline ID

Access

Restricted to superusers and members of pg_read_server_files.