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:
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:
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:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
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:
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:
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:
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.