Re: pg_walinspect: add functions to locate and list WAL by time and LSN

From: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
To: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: pg_walinspect: add functions to locate and list WAL by time and LSN
Date: 2026-11-18 03:16:31
Message-ID: A2AE59B1-5FF6-44CC-A990-683E49011F75@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> On Nov 18, 2026, at 01:17, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Thu, Sep 17, 2026 at 9:06 PM Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> 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/

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message shveta malik 2026-11-18 03:38:47 Re: Initial table sync copies the whole table when it is no longer published
Previous Message Dewei Dai 2026-11-18 02:55:55 [PATCH] Reject duplicate SQL/JSON PASSING argument names