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(dot)ntt(at)gmail(dot)com> a
écrit :

> At Wed, 30 Mar 2022 21:07:40 +0200, Pierre Forstmann <
> pierre(dot)forstmann(at)gmail(dot)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
>