Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbGhF-0003xj-Fy for pgsql-bugs@arkaria.postgresql.org; Mon, 04 Apr 2022 07:00:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nbGhE-0001fU-EF for pgsql-bugs@arkaria.postgresql.org; Mon, 04 Apr 2022 07:00:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbGhE-0001fK-5X for pgsql-bugs@lists.postgresql.org; Mon, 04 Apr 2022 07:00:16 +0000 Received: from mailout208.brandenburg.de ([194.76.232.208]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbGh7-0000xW-70 for pgsql-bugs@lists.postgresql.org; Mon, 04 Apr 2022 07:00:15 +0000 From: "Lorenz, Christopher" To: "pgsql-bugs@lists.postgresql.org" Subject: AW: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles Thread-Topic: BUG #17393: Delete database after recovery with point-in-time is still missing datafiles Thread-Index: AQHYGQHHvRla3dcSnEiAs9CiPRT6ZqyyGG0AgCYJQCCAAF2iAIAA3yKAgAZCxlA= Disposition-Notification-To: "Lorenz, Christopher" Date: Mon, 4 Apr 2022 07:00:02 +0000 Message-ID: <592564ffc7c34699b869aee3a492e1fb@ZIT-BB.Brandenburg.de> References: <20220306074134.pmdoskylyxxjhkmb@jrouhaud> <20220331.172617.940477383352812887.horikyota.ntt@gmail.com> In-Reply-To: <20220331.172617.940477383352812887.horikyota.ntt@gmail.com> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.129.65.41] x-tm-as-product-ver: SMEX-14.0.0.1158-9.0.1000-26812.006 x-tm-as-result: No-10--9.794100-8.000000 x-tmase-matchedrid: 2mVTIgh7BvF4GcCqxQcAiI61Z+HJnvsO82SgwNf6SK7G5dXdQTj2OMD4 MZKpUfApaIAATe4gmZCh8xqgyGTj+C01VXlcjcCUEzEoOqAAVLPv9JJ5souqzOE1pYoXZydBKPn doQHmoMv0IM5Lt2A9/v+CEHfQvY8OhpgZAljFgxOSjoRSvyZJ0FvQxoz1O6zPmyiLZetSf8kir3 kOMJmHTB9GgLAHOBFiec3QM3secWYc6bZYImzmnMXWXEB1OG4kYQ2KBeSApUgXsREBq1zrccnrI +JCw+6dlExlQIQeRG0= x-tm-as-user-approved-sender: No x-tm-as-user-blocked-sender: No x-tmase-result: 10--9.794100-8.000000 x-tmase-version: SMEX-14.0.0.1158-9.0.1000-26812.006 x-tm-snts-smtp: DECC6F7E4CBB406A80F9882883DF76DD699D8F56E1CFB82ECF70BF7F74A02AD02000:9 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 c= an get an valid restore if I set an recovery_target_name before. In most ca= ses I don't have the txid or a named recovery point, cause the DROP was don= e accidentally.=20 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 ha= ve the last wal I've have to start looking - find out the last txid of a COMMIT before current stopping point of recov= ery using pg_waldump, decreasing the txid by 1 isn't sure cause the txids a= re maybe unordered - Run the recover a second time with supplied txid to stop after the transa= ction 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 a= n postgresql cluster with database (which is show in list of databases) wit= hout 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 w= ork. Regards Christopher Lorenz