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