On Mon, 18 Nov 2019 at 18:48, Nicolas Lutic <n.lu...@loxodata.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
