Re: PITR on DROP DATABASE, deleting of the database directory despitethe recovery_target_time set before.

Dear Hackers,

After a drop database

, he tried to recover the data on the last inserted transaction by using the recovery_target_time.

The issue is the database is present in the system catalog but the directory was still deleted.

Here the technical information of the database

version 11

default  postgresql.conf except for this options

    wal_level = replica

    archive_mode = on

    archive_command = 'cp %p /tmp/wal_archive/%f '

    log_statement = 'all'

    log_min_messages = debug5

  

The following method was used 

• create cluster

• create database

• create 1 table 

• create 1 index on 1 column

• insert 1 rows

• backup with pg_base_backup

• insert 2 rows

• drop database

•     Change recovery behaviour in that case to prevent all xact operation to perform until COMMIT timestamp is checked against recovery_time bound (but it seems to be difficult as state https://www.postgresql.org/message-id/flat/20141125160629.GC21475%40msg.df7cb.de which also identifies the problem and tries to give some solutions.  Maybe another way, as a trivial guess (all apologises) is to buffer immediate xacts until we have the commit for each and apply the whole buffer xact once the timestamp known (and checked agains recovery_target_time value);

•     The other way to improve this is to update PostgreSQL documentation  by specifying that recovery_target_time cannot be used in this case. There should be multiple places where it can be stated. The best one (if only one) seems to be in  https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/config.sgml;h=