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 1m6zEA-00052B-3a for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 17:44:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m6zE9-0006n8-2U for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 17:44:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m6zE8-0006n0-Pk for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 17:44:48 +0000 Received: from mail.am-soft.de ([83.218.36.120]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m6zE6-0004ey-4t for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 17:44:48 +0000 Received: from mail.am-soft.de (localhost [127.0.0.1]) by mail.am-soft.de (Postfix) with ESMTP id 548333E20817; Fri, 23 Jul 2021 19:44:45 +0200 (CEST) Received: from Bit02EX.bitstoregruppe.local (exchange01.bitstore.group [116.202.141.225]) by mail.am-soft.de (Postfix) with ESMTPA id 3E2EE3E20815 for ; Fri, 23 Jul 2021 19:44:45 +0200 (CEST) Received: from tschoening-nb.fritz.box (77.78.163.99) by Bit02EX.bitstoregruppe.local (192.168.254.21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2106.2; Fri, 23 Jul 2021 19:44:32 +0200 Date: Fri, 23 Jul 2021 19:44:35 +0200 From: =?utf-8?B?VGhvcnN0ZW4gU2Now7ZuaW5n?= Organization: AM-SoFT IT-Service - Bitstore Hameln GmbH Message-ID: <562203777.20210723194435@am-soft.de> To: Subject: Re: What's the dfifference between pg_start_backup+copy+pg_stop_backup+WAL vs. pg_start_backup+pg_stop_backup+copy+WAL? In-Reply-To: <20210723152418.GK20766@tamriel.snowman.net> References: <1208702063.20210723115638@am-soft.de> <399c734c5b9ffe10f68bcb79a69bea000d8d0c21.camel@cybertec.at> <1751752722.20210723161202@am-soft.de> <20210723152418.GK20766@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Originating-IP: [77.78.163.99] X-ClientProxiedBy: Bit02EX.bitstoregruppe.local (192.168.254.21) To Bit02EX.bitstoregruppe.local (192.168.254.21) X-C2ProcessedOrg: e1a4acb5-ea26-4a8f-9559-e23e579650e8 X-AV-Checked: ClamAV using ClamSMTP List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Guten Tag Stephen Frost, am Freitag, 23. Juli 2021 um 17:24 schrieben Sie: > [...]Similar, once you reach the BACKUP_END record of > a backup you know the database is consistent- but only if all of the > files that PG was writing to were copied between the start checkpoint > of the backup and the BACKUP_END record in the WAL. So I am correct that the recovery process starts at what is documented as CHECKPOINT LOCATION in the file backup_label? If the data directory looks like a more current checkpoint might be available, that is NOT used? Instead, all available WALs are replayed? But how come the state of other data files into the recovery process? that's what Laurenz Albe's example is about: A data file which is more current than expected because that file is only copied, but the associated changes are stored in WALs after pg_stop_backup and therefore need to be considered lost. Why is the data file with that additional row in B used at all? The additional row is associated with a transaction number not available when restoring based on CHECKPOINT LOCATION + WALs of pg_stop_backup. So why is that row used at all instead of simply ignored? That would actually revert the cluster to the state of pg_stop_backup, but as said, backup outdate anyway. I still don't get why the result is not as consistent as e.g. a crash at the same point in time instead of doing a backup. Mit freundlichen Gr=C3=BC=C3=9Fen Thorsten Sch=C3=B6ning --=20 AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister f=C3=BCr IT u= nd TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Ham= eln AG Hannover HRB 221853 - Gesch=C3=A4ftsf=C3=BChrer: Janine Galonska F=C3=BCr R=C3=BCckfragen stehe ich Ihnen sehr gerne zur Verf=C3=BCgung. Mit freundlichen Gr=C3=BC=C3=9Fen Thorsten Sch=C3=B6ning Tel: 05151 9468 0 Fax: 05151 9468 88 Mobil:=20 Webseite: https://www.am-soft.de=20 AM-Soft IT-Service - Bitstore Hameln GmbH i.G.=C2=A0ist ein Mitglied der Bi= tstore Gruppe - Ihr Full-Service-Dienstleister f=C3=BCr IT und TK AM-Soft IT-Service - Bitstore Hameln GmbH i.G. Brandenburger Str. 7c 31789 Hameln Tel: 05151 9468 0 Bitstore IT-Consulting GmbH Zentrale - Berlin Lichtenberg Frankfurter Allee 285 10317 Berlin Tel: 030 453 087 80 CBS IT-Service - Bitstore Kaulsdorf UG Tel: 030 453 087 880 1 B=C3=BCro Dallgow-D=C3=B6beritz Tel: 03322 507 020 B=C3=BCro Kloster Lehnin Tel: 033207 566 530 PCE IT-Service=C2=A0- Bitstore Darmstadt UG Darmstadt Tel: 06151 392 973 0 B=C3=BCro Neuruppin Tel: 033932 606 090 ACI EDV Systemhaus - Bitstore Dresden GmbH Dresden Tel: 0351 254 410 Das Systemhaus - Bitstore Magdeburg GmbH Magdeburg Tel: 0391 636 651 0 Allerdata.IT - Bitstore Wittenberg GmbH Wittenberg Tel: 03491=C2=A0876 735 7 B=C3=BCro Liebenwalde Tel: 033054 810 00 HSA - das B=C3=BCro - Bitstore Altenburg UG Altenburg Tel: 0344 784 390 97 Bitstore IT - Consulting GmbH NL Piesteritz=20 Piesteritz Tel: 03491 644 868 6 Solltec IT-Services - Bitstore Braunschweig UG Braunschweig Tel: 0531 206 068 0 MF Computer Service - Bitstore G=C3=BCtersloh GmbH G=C3=BCtersloh Tel: 05245=C2=A0920 809 3 Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger = Str. 7c , 31789 Hameln Gesch=C3=A4ftsf=C3=BChrer Janine Galonska