MIME-Version: 1.0
References: 
 <CA+6HdutGU9Au=ADaa+Q6jp=HGXFeUrtBPcfdHKK2fLdeKnd+Tg@mail.gmail.com>
 <CAF4emOmk7ZOqQ0+0FDzaPY+ZkisMYVa4QAhC8MjdQdr2MQViTA@mail.gmail.com>
In-Reply-To: 
 <CAF4emOmk7ZOqQ0+0FDzaPY+ZkisMYVa4QAhC8MjdQdr2MQViTA@mail.gmail.com>
From: Peter Gram <peter.m.gram@gmail.com>
Date: Fri, 16 Aug 2024 15:03:01 +0200
Message-ID: 
 <CAJ=80GUOozWnm14_vJ3MccTZ9LNJ7d6Rsd_bkszMr=MrWdFm-w@mail.gmail.com>
Subject: =?UTF-8?Q?Re=3A_Reindex_doesn=E2=80=99t_not_working_replica_nodes?=
To: khan Affan <bawag773@gmail.com>
Cc: Mohammed Afsar <vmdapsar@gmail.com>, pgsql-admin@lists.postgresql.org,
	pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fee606061fcc9437"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJ%3D80GUOozWnm14_vJ3MccTZ9LNJ7d6Rsd_bkszMr%3DMrWdFm-w%40mail.gmail.com>
Precedence: bulk

--000000000000fee606061fcc9437
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Mohammed

If we are talking physical replication then the wall includes changes to
index=E2=80=99s data. If we are talking logical replication the wall does n=
ot
include index changes.

Med venlig hilsen

Peter Gram
S=C3=A6byholmsvej 18
2500 Valby

Mobile: (+45) 5374 7107
Email: peter.m.gram@gmail.com


On Fri, 16 Aug 2024 at 13.20, khan Affan <bawag773@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 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 th=
e
> 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 <vmdapsar@gmail.co=
m> 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 exit=
ing
>> on the table.
>> Postgres 11.10 streaming replication
>>
>> Regards,
>> Mohammed Afsar
>>
>

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

<div dir=3D"auto">Hi Mohammed</div><div dir=3D"auto"><br></div><div dir=3D"=
auto">If we are talking physical replication then the wall includes changes=
 to index=E2=80=99s data. If we are talking logical replication the wall do=
es not include index changes.=C2=A0</div><div dir=3D"auto"><br clear=3D"all=
"><div dir=3D"auto"><div dir=3D"ltr" class=3D"gmail_signature" data-smartma=
il=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=
=3D"ltr"><div> Med venlig hilsen<br><br>Peter Gram<br>S=C3=A6byholmsvej 18=
=C2=A0</div><div>2500 Valby <span style=3D"font-size:12.8px"></span><br><br=
></div><div>Mobile: (+45) 5374 7107<br></div><div>Email: <a href=3D"mailto:=
peter.m.gram@gmail.com" target=3D"_blank">peter.m.gram@gmail.com</a></div><=
div><div style=3D"color:rgb(34,34,34);font-family:arial,sans-serif;font-siz=
e:small;font-style:normal;font-weight:400;letter-spacing:normal;text-align:=
start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0=
px;background-color:rgb(255,255,255)"><div><div dir=3D"ltr"><div><div dir=
=3D"ltr"><div><div dir=3D"ltr"><div dir=3D"ltr"><div><div><div><div><br></d=
iv></div></div></div></div></div></div></div></div></div></div></div></div>=
</div></div></div></div></div></div></div></div><div><br></div><div><br><di=
v class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, 16 Au=
g 2024 at 13.20, khan Affan &lt;<a href=3D"mailto:bawag773@gmail.com">bawag=
773@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div d=
ir=3D"ltr">Hi Mohammed,<br><br>PostgreSQL replication is based on replicati=
ng WAL (Write-Ahead Log) records, which capture changes to data files such =
as inserts, updates, deletes, and the creation of new tables. However, <br>=
<br>WAL does not include the physical structure of indexes.<br><br>If you&#=
39;re encountering issues like zero rows being fetched from the replica, it=
 could be due to index inconsistencies or replication lag. To ensure your r=
ead replica has updated indexes, you can promote the replica to be the prim=
ary node, perform the REINDEX operation, and then revert the roles, making =
the original primary node the primary again.<br><br>Regards<br>Muhammad Aff=
an</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr=
">On Wed, Aug 14, 2024 at 5:58=E2=80=AFPM Mohammed Afsar &lt;<a href=3D"mai=
lto:vmdapsar@gmail.com" target=3D"_blank">vmdapsar@gmail.com</a>&gt; wrote:=
<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"aut=
o">Dear experts,</div><div dir=3D"auto"><br></div><div dir=3D"auto"><br></d=
iv><div dir=3D"auto">We have initiated reindex on source db but it not repl=
icated to replica db and we have having select query fetching with zero row=
s but data exiting on the table.<br clear=3D"all">Postgres 11.10 streaming =
replication=C2=A0<br clear=3D"all"><div dir=3D"auto"><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr"><br></div><div dir=3D"ltr">Regards,</=
div><div dir=3D"ltr">Mohammed Afsar</div></div></div></div>
</blockquote></div>
</blockquote></div></div>

--000000000000fee606061fcc9437--