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

From: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
To: Chao Li <li(dot)evan(dot)chao(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-17 17:17:40
Message-ID: CALj2ACXkPFin9RxwYsw4HmEnW4HGGH6DAP6gb_LCiOZ=bfdueQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

> 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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2026-11-17 17:22:51 Re: [PATCH]Fix planner's strictness check for JsonExpr
Previous Message Sami Imseih 2026-11-17 17:16:50 Re: Improve pg_stat_statements scalability