In the last posts about the new incremental backup feature we’ve looked at the following topics:
- How does it work in general? PostgreSQL 17: Support for incremental backups
- Backup chains, what is it? PostgreSQL 17: Incremental backups and backup chains
- Additional system information functions and how you can use them – PostgreSQL 17: Incremental backups, system information functions
In this post we’ll look into the final topic for this feature: In addition to what was described in the previous posts, there is also a new utility which made it into PostgreSQL: pg_walsummary. As the name implies this can be used to get information about the WAL summary files from an operating system point of view.
This time we’ll not start from a fresh, new cluster but we start with a new table containing some data:
1 2 3 4 5 6 7 8 9 10 11 | postgres=# create table x ( a int , b text ); CREATE TABLE postgres=# insert into x select i, i::text from generate_series(1,10000) i; INSERT 0 10000 postgres=# checkpoint ; CHECKPOINT postgres=# select pg_relation_filepath( 'x' ); pg_relation_filepath ---------------------- base/5/16399 (1 row) |
As we know already from the previous post, all the changed blocks should be listed in the last WAL summary file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | postgres@debian12-pg: /home/postgres/ [pgdev] cd $PGDATA /pg_wal/summaries/ postgres@debian12-pg: /u02/pgdata/PGDEV/pg_wal/summaries/ [pgdev] ls -latr total 92 -rw------- 1 postgres postgres 4808 Jan 18 10:51 00000001000000000100002800000000010B3098.summary -rw------- 1 postgres postgres 4944 Jan 18 10:51 0000000100000000010B309800000000014E8838.summary -rw------- 1 postgres postgres 32 Jan 18 10:51 0000000100000000014E883800000000014E8938.summary -rw------- 1 postgres postgres 230 Jan 18 10:51 0000000100000000014E893800000000014EF360.summary -rw------- 1 postgres postgres 32 Jan 18 10:51 0000000100000000014EF36000000000014EF460.summary -rw------- 1 postgres postgres 88 Jan 18 10:51 0000000100000000014EF46000000000014EF798.summary -rw------- 1 postgres postgres 32 Jan 18 10:51 0000000100000000014EF79800000000014EF810.summary -rw------- 1 postgres postgres 682 Jan 18 11:11 0000000100000000014EF8100000000001557C00.summary -rw------- 1 postgres postgres 88 Jan 18 11:14 000000010000000001557C0000000000015598C8.summary -rw------- 1 postgres postgres 8250 Jan 18 11:23 0000000100000000015598C80000000005290968.summary -rw------- 1 postgres postgres 8490 Jan 18 11:29 00000001000000000529096800000000077DC3C0.summary -rw------- 1 postgres postgres 116 Jan 18 11:49 0000000100000000077DC3C000000000077DE570.summary drwx------ 4 postgres postgres 4096 Jan 18 11:49 .. -rw------- 1 postgres postgres 434 Jan 18 14:29 0000000100000000077DE57000000000077F4060.summary -rw------- 1 postgres postgres 584 Jan 18 14:49 0000000100000000077F40600000000007811618.summary -rw------- 1 postgres postgres 756 Jan 18 15:35 00000001000000000781161800000000078D3198.summary drwx------ 2 postgres postgres 4096 Jan 18 15:35 . |
If we give the last summary file to pg_walsummary and grep for our table, we’ll get this:
1 2 3 4 | postgres@debian12-pg: /u02/pgdata/PGDEV/pg_wal/summaries/ [pgdev] pg_walsummary 00000001000000000781161800000000078D3198.summary | grep 16399 TS 1663, DB 5, REL 16399, FORK main: limit 0 TS 1663, DB 5, REL 16399, FORK main: blocks 0..53 TS 1663, DB 5, REL 16399, FORK vm: block 0 |
The number of changed blocks reported here (0..53) is again consistent with what we can see on disk:
1 2 3 4 | postgres@debian12-pg: /u02/pgdata/PGDEV/pg_wal/summaries/ [pgdev] ls -la ../.. /base/5/16399 -rw------- 1 postgres postgres 442368 Jan 18 15:35 ../.. /base/5/16399 postgres@debian12-pg: /u02/pgdata/PGDEV/pg_wal/summaries/ [pgdev] echo "442368/8192" | bc 54 |
“limit 0” means, that the relation was either created or truncated in this range of WAL and “vm” means the visibility map.
By default pg_walsummary gives you a line per range of blocks, but you may list the individual blocks as well:
1 2 3 4 5 6 7 8 9 10 | postgres@debian12-pg: /u02/pgdata/PGDEV/pg_wal/summaries/ [pgdev] pg_walsummary --individual 00000001000000000781161800000000078D3198.summary | grep 16399 TS 1663, DB 5, REL 16399, FORK main: limit 0 TS 1663, DB 5, REL 16399, FORK main: block 0 TS 1663, DB 5, REL 16399, FORK main: block 1 TS 1663, DB 5, REL 16399, FORK main: block 2 TS 1663, DB 5, REL 16399, FORK main: block 3 ... TS 1663, DB 5, REL 16399, FORK main: block 52 TS 1663, DB 5, REL 16399, FORK main: block 53 TS 1663, DB 5, REL 16399, FORK vm: block 0 |
Another option you have is to give all the summary files to pg_walsummary:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | postgres@debian12-pg: /u02/pgdata/PGDEV/pg_wal/summaries/ [pgdev] pg_walsummary * TS 1663, DB 1, REL 112, FORK main: block 0 TS 1663, DB 1, REL 113, FORK main: block 0 TS 1663, DB 1, REL 174, FORK main: block 0 TS 1663, DB 1, REL 175, FORK main: block 0 TS 1663, DB 1, REL 548, FORK main: block 0 TS 1663, DB 1, REL 549, FORK main: block 0 TS 1663, DB 1, REL 827, FORK main: block 0 TS 1663, DB 1, REL 828, FORK main: block 0 TS 1663, DB 1, REL 2187, FORK main: block 0 TS 1663, DB 1, REL 2228, FORK main: blocks 0..1 TS 1663, DB 1, REL 2337, FORK main: block 0 ... TS 1663, DB 5, REL 16402, FORK main: limit 0 TS 1663, DB 5, REL 16403, FORK main: limit 0 TS 1663, DB 5, REL 16403, FORK main: block 0 |
Depending on what you’re interested in, you can now easily grep over the output.
I don’t think pg_walsummary is something a lot of people will use on a day by day basis, but having it available for troubleshooting and testing is great.