> On Nov 18, 2026, at 01:17, Bharath Rupireddy 
> <[email protected]> wrote:
> 
> 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.

Thanks a lot for your feedback.

> 
>> 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.

I'm afraid that's not easy. As I implemented in the patch, I even chose to use 
a binary search to find the start and end LSN boundaries for a time range. I 
don't think this can be done easily with just a few SQL statements, especially 
for ordinary DBAs. I've heard from many DBAs who have requested such a 
function. Surya also agreed that this is a real use case.

> 
>> 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?

We cannot imagine all users' operational models. I started talking to DBAs last 
year. My main takeaway is that real-world use cases are usually far more 
complicated than what I can imagine as a hacker writing PostgreSQL code. I 
didn't come up with this feature myself; some DBAs requested it.

> 
>> 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 guess users would be more likely to set wal_keep_size to a large value in 
order to retain more WAL files.

Yes, users can write scripts to do whatever kind of analysis. But here, users 
want a simple, native PG method. This patch is trying to address a user 
requirement and make DBAs' lives a bit easier.

Also, this patch doesn't touch the core, it just adds two new functions to the 
pg_walinspect extension.

> 
> 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.
> 

I don't feel this patch adds significant complexity to pg_walinspect. This 
feature doesn't change the existing logic of pg_walinspect, it only adds two 
new functions. And as explained above, what the new functions do is not as easy 
to achieve with just a bit of SQL. Whenever possible, we should try to improve 
the user experience.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/






Reply via email to