Date: Mon, 31 Aug 2026 05:06:36 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Zsolt Parragi <zsolt.parragi@percona.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Offline data checksum changes can cause incorrect checksum state
 on standbys
Message-ID: <apUL3N4IE934qJ08@bdtpg>
References: <an8z8CJMa46FIxxn@bdtpg>
 <188A1307-1A92-45CA-9DBE-FB0962D3756E@yesql.se>
 <apEdrmkoNT6R9XyJ@bdtpg>
 <6370C439-408D-40F6-B956-55DF19FFE41C@yesql.se>
 <apFTwa68Qn0S0dIX@bdtpg>
 <CAN4CZFOv4CZfj0i1oesNhZV68zMN7SQtZPLJFEQip2dG=Pf-8Q@mail.gmail.com>
 <apGltAexjCkHPatS@bdtpg>
 <CAN4CZFMaw79Yd-sV=g=016xX3kpijD0WH3fPJEAkSusZwn7Avw@mail.gmail.com>
 <apJGv8fffeUfSkCL@bdtpg>
 <CAN4CZFMoDj7mbY231r-pmPywwJpneksUiNXDwkNokkveGtobQg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAN4CZFMoDj7mbY231r-pmPywwJpneksUiNXDwkNokkveGtobQg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/apUL3N4IE934qJ08%40bdtpg>
Precedence: bulk

Hi,

On Sat, Aug 29, 2026 at 10:36:14PM +0100, Zsolt Parragi wrote:
> > Thanks! I don't see the patch attached. Would you mind sharing it?
> 
> Sorry, I forgot to attach it to the previous email.

Thanks!

=== 1

The v5-0001 commit message says:

"
The documented procedure for offline changes in a replication setup
becomes the lockstep one: stop all nodes, run pg_checksums on each of
them, then restart.
"

I did some more testing and realized that stopping both nodes is not sufficient
to prevent a mismatch in all cases.

For example, start a primary and standby with checksums off, with the standby's
latest replayed checksum transition at L0:

1. Stop the standby.
2. Enable and then disable checksums online on the primary. This writes:

  L1: inprogress-on
  L2: on
  L3: inprogress-off
  L4: off

3. Stop the primary.
4. Run pg_checksums --enable on both stopped nodes.

At this point:

primary: on, watermark L4
standby: on, watermark L0

The standby has not seen L1-L4. When it restarts, each record has an LSN greater
than L0 and is therefore applied. The final XLOG2_CHECKSUMS(off) changes the
standby back to off, while the primary remains on. We get a mismatch despite
both nodes being stopped when pg_checksums ran.

The mismatch remains silent until a later primary checkpoint carrying on is
replayed. FWIW, v1 has the same issue.

Fixing this would probably require recording additional ordering information for
offline changes, adding even more complexity to v5. Another option would be to
document that the standby must be fully caught up before both nodes are stopped
for the offline operation.

> > Do you see the control version change as a concern?
> 
> Yes, it is another non-trivial change in an already complex patch,
> really close to RC1. It's also not an area where we could easily
> implement bug fixes in a minor version, if we discover something
> later.

Yeah, and I think the case above reinforces that concern.

=== 2

+   printf(_("Data checksum watermark:              %X/%08X\n"),
+          LSN_FORMAT_ARGS(ControlFile->data_checksum_lsn));
+   printf(_("Data checksum state is node-local:    %s\n"),
+          (ControlFile->data_checksum_is_local ? _("yes") : _("no")));

That produces pg_upgrade --check against a running source cluster with checksums
enabled to fail with:

"
  old cluster does not use data checksums but the new one does
"

Matching "Data page checksum version:" specifically should fix it.

That makes me realize that we don't have tests for pg_upgrade --check against a
running cluster: I'll open a dedicated thread and submit a patch to add those
new tests.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com