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

> On 14 Aug 2026, at 17:27, Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:

> Looking forward to seeing your and Zsolt's proposals.

This has now been worked on quite extensively by Zsolt, myself and Tomas Vondra
and a number of patchrevisions have been created and rewritten. There are two
separate issues in this report: a) a correctly done offline checksum change in
a replicated cluster doesn't work; b) mismatched checksum states across
replicated nodes is not detected and will make pg_rewind and similar tools
dangerous. The former is a regression due to the online checksums work, the
latter is an issue which exists in all supported versions due to how
pg_checksums was implemented. This is today mentioned very briefly in the docs
but clearly hasn't been looked into to fix. Below are each issue discussed in
more detail.

The regression in a correctly done offline change is due to combining
pg_checksums which rewrite data on risk without WAL logging (or any logging at
all) the transformation, with online checksums which WAL log the state change.
With online checksums, the local state on the standby was overwritten during
replay by the dataChecksumState in the checkpoint. By definition, the
checkpoint will be from before the offline change and thus enabling checksums
would replay checksums being disabled. The fix in 0001 is to not adopt the
state change from the replay of checkpoints, only from XLOG2_CHECKSUMS records,
and to alert the user with a log entry if the states mismatch.

Detecting a state mismatch, and refusing to start a standby which does not
match the primary is a lot harder than it may seem. We have had a few
different patches implementing this and they all have the flaw that by the time
the standby can be shut down due to mismatch, it needs to be rebuilt from a
base backup and cannot be recovered with the (presumably) missing pg_checksums
command. Due to this, the current approach is to log a WARNING for mismatched
states, which while not perfect improves upon what we have today in v14 through
v18 where it's silently ignored.

There are also few more commits in this patchset related to issue B):

0002 makes pg_checksums refuse to operate on standbys where the state hasn't
been resolved from an online checksums change. On a primary, the state will
heal itself upon startup and pg_checksums refuse a crashed primary already.

0003-0004 fixes pg_rewind and pg_combinebackup to error out on mismatched
states instead of risk damaging data. A variant of these fixes should be
backpatched into all supported versions are the issue is present with offline
checksums.

So why wasn't this regression caught before feature freeze? The main reason is
that I failed to add test cases for offline checksum changes in replicated
clusters when I wrote the online checksums patch. It contains tests for
offline change of a single primary which is the easier case to handle. The
pg_checksums test suite also doesn't test the replicated scenario at all. Even
if online checksums end up reverted, tests for replicated clusters should be
added to pg_checksums as it currently lacks test coverage.

The 0001 patch is the least invasive patch to solve the regression that either
of us has managed to come up with, but it's still far from trivial. The plan
going forward for this hinges on whether or not online checksums get reverted,
but here is at least a patchset addressing the open item for future reference.
Should this get committed we probably need to gate a few tests under
PG_TEST_EXTRA to keep things at a reasonable scale, but for now they are all
left in the main path.

--
Daniel Gustafsson