PoC: Simplify recovery after dropping a table by LOGGING the restore LSN

Hi Andrey, Nikolay, and Kirk

On 08.11.24 04:46, Kirk Wolak 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.

I've tested the patch it it works as described.

postgres=# CREATE TABLE t();
postgres=# SELECT pg_current_wal_lsn( );
 pg_current_wal_lsn
--------------------
 0/2A0677D8
(1 row)
postgres=# DROP TABLE t;

2024-11-26 14:12:28.915 CET [338209] LOG:  Aquired drop table lock on
table t. Restore at 0/2A0677D8
2024-11-26 14:12:28.915 CET [338209] STATEMENT:  DROP TABLE t;

A few comments:

1) non "permanent" tables

Since TEMPORARY TABLES are automatically dropped in the end of a
session, simply leaving a session that contains a TEMPORARY TABLE also
logs the LSN:

postgres=# CREATE TEMPORARY TABLE t_temporary AS SELECT
generate_series(1,100);
SELECT 100
postgres=# quit

2024-11-26 14:34:26.596 CET [386777] LOG:  Aquired drop table lock on
table t_temporary. Restore at 0/2A149EC0

Dropping the TEMPORARY TABLE also triggers this log message

postgres=# CREATE TEMPORARY TABLE t_temporary AS SELECT
generate_series(1,100);
postgres=# DROP TABLE t_temporary;

2024-11-26 14:35:13.174 CET [387378] LOG:  Aquired drop table lock on
table t_temporary. Restore at 0/2A1664E8
2024-11-26 14:35:13.174 CET [387378] STATEMENT:  DROP TABLE t_temporary;

The same applies for UNLOGGED TABLES:

postgres=# CREATE UNLOGGED TABLE t_unlogged AS SELECT
generate_series(1,100);
postgres=# SELECT * FROM page_header(get_raw_page('public.t_unlogged',0));
 lsn | checksum | flags | lower | upper | special | pagesize | version |
prune_xid
-----+----------+-------+-------+-------+---------+----------+---------+-----------
 0/0 |        0 |     0 |   424 |  4992 |    8192 |     8192 |       4
|         0
(1 row)
postgres=# DROP TABLE t_unlogged;

2024-11-26 14:33:00.397 CET [338209] LOG:  Aquired drop table lock on
table t_unlogged. Restore at 0/2A143DC8
2024-11-26 14:33:00.397 CET [338209] STATEMENT:  DROP TABLE t_unlogged;

2) log message readability

For better readability, perhaps the table name should be logged with
double quotes, as in :

2024-11-26 13:25:35.525 CET [338209] ERROR:  table "x" does not exist
2024-11-26 13:25:35.525 CET [338209] STATEMENT:  DROP TABLE x;

3) format of existing log messages containing LSN

I haven't looked too deep into it, but the log format used by checkpoint
might be helpful for applications that need to parse log files. For
instance, a log entry triggered after a DROP DATABASE:

postgres=# CREATE DATABASE db;
postgres=# DROP DATABASE db;

2024-11-26 13:30:08.166 CET [329713] LOG:  checkpoint starting:
immediate force wait
2024-11-26 13:30:08.177 CET [329713] LOG:  checkpoint complete: wrote 3
buffers (0.0%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 1
recycled; write=0.002 s, sync=0.002 s, total=0.011 s; sync files=4,
longest=0.001 s, average=0.001 s; distance=4306 kB, estimate=4308 kB;
lsn=0/318AFD8, redo lsn=0/318AF80

  This is a rough patch, very simple and effective.  We are looking
for feedback.

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

+1

Thanks!

Best, Jim

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]/messages/by-id/3bda6b56-16bd-48fe-8e23-5ef58a6a4e34@uni-muenster.de -- Best regards, Kirill Reshke

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]: /messages/by-id/3bda6b56-16bd-48fe-8e23-5ef58a6a4e34@uni-muenster.de -- Best regards, Kirill Reshke
--
Best regards,
Kirill Reshke

Subject: Re: PoC: Simplify recovery after dropping a table by LOGGING the
restore LSN

Hi all,

I've implemented an improved version of this feature that addresses
Kirill's concern about logging the wrong LSN [1]/messages/by-id/CALdSSPiRDvbuPgZKAB1+BDb3Nfe-i3PYkiy=ScMZ7tU5f0toKQ@mail.gmail.com -- Best regards, Dmitry Lebedev.

The key difference: instead of logging WAL insert pointer at lock time,
this patch logs the actual commit LSN by extending XactCallback to pass
the commit LSN from the commit record.

Main improvements:
- Logs real commit LSN, not an earlier position
- Handles ROLLBACK correctly (no log for aborted drops)
- Supports SAVEPOINT, ROLLBACK TO, COMMIT AND CHAIN
- New GUC: log_object_drops (default: off)
- Includes DROP DATABASE support
- Comprehensive TAP tests

This makes the LSN reliable for PITR recovery regardless of system load.

Patch attached. Feedback welcome!

[1]: /messages/by-id/CALdSSPiRDvbuPgZKAB1+BDb3Nfe-i3PYkiy=ScMZ7tU5f0toKQ@mail.gmail.com -- Best regards, Dmitry Lebedev
/messages/by-id/CALdSSPiRDvbuPgZKAB1+BDb3Nfe-i3PYkiy=ScMZ7tU5f0toKQ@mail.gmail.com
--
Best regards,
Dmitry Lebedev

пт, 28 нояб. 2025 г. в 16:39, Kirill Reshke <reshkekirill@gmail.com>:

On 1 Dec 2025, at 12:11, Дмитрий Лебедев <idemonlebedev@gmail.com> wrote:

I've implemented an improved version of this feature that addresses
Kirill's concern about logging the wrong LSN [1].

Cool! On a first glance patch looks good. I'll test it more later, for now I've fixed indentation and rebased on master to fix conflicts with recent "wait for LSN feature".

Please also avoid top posting :) Post answers below cited text. And do not post unrelevant citations at all. Mail archives a read more frequently than written.

Also, it's a good idea to number patch versions so reviewers can post feedback for some specific version. Here I used version number 5.

Thanks!

Best regards, Andrey Borodin.

Hello hackers,

Attached is v6 of the patch to log the target LSN on DROP TABLE,
TRUNCATE TABLE, and DROP DATABASE when the GUC log_object_drops is
enabled (default off). This is based on Dmitry Lebedev's earlier work,
which I have revised and extended.

When a permanent table is dropped or truncated and the transaction
commits, the server logs a message containing the commit LSN. The
patch adds a new variable, XactLastCommitStart, which records
ProcLastRecPtr (the start of the commit WAL record) immediately after
it is written in RecordTransactionCommit(). The logging is handled by
a transaction callback, ensuring it only runs when the transaction
actually commits. Rolled-back operations, including ROLLBACK TO
SAVEPOINT, are discarded and never logged.

Every permanent table that is physically dropped is logged, including
tables dropped by DROP SCHEMA ... CASCADE and child partitions, so
dropping a schema or a partitioned table produces one line per table.
TRUNCATE is handled the same way. DROP DATABASE logs an intermediate
LSN (the WAL insert position at the
time of the drop) instead of the commit LSN.

This LSN provides an exact recovery target. Setting
recovery_target_lsn to this value with recovery_target_inclusive =
false stops recovery just before the commit record, preserving the
table. For example:
LOG: table "public.t" (OID 16384) dropped, lsn=0/01776698
HINT: To recover dropped or truncated tables, use
recovery_target_lsn = '0/01776698' with recovery_target_inclusive =
false.

On Mon, Sep 28, 2026 at 11:37 AM Kirill Reshke <reshkekirill@gmail.com> wrote:

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.

The object access hook runs when the DROP executes, before the
transaction commits, so the commit LSN doesn't exist yet at that
point. To log it, an extension would also need a transaction callback
that runs at commit and would have to discard entries from rolled-back
savepoints. That is what this patch does, with a new
XactLastCommitStart variable for the LSN.

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

Both are filtered out. We check relation persistence and only register
permanent relations (RELPERSISTENCE_PERMANENT). Neither TEMP nor
UNLOGGED tables produce log entries.

Best,
Salma Elsayed

Hi Salma

On 28/09/2026 15:54, Salma El-Sayed wrote:

Attached is v6 of the patch to log the target LSN on DROP TABLE,
TRUNCATE TABLE, and DROP DATABASE when the GUC log_object_drops is
enabled (default off). This is based on Dmitry Lebedev's earlier work,
which I have revised and extended.

Thanks for the patch!

v6 no longer applies to master, since two new tests were added. 0001 is
v6 rebased, with the test file renamed to
060_drop_table_logging.pl and meson.build updated accordingly -- no
other changes.

== drop database ==

IIUC simply adding a lsn recorded after a drop database expectedly
restores an invalid database, which I can confirm:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5433" failed:
FATAL: cannot connect to invalid database "db"
HINT: Use DROP DATABASE to drop invalid databases.

But I was wondering if capturing the lsn right after
pgstat_drop_database(db_id) woul be a better option here.I tested that
locally, and recovering to that lsn gives back a valid database, with
its original CONNECTION LIMIT, settings and data, and no manual step
needed -- That's in 0002. It still needs more testing..

WDYT?

Thanks for working on this.

Best, Jim

Hi Jim,

On Sun, Oct 11, 2026 at 1:27 AM Jim Jones <jim.jones@uni-muenster.de> wrote:

v6 no longer applies to master, since two new tests were added. 0001 is
v6 rebased, with the test file renamed to
060_drop_table_logging.pl and meson.build updated accordingly -- no
other changes.

Thanks for your response and for fixing the rebase conflict.

== drop database ==

But I was wondering if capturing the lsn right after
pgstat_drop_database(db_id) woul be a better option here.I tested that
locally, and recovering to that lsn gives back a valid database, with
its original CONNECTION LIMIT, settings and data, and no manual step
needed -- That's in 0002. It still needs more testing..

I agree that avoiding the manual step is much better. I tested your
patch and it works as expected. I included your fix in the main patch
and updated the documentation accordingly.
I also had an LLM help write more tests (for drop db recovery and truncate).

Attached is v8 rebased on master.

Best regards,
Salma Elsayed

Hi Salma

On 12/10/2026 14:14, Salma El-Sayed wrote:

Attached is v8 rebased on master.

Thanks for the update.

I played a bit with v8 and it seems that the lsn is logged a bit too
early. If a drop database command fails, it still gets logged:

psql (20devel)
Type "help" for help.

postgres=# create table t (g int, pad text);
CREATE TABLE
postgres=# insert into t select g, repeat('x', 100) from
generate_series(1, 600000) g;
INSERT 0 600000
postgres=# create database db;
CREATE DATABASE
postgres=# update t set g = g + 1;
UPDATE 600000
postgres=# set log_object_drops to on;
SET
postgres=# set statement_timeout = '20ms';
SET
postgres=# drop database db;
ERROR: canceling statement due to statement timeout

postgres=# select oid,datname from pg_database where datname = 'db';
oid | datname
-------+---------
16414 | db
(1 row)

.. but the logs show:

LOG: database "db" (OID 16414) dropped, lsn=0/47AAC7E0

Just for testing, I placed the ereport after remove_dbtablespaces() and
the lsn is no longer logged in the scenario above -- see 0002, but it
needs more testing. WDYT?

I will take a closer look at the tests in the next few days.

Best, Jim

Hi Jim,

Thanks for your response.

postgres=# create table t (g int, pad text);
CREATE TABLE
postgres=# insert into t select g, repeat('x', 100) from
generate_series(1, 600000) g;
INSERT 0 600000
postgres=# create database db;
CREATE DATABASE
postgres=# update t set g = g + 1;
UPDATE 600000
postgres=# set log_object_drops to on;
SET
postgres=# set statement_timeout = '20ms';
SET
postgres=# drop database db;
ERROR: canceling statement due to statement timeout

postgres=# select oid,datname from pg_database where datname = 'db';
oid | datname
-------+---------
16414 | db
(1 row)

.. but the logs show:

LOG: database "db" (OID 16414) dropped, lsn=0/47AAC7E0

But in this exact scenario, didn't the database become invalid?
postgres=# \c db
connection to server on socket "/tmp/.s.PGSQL.5435" failed: FATAL:
cannot connect to invalid database "db"
HINT: Use DROP DATABASE to drop invalid databases.

The LSN value itself is fine to recover to. The problem is the word
"dropped," since the drop did not actually complete.

I tested with injection points. If an error occurs after v9's logging
point, we get the same situation again: the log assumes the database
was dropped when it was not. I moved the LSN registration earlier in
dropdb(), so that a failure after that point still has something to
log.

I am now using a callback, similar to how we handle DROP TABLE. On
commit, we log the LSN as before. On abort, we still log a message,
but state the drop did not complete instead of saying it dropped. If
"drop database db;" is retried on an already invalid database, we
still log a message, but without a recovery_target_lsn, and we state
that the original recovery point is not known to this session.

Attached is v10 with 0002 for the callback implementation. Let me know
what you think.
It still needs more testing.

Best regards,
Salma Elsayed

Hello

 		drop_lsn = GetXLogInsertRecPtr();
-
+		RegisterDropDatabaseLog(db_id, dbname, drop_lsn, already_invalid);
+	}

...

+	if (!dropdb_callback_registered)
+	{
+		RegisterXactCallback(DropDbXactCallback, NULL);

If a backend crashes between them the callback doesn't run and we are left with an invalid database without logging it.

You can reproduce this by stopping the drop at the 'dropdb-after-invalid-marker' injection point and killing the backend.

On 15/10/2026 16:26, Salma El-Sayed wrote:

But in this exact scenario, didn't the database become invalid?
postgres=# \c db
connection to server on socket "/tmp/.s.PGSQL.5435" failed: FATAL:
cannot connect to invalid database "db"
HINT: Use DROP DATABASE to drop invalid databases.

The LSN value itself is fine to recover to. The problem is the word
"dropped," since the drop did not actually complete.

Right, this is how I see it too. Stating that the database was dropped
while it is still there (valid or not) isn't ideal.

On top of what Zsolt pointed out:

== new warning ==

postgres=# drop database db;
ERROR: canceling statement due to statement timeout
WARNING: DROP DATABASE "db" (OID 16393) did not complete; the database
may be left invalid and require DROP DATABASE to be run again
HINT: Files belonging to this database may already have been removed.
To recover data from before this attempt, use recovery_target_lsn =
'0/10276790' with recovery_target_inclusive = false.

This warning is now sent to the client, and I have mixed feelings about
it. If a cancelled drop database should tell the user that the database
was left invalid, that's useful whether or not this GUC is set, and it
belongs in a separate patch, not behind log_object_drops. While I
understand the motivation behind it, I'd argue that it should be logged
only in the server -- the user most likely cannot act on it anyway :)
But it is just my opinion.

Also, the primary message is quite long (two clauses). I recommend
taking a look at the error style guide[1]

Thanks for working on this!

Best, Jim

1 - https://www.postgresql.org/docs/18/error-style-guide.html

Hi Jim and Zsolt,

On Fri, Oct 16, 2026 at 12:25 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:

If a backend crashes between them the callback doesn't run and we are
left with an invalid database without logging it.

You can reproduce this by stopping the drop at the
'dropdb-after-invalid-marker' injection point and killing the backend.

Thanks, I was able to reproduce it. I don't think the callback can
cover this case at all, since nothing runs in the backend after it
dies.

The only option I could think of was logging the lsn with a neutral
message right after we capture it, before the database is marked
invalid. I tried it and I don't think it's a good fit. At that point
we don't know if the drop will succeed, so the message can only say
the drop is starting. We would still need the callback afterwards to
log the real outcome, so every DROP DATABASE would log two messages,
and after a crash the user would only have the neutral one and would
need to check pg_database to find out what actually happened.

What makes DROP DATABASE harder than DROP TABLE is that it marks the
database invalid before doing work it can't roll back, so a failed or
crashed drop can leave the database broken.

So I'd like to remove DROP DATABASE from this patch and keep it to
DROP TABLE and TRUNCATE TABLE, which was the original scope. DROP
DATABASE could be handled separately in another patch. What do you
think?

On Fri, Oct 16, 2026 at 3:18 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

This warning is now sent to the client, and I have mixed feelings about
it. If a cancelled drop database should tell the user that the database
was left invalid, that's useful whether or not this GUC is set, and it
belongs in a separate patch, not behind log_object_drops.

I agree. Warning that a cancelled DROP DATABASE left the database
invalid is a separate issue from log_object_drops and should be its
own patch.

Also, the primary message is quite long (two clauses). I recommend
taking a look at the error style guide[1]

Thanks. That message goes away with DROP DATABASE, but I'll go through
the style guide and apply it to the DROP TABLE and TRUNCATE messages
in the next version.

Best regards,
Salma Elsayed

On Wed, Oct 21, 2026 at 7:18 AM Salma Elsayed <salmasayed182003@gmail.com>
wrote:

Hi Jim and Zsolt,

On Fri, Oct 16, 2026 at 12:25 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:

If a backend crashes between them the callback doesn't run and we are
left with an invalid database without logging it.

...
So I'd like to remove DROP DATABASE from this patch and keep it to
DROP TABLE and TRUNCATE TABLE, which was the original scope. DROP
DATABASE could be handled separately in another patch. What do you
think?

+1

Having worked on the original patch, our goal was protecting the table
data.
The attempt to add DROP DATABASE "seemed" like it could come along for the
ride,
but it is clearly a different beast, for the reasons you describe.

Kirk Out!

On 10/21/26 2:49 PM, Kirk Wolak wrote:

  Having worked on the original patch, our goal was protecting the
table data.
The attempt to add DROP DATABASE "seemed" like it could come along for
the ride,
but it is clearly a different beast, for the reasons you describe.

+1

I agree that leaving drop database for another patch is a good idea.
Having it would be quite nice, bit it's indeed much harder than we
anticipated, so I guess it's ok to postpone it.

Best, Jim