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

Hi,

On Thu, Feb 03, 2022 at 01:26:03PM +0000, PG Bug reporting form wrote:
> 
> a recovery from a basebackup and PIT-recovery after a acccedently DROP
> DATABASE before the deletion, the database is listed in list of database,
> but if I try to connect, the files and directory for that database are
> missing. 
> 
> Reproduce:
> 0. configure postgresql to backup pg_wal
> 1. Create a database (e.g. test)
> 2. make a backup with pg_basebackup
> 3. save the current timestamp (at 14:01:31)
> 4. drop the database (at 14:01:37)
> 5. Save all pg_wal with pg_switch_wal()
> 6. Restore the basebackup to $PGDATA
> 7. Starting point in time recovery to 14:01:31
> 8. Wait and check the database 
>  a. database is show with \l in psql => OK
>  b. connection shows error that the directory in PGDATA/base is missing =>
> Not OK
> 
> Expected:
> Recovery stops before DROP DATABASE command. The list of database shows my
> delete database and all files are existing as in given PIT timestamp.
> 
> Logfile:
> 2022-02-03 14:01:47.073 CET [5526] LOG:  starting point-in-time recovery to
> 2022-02-03 14:01:31+01
> 2022-02-03 14:01:47.093 CET [5526] LOG:  restored log file
> "000000010000000000000002" from archive
> 2022-02-03 14:01:47.148 CET [5526] LOG:  redo starts at 0/2000028
> 2022-02-03 14:01:47.156 CET [5526] LOG:  consistent recovery state reached
> at 0/2000100
> 2022-02-03 14:01:47.156 CET [5524] LOG:  database system is ready to accept
> read only connections
> 2022-02-03 14:01:47.175 CET [5526] LOG:  restored log file
> "000000010000000000000003" from archive
> 2022-02-03 14:01:47.224 CET [5526] LOG:  recovery stopping before commit of
> transaction 486, time 2022-02-03 14:01:37.011667+01
> 2022-02-03 14:01:47.224 CET [5526] LOG:  pausing at the end of recovery
> 2022-02-03 14:01:47.224 CET [5526] HINT:  Execute pg_wal_replay_resume() to
> promote.
> 2022-02-03 14:01:52.199 CET [5546] FATAL:  database "test" does not exist
> 2022-02-03 14:01:52.199 CET [5546] DETAIL:  The database subdirectory
> "base/16384" is missing.

I can't reproduce the problem. Now, given what you seem to be using, this is
likely an operator error:

> Steps in script:
> [...]
> # Stop db
> pg_ctl -D $PGDATAstop

this won't stop the instance.  If that's what you're really doing it's clearly
going to be broken.

> # recovery
> rm -rf $PGDATA/*
> cp -r $backup_dir/0/* $PGDATA/
> touch $PGDATA/recovery.signal
> echo "recovery_target_time='$rec_pit'" >> $inst_dir/postgresql.conf

no WAL recovery?

I recommend reading
https://www.postgresql.org/docs/current/continuous-archiving.html to see what
are the correct steps to restore a PITR backup, and if you still have a problem
please show a script that is self contained (ie. provide all parameter
initialization), safe, and actually works.