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