WAL

Activity

-- pgMonitor Exporter (ccp_wal_activity)
SELECT 
    last_5_min_size_bytes,
    (SELECT COALESCE(sum(size),0) FROM pg_catalog.pg_ls_waldir()) AS total_size_bytes
FROM 
    (SELECT COALESCE(sum(size),0) AS last_5_min_size_bytes 
     FROM pg_catalog.pg_ls_waldir() 
     WHERE modification > CURRENT_TIMESTAMP - '5 minutes'::interval) x

Current WAL LSN

SELECT pg_current_wal_lsn();

Last WAL Received/Replayed (Replication Target)

SELECT pg_last_wal_receive_lsn() last_received, pg_last_wal_replay_lsn() last_replayed

Bytes Between Two LSNs

select '30/77000060'::pg_lsn - '2C/7D000000'::pg_lsn size_bytes;

Useful PG_WALINSPECT Extension Queries

select * from pg_get_wal_records_info('275/20000000','275/200018F0');

select * from pg_get_wal_record_info('275/20001850');

select * from pg_get_wal_block_info('275/20000000','275/200018F0', true);

select * from pg_get_wal_stats('275/20000000','275/200018F0');

select n.nspname,
      case 
         when c.relkind = 'r' then 'table'
         when c.relkind = 'i' then 'index'
         when c.relkind = 'S' then 'sequence'
         when c.relkind = 't' then 'toast'
         when c.relkind = 'v' then 'view'
         when c.relkind = 'm' then 'materialized view'
         when c.relkind = 'c' then 'composite type'
         when c.relkind = 'f' then 'foreign table'
         when c.relkind = 'p' then 'partitioned table'
         when c.relkind = 'I' then 'partitioned index'
         else 'other'
       end object_type,
       c.relname, w.start_lsn, w.end_lsn, w.prev_lsn, w.relblocknumber,
       w.xid, w.resource_manager, w.record_type, 
       w.record_length, w.main_data_length,
       w.block_data_length, w.block_fpi_length, w.block_fpi_info,
       w.description, w.block_data, w.block_fpi_data
from pg_get_wal_block_info('275/20000000','275/200018F0', true) w
     join pg_class c on (c.relfilenode = w.relfilenode)
     join pg_namespace n on (n.oid = c.relnamespace)
     join pg_type t on (t.oid = c.reltype);