Re: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles

Hello,

Thanks your answer posted on the World Backup Day:
https://worldbackupday.com/en :-)

Regards

Le jeu. 31 mars 2022 à 10:26, Kyotaro Horiguchi <horikyota.ntt@gmail.com> a
écrit :

> At Wed, 30 Mar 2022 21:07:40 +0200, Pierre Forstmann <
> pierre.forstmann@gmail.com> wrote in
> > I can reproduce your issue with your base_recovery.sh with PG 13.6 on
> Alma
> > Linux 8.5.
> >
> > If I modify your script to display transaction numbers before and after
> > DROP DATABASE with:
> >
> > echo -e $rt `date` "drop the database test ..." $wt
> > # Drop DB
> > psql -c 'select txid_current();'
> > psql -c 'DROP DATABASE test'
> > psql -c 'select txid_current();'
>
> This is not a bug, rather a limitation of recovery_target_time.
>
> The script specifies recovery target by time. The target time time is
> compared with timestamps embedded in some kinds of WAL record, mainly
> COMMIT.
>
> The reason why it works is that the query creates COMMIT
> records. Otherwise no timestamp seen since the backup point and before
> the DROP record. Thus recovery has no means to stop by the target time
> before the DROP.
>
>
> The WAL for the bad case looks like this (abbreviated and thinned out):
>
> tx:  486,  desc: DROP dir 1663/16384
> tx:  486,  desc: COMMIT 2022-03-31 16:58:05.981976 JST; inval msgs:
> catcache 21; sync
> tx:    0,  desc: SWITCH
>
> > 2022-03-31 16:58:11.448 JST [145533] LOG:  recovery stopping before
> commit of transaction 486, time 2022-03-31 16:58:05.981976+09
>
> There's no timestamped WAL record found before the DROP, recovery runs
> until the COMMIT record *after* the DROP, so the directory is removed.
>
>
> If txid_current() is performed, it looks like:
>
> tx: 486,  desc: COMMIT 2022-03-31 17:10:25.775619 JST
> tx: 487,  desc: DELETE off 4 flags 0x00 KEYS_UPDATED , blkref #0: rel
> 1664/0/1262 blk 0 FPW
> tx: 487,  desc: DROP dir 1663/16384
> tx: 487,  desc: COMMIT 2022-03-31 17:10:25.794775 JST
> tx: 488,  desc: COMMIT 2022-03-31 17:10:25.801121 JST
> tx:   0,  desc: SWITCH
>
> > recovery stopping before commit of transaction 486, time 2022-03-31
> 17:10:25.775619+09
>
> In this case, revovery stops *before* the DROP.
>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>