Subject: Reindex doesn’t not working replica nodes

From: Mohammed Afsar <[email-masked]>
Date: Wed, 14 Aug 2024 18:28:01 +0530

Dear experts,

We have initiated reindex on source db but it not replicated to replica db
and we have having select query fetching with zero rows but data exiting on
the table.
Postgres 11.10 streaming replication

Regards,
Mohammed Afsar

---

From: khan Affan <[email-masked]>
Date: Fri, 16 Aug 2024 16:20:05 +0500

Hi Mohammed,

PostgreSQL replication is based on replicating WAL (Write-Ahead Log)
records, which capture changes to data files such as inserts, updates,
deletes, and the creation of new tables. However,

WAL does not include the physical structure of indexes.

If you're encountering issues like zero rows being fetched from the
replica, it could be due to index inconsistencies or replication lag. To
ensure your read replica has updated indexes, you can promote the replica
to be the primary node, perform the REINDEX operation, and then revert the
roles, making the original primary node the primary again.

Regards
Muhammad Affan

---

From: Mohammed Afsar <[email-masked]>
Date: Fri, 16 Aug 2024 17:49:31 +0530

Thanks for the information but we are encountered OS compatibility issues
source to replica servers.

Source Alma Linux 9.0
Replica centos 7.0
Patroni 3.1.0

Regards,
Mohammed Afsar

On Fri, 16 Aug 2024 at 4:50 PM, khan Affan <[email-masked]> wrote:

>> We have initiated reindex on source db but it not replicated to replica
>> db and we have having select query fetching with zero rows but data exiting
>> on the table.

---

From: Peter Gram <[email-masked]>
Date: Fri, 16 Aug 2024 15:03:01 +0200

Hi Mohammed

If we are talking physical replication then the wall includes changes to
index’s data. If we are talking logical replication the wall does not
include index changes.

Med venlig hilsen

Peter Gram
Sæbyholmsvej 18
2500 Valby

Mobile: (+45) 5374 7107
Email: [email-masked]

---

From: khan Affan <[email-masked]>
Date: Fri, 16 Aug 2024 18:08:10 +0500

Thanks for sharing the additional information. Initially, the query didn't
provide the full context regarding OS compatibility, which is crucial in
understanding the replication issues (physical and logical).
For future inquiries, including details like OS versions, replication
setups, and any specific configurations can help us address your concerns
more accurately and efficiently.

---

From: Mohammed Afsar <[email-masked]>
Date: Fri, 16 Aug 2024 18:38:22 +0530

Hi Peter,

We are using streaming replications but still reindex is not working source
db has 11.15replica has 11.11 PostgreSQL version.

Regards,
Mohammed Afsar

---

From: Scott Ribe <[email-masked]>
Date: Fri, 16 Aug 2024 07:19:04 -0600

> On Aug 16, 2024, at 7:08 AM, Mohammed Afsar <[email-masked]> wrote:
> 
> We are using streaming replications but still reindex is not working source db has 11.15replica has 11.11 PostgreSQL version.

This is not your problem.

As you understand, I think, with streaming replication all files, including indexes, are exactly duplicated on the replica. If the OS on the two sides have different glibc versions which have different collations, then the index structure of the primary will be a corrupted index on the replica. You have to fix your OS version incompatibility, or move to a version of postgres which can use ICU collations and make sure that library is compatible on both sides.

---

From: Mohammed Afsar <[email-masked]>
Date: Fri, 16 Aug 2024 19:18:44 +0530

Got it thanks for the deep dive explanation.Scott 👍🏻

And parallel  we got below error also included OS incompatibility?

postgres[265938]: segfault at 18 ip 00000000004cf11b

Regards,
Mohammed Afsar

On Fri, 16 Aug 2024 at 6:49 PM, Scott Ribe <[email-masked]>
wrote:

> > We are using streaming replications but still reindex is not working
> source db has 11.15replica has 11.11 PostgreSQL version.
>
> As you understand, I think, with streaming replication all files,
> including indexes, are exactly duplicated on the replica. If the OS on the
> two sides have different glibc versions which have different collations,
> then the index structure of the primary will be a corrupted index on the
> replica. You have to fix your OS version incompatibility, or move to a
> version of postgres which can use ICU collations and make sure that library
> is compatible on both sides.
>
>