Hi,

On Thu, Sep 17, 2026 at 9:06 PM Chao Li <[email protected]> wrote:
>
> I'd like to propose adding two new SQL functions to pg_walinspect.

Thanks, and sorry for the delay.

> During HOW2026 in Jinan this April, a DBA talked to me that PG lacks an easy 
> way to locate WAL files by time.

I think using the existing pg_walinspect functions, one can look up
the times using the transaction commit/abort time from the description
column, starting from the last checkpoint's redo LSN till the current
flush LSN. Isn't this helpful? It takes a bit of time to do so, but
that should be fine.

> He described a scenario where somebody had done something wrong in a 
> production database, for example accidentally deleting important data. The 
> administrator knew that the incident had happened at around 1am the last 
> night and wanted to confirm what had happened by analyzing WAL.

Isn't one expected to set up *some* kind of auditing or delayed
streaming standby or such in production if worried about such
accidental deletes/drops?

> The problem was that, with thousands of WAL files in pg_wal, where should he 
> start?

WAL files are archived and renamed/reused/removed, so all the
thousands of files, one of which could be the interesting file
containing the accidental WAL records, are not guaranteed to be
available in the pg_wal dir anyway. 1000 WAL files come to around
16GB, and I'm not sure if anyone really prefers to delay checkpoints
beyond that point (max_wal_size is also usually set up to trigger the
checkpoint). If needed, one can use pg_waldump and some scripts to do
the analysis independently of the database instance.

I'm not against enhancing pg_walinspect for more use cases. However,
I'm a bit worried about complicating it for this. Unless I'm missing
something here, the reported issue could have been solved with a bit
of SQL over the existing functions and/or with some scripting with
pg_waldump over pg_wal dir or the archived WAL.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com


Reply via email to