MIME-Version: 1.0
References: <CAJ4rSwspyYBppQS701C=BVFX2yrP1JErgDCZXbC15BQh2zAS1Q@mail.gmail.com>
In-Reply-To: <CAJ4rSwspyYBppQS701C=BVFX2yrP1JErgDCZXbC15BQh2zAS1Q@mail.gmail.com>
From: Chris Wilson <chris+google@qwirx.com>
Date: Wed, 22 Oct 2025 13:30:12 +0100
Message-ID: <CAOg7f80JJCMHXhNLUjnHMwBFV-f9Msv4L=PML0CdaZ4n47uEeA@mail.gmail.com>
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9
 (PostgreSQL 11 streaming replication)
To: Bala M <krishna.pgdba@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000082cdf20641be7cb6"
Archived-At: <https://www.postgresql.org/message-id/CAOg7f80JJCMHXhNLUjnHMwBFV-f9Msv4L%3DPML0CdaZ4n47uEeA%40mail.gmail.com>
Precedence: bulk

--00000000000082cdf20641be7cb6
Content-Type: text/plain; charset="UTF-8"

Hi Krishna,

This might be due to the upgrade in glibc between RHEL7 and RHEL8, which
includes updated collations that change sort orders and thus invalidate
existing indexes, forcing them to be rebuilt (on a RHEL>7 system) if they
contain any values which sort differently under the new collation than the
old one.

More details can be found here, including a query to identify affected
indexes: https://wiki.postgresql.org/wiki/Locale_data_changes

[RHEL] *Version 8 uses the new locale data*. Therefore, caution will be
> necessary when upgrading."


Thanks, Chris.

On Wed, 22 Oct 2025 at 11:53, Bala M <krishna.pgdba@gmail.com> wrote:

> Hi Team,
>
> We are facing an issue related to index corruption after migrating our
> PostgreSQL 11 setup from *RHEL 7* to *RHEL 9* using *streaming
> replication* (base backup method).
>
> After bringing up the standby on RHEL 9, we observed that certain tables
> are not returning results when queries use indexed scans. Upon
> investigation, the following check confirms index corruption:
>
> The same indexes work fine on the RHEL 7 (primary) side. However, on the RHEL 9 replica, queries that rely on this index return zero records.
> Rebuilding the indexes  fixed the issue temporarily but we have many indexes  and our DB size is more than 10TB.
>
> *Environment details:*
>
>    -
>
>    PostgreSQL Version: 11.15
>    -
>
>    OS on primary: RHEL 7.9
>    -
>
>    OS on standby: RHEL 9.6
>    -
>
>    Replication Type: Streaming replication (initialized using
>    pg_basebackup)
>    -
>
>    Data Directory initialized from RHEL 7 base backup
>
> *Issue Summary:*
>
>    -
>
>    Indexes appear and are the same size as per prod  on standby after
>    base backup restore.
>    -
>
>    We are able to read  the data from the tables without index scans on
>    standy by RHEL 9.
>    -
>
>    No filesystem or WAL errors observed in logs.
>
> Could this be related to OS-level binary or page layout differences
> between RHEL 7 and RHEL 9 for PostgreSQL 11 binaries?
> Any insights or recommended actions would be greatly appreciated.
>
>
> Thanks & Regards,
> *krishna.*
>
>

--00000000000082cdf20641be7cb6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Krishna,</div><div><br></div><div>This might be du=
e to the upgrade in glibc between RHEL7 and RHEL8, which includes updated c=
ollations that change sort orders and thus invalidate existing indexes, for=
cing them to be rebuilt (on a RHEL&gt;7 system) if they contain any values =
which sort differently under the new collation than the old one.</div><div>=
<br></div><div>More details can be found here, including a query to identif=
y affected indexes:=C2=A0<a href=3D"https://wiki.postgresql.org/wiki/Locale=
_data_changes">https://wiki.postgresql.org/wiki/Locale_data_changes</a></di=
v><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">[RHEL]=
=C2=A0<b>Version 8 uses the new locale data</b>. Therefore, caution will be=
 necessary when upgrading.&quot;</blockquote><div><br></div><div>Thanks, Ch=
ris.</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div d=
ir=3D"ltr" class=3D"gmail_attr">On Wed, 22 Oct 2025 at 11:53, Bala M &lt;<a=
 href=3D"mailto:krishna.pgdba@gmail.com">krishna.pgdba@gmail.com</a>&gt; wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"><div><p>Hi Team,</p>
<p>We are facing an issue related to index corruption after migrating our P=
ostgreSQL 11 setup from <strong>RHEL 7</strong> to <strong>RHEL 9</strong> =
using <strong>streaming replication</strong> (base backup method).</p>
<p>After bringing up the standby on RHEL 9, we observed that certain tables=
 are not returning results when queries use indexed scans. Upon investigati=
on, the following check confirms index corruption:</p>
<pre><div><div><div><div></div></div></div><div dir=3D"ltr"><span style=3D"=
font-family:Arial,Helvetica,sans-serif">The same indexes work fine on the R=
HEL 7 (primary) side. However, on the RHEL 9 replica, queries that rely on =
this index return zero records. </span></div><div dir=3D"ltr"><span style=
=3D"font-family:Arial,Helvetica,sans-serif">Rebuilding the indexes </span><=
span style=3D"font-family:Arial,Helvetica,sans-serif"> fixed the issue temp=
orarily but we have many indexes  and our DB size is more than 10TB.</span>=
</div></div></pre>
<p><strong>Environment details:</strong></p>
<ul>
<li>
<p>PostgreSQL Version: 11.15</p>
</li>
<li>
<p>OS on primary: RHEL 7.9</p>
</li>
<li>
<p>OS on standby: RHEL 9.6</p>
</li>
<li>
<p>Replication Type: Streaming replication (initialized using <code>pg_base=
backup</code>)</p>
</li>
<li>
<p>Data Directory initialized from RHEL 7 base backup</p>
</li>
</ul>
<p><strong>Issue Summary:</strong></p>
<ul>
<li>
<p>Indexes appear and are the same=C2=A0size as per prod=C2=A0 on standby a=
fter base backup restore.</p>
</li>
<li>
<p>We are able to read=C2=A0 the data from the tables without index scans o=
n standy by RHEL 9.</p>
</li>
<li>
<p>No filesystem or WAL errors observed in logs.</p>
</li>
</ul>
<p>Could this be related to OS-level binary or page layout differences betw=
een RHEL 7 and RHEL 9 for PostgreSQL 11 binaries?<br>
Any insights or recommended actions would be greatly appreciated.</p><p><br=
></p>
<p>Thanks &amp; Regards,<br><b>krishna.</b></p></div><div><div dir=3D"ltr" =
class=3D"gmail_signature"><div dir=3D"ltr"><div><br></div></div></div></div=
></div>
</blockquote></div>

--00000000000082cdf20641be7cb6--