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

On Mon, 18 Nov 2019 at 18:48, Nicolas Lutic <n(dot)lutic(at)loxodata(dot)com> wrote:

> Dear Hackers,
>
> After a drop database
>

with FORCE?

> , 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
>
> autocommit?

>
>
>
> - drop database
>
> force?

>
> - 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=
> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/config.sgml;h=f83770350eda5625179526300c652f23ff29c9fe;hb=HEAD#l3400>
>
>
If this only happens when a DB is dropped under load with force, I lean
toward just documenting it as a corner case.

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise