public inbox for [email protected]help / color / mirror / Atom feed
Reindex doesn’t not working replica nodes 8+ messages / 4 participants [nested] [flat]
* Reindex doesn’t not working replica nodes @ 2024-08-14 12:58 Mohammed Afsar <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Mohammed Afsar @ 2024-08-14 12:58 UTC (permalink / raw) To: [email protected]; pgsql-admin 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 11:20 khan Affan <[email protected]> parent: Mohammed Afsar <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: khan Affan @ 2024-08-16 11:20 UTC (permalink / raw) To: Mohammed Afsar <[email protected]>; +Cc: [email protected]; pgsql-admin 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 On Wed, Aug 14, 2024 at 5:58 PM Mohammed Afsar <[email protected]> wrote: > 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 > ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 12:19 Mohammed Afsar <[email protected]> parent: khan Affan <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Mohammed Afsar @ 2024-08-16 12:19 UTC (permalink / raw) To: khan Affan <[email protected]>; +Cc: [email protected]; pgsql-admin 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 protected]> wrote: > 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 > > On Wed, Aug 14, 2024 at 5:58 PM Mohammed Afsar <[email protected]> wrote: > >> 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 >> > ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 13:03 Peter Gram <[email protected]> parent: khan Affan <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Peter Gram @ 2024-08-16 13:03 UTC (permalink / raw) To: khan Affan <[email protected]>; +Cc: Mohammed Afsar <[email protected]>; [email protected]; pgsql-admin 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 protected] On Fri, 16 Aug 2024 at 13.20, khan Affan <[email protected]> wrote: > 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 > > On Wed, Aug 14, 2024 at 5:58 PM Mohammed Afsar <[email protected]> wrote: > >> 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 >> > ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 13:08 khan Affan <[email protected]> parent: Mohammed Afsar <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: khan Affan @ 2024-08-16 13:08 UTC (permalink / raw) To: Mohammed Afsar <[email protected]>; +Cc: [email protected]; pgsql-admin 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. On Fri, Aug 16, 2024 at 5:19 PM Mohammed Afsar <[email protected]> wrote: > 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 protected]> wrote: > >> 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 >> >> On Wed, Aug 14, 2024 at 5:58 PM Mohammed Afsar <[email protected]> >> wrote: >> >>> 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 >>> >> ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 13:08 Mohammed Afsar <[email protected]> parent: Peter Gram <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Mohammed Afsar @ 2024-08-16 13:08 UTC (permalink / raw) To: Peter Gram <[email protected]>; +Cc: khan Affan <[email protected]>; [email protected]; pgsql-admin 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 On Fri, 16 Aug 2024 at 6:33 PM, Peter Gram <[email protected]> wrote: > 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 protected] > > > > On Fri, 16 Aug 2024 at 13.20, khan Affan <[email protected]> wrote: > >> 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 >> >> On Wed, Aug 14, 2024 at 5:58 PM Mohammed Afsar <[email protected]> >> wrote: >> >>> 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 >>> >> ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 13:19 Scott Ribe <[email protected]> parent: Mohammed Afsar <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Scott Ribe @ 2024-08-16 13:19 UTC (permalink / raw) To: Mohammed Afsar <[email protected]>; +Cc: pgsql-admin > On Aug 16, 2024, at 7:08 AM, Mohammed Afsar <[email protected]> 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. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex doesn’t not working replica nodes @ 2024-08-16 13:48 Mohammed Afsar <[email protected]> parent: Scott Ribe <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Mohammed Afsar @ 2024-08-16 13:48 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: pgsql-admin 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 protected]> wrote: > > On Aug 16, 2024, at 7:08 AM, Mohammed Afsar <[email protected]> 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. > > ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-08-16 13:48 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-14 12:58 Reindex doesn’t not working replica nodes Mohammed Afsar <[email protected]> 2024-08-16 11:20 ` khan Affan <[email protected]> 2024-08-16 12:19 ` Mohammed Afsar <[email protected]> 2024-08-16 13:08 ` khan Affan <[email protected]> 2024-08-16 13:03 ` Peter Gram <[email protected]> 2024-08-16 13:08 ` Mohammed Afsar <[email protected]> 2024-08-16 13:19 ` Scott Ribe <[email protected]> 2024-08-16 13:48 ` Mohammed Afsar <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox