| F.35. pg_walinspect — low-level WAL inspection | ||||
|---|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules and Extensions | Home | Next |
F.35. pg_walinspect — low-level WAL inspection#
Thepg_walinspect module provides SQL functions that allow you to inspect the contents of write-ahead log of a runningPostgreSQL database cluster at a low level, which is useful for debugging, analytical, reporting or educational purposes. It is similar topg_waldump, but accessible through SQL rather than a separate utility.
All the functions of this module will provide the WAL information using the server's current timeline ID.
Note
Thepg_walinspect functions are often called using an LSN argument that specifies the location at which a known WAL record of interestbegins. However, some functions, such aspg_logical_emit_message, return the LSNafter the record that was just inserted.
Tip
All of thepg_walinspect functions that show information about records that fall within a certain LSN range are permissive about acceptingend_lsn arguments that are after the server's current LSN. Using anend_lsn“from the future” will not raise an error.
It may be convenient to provide the valueFFFFFFFF/FFFFFFFF (the maximum validpg_lsn value) as anend_lsn argument. This is equivalent to providing anend_lsn argument matching the server's current LSN.
By default, use of these functions is restricted to superusers and members of thepg_read_server_files role. Access may be granted by superusers to others usingGRANT.
F.35.1. General Functions#
pg_get_wal_record_info(in_lsn pg_lsn) returns record#Gets WAL record information about a record that is located at or after the
in_lsnargument. For example:postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');-[ RECORD 1 ]----+-------------------------------------------------start_lsn | 0/E419E28end_lsn | 0/E419E68prev_lsn | 0/E419D78xid | 0resource_manager | Heap2record_type | VACUUMrecord_length | 58main_data_length | 2fpi_length | 0description | nunused: 5, unused: [1, 2, 3, 4, 5]block_ref | blkref #0: rel 1663/16385/1249 fork main blk 364If
in_lsnisn't at the start of a WAL record, information about the next valid WAL record is shown instead. If there is no next valid WAL record, the function raises an error.pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn) returns setof record#Gets information of all the valid WAL records between
start_lsnandend_lsn. Returns one row per WAL record. For example:postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;-[ RECORD 1 ]----+--------------------------------------------------------------start_lsn | 0/1E913618end_lsn | 0/1E913650prev_lsn | 0/1E9135A0xid | 0resource_manager | Standbyrecord_type | RUNNING_XACTSrecord_length | 50main_data_length | 24fpi_length | 0description | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775block_ref |The function raises an error if
start_lsnis not available.pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data boolean DEFAULT true) returns setof record#Gets information about each block reference from all the valid WAL records between
start_lsnandend_lsnwith one or more block references. Returns one row per block reference per WAL record. For example:postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');-[ RECORD 1 ]-----+-----------------------------------start_lsn | 0/1230278end_lsn | 0/12302B8prev_lsn | 0/122FD40block_id | 0reltablespace | 1663reldatabase | 1relfilenode | 2658relforknumber | 0relblocknumber | 11xid | 341resource_manager | Btreerecord_type | INSERT_LEAFrecord_length | 64main_data_length | 2block_data_length | 16block_fpi_length | 0block_fpi_info |description | off: 46block_data | \x00002a00070010402630000070696400block_fpi_data |This example involves a WAL record that only contains one block reference, but many WAL records contain several block references. Rows output by
pg_get_wal_block_infoare guaranteed to have a unique combination ofstart_lsnandblock_idvalues.Much of the information shown here matches the output that
pg_get_wal_records_infowould show, given the same arguments. However,pg_get_wal_block_infounnests the information from each WAL record into an expanded form by outputting one row per block reference, so certain details are tracked at the block reference level rather than at the whole-record level. This structure is useful with queries that track how individual blocks changed over time. Note that records with no block references (e.g.,COMMITWAL records) will have no rows returned, sopg_get_wal_block_infomay actually returnfewer rows thanpg_get_wal_records_info.The
reltablespace,reldatabase, andrelfilenodeparameters referencepg_tablespace.oid,pg_database.oid, andpg_class.relfilenoderespectively. Therelforknumberfield is the fork number within the relation for the block reference; seecommon/relpath.hfor details.Tip
The
pg_filenode_relationfunction (seeTable 9.101) can help you to determine which relation was modified during original execution.It is possible for clients to avoid the overhead of materializing block data. This may make function execution significantly faster. When
show_datais set tofalse,block_dataandblock_fpi_datavalues are omitted (that is, theblock_dataandblock_fpi_dataOUTarguments areNULLfor all rows returned). Obviously, this optimization is only feasible with queries where block data isn't truly required.The function raises an error if
start_lsnis not available.pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false) returns setof record#Gets statistics of all the valid WAL records between
start_lsnandend_lsn. By default, it returns one row perresource_managertype. Whenper_recordis set totrue, it returns one row perrecord_type. For example:postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500') WHERE count > 0 AND "resource_manager/record_type" = 'Transaction' LIMIT 1;-[ RECORD 1 ]----------------+-------------------resource_manager/record_type | Transactioncount | 2count_percentage | 8record_size | 875record_size_percentage | 41.23468426013195fpi_size | 0fpi_size_percentage | 0combined_size | 875combined_size_percentage | 2.8634072910530795The function raises an error if
start_lsnis not available.
F.35.2. Author#
Bharath Rupireddy<bharath.rupireddyforpostgres@gmail.com>