Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTlLq-00AaTi-8v for pgsql-general@arkaria.postgresql.org; Wed, 09 Aug 2023 15:43:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qTlLo-006XwM-1K for pgsql-general@arkaria.postgresql.org; Wed, 09 Aug 2023 15:43:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTlLn-006XwE-CG for pgsql-general@lists.postgresql.org; Wed, 09 Aug 2023 15:43:56 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qTlLg-001VSW-Jk for pgsql-general@lists.postgresql.org; Wed, 09 Aug 2023 15:43:54 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 2983F5F7B7; Wed, 9 Aug 2023 11:43:42 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1691595822; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=d9vtZBowxJjD55Txpb/ml/tPOBhETVVdPfN/RAPd25k=; b=IYW6/jbXpRna1+WBUsbpw9EStIb8SG7M/Sr/m9o0c5ObpwhzwD1uGpsnAezwa+HdlcEuIu yKPsFCrNREGDnXtfq8etVqzlq7XSxeakyKxHb5r/FdJfrEcPUAFJxO1aRkYVHYgEWPyQyH 0T8F3d+2r2WeBqx+fdrXu+gIx0ddelc= Date: Wed, 9 Aug 2023 11:43:42 -0400 From: Stephen Frost To: Meera Nair Cc: "pgsql-general@lists.postgresql.org" , Punit Pranesh Koujalgi Subject: Re: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="amj9g+FjGYQ+S461" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --amj9g+FjGYQ+S461 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Greetings, * Meera Nair (mnair@commvault.com) wrote: > The backups we ran were all exclusive backups. You should not use exclusive backups. They've been deprecated for a long, long time and have been removed in the most recent version of PG. You really should not be trying to build your own tooling for this. Please, use an existing tool where the authors have already worked through how all of this works and understand how BACKUP_END works and how to reliably perform a backup and restore of PG. Despite the documentation seeming to imply that it's trivial to do this on your own, it really isn't. > pg_start_backup('label') returned 000000040000000200000041. Then we made a copy of data directory . > Then pg_stop_backup() was executed and it returned 000000040000000200000046. I'm really not sure what you're doing exactly, but it sure looks like the BACKUP_END in the 46 WAL file is associated with the .backup file for the backup started in the 45 segment. You have this file: 000000040000000200000045.00005AE0.backup and then this is what you reported from the 46 file: rmgr: XLOG len (rec/tot): 34/ 34, tx: 0, lsn: 2/46000110, prev 2/46000098, desc: BACKUP_END 2/45005AE0 Note that the 'BACKUP_END' data content there is the starting point of the backup for which that is the BACKUP_END for- 2/45005AE0. That matches with a backup being started in the 45 file and matches the start time of the .backup file that you have in the archive. That's why the restore of the backup started in the 41 segment isn't picking up on the BACKUP_END in the 46 file as being the one matching that backup- because that's from some other backup. > To verify the copied data is restorable, we stopped server. Renamed data and archive log directories. > Replaced it with data and archive log directories copied earlier. In archive log directory logs from 41 to 49 is present. The pg_wal dir can and should be empty at the start. > Created recovery.signal and added restore_command to postgresql.conf file. And tried to start server. this failed with error in previous mail. The restore_command should be copying WAL files from the location where they were stored by the archive_command. Thanks, Stephen --amj9g+FjGYQ+S461 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmTTtCsACgkQ7WyKOINH ZFXYVxAAgHVsttsIABYOreEgqNOEcoBVCcVbEcKKFYfdAkFaqQAOCK5LRMEDTSkY NKkb//eqlAU+y61fyLNAQ3Qhl4v/HDQNwLc9UE/hX0aOpNOxfxoBTOJr8EX9gwRE ZxUHKaTl/uEssqgIPKPX83gn5wHjQbGoNv3qOgfuy8peF34MI52fLsVMSHZDp2K1 sG/6l7mbEVPsQZSrMOix2Q6zV0bEPi1iXZMwgMFsgr5Y3YogyMnVSBJ/MWzDz/Ze Fyo4Y/D5MPGTpuAlvpC+3fiB6qOEtv/zTAxsTnfQlwIKnENKvoXtdJWdw5EFDN4b Q6jrMMHfxVIsvvfpB54Wf2Th6lboY3e5xJIjhbQ6/jzZ72KVKFk4NSPFH+A0xauL 5h191JBXR7m8M02oaokjZDcVVSkat3pTtDhPJU/d1iRz8InbITk7zM4SydpEubhm 4YvQX7IN4dwhN4ZkTb+GNsAu8c6YmGWrvve6l6z5cbaTLR2pKzvSRFtofc6yicOE 8+fTZGaA7SGiTQGH51cPhVu5BkF/lgIQcbmee5JVJTXuJPHc6cBCLaopFhwmYi0+ pWBO3WfJKJnPHcuAGip5GEa1RBIXtHfotDJgQikQPUZG03MgCIFwyiZ2dYpOqQjD +jC9vmwyQzAkaM1DxPaHsdSg5kLB31o1lHFFfPnZ/KJ2pgSvQvU= =PnKW -----END PGP SIGNATURE----- --amj9g+FjGYQ+S461--