CODEWITHSUNDEEP
postgresqlchange-data-capturewal
skunkwerk
skunkwerk

Reputation: 3070

querying the database's write-ahead log

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

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?

Upvotes: 3

Views: 1390

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246348

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.

Upvotes: 4

Related Questions