querying the database's write-ahead log

Question

I'd like to be able to track changes made to a database (PostgreSQL) over the past month. I know of two options:

• database triggers
• change data capture of the write-ahead log

But is there no way to query the write-ahead log directly from the database itself? I.e. something like

SELECT *
FROM WRITE_AHEAD_LOG
WHERE table='mytable' AND event_timestamp>'2018-01-01' etc?

Answer 1

The write ahead log (WAL) does not contain information about the queries that were run on the server, nor does it have information about the names of the tables that are affected.

It contains information on the physical level, like “write these 24 bytes into block 746 of file 3412 at offset 42”.

One thing you could investigate is logical decoding, which uses information from the WAL and the current state of the catalog tables to generate information like you want.

Have a look at the test_decoding module for that.

It won't show the queries that actually ran against PostgreSQL, but it will show the modifications to the tables.