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 1lFKpk-0005aC-6X for pgsql-bugs@arkaria.postgresql.org; Thu, 25 Feb 2021 17:53:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lFKpj-0005RY-3s for pgsql-bugs@arkaria.postgresql.org; Thu, 25 Feb 2021 17:53:51 +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 1lFKpi-0005RR-UF for pgsql-bugs@lists.postgresql.org; Thu, 25 Feb 2021 17:53:50 +0000 Received: from mail.thelabyrinth.net ([45.56.70.56]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lFKpf-0005KT-R7 for pgsql-bugs@lists.postgresql.org; Thu, 25 Feb 2021 17:53:50 +0000 Received: from desktop.home.thelabyrinth.net (c-73-39-102-148.hsd1.md.comcast.net [73.39.102.148]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id 0114954662; Thu, 25 Feb 2021 17:53:44 +0000 (UTC) Subject: Re: BUG #16894: PANIC: WAL contains references to invalid pages To: =?UTF-8?B?0JDQvdGC0L7QvSDQmtGD0YDQvtGH0LrQuNC9?= , pgsql-bugs@lists.postgresql.org References: <16894-c29735df12727642@postgresql.org> From: David Steele Message-ID: Date: Thu, 25 Feb 2021 12:53:44 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.16; rv:78.0) Gecko/20100101 Thunderbird/78.7.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2/24/21 7:22 AM, Антон Курочкин wrote: > > We got error when PITR restoring individual databases from a backup > using > pgbackrest. > > Opened issue in pgbackrest repository: > https://github.com/pgbackrest/pgbackrest/issues/796 > > It would be great if you would look at this problem. All the > information is > in the issue. > > Pgbackrest developers offered to provide this information to the > postgres > folks. > > Turned out that the problem is playing WAL files on databases that are > restored with zero files. > The database files from the backup are restored successfully, but an > error > occurs when starting PG and playing WAL: > < 2021-02-21 10:35:09.050 UTC > WARNING: page 22659 of relation > base/17221/17557 is uninitialized > < 2021-02-21 10:35:09.050 UTC > CONTEXT: xlog redo at 7E/8D9C04C0 for > Heap2/VISIBLE: cutoff xid 810424291 flags 1 > < 2021-02-21 10:35:09.050 UTC > PANIC: WAL contains references to > invalid pages > < 2021-02-21 10:35:09.050 UTC > CONTEXT: xlog redo at 7E/8D9C04C0 for > Heap2/VISIBLE: cutoff xid 810424291 flags 1 For context, when a user performs selective restore all the relations for databases *not* selected are restored as sparse zero files with the correct size. WAL replay will update those relations but they are not expected to be consistent, and in fact we make it impossible to logon to those databases so the only option is to drop them. Still, this is a fairly efficient way to get at a single database in a cluster that contains hundreds or thousands of databases. Clearly the methodology is a bit unorthodox, but even so we don't expect to see an error here. This is the first time we've gotten a detailed analysis of the issue, so I haven't had time to really look into it, but perhaps there is a full page write missing? In the case of corruption the administrator might zero a page and we expect WAL replay to succeed, I think. Regards, -- -David david@pgmasters.net