Offline data checksum changes can cause incorrect checksum state on standbys

On 12 Aug 2026, at 09:55, Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:

First one, is a case where a standby can enable checksum verification without its
own pages having been checksummed, making the standby unreadable.

Thanks for the report. While I don't have a proposal ready at this time of
writing, I wanted to ACK having seen this and make it known (to RMT) that it is
being worked on.

Therefore, I think a simpler fix is to preserve the pre-f19c0eccae96 behavior
for offline checksum changes: they are not propagated through WAL. In the second
repro, the standby therefore remains off, honoring its local offline change.

I wholeheartedly disagree, running a cluster with mismatched data_checksums
settings across the nodes is not a supported mode of operation, and is already
documented to not work (albeit it way too vague wording IMO). This doesn't
work as it is right now (in any version of postgres), pg_rewind or other file
based tools can break it, and we should not attempt to make it work.

Detecting a cluster with mismatched settings and safely erroring out as well as
improving the documentation is what I think we should do.

If this looks like too much code changes so close to the v19 release, another
option could be to remove pg_checksums --enable and --disable while keeping --check
and require checksum state changes to be done online.

That's also not a good option, I think we need to make sure offline enabling of
checksums *if done correctly* works as intended, and if done incorrectly errors
out safely.

I have a patch proposal brewing, and I know Zsolt has been looking into it as
well. Hopefully there will be something to share very soon.

--
Daniel Gustafsson

Hi,

On Fri, Aug 14, 2026 at 03:59:06PM +0200, Daniel Gustafsson wrote:

running a cluster with mismatched data_checksums
settings across the nodes is not a supported mode of operation, and is already
documented to not work

Thanks for feedback!

The pre f19c0eccae96 pg_checksums documentation said:

"
When using a replication setup with tools which perform direct copies
of relation file blocks (for example pg_rewind), enabling or disabling
checksums can lead to page corruptions in the shape of incorrect
checksums if the operation is not done consistently across all nodes.
"

I read that as a recommendation to stop and switch all nodes consistently, and
as a warning about direct block-copy tools. That interpretation, together with
the pre f19c0eccae96 behavior, is why v1 proposed keeping offline pg_checksums
changes local. The intention was to preserve the previous behavior, not to
introduce a new supported mode.

I just realized that f19c0eccae96 explicitly changed the "Off-line Enabling of
Checksums" documentation:

"
Data checksums are enabled or disabled at the full cluster level, and cannot
be specified individually for databases or tables.
"

by:

"
Data checksums are enabled or disabled at the full cluster level, and cannot
be specified individually for databases, tables or replicated cluster members.
"

while leaving the pg_checksums documentation quoted above unchanged.

Depending on how the issue will be addressed, it might be worth changing this
pg_checksums wording too?

Looking forward to seeing your and Zsolt's proposals.

Regards,

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

On 14 Aug 2026, at 17:27, Bertrand Drouvot <bertranddrouvot.pg@gmail.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

Hi,

On Wed, Aug 26, 2026 at 10:37:09PM +0200, Daniel Gustafsson wrote:

On 14 Aug 2026, at 17:27, Bertrand Drouvot <bertranddrouvot.pg@gmail.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.

Thanks for looking at it!

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.

Agreed.

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.

That makes sense to me and matches the intent of my v1 for this regression: keep
offline changes local while still applying WAL online transitions, with the useful
addition of a warning on mismatch.

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.

Only looking at 0001 here, I've a few comments:

=== 1

@@ -9288,17 +9572,33 @@ xlog2_redo(XLogReaderState *record)

SpinLockAcquire(&XLogCtl->info_lck);
XLogCtl->data_checksum_version = state.new_checksum_state;
+ SetLocalDataChecksumState(state.new_checksum_state);
SpinLockRelease(&XLogCtl->info_lck);

This applies every XLOG2_CHECKSUMS record encountered during recovery, even when
the same record was applied before.

For example, a standby can replay the final "on" record and then stop cleanly
without advancing its restartpoint beyond that record. If checksums are subsequently
disabled offline, the next startup begins from the older restartpoint and replays
the same on record again, overriding the offline disable.

=== 2

+    * checkPoint.dataChecksumState was sampled while holding the WAL insert
+    * locks, so it is the state in effect at the redo point. 
.
.
.
+   SpinLockAcquire(&XLogCtl->info_lck);
+   if (checkPoint.dataChecksumState == XLogCtl->data_checksum_version)
+       ControlFile->data_checksum_version = checkPoint.dataChecksumState;
+   SpinLockRelease(&XLogCtl->info_lck);

I’m not sure the "state in effect at the redo point" is always correct. There is
a window between inserting the checksum transition record and updating
XLogCtl->data_checksum_version.

XLOG2_CHECKSUMS(on) records the target state. The transition can therefore
proceed as follows:

1. The current shared state is inprogress-on.
2. XLogChecksums() inserts XLOG2_CHECKSUMS(on) and releases its WAL insertion
lock.
3. Before the shared state is updated to on, the checkpoint still reads
inprogress-on and inserts XLOG_CHECKPOINT_REDO.
4. The transition then updates the shared state to on.

The WAL order is then:

XLOG2_CHECKSUMS(on)
XLOG_CHECKPOINT_REDO(inprogress-on)

If the server crashes after the concurrent checkpoint from step 3 completes,
but before the enabling operation’s later checkpoint completes, recovery starts
from that redo point and does not replay the preceding on record. It can
therefore resolve inprogress-on back to off. The equality check above does
not repair this ordering.

=== 3

+   if ((haveBackupLabel || XLogRecPtrIsValid(ControlFile->backupStartPoint)) &&
+       !(XLogRecPtrIsValid(ControlFile->backupEndPoint) &&
+         ControlFile->backupEndRequired))
+   {
+       if (wasShutdown)
+           AdoptReplayedDataChecksumState(checkPoint.dataChecksumState);
+       else
+           adoptChecksumStateFromNextCheckpoint = true;
+   }

IIUC, this can overwrite the checksum state copied from the source during
pg_rewind with the state from the last common checkpoint.

For example, if the common checkpoint says off, but both source and target
were enabled offline after divergence, recovery adopts off. Since the
offline enable has no XLOG2_CHECKSUMS record, nothing restores on.

I have only looked at 0001 for this point, so I don't know whether one of the
following patches handles this case.

=== 4

+   /*
+    * Note the data checksum state the flush below starts under.  Replay runs
+    * concurrently and can change the state while the flush is in progress,
+    * in which case the flush covers pages written under both states; see
+    * where the state is persisted further down.
+    */
+   SpinLockAcquire(&XLogCtl->info_lck);
+   checksum_state = XLogCtl->data_checksum_version;
+   SpinLockRelease(&XLogCtl->info_lck);
+

CheckPointGuts(lastCheckPoint.redo, flags);

+        * Persist only if the flush above ran under one state throughout; see
+        * CreateCheckPoint() for why.
+        */
+       SpinLockAcquire(&XLogCtl->info_lck);
+       if (checksum_state == XLogCtl->data_checksum_version)
+           ControlFile->data_checksum_version = checksum_state;
+       SpinLockRelease(&XLogCtl->info_lck);

I think comparing only data_checksum_version cannot detect a complete
on->off->on transition during CheckPointGuts(). The initial and final values
match even though the flush ran under multiple states.

FWIW, while v4-0001 may address other issues present in v1, v1 would avoid the
specific cases described in === 1 through === 3. Some parts of it may therefore
be worth considering here.

Regards,

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

On 28 Aug 2026, at 07:33, Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:

Only looking at 0001 here, I've a few comments:

Thanks, I've yet to dig into it completely but below are a few quick questions
to help me along the way.

=== 1

@@ -9288,17 +9572,33 @@ xlog2_redo(XLogReaderState *record)

SpinLockAcquire(&XLogCtl->info_lck);
XLogCtl->data_checksum_version = state.new_checksum_state;
+ SetLocalDataChecksumState(state.new_checksum_state);
SpinLockRelease(&XLogCtl->info_lck);

This applies every XLOG2_CHECKSUMS record encountered during recovery, even when
the same record was applied before.

For example, a standby can replay the final "on" record and then stop cleanly
without advancing its restartpoint beyond that record. If checksums are subsequently
disabled offline, the next startup begins from the older restartpoint and replays
the same on record again, overriding the offline disable.

Do you mean that checksums are disabled offline across the cluster on all
nodes, or just on the standby?

=== 2

+    * checkPoint.dataChecksumState was sampled while holding the WAL insert
+    * locks, so it is the state in effect at the redo point. 
.
.
.
+   SpinLockAcquire(&XLogCtl->info_lck);
+   if (checkPoint.dataChecksumState == XLogCtl->data_checksum_version)
+       ControlFile->data_checksum_version = checkPoint.dataChecksumState;
+   SpinLockRelease(&XLogCtl->info_lck);

I’m not sure the "state in effect at the redo point" is always correct. There is
a window between inserting the checksum transition record and updating
XLogCtl->data_checksum_version.

XLOG2_CHECKSUMS(on) records the target state. The transition can therefore
proceed as follows:

1. The current shared state is inprogress-on.
2. XLogChecksums() inserts XLOG2_CHECKSUMS(on) and releases its WAL insertion
lock.
3. Before the shared state is updated to on, the checkpoint still reads
inprogress-on and inserts XLOG_CHECKPOINT_REDO.
4. The transition then updates the shared state to on.

The WAL order is then:

XLOG2_CHECKSUMS(on)
XLOG_CHECKPOINT_REDO(inprogress-on)

If the server crashes after the concurrent checkpoint from step 3 completes,
but before the enabling operation’s later checkpoint completes, recovery starts
from that redo point and does not replay the preceding on record. It can
therefore resolve inprogress-on back to off. The equality check above does
not repair this ordering.

If this can happen then online checksums wouldn't work at all right? This
window is happening inside a critical section while DELAY_CHKPT_START is set to
prevent a checkpoint from storing the state and completing to protect against
this. Have you been able to construct a repro (with injection points) where a
REDO record after a CHECKSUM record carries the wrong state?

--
Daniel Gustafsson

Hi,

On Fri, Aug 28, 2026 at 08:53:38AM +0200, Daniel Gustafsson wrote:

On 28 Aug 2026, at 07:33, Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:

Only looking at 0001 here, I've a few comments:

Thanks, I've yet to dig into it completely but below are a few quick questions
to help me along the way.

=== 1

@@ -9288,17 +9572,33 @@ xlog2_redo(XLogReaderState *record)

SpinLockAcquire(&XLogCtl->info_lck);
XLogCtl->data_checksum_version = state.new_checksum_state;
+ SetLocalDataChecksumState(state.new_checksum_state);
SpinLockRelease(&XLogCtl->info_lck);

This applies every XLOG2_CHECKSUMS record encountered during recovery, even when
the same record was applied before.

For example, a standby can replay the final "on" record and then stop cleanly
without advancing its restartpoint beyond that record. If checksums are subsequently
disabled offline, the next startup begins from the older restartpoint and replays
the same on record again, overriding the offline disable.

Do you mean that checksums are disabled offline across the cluster on all
nodes, or just on the standby?

Disabling checksums offline on the standby is sufficient although that is not the
intended procedure.

Disabling offline on both the primary and standby also produce the issue.

=== 2

If this can happen then online checksums wouldn't work at all right?

You’re right, my previous explanation was not fully accurate.

The 0001-specific concern is that a checkpoint can capture
checkPoint.dataChecksumState as inprogress-on, then insert XLOG_CHECKPOINT_REDO
correctly carrying on. The delay protects the flush, but the earlier value remains
stale. The equality check then does not persist on, and recovery no longer adopts
it from the REDO record, so a crash before the following checkpoint completes can
resolve the state back to off.

Have you been able to construct a repro (with injection points) where a
REDO record after a CHECKSUM record carries the wrong state?

Not with an injection point, but you can repro that way:

In xlog.c add 3 sleeps (see repro.txt attached):

- In SetDataChecksumsOn() to hold the launcher at inprogress-on.
- In SetDataChecksumsOn() to park it at on before its own checkpoint.
- In CreateCheckPoint() sleep/spin until XLogCtl->data_checksum_version == on.

Then:

start a cluster with initdb --no-data-checksums
Run SELECT pg_enable_data_checksums()
Then within 60s run CHECKPOINT
Once the checkpoint completes (SHOW data_checksums = on but pg_controldata still shows version 3)
pkill -9 the cluster
restart
check SHOW data_checksums: it comes back off.

Regards,

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

Thanks!

I agree that v4 is not a complete fix, and we could make it better. The question is the balance, as every change also makes it more complex. The main point of it is to try to minimize how invasive of a patch it is, and making sure that offline changes work and don't result in completely breaking a standby.

1: this is a valid issue, but requires somebody doing an offline change immediately after an online change. The effect is that in this case, the standby might roll back the offline change, but it will print out a warning about this into the log, so this is visible, and everything will continue working.

2: my understanding is that if there's a concurrent checkpoint and a crash shortly after it, we might throw away an otherwise completed online checksum after restarting. We properly log that checksums were interrupted, and the state remains "off" on all nodes. While this is not ideal, I think this is an unlikely scenario and not the only such issue, for example a failing DROP DATABASE foo FORCE similarly can interrupt checksums in an unlikely case, as I reported in another thread.

3: also valid, but in my repro of this the warning fired, so it's not silent, and things seem to work fine after the warning, and the user can issue either an online or an offline change.

4: I couldn't construct a repro for this case, I think this can only happen in theory in very specific engineered scenarios

FWIW, while v4-0001 may address other issues present in v1, v1 would avoid the
specific cases described in === 1 through === 3. Some parts of it may therefore
be worth considering here.

I agree that combining the two patches would be the best solution in the warning direction, e.g. solving 1+3 requires the pg_control changes from v1. The reason I left that out is what I started with in this reply: simplicity. I was mainly considering combining the two because of v4 can emit spurious warnings in some cases (and then the additional log state stating the correction), but even with these I am not sure if we should make it more complex, as none of these result in crashes/data corruption, only in state rolling back in some engineering situations. I'll try to look into what adding the two patches together looks like, but it most likely combines their size, as they improve the current master code in different ways.

Hi,

On Fri, Aug 28, 2026 at 04:16:55AM -0700, Zsolt Parragi wrote:

Thanks!

I agree that v4 is not a complete fix, and we could make it better.
The question is the balance, as every change also makes it more
complex. The main point of it is to try to minimize how invasive of a
patch it is, and making sure that offline changes work and don't
result in completely breaking a standby.

1: this is a valid issue, but requires somebody doing an offline
change immediately after an online change.

I don't think it has to be immediate. The window lasts until the standby records
a restartpoint after the XLOG2_CHECKSUMS(on) record. That may happen considerably
later, depending on checkpoint replay and restartpoint creation.

The effect is that in this
print out a warning about this into the log, so this is visible, and
everything will continue working.

Yes, but that leaves mismatched states and that's what we try to avoid.

2: my understanding is that if there's a concurrent checkpoint and a
crash shortly after it, we might throw away an otherwise completed
online checksum after restarting. We properly log that checksums were
interrupted, and the state remains "off" on all nodes.

Right, but the online transition had reached on before the crash and is rolled
back because of 0001’s handling of the stale inprogress-on value. Even if all
nodes return to off, that still looks like an incorrect state rollback introduced
by 0001.

While this is
not ideal, I think this is an unlikely scenario

yeah, probably.

and not the only such
issue, for example a failing DROP DATABASE foo FORCE similarly can
interrupt checksums in an unlikely case, as I reported in another
thread.

I think our case is different.
The checksum state has reached on, and both XLOG2_CHECKSUMS and the later REDO
record carry on. It returns to off due to 0001.
The sleeps in the repro only make the possible interleaving deterministic.

3: also valid, but in my repro of this the warning fired, so it's not
silent, and things seem to work fine after the warning, and the user
can issue either an online or an offline change.

The warning is useful, but I don't think it makes the resulting state correct.
I think that leaves precisely the mismatched state we are trying to avoid.

4: I couldn't construct a repro for this case, I think this can only
happen in theory in very specific engineered scenarios

Yes, it's doable. While that does not lead to correctness issue, it still
questions the logic here.

I agree that combining the two patches would be the best solution in
the warning direction, e.g. solving 1+3 requires the pg_control
changes from v1.

Yeah, but if the resulting patch ends up being significantly more complex, that
would not be reassuring either.

The reason I left that out is what I started with in
this reply: simplicity. I was mainly considering combining the two
because of v4 can emit spurious warnings in some cases

Does that refer to the cases I reported for v4, or did you have additional cases
in mind?

additional log state stating the correction), but even with these I am
not sure if we should make it more complex, as none of these result in
crashes/data corruption, only in state rolling back in some
engineering situations.

Yeah, I understand each of these tradeoffs in isolation. What concerns me is their
cumulative effect: we moved from wanting to reject mismatched states to warning
about them, and we are now considering leaving some known rollback cases unhandled
to keep the patch manageable.

Also, I’m not sure 1 and 3 are limited to engineered situations. Even without
immediate crashes, they can leave the nodes with mismatched states, which is
what the patchset is trying to address.

I'll try to look into what adding the two
patches together looks like, but it most likely combines their size,
as they improve the current master code in different ways.

Thanks!

Regards,

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

Yeah, I understand each of these tradeoffs in isolation. What concerns me is their
cumulative effect: we moved from wanting to reject mismatched states to warning
about them, and we are now considering leaving some known rollback cases unhandled
to keep the patch manageable.

Also, I’m not sure 1 and 3 are limited to engineered situations. Even without
immediate crashes, they can leave the nodes with mismatched states, which is
what the patchset is trying to address.

1, for example requires executing an offline change quickly after an online change. I'm not saying that it shouldn't work better, just questioning how realistic that scenario is.

Yeah, but if the resulting patch ends up being significantly more complex, that
would not be reassuring either.

and

I'll try to look into what adding the two
patches together looks like, but it most likely combines their size,
as they improve the current master code in different ways.

Thanks!

I looked into this, and I was right that if I add the pg_control changes to v4 it nearly doubles the size of the actual code changes from ~300 to ~550 lines, and fixes all the issues you reported while also keeping the existing suite of tests passing. The diff compared to v4 is relatively simple, so I don't think that would be an issue by itself, but it's another control version change.

Hi,

On Fri, Aug 28, 2026 at 11:04:28AM -0500, Zsolt Parragi wrote:

I'll try to look into what adding the two
patches together looks like, but it most likely combines their size,
as they improve the current master code in different ways.

Thanks!

I looked into this,

Thanks!

and I was right that if I add the pg_control
changes to v4 it nearly doubles the size of the actual code changes
from ~300 to ~550 lines, and fixes all the issues you reported

Yeah, that's what I expected, as v1 included those changes to handle these cases.

while
also keeping the existing suite of tests passing. The diff compared to
v4 is relatively simple, so I don't think that would be an issue by
itself,

Thanks! I don't see the patch attached. Would you mind sharing it?

but it's another control version change.

Do you see the control version change as a concern?

Regards,

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

Thanks! I don't see the patch attached. Would you mind sharing it?

Sorry, I forgot to attach it to the previous email.

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.

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

On 31 Aug 2026, at 07:06, Bertrand Drouvot <bertranddrouvot.pg@gmail.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

Hi,

On Mon, Aug 31, 2026 at 09:16:52AM +0200, Daniel Gustafsson wrote:

On 31 Aug 2026, at 07:06, Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:

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

Yeah, that could make sense, at least to make the limitation explicit and warn
users about these cases.

as they work with completely different durability models.

Yeap.

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.

I agree.

Making sure that StartupXLOG
can detect the offline change (or something along those lines) and properly log
it seems like a better starting point.

That sounds worth exploring. Would the idea be for pg_checksums to leave a
marker in pg_control which StartupXLOG would turn into a WAL logged transition?

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.

Yeah, I see your point that the underlying issue is the non WAL logged change.
My concern was not about a pg_control change in itself, but that addressing this
case might require further changes to the current pg_control design, which adds
risk this close to RC1.

Regards,

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

On Mon, 31 Aug 2026, Bertrand Drouvot <bertranddrouvot.pg@gmail.com> wrote:

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

...

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.

This is one of the variations I mentioned earlier, that we can emit spurious warnings in some cases, and later state that the state restored, and print a log about that. And with that, this is one f the main reasons why I thought we shouldn't try to make it an error in 19.

This is reachable in even simpler, not so engineered scenarios. I added the logging of restored state especially for cases like this, so we can later see that the warning resolved itself.

On Mon, 31 Aug 2026, Daniel Gustafsson <daniel@yesql.se> wrote:

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.

We can simply prevent this (in this case) by not printing out any warnings until we reached the primary's LSN, the standby already receives this information at connection time. I have a patch for it, but it was again a bit more complex than I initially hoped for. (and even then, we will still have a few corner-cases left with spurious warnings, if I remember correctly even with that there would be still an issue with chaining standbys, that needs another fix)

Yeah, I see your point that the underlying issue is the non WAL logged change.
My concern was not about a pg_control change in itself, but that addressing this
case might require further changes to the current pg_control design, which adds
risk this close to RC1.

(When I read the emails previously I completely missed the part that
in this case it remains a mismatch. Sorry, that was before coffee)

I don't think we can get this completely right without both wal and
pg_control changes (and possibly some extra connection-time
communication between the standby and primary to provide early
reporting instead of delayed in the most common cases)
If we want to move toward the error direction in pg20 (which we
should) we will have to log that an offline change happened.

I attached v6 which for now documents the current behavior as-is, and
other than that I reorganized the test files.

Hi,

On Mon, Aug 31, 2026 at 11:55:01AM +0100, Zsolt Parragi wrote:

Yeah, I see your point that the underlying issue is the non WAL logged change.
My concern was not about a pg_control change in itself, but that addressing this
case might require further changes to the current pg_control design, which adds
risk this close to RC1.

I don't think we can get this completely right without both wal and
pg_control changes (and possibly some extra connection-time
communication between the standby and primary to provide early
reporting instead of delayed in the most common cases)
If we want to move toward the error direction in pg20 (which we
should) we will have to log that an offline change happened.

I attached v6 which for now documents the current behavior as-is, and
other than that I reorganized the test files.

Thanks! I did not look in details but it looks like that pg_upgrade --check against
a running source cluster with checksums enabled will still fail (finding === 2 in
[0]: /messages/by-id/apUL3N4IE934qJ08@bdtpg

[0]: /messages/by-id/apUL3N4IE934qJ08@bdtpg
[1]: /messages/by-id/apU4/hmRv/4gv20W@bdtpg

Regards,

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

Thanks! I did not look in details but it looks like that pg_upgrade --check against
a running source cluster with checksums enabled will still fail (finding === 2 in
[0], tested with the test shared in [1]).

Yes, I missed that in the previous version, v7 fixes it.

Hi,

On Mon, Aug 31, 2026 at 02:10:15PM +0100, Zsolt Parragi wrote:

Thanks! I did not look in details but it looks like that pg_upgrade --check against
a running source cluster with checksums enabled will still fail (finding === 2 in
[0], tested with the test shared in [1]).

Yes, I missed that in the previous version, v7 fixes it.

Thanks, yeah it fixes it.

=== 1

The v7-0001 commit message says:

"
pg_rewind keeps the target's own state, watermark and flag in the
control file it installs, since most of the data directory remains the
target's; replay from the last common checkpoint still adopts a state
its watermark does not cover, and applies any online transition the
target has not seen.
"

I found a case where the source's online enable occurs after divergence and was
never seen by the target, but replay skips it instead of applying it:

1. Start a primary and standby with checksums off.
2. Stop the primary and promote the standby.
3. Enable checksums online on the promoted source.
4. Restart the old primary on the old timeline, advance its WAL beyond the source’s
enable watermark, then enable and disable checksums online.
5. Rewind the old primary from the promoted source.

The target is now off, with a watermark on the old timeline numerically greater
than the source's enable records on the new timeline. pg_rewind preserves that
watermark, so recovery treats those source records as already applied and skips
them.

Maybe the watermark needs timeline context, or pg_rewind needs to adjust it
when it comes from the target's divergent history?

Regards,

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