When PostgreSQL 18 introduced the ability to verify tar based (and compressed) backups with pg_verifybackup there was one limitation: The verification of the WAL files in the tars (or compressed files) had to be skipped (--no-parse-wal) because pg_waldump in that version of PostgreSQL is not able to cope with that (and pg_waldump is used by pg_verifybackup). This will change with PostgreSQL 19 because of this commit: “pg_waldump: Add support for reading WAL from tar archives”.

This is maybe not a feature a lot of people have waited for but it makes two tasks a lot easier:

  • As mentioned above: pg_verifybackup can now read from WAL in tar and compressed files and therefore can do WAL verification
  • When you have WAL in a tar or compressed file and you know what you’re looking for you do not need to manually extract those archives before using pg_waldump

To see that in action once can create a tar or compressed backup with pb_basebackup:

postgres@:/home/postgres/ [pgdev] mkdir /var/tmp/dummy
postgres@:/home/postgres/ [pgdev] pg_basebackup --checkpoint=fast --format=t --pgdata=/var/tmp/dummy
postgres@:/home/postgres/ [pgdev] ls -la /var/tmp/dummy
total 128476
drwxr-xr-x. 1 postgres postgres        66 May 11 06:36 .
drwxrwxrwt. 1 root     root           762 May 11 06:33 ..
-rw-------. 1 postgres postgres    149515 May 11 06:36 backup_manifest
-rw-------. 1 postgres postgres 114619904 May 11 06:36 base.tar
-rw-------. 1 postgres postgres  16778752 May 11 06:36 pg_wal.tar

Looking at the PostgreSQL log file while the backup is running gives us a LSN we can give to pg_waldump:

2026-05-11 06:36:18.397 CEST - 2 - 1731 -  - @ - 0LOG:  checkpoint complete: fast force wait: wrote 2 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 1 removed, 0 recycled; write=0.002 s, sync=0.005 s, total=0.019 s; sync files=4, longest=0.003 s, average=0.002 s; distance=16384 kB, estimate=16384 kB; lsn=0/0D000088, redo lsn=0/0D000028

postgres@:/home/postgres/ [pgdev] pg_waldump --path=/var/tmp/dummy/pg_wal.tar -s "0/0D000088" 
rmgr: XLOG        len (rec/tot):    122/   122, tx:          0, lsn: 0/0D000088, prev 0/0D000050, desc: CHECKPOINT_ONLINE redo 0/0D000028; tli 1; prev tli 1; fpw true; wal_level replica; logical decoding false; xid 0:729; oid 16420; multi 1; offset 1; oldest xid 684 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 729; checksums on; online
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/0D000108, prev 0/0D000088, desc: RUNNING_XACTS nextXid 729 latestCompletedXid 728 oldestRunningXid 729; dbid: 0
rmgr: XLOG        len (rec/tot):     34/    34, tx:          0, lsn: 0/0D000140, prev 0/0D000108, desc: BACKUP_END 0/0D000028
rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/0D000168, prev 0/0D000140, desc: SWITCH 
pg_waldump: error: could not find WAL "00000001000000000000000E" in archive "pg_wal.tar

This helps pg_verifybackup fully verify a backup (in previous versions you had to use “–no-parse-wal”):

postgres@:/home/postgres/ [pgdev] pg_verifybackup --progress /var/tmp/dummy/
111933/111933 kB (100%) verified
backup successfully verified

As usual, thanks to all involved.

Post Views: 18
Thumbnail [60x60]
by
Daniel Westermann