We use postgresql as a database. For the backup part, we store full cluster backups and wal files to allow point in time restoration of our database.
Our wal files take up quite a bit of space when compared to our full backups, so we would like to inspect the content of our wal files, and more precisely be able to see what tables contribute the most to the volume of our wal files.
Question
Is there a way to inspect what tables/relations are targeted by the actions stored in a specific wal file ? and how many rows or records are affected ?
pg_waldump is the tool you'd want to use to inspect the contents of a WAL file. Basically, you can look to see which DML and DDL statements (along with other events) were captured into the WAL file.
One example is below:
-bash-4.2$ pgbench -i
dropping old tables...
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.27 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.77 s (drop tables 0.03 s, create tables 0.04 s, client-side generate 0.34 s, vacuum 0.21 s, primary keys 0.15 s).
-bash-4.2$ psql -c "select * from pg_class where relname= 'pgbench_accounts'"
oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relch
ecks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-------+------------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+------
-----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------------+--------------
16434 | pgbench_accounts | 2200 | 16436 | 0 | 10 | 2 | 16440 | 0 | 1640 | 100000 | 1640 | 0 | t | f | p | r | 4 |
0 | f | f | f | f | f | t | d | f | 0 | 514 | 1 | | {fillfactor=100} |
(1 row)
-bash-4.2$ pg_waldump 00000001000000000000000* | grep 16440
rmgr: Storage len (rec/tot): 42/ 42, tx: 514, lsn: 0/02EC03B0, prev 0/02EC0380, desc: CREATE base/13255/16440
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02EC0CC0, prev 0/02EC0C78, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 0
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02EC2650, prev 0/02EC0CC0, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 1
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02EC3FC8, prev 0/02EC2650, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 2
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02EC5958, prev 0/02EC3FC8, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 3
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02EC72E8, prev 0/02EC5958, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 4
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02EC8C78, prev 0/02EC72E8, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 5
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02ECA608, prev 0/02EC8C78, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 6
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02ECBF80, prev 0/02ECA608, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 7
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02ECD910, prev 0/02ECBF80, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 8
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02ECF2A0, prev 0/02ECD910, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 9
rmgr: Heap2 len (rec/tot): 6515/ 6515, tx: 514, lsn: 0/02ED0C30, prev 0/02ECF2A0, desc: MULTI_INSERT+INIT 61 tuples flags 0x00, blkref #0: rel 1663/13255/16440 blk 10
<snip>
Bear in mind that in WAL, tables are referred to by their relfilenode and not by their oid (look for 16440 in the example)
More information about pg_waldump can be found in the documentation