Re: PoC: Simplify recovery after dropping a table by LOGGING the restore LSN - Mailing list pgsql-hackers

On Fri, 8 Nov 2024 at 08:47, Kirk Wolak <wolakk@gmail.com> wrote:
>
> Hackers,
>   The concept was driven by an all too common support request.  A user accidentally dropped the wrong table.  (this could also be applied to dropping a database, etc).
>
>   If we had the LSN before the drop, this would be easier.  So we actually log the LSN when the lock is required so that we have an accurate LSN and the recovery is much simpler.
>
>   All we are doing is inserting a simple LOG message:
> Acquired drop table lock on table <relname>. Restore at <LSN>

This is indeed useful for the number of accidental data loss recovery.

>
>   Comments are appreciated!
>
>   Should we ALSO consider this for:
>      - DROP DATABASE
>      - TRUNCATE TABLE
>      - DELETE (only when it is without a WHERE clause?)
>      - UPDATE (only when it is without a WHERE clause?)

options 1 & 2 looks sane, but logging lsn for DELETE/UPDATE looks extra.

I am not convinced this change is necessary to be done inside
PostgreSQL. What stops us from logging all the same inside object
access hook defined by extension? This way we can define any rule on
when to log this.

There are a number of cases to consider, pointed out by Jim, such as
the TEMP table and the UNLOGGED table. [0]

I want to highlight that we are logging the current WAL insert
pointer, which can be arbitrarily less than the actual LSN of the
commit record that deletes the table in case of high load. We first
acquire a deletion lock, then we assemble the xlog record and only
then we insert this record into the WAL file.  So, the log message
should be something like 'Restore at lsn xxx or later'.

[0] https://www.postgresql.org/message-id/3bda6b56-16bd-48fe-8e23-5ef58a6a4e34%40uni-muenster.de
--
Best regards,
Kirill Reshke