Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sewgy-00Ebjh-F4 for pgsql-admin@arkaria.postgresql.org; Fri, 16 Aug 2024 13:08:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sewgx-005B2a-0a for pgsql-admin@arkaria.postgresql.org; Fri, 16 Aug 2024 13:08:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sewgw-005B2R-JI for pgsql-admin@lists.postgresql.org; Fri, 16 Aug 2024 13:08:30 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sewgu-0059av-DI for pgsql-admin@postgresql.org; Fri, 16 Aug 2024 13:08:30 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-268a9645e72so1307400fac.1 for ; Fri, 16 Aug 2024 06:08:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723813706; x=1724418506; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Ri6b8ay/p4ZuthEwv0twTdnXpXnA1D2LrRr5V8o7mUQ=; b=CoRYRWO832mbA7kNOA7wMK01oCS72p4Lm8NZ0qQmfOzzfBDS5MAnXFLAAd/vlHA8oO dg4GEjqyfl2PwDL1xIUiuNIlyanIo7RDkESPNA33Dpf4+h7qY/Gysp2UH6yBZD8TkcEL XwphYgKoX1/TCjo47TzyuXPuZ2pC8ZZE4/Jco+ofD8W6MdI4zlMWvoMQzCNGKu7JhCDQ oyu8XkQVy4jKp3+VQdJYL4twfGUJGAsM0bZYUTHhTWXBW+2ardZzmbZ2uFWNu7B02NfY b5foW2v3FuWLnL1PvSJZBwkWpt769XDUASiwsOmzexhw4H2kNmrzzANbGHiH6iHS/iAc WFbQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723813706; x=1724418506; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Ri6b8ay/p4ZuthEwv0twTdnXpXnA1D2LrRr5V8o7mUQ=; b=IKDw0YRQUMi2YDXNdAAyE2leC+GIH06mqqj4/Uzi0mCZBFSqG5MEyiUrOaQKSF0mjV lhqVLryfCI/ZET6rSlVMyIhvkpfrZAsFG40rAGWXXsNFK0m70GiMBhdN9GMy/qU4orMS PDunk/1wQsiqBlViroCcJX8o/b1pSKQkN4XMYXHXrfNIoIWTZN8y1GanoMgQBFZ5RUqZ a5GgT10SDkSp99GsR2ZZ2NLRQbTxi84Ltbl/m3U+G813XtQ/Aq9PwlQE46/hOQ1wkRFa JX0KDRjBAAoF7VUxPdY82zgjaaFEkGIYEVG0p2jlzQrx1chx+ImzaIhHfu/CPlv8wTHg oMmg== X-Forwarded-Encrypted: i=1; AJvYcCXpAE0PWnKeIz1YKhaLrkNdpfeJYbT3IN7tO2tljrXg3EA/bDjXurHmoyIqLRQDdC7S9Z349q8P2qC2UdONfSgiX4sqvuDwCsN6kw== X-Gm-Message-State: AOJu0YxzTJyc2RU0nQFwXwe1HgsB97jZ7dYCcpgS75GsTh0FqmVheAZ6 w0ZyI0AziMOUHptQUHTesbPcFanAsnsj4407577zhN81s87g20DeAeBfZPZ2flE60Cl1JNzgFGZ 9zaAvNMEQuxTkaoYKgZxuJoW7+w== X-Google-Smtp-Source: AGHT+IEHZMfCMrc/Di9jJxJUuP8RHE26xtAYDp5kGTppBgLKNwFdkXV8YoW6CJU4mhi7p7XIcL4MtxJMl13jr42BoLg= X-Received: by 2002:a05:687c:2b88:b0:25e:14e9:10c9 with SMTP id 586e51a60fabf-2701c3454c3mr2615164fac.6.1723813706599; Fri, 16 Aug 2024 06:08:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: khan Affan Date: Fri, 16 Aug 2024 18:08:10 +0500 Message-ID: Subject: =?UTF-8?Q?Re=3A_Reindex_doesn=E2=80=99t_not_working_replica_nodes?= To: Mohammed Afsar Cc: pgsql-admin@lists.postgresql.org, pgsql-admin Content-Type: multipart/alternative; boundary="000000000000a41954061fcca760" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a41954061fcca760 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Mohammed Afsar = 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=E2=80=AFPM, khan Affan w= rote: > >> 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 replic= a >> to be the primary node, perform the REINDEX operation, and then revert t= he >> roles, making the original primary node the primary again. >> >> Regards >> Muhammad Affan >> >> On Wed, Aug 14, 2024 at 5:58=E2=80=AFPM Mohammed Afsar >> 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 exi= ting >>> on the table. >>> Postgres 11.10 streaming replication >>> >>> Regards, >>> Mohammed Afsar >>> >> --000000000000a41954061fcca760 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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


On Fri, Aug 16, 2024 at 5:19=E2=80= =AFPM Mohammed Afsar <vmdapsar@gma= il.com> wrote:
Thanks for the information but we are encountered O= S compatibility issues source to replica servers.
Source Alma Linux 9.0
Repl= ica centos 7.0
Patroni 3.1.0
=
Regards,
Mohammed Afsar


On Fri, 16 Aug 2024 at 4:50=E2=80=AFPM, k= han Affan <bawag= 773@gmail.com> 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 ta= bles. However,

WAL does not include the physical structure of index= es.

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

Rega= rds
Muhammad Affan

On Wed, Aug 14, 2024 at 5:58=E2=80=AFPM Mohammed Afsar= <vmdapsar@gmail= .com> wrote:
Dear experts,


We have initiated reindex on source= db but it not replicated to replica db and we have having select query fet= ching with zero rows but data exiting on the table.
Postgr= es 11.10 streaming replication=C2=A0

Regards,
Mohammed Afsar
--000000000000a41954061fcca760--