pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Lorenz, Christopher <Christopher.Lorenz@ZIT-BB.Brandenburg.de>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: AW: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles
Date: Mon, 4 Apr 2022 07:00:02 +0000
Message-ID: <592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de> (raw)
In-Reply-To: <20220331.172617.940477383352812887.horikyota.ntt@gmail.com>
References: <20220306074134.pmdoskylyxxjhkmb@jrouhaud>
	<b794d37a253944cf977d405ac579c233@ZIT-BB.Brandenburg.de>
	<CAM-sOH8NgXknOEAEp09zyx73Ve-VgH83uxc=BeY4VrTmrSg-yA@mail.gmail.com>
	<20220331.172617.940477383352812887.horikyota.ntt@gmail.com>

Hi,

Thanks for your explanation. I makes sense cause the DML command DROP isn't transactional so it don't have a COMMIT which can be rolled back. I also can get an valid restore if I set an recovery_target_name before. In most cases I don't have the 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 1 isn'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 postgresql cluster with database (which is show in list of databases) without datafiles. It recovered a corrupt database which isn'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





view thread (3+ messages)

Message-ID: <592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de>
Permalink:  ../592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de/
Also on:    postgresql.org/message-id/592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: Christopher.Lorenz@ZIT-BB.Brandenburg.de
  Subject: Re: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles
  In-Reply-To: <592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de>
  References: <20220306074134.pmdoskylyxxjhkmb@jrouhaud> <b794d37a253944cf977d405ac579c233@ZIT-BB.Brandenburg.de> <CAM-sOH8NgXknOEAEp09zyx73Ve-VgH83uxc=BeY4VrTmrSg-yA@mail.gmail.com> <20220331.172617.940477383352812887.horikyota.ntt@gmail.com> <592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox