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 1qxGaF-00HFfS-FQ for pgsql-general@arkaria.postgresql.org; Mon, 30 Oct 2023 00:56:47 +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 1qxGaE-00A6OV-4m for pgsql-general@arkaria.postgresql.org; Mon, 30 Oct 2023 00:56:46 +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 1qxGaD-00A6ON-QF for pgsql-general@lists.postgresql.org; Mon, 30 Oct 2023 00:56:45 +0000 Received: from wout1-smtp.messagingengine.com ([64.147.123.24]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qxGaB-003k1f-Ck for pgsql-general@lists.postgresql.org; Mon, 30 Oct 2023 00:56:45 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.west.internal (Postfix) with ESMTP id 0B68232004AE for ; Sun, 29 Oct 2023 20:56:40 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Sun, 29 Oct 2023 20:56:41 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to; s=fm3; t=1698627400; x=1698713800; bh=dg F97EJv2o8CnGMJlgECmFH26RPVSUo7FkeKK21m1e8=; b=s9N19Jy/nAMqgryPda LBXmXz0s78yyqRZNnyUFSB5f0l6fIR56vujENcZ01TdxghS31HAnwiqMnLWATX3e hCUlTK5CJun5NdOPKRjVXXlcp3dyYPBq3IUOJDgVr0uQs6dAvevq/8HW2RXen8hz IIU5lcXOw/ok8H1C8SVp89mtE1OgyTtIMabyweQvhqIYA0OCYploWjAWmtWXJ/+l YKByHnFTpZIHR56F1tKkNwp25RCKNGv1wv3H+xvRywiN2k87R86ByWcCM0OGTgv7 l/JFOmG8BIA5PEXstGj6UahxH7FJkI4S3Aaac3UEsZOnP2SfGmtOsb7plRlFNYu2 aTbg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm3; t=1698627400; x=1698713800; bh=dgF97EJv2o8Cn GMJlgECmFH26RPVSUo7FkeKK21m1e8=; b=mC2Pum4Inm+XVHuChK0sgl18S4PIl Yy5TNoZr1JggSCNoGVzD2BIXdqpqGJkmRNlEZhm41nN59/RpEoreOBHrE8IBLqR1 aW0y13W3IxqmvRYld1f5S0Es1aGXqTlN/WH9MkHtsPJnF1aZ3fbz5AlEW9MEHtxs KpghZAZDdSFpJAX46fDFhytBogptB5w499u3TgqDCn0QkGzunkctpHVKlbeBulPM 5NVH6DkRoOdOqbqETL595DmAazOmfkdpxqvQP5uc2nQqLVMk2qb1SoFdAD2sjN9F gY4z3sR/yY5d/VmiZ8bxz6lB9tVBJIp6PYcnonXBV/L+n1VBG0Yz6xtSA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrleelgddvkecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecufghrlhcuvffnffculdefhedmnecujfgurhepfffhvf fukfhfgggtuggjsehgtderredttddunecuhfhrohhmpefoihgthhgrvghlucfrrghquhhi vghruceomhhitghhrggvlhesphgrqhhuihgvrhdrgiihiieqnecuggftrfgrthhtvghrnh epvedtgfekudduueevteefgefgjeduvddtteeuhfelueffueekiefhjefgieegudelnecu vehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomhepmhhitghhrg gvlhesphgrqhhuihgvrhdrgiihii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA for ; Sun, 29 Oct 2023 20:56:39 -0400 (EDT) Date: Mon, 30 Oct 2023 09:56:31 +0900 From: Michael Paquier To: pgsql-general@lists.postgresql.org Subject: Re: pg_checksums? Message-ID: References: <22272207-E6E5-4DD5-9BD4-5F6B647E2C3B@gmail.com> <0D18D31E-EF52-434D-875C-08BEC8DDD553@yesql.se> <20231029014310.7pbdfhincj2tds4l@hjp.at> <81D43A87-7D3D-4F81-B462-641F15B5E332@gmail.com> <20231029104911.dj73ivozabdr5or4@hjp.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="5pqPLIepNCKpoi40" Content-Disposition: inline In-Reply-To: <20231029104911.dj73ivozabdr5or4@hjp.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --5pqPLIepNCKpoi40 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sun, Oct 29, 2023 at 11:49:11AM +0100, Peter J. Holzer wrote: > On 2023-10-29 10:11:07 +0100, Paul F=F6rster wrote: >> On Oct 29, 2023, at 02:43, Peter J. Holzer wrote: >>> I don't think so. AFAIK Replication keeps the data files in sync on a >>> bit-for-bit level and turning on checksums changes the data layout. >>> Running a cluster where one node has checksums and the other doesn't >>> would result in a complete mess. >>=20 >> I agree with the last sentence. This is why I asked if it is safe to >> enable checksums on a replica, switch over and then do it again on the >> ex primary, i.e. now new replica without doing a reinit. >=20 > It *might* work if there are zero writes on the primary during the > downtime of the replica (because those writes couldn't be replicated), > but that seems hard to ensure. Even if you could get away with making > the primary read-only (is this even possible?) I wouldn't have much > confidence in the result and reinit the (new) replica anyway. Hm? Page checksums are written when a page is flushed to disk, we don't set them for dirty buffers or full-page writes included in WAL, so it should be OK to do something like the following: - Stop cleanly a standby. - Run pg_checksums on the standby to enable them. - Restart the standby. - Catchup with the latest changes - Stop cleanly the primary, letting the shutdown checkpoint be replicated to the standby. - Promote the standby. - Enable checksums on the previous primary. - Start the previous primary to be a standby of the node you failed over to. -- Michael --5pqPLIepNCKpoi40 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmU+/z8ACgkQnvQgOdby QH2nvQ/+JjqSsVnJulyb6JLrUWKSQJ4lOcf5RHrT+lZ7BQbrqLw13/8bxXOUfe3L 0w/d1pyz/11W9Bd+/JbojwIIs4whrfuDZDduV2dbbng2aZrwd8PGt87eZpxTJvEF HAxDaV1HYacpHLQ4H+E17Q4fZ9G5ocvSp0Q0IYaKNZn2w/WWkSN/oCmxOT3rLAgi XK/HTBJF8xo+3UIfY0d4Ii3St67jXyGJNvaA9S1VPBlWZcue5fJ+Mc9mtqyrD9I6 GMBnpeozofQs6lfndWgjeGhaBf/EhF99w98dgJrIbt37Gg4YY+qb7ksMKaZST/GY jdUmjKxDLhRA0pRfRWHJoZZWLePPFx+x9Fd4qwgp7AfEXxNbnQrVbeLAeZ0qnLWY 9PfXzHS84z3YoeVX7lboo/4yo6vMhxBvnyYjs4qHQczvHVoUnUUtJ5Di8O55a5EN rT2sqsRRCWWz5Gl0yPfAfChwdg9hk+rxn7qJ56hLLHdWCAke7cipvM5QYNA6HP2G aMp3wGu0BT8GYq+uJoe0jO8l/ZEwzLHXswTBHXSly0Kxu411+zlczP2bIcZgPNqv pcJbBwo2Q4H3TBo2JscWyWxzRtgT01bpo3pIyYNcFNe0D5B1O0g7nWEq1Kywi44Z CR6tI2X3rz/yNIkPXzQQ+9hS9xjKWhHfuQN9/MmgdnAtWSlUQQY= =hi1u -----END PGP SIGNATURE----- --5pqPLIepNCKpoi40--