F.37. pg_walinspect
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 current server's timeline ID.
All the functions of this module will try to find the first valid WAL record that is at or after the givenin_lsn orstart_lsn and will emit error if no such record is available. Similarly, theend_lsn must be available, and if it falls in the middle of a record, the entire record must be available.
Note
Some functions, such aspg_logical_emit_message, return the LSNafter the record just inserted. Therefore, if you pass that LSN asin_lsn orstart_lsn to one of these functions, it will return thenext record.
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.37.1. General Functions
pg_get_wal_record_info(in_lsn pg_lsn) returns recordGets WAL record information of a given LSN. If the given LSN isn't at the start of a WAL record, it gives the information of the next available valid WAL record; or an error if no such record is found. For example, usage of the function is as follows:
postgres=# SELECT * FROM pg_get_wal_record_info('0/1E826E98');-[ RECORD 1 ]----+----------------------------------------------------start_lsn | 0/1E826F20end_lsn | 0/1E826F60prev_lsn | 0/1E826C80xid | 0resource_manager | Heap2record_type | PRUNErecord_length | 58main_data_length | 8fpi_length | 0description | snapshotConflictHorizon 33748 nredirected 0 ndead 2block_ref | blkref #0: rel 1663/5/60221 fork main blk 2pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn) returns setof recordGets information of all the valid WAL records between
start_lsnandend_lsn. Returns one row per WAL record. Ifstart_lsnorend_lsnare not yet available, the function will raise an error. 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 |pg_get_wal_records_info_till_end_of_wal(start_lsn pg_lsn) returns setof recordThis function is the same as
pg_get_wal_records_info(), except that it gets information of all the valid WAL records fromstart_lsntill the end of WAL.pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false) returns setof recordGets 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. Ifstart_lsnorend_lsnare not yet available, the function will raise an error. 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.8634072910530795pg_get_wal_stats_till_end_of_wal(start_lsn pg_lsn, per_record boolean DEFAULT false) returns setof recordThis function is the same as
pg_get_wal_stats(), except that it gets statistics of all the valid WAL records fromstart_lsntill end of WAL.
F.37.2. Author
Bharath Rupireddy<bharath.rupireddyforpostgres@gmail.com>