Re: Offline data checksum changes can cause incorrect checksum state on standbys

> On 31 Aug 2026, at 07:06, Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> 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.

I think we really need to think about documenting a lot of this, potentially
even to the point of saying that offline and online changes should not be mixed
as they work with completely different durability models.

The more I think about this the less excited I am about contorting the logic of
a feature which does proper WAL logging to cope with a tool that doesn't,
including misuses like creating mismatched clusters. We should probably start
to look at improving pg_checksums such that transitions are WAL logged rather
than shoehorning in such changes with a WAL logged flow. pg_checksums rewrites
the datadirectory without the postmaster given any information that any change
was made, which in itself should be a red flag. Making sure that StartupXLOG
can detect the offline change (or something along those lines) and properly log
it seems like a better starting point.

>>> 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.

I don't think the above reinforces not wanting to do a pg_control change at
this point. I think it reinforces that changing datafiles without WAL logging
is a fairly slippery slope.

--
Daniel Gustafsson