[PATCH] Add recovery boundary WAL record for database and tablespace commands
CREATE/DROP DATABASE, ALTER DATABASE SET TABLESPACE
and CREATE/DROP TABLESPACE change directories as their WAL is replayed,
while recovery_target_time is only checked on commit and abort records.
So a target set before such a command does not stop recovery before it,
leaving a dropped database gone or an orphan directory.
This was reported in [1]/messages/by-id/aac5c3e6-2805-ba06-143b-070478aa9434@loxodata.com and again in [2]/messages/by-id/43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org.
0001 emits an XLOG2 record carrying a timestamp
before the first irreversible record of each command
and stops recovery before it when the timestamp is past the target.
Stopping there loses nothing, as replaying the record has no effect.
0002 makes recovery_min_apply_delay wait on the record too.
A delayed standby currently removes a dropped database's directory
as soon as it reads the drop, long before the delay expires.
[1]: /messages/by-id/aac5c3e6-2805-ba06-143b-070478aa9434@loxodata.com
/messages/by-id/aac5c3e6-2805-ba06-143b-070478aa9434@loxodata.com
[2]: /messages/by-id/43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org
/messages/by-id/43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org
--
JH Shin
Attachments:
t253696_10002-Make-recovery_min_apply_delay-wait-on-recovery-bound.patchapplication/octet-stream; name=0002-Make-recovery_min_apply_delay-wait-on-recovery-bound.patchDownload+35-14
0001-Add-recovery-boundary-WAL-record-for-database-and-ta.patchapplication/octet-stream; name=0001-Add-recovery-boundary-WAL-record-for-database-and-ta.patchDownload+198-4
Hi JoongHyuk,
Thanks for picking this up. Both reports have been open for years and
took me some time to understand why we need that.
One question. The 2019 report also mentions recovery_target_xid set
to the xid of DROP DATABASE, with recovery_target_inclusive = off.
With the patch I still get
FATAL: cannot connect to invalid database "db_dropped"
Do you plan to handle this case, or should the docs mention it?
Thanks,
Shihao
On Mon, Sep 7, 2026 at 1:21 AM JoongHyuk Shin <sjh910805@gmail.com> wrote:
Show quoted text
CREATE/DROP DATABASE, ALTER DATABASE SET TABLESPACE
and CREATE/DROP TABLESPACE change directories as their WAL is replayed,
while recovery_target_time is only checked on commit and abort records.
So a target set before such a command does not stop recovery before it,
leaving a dropped database gone or an orphan directory.
This was reported in [1] and again in [2].0001 emits an XLOG2 record carrying a timestamp
before the first irreversible record of each command
and stops recovery before it when the timestamp is past the target.
Stopping there loses nothing, as replaying the record has no effect.0002 makes recovery_min_apply_delay wait on the record too.
A delayed standby currently removes a dropped database's directory
as soon as it reads the drop, long before the delay expires.[1]
/messages/by-id/aac5c3e6-2805-ba06-143b-070478aa9434@loxodata.com
[2]
/messages/by-id/43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org--
JH Shin