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
