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