AW: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles - Mailing list pgsql-bugs

From Lorenz, Christopher
Subject AW: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles
Date
Msg-id 592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de
Whole thread Raw
In response to Re: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles  (Kyotaro Horiguchi <horikyota.ntt@gmail.com>)
List pgsql-bugs
Hi,

Thanks for your explanation. I makes sense cause the DML command DROP isn't transactional so it don't have a COMMIT
whichcan be rolled back. I also can get an valid restore if I set an recovery_target_name before. In most cases I don't
havethe txid or a named recovery point, cause the DROP was done accidentally.  

I've don't test it, but to get a restore as I need:
- Run a recovery to get the txid and exactly time of the DROP, so I also have the last wal I've have to start looking
- find out the last txid of a COMMIT before current stopping point of recovery using pg_waldump, decreasing the txid by
1isn't sure cause the txids are maybe unordered 
- Run the recover a second time with supplied txid to stop after the transaction before the DROP (recovery_target_xid)

> This is not a bug, rather a limitation of recovery_target_time.

I know this is not "real" a bug, it should be in the documentation (I don't found this anywhere in context of
recovery).Or better I should be printed out in log of the recovery if it happened. This case of recovery produce an
postgresqlcluster with database (which is show in list of databases) without datafiles. It recovered a corrupt database
whichisn't fine. After it happened for me I spend time to find the reason of the faulty recovery. 

If postgresql is doing an empty commit before DML commands it should also work.

Regards

Christopher Lorenz



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17454: Using psql without ipv4 address,the inet_server_port do not return PORT.
Next
From: "qiumingcheng"
Date:
Subject: a code question